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);}}// 打印最终的 ObjectNodeSystem.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);}}}}}}