JdbcTemplate 来封装数据库jdbc操作细节

来源：互联网发布：java方向算法书籍推荐编辑：程序博客网时间：2024/05/29 07:43

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

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

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

1int k = jdbcTemplate.update("UPDATE tblname SET prop1=?,prop2=?...", newObject[]{...});

1jdbcTemplate.update("INSERT INTO tblname VALUES(?,?,..)", new Object[]{...},
2     new int[]{Types.VARCHAR,Types.NUMERIC});

01jdbcTemplate.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)

1final User user = newUser();
2jdbcTemplate.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);

01List 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 便捷方法

1List uNames = jdbcTemplate.queryForList("SELECT name FROM tblname WHERE id>?",
2    new Integer []{5}, String.class);

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

1String user = jdbcTemplate.queryForObject("SELECT name FROM tblname WHERE id=?",
2     new Integer []{5}, String.class );

1intuNum = jdbcTemplate.queryForInt("SELECT count(*) FROM tblname WHERE id>?",
2    new Integer []{5});

4)使用jdbc 操作类

a)扩展 MappingSqlQuery类

01class 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}
13JdbcQueryObject queryObj = new JdbcQueryObject( ds,
14      "SELECT .. FROM tblName WHERE param=?");
15List list = queryObj.execute(new Object[]{...});

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

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

c)使用 SqlUpdate 类更新

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

5)支持jdbc 事务

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

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

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

默认实现为：DefaultTransactionDefinition类

01PlatformTransactionManager tm = 
02newDataSourceTransactionManager(
03            jdbcTemplate.getDataSource() );
04TransactionStatus status = null;
05try{
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}  提供了JdbcTemplate 来封装数据库jdbc操作细节： 
包括： 数据库连接[打开/关闭] ，异常转义 ，SQL执行 ，查询结果的转换 

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

1) 使用JdbcTemplate 更新(insert /update /delete)
1int k = jdbcTemplate.update("UPDATE tblname SET prop1=?,prop2=?...", newObject[]{...});
1jdbcTemplate.update("INSERT INTO tblname VALUES(?,?,..)", new Object[]{...},
2     new int[]{Types.VARCHAR,Types.NUMERIC});
01jdbcTemplate.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)
1final User user = newUser();
2jdbcTemplate.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);
01List 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 便捷方法
1List uNames = jdbcTemplate.queryForList("SELECT name FROM tblname WHERE id>?",
2    new Integer []{5}, String.class);
1List<Map> uMapList = (List<Map>) jdbcTemplate.queryForList( "SELECT id, name FROM tblname WHERE id>?",
2             newInteger []{5});
3for(Map<String,Object> uMap :uMapList){
4      Integer id = uMap.get("id");
5      String name = uMap.get("name");
6};
1String user = jdbcTemplate.queryForObject("SELECT name FROM tblname WHERE id=?",
2     new Integer []{5}, String.class );
1intuNum = jdbcTemplate.queryForInt("SELECT count(*) FROM tblname WHERE id>?",
2    new Integer []{5});


4)使用jdbc 操作类
a)扩展 MappingSqlQuery类
01class 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}
13JdbcQueryObject queryObj = new JdbcQueryObject( ds,
14      "SELECT .. FROM tblName WHERE param=?");
15List list = queryObj.execute(new Object[]{...});
b)使用 SqlFunction 类 查询单条结果
1SqlFunction queryFun = newSqlFunction( ds,
2      "select count(*) from tblName where ..." ,new int[]{Types.CHAR,...} );
3queryFun.compile();
4queryFun.run(new Object[]{p1,p2,..});
c)使用 SqlUpdate 类 更新
1SqlUpdate updateFunc = new SqlUpdate(ds ,"INSERT tblName ...");
2updateFunc.declareParameter( new SqlParameter("prop",Types.CHAR) );
3updateFunc.compile();
4updateFunc.update(new String[]{s1,s1});
5)支持jdbc 事务
spring的事务管理有两种方式：编程式事务、声明式事务
这里谈一下 基于数据库单一资源的编程式事务:
spring用实现TransactionDefinition接口的类定义事务的属性：传播行为；隔离级别；超时值；只读标志
默认实现为：DefaultTransactionDefinition类
01PlatformTransactionManager tm = 
02newDataSourceTransactionManager(
03            jdbcTemplate.getDataSource() );
04TransactionStatus status = null;
05try{
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}  

0 0