spring jdbcTemplate使用

一、使用示例
(1)springJdbcContext.xml

<?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)

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)接口实现

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)测试

/**
 * 
 */
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)配置文件

<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)读写

@Resource(name = "lobHandler")   
private LobHandler lobHandler;   
  
@Resource(name = "jdbcTemplate")   
private  JdbcTemplate jdbcTemplate;   
  
public void savePost(final Post post) {     
   String sql = " INSERT INTO t_post(post_id,user_id,post_text,post_attach)"  
     + " VALUES(?,?,?,?)";   
   jdbcTemplate().execute(sql,   
     new AbstractLobCreatingPreparedStatementCallback(this.lobHandler) {   
         protected void setValues(PreparedStatement ps,   
              LobCreator lobCreator) throws SQLException {   
                  ps.setInt(1, incre.nextIntValue());    
                  ps.setInt(2, post.getUserId());    
                  lobCreator.setClobAsString(ps, 3, post.getPostText());   
                  lobCreator.setBlobAsBytes(ps, 4, post.getPostAttach());   
        }
     });   
}   
  
public List findAttachs(final int userId){   
   String sql = "SELECT post_id,post_attach FROM t_post where user_id =? and post_attach is not null";   
   return jdbcTemplate().query( sql, new Object[] {userId},   
      new RowMapper() {   
          public Object mapRow(ResultSet rs, int rowNum) throws SQLException {   
          Post post = new Post();   
          int postId = rs.getInt(1);   
          byte[] attach = lobHandler.getBlobAsBytes(rs, 2);   
          post.setPostId(postId);   
          post.setPostAttach(attach);   
          return post;   
      }  
   });  
}  

评论

11 楼 ruyi0127 2015-10-08  

楼主代码不全，ActorJdbcTemplateDao  和 Actor 类均没有

10 楼 ruyi0127 2015-10-08  

楼主没有把类ActorJdbcTemplateDao写出来。。。  

9 楼 laodao278838 2013-12-27  

  ，值得收藏！

8 楼 知了ing 2013-04-25  

不错

7 楼 fkpj_bit 2011-11-03  

楼主好像没有把类ActorJdbcTemplateDao写出来。。。

6 楼 银杏树 2010-10-09  

收藏，必须的

5 楼 lxsgoodluck 2009-05-24  

wxq594808632 写道

lxsgoodluck 写道
用GeneratedKeyHolder的地方说“MySQL中不行，Oracle可以”，是不对的，spring中isReturnGeneratedKeys默认是false, 需要setReturnGeneratedKeys(true) 方法怎么写?哪个对象调用的?

SqlUpdate及其扩展类的对象
可以看下我这里的帖子http://www.iteye.com/post/1023484

4 楼 wxq594808632 2009-05-15  

lxsgoodluck 写道

用GeneratedKeyHolder的地方说“MySQL中不行，Oracle可以”，是不对的，spring中isReturnGeneratedKeys默认是false, 需要setReturnGeneratedKeys(true)

方法怎么写?哪个对象调用的?

3 楼 log_cd 2009-04-02  

The basic idea is to use a SqlUpdate object, set the ReturnGeneratedKeys property to true, and then to pass in a KeyHolder object to extract the generated keys. I hope this helps anyone else using MSSQL.

For example, given the schema:


CREATE TABLE [scheduler_jobs] (
[job_id] [int] IDENTITY (1, 1) NOT NULL ,
[job_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[job_detail] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[class_name] [varchar] (255) NULL ,
[start] [datetime] NULL ,
[increment_millis] [bigint] NULL
) ON [PRIMARY]

use:

SqlUpdate su = new SqlUpdate();
su.setDataSource(getDataSource());
su.setSql(sql);
su.declareParameter(new SqlParameter(Types.VARCHAR));
su.declareParameter(new SqlParameter(Types.VARCHAR));
su.declareParameter(new SqlParameter(Types.VARCHAR));
su.declareParameter(new SqlParameter(Types.TIMESTAMP));
su.declareParameter(new SqlParameter(Types.BIGINT));
su.setReturnGeneratedKeys(true);
su.compile();

Object[] params = new Object[]{job.getJobName(), job.getJobDetail(), job.getClassName(), job.getStart(), new Long(job.getIncrementMillis())};
KeyHolder keyHolder = new GeneratedKeyHolder();
su.update(params,keyHolder);
int id = keyHolder.getKey().intValue();

2 楼 log_cd 2009-04-02  

谢谢指正！

1 楼 lxsgoodluck 2009-04-02  

用GeneratedKeyHolder的地方说“MySQL中不行，Oracle可以”，是不对的，spring中isReturnGeneratedKeys默认是false, 需要setReturnGeneratedKeys(true)