spring2通过jdbc的方式读取、更新数据库的clob或者blob类型的数据

      今天由于项目的需要，必须在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());

 }

}

评论

1 楼 enet_java 2010-10-27  

多谢！！！