JdbcTemplate 來封裝資料庫jdbc操作細節

版權聲明：本文為部落客原創文章，未經部落客允許不得轉載。 https://blog.csdn.net/qingfeng812/article/details/21703865

提供了JdbcTemplate 來封裝資料庫jdbc操作細節： 

包括： 資料庫連接配接[打開/關閉] ，異常轉義 ，SQL執行 ，查詢結果的轉換 

使用模闆方式封裝 jdbc資料庫操作-固定流程的動作，提供豐富callback回調接口功能，友善使用者自定義加工細節，更好子產品化jdbc操作，簡化傳統的JDBC操作的複雜和繁瑣過程。 

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

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

1	jdbcTemplate.update( "INSERT INTO tblname VALUES(?,?,..)" ,  new Object[]{...},

2	new int []{Types.VARCHAR,Types.NUMERIC});

01	jdbcTemplate.update( "INSERT INTO tblname VALUES(?,?,..)" ,

02	new PreparedStatementSetter(){

03	public void setValues(PreparedStatement ps)  throws SQLException{

04	ps.setLong( 1 , user.getId( 1 ));

05	ps.setString( 2 , user.getName( 2 ));

06	ps.setDate( 3 ,  new java.sql.Date( new Date().getTime());

07	ps.setTimestamp( 4 ,  new Timestamp( new Date().getTime());

08

}

09

}

10

);

2) 使用JdbcTemplate 查詢 (select)

1	final User user =  new User();

2	jdbcTemplate.query( "SELECT id,name,.. FROM tblname WHERE id=1" ,

3	new RowCallbackHandler(){

4	public void processRow(ResultSet rs)  throws SQLException{

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	new RowMapper(){

03	public Object mapRow(ResultSet rs, int no)  throws SQLException{

04	User user =  new User();

05	user.setId(rs.getLong( 1 ));

06	user.setName(rs.getString( 2 ));

07	return user ;

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	new Integer []{ 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	int uNum = 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( new Sqlparameter( "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 =  new SqlFunction( 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	new DataSourceTransactionManager(

03	jdbcTemplate.getDataSource() );

04	TransactionStatus status =  null ;

05

try {

06	//null 預設事務屬性配置DefaultTransactionDefinition

07	status = tm.getTransaction( null );

08	for ( final String wd: words){

09

try {

10	jdbcTemplate.update( insertWordSql,

11	new PreparedStatementSetter(){

12

13	public void setValues(PreparedStatement pstate)

14	throws SQLException {

15	pstate.setString( 1 , wd) ;

16	pstate.setTimestamp( 2 ,

17	new Timestamp(  new Date().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

}

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

1	jdbcTemplate.update( "INSERT INTO tblname VALUES(?,?,..)" ,  new Object[]{...},

2	new int []{Types.VARCHAR,Types.NUMERIC});

01	jdbcTemplate.update( "INSERT INTO tblname VALUES(?,?,..)" ,

02	new PreparedStatementSetter(){

03	public void setValues(PreparedStatement ps)  throws SQLException{

04	ps.setLong( 1 , user.getId( 1 ));

05	ps.setString( 2 , user.getName( 2 ));

06	ps.setDate( 3 ,  new java.sql.Date( new Date().getTime());

07	ps.setTimestamp( 4 ,  new Timestamp( new Date().getTime());

08

}

09

}

10

);

1	final User user =  new User();

2	jdbcTemplate.query( "SELECT id,name,.. FROM tblname WHERE id=1" ,

3	new RowCallbackHandler(){

4	public void processRow(ResultSet rs)  throws SQLException{

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	new RowMapper(){

03	public Object mapRow(ResultSet rs, int no)  throws SQLException{

04	User user =  new User();

05	user.setId(rs.getLong( 1 ));

06	user.setName(rs.getString( 2 ));

07	return user ;

08

}

09

}

10

};

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	new Integer []{ 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	int uNum = jdbcTemplate.queryForInt( "SELECT count(*) FROM tblname WHERE id>?" ,

2	new Integer []{ 5 });

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( new Sqlparameter( "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[]{...});

1	SqlFunction queryFun =  new SqlFunction( ds,

2	"select count(*) from tblName where ..." , new int []{Types.CHAR,...} );

3	queryFun.compile();

4	queryFun.run( new Object[]{p1,p2,..});

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});

01	PlatformTransactionManager tm =

02	new DataSourceTransactionManager(

03	jdbcTemplate.getDataSource() );

04	TransactionStatus status =  null ;

05

try {

06	//null 預設事務屬性配置DefaultTransactionDefinition

07	status = tm.getTransaction( null );

08	for ( final String wd: words){

09

try {

10	jdbcTemplate.update( insertWordSql,

11	new PreparedStatementSetter(){

12

13	public void setValues(PreparedStatement pstate)

14	throws SQLException {

15	pstate.setString( 1 , wd) ;

16	pstate.setTimestamp( 2 ,

17	new Timestamp(  new Date().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

}