Spring + jdbc +connection pool + transaction

1. 添加lib

<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-tx</artifactId>
			<version>3.2.6.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>3.2.6.RELEASE</version>
		</dependency>

2. 修改spring配置

<?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-3.2.xsd
	http://www.springframework.org/schema/context
	http://www.springframework.org/schema/context/spring-context-3.2.xsd
	http://www.springframework.org/schema/aop
	http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
	http://www.springframework.org/schema/tx
	http://www.springframework.org/schema/tx/spring-tx-3.2.xsd">

	<!-- scan all beans and inject dependence -->
	<context:component-scan base-package="com.myproject"/>
	<!-- add aop support -->
	<aop:aspectj-autoproxy/>
	
	<!-- read from database.properties -->
	<context:property-placeholder location="classpath:database.properties"/>
	<bean id="datasource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
		<property name="driverClassName" value="${driverClassName}"></property>
		<property name="url" value="${url}"/>
	    <property name="username" value="root"/>
	    <property name="password" value="admin"/>
	     <!-- 连接池启动时的初始值 -->
		 <property name="initialSize" value="${initialSize}"/>
		 <!-- 连接池的最大值 -->
		 <property name="maxActive" value="${maxActive}"/>
		 <!-- 最大空闲值.当经过一个高峰时间后，连接池可以慢慢将已经用不到的连接慢慢释放一部分，一直减少到maxIdle为止 -->
		 <property name="maxIdle" value="${maxIdle}"/>
		 <!--  最小空闲值.当空闲的连接数少于阀值时，连接池就会预申请去一些连接，以免洪峰来时来不及申请 -->
		 <property name="minIdle" value="${minIdle}"/>
	</bean>
	
	<!-- add Transaction support -->
	<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="datasource"/>
	</bean>
	<!-- add Transaction support in annotation format -->
	<tx:annotation-driven transaction-manager="txManager"/>
</beans>

3.添加AccountDao

import java.sql.Types;
import javax.annotation.Resource;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
@Repository
public class AccountDaoBean implements AccountDao{
	
	private JdbcTemplate jdbcTemplate;
	
	@Resource
	public void setDataSource(DataSource datasource){
		this.jdbcTemplate=new JdbcTemplate(datasource);
	}
	
	@Override
	public int insertAccount(Account account) {
		String sql="insert into account(username, password) values(?, ?)";
		return this.jdbcTemplate.update(sql, new Object[]{account.getUsername(), account.getPassword()}, 
				new int[]{java.sql.Types.VARCHAR, java.sql.Types.VARCHAR});
	}

	@Override
	public Account queryAccountById(int id) {
		String sql="select * from account where id =?";
		return this.jdbcTemplate.queryForObject(sql, new Object[]{id}, 
				new int[]{java.sql.Types.INTEGER}, new AccountMapper());
	}

	@Override
	public List<Account> queryAccount(Account account) {
		String sql="select * from account where 1=1 ";
		if(account.getUsername()!=null)
			sql=sql+"and username=? ";
		if(account.getPassword()!=null)
			sql=sql+"and password=? ";
		return this.jdbcTemplate.query(sql, new Object[]{account.getUsername(), account.getPassword()}, 
				new int[]{java.sql.Types.VARCHAR, java.sql.Types.VARCHAR}, new AccountMapper());
	}

	@Override
	public int updateAccount(Account account) {
		String sql="update account set username=?, password=? where id=?";
		return this.jdbcTemplate.update(sql, new Object[]{account.getUsername(), account.getPassword(), account.getId()}, 
				new int[]{Types.VARCHAR, Types.VARCHAR, Types.INTEGER});
	}

	@Override
	public int deleteAccount(int id) {
		String sql="delete from account where id=?";
		return this.jdbcTemplate.update(sql, new Object[]{id}, 
				new int[]{Types.INTEGER});
	}
}

4.添加RowMapper

import org.springframework.jdbc.core.RowMapper;
public class AccountMapper implements RowMapper<Account>{

	@Override
	public Account mapRow(ResultSet rs, int index) throws SQLException {
		Account account = new Account();
		account.setId(rs.getInt("id"));
		account.setUsername("username");
		account.setPassword("password");
		return account;
	}
}

添加Service

import javax.annotation.Resource;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
@Service
@Transactional
public class AccountService {
	
	@Resource
	private AccountDao accountDao;
	
	//don't need to add transaction in query method 
	@Transactional(propagation=Propagation.NOT_SUPPORTED)
	public Account Login(String username, String password){
		System.out.println(username + " want  to login.");
		Account account = new Account();
		account.setUsername(username);
		account.setPassword(password);
		List<Account> list=  accountDao.queryAccount(account);
		if(list.size()==1)
			return list.get(0);
		else 
			return null;
	}
	
	//in method which already has "throws", we must add rollbackFor if want to rollback for this exception,
	//otherwise the method will not be rollback
	@Transactional(rollbackFor=Exception.class)
	public void reqister(Account account) {
		accountDao.insertAccount(account);
		throw new RuntimeException("==no register");
	}
}

测试

@Test
	public void test(){
		try {
			ApplicationContext ac=new ClassPathXmlApplicationContext("spring.xml");
			AccountService accountService =(AccountService)ac.getBean("accountService");
			
			Account account = new Account();
			account.setUsername("tom");
			account.setPassword("116");
			accountService.reqister(account);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

注意：

• 1. 用@Transactional修饰的class默认情况下会对每个method加上独立的事务，当一个method被另一个method调用时，事务会合并。
• 2. 默认情况下方法内抛出runtime异常，会rollback。 但如果方法定义中有throws *Exception, 不会对抛出的*Exception回滚； 如果需要回滚*Exception，需要对方法加上注释@Transactional(rollbackFor=*Exception.class)。
• 3. 原理同#2， 我们也可以使用@Transactional(noRollbackFor=*Exception.class)指定不回滚某种异常。
• 4. 在#1中指出@Transactional修饰的class默认情况下会对每个method加上独立的事务，对于查询方法我们可以使用注释@Transactional(propagation=Propagation.NOT_SUPPORTED)申明该方法不需要事务管理。  propagation 还有其他属性（见附件）：