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

2008-03-07 18:03:29

　标签：DB2 Bug Clob、Blob　　　[推送到技术圈]

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能尽快解决这些问题。

本文出自 “熔岩” 博客，转载请与作者联系！

昵称：
验证码：		点击图片可刷新验证码　　博客过2级，无需填写验证码
内容：

熔 岩

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

熔岩