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