org.smartloli.util.JSqlUtils Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of jsql-client Show documentation
Intermediate DataSet use sql to query , Through the jsql-client , we can use sql to get results .
There is a newer version: 1.0.2
/**
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.smartloli.util;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.smartloli.common.map.JSqlMapData;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.gson.Gson;

/**
 * Created by Mar 29, 2016
 *
 * @author smartloli
 * 
 */
public class JSqlUtils {

	/**
	 * 
	 * @param tabSchema
	 *            : Table column,such as {"id":"integer","name":"varchar"}
	 * @param tableName
	 *            : Defining table names for query datasets, such as "user"
	 * @param dataSets
	 *            : DataSets ,such as
	 *            [{"id":1,"name":"aaa"},{"id":2,"name":"bbb"},{}...]
	 * @param sql
	 *            : such as "SELECT * FROM TBL"
	 * 
	 * @return String
	 * @throws Exception
	 *             : Throws an exception
	 */
	public static String query(JSONObject tabSchema, String tableName, JSONArray dataSets, String sql) throws Exception {
		File file = createTempJson();
		List> list = new LinkedList>();
		for (Object obj : dataSets) {
			JSONObject object = (JSONObject) obj;
			List tmp = new LinkedList<>();
			for (String key : object.keySet()) {
				tmp.add(object.getString(key));
			}
			list.add(tmp);
		}
		JSqlMapData.loadSchema(tabSchema, tableName, list);

		Class.forName("org.apache.calcite.jdbc.Driver");
		Properties info = new Properties();

		Connection connection = DriverManager.getConnection("jdbc:calcite:model=" + file.getAbsolutePath(), info);
		Statement st = connection.createStatement();
		ResultSet result = st.executeQuery(sql);
		ResultSetMetaData rsmd = result.getMetaData();
		List> ret = new ArrayList>();
		while (result.next()) {
			Map map = new HashMap();
			for (int i = 1; i <= rsmd.getColumnCount(); i++) {
				map.put(rsmd.getColumnName(i), result.getString(rsmd.getColumnName(i)));
			}
			ret.add(map);
		}
		result.close();
		connection.close();
		return new Gson().toJson(ret);
	}

	private static File createTempJson() throws IOException {
		JSONObject object = new JSONObject();
		object.put("version", "1.0");
		object.put("defaultSchema", "db");
		JSONArray array = new JSONArray();
		JSONObject tmp = new JSONObject();
		tmp.put("name", "db");
		tmp.put("type", "custom");
		tmp.put("factory", "org.smartloli.schema.JSqlSchemaFactory");
		JSONObject tmp2 = new JSONObject();
		tmp.put("operand", tmp2.put("database", "calcite_memory_db"));
		array.add(tmp);
		object.put("schemas", array);
		File f = File.createTempFile("calcitedb", ".json");
		FileWriter out = new FileWriter(f);
		out.write(object.toJSONString());
		out.close();
		f.deleteOnExit();
		return f;
	}

}