<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<!-- 配置连接池属性 -->
<property name="driverClass" value="${jdbc.driver}"/>
<property name="jdbcUrl" value="${jdbc.url}"/>
<property name="user" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
<!--连接池中保留的最小连接数。-->
<property name="minPoolSize" value="10" />
<!--连接池中保留的最大连接数。Default: 15 -->
<property name="maxPoolSize" value="100" />
<!--最大空闲时间,60秒内未使用则连接被丢弃。若为0则永不丢弃。Default: 0 -->
<property name="maxIdleTime" value="60" />
<!--当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3 -->
<property name="acquireIncrement" value="3" />
<!-- JDBC的标准参数,用以控制数据源内加载的PreparedStatements数量。但由于预缓存的statements属于单个 connection而不是整个连接池。所以设置这个参数需要考虑到多方面的因素。如果maxStatements与-->
<!--maxStatementsPerConnection均为0,则缓存被关闭。Default: 0 -->
<property name="maxStatements" value="100" />
<!-- 初始化时获取三个连接,取值应在minPoolSize与maxPoolSize之间。Default: 3-->
<property name="initialPoolSize" value="10" />
<!-- 每180秒检查所有连接池中的空闲连接。Default: 0-->
<property name="idleConnectionTestPeriod" value="180" />
<!-- 定义在从数据库获取新连接失败后重复尝试的次数,Oracle数据库校验登录失败次数(FAILED_LOGIN_ATTEMPTS)为10次,并且是一个不断叠加的过程,
在保证密码正确之前,无论中间间隔多长时间,登陆失败连续超过十次,用户会被锁定(acquireRetryAttempts * acquireIncrement )+ initialPoolSize。Default: 30-->
<property name="acquireRetryAttempts" value="2" />
<!-- 获取连接失败将会引起所有等待连接池来获取连接的线程抛出异常。但是数据源仍有效 保留,并在下次调用getConnection()的时候继续尝试获取连接。-->
<!-- 如果设为true,那么在尝试获取连接失败后该数据源将申明已断开并永久关闭。Default: false-->
<property name="breakAfterAcquireFailure" value="false" />
<!-- 如果设为true那么在取得连接的同时将校验连接的有效性。Default: false-->
<property name="testConnectionOnCheckout" value="false" />
</bean>
mysql> show processlist;
+-----+-----------------+-----------------+--------+---------+-------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+-----------------+--------+---------+-------+------------------------+------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 71120 | Waiting on empty queue | NULL |
| 370 | | localhost:9098 | ztdate | Sleep | 10092 | | NULL |
| 371 | ztdate | localhost:9108 | ztdate | Sleep | 66989 | | NULL |
| 590 | root | localhost:31942 | NULL | Query | 0 | starting | show processlist |
| 753 | root | localhost:38787 | mysql | Sleep | 2862 | | NULL |
| 754 | root | localhost:38790 | sys | Sleep | 2862 | | NULL |
+-----+-----------------+-----------------+--------+---------+-------+------------------------+------------------+
6 rows in set (0.00 sec)
mysql> show processlist;
+------+-----------------+-----------------+--------+---------+-------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-----------------+-----------------+--------+---------+-------+------------------------+------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 71156 | Waiting on empty queue | NULL |
| 370 | ztdate | localhost:9098 | ztdate | Sleep | 10128 | | NULL |
| 371 | ztdate | localhost:9108 | ztdate | Sleep | 67025 | | NULL |
| 590 | root | localhost:31942 | NULL | Query | 0 | starting | show processlist |
| 753 | root | localhost:38787 | mysql | Sleep | 2898 | | NULL |
| 754 | root | localhost:38790 | sys | Sleep | 2898 | | NULL |
| 8430 | ztdate | localhost:54232 | ztdate | Sleep | 2 | | NULL |
| 8431 | ztdate | localhost:54233 | ztdate | Sleep | 3 | | NULL |
| 8432 | ztdate | localhost:54234 | ztdate | Sleep | 3 | | NULL |
| 8433 | ztdate | localhost:54235 | ztdate | Sleep | 3 | | NULL |
| 8434 | ztdate | localhost:54236 | ztdate | Sleep | 2 | | NULL |
| 8435 | ztdate | localhost:54237 | ztdate | Sleep | 2 | | NULL |
| 8436 | ztdate | localhost:54239 | ztdate | Sleep | 2 | | NULL |
| 8437 | ztdate | localhost:54238 | ztdate | Sleep | 2 | | NULL |
| 8438 | ztdate | localhost:54240 | ztdate | Sleep | 2 | | NULL |
| 8439 | ztdate | localhost:54241 | ztdate | Sleep | 2 | | NULL |
+------+-----------------+-----------------+--------+---------+-------+------------------------+------------------+
16 rows in set (0.00 sec)
每查询一次数据库连接数增加10个。
解决方法:
public class ApplicationBean {
static private ApplicationContext ac = null;
static {
ac = new ClassPathXmlApplicationContext("applicationContext.xml");
}
private ApplicationBean() {
}
public static ApplicationContext getAc() {
return ac;
}
}
ApplicationContext act = ApplicationBean.getAc();
JdbcTemplate jdbcTemplate = (JdbcTemplate) act.getBean("jdbcTemplate");