1)clob类型的数据不能直接insert,要先通过empty_clob()方法给它分配一个locator(同理,blob的用empty_blob()函数分配locator).然后把它select出来(此时它当然没有数据,但结果集不是空的),得到一个Clob的对象,修改该对象的内容让它满足我们的需要,再通过update方法更新该行记录.
2) 通过select修改含lob类型的记录时一定要锁定该行(通过for update关键字实现),否则oracle会报错.
3) 刚插入的记录就select for update, 会出现"违反读取顺序"错误,解决办法是将自动提交功能置为false,即不允许自动提交,然后commit它,再select,就可以了！
下例是插入clob
public boolean addCase(Case newCase){
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
conn=JDBCUtil.getInstance().getConnection();
conn.setAutoCommit(false);
String priSql="select seq_case.nextval from dual";
ps=conn.prepareStatement(priSql);
rs=ps.executeQuery();
Integer cid=null;
if(rs.next()){
cid=rs.getInt(1);
}
String sql="insert into case(cid,cname,caddr,cdescribe,ctime) values(?,?,?,empty_clob(),?)";
ps=conn.prepareStatement(sql);
ps.setInt(1, cid);
ps.setString(2, newCase.getCname());
ps.setString(3, newCase.getCaddr());
ps.setString(4, newCase.getCtime());
int insertResult=ps.executeUpdate();
mit();
ps.clearParameters();
if(insertResult>0){
String selUpdateSql="select cdescribe from case where cid=? for update";
ps=conn.prepareStatement(selUpdateSql);
ps.setInt(1, cid);
rs=ps.executeQuery();
if(rs.next()){
CLOB clob=(CLOB) rs.getClob(1);
clob.putString(1, newCase.getCdescribe());
String updateSql="update case set cdescribe=? where cid=?";
ps=conn.prepareStatement(updateSql);
ps.setClob(1, clob);
ps.setInt(2, cid);
ps.executeUpdate();
}
mit();
conn.setAutoCommit(true);
return true;
}
}catch(Exception e){
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JDBCUtil.closeAll(conn, null, rs, ps);
}
return false;
}
下例是插入blob
public void insertBlob(){
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
conn=JDBCUntil.getInstance().getConnection();
conn.setAutoCommit(false);
String sql1="select seq_facefea.nextval from dual";
ps=conn.prepareStatement(sql1);
rs=ps.executeQuery();
Integer fid = null;
if(rs.next()){
fid=rs.getInt(1);
System.out.println("查询出主键fid:"+fid);
}
String sql2="insert into blobtest(bid,bname) values(?,empty_blob())";
ps = conn.prepareStatement(sql2);
ps.setInt(1,fid);
int i=ps.executeUpdate();
System.out.println("执行了更新:"+i);
mit();
String sql3="select bname from blobtest where bid = ? for update";
ps = conn.prepareStatement(sql3);
ps.setInt(1,fid);
rs = ps.executeQuery();
if(rs.next()){
System.out.println("执行了查询");
BLOB b = (BLOB) rs.getBlob(1);
BufferedOutputStream os = new BufferedOutputStream(b.setBinaryStream(0));
os.write("test".getBytes());
os.flush();
os.close();
// ps=conn.prepareStatement("update blobtest set bname=? where bid=?"); //以下注释这几行可有可无保险起见最好加上
// ps.setBlob(1, b);
// ps.setInt(2, fid);
// ps.executeUpdate();
}
mit();
conn.setAutoCommit(true);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JDBCUntil.closeAll(conn, null, rs, ps);
}
}
