实体Test中clob类型字段定义如下:(部分代码)
private Clob resultStr;
@Lob
@Basic(fetch = FetchType.EAGER)
@Column(name = "RESULTSTR",columnDefinition="CLOB")
public Clob getResultStr() {
return resultStr;
}
@Lob
@Basic(fetch = FetchType.EAGER)
@Column(name = "RESULTSTR",columnDefinition="CLOB")
public void setResultStr(Clob resultStr) {
this.resultStr = resultStr;
}
TestManager.java中插入clob字段,代码示例如下:
public boolean doInsert(Test entity, String resultStr){
boolean result = true;
Connection conn = null;
String sql = "insert into TB_TEST(ID,TYPE,RESULTSTR) values (?, ?, empty_clob())";
//锁住该列,防止并发写入时候该字段同时被多次写入造成错误
String sqlClob = "select RESULTSTR from TB_TEST where id=? for update";
PreparedStatement pst =null;
ResultSet rs = null;
Writer writer = null;
try {
conn = DBUtil.getConnection();//获取数据库连接,此处为oracle数据库
conn.setAutoCommit(false);//设置不自动提交,开启事务
pst = conn.prepareStatement(sql);
pst.setLong(1, entity.getId());
pst.setString(2,entity.getType());
pst.executeUpdate();
pst= conn.prepareStatement(sqlClob);
pst.setLong(1, entity.getId());
rs = pst.executeQuery();
CLOB clob = null;
if(rs.next()){
try {
clob = (CLOB) rs.getClob(1);
writer = clob.getCharacterOutputStream(); //拿到clob的字符输入流
writer.write(resultStr);
writer.flush();
writer.close();
} catch (IOException e) {
e.printStackTrace();
}
}
conn.commit();
} catch (SQLException e) {
result = false;
try {
conn.rollback();//当commit或者rollback后会自动释放该列的锁定
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.setAutoCommit(true);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}//还原
DBUtil.closeConnection(conn, pst, rs);
}
return result;
}
TestController.java中相关代码如下:
Test test = new Test();
long id = testManager.getSeq();
test.setId(id);
test.setType(type);
boolean reFlag = testManager.doInsert(test, result);
其中getSeq()方法的作用是获取表格序列生成的ID,该方法在TestManager中定义,参考代码如下:
public long getSeq(){
Connection conn = null;
String sql = "select SEQ_TB_TEST.nextval as id from dual";
PreparedStatement pst =null;
ResultSet rs = null;
long id = 0;
try {
conn = DBUtil.getConnection();
pst= conn.prepareStatement(sql);
rs = pst.executeQuery();
if(rs.next()){
id = rs.getLong(1);
}
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
conn.setAutoCommit(true);
} catch (SQLException e) {
}
//还原
DBUtil.closeConnection(conn, pst, rs);
}
return id;
}