spring jdbcTemplate使用

(1)springJdbcContext.xml 

Java代码  

1. <?xml version="1.0" encoding="UTF-8"?>  
2. <beans xmlns="http://www.springframework.org/schema/beans"   
3.     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"   
4.     xmlns:context="http://www.springframework.org/schema/context"   
5.     xmlns:aop="http://www.springframework.org/schema/aop"   
6.     xmlns:tx="http://www.springframework.org/schema/tx"   
7.     xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd   
8.     http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd   
9.     http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd   
10.     http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd">  
11.     <description>springApp</description>      
12.     <!-- dataSource for MySQL -->  
13.     <bean id="dataSource"  
14.         class="org.apache.commons.dbcp.BasicDataSource"  
15.         destroy-method="close">  
16.         <property name="driverClassName"  
17.             value="com.mysql.jdbc.Driver" />  
18.         <property name="url"  
19.             value="jdbc:mysql://localhost:3306/springapp" />  
20.         <property name="username" value="root" />  
21.         <property name="password" value="****" />  
22.     </bean>     
23.     <bean id = "TransactionManager"   
24.          class = "org.springframework.jdbc.datasource.DataSourceTransactionManager">   
25.          <property name = "dataSource" ref="dataSource"/>   
26.     </bean>  
27.     <!--1:配置一个JdbcTemplate实例，并将这个“共享的”，“安全的”实例注入到不同的DAO类中去-->  
28.     <bean id = "jdbcTemplate"   
29.          class = "org.springframework.jdbc.core.JdbcTemplate">   
32.     <bean id = "actorJdbcTemplateDao"   
33.          class = "com.logcd.bo.dao.impl.ActorJdbcTemplateDaoImpl">   
34.          <property name="jdbcTemplate" ref="jdbcTemplate"/>   
36.     <!--2:将共享的DataSource实例注入到DAO中，JdbcTemplate实例在DataSource的setter方法中被创建-->  
37.     <bean id = "actorEventDao"   
38.          class = "com.logcd.bo.dao.impl.ActorEventDaoImpl">   
41.     <!--利用了拦截器的原理。-->     
42.    <bean id="transactionInterceptor"    
43.          class="org.springframework.transaction.interceptor.TransactionInterceptor">     
44.         <property name="transactionManager">      
45.                   <ref bean="transactionManager" />     
46.         </property>     
47.     <!-- 配置事务属性 -->  
48.    <property name="transactionAttributes">     
49.         <props>     
50.             <prop key="delete*">PROPAGATION_REQUIRED</prop>  
51.             <prop key="operate*">PROPAGATION_REQUIRED,-Exception</prop>     
52.             <prop key="insert*">PROPAGATION_REQUIRED,-Exception</prop>     
53.             <prop key="update*">PROPAGATION_REQUIRED,-Exception</prop>     
54.             <prop key="save*">PROPAGATION_REQUIRED</prop>     
55.             <prop key="find*">PROPAGATION_REQUIRED,readOnly</prop>     
56.        </props>     
57.    </property>     
58.    </bean>     
59.    <bean id="txProxy"    
60.          class="org.springframework.aop.framework.autoproxy.BeanNameAutoProxyCreator">     
61.         <property name="beanNames">     
62.           <list>     
63.              <value>*Dao*</value><!--只是为了测试，一般为service-->  
64.           </list>     
66.         <property name="interceptorNames">     
68.              <value>transactionInterceptor</value>     
71.    </bean>    
72. </beans>  

(2)接口：(以第二种方式定义DAO) 

1. package com.logcd.bo.dao;  
2. import java.util.List;  
3. import org.springframework.jdbc.support.KeyHolder;  
4. import com.logcd.bo.Actor;  
5. public interface ActorEventDao {  
6.     /** 
7.      * 根据SQL建表 
8.      * @param sql 
9.      */  
10.     public void createTableBySQL(String sql);  
12.      * 统计firstName相同的总数 
13.      * @param firstName 
14.      * @return 
16.     public int findCountOfActorsByFirstName(String firstName);  
18.      * 插入记录并返回自动生成的主键Id 
19.      * @param ps 
22.     public KeyHolder insertActor(final Actor actor);  
24.      * 用SimpleJdbcInsert插入一条记录:mysql测试成功 
26.     public long inserOneActor(Actor actor);  
28.      * 插入/更新/删除数据 
29.      * @param sql 有参数语句 
30.      * @param obj 参数值数组 
32.     public int operateActor(String sql,Object[] obj);  
34.      * 根据SQL查询记录总数 
38.     public int findRowCountBySQL(String sql);  
40.      * 根据Id查找指定对象 
41.      * @param id 
44.     public Actor findActorById(long id);  
50.     public Actor findActorByIdSimple(long id);  
52.      * 返回所有对象 
55.     public List findAllActors();  
56.             /** 
57.      * 批量更新 
58.      * @param actors 
61.     public int[] updateBatchActors(final List actors);  
67.     public int[] updateBatchActorsSimple(final List<Actor> actors);  
68. }  

(3)接口实现 

1. package com.logcd.bo.dao.impl;  
2. import java.sql.Connection;  
3. import java.sql.PreparedStatement;  
4. import java.sql.ResultSet;  
5. import java.sql.SQLException;  
7. import javax.sql.DataSource;  
8. import org.springframework.jdbc.core.JdbcTemplate;  
9. import org.springframework.jdbc.core.PreparedStatementCreator;  
10. import org.springframework.jdbc.core.RowMapper;  
11. import org.springframework.jdbc.support.GeneratedKeyHolder;  
14. import com.logcd.bo.dao.ActorEventDao;  
15. public class ActorEventDaoImpl implements ActorEventDao{  
16.     private JdbcTemplate jdbcTemplate;  
17.     //NamedParameterJdbcTemplate对JdbcTemplate封装，增加了命名参数特性  
18.     private NamedParameterJdbcTemplate namedParameterJdbcTemplate;  
19.     //SimpleJdbcTemplate对JdbcTemplate封装,某些特性要在java5以上才工作  
20.     private SimpleJdbcTemplate simpleJdbcTemplate;  
21.     //简化插入数据操作  
22.     private SimpleJdbcInsert inserActor;  
23.     public void setDataSource(DataSource dataSource){  
24.         this.jdbcTemplate = new JdbcTemplate(dataSource);  
25.         this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);  
26.         this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);  
27.         this.inserActor = new SimpleJdbcInsert(dataSource)  
28.         .withTableName("actors")  
29.         .usingColumns("first_name","last_name")//插入这些字段  
30.         .usingGeneratedKeyColumns("id");//带回生成的id  
31.     }  
33.      * 用SimpleJdbcInsert插入一条记录 
35.     public long inserOneActor(Actor actor){  
36.         Map<String,Object> parameters = new HashMap<String,Object>();  
37.         parameters.put("first_name",actor.getFirstName());  
38.         parameters.put("last_name",actor.getLastName());  
39.         return inserActor.executeAndReturnKey(parameters).longValue();  
46.     public int findCountOfActorsByFirstName(String firstName){  
47.         String sql="select count(0) from actors where first_name = :first_name";  
48.         SqlParameterSource namedParameters = new MapSqlParameterSource("first_name",firstName);  
49.         //Map namedParameter = Collections.singletonMap("first_name",firstName);  
50.         //还有一种Bean封装的方式  
51.         //SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(exampleActor);  
52.         return this.namedParameterJdbcTemplate.queryForInt(sql, namedParameters);  
58.     public void createTableBySQL(String sql) {  
59.         this.jdbcTemplate.execute(sql);  
62.      * 插入记录并返回自动生成的主键Id(MySQL中不行，Oracle可以) 
66.     public KeyHolder insertActor(final Actor actor){  
67.         final String addSql = "insert into actors(first_name,last_name) values (?,?)";  
68.         KeyHolder keyHolder = new GeneratedKeyHolder();  
69.         this.jdbcTemplate.update(new PreparedStatementCreator(){  
70.             public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {  
71.                 PreparedStatement ps =  
72.                     conn.prepareStatement(addSql, new String[]{"id"});//返回id  
73.                 ps.setString(1, actor.getFirstName());  
74.                 ps.setString(2, actor.getLastName());  
75.                 return ps;  
76.             }  
77.         });  
78.         System.out.println(keyHolder.getKey());  
79.         return keyHolder;  
86.     public int operateActor(String sql,Object[] obj){  
87.         return this.jdbcTemplate.update(sql, obj);  
94.     public int findRowCountBySQL(String sql){  
95.         return this.jdbcTemplate.queryForInt(sql);  
102.     public Actor findActorById(long id){  
103.         Actor actor = (Actor) this.jdbcTemplate.queryForObject(  
104.                 "select id,first_name,last_name from actors where id = ?",  
105.                 new Object[]{new Long(id)},   
106.                 new RowMapper(){  
107.                     public Object mapRow(ResultSet rs, int rowNum) throws SQLException {  
108.                         Actor act = new Actor();  
109.                         act.setId(rs.getLong("id"));  
110.                         act.setFirstName(rs.getString("first_name"));  
111.                         act.setLastName(rs.getString("last_Name"));  
112.                         return act;  
113.                     }  
114.                 });  
115.         return actor;  
122.     public Actor findActorByIdSimple(long id){  
123.         String sql = "select id,first_name,last_name from actors where id = ?";  
124.         ParameterizedRowMapper<Actor> mapper = new ParameterizedRowMapper<Actor>(){  
125.             //notice the return type with respect to java 5 covariant return types  
126.             public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {  
127.                 Actor act = new Actor();  
128.                 act.setId(rs.getLong("id"));  
129.                 act.setFirstName(rs.getString("first_name"));  
130.                 act.setLastName(rs.getString("last_Name"));  
131.                 return act;  
133.         };  
134.         return this.simpleJdbcTemplate.queryForObject(sql, mapper, id);  
140.     public List findAllActors(){  
141.         return this.jdbcTemplate.query(  
142.                 "select id,first_name,last_name from actors",  
143.                 new ActorMapper());  
146.      * 定义一个静态内部类，在Dao的方法中被共享 
147.      * @author logcd 
149.     private static final class ActorMapper implements RowMapper{  
150.         public Object mapRow(ResultSet rs, int rowNum) throws SQLException {  
151.             Actor act = new Actor();  
152.             act.setId(rs.getLong("id"));  
153.             act.setFirstName(rs.getString("first_name"));  
154.             act.setLastName(rs.getString("last_Name"));  
155.             return act;  
156.         }  
164.     public int[] updateBatchActors(final List actors){  
165.         int[] updateCounts =this.jdbcTemplate.batchUpdate(  
166.                 "update actors set first_name = ?, last_name = ? where id =? ",   
167.                 new BatchPreparedStatementSetter(){  
168.                     public int getBatchSize() {  
169.                         return actors.size();  
171.                     public void setValues(PreparedStatement ps, int i) throws SQLException {  
172.                         ps.setString(1, ((Actor)actors.get(i)).getFirstName());  
173.                         ps.setString(2, ((Actor)actors.get(i)).getLastName());  
174.                         ps.setLong(3, ((Actor)actors.get(i)).getId());  
177.         return updateCounts;  
184.     public int[] updateBatchActorsSimple(final List<Actor> actors){  
185.         //如果对象数组与占位符出现位置一一对应  
186.         //SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(actors.toArray());  
187.         List<Object[]> batch = new ArrayList<Object[]>();  
188.         for(Actor actor:actors){  
189.             Object[] values = new Object[]{//注意顺序  
190.                     actor.getFirstName(),  
191.                     actor.getLastName(),  
192.                     actor.getId()};  
193.             batch.add(values);  
195.         int[] updateCounts = this.simpleJdbcTemplate.batchUpdate(  
196.                 "update actors set first_name = ?, last_name = ? where id =? ",  
197.                 batch);  

(4)测试 

1. /** 
2.  *  
3.  */  
4. package com.logcd.test;  
5. import org.springframework.context.ApplicationContext;  
6. import org.springframework.context.support.ClassPathXmlApplicationContext;  
10. import com.logcd.bo.dao.ActorJdbcTemplateDao;  
11. import junit.framework.TestCase;  
13.  * @author logcd 
15. public class SpringJdbcTest extends TestCase {  
16.     private ActorEventDao actorEventDao;   
17.     private ActorJdbcTemplateDao actorJdbcTemplateDao;  
18.     protected void setUp() throws Exception {  
19.         super.setUp();  
20.         ApplicationContext context = new ClassPathXmlApplicationContext("springJdbcContext.xml");  
21.         actorEventDao = (ActorEventDao)context.getBean("actorEventDao");  
22.         actorJdbcTemplateDao = (ActorJdbcTemplateDao)context.getBean("actorJdbcTemplateDao");  
24.     protected void tearDown() throws Exception {  
25.         super.tearDown();  
27.     public void testActorEventDao(){  
28.         String creatSql = "create table ACTORS(" +  
29.         "ID int not null auto_increment," +  
30.         "FIRST_NAME varchar(15)," +  
31.         "LAST_NAME varchar(15)," +  
32.         "primary key (ID)" +  
33.         ");" ;  
34.         //建表  
35.         actorEventDao.createTableBySQL(creatSql);  
36.         String addSql = "insert into actors(first_name,last_name) values(?,?);";  
37.         Object[] obj = new Object[]{"wang","jinming"};  
38.         //新增  
39.         System.out.println(actorEventDao.operateActor(addSql, obj));  
40.         String countSql = "select count(0) from actors";  
41.         System.out.println("Count:"+actorEventDao.findRowCountBySQL(countSql));  
42.         System.out.println("Count:"+actorJdbcTemplateDao.findRowCountBySQL(countSql));  
43.         //根据id查找  
44.         Actor actor = actorEventDao.findActorById(1);  
45.         System.out.println("id:"+actor.getId()+"  first_name:"+actor.getFirstName()+"  last_name:"+actor.getLastName());  
46.         //输出所有  
47.         for(Object o:actorEventDao.findAllActors()){  
48.             Actor act = (Actor) o;  
49.             System.out.println("id:"+act.getId()+"  first_name:"+act.getFirstName()+"  last_name:"+act.getLastName());  
51.         Actor newAct=new Actor();  
52.         newAct.setFirstName("jin");  
53.         newAct.setLastName("ming");  
54.         KeyHolder keyHold =actorEventDao.insertActor(newAct);  
55.         System.out.println(keyHold.getKey());//mysql得不到id  
56.         List<Actor> list = new ArrayList<Actor>();  
60.             act.setLastName("www");  
61.             list.add(act);  
63.         actorEventDao.batchUpdateActors(list);  

二、关于操作Blob和Clob问题 

     spring定义了一个以统一的方式操作各种数据库的Lob类型数据的LobCreator(保存的时候用),同时提供了一个LobHandler为操作二进制字段和大文本字段提供统一接口访问。 

(1)配置文件 

Xml代码  

1. <bean id="nativeJdbcExtractor"  
2.  class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"  
3.       lazy-init="true"/>  
4. <bean id="lobHandler"  
5.  class="org.springframework.jdbc.support.lob.OracleLobHandler"  
6.         lazy-init="true"  
7.         p:nativeJdbcExtractor-ref="nativeJdbcExtractor"/>  
8. <bean id="defaultLobHandler"  
9.    class="org.springframework.jdbc.support.lob.DefaultLobHandler"  
10.    lazy-init="true" />  
11. <bean id="jdbcTemplate"  
12. class="org.springframework.jdbc.core.JdbcTemplate"  
13.         p:dataSource-ref="appDS"  
15. <bean id="txMangager"  
16.  class="org.springframework.jdbc.datasource.DataSourceTransactionManager"  
17.         p:dataSource-ref="appDS"/>  
18. <tx:annotation-driven transaction-manager="txMangager" proxy-target-class="true"/>  

(2)读写 

1. @Resource(name = "lobHandler")     
2. private LobHandler lobHandler;     
3. @Resource(name = "jdbcTemplate")     
4. private  JdbcTemplate jdbcTemplate;     
5. public void savePost(final Post post) {       
6.    String sql = " INSERT INTO t_post(post_id,user_id,post_text,post_attach)"    
7.      + " VALUES(?,?,?,?)";     
8.    jdbcTemplate().execute(sql,     
9.      new AbstractLobCreatingPreparedStatementCallback(this.lobHandler) {     
10.          protected void setValues(PreparedStatement ps,     
11.               LobCreator lobCreator) throws SQLException {     
12.                   ps.setInt(1, incre.nextIntValue());      
13.                   ps.setInt(2, post.getUserId());      
14.                   lobCreator.setClobAsString(ps, 3, post.getPostText());     
15.                   lobCreator.setBlobAsBytes(ps, 4, post.getPostAttach());     
17.      });     
18. }     
19. public List findAttachs(final int userId){     
20.    String sql = "SELECT post_id,post_attach FROM t_post where user_id =? and post_attach is not null";     
21.    return jdbcTemplate().query( sql, new Object[] {userId},     
22.       new RowMapper() {     
23.           public Object mapRow(ResultSet rs, int rowNum) throws SQLException {     
24.           Post post = new Post();     
25.           int postId = rs.getInt(1);     
26.           byte[] attach = lobHandler.getBlobAsBytes(rs, 2);     
27.           post.setPostId(postId);     
28.           post.setPostAttach(attach);     
29.           return post;     
30.       }    
31.    });    
32. }