Spring与C3p0连接数据库对事务操作

maven包：

 1 <properties>
 2     <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
 3     <maven.compiler.source>1.7</maven.compiler.source>
 4     <maven.compiler.target>1.7</maven.compiler.target>
 5     <spring.version>4.3.24.RELEASE</spring.version>
 6   </properties>
 7 
 8   <dependencies>
 9     <dependency>
10       <groupId>junit</groupId>
11       <artifactId>junit</artifactId>
12       <version>4.11</version>
13       <scope>test</scope>
14     </dependency>
15     <!-- Spring -->
16     <dependency>
17       <groupId>org.springframework</groupId>
18       <artifactId>spring-core</artifactId>
19       <version>${spring.version}</version>
20     </dependency>
21     <dependency>
22       <groupId>org.springframework</groupId>
23       <artifactId>spring-context</artifactId>
24       <version>${spring.version}</version>
25     </dependency>
26     <dependency>
27       <groupId>org.springframework</groupId>
28       <artifactId>spring-web</artifactId>
29       <version>${spring.version}</version>
30     </dependency>
31     <dependency>
32       <groupId>org.springframework</groupId>
33       <artifactId>spring-context-support</artifactId>
34       <version>${spring.version}</version>
35     </dependency>
36     <dependency>
37       <groupId>org.springframework</groupId>
38       <artifactId>spring-webmvc</artifactId>
39       <version>${spring.version}</version>
40     </dependency>
41     <dependency>
42       <groupId>org.springframework</groupId>
43       <artifactId>spring-test</artifactId>
44       <version>${spring.version}</version>
45     </dependency>
46     <!-- aop -->
47     <dependency>
48       <groupId>org.springframework</groupId>
49       <artifactId>spring-aop</artifactId>
50       <version>${spring.version}</version>
51     </dependency>
52     <dependency>
53       <groupId>org.aspectj</groupId>
54       <artifactId>aspectjweaver</artifactId>
55       <version>1.8.13</version>
56     </dependency>
57     <dependency>
58       <groupId>cglib</groupId>
59       <artifactId>cglib</artifactId>
60       <version>3.2.4</version>
61     </dependency>
62     <!-- spring jdbc -->
63     <dependency>
64       <groupId>mysql</groupId>
65       <artifactId>mysql-connector-java</artifactId>
66       <version>5.1.47</version>
67     </dependency>
68     <dependency>
69       <groupId>org.springframework</groupId>
70       <artifactId>spring-jdbc</artifactId>
71       <version>${spring.version}</version>
72     </dependency>
73     <dependency>
74       <groupId>org.springframework</groupId>
75       <artifactId>spring-tx</artifactId>
76       <version>${spring.version}</version>
77     </dependency>
78     <!-- C3p0-->
79     <dependency>
80       <groupId>com.mchange</groupId>
81       <artifactId>c3p0</artifactId>
82       <version>0.9.5.4</version>
83     </dependency>
84   </dependencies>

View Code

applicationContext.xml：

C3p0连接配置：

  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" xmlns:aop="http://www.springframework.org/schema/aop"
  4        xmlns:tx="http://www.springframework.org/schema/tx"
  5        xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/aop https://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">
  6 
  7 
  8     <!-- 配置Jdbc模板-->
  9     <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
 10         <property name="dataSource" ref="dataSource"></property>
 11     </bean>
 12 
 13     <bean id="userDao" class="com.etc.dao.UserDAO">
 14         <property name="jdbcTemplate" ref="jdbcTemplate"></property>
 15     </bean>
 16 
 17     <bean id="bankDao" class="com.etc.dao.BankDao">
 18         <property name="jdbcTemplate" ref="jdbcTemplate"></property>
 19     </bean>
 20 
 21     <bean id="bankServiceImpl" class="com.etc.service.serviceImpl.BankServiceImpl">
 22         <property name="bankDao" ref="bankDao"></property>
 23     </bean>
 24 
 25 
 26     <!--基于数据源的事务管理器，通过属性引用数据源-->
 27     <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
 28         <property name="dataSource" ref="dataSource"></property>
 29     </bean>
 30     <aop:config>
 31         <aop:pointcut expression="execution(* com.etc.service.*.*(..))" id="pc"/>
 32         <aop:advisor advice-ref="txAdvice" pointcut-ref="pc"/>
 33     </aop:config>
 34 
 35     <tx:advice id="txAdvice" transaction-manager="transactionManager">
 36         <tx:attributes>
 37             <tx:method name="*"/>
 38         </tx:attributes>
 39     </tx:advice>
 40 
 41     <!-- 配置数据源 -->
 42     <bean id="dataSource"
 43           class="com.mchange.v2.c3p0.ComboPooledDataSource"
 44           destroy-method="close">
 45         <property name="driverClass">
 46             <value>com.mysql.jdbc.Driver</value>
 47         </property>
 48         <property name="jdbcUrl">
 49             <value>jdbc:mysql://localhost:3310/day07?characterEncoding=UTF-8</value>
 50         </property>
 51         <property name="user">
 52             <value>root</value>
 53         </property>
 54         <property name="password">
 55             <value>123456</value>
 56         </property>
 57 
 58         <!--连接池中保留的最小连接数。-->
 59         <property name="minPoolSize">
 60             <value>5</value>
 61         </property>
 62 
 63         <!--连接池中保留的最大连接数。Default: 15 -->
 64         <property name="maxPoolSize">
 65             <value>30</value>
 66         </property>
 67 
 68         <!--初始化时获取的连接数，取值应在minPoolSize与maxPoolSize之间。Default: 3 -->
 69         <property name="initialPoolSize">
 70             <value>10</value>
 71         </property>
 72 
 73         <!--最大空闲时间,60秒内未使用则连接被丢弃。若为0则永不丢弃。Default: 0 -->
 74         <property name="maxIdleTime">
 75             <value>60</value>
 76         </property>
 77 
 78         <!--当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3 -->
 79         <property name="acquireIncrement">
 80             <value>5</value>
 81         </property>
 82 
 83         <!--JDBC的标准参数，用以控制数据源内加载的PreparedStatements数量。但由于预缓存的statements
 84          属于单个connection而不是整个连接池。所以设置这个参数需要考虑到多方面的因素。
 85          如果maxStatements与maxStatementsPerConnection均为0，则缓存被关闭。Default: 0-->
 86         <property name="maxStatements">
 87             <value>0</value>
 88         </property>
 89 
 90         <!--每60秒检查所有连接池中的空闲连接。Default: 0 -->
 91         <property name="idleConnectionTestPeriod">
 92             <value>60</value>
 93         </property>
 94 
 95         <!--定义在从数据库获取新连接失败后重复尝试的次数。Default: 30 -->
 96         <property name="acquireRetryAttempts">
 97             <value>30</value>
 98         </property>
 99 
100         <!--获取连接失败将会引起所有等待连接池来获取连接的线程抛出异常。但是数据源仍有效
101          保留，并在下次调用getConnection()的时候继续尝试获取连接。如果设为true，那么在尝试
102          获取连接失败后该数据源将申明已断开并永久关闭。Default: false-->
103         <property name="breakAfterAcquireFailure">
104             <value>true</value>
105         </property>
106 
107         <!--因性能消耗大请只在需要的时候使用它。如果设为true那么在每个connection提交的
108          时候都将校验其有效性。建议使用idleConnectionTestPeriod或automaticTestTable
109          等方法来提升连接测试的性能。Default: false -->
110         <property name="testConnectionOnCheckout">
111             <value>false</value>
112         </property>
113     </bean>
114 </beans>

userDao

 1 package com.etc.dao;
 2 
 3 import com.etc.entity.User;
 4 import org.springframework.jdbc.core.JdbcTemplate;
 5 import org.springframework.jdbc.core.RowMapper;
 6 
 7 import java.sql.ResultSet;
 8 import java.sql.SQLException;
 9 import java.util.List;
10 
11 public class UserDAO {
12 
13     private JdbcTemplate jdbcTemplate;
14 
15     public void save() {
16         jdbcTemplate.execute("insert into t_user (username,password) values ('3','4')");
17     }
18 
19 
20     public List<User> queryAll() {
21         return jdbcTemplate.query("select * from t_user", new RowMapper<User>() {
22             @Override
23             public User mapRow(ResultSet resultSet, int i) throws SQLException {
24                 return result(resultSet);
25             }
26         });
27     }
28 
29     public User query(User user) {
30         return jdbcTemplate.queryForObject("select * from t_user where id=?", new Object[]{user.getId()}, new RowMapper<User>() {
31             @Override
32             public User mapRow(ResultSet resultSet, int i) throws SQLException {
33                 return result(resultSet);
34             }
35         });
36     }
37 
38 
39     public static User result(ResultSet resultSet) throws SQLException {
40         User user = new User();
41         user.setId(resultSet.getInt("id"));
42         user.setUsername(resultSet.getString("username"));
43         user.setPassword(resultSet.getString("password"));
44         return user;
45     }
46 
47 
48     public JdbcTemplate getJdbcTemplate() {
49         return jdbcTemplate;
50     }
51 
52     public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
53         this.jdbcTemplate = jdbcTemplate;
54     }
55 }

View Code