Spring JdbcTemplate小结

时间：2023-03-09 00:47:57
Spring JdbcTemplate小结

提供了JdbcTemplate 来封装数据库jdbc操作细节： 
包括： 数据库连接[打开/关闭] ，异常转义 ，SQL执行 ，查询结果的转换

使用模板方式封装 jdbc数据库操作-固定流程的动作，提供丰富callback回调接口功能，方便用户自定义加工细节，更好模块化jdbc操作，简化传统的JDBC操作的复杂和繁琐过程。

1) 使用JdbcTemplate 更新(insert /update /delete)

1 int k = jdbcTemplate.update("UPDATE tblname SET prop1=?,prop2=?...", newObject[]{...});
1 jdbcTemplate.update("INSERT INTO tblname VALUES(?,?,..)", new Object[]{...},
2      new int[]{Types.VARCHAR,Types.NUMERIC});
01 jdbcTemplate.update("INSERT INTO tblname VALUES(?,?,..)",                    
02         newPreparedStatementSetter(){                         
03                public void setValues(PreparedStatement ps) throwsSQLException{     
04                     ps.setLong(1, user.getId(1));
05                     ps.setString(2, user.getName(2));  
06                     ps.setDate(3, new java.sql.Date(newDate().getTime()); 
07                     ps.setTimestamp(4, new Timestamp(new Date().getTime());
08                }                    
09         }
10 );

2) 使用JdbcTemplate 查询 (select)

1 final User user = newUser();
2 jdbcTemplate.query("SELECT id,name,.. FROM tblname WHERE id=1",
3        newRowCallbackHandler(){
4               public void processRow(ResultSet rs) throwsSQLException{
5                     user.setId(rs.getLong(1));
6                     user.setName(rs.getString(2));
7               }
8       }
9 );
01 List uGroup = jdbcTemplate.query("SELECT id,name,.. FROM tblname WHERE igroup=1",
02      newRowMapper(){
03             public Object mapRow(ResultSet rs,int no) throwsSQLException{
04                      User user = newUser();
05                      user.setId(rs.getLong(1));
06                      user.setName(rs.getString(2));
07                      returnuser ;
08             }
09      }
10 };

3)使用JdbcTemplate 便捷方法

1 List uNames = jdbcTemplate.queryForList("SELECT name FROM tblname WHERE id>?",
2     new Integer []{5}, String.class);
1 List<Map> uMapList = (List<Map>) jdbcTemplate.queryForList( "SELECT id, name FROM tblname WHERE id>?",
2              newInteger []{5});
3 for(Map<String,Object> uMap :uMapList){
4       Integer id = uMap.get("id");
5       String name = uMap.get("name");
6 };
1 String user = jdbcTemplate.queryForObject("SELECT name FROM tblname WHERE id=?",
2      new Integer []{5}, String.class );
1 intuNum = jdbcTemplate.queryForInt("SELECT count(*) FROM tblname WHERE id>?",
2     new Integer []{5});

4)使用jdbc 操作类

a)扩展 MappingSqlQuery类

01 class JdbcQueryObject extends MappingSqlQuery { // extends SqlQuery
02       public JdbcQueryObject (DataSource ds,String sql){
03             this.setDataSource( ds );
04             this.setSql( sql );
05             this.declareParameter(newSqlparameter("propName",
06                 Types.VARCHAR);// propName 提示作用
07         this.compile();
08       }
09       public Object mapRow(ResultSet rs,int p) throws SQLException{
10                  // ...
11      }
12 }
13 JdbcQueryObject queryObj = new JdbcQueryObject( ds,
14       "SELECT .. FROM tblName WHERE param=?");
15 List list = queryObj.execute(new Object[]{...});

b)使用 SqlFunction 类 查询单条结果

1 SqlFunction queryFun = newSqlFunction( ds,
2       "select count(*) from tblName where ..." ,new int[]{Types.CHAR,...} );
3 queryFun.compile();
4 queryFun.run(new Object[]{p1,p2,..});

c)使用 SqlUpdate 类 更新

1 SqlUpdate updateFunc = new SqlUpdate(ds ,"INSERT tblName ...");
2 updateFunc.declareParameter( new SqlParameter("prop",Types.CHAR) );
3 updateFunc.compile();
4 updateFunc.update(new String[]{s1,s1});

5)支持jdbc 事务

spring的事务管理有两种方式：编程式事务、声明式事务

这里谈一下 基于数据库单一资源的编程式事务:

spring用实现TransactionDefinition接口的类定义事务的属性：传播行为；隔离级别；超时值；只读标志

默认实现为：DefaultTransactionDefinition类

01 PlatformTransactionManager tm = 
02 newDataSourceTransactionManager(
03             jdbcTemplate.getDataSource() );
04 TransactionStatus status = null;
05 try{
06     //null 默认事务属性配置DefaultTransactionDefinition
07     status = tm.getTransaction(null);          
08   for(finalString wd: words){         
09    try {
10      jdbcTemplate.update( insertWordSql,
11           new PreparedStatementSetter(){
12  
13     public voidsetValues(PreparedStatement pstate)
14                      throws SQLException {
15                 pstate.setString(1, wd) ;
16         pstate.setTimestamp(2,
17         new Timestamp( newDate().getTime() ));                            
18     }                  
19         }
20      );                
21              
22    } catch (DataAccessException e) {
23        e.printStackTrace();
24        //tm.rollback(status);
25     }
26     } // end for
27 } finally {
28      tm.commit(status);
29 }  

转自：http://hwqjavaeye.iteye.com/blog/289330

相关文章