java 读取sq3所有表数据到objectNode

1.实现效果：将sq3中所有表的所有字段读到objectNode 对象中，兼容后期表字段增删情况，数据组织形式如下图所示：

代码截图：

代码如下：

package com.xxx.check.util;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ObjectNode;

public class SQLiteReader {

    public static void main(String[] args) {
        String url = "jdbc:sqlite:/Users/tt/Desktop/test_data/310099.sq3";
        ObjectMapper mapper = new ObjectMapper();
        ObjectNode rootNode = mapper.createObjectNode();

        try (Connection conn = DriverManager.getConnection(url)) {
            if (conn != null) {
                // 获取所有表名
                List<String> tableNames = getTableNames(conn);
                for (String tableName : tableNames) {
                    ObjectNode tableNode = mapper.createObjectNode();
                    // 将表的数据添加到 tableNode 中
                    addTableDataToNode(conn, tableName, tableNode);
                    // 将 tableNode 添加到根节点
                    rootNode.set(tableName, tableNode);
                }
            }
            // 打印最终的 ObjectNode
            System.out.println(mapper.writerWithDefaultPrettyPrinter().writeValueAsString(rootNode));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库中所有表的名称
     * @param conn 数据库连接
     * @return 表名列表
     * @throws SQLException SQL 异常
     */
    private static List<String> getTableNames(Connection conn) throws SQLException {
        List<String> tableNames = new ArrayList<>();
        DatabaseMetaData metaData = conn.getMetaData();
        ResultSet resultSet = metaData.getTables(null, null, null, new String[]{"TABLE"});
        while (resultSet.next()) {
            tableNames.add(resultSet.getString("TABLE_NAME"));
        }
        resultSet.close();
        return tableNames;
    }

    /**
     * 将指定表的数据添加到 ObjectNode 中
     * @param conn 数据库连接
     * @param tableName 表名
     * @param tableNode 存储表数据的 ObjectNode
     * @throws SQLException SQL 异常
     */
    private static void addTableDataToNode(Connection conn, String tableName, ObjectNode tableNode) throws SQLException {
        String sql = "SELECT * FROM " + tableName;
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            while (rs.next()) {
                // 假设 id 列是第一列
                String id = rs.getString(1);
                ObjectNode rowNode = tableNode.putObject(id);

                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i);
                    Object value = rs.getObject(i);
                    if (value != null) {
                        if (value instanceof String) {
                            rowNode.put(columnName, (String) value);
                        } else if (value instanceof Integer) {
                            rowNode.put(columnName, (Integer) value);
                        } else if (value instanceof Long) {
                            rowNode.put(columnName, (Long) value);
                        } else if (value instanceof Double) {
                            rowNode.put(columnName, (Double) value);
                        } else if (value instanceof Boolean) {
                            rowNode.put(columnName, (Boolean) value);
                        } else {
                            // 对于其他类型，转换为字符串存储
                            rowNode.put(columnName, value.toString());
                        }
                    } else {
                        rowNode.putNull(columnName);
                    }
                }
            }
        }
    }

}