在Java中高效转换DB2 BLOB数据为JSON字符串的教程

// 示例：原始Java尝试转换BLOB
Blob pay1 = res.getBlob("COLUMN_NAME");
InputStream in = pay1.getBinaryStream();
Reader reader1 = new InputStreamReader(in, StandardCharsets.UTF_8);
JsonReader reader2 = new JsonReader(reader1);
reader2.setLenient(true); // 尝试宽松模式解析
JsonElement out = JsonParser.parseReader(reader2);
System.out.println(out);

SELECT SYSTOOLS.BSON2JSON(COLUMN_NAME) FROM TABLE_TEST;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class Db2BlobToJsonConverter {

    private static final String JDBC_CLASS_NAME = "com.ibm.db2.jcc.DB2Driver"; // DB2 JDBC驱动类名
    private static final String DB_URL = "jdbc:db2://your_db2_host:port/your_database"; // DB2连接URL
    private static final String DB_USER = "your_username"; // 数据库用户名
    private static final String DB_PASSWORD = "your_password"; // 数据库密码

    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement pstate = null;
        ResultSet res = null;

        try {
            // 1. 加载DB2 JDBC驱动
            Class.forName(JDBC_CLASS_NAME);
            System.out.println("DB2 driver loaded successfully.");

            // 2. 建立数据库连接
            Properties props = new Properties();
            props.setProperty("user", DB_USER);
            props.setProperty("password", DB_PASSWORD);
            // 根据需要添加其他连接属性
            // props.setProperty("useJDBC4ColumnNameAndLabelSemantics", "2"); 

            connection = DriverManager.getConnection(DB_URL, props);
            System.out.println("Connected to DB2 successfully.");

            // 3. 构建SQL查询，使用SYSTOOLS.BSON2JSON()函数
            String sqlQuery = "SELECT SYSTOOLS.BSON2JSON(COLUMN_NAME) AS JSON_DATA FROM TABLE_TEST";
            pstate = connection.prepareStatement(sqlQuery);

            // 4. 执行查询
            res = pstate.executeQuery();

            // 5. 处理结果集
            while (res.next()) {
                // 直接获取已转换为JSON的字符串
                String jsonData = res.getString("JSON_DATA"); 
                System.out.println("Converted JSON Data: " + jsonData);

                // 如果需要，可以使用任何JSON库进一步解析此字符串
                // JsonElement jsonElement = JsonParser.parseString(jsonData);
                // System.out.println("Parsed JsonElement: " + jsonElement);
            }

        } catch (ClassNotFoundException e) {
            System.err.println("DB2 JDBC Driver not found: " + e.getMessage());
            e.printStackTrace();
        } catch (SQLException e) {
            System.err.println("Database access error occurred: " + e.getMessage());
            e.printStackTrace();
        } finally {
            // 6. 关闭资源
            try {
                if (res != null) res.close();
                if (pstate != null) pstate.close();
                if (connection != null) connection.close();
                System.out.println("Database resources closed.");
            } catch (SQLException e) {
                System.err.println("Error closing database resources: " + e.getMessage());
            }
        }
    }
}