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

//package com.sundata.internalevaluation.calc.util;
//
//import com.fasterxml.jackson.core.JsonProcessingException;
//import com.fasterxml.jackson.databind.JsonNode;
//import com.fasterxml.jackson.databind.ObjectMapper;
//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-18 11:44:07
// * @description: json转虚拟表
// */
//public class JsonToCalciteUtil {
//
//    private Connection connection = null;
//
//    private Statement statement = null;
//
//    private ResultSet resultSet = null;
//
//    public ResultSet jsonToResultSet(String jsonStr, String sql) throws SQLException, JsonProcessingException {
//        // 1. 使用 Jackson 解析 JSON 数据
//        ObjectMapper objectMapper = new ObjectMapper();
//        JsonNode rootNode = objectMapper.readTree(jsonStr);
//
//        //2. 创建 Calcite Schema
//        connection = DriverManager.getConnection("jdbc:calcite:");
//        CalciteConnection calciteConnection = connection.unwrap(CalciteConnection.class);
//
//
//        String json01 = "[\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" +
//                "  ]";
//
//
//        String json02 = "[\n" +
//                "    {\n" +
//                "      \"stuNo\": \"2e482de1-c984-4447-a968-ffc2f9cd4a2a\",\n" +
//                "      \"subNo\": \"07cc9eca-4e66-4d1c-9dd1-fe4671c2e18b\",\n" +
//                "      \"score\": 70.82\n" +
//                "    },\n" +
//                "    {\n" +
//                "      \"stuNo\": \"2e482de1-c984-4447-a968-ffc2f9cd4a2a\",\n" +
//                "      \"subNo\": \"cda7b717-bbff-40fd-af90-7e22dcea8457\",\n" +
//                "      \"score\": 24.71\n" +
//                "    },\n" +
//                "    {\n" +
//                "      \"stuNo\": \"2e482de1-c984-4447-a968-ffc2f9cd4a2a\",\n" +
//                "      \"subNo\": \"9cf8534b-37c6-4365-8453-d1454e336992\",\n" +
//                "      \"score\": 44.05\n" +
//                "    },\n" +
//                "    {\n" +
//                "      \"stuNo\": \"2e482de1-c984-4447-a968-ffc2f9cd4a2a\",\n" +
//                "      \"subNo\": \"b21f3d9b-670b-4819-8faa-dbb2324f522a\",\n" +
//                "      \"score\": 78.65\n" +
//                "    },\n" +
//                "    {\n" +
//                "      \"stuNo\": \"2e482de1-c984-4447-a968-ffc2f9cd4a2a\",\n" +
//                "      \"subNo\": \"635c0a8a-3701-43f1-a1d6-d359465df3c6\",\n" +
//                "      \"score\": 26.77\n" +
//                "    },\n" +
//                "    {\n" +
//                "      \"stuNo\": \"2e482de1-c984-4447-a968-ffc2f9cd4a2a\",\n" +
//                "      \"subNo\": \"2aa9e2d1-5f0e-41c6-8bf5-970b08d47b67\",\n" +
//                "      \"score\": 3.0\n" +
//                "    }\n" +
//                "  ]";
//
//
//        SchemaPlus rootSchema = calciteConnection.getRootSchema();
//        SchemaPlus credite = rootSchema.add("credite", new AbstractSchema());
//
//
//        SchemaPlus subjectSc = rootSchema.add("subjectSc", new AbstractSchema());
//        subjectSc.add("subject", new JsonScannableTable(Sources.of(json01)));
//        SchemaPlus scoresSc = rootSchema.add("scoresSc", new AbstractSchema());
//        scoresSc.add("scores", new JsonScannableTable(Sources.of(json02)));
//
//
//        // 注册 JSON 数据为表
//        rootNode.fields().forEachRemaining(entry -> {
//            String tableName = entry.getKey();
//            JsonNode tableData = entry.getValue();
//            rootSchema.add(tableName, new JsonScannableTable(Sources.of(tableData.toString())));
//        });
//
//        sql =
//                """
//                        select "subjectSc"."subject"."subName","scoresSc"."scores"."score" from "subjectSc"."subject" inner join "scoresSc"."scores" on "subjectSc"."subject"."subNo" = "scoresSc"."scores"."subNo"
//                        """;
//
//
//        // 4. 执行 SQL 查询
//        statement = connection.createStatement();
//        resultSet = statement.executeQuery(sql);
//        while (resultSet.next()) {
//            System.out.printf("Order ID: %s, Amount: %.2f\n",
//                    resultSet.getString(1),
//                    resultSet.getDouble(2));
//
//        }
//        return resultSet;
//    }
//
//    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, JsonProcessingException {
//        JsonToCalciteUtil jsonToCalciteUtil = new JsonToCalciteUtil();
//
//        String json1 = "{\n" +
//                "  \"student\": [\n" +
//                "    {\n" +
//                "      \"stuNo\": \"2e482de1-c984-4447-a968-ffc2f9cd4a2a\",\n" +
//                "      \"syuName\": \"阎彬\",\n" +
//                "      \"stuSex\": \"男\",\n" +
//                "      \"stuAge\": 26,\n" +
//                "      \"stuClass\": \"1\"\n" +
//                "    }\n" +
//                "  ],\n" +
//                "  \"scores\": [\n" +
//                "    {\n" +
//                "      \"stuNo\": \"2e482de1-c984-4447-a968-ffc2f9cd4a2a\",\n" +
//                "      \"subNo\": \"07cc9eca-4e66-4d1c-9dd1-fe4671c2e18b\",\n" +
//                "      \"score\": 70.82\n" +
//                "    },\n" +
//                "    {\n" +
//                "      \"stuNo\": \"2e482de1-c984-4447-a968-ffc2f9cd4a2a\",\n" +
//                "      \"subNo\": \"cda7b717-bbff-40fd-af90-7e22dcea8457\",\n" +
//                "      \"score\": 24.71\n" +
//                "    },\n" +
//                "    {\n" +
//                "      \"stuNo\": \"2e482de1-c984-4447-a968-ffc2f9cd4a2a\",\n" +
//                "      \"subNo\": \"9cf8534b-37c6-4365-8453-d1454e336992\",\n" +
//                "      \"score\": 44.05\n" +
//                "    },\n" +
//                "    {\n" +
//                "      \"stuNo\": \"2e482de1-c984-4447-a968-ffc2f9cd4a2a\",\n" +
//                "      \"subNo\": \"b21f3d9b-670b-4819-8faa-dbb2324f522a\",\n" +
//                "      \"score\": 78.65\n" +
//                "    },\n" +
//                "    {\n" +
//                "      \"stuNo\": \"2e482de1-c984-4447-a968-ffc2f9cd4a2a\",\n" +
//                "      \"subNo\": \"635c0a8a-3701-43f1-a1d6-d359465df3c6\",\n" +
//                "      \"score\": 26.77\n" +
//                "    },\n" +
//                "    {\n" +
//                "      \"stuNo\": \"2e482de1-c984-4447-a968-ffc2f9cd4a2a\",\n" +
//                "      \"subNo\": \"2aa9e2d1-5f0e-41c6-8bf5-970b08d47b67\",\n" +
//                "      \"score\": 3.0\n" +
//                "    }\n" +
//                "  ],\n" +
//                "  \"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" +
//                "  ]\n" +
//                "}";
//
//        ResultSet resultSet1 = jsonToCalciteUtil.jsonToResultSet(json1, "");
//
//
//    }
//
//}