java db2 clob_JDBC操作DB2 Clob、Blob字段的Bug探究

"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();

}

}