应用Spring操作oracle中clob类型字段

今天要做个oracle中多clob类型字段操作的东西，到网上找了一下，参考很多。这里总结下：
1、 插入clob字段

private LobHandler lobHandler = new DefaultLobHandler();;
CommonsDbcpNativeJdbcExtractor nativeJdbcExtractor = = new CommonsDbcpNativeJdbcExtractor();
String sql = "insert into gg (test1,colb1) values ('',?)";
final String str = "" //Clob的值
OracleLobHandler handler = new OracleLobHandler();
handler.setNativeJdbcExtractor(this.nativeJdbcExtractor);
this.lobHandler = (LobHandler) handler;
jdbcTemplate.execute(sql, new AbstractLobCreatingPreparedStatementCallback(this.lobHandler){
protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException,
DataAccessException
{
lobCreator.setClobAsString(ps, 1 , str ); //设置详细的CLOB字段的内容
}
}
2、 修改clob字段
public void modifyXML(String sql,final String xml){
CommonsDbcpNativeJdbcExtractor nativeJdbcExtractor = new CommonsDbcpNativeJdbcExtractor();
final OracleLobHandler handler = new OracleLobHandler();
handler.setNativeJdbcExtractor(nativeJdbcExtractor);
RmProjectHelper.getCommonBsInstance().doUpdate(sql, new PreparedStatementSetter(){
public void setValues(PreparedStatement ps) throws SQLException {
handler.getLobCreator().setClobAsString(ps, 1, xml);
}
});

}

3、 取数据
String sql = "select clob1 from gg where id= ?"
jdbcTemplate.query(sql ,
new Object[] {new Integer(1 )},
new AbstractLobStreamingResultSetExtractor() {
protected void streamData(ResultSet rs) throws SQLException, IOException, DataAccessException {
lobHandler.getClobAsCharacterStream(rs, 1 ); //得到Clob，需要自己处理
}
});

或者：
public List getPublicArticle(String sql){
List articleList = RmProjectHelper.getCommonBsInstance().doQuery(sql,new RowMapper() {
public Object mapRow(ResultSet rs, int arg1) throws SQLException {
ResultSet r = rs;
String[] articleArray = new String[] { rs.getString("ID"),
ClobToString(rs.getClob("DATA_XML")),
rs.getString("PUBLISH_STATUS"),
rs.getString("TAGBUNDLE_ID"),
rs.getString("MARK_TYPE"),
rs.getString("COLUMN_NAME"),
rs.getString("LAST_OPERATOR"),
rs.getString("LAST_OPERATE_TIME")
};
return articleArray;
}
});
return articleList;
}

取出clob类型后需要自己转换成String：

public String ClobToString(java.sql.Clob clob){
String reString = "";
Reader is;
StringBuffer sb = new StringBuffer();
try {
is = clob.getCharacterStream();
BufferedReader br = new BufferedReader(is);
String s = br.readLine();
while (s != null) {// 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
sb.append(s);
s = br.readLine();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
reString = sb.toString();
return reString;
}