`

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

	<description>springApp</description>	
	<!-- dataSource for MySQL -->
	<bean id="dataSource"
		class="org.apache.commons.dbcp.BasicDataSource"
		destroy-method="close">
		<property name="driverClassName"
			value="com.mysql.jdbc.Driver" />
		<property name="url"
			value="jdbc:mysql://localhost:3306/springapp" />
		<property name="username" value="root" />
		<property name="password" value="****" />
	</bean>	

	<bean id = "TransactionManager" 
         class = "org.springframework.jdbc.datasource.DataSourceTransactionManager"> 
         <property name = "dataSource" ref="dataSource"/> 
    </bean>
    
    <!--1:配置一个JdbcTemplate实例，并将这个“共享的”，“安全的”实例注入到不同的DAO类中去-->
    <bean id = "jdbcTemplate" 
         class = "org.springframework.jdbc.core.JdbcTemplate"> 
         <property name = "dataSource" ref="dataSource"/> 
    </bean>

    <bean id = "actorJdbcTemplateDao" 
         class = "com.logcd.bo.dao.impl.ActorJdbcTemplateDaoImpl"> 
         <property name="jdbcTemplate" ref="jdbcTemplate"/> 
    </bean>
    
    <!--2:将共享的DataSource实例注入到DAO中，JdbcTemplate实例在DataSource的setter方法中被创建-->
    <bean id = "actorEventDao" 
         class = "com.logcd.bo.dao.impl.ActorEventDaoImpl"> 
         <property name = "dataSource" ref="dataSource"/> 
    </bean>

    <!--利用了拦截器的原理。-->   
   <bean id="transactionInterceptor"  
         class="org.springframework.transaction.interceptor.TransactionInterceptor">   
        <property name="transactionManager">    
                  <ref bean="transactionManager" />   
        </property>   
    <!-- 配置事务属性 -->
   <property name="transactionAttributes">   
        <props>   
            <prop key="delete*">PROPAGATION_REQUIRED</prop>
            <prop key="operate*">PROPAGATION_REQUIRED,-Exception</prop>   
            <prop key="insert*">PROPAGATION_REQUIRED,-Exception</prop>   
            <prop key="update*">PROPAGATION_REQUIRED,-Exception</prop>   
            <prop key="save*">PROPAGATION_REQUIRED</prop>   
            <prop key="find*">PROPAGATION_REQUIRED,readOnly</prop>   
       </props>   
   </property>   
   </bean>   
   <bean id="txProxy"  
         class="org.springframework.aop.framework.autoproxy.BeanNameAutoProxyCreator">   
        <property name="beanNames">   
          <list>   
             <value>*Dao*</value><!--只是为了测试，一般为service-->
          </list>   
        </property>   
        <property name="interceptorNames">   
          <list>   
             <value>transactionInterceptor</value>   
          </list>   
        </property>   
   </bean>  

</beans>


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

Java代码 复制代码 收藏代码
  1. package com.logcd.bo.dao;   
  2.   
  3. import java.util.List;   
  4.   
  5. import org.springframework.jdbc.support.KeyHolder;   
  6.   
  7. import com.logcd.bo.Actor;   
  8.   
  9. public interface ActorEventDao {   
  10.     /**  
  11.      * 根据SQL建表  
  12.      * @param sql  
  13.      */  
  14.     public void createTableBySQL(String sql);   
  15.   
  16.     /**  
  17.      * 统计firstName相同的总数  
  18.      * @param firstName  
  19.      * @return  
  20.      */  
  21.     public int findCountOfActorsByFirstName(String firstName);   
  22.   
  23.     /**  
  24.      * 插入记录并返回自动生成的主键Id  
  25.      * @param ps  
  26.      * @return  
  27.      */  
  28.     public KeyHolder insertActor(final Actor actor);   
  29.   
  30.     /**  
  31.      * 用SimpleJdbcInsert插入一条记录:mysql测试成功  
  32.      */  
  33.     public long inserOneActor(Actor actor);   
  34.        
  35.     /**  
  36.      * 插入/更新/删除数据  
  37.      * @param sql 有参数语句  
  38.      * @param obj 参数值数组  
  39.      */  
  40.     public int operateActor(String sql,Object[] obj);   
  41.   
  42.     /**  
  43.      * 根据SQL查询记录总数  
  44.      * @param sql  
  45.      * @return  
  46.      */  
  47.     public int findRowCountBySQL(String sql);   
  48.   
  49.     /**  
  50.      * 根据Id查找指定对象  
  51.      * @param id  
  52.      * @return  
  53.      */  
  54.     public Actor findActorById(long id);   
  55.   
  56.     /**  
  57.      * 根据Id查找指定对象  
  58.      * @param id  
  59.      * @return  
  60.      */  
  61.     public Actor findActorByIdSimple(long id);   
  62.   
  63.     /**  
  64.      * 返回所有对象  
  65.      * @return  
  66.      */  
  67.     public List findAllActors();   
  68.   
  69.             /**  
  70.      * 批量更新  
  71.      * @param actors  
  72.      * @return  
  73.      */  
  74.     public int[] updateBatchActors(final List actors);   
  75.   
  76.     /**  
  77.      * 批量更新  
  78.      * @param actors  
  79.      * @return  
  80.      */  
  81.     public int[] updateBatchActorsSimple(final List<Actor> actors);   
  82.   
  83. }  
package com.logcd.bo.dao;

import java.util.List;

import org.springframework.jdbc.support.KeyHolder;

import com.logcd.bo.Actor;

public interface ActorEventDao {
	/**
	 * 根据SQL建表
	 * @param sql
	 */
	public void createTableBySQL(String sql);

	/**
	 * 统计firstName相同的总数
	 * @param firstName
	 * @return
	 */
	public int findCountOfActorsByFirstName(String firstName);

	/**
	 * 插入记录并返回自动生成的主键Id
	 * @param ps
	 * @return
	 */
	public KeyHolder insertActor(final Actor actor);

	/**
	 * 用SimpleJdbcInsert插入一条记录:mysql测试成功
	 */
	public long inserOneActor(Actor actor);
	
	/**
	 * 插入/更新/删除数据
	 * @param sql 有参数语句
	 * @param obj 参数值数组
	 */
	public int operateActor(String sql,Object[] obj);

	/**
	 * 根据SQL查询记录总数
	 * @param sql
	 * @return
	 */
	public int findRowCountBySQL(String sql);

	/**
	 * 根据Id查找指定对象
	 * @param id
	 * @return
	 */
	public Actor findActorById(long id);

	/**
	 * 根据Id查找指定对象
	 * @param id
	 * @return
	 */
	public Actor findActorByIdSimple(long id);

	/**
	 * 返回所有对象
	 * @return
	 */
	public List findAllActors();

         	/**
	 * 批量更新
	 * @param actors
	 * @return
	 */
	public int[] updateBatchActors(final List actors);

	/**
	 * 批量更新
	 * @param actors
	 * @return
	 */
	public int[] updateBatchActorsSimple(final List<Actor> actors);

}


(3)接口实现

Java代码 复制代码 收藏代码
  1. package com.logcd.bo.dao.impl;   
  2.   
  3. import java.sql.Connection;   
  4. import java.sql.PreparedStatement;   
  5. import java.sql.ResultSet;   
  6. import java.sql.SQLException;   
  7. import java.util.List;   
  8.   
  9. import javax.sql.DataSource;   
  10.   
  11. import org.springframework.jdbc.core.JdbcTemplate;   
  12. import org.springframework.jdbc.core.PreparedStatementCreator;   
  13. import org.springframework.jdbc.core.RowMapper;   
  14. import org.springframework.jdbc.support.GeneratedKeyHolder;   
  15. import org.springframework.jdbc.support.KeyHolder;   
  16.   
  17. import com.logcd.bo.Actor;   
  18. import com.logcd.bo.dao.ActorEventDao;   
  19.   
  20. public class ActorEventDaoImpl implements ActorEventDao{   
  21.        
  22.     private JdbcTemplate jdbcTemplate;   
  23.        
  24.     //NamedParameterJdbcTemplate对JdbcTemplate封装，增加了命名参数特性   
  25.     private NamedParameterJdbcTemplate namedParameterJdbcTemplate;   
  26.   
  27.     //SimpleJdbcTemplate对JdbcTemplate封装,某些特性要在java5以上才工作   
  28.     private SimpleJdbcTemplate simpleJdbcTemplate;   
  29.        
  30.     //简化插入数据操作   
  31.     private SimpleJdbcInsert inserActor;   
  32.        
  33.     public void setDataSource(DataSource dataSource){   
  34.         this.jdbcTemplate = new JdbcTemplate(dataSource);   
  35.         this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);   
  36.         this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);   
  37.         this.inserActor = new SimpleJdbcInsert(dataSource)   
  38.         .withTableName("actors")   
  39.         .usingColumns("first_name","last_name")//插入这些字段   
  40.         .usingGeneratedKeyColumns("id");//带回生成的id   
  41.     }   
  42.   
  43.     /**  
  44.      * 用SimpleJdbcInsert插入一条记录  
  45.      */  
  46.     public long inserOneActor(Actor actor){   
  47.         Map<String,Object> parameters = new HashMap<String,Object>();   
  48.         parameters.put("first_name",actor.getFirstName());   
  49.         parameters.put("last_name",actor.getLastName());   
  50.         return inserActor.executeAndReturnKey(parameters).longValue();   
  51.     }   
  52.        
  53.     /**  
  54.      * 统计firstName相同的总数  
  55.      * @param firstName  
  56.      * @return  
  57.      */  
  58.     public int findCountOfActorsByFirstName(String firstName){   
  59.         String sql="select count(0) from actors where first_name = :first_name";   
  60.         SqlParameterSource namedParameters = new MapSqlParameterSource("first_name",firstName);   
  61.         //Map namedParameter = Collections.singletonMap("first_name",firstName);   
  62.         //还有一种Bean封装的方式   
  63.         //SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(exampleActor);   
  64.         return this.namedParameterJdbcTemplate.queryForInt(sql, namedParameters);   
  65.     }   
  66.   
  67.     /**  
  68.      * 根据SQL建表  
  69.      * @param sql  
  70.      */  
  71.     public void createTableBySQL(String sql) {   
  72.         this.jdbcTemplate.execute(sql);   
  73.     }   
  74.        
  75.     /**  
  76.      * 插入记录并返回自动生成的主键Id(MySQL中不行，Oracle可以)  
  77.      * @param ps  
  78.      * @return  
  79.      */  
  80.     public KeyHolder insertActor(final Actor actor){   
  81.         final String addSql = "insert into actors(first_name,last_name) values (?,?)";   
  82.         KeyHolder keyHolder = new GeneratedKeyHolder();   
  83.         this.jdbcTemplate.update(new PreparedStatementCreator(){   
  84.             public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {   
  85.                 PreparedStatement ps =   
  86.                     conn.prepareStatement(addSql, new String[]{"id"});//返回id   
  87.                 ps.setString(1, actor.getFirstName());   
  88.                 ps.setString(2, actor.getLastName());   
  89.                 return ps;   
  90.             }   
  91.                
  92.         });   
  93.         System.out.println(keyHolder.getKey());   
  94.         return keyHolder;   
  95.     }   
  96.        
  97.     /**  
  98.      * 插入/更新/删除数据  
  99.      * @param sql 有参数语句  
  100.      * @param obj 参数值数组  
  101.      */  
  102.     public int operateActor(String sql,Object[] obj){   
  103.         return this.jdbcTemplate.update(sql, obj);   
  104.     }   
  105.   
  106.     /**  
  107.      * 根据SQL查询记录总数  
  108.      * @param sql  
  109.      * @return  
  110.      */  
  111.     public int findRowCountBySQL(String sql){   
  112.         return this.jdbcTemplate.queryForInt(sql);   
  113.     }   
  114.        
  115.     /**  
  116.      * 根据Id查找指定对象  
  117.      * @param id  
  118.      * @return  
  119.      */  
  120.     public Actor findActorById(long id){   
  121.         Actor actor = (Actor) this.jdbcTemplate.queryForObject(   
  122.                 "select id,first_name,last_name from actors where id = ?",   
  123.                 new Object[]{new Long(id)},    
  124.                 new RowMapper(){   
  125.   
  126.                     public Object 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;   
  132.                     }   
  133.                        
  134.                 });   
  135.         return actor;   
  136.     }   
  137.   
  138.   
  139.     /**  
  140.      * 根据Id查找指定对象  
  141.      * @param id  
  142.      * @return  
  143.      */  
  144.     public Actor findActorByIdSimple(long id){   
  145.         String sql = "select id,first_name,last_name from actors where id = ?";   
  146.            
  147.         ParameterizedRowMapper<Actor> mapper = new ParameterizedRowMapper<Actor>(){   
  148.             //notice the return type with respect to java 5 covariant return types   
  149.             public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {   
  150.                 Actor act = new Actor();   
  151.                 act.setId(rs.getLong("id"));   
  152.                 act.setFirstName(rs.getString("first_name"));   
  153.                 act.setLastName(rs.getString("last_Name"));   
  154.                 return act;   
  155.             }   
  156.         };   
  157.            
  158.         return this.simpleJdbcTemplate.queryForObject(sql, mapper, id);   
  159.     }   
  160.        
  161.     /**  
  162.      * 返回所有对象  
  163.      * @return  
  164.      */  
  165.     public List findAllActors(){   
  166.         return this.jdbcTemplate.query(   
  167.                 "select id,first_name,last_name from actors",   
  168.                 new ActorMapper());   
  169.     }   
  170.        
  171.     /**  
  172.      * 定义一个静态内部类，在Dao的方法中被共享  
  173.      * @author logcd  
  174.      */  
  175.     private static final class ActorMapper implements RowMapper{   
  176.   
  177.         public Object mapRow(ResultSet rs, int rowNum) throws SQLException {   
  178.             Actor act = new Actor();   
  179.             act.setId(rs.getLong("id"));   
  180.             act.setFirstName(rs.getString("first_name"));   
  181.             act.setLastName(rs.getString("last_Name"));   
  182.             return act;   
  183.         }   
  184.            
  185.     }   
  186. }   
  187.   
  188.     /**  
  189.      * 批量更新  
  190.      * @param actors  
  191.      * @return  
  192.      */  
  193.     public int[] updateBatchActors(final List actors){   
  194.         int[] updateCounts =this.jdbcTemplate.batchUpdate(   
  195.                 "update actors set first_name = ?, last_name = ? where id =? ",    
  196.                 new BatchPreparedStatementSetter(){   
  197.   
  198.                     public int getBatchSize() {   
  199.                         return actors.size();   
  200.                     }   
  201.   
  202.                     public void setValues(PreparedStatement ps, int i) throws SQLException {   
  203.                         ps.setString(1, ((Actor)actors.get(i)).getFirstName());   
  204.                         ps.setString(2, ((Actor)actors.get(i)).getLastName());   
  205.                         ps.setLong(3, ((Actor)actors.get(i)).getId());   
  206.                     }   
  207.                        
  208.                 });   
  209.         return updateCounts;   
  210.     }   
  211.   
  212.     /**  
  213.      * 批量更新  
  214.      * @param actors  
  215.      * @return  
  216.      */  
  217.     public int[] updateBatchActorsSimple(final List<Actor> actors){   
  218.         //如果对象数组与占位符出现位置一一对应   
  219.         //SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(actors.toArray());   
  220.         List<Object[]> batch = new ArrayList<Object[]>();   
  221.         for(Actor actor:actors){   
  222.             Object[] values = new Object[]{//注意顺序   
  223.                     actor.getFirstName(),   
  224.                     actor.getLastName(),   
  225.                     actor.getId()};   
  226.             batch.add(values);   
  227.         }   
  228.         int[] updateCounts = this.simpleJdbcTemplate.batchUpdate(   
  229.                 "update actors set first_name = ?, last_name = ? where id =? ",   
  230.                 batch);   
  231.         return updateCounts;   
  232.     }  
package com.logcd.bo.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.logcd.bo.Actor;
import com.logcd.bo.dao.ActorEventDao;

public class ActorEventDaoImpl implements ActorEventDao{
	
	private JdbcTemplate jdbcTemplate;
	
	//NamedParameterJdbcTemplate对JdbcTemplate封装，增加了命名参数特性
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

	//SimpleJdbcTemplate对JdbcTemplate封装,某些特性要在java5以上才工作
	private SimpleJdbcTemplate simpleJdbcTemplate;
	
	//简化插入数据操作
	private SimpleJdbcInsert inserActor;
	
	public void setDataSource(DataSource dataSource){
		this.jdbcTemplate = new JdbcTemplate(dataSource);
		this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
		this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
		this.inserActor = new SimpleJdbcInsert(dataSource)
		.withTableName("actors")
		.usingColumns("first_name","last_name")//插入这些字段
		.usingGeneratedKeyColumns("id");//带回生成的id
	}

	/**
	 * 用SimpleJdbcInsert插入一条记录
	 */
	public long inserOneActor(Actor actor){
		Map<String,Object> parameters = new HashMap<String,Object>();
		parameters.put("first_name",actor.getFirstName());
		parameters.put("last_name",actor.getLastName());
		return inserActor.executeAndReturnKey(parameters).longValue();
	}
	
	/**
	 * 统计firstName相同的总数
	 * @param firstName
	 * @return
	 */
	public int findCountOfActorsByFirstName(String firstName){
		String sql="select count(0) from actors where first_name = :first_name";
		SqlParameterSource namedParameters = new MapSqlParameterSource("first_name",firstName);
		//Map namedParameter = Collections.singletonMap("first_name",firstName);
		//还有一种Bean封装的方式
		//SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(exampleActor);
		return this.namedParameterJdbcTemplate.queryForInt(sql, namedParameters);
	}

	/**
	 * 根据SQL建表
	 * @param sql
	 */
	public void createTableBySQL(String sql) {
		this.jdbcTemplate.execute(sql);
	}
	
	/**
	 * 插入记录并返回自动生成的主键Id(MySQL中不行，Oracle可以)
	 * @param ps
	 * @return
	 */
	public KeyHolder insertActor(final Actor actor){
		final String addSql = "insert into actors(first_name,last_name) values (?,?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		this.jdbcTemplate.update(new PreparedStatementCreator(){
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps =
					conn.prepareStatement(addSql, new String[]{"id"});//返回id
				ps.setString(1, actor.getFirstName());
				ps.setString(2, actor.getLastName());
				return ps;
			}
			
		});
		System.out.println(keyHolder.getKey());
		return keyHolder;
	}
	
	/**
	 * 插入/更新/删除数据
	 * @param sql 有参数语句
	 * @param obj 参数值数组
	 */
	public int operateActor(String sql,Object[] obj){
		return this.jdbcTemplate.update(sql, obj);
	}

	/**
	 * 根据SQL查询记录总数
	 * @param sql
	 * @return
	 */
	public int findRowCountBySQL(String sql){
		return this.jdbcTemplate.queryForInt(sql);
	}
	
	/**
	 * 根据Id查找指定对象
	 * @param id
	 * @return
	 */
	public Actor findActorById(long id){
		Actor actor = (Actor) this.jdbcTemplate.queryForObject(
				"select id,first_name,last_name from actors where id = ?",
				new Object[]{new Long(id)}, 
				new RowMapper(){

					public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
						Actor act = new Actor();
						act.setId(rs.getLong("id"));
						act.setFirstName(rs.getString("first_name"));
						act.setLastName(rs.getString("last_Name"));
						return act;
					}
					
				});
		return actor;
	}


	/**
	 * 根据Id查找指定对象
	 * @param id
	 * @return
	 */
	public Actor findActorByIdSimple(long id){
		String sql = "select id,first_name,last_name from actors where id = ?";
		
		ParameterizedRowMapper<Actor> mapper = new ParameterizedRowMapper<Actor>(){
			//notice the return type with respect to java 5 covariant return types
			public Actor mapRow(ResultSet rs, int rowNum) throws SQLException {
				Actor act = new Actor();
				act.setId(rs.getLong("id"));
				act.setFirstName(rs.getString("first_name"));
				act.setLastName(rs.getString("last_Name"));
				return act;
			}
		};
		
		return this.simpleJdbcTemplate.queryForObject(sql, mapper, id);
	}
	
	/**
	 * 返回所有对象
	 * @return
	 */
	public List findAllActors(){
		return this.jdbcTemplate.query(
				"select id,first_name,last_name from actors",
				new ActorMapper());
	}
	
	/**
	 * 定义一个静态内部类，在Dao的方法中被共享
	 * @author logcd
	 */
	private static final class ActorMapper implements RowMapper{

		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			Actor act = new Actor();
			act.setId(rs.getLong("id"));
			act.setFirstName(rs.getString("first_name"));
			act.setLastName(rs.getString("last_Name"));
			return act;
		}
		
	}
}

	/**
	 * 批量更新
	 * @param actors
	 * @return
	 */
	public int[] updateBatchActors(final List actors){
		int[] updateCounts =this.jdbcTemplate.batchUpdate(
				"update actors set first_name = ?, last_name = ? where id =? ", 
				new BatchPreparedStatementSetter(){

					public int getBatchSize() {
						return actors.size();
					}

					public void setValues(PreparedStatement ps, int i) throws SQLException {
						ps.setString(1, ((Actor)actors.get(i)).getFirstName());
						ps.setString(2, ((Actor)actors.get(i)).getLastName());
						ps.setLong(3, ((Actor)actors.get(i)).getId());
					}
					
				});
		return updateCounts;
	}

	/**
	 * 批量更新
	 * @param actors
	 * @return
	 */
	public int[] updateBatchActorsSimple(final List<Actor> actors){
		//如果对象数组与占位符出现位置一一对应
		//SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(actors.toArray());
		List<Object[]> batch = new ArrayList<Object[]>();
		for(Actor actor:actors){
			Object[] values = new Object[]{//注意顺序
					actor.getFirstName(),
					actor.getLastName(),
					actor.getId()};
			batch.add(values);
		}
		int[] updateCounts = this.simpleJdbcTemplate.batchUpdate(
				"update actors set first_name = ?, last_name = ? where id =? ",
				batch);
		return updateCounts;
	}


(4)测试

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

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.support.KeyHolder;

import com.logcd.bo.Actor;
import com.logcd.bo.dao.ActorEventDao;
import com.logcd.bo.dao.ActorJdbcTemplateDao;

import junit.framework.TestCase;

/**
 * @author logcd
 */
public class SpringJdbcTest extends TestCase {

	private ActorEventDao actorEventDao; 
	private ActorJdbcTemplateDao actorJdbcTemplateDao;
	
	protected void setUp() throws Exception {
		super.setUp();
		ApplicationContext context = new ClassPathXmlApplicationContext("springJdbcContext.xml");
		actorEventDao = (ActorEventDao)context.getBean("actorEventDao");
		actorJdbcTemplateDao = (ActorJdbcTemplateDao)context.getBean("actorJdbcTemplateDao");
	}

	protected void tearDown() throws Exception {
		super.tearDown();
	}

	public void testActorEventDao(){
		String creatSql = "create table ACTORS(" +
		"ID int not null auto_increment," +
		"FIRST_NAME varchar(15)," +
		"LAST_NAME varchar(15)," +
		"primary key (ID)" +
		");" ;
		//建表
		actorEventDao.createTableBySQL(creatSql);
		
		String addSql = "insert into actors(first_name,last_name) values(?,?);";
		Object[] obj = new Object[]{"wang","jinming"};
		//新增
		System.out.println(actorEventDao.operateActor(addSql, obj));
	
		String countSql = "select count(0) from actors";
		System.out.println("Count:"+actorEventDao.findRowCountBySQL(countSql));
		System.out.println("Count:"+actorJdbcTemplateDao.findRowCountBySQL(countSql));
		//根据id查找
		Actor actor = actorEventDao.findActorById(1);
		System.out.println("id:"+actor.getId()+"  first_name:"+actor.getFirstName()+"  last_name:"+actor.getLastName());
		//输出所有
		for(Object o:actorEventDao.findAllActors()){
			Actor act = (Actor) o;
			System.out.println("id:"+act.getId()+"  first_name:"+act.getFirstName()+"  last_name:"+act.getLastName());
		}
		
		Actor newAct=new Actor();
		newAct.setFirstName("jin");
		newAct.setLastName("ming");
		KeyHolder keyHold =actorEventDao.insertActor(newAct);
		System.out.println(keyHold.getKey());//mysql得不到id

		List<Actor> list = new ArrayList<Actor>();
		for(Object o:actorEventDao.findAllActors()){
			Actor act = (Actor) o;
			System.out.println("id:"+act.getId()+"  first_name:"+act.getFirstName()+"  last_name:"+act.getLastName());
			act.setLastName("www");
			list.add(act);
		}
		actorEventDao.batchUpdateActors(list);
		for(Object o:actorEventDao.findAllActors()){
			Actor act = (Actor) o;
			System.out.println("id:"+act.getId()+"  first_name:"+act.getFirstName()+"  last_name:"+act.getLastName());
		}
	}
}


二、关于操作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.   
  5. <bean id="lobHandler"  
  6.  class="org.springframework.jdbc.support.lob.OracleLobHandler"  
  7.         lazy-init="true"  
  8.         p:nativeJdbcExtractor-ref="nativeJdbcExtractor"/>  
  9.   
  10. <bean id="defaultLobHandler"  
  11.    class="org.springframework.jdbc.support.lob.DefaultLobHandler"  
  12.    lazy-init="true" />  
  13.   
  14. <bean id="jdbcTemplate"  
  15. class="org.springframework.jdbc.core.JdbcTemplate"  
  16.         p:dataSource-ref="appDS"  
  17.         p:nativeJdbcExtractor-ref="nativeJdbcExtractor"/>  
  18.              
  19. <bean id="txMangager"  
  20.  class="org.springframework.jdbc.datasource.DataSourceTransactionManager"  
  21.         p:dataSource-ref="appDS"/>  
  22.   
  23. <tx:annotation-driven transaction-manager="txMangager" proxy-target-class="true"/>   
<bean id="nativeJdbcExtractor"
 class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"
      lazy-init="true"/>

<bean id="lobHandler"
 class="org.springframework.jdbc.support.lob.OracleLobHandler"
        lazy-init="true"
        p:nativeJdbcExtractor-ref="nativeJdbcExtractor"/>

<bean id="defaultLobHandler"
   class="org.springframework.jdbc.support.lob.DefaultLobHandler"
   lazy-init="true" />

<bean id="jdbcTemplate"
class="org.springframework.jdbc.core.JdbcTemplate"
        p:dataSource-ref="appDS"
        p:nativeJdbcExtractor-ref="nativeJdbcExtractor"/>
	      
<bean id="txMangager"
 class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
        p:dataSource-ref="appDS"/>

<tx:annotation-driven transaction-manager="txMangager" proxy-target-class="true"/>



(2)读写

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

您还没有登录,请您登录后再发表评论

相关推荐

Magicbox
Global site tag (gtag.js) - Google Analytics