100 请教： 用JAVA 如何实现对 DB2 CLOB 的插入，修改，查询操作？

[tanguangbin]

用JAVA 如何实现对 DB2 CLOB 的插入，修改，查询操作？如果有HIBERNATE 操作 DB2 CLOB 的例子，加分。。。。

[子陌红尘]

如果你一开始就定义返回结果集的游标，只要在最后返回时OPEN一下那个游标就行了；
如果你在最后定义游标，那把游标定义和打开这部分用begin 和end;包含起来

[子陌红尘]

汗ing，回错帖子了，:(

[heislazy]

//insert
stmt = con.prepareStatement("INSERT INTO BOOKCOVERS VALUES(?,?)");
File imageFile = new File("c:\\\\test.jpg");
InputStream inputStream = new FileInputStream(imageFile);
stmt.setString(1,"0738425826");
stmt.setBinaryStream(2,inputStream,(int)(imageFile.length()));
stmt.executeUpdate();

//select
stmt = con.prepareStatement("SELECT BOOKCOVER FROM BOOKCOVERS WHERE BOOKISBN=?");
stmt.setString(1, "0738425826");
ResultSet resultSet = stmt.executeQuery();
while (resultSet.next()) {
// materialization of the Blob
Blob blob = resultSet.getBlob(1);
InputStream inputStream = blob.getBinaryStream();
File fileOutput = new File("e:\\songshe\\clonetest.jpg");
FileOutputStream fo = new FileOutputStream(fileOutput);
int c;
while ((c = inputStream.read()) != -1)
fo.write(c);
fo.close();
System.out.println("Blob retrieved");
}

以前有人问过

[tanguangbin]

老大我问的是CLOB 不是BLOB

[wuguix]

我正在研究这个，但是查出来的结果是乱吗
录入
ByteArrayInputStream bai1 = new ByteArrayInputStream(tem.getBytes());
ps.setAsciiStream(i+1,bai1,bai1.available());
取

InputStream as =rs.getAsciiStream("body");
byte[] bt = new byte[as.available()];
int f=0;
while((f=as.read(bt))!=-1){
}
out.println(new String(bt));

[野路子技术宅]

用setCharacterStream(position, reader, len);
===========================
//excute
preparedStatement ps = ..;
ps.set...
....
Clob c = ...;

setPsClob(ps, c, bean.getPosition());
..
}
===================================
public void setPsClob(PreparedStatement ps, Clob c,int position) {

StringBuffer buf = new StringBuffer();
Reader reader = null;
int len = 0;
ClobConvertBean cb = new ClobConvertBean();
cb = this.getClobConvertBean(c);
reader = cb.getReader();
len = cb.getLength();
try {
ps.setCharacterStream(position, reader, len);
} catch (SQLException e) {
e.printStackTrace();
}
}

public ClobConvertBean getClobConvertBean(Clob c) {
ClobConvertBean bean = new ClobConvertBean();
StringBuffer buf = new StringBuffer();
BufferedReader br = null;
StringReader sr = null;
boolean clobNotNull = false;
try {
if (c != null) {
br = new BufferedReader(c.getCharacterStream());
clobNotNull = true;
String line = null;
try {
while ((line = br.readLine()) != null) {
buf.append(line);
}
} catch (IOException e) {
e.printStackTrace();
}
}
} catch (SQLException e) {
e.printStackTrace();
}
if (clobNotNull && buf.toString().trim().length() > 0) {
StringReader bf = new StringReader(buf.toString());
bean.setReader(bf);
bean.setLength(buf.length());
} else {
StringReader bf = new StringReader(" ");
bean.setReader(bf);
bean.setLength(1);
}

return bean;

}
===========================
public class ClobConvertBean {
private Reader reader;
private int length = 0;
public int getLength() {
return length;
}
public void setLength(int length) {
this.length = length;
}
public Reader getReader() {
return reader;
}
public void setReader(Reader reader) {
this.reader = reader;
}


}

[zhengcg]

没有用过，不过用接口 java.sql.Clob 应该完全能够解决问题吧。
通过 getClob 进行查询，通过 setClob 进行插入、修改。下面是JDK提供的例子：
Clob notes = rs.getClob("NOTES");
java.io.InputStream in = notes.getAsciiStream();
java.io.Reader reader = notes.getCharacterStream();
String substring = notes.getSubString(10, 5);


Clob notes = rs.getClob("NOTES");
PreparedStatement pstmt = con.prepareStatement(
"UPDATE SALES_STATS SET COMMENTS = ? WHERE SALES > 500000");
pstmt.setClob(1, notes);
pstmt.executeUpdate();