JDBC操作DB2 Clob、Blob字段的Bug探究

JDBC操作DB2 Clob、Blob字段的Bug探究

 

J2EE的开发之路布满了陷阱，相信很多人都深有体会。

 

IBM的东西就是傻大笨粗，巨难用！！！

最近再用JDBC操作DB2的Clob和Blob时候出现了些问题：

我用的是DB2 V9安装程序自带的驱动，相传DB2驱动很多，这个最常用，也是最规范一个。

 

我用的驱动名字如下：

db2jcc.jar
db2jcc_license_cisuz.jar
db2jcc_license_cu.jar

 

Clob出现的问题：写入Clob没有问题，读取出问题。

Blob出现的问题：都写通常情况下没有问题。但当同时写入Data和Blob字段时候，会出问题。（一个例子中遇到的，不足以给出肯定结果）。

 

抛出的异常代码如下：
com.ibm.db2.jcc.c.SqlException
 at com.ibm.db2.jcc.c.ec.<init>(ec.java:183)
 at com.ibm.db2.jcc.b.d.b(d.java:1328)
 at com.ibm.db2.jcc.c.s.a(s.java:748)
 at com.ibm.db2.jcc.c.s.U(s.java:1393)
 at com.ibm.db2.jcc.c.wf.getClob(wf.java:914)
 ......

 

为测试搭建环境：

CREATE TABLE ZFZVF.T_LOB (
   NAME VARCHAR(24),
  TXT CLOB(2M) LOGGED NOT COMPACT,
  IMG BLOB(2M) LOGGED NOT COMPACT
);

 

DROP TABLE ZFZVF.NJ_GT;
COMMIT;
CREATE TABLE ZFZVF.NJ_GT (
  ID BIGINT,
  ZTBS BIGINT,
  NJBS BIGINT,
  ND VARCHAR(4),
  NJNR BLOB(2M) LOGGED NOT COMPACT,
  SJC TIMESTAMP
);
COMMIT;
COMMENT ON TABLE ZFZVF.NJ_GT IS '年检_个体';
COMMENT ON ZFZVF.NJ_GT (
  ID IS 'ID',
ZTBS IS '主体BS',
NJBS IS '年检BS',
ND IS '年度',
NJNR IS '年检内容',
SJC IS '时间戳' );
COMMIT;

例子如下：

package lob;  

import java.io.*;  
import java.sql.*;  

/**  
* DB2 Clob、Blob Bug探究  
* File: TestLob4DB2.java  
* User: leizhimin  
* Date: 2008-3-3 8:56:00  
*/  
public class TestLob4DB2{  
     public static final String url = "jdbc:db2://127.0.0.1:50000/zfzvf";  
    public static final String username = "zfzvf";  
    public static final String password = "zfzvfdb2";  
    public static final String driverClassName = "com.ibm.db2.jcc.DB2Driver";  

    /**  
     * 获取数据库连接Connection  
     *  
     * @return 数据库连接Connection  
     */  
    public static Connection makeConnection() {  
        Connection conn = null;  
        try {  
            Class.forName(driverClassName);  
        } catch (ClassNotFoundException e) {  
            e.printStackTrace();  
        }  
        try {  
            conn = DriverManager.getConnection(url, username, password);  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
        return conn;  
    }  

    /**  
     * 测试连接  
     */  
    public static void testConnection() {  
        Connection conn = makeConnection();  
        try {  
            Statement stmt = conn.createStatement();  
            ResultSet rs = stmt.executeQuery("SELECT * FROM ZFZVF.DM_HYML");  
            while (rs.next()) {  
                String s1 = rs.getString(1);  
                String s2 = rs.getString(2);  
                System.out.println(s1 + s2);  
            }  
            rs.close();  
            stmt.close();  
        } catch (SQLException e) {  
            e.printStackTrace();  
        } finally {  
            try {  
                conn.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
        }  
    }  

    /**  
     * 插入文件测试  
     */  
    public static void testInsertlob() {  
        Connection conn = makeConnection();  
        try {  
            conn.setAutoCommit(false);  
            File txtFile = new File("C://txt.txt");  
            File imgFile = new File("C://img.png");  
            int txt_len = (int) txtFile.length();  
            int img_len = (int) imgFile.length();  
            try {  
                InputStream fis1 = new FileInputStream(txtFile);  
                InputStream fis2 = new FileInputStream(imgFile);  
                PreparedStatement pstmt = conn.prepareStatement("INSERT INTO ZFZVF.T_LOB(NAME,TXT,IMG) VALUES('G',?,?)");  
                pstmt.setAsciiStream(1, fis1, txt_len);  
                pstmt.setBinaryStream(2, fis2, img_len);  
                pstmt.executeUpdate();  
                conn.commit();  
            } catch (FileNotFoundException e) {  
                e.printStackTrace();  
            }  
        } catch (SQLException e) {  
            e.printStackTrace();  
        } finally {  
            try {  
                conn.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
        }  
    }  

    /**  
     * 查询DB2 Clob测试 ,会抛出异常  
     */  
    public static void testQueryLob() {  
        Connection conn = makeConnection();  
        try {  
            conn.setAutoCommit(true);  
            PreparedStatement stmt = conn.prepareStatement("SELECT TXT,IMG FROM ZFZVF.T_LOB");  
            ResultSet rs = stmt.executeQuery();  
            while (rs.next()) {  
                Clob clob = rs.getClob("TXT");  
                Blob blob = rs.getBlob("IMG");  
                InputStreamReader ir = (InputStreamReader) clob.getCharacterStream();  
                File fileOutput = new File("C://txt_1.txt");  
                FileOutputStream fo = new FileOutputStream(fileOutput);  
                int c;  
                while ((c = ir.read()) != -1) {  
                    fo.write(c);  
                    break;  
                }  
                fo.close();  
            }  
        } catch (SQLException e) {  
            e.printStackTrace();  
        } catch (IOException e) {  
            e.printStackTrace();  
        } finally {  
            try {  
                conn.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
        }  
    }  

    /**  
     * 查询DB2 Blob测试  
     */  
    public static void testBlobQuery() {  
        Connection conn = makeConnection();  
        try {  
            conn.setAutoCommit(true);  
            PreparedStatement stmt = conn.prepareStatement("SELECT img FROM ZFZVF.T_LOB");  
            ResultSet rs = stmt.executeQuery();  
            int i = 0;  
            while (rs.next()) {  
                Blob blob = rs.getBlob(1);  
                InputStream inputStream = blob.getBinaryStream();  
                File fileOutput = new File("c://str_x" + i + ".txt");  
                FileOutputStream fo = new FileOutputStream(fileOutput);  
                int c;  
                while ((c = inputStream.read()) != -1)  
                    fo.write(c);  
                fo.close();  
                System.out.println("Blob " + i + "  retrieved!!");  
                i++;  
            }  
        } catch (SQLException e) {  

        } catch (FileNotFoundException e) {  
            e.printStackTrace();  
        } catch (IOException e) {  
            e.printStackTrace();  
        } finally {  
            try {  
                conn.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
        }  
    }  

    /**  
     * 查询DB2 Clob测试 ,会抛出异常  
     */  
    public static void testClobQuery() {  
        Connection conn = makeConnection();  
        try {  
            conn.setAutoCommit(false);  
            PreparedStatement stmt = conn.prepareStatement("SELECT TXT FROM ZFZVF.T_LOB");  
            ResultSet rs = stmt.executeQuery();  
            conn.commit();  
            while (rs.next()) {  
                Clob clob = rs.getClob(1);  
                InputStream is = clob.getAsciiStream();  
                File fileOutput = new File("C://ttttt.txt");  
                FileOutputStream fo = new FileOutputStream(fileOutput);  
                int c;  
                while ((c = is.read()) != -1)  
                    fo.write(c);  
                fo.close();  
                break;  
            }  
        } catch (SQLException e) {  
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.  
        } catch (FileNotFoundException e) {  
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.  
        } catch (IOException e) {  
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.  
        } finally {  
            try {  
                conn.close();  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }  
        }  

    }  

    public static void main(String args[]) {  
        testConnection();  
        testInsertlob();  
//        testQueryLob();  
        testBlobQuery();  
       testClobQuery();  

    }  
}

 

一一运行各个测试方法，可以看到操作Clob时候抛出了异常。

 

由于DB2驱动比较多，而一般都用测试用的这个驱动做开发，因此我也没有尝试用别的驱动了。对于这个问题，我自己用Blob来存储本应用Clob来处理的字段，也就是将原Clob定义为Blob，通过Blob来操作。具体代码就不赘述了。

 

由于大量使用Blob，又从中发现个问题，就是日期和Blob同时插入时会出一些问题。有兴趣的博友可以尝试尝试，最近很忙，也没有那么精力去细究。希望IBM能尽快解决这些问题。