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

指尖的阳光
559 阅读3分钟
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能尽快解决这些问题。


avatar
文章
阅读
粉丝