JDBC CLOB\BLOB操作

這幾天遇到個資料移轉大欄位的問題一直沒有時間解決，今天終於有時間解決這個問題。

情境

SQL Server資料庫某張表的資料移轉到Oracle資料庫，早期未考慮特殊欄位類型的問題，但真正業務應用測試時發現了問題，對應Oracle中該欄位內容為空白（遷移時忽略了）。需要我們依據主鍵將SQL Server表中的內容更新到Oracle上來，直接利用JDBC就可以處理此問題。

JDBC處理的一些總結

Oracle JDBC驅動程式有相容問題，有JDBC3\JDBC4之分

Oracle下對CLOB的操作是字元流的形式，在JDBC4之前插入帶有CLOB的資料需要分兩步

1. 使用EMPTY_CLOB()，先插入到資料庫這時該欄位值為空白

1. 插入完成後單獨對CLOB欄位進行更新操作

JDBC4之後Oracle11G對這塊的處理改善了很多

1. 使用connection對象建立Clob對象，將值寫入該Clob對象
2. 直接添加到PreparedStatement的parameter即可

SQL Server資料庫JDBC

1. 取TEXT/NTEXT類型欄位值，直接通過ResultSet#getString(index)即可

1. 取BINARY類型則需要通過位元組流的方式完成

範例程式碼

JDBC4.0對CLOB的插入

    /**     * 測試插入     * <p>1、注意Oracle的JDBC驅動為ojdbc6.jar     * @throws SQLException     */    public void insertDateNew() throws SQLException {        String sql = "INSERT INTO ".concat(tableName).concat(" (ID,CONTENT) VALUES(?,?)");        PreparedStatement pstmt = null;        try {            this.conn.setAutoCommit(false);            pstmt = this.conn.prepareStatement(sql);            for (int i = 1; i < 50; i++) {                pstmt.setString(1, String.valueOf(i));                Clob clob = this.conn.createClob();                clob.setString(1, "HelloWorld " + i);                pstmt.setClob(2, clob);                pstmt.addBatch();            }            pstmt.executeBatch();            this.conn.commit();        } catch (SQLException e) {            this.conn.rollback();            e.printStackTrace();        } finally {            this.conn.setAutoCommit(true);            DBUtils.close(pstmt);        }    }

 

JDBC3.0的插入

    /**     * 測試插入CLOB     */    public void insertData() throws SQLException {        String sql = "INSERT INTO ".concat(tableName).concat(" (ID,CONTENT) VALUES(?,EMPTY_CLOB())");        PreparedStatement pstmt = null;        try {            this.conn.setAutoCommit(false);            pstmt = this.conn.prepareStatement(sql);            for (int i = 1; i < 50; i++) {                pstmt.setString(1, String.valueOf(i));                pstmt.addBatch();            }            pstmt.executeBatch();            this.updateInsertCLOB();            this.conn.commit();        } catch (SQLException e) {            this.conn.rollback();            e.printStackTrace();        } finally {            this.conn.setAutoCommit(true);            DBUtils.close(pstmt);        }    }    /**     * 支援未實現JDBC4的情況     * @param pstmt {@link PreparedStatement}     * @throws SQLException     */    private void updateInsertCLOB() throws SQLException {        String sql = "SELECT ID,CONTENT FROM ".concat(tableName);        PreparedStatement pstmt = null;        ResultSet resultSet = null;        try {            pstmt = this.conn.prepareStatement(sql);            resultSet = pstmt.executeQuery(sql);            while (resultSet.next()) {                Clob clobInst = resultSet.getClob(2);                Writer writerInst = clobInst.setCharacterStream(1);                writerInst.write("HelloWorld " + resultSet.getString(1));                writerInst.close();            }        } catch (IOException e) {            throw new SQLException(e.getCause());        } finally {            DBUtils.close(pstmt);        }    }

 

位元組/字元流類型處理

    /**     * 測試查詢     * <p>     * 1、TEXT類型直接getString即可     * <p>     * 2、主要是BINARY類型的處理     * @throws SQLException     */    public void queryDate() throws SQLException {        String qrySql = "SELECT * FROM BLOB_TABLE A WHERE A.ID=?";        PreparedStatement pstmt = null;        try {            pstmt = this.conn.prepareStatement(qrySql);            pstmt.setString(1, "1");            ResultSet rs = pstmt.executeQuery();            while (rs.next()) {                InputStream ins = rs.getBinaryStream("PHOTO");                restoreInputStream(ins);                ins.close();            }        } catch (SQLException e) {            e.printStackTrace();        } catch (IOException e) {            // TODO Auto-generated catch block            e.printStackTrace();        } finally {            DBUtils.close(pstmt);        }    }    /**     * 轉儲輸入資料流     * @param ins {@link InputStream}     * @throws IOException IOException     */    private void restoreInputStream(InputStream ins) {        BufferedOutputStream bosInst = null;        byte[] buff = new byte[1024 * 1024];        try {            bosInst = new BufferedOutputStream(new FileOutputStream("C:\\ttt.zip"));            int flag = -1;            while ((flag = ins.read(buff)) != -1) {                bosInst.write(buff, 0, flag);            }            bosInst.flush();        } catch (FileNotFoundException e) {            e.printStackTrace();        } catch (IOException e) {            e.printStackTrace();        } finally {            if (bosInst != null) {                try {                    bosInst.close();                } catch (IOException e) {                    e.printStackTrace();                }            }        }    }