悩みがちなLOBデータの挿入手順メモ

JDBCで表にデータを格納するとき、INT型とかVARCHAR型とかなら自信を持って「こうやるんだ」と言えるんだけど、LOBデータ(CLOB、BLOB、XML)となると途端に「え、えと‥」となってしまう自分がいる。単なるcharacter stream、byte streamなんだけどね。
そんなわけで、可能なやり方を一通りメモ。
とりあえず、確認したDBMSは次のものだけ。

IBM DB2 Express-C v9.1.2 (Windows Vista Business)
IBM DB2 Express-C v9.5.2 (Windows Vista Business)
IBM DB2 Express-C v9.7.0 (Windows Vista Business)

Javaのバージョンは次のとおり。

JDK 5.0 (JDBC 3.0)

確認に使用したテーブルの定義は次のとおり。

CREATE TABLE LobTest(
    id BIGINT NOT NULL PRIMARY KEY,
    clobData CLOB,
    blobData BLOB,
    xmlData XML)

まず正当なやり方。

import java.io.ByteArrayInputStream;
import java.io.StringReader;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Main1 {

    private static long lastId = System.currentTimeMillis();
    private static synchronized long nextId() {
        long res = System.currentTimeMillis();
        if (res <= lastId) {
            return lastId++;
        } else {
            lastId = res;
            return res;
        }
    }

    /**
     * @param args
     */
    public static void main(String[] args) throws Exception {
        Class.forName("com.ibm.db2.jcc.DB2Driver");

        Connection conn = DriverManager.getConnection("jdbc:db2://localhost:50000/sandbox", "userId", "password");

        try {
            clob01(conn);
            clob02(conn);

            blob01(conn);
            blob02(conn);

            xml01(conn);
            xml02(conn);
            xml03(conn);
            xml04(conn);
        } finally {
            conn.close();
        }
    }

    /*
     * PreparedStatement.setString()を使ってCLOBを挿入。
     */
    private static void clob01(Connection conn) throws SQLException {
        String queryStr = "INSERT INTO LobTest(id, clobData) VALUES(?, ?)";
        PreparedStatement pstmt = conn.prepareStatement(queryStr);
        try {
            pstmt.setLong(1, nextId());
            String str = "Hello CLOB 世界";
            pstmt.setString(2, str);
            int updateCount = pstmt.executeUpdate();
            System.out.println("clob01: updateCount: " + updateCount);
        } finally {
            pstmt.close();
        }
    }
    /*
     * PreparedStatement.setCharacterStream()を使ってCLOBを挿入。
     */
    private static void clob02(Connection conn) throws SQLException {
        String queryStr = "INSERT INTO LobTest(id, clobData) VALUES(?, ?)";
        PreparedStatement pstmt = conn.prepareStatement(queryStr);
        try {
            pstmt.setLong(1, nextId());
            String str = "Hello CLOB 世界";
            StringReader reader = new StringReader(str);
            pstmt.setCharacterStream(2, reader, str.length());
            int updateCount = pstmt.executeUpdate();
            System.out.println("clob02: updateCount: " + updateCount);
        } finally {
            pstmt.close();
        }
    }

    /*
     * PreparedStatement.setBytes()を使ってBLOBを挿入。
     */
    private static void blob01(Connection conn) throws SQLException, UnsupportedEncodingException {
        String queryStr = "INSERT INTO LobTest(id, blobData) VALUES(?, ?)";
        PreparedStatement pstmt = conn.prepareStatement(queryStr);
        try {
            pstmt.setLong(1, nextId());
            byte[] b = "Hello BLOB 世界".getBytes("UTF-8");
            pstmt.setBytes(2, b);
            int updateCount = pstmt.executeUpdate();
            System.out.println("blob01: updateCount: " + updateCount);
        } finally {
            pstmt.close();
        }
    }
    /*
     * PreparedStatement.setBinaryStream()を使ってBLOBを挿入。
     */
    private static void blob02(Connection conn) throws SQLException, UnsupportedEncodingException {
        String queryStr = "INSERT INTO LobTest(id, blobData) VALUES(?, ?)";
        PreparedStatement pstmt = conn.prepareStatement(queryStr);
        try {
            pstmt.setLong(1, nextId());
            byte[] b = "Hello BLOB 世界".getBytes("UTF-8");
            ByteArrayInputStream in = new ByteArrayInputStream(b);
            pstmt.setBinaryStream(2, in, b.length);
            int updateCount = pstmt.executeUpdate();
            System.out.println("blob02: updateCount: " + updateCount);
        } finally {
            pstmt.close();
        }
    }

    /*
     * PreparedStatement.setString()を使ってXMLを挿入。
     */
    private static void xml01(Connection conn) throws SQLException {
        String queryStr = "INSERT INTO LobTest(id, xmlData) VALUES(?, ?)";
        PreparedStatement pstmt = conn.prepareStatement(queryStr);
        try {
            pstmt.setLong(1, nextId());
            String str = "<root>Hello XML 世界</root>";
            pstmt.setString(2, str);
            int updateCount = pstmt.executeUpdate();
            System.out.println("xml01:  updateCount: " + updateCount);
        } finally {
            pstmt.close();
        }
    }
    /*
     * PreparedStatement.setCharacterStream()を使ってXMLを挿入。
     */
    private static void xml02(Connection conn) throws SQLException {
        String queryStr = "INSERT INTO LobTest(id, xmlData) VALUES(?, ?)";
        PreparedStatement pstmt = conn.prepareStatement(queryStr);
        try {
            pstmt.setLong(1, nextId());
            String str = "<root>Hello XML 世界</root>";
            StringReader reader = new StringReader(str);
            pstmt.setCharacterStream(2, reader, str.length());
            int updateCount = pstmt.executeUpdate();
            System.out.println("xml02:  updateCount: " + updateCount);
        } finally {
            pstmt.close();
        }
    }
    /*
     * PreparedStatement.setBytes()を使ってXMLを挿入。
     */
    private static void xml03(Connection conn) throws SQLException, UnsupportedEncodingException {
        String queryStr = "INSERT INTO LobTest(id, xmlData) VALUES(?, ?)";
        PreparedStatement pstmt = conn.prepareStatement(queryStr);
        try {
            pstmt.setLong(1, nextId());
            byte[] b = "<root>Hello XML 世界</root>".getBytes("UTF-8");
            pstmt.setBytes(2, b);
            int updateCount = pstmt.executeUpdate();
            System.out.println("xml03:  updateCount: " + updateCount);
        } finally {
            pstmt.close();
        }
    }
    /*
     * PreparedStatement.setBinaryStream()を使ってXMLを挿入。
     */
    private static void xml04(Connection conn) throws SQLException, UnsupportedEncodingException {
        String queryStr = "INSERT INTO LobTest(id, xmlData) VALUES(?, ?)";
        PreparedStatement pstmt = conn.prepareStatement(queryStr);
        try {
            pstmt.setLong(1, nextId());
            byte[] b = "<root>Hello XML 世界</root>".getBytes("UTF-8");
            ByteArrayInputStream in = new ByteArrayInputStream(b);
            pstmt.setBinaryStream(2, in, b.length);
            int updateCount = pstmt.executeUpdate();
            System.out.println("xml04:  updateCount: " + updateCount);
        } finally {
            pstmt.close();
        }
    }

}

バイト配列が必要な場面では、何も考えずに getBytes("UTF-8") を呼び出しているが、XML型の場合はUTF-8固定なので、これ以外の文字エンコーディングを指定すると、データによっては解析エラーになる。BLOBの場合も、実際に格納されるデータの文字エンコーディングがどうなるのかという問題があるのだが、それはまた気が向いたときに(ぉ。

次は、JDBCドライバ依存となる邪道なやり方。

import java.io.UnsupportedEncodingException;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Main2 {

    private static long lastId = System.currentTimeMillis();
    private static synchronized long nextId() {
        long res = System.currentTimeMillis();
        if (res <= lastId) {
            return lastId++;
        } else {
            lastId = res;
            return res;
        }
    }

    /**
     * @param args
     */
    public static void main(String[] args) throws Exception {
        Class.forName("com.ibm.db2.jcc.DB2Driver");

        Connection conn = DriverManager.getConnection("jdbc:db2://localhost:50000/sandbox", "userId", "password");

        try {
            clob03(conn);

            blob03(conn);

            xml05(conn);
            xml06(conn);
        } finally {
            conn.close();
        }
    }

    /*
     * DB2 JDBCドライバのクラス(com.ibm.db2.jcc.t2zos.DB2LobFactory)を使ってCLOBを挿入。
     */
    private static void clob03(Connection conn) throws SQLException {
        String queryStr = "INSERT INTO LobTest(id, clobData) VALUES(?, ?)";
        PreparedStatement pstmt = conn.prepareStatement(queryStr);
        try {
            pstmt.setLong(1, nextId());
            String str = "Hello CLOB 世界";
            Clob clobData = com.ibm.db2.jcc.t2zos.DB2LobFactory.createClob(str);
            pstmt.setClob(2, clobData);
            int updateCount = pstmt.executeUpdate();
            System.out.println("clob03: updateCount: " + updateCount);
        } finally {
            pstmt.close();
        }
    }

    /*
     * DB2 JDBCドライバのクラス(com.ibm.db2.jcc.t2zos.DB2LobFactory)を使ってBLOBを挿入。
     */
    private static void blob03(Connection conn) throws SQLException, UnsupportedEncodingException {
        String queryStr = "INSERT INTO LobTest(id, blobData) VALUES(?, ?)";
        PreparedStatement pstmt = conn.prepareStatement(queryStr);
        try {
            pstmt.setLong(1, nextId());
            byte[] b = "Hello BLOB 世界".getBytes("UTF-8");
            Blob blobData = com.ibm.db2.jcc.t2zos.DB2LobFactory.createBlob(b);
            pstmt.setBlob(2, blobData);
            int updateCount = pstmt.executeUpdate();
            System.out.println("blob03: updateCount: " + updateCount);
        } finally {
            pstmt.close();
        }
    }

    /*
     * DB2 JDBCドライバのクラス(com.ibm.db2.jcc.t2zos.DB2LobFactory)を使ってXMLを挿入。
     */
    private static void xml05(Connection conn) throws SQLException {
        String queryStr = "INSERT INTO LobTest(id, xmlData) VALUES(?, ?)";
        PreparedStatement pstmt = conn.prepareStatement(queryStr);
        try {
            pstmt.setLong(1, nextId());
            String str = "<root>Hello XML 世界</root>";
            Clob clobData = com.ibm.db2.jcc.t2zos.DB2LobFactory.createClob(str);
            pstmt.setClob(2, clobData);
            int updateCount = pstmt.executeUpdate();
            System.out.println("xml05:  updateCount: " + updateCount);
        } finally {
            pstmt.close();
        }
    }
    /*
     * DB2 JDBCドライバのクラス(com.ibm.db2.jcc.t2zos.DB2LobFactory)を使ってXMLを挿入。
     */
    private static void xml06(Connection conn) throws SQLException, UnsupportedEncodingException {
        String queryStr = "INSERT INTO LobTest(id, xmlData) VALUES(?, ?)";
        PreparedStatement pstmt = conn.prepareStatement(queryStr);
        try {
            pstmt.setLong(1, nextId());
            byte[] b = "<root>Hello XML 世界</root>".getBytes("UTF-8");
            Blob blobData = com.ibm.db2.jcc.t2zos.DB2LobFactory.createBlob(b);
            pstmt.setBlob(2, blobData);
            int updateCount = pstmt.executeUpdate();
            System.out.println("xml06:  updateCount: " + updateCount);
        } finally {
            pstmt.close();
        }
    }

}

まぁ、普通は使わないよな。
‥っていうか、どこかでこれと似たコードを見たんだけど、なんでそんなやり方をするかなぁ‥

HHeLiBeXの日記正道編

日々の記憶の記録とメモ‥

悩みがちなLOBデータの挿入手順メモ