ZKJServer/src/main/java/com/sundata/internalevaluation/calc/util/CalciteUtil.java

package com.sundata.internalevaluation.calc.util;

import org.apache.calcite.adapter.file.JsonScannableTable;
import org.apache.calcite.jdbc.CalciteConnection;
import org.apache.calcite.schema.SchemaPlus;
import org.apache.calcite.schema.impl.AbstractSchema;
import org.apache.calcite.util.Sources;

import java.sql.*;

/**
 * Created by IntelliJ IDEA.
 *
 * @author JoeLazy
 * @date 2025-02-22 15:56:09
 * @description: json转虚拟数据库、表进行sql查询工具类
 */
public class CalciteUtil {

    private Connection connection;

    private SchemaPlus rootSchema;

    private Statement statement;

    private ResultSet resultSet;

    private CalciteUtil() {
    }


    /**
     * 获取工具类对象实例
     * @return CalciteUtil
     * @throws SQLException
     */
    public static CalciteUtil getInstance() throws SQLException {
        CalciteUtil calciteUtil = new CalciteUtil();
        calciteUtil.createRootSchema();
        return calciteUtil;
    }


    /**
     * 创建跟根schema
     * @throws SQLException
     */
    private void createRootSchema() throws SQLException {
        connection = DriverManager.getConnection("jdbc:calcite:");
        CalciteConnection calcConnection = connection.unwrap(CalciteConnection.class);
        rootSchema = calcConnection.getRootSchema();
    }


    /**
     * 创建 域 （数据库）
     * @param dbName  数据库名
     * @return 数据库对应的 Schema
     */
    public SchemaPlus createDatabase(String dbName) {
        return rootSchema.add(dbName, new AbstractSchema());
    }


    /**
     * 创建表格
     * @param dbSchema 数据库对应的 Schema
     * @param tabName 数据表名
     * @param json listMap对应json    [{a:a1,b:b1},{a:a2,b:b2}...]
     * @throws SQLException
     */
    public void createTable(SchemaPlus dbSchema, String tabName, String json) throws SQLException {
        if (dbSchema == null) {
            throw new RuntimeException("dbSchema 不能为空 ！！");
        }
        if (rootSchema.getSubSchema(dbSchema.getName()) == null) {
            throw new RuntimeException("dbSchema不存在！！");
        } else {
            dbSchema.add(tabName, new JsonScannableTable(Sources.of(json)));
        }
        dbSchema.add(tabName, new JsonScannableTable(Sources.of(json)));
    }


    /**
     * 执行sql
     * @param sql sql语句
     * @return 查询结果（仅返回第一行第一列）    如果无数据返回 null
     * @throws SQLException
     */
    public Object doExecute(String sql) throws SQLException {
        if (statement == null) {
            statement = connection.createStatement();
        }
        resultSet = statement.executeQuery(sql);
        if (resultSet.next()) {
            return resultSet.getObject(1);
        }
        return null;
    }


    /**
     * 关闭资源
     * @throws SQLException
     */
    public void closeResource() throws SQLException {
        if (resultSet != null) {
            resultSet.close();
        }
        if (statement != null) {
            statement.close();
        }
        if (connection != null) {
            connection.close();
        }
    }


    public static void main(String[] args) throws SQLException {
        String subject = "[\n" +
                "    {\n" +
                "      \"subNo\": \"07cc9eca-4e66-4d1c-9dd1-fe4671c2e18b\",\n" +
                "      \"subName\": \"Associate Degree in Health Science\"\n" +
                "    },\n" +
                "    {\n" +
                "      \"subNo\": \"cda7b717-bbff-40fd-af90-7e22dcea8457\",\n" +
                "      \"subName\": \"Associate Degree in Medicine\"\n" +
                "    },\n" +
                "    {\n" +
                "      \"subNo\": \"9cf8534b-37c6-4365-8453-d1454e336992\",\n" +
                "      \"subName\": \"Master of Biological Science\"\n" +
                "    },\n" +
                "    {\n" +
                "      \"subNo\": \"b21f3d9b-670b-4819-8faa-dbb2324f522a\",\n" +
                "      \"subName\": \"Bachelor of Criminology\"\n" +
                "    },\n" +
                "    {\n" +
                "      \"subNo\": \"635c0a8a-3701-43f1-a1d6-d359465df3c6\",\n" +
                "      \"subName\": \"Master of Teaching\"\n" +
                "    },\n" +
                "    {\n" +
                "      \"subNo\": \"2aa9e2d1-5f0e-41c6-8bf5-970b08d47b67\",\n" +
                "      \"subName\": \"Associate Degree in Creative Arts\"\n" +
                "    }\n" +
                "  ]";


        CalciteUtil instance = CalciteUtil.getInstance();

        SchemaPlus db1 = instance.createDatabase("db1");

        instance.createTable(db1, "tab1", subject);

        String sql = "";
        sql = """
                select "db1"."tab1"."subName" from "db1"."tab1" where "db1"."tab1"."subNo" = '635c0a8a-3701-43f1-a1d6-d359465df3c6'
                
                """;
        // 执行sql
        Object o = instance.doExecute(sql);

        System.out.println(o);
        // 关闭资源
        instance.closeResource();
    }
}