JDBC模板

最近写了几个同步数据的统计任务，使用到了JDBCTemplate的批处理，顺手将JDBCTemplate的用法大概总结了一下。
JDBCTemplate主要提供了五类方法：

execute()方法：可用于执行任何sql语句，但是一般用来执行DDL语句；
update()以及batchUpdate()方法：
update()方法用来执行增加、修改和删除等语句；
batchUpdate()方法用来执行批处理相关的语句；
query()以及queryForXxx()：用来执行查询相关的语句；
call()方法：用于执行存储过程、函数相关的语句；

JDBCTemplate支持的回调类：
预编译语句及存储过程创建回调：用于根据JDBCTemplate提供的连接创建相应的语句；
1、PreparedStatementCreator：通过回调获取JDBCTemplate提供的Connection，由用户使用该Connection来创建相关的PreparedStatement；      
   代码示例：

jdbcTemplate.update(new PreparedStatementCreator(){
    @Override
    public PreparedStatement createPreparedStatement(Connection conn) {
        PreparedStatement ps = null;
        final String sql = "******";
        try{
            ps = conn.prepareStatement(sql);
            ps.setString(1,UUID.randomUUID().toString());
        }catch(Exception e){
            logger.error("**** error " + e);
        }finally{
            conn.commit();
        }
        return ps;
    }
}); 

2、CallableStatementCreator：通过回调获取JDBCTemplate提供的Connection，由用户使用该Connection来创建相关的CallableStatement；
代码示例：

jdbcTemplate.execute(new CallableStatementCreator(){
    @Override
    public CallableStatement createCallableStatement(Connection connection) {
        String procedure = "{call PRC_BJ_SYNC_CIRCUIT_DATA_ALL (?)}";//存储过程
        CallableStatement cs = null;
        try {
            cs = connection.prepareCall(procedure);
            cs.registerOutParameter(1,OracleTypes.VARCHAR);//注册输出参数的类型
        } catch (Exception e) {
            logger.error("call procedure error : " + e);
            log.setResultCode("-2");//调用存储过程失败
        }
        return cs;
    }
    }, new CallableStatementCallback(){
        @Override
        public Object doInCallableStatement(CallableStatement cs) {
            String csStr = null;
            try {
                cs.execute();
                csStr = cs.getString(1);
            } catch (Exception e) {
                logger.error("call procedure error : " + e);
                log.setResultCode("-2");//调用存储过程失败
            }
            return csStr;//获取输出参数的值 
        }
    }
);

预编译语句设值回调：用于给预编译语句相应参数设值；
1、PreparedStatementSetter：通过回调获取JDBCTemplate提供的PreparedStatement，由用户来对相应的预编译语句的参数设值；
2、BatchPreparedStatementSetter：类似于PreparedStatementSetter，但用于批处理，需要指定批处理的大小；
自定义功能回调：提供给用户一个扩展点，用户可以在指定类型的扩展点执行任何数量需要的操作；
1、ConnectionCallBack：通过回调获取JDBCTemplate提供的Connection，用户可在该Connection执行任何数量的操作；
2、StatementCallBack：通过回调获取JDBCTemplate提供的Statement，用户可以在该Statement提供任何数量的操作；
3、PreparedStatementCallBack：通过回调获取JDBCTemplate提供的PreparedStatement，用户可以在该PreparedStatement执行任何数量的操作；
4、CallableStatementCallBack：通过回调获取JDBCTemplate提供的CallableStatement，用户可以在该CallableStatement执行任何数量的操作；
结果集处理回调：通过回调处理ResultSet或将ResultSet转换为需要的形式；
1、RowMapper：用于将结果集每行数据转换为需要的类型，用户需实现方法mapRow(ResultSet rs, int rowNum)来完成将每行数据转换为相应的类型。
2、RowCallbackHandler：用于处理ResultSet的每一行结果，用户需实现方法processRow(ResultSet rs)来完成处理，在该回调方法中无需执行rs.next()，该操作由JdbcTemplate来执行，用户只需按行获取数据然后处理即可。
3、ResultSetExtractor：用于结果集数据提取，用户需实现方法extractData(ResultSet rs)来处理结果集，用户必须处理整个结果集；
具体示例：
1、预编译语句或存储过程创建回调，自定义功能回调使用：
首先使用PreparedStatementCreator创建一个预编译语句，其次由JdbcTemplate通过PreparedStatementCallback回调传回，由用户决定如何执行该PreparedStatement。此处我们使用的是execute方法。

public void testPreparedStatement() { 
  int count = jdbcTemplate.execute(new PreparedStatementCreator() { 
     @Override 
     public PreparedStatement createPreparedStatement(Connection conn) 
         throws SQLException { 
         return conn.prepareStatement("select count(*) from test"); 
     }}, new PreparedStatementCallback<Integer>() { 
     @Override 
     public Integer doInPreparedStatement(PreparedStatement pstmt) 
         throws SQLException, DataAccessException { 
         pstmt.execute(); 
         ResultSet rs = pstmt.getResultSet(); 
         rs.next(); 
         return rs.getInt(1); 
      }});       
} 

2、预编译语句设值回调使用：
通过JdbcTemplate的int update(String sql, PreparedStatementSetter pss)执行预编译sql，其中sql参数为“insert into TBL_SYS(name) values (?) ”，该sql有一个占位符需要在执行前设值，PreparedStatementSetter实现就是为了设值，使用setValues(PreparedStatement pstmt)回调方法设值相应的占位符位置的值。JdbcTemplate也提供一种更简单的方式“update(String sql, Object[] args)”来实现设值，所以只要当使用该种方式不满足需求时才应使用PreparedStatementSetter。

public void testPreparedStatement() { 
  String insertSql = "insert into TBL_SYS(name) values (?)"; 
  int count = jdbcTemplate.update(insertSql, new PreparedStatementSetter() { 
      @Override 
      public void setValues(PreparedStatement pstmt) throws SQLException { 
          pstmt.setObject(1, "name"); 
  }}); 
 
  //**************************************************************   
  String deleteSql = "delete from TBL_SYS where name=?"; 
  count = jdbcTemplate.update(deleteSql, new Object[] {"name"});
}

3、结果集处理回调：
RowMapper接口提供mapRow(ResultSet rs, int rowNum)方法将结果集的每一行转换为一个Map，当然可以转换为其他类。

public void testResultSet() { 
  jdbcTemplate.update("insert into TBL_SYS(name) values('name')"); 
 
  String listSql = "select * from TBL_SYS"; 
  List result = jdbcTemplate.query(listSql, new RowMapper<Map>() { 
      @Override 
      public Map mapRow(ResultSet rs, int rowNum) throws SQLException { 
          Map row = new HashMap(); 
          row.put(rs.getInt("id"), rs.getString("name")); 
          return row; 
  }});      
}   

RowCallbackHandler接口也提供方法processRow(ResultSet rs)，能将结果集的行转换为需要的形式。

public void testResultSet() { 
  jdbcTemplate.update("insert into TBL_SYS(name) values('name')"); 
 
  String listSql = "select * from TBL_SYS"; 
  final List result = new ArrayList(); 
  jdbcTemplate.query(listSql, new RowCallbackHandler() { 
      @Override 
      public void processRow(ResultSet rs) throws SQLException { 
          Map row = new HashMap(); 
          row.put(rs.getInt("id"), rs.getString("name")); 
          result.add(row); 
  }}); 
}

ResultSetExtractor使用回调方法extractData(ResultSet rs)提供给用户整个结果集，让用户决定如何处理该结果集。

public void testResultSet() {
  jdbcTemplate.update("insert into TBL_SYS(name) values('name')"); 
 
  String listSql = "select * from TBL_SYS"; 
  List result = jdbcTemplate.query(listSql, new ResultSetExtractor<List>() { 
      @Override 
      public List extractData(ResultSet rs) throws SQLException, DataAccessException { 
          List result = new ArrayList(); 
          while(rs.next()) { 
              Map row = new HashMap(); 
              row.put(rs.getInt("id"), rs.getString("name")); 
              result.add(row); 
           } 
           return result; 
  }}); 
}

4、JDBCTemplate的queryForXxx()方法：

//1.查询一行数据并返回int型结果 
jdbcTemplate.queryForInt("select count(*) from TBL_SYS"); 
//2. 查询一行数据并将该行数据转换为Map返回 
jdbcTemplate.queryForMap("select * from TBL_SYS where name='Tom'"); 
//3.查询一行任何类型的数据，最后一个参数指定返回结果类型 
jdbcTemplate.queryForObject("select count(*) from TBL_SYS", Integer.class); 
//4.查询一批数据，默认将每行数据转换为Map       
jdbcTemplate.queryForList("select * from TBL_SYS"); 
//5.只查询一列数据列表，列类型是String类型，列名字是name 
jdbcTemplate.queryForList("select name from TBL_SYS where name=?", new Object[]{"Tom"}, String.class); 
//6.查询一批数据，返回为SqlRowSet，类似于ResultSet，但不再绑定到连接上 
SqlRowSet rs = jdbcTemplate.queryForRowSet("select * from TBL_SYS"); 

5、存储过程及函数回调：
存储过程回调：
方法一：

首先使用CallableStatementCreator 创建一个预编译语句，其次由JdbcTemplate通过CallableStatementCallback 回调传回，由用户决定如何执行该CallableStatement 。此处我们使用的是execute方法。

public static void callProcedure() {
    jdbcTemplate.execute(new CallableStatementCreator(){
        @Override
        public CallableStatement createCallableStatement(Connection connection) {
            String procedure = "{call PRC_BJ_SYNC_CIRCUIT_DATA_ALL (?)}";//存储过程
            CallableStatement cs = null;
            try {
                cs = connection.prepareCall(procedure);
                cs.registerOutParameter(1,OracleTypes.VARCHAR);//注册输出参数的类型
            } catch (Exception e) {
 
            }
            return cs;
        }
    }, new CallableStatementCallback(){
        @Override
        public Object doInCallableStatement(CallableStatement cs) {
            String csStr = null;
            try {
                cs.execute();
                csStr = cs.getString(1);
            } catch (Exception e) {
 
            }
            return csStr;//获取输出参数的值 
        }});
} 

方法二：

public void testCallableStatementCreator() { 
    final String callProcedureSql = "{call PROCEDURE_TEST(?, ?)}"; 
    List<SqlParameter> params = new ArrayList<SqlParameter>(); 
    params.add(new SqlInOutParameter("inOutName", Types.VARCHAR)); 
    params.add(new SqlOutParameter("outId", Types.INTEGER)); 
     // outValues：通过SqlInOutParameter及SqlOutParameter参数定义的name来获取存储过程结果
    Map<String, Object> outValues = jdbcTemplate.call( 
      new CallableStatementCreator() { 
        @Override 
        public CallableStatement createCallableStatement(Connection conn) throws SQLException { 
          CallableStatement cstmt = conn.prepareCall(callProcedureSql); 
          cstmt.registerOutParameter(1, Types.VARCHAR); 
          cstmt.registerOutParameter(2, Types.INTEGER); 
          cstmt.setString(1, "test"); 
          return cstmt; 
    }}, params);   
}  

自定义函数回调：

1、params：用于描述自定义函数占位符参数或命名参数类型；SqlParameter用于描述IN类型参数、SqlOutParameter用于描述OUT类型参数、SqlInOutParameter用于描述INOUT类型参数、SqlReturnResultSet用于描述调用存储过程或自定义函数返回的ResultSet类型数据，其中SqlReturnResultSet需要提供结果集处理回调用于将结果集转换为相应的形式，hsqldb自定义函数返回值是ResultSet类型。

2、CallableStatementCreator：提供Connection对象用于创建CallableStatement对象

3、outValues：调用call方法将返回类型为Map<String, Object>对象；

4、outValues.get("result")：获取结果，即通过SqlReturnResultSet对象转换过的数据；其中SqlOutParameter、SqlInOutParameter、SqlReturnResultSet指定的name用于从call执行后返回的Map中获取相应的结果，即name是Map的键。

 

public void testCallableStatementCreator() { 
    final String callFunctionSql = "{call FUNCTION_TEST(?)}"; 
    List<SqlParameter> params = new ArrayList<SqlParameter>(); 
    params.add(new SqlParameter(Types.VARCHAR)); 
    params.add(new SqlReturnResultSet("result",new ResultSetExtractor<Integer>() { 
           @Override 
           public Integer extractData(ResultSet rs) throws SQLException,DataAccessException { 
               while(rs.next()) { 
                   return rs.getInt(1); 
               } 
              return 0; 
       })); 
 
    Map<String, Object> outValues = jdbcTemplate.call(new CallableStatementCreator() { 
            @Override 
            public CallableStatement createCallableStatement(Connection conn) throws SQLException { 
              CallableStatement cstmt = conn.prepareCall(callFunctionSql); 
              cstmt.setString(1, "test");
              return cstmt; 
    }}, params); 
}

 几种具体使用：
1、调用无返回值的存储过程

public class callProcedure {      
    private JdbcTemplate jdbcTemplate;      
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {      
    this.jdbcTemplate = jdbcTemplate;      
    }      
    public void test(){      
       this.jdbcTemplate.execute("{call procedureName (?)}");      
    }      
  } 

2、调用有返回值的存储过程（不是结果集）

public class test {  
        
      /** 
       * 调用无参的存储过程（有返回值） 
       * @return 
       */  
      public static int callProcedure() {  
            String str = (String)jdbcTemplate.execute(new CallableStatementCreator(){  
                  @Override  
                  public CallableStatement createCallableStatement(Connection connection) {  
                        String procedure = "{call PRC_BJ_SYNC_CIRCUIT_DATA_INCRE (?)}";//存储过程  
                        CallableStatement cs = null;//创建存储过程的对象  
                        try {  
                              cs = connection.prepareCall(procedure);  
                              cs.registerOutParameter(1,OracleTypes.VARCHAR);//注册输出参数的类型  
                        } catch (Exception e) {  
                              logger.error("call procedure error : " + e);  
                        }  
                        return cs;  
                  }  
            }, new CallableStatementCallback(){  
                  @Override  
                  public Object doInCallableStatement(CallableStatement cs) {  
                        String csStr = null;  
                        try {  
                              cs.execute();  
                              csStr = cs.getString(1);//获取输出参数的值  
                        } catch (Exception e) {  
                              logger.error("call procedure error : " + e);  
                        }  
                        return csStr;//获取输出参数的值  
                  }});  
            return Integer.parseInt(str);  
      }  
        
      /** 
       * 调用有参的存储过程（有返回值） 
       * @return 
       */  
      public static int callProcedure() {  
            String str = (String)jdbcTemplate.execute(new CallableStatementCreator(){  
                  @Override  
                  public CallableStatement createCallableStatement(Connection connection) {  
                        String procedure = "{call PRC_BJ_SYNC_CIRCUIT_DATA_INCRE (?,?)}";//存储过程  
                        CallableStatement cs = null;//创建存储过程的对象  
                        try {  
                              cs = connection.prepareCall(procedure);  
                              cs.setString(1,"value1");//设置入参的值  
                              cs.registerOutParameter(1,OracleTypes.VARCHAR);//注册输出参数的类型  
                        } catch (Exception e) {  
                              logger.error("call procedure error : " + e);  
                        }  
                        return cs;  
                  }  
            }, new CallableStatementCallback(){  
                  @Override  
                  public Object doInCallableStatement(CallableStatement cs) {  
                        String csStr = null;  
                        try {  
                              cs.execute();  
                              csStr = cs.getString(2);//获取输出参数的值  
                        } catch (Exception e) {  
                              logger.error("call procedure error : " + e);  
                        }  
                        return csStr;//获取输出参数的值  
                  }});  
            return Integer.parseInt(str);  
      }  
}  

3、调用有返回值的存储过程（是结果集）
 

public class test {      
    List resultList = (List) jdbcTemplate.execute(    
           new CallableStatementCreator() {    
          public CallableStatement createCallableStatement(Connection con) throws SQLException {    
             String storedProc = "{call PRC_BJ_SYNC_CUST_DATA(?,?)}";// 调用的sql    
             CallableStatement cs = con.prepareCall(storedProc);    
             cs.setString(1, "p1");// 设置输入参数的值    
             cs.registerOutParameter(2, OracleTypes.CURSOR);// 注册输出参数的类型    
             return cs;    
          }    
           }, new CallableStatementCallback() {    
          public Object doInCallableStatement(CallableStatement cs) throws SQLException,DataAccessException {    
             List resultsMap = new ArrayList();    
             cs.execute();    
             ResultSet rs = (ResultSet) cs.getObject(2);// 获取游标一行的值    
             while (rs.next()) {// 转换每行的返回值到Map中    
            Map rowMap = new HashMap();    
            rowMap.put("id", rs.getString("id"));    
            rowMap.put("name", rs.getString("name"));    
            resultsMap.add(rowMap);    
             }    
             rs.close();    
             return resultsMap;    
          }    
        });    
    for (int i = 0; i < resultList.size(); i++) {    
       Map rowMap = (Map) resultList.get(i);    
       String id = rowMap.get("id").toString();    
       String name = rowMap.get("name").toString();    
       System.out.println("id=" + id + ";name=" + name);    
    }