1. 插入方法,首先需要先插入一个空的CLOB对象,之后查询出该条对象,并进行更新操作:
@Override
public void add(Object obj) throws DAOException {
Connection conn = null;
PreparedStatement pst = null;
try {
conn = getJdbcTemplate().getDataSource().getConnection();
conn.setAutoCommit(false);
//获得一个主键
String seqSql ="select cms.SEQ_TERMSERVICE_ID.nextval from dual";
Long id = this.getJdbcTemplate().queryForLong(seqSql);
//执行插入操作
Statement st = conn.createStatement();
String sql1 = "insert into cms.s_termservice(TERMSERVICE_ID,CREATETIME,UPDATETIME,CONTENT) values(" + id + ",sysdate,sysdate,empty_clob())";
st.executeUpdate(sql1);
//从数据库中重新获取CLOB对象写入数据
String sql2 = "select CONTENT from cms.s_termservice where TERMSERVICE_ID=" + id + " for update";
//锁定数据行进行更新,注意“for update”语句
ResultSet rs = st.executeQuery(sql2);
while(rs.next()) {
CLOB clob = (CLOB)rs.getClob("CONTENT");
clob.putString(1, ((TermService)obj).getContent());
String sql3 = "update cms.s_termservice set CONTENT=? where TERMSERVICE_ID=" + id;
pst = conn.prepareStatement(sql3);
pst.setClob(1, clob);
pst.execute();
}
//最后一步自己提交
conn.commit();
conn.setAutoCommit(true);
} catch (SQLException e) {
logger.error("in TermserviceDaoImpl,add fail, e is " + e.getMessage());
} finally {
try {
if(conn != null) {
conn.close();
}
if(pst != null) {
pst.close();
}
} catch (SQLException e1) {
logger.error("in TermserviceDaoImpl,add fail, e1 is " + e1.getMessage());
}
}
}
2. 更新操作,我个人是先置空CLOB字段,在从新更新该字段,如果没这样做,在更新时,如果更新数据比数据库中的数据短时,并不能完全更新,本人是这样操作的
以下是代码:
@Override
public void update(Object obj) throws DAOException {
Connection conn = null;
PreparedStatement pst = null;
try {
conn = getJdbcTemplate().getDataSource().getConnection();
conn.setAutoCommit(false);
Statement st = conn.createStatement();
int id = ((TermService)obj).getTermserviceId();
//从数据库中重新获取CLOB对象写入数据
String sql1 = "update cms.s_termservice s set s.CONTENT=' ' where TERMSERVICE_ID=" + id; //这边需要设置一个空的字段,后面就不会出现空指针
st.execute(sql1);
String sql2 = "select CONTENT from cms.s_termservice s where s.TERMSERVICE_ID=" + id;
//锁定数据行进行更新,注意“for update”语句
ResultSet rs = st.executeQuery(sql2);
while(rs.next()) {
CLOB clob = (CLOB)rs.getClob("CONTENT");
if(clob != null) {
clob.putString(1, ((TermService)obj).getContent());
String sql3 = "update cms.s_termservice set CONTENT=?,UPDATETIME=sysdate where TERMSERVICE_ID=" + id;
pst = conn.prepareStatement(sql3);
pst.setClob(1, clob);
pst.execute();
}
}
//最后一步自己提交
conn.commit();
conn.setAutoCommit(true);
} catch (SQLException e) {
logger.error("in TermserviceDaoImpl,add fail, e is " + e.getMessage());
} finally {
try {
if(conn != null) {
conn.close();
}
if(pst != null) {
pst.close();
}
} catch (SQLException e1) {
logger.error("in TermserviceDaoImpl,add fail, e1 is " + e1.getMessage());
}
}
}
3. 获取CLOB,并进行STRING的转化
private RowMapper showTSMapper = new RowMapper() {
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
TermService termService = new TermService();
termService.setTermserviceId(rs.getInt("TERMSERVICE_ID"));
termService.setCreatetime(rs.getDate("CREATETIME"));
termService.setUpdatetime(rs.getDate("UPDATETIME"));
StringBuffer sb = new StringBuffer();
try {
CLOB clob = (CLOB)rs.getClob("CONTENT");
if(clob != null) {
Reader is = clob.getCharacterStream();
BufferedReader br = new BufferedReader(is);
String s = br.readLine();
while (s != null) {
sb.append(s);
s = br.readLine();
}
}
if(sb.length()>10) {
termService.setContent(sb.substring(0,10) + ".......");
} else {
termService.setContent(sb.toString());
}
} catch (IOException e) {
logger.error("in TermserviceDaoImpl.showTSMapper fail, e is " + e.getMessage());
}
return termService;
}
};
private RowMapper modifyMapper = new RowMapper() {
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
TermService termService = new TermService();
termService.setTermserviceId(rs.getInt("TERMSERVICE_ID"));
termService.setCreatetime(rs.getDate("CREATETIME"));
termService.setUpdatetime(rs.getDate("UPDATETIME"));
StringBuffer sb = new StringBuffer();
try {
CLOB clob = (CLOB)rs.getClob("CONTENT");
if(clob != null) {
Reader is = clob.getCharacterStream();
BufferedReader br = new BufferedReader(is);
String s = br.readLine();
while (s != null) {
sb.append(s);
s = br.readLine();
}
}
termService.setContent(sb.toString());
} catch (IOException e) {
logger.error("in TermserviceDaoImpl.tsMapper fail, e is " + e.getMessage());
}
return termService;
}
};