Java 讀取 DB2 XML 資料完整實戰代碼 (JDBC + XQuery)

環境準備

• JDBC Driver: com.ibm.db2.jcc.DB2Driver (db2jcc.jar + db2jcc_license_cu.jar)
• 資料表結構：CREATE TABLE XMLTABLE (ID INT, CONTENT XML)
• 測試資料：INSERT INTO XMLTABLE VALUES (2, XMLPARSE(DOCUMENT '<root><book><name>Java</name></book></root>'))

完整 Java 類別實現

package X2R2D;

import java.sql.*;

public class DataBase {
    private Connection conn = null;
    private Statement stmt = null;

    public boolean openConn() throws Exception {
        try {
            Class.forName("com.ibm.db2.jcc.DB2Driver");
            String url = "jdbc:db2://<host>:50000/<db>";
            String user = "<user>";
            String password = "<pwd>";
            conn = DriverManager.getConnection(url, user, password);
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean openStatement() throws SQLException {
        stmt = conn.createStatement();
        return true;
    }

    public ResultSet getQuery(String sql) throws Exception {
        return stmt.executeQuery(sql);
    }

    public boolean closeConn() throws SQLException {
        if (conn != null) conn.close();
        return true;
    }

    public static void main(String[] args) throws Exception {
        DataBase db = new DataBase();
        db.openConn();
        db.openStatement();

        // XQuery: 擷取所有 book 的 name 文字節點
        String sql = "SELECT ID, " +
                     "XMLQUERY('for $e in $d/root/book return $e/name/text()' " +
                     "PASSING CONTENT AS \"d\") " +
                     "FROM XMLTABLE " +
                     "WHERE ID = 2";

        ResultSet rs = db.getQuery(sql);
        while (rs.next()) {
            System.out.println("ID: " + rs.getInt(1) + "  Name: " + rs.getString(2));
        }
        db.closeConn();
    }
}

關鍵技術點解析

1. XMLQUERY 函數：在 SQL 中嵌入 XQuery 表達式，PASSING 子句將 XML 欄位綁定給變數 $d。
2. XPath/XQuery 路徑：/root/book 定位節點，name/text() 取文字內容。
3. 結果集處理：XMLQUERY 回傳 XML 序列，JDBC getString() 會序列化為字串；若需結構化處理可用 getSQLXML() (JDBC 4.0+)。
4. 命名空間：若 XML 含命名空間，XQuery 需宣告 declare namespace ns="...";。

進階：XMLTABLE 關聯式投影

SELECT X.*
FROM XMLTABLE,
     XMLTABLE('for $b in $d/root/book return $b'
              PASSING CONTENT AS "d"
              COLUMNS
                BOOK_NAME VARCHAR(100) PATH 'name/text()',
                PRICE     DECIMAL(10,2)  PATH 'price/text()') AS X
WHERE ID = 2

將 XML 節點直接「炸開」為關聯式行集，方便後續 JOIN、聚合分析。