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.xsdhttp://www.springframework.org/schema/contexthttp://www.springframework.org/schema/context/spring-context-3.2.xsdhttp://www.springframework.org/schema/aophttp://www.springframework.org/schema/aop/spring-aop-3.2.xsdhttp://www.springframework.org/schema/txhttp://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;@Repositorypublic class AccountDaoBean implements AccountDao{private JdbcTemplate jdbcTemplate;@Resourcepublic void setDataSource(DataSource datasource){this.jdbcTemplate=new JdbcTemplate(datasource);}@Overridepublic 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});}@Overridepublic 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());}@Overridepublic 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());}@Overridepublic 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});}@Overridepublic 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>{@Overridepublic 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@Transactionalpublic class AccountService {@Resourceprivate 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");}}

测试

@Testpublic 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 还有其他属性（见附件）：