今天由于项目的需要,必须在spring下通过jdbc的方式进行读取和更新clob类型的数据,刚开始的时候,我直接通过spring获取数据连接,然后用PreparedStatement进行处理,但结果却是发生错误,后来参考了一下spring的官方文档,得知运用LobCreator类可以达到目的,于是通过查阅API经过多番的修改调试后,终于解决问题,代码片段如下,附件的文件是详细的代码。
保存数据到clob字段的方法:
/**
* 保存到数据库
*
* @param routerList
*/
public static void saveToDatabase(List<CustomerRouter> routerList) {
if (routerList != null && routerList.size() > 0) {
// 保存动作
try {
final List<CustomerRouter> list = routerList;
//插入数据的sql语句
final String insertSql = "insert into customer_router(" + "ID,"
+ "START_CUSTOMER_CODE," + "END_CUSTOMER_CODE,"
+ "ROUTER_DISTANCE," + "ROUTER_DISTANCE_EX,"
+ "ROAD_ID," + "ROUTER_LINE) "
+ "values(CUSTOMER_ROUTER_SEQ.NEXTVAL," + "?," + "?,"
+ "?," + "?," + "?," + "?" + ")";
//更新数据的sql语句
final String updateSql = "update customer_router " +
"set ROUTER_DISTANCE = ?," +
"ROUTER_DISTANCE_EX = ?," +
"ROAD_ID = ?," +
"ROUTER_LINE = ? " +
"where START_CUSTOMER_CODE = ? " +
"and END_CUSTOMER_CODE = ? ";
TransactionTemplate tt = DBUtil.getTransactionTemplate();
tt.execute(new TransactionCallback() {
public Object doInTransaction(TransactionStatus ts) {
JdbcTemplatePlus jdbcTemplate = DBUtil.getJdbcTemplate();
for (CustomerRouter router : list) {
OracleLobHandler lobHandler = DBUtil.getLobHandler();
//先更新
if(DEBUG)System.out.println("更新");
Object obj = jdbcTemplate.execute(updateSql, new MyLobCreps1(lobHandler,router));
int updateRows = ((Integer)obj).intValue();
if(updateRows == 0){//如果不存在,则插入
if(DEBUG)System.out.println("插入");
jdbcTemplate.execute(insertSql,new MyLobCreps(lobHandler,router));
}
}
return null;
}
});
} catch (Exception e) {
e.printStackTrace();
} finally {
}
}
}
MyLobCreps类的内容:
public class MyLobCreps extends AbstractLobCreatingPreparedStatementCallback{
CustomerRouter router = null;
public MyLobCreps(LobHandler arg0,CustomerRouter router) {
super(arg0);
this.router = router;
// TODO Auto-generated constructor stub
}
@Override
protected void setValues(
PreparedStatement ps,
LobCreator lc)
throws SQLException,
DataAccessException {
ps.setString(1,router.getStartCustomerCode());
ps.setString(2,router.getEndCustomerCode());
ps.setFloat(3,router.getRouterDistance());
ps.setFloat(4,router.getRouterDistanceEx());
lc.setClobAsString(ps, 5, router.getRoadId());
lc.setClobAsString(ps, 6, router.getRouterLine());
}
}