在项目中尝试使用了几种开源的数据库连接池实现。一种是dbcp,一种是c3p0,还有一种是proxool,这几种数据库连接池都可以很容易的在Spring配置起来。性能总体上上感觉dbcp为最优,因为稳定性和并发性都是我的项目需要的。
项目中经过反复测试,如果web server和数据库server不是同一个机器的话,在断网时间比较短的时间内三种数据库连接池都能较好的重连,但是在断网时间超过8个钟头 proxool就不能恢复工作了。但是dbcp却能很快的重新连接。实际生产环境中稳定性和总体性能是最重要的,都需要做相应的测试才能放心的让系统上生 产线。
这里给出项目中数据库连接池配置:
dbcp的jndi:
proxool(proxool-0.9.0RC1)的配置:
c3p0(c3p0-0.9.0):
详细配置请访问
http://jakarta.apache.org/commons/dbcp/configuration.html
项目中经过反复测试,如果web server和数据库server不是同一个机器的话,在断网时间比较短的时间内三种数据库连接池都能较好的重连,但是在断网时间超过8个钟头 proxool就不能恢复工作了。但是dbcp却能很快的重新连接。实际生产环境中稳定性和总体性能是最重要的,都需要做相应的测试才能放心的让系统上生 产线。
这里给出项目中数据库连接池配置:
dbcp的jndi:
1
<
bean
id
="dataSource"
2
class
="org.springframework.jndi.JndiObjectFactoryBean"
>
3
<
property
name
="jndiName"
>
4
<
value
>
java:comp/env/jdbc/mysql
</
value
>
5
</
property
>
6
</
bean
>
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
2
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
3
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
4
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
5
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
6
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
proxool(proxool-0.9.0RC1)的配置:
<
bean
id
="MySqlDataSource"
class ="org.logicalcobwebs.proxool.ProxoolDataSource"
lazy-init ="false" >
< property name ="driver" >
< value > com.mysql.jdbc.Driver </ value >
</ property >
< property name ="driverUrl" >
< value > jdbc:mysql://ip:3306/dbname?useUnicode=true & characterEncoding=utf8 & autoReconnect=true </ value >
</ property >
< property name ="user" >
< value > user </ value >
</ property >
< property name ="password" >
< value > password </ value >
</ property >
< property name ="maximumConnectionCount" >
< value > 500 </ value >
</ property >
< property name ="houseKeepingSleepTime" >
< value > 15000 </ value >
</ property >
< property name ="houseKeepingTestSql" >
< value > select CURRENT_DATE </ value >
</ property >
< property name ="testBeforeUse" >
< value > true </ value >
</ property >
< property name ="alias" >
< value > mysqlProxoolDataSource </ value >
</ property >
<!--
<property name="maximumActiveTime">
<value>10000</value>
</property>
-->
< property name ="simultaneousBuildThrottle" >
< value > 1000 </ value >
</ property >
< property name ="trace" >
< value > false </ value >
</ property >
</ bean >
建议使用DBCP,配置在tomcat中,然后在spring中使用jndi的形式获取。
class ="org.logicalcobwebs.proxool.ProxoolDataSource"
lazy-init ="false" >
< property name ="driver" >
< value > com.mysql.jdbc.Driver </ value >
</ property >
< property name ="driverUrl" >
< value > jdbc:mysql://ip:3306/dbname?useUnicode=true & characterEncoding=utf8 & autoReconnect=true </ value >
</ property >
< property name ="user" >
< value > user </ value >
</ property >
< property name ="password" >
< value > password </ value >
</ property >
< property name ="maximumConnectionCount" >
< value > 500 </ value >
</ property >
< property name ="houseKeepingSleepTime" >
< value > 15000 </ value >
</ property >
< property name ="houseKeepingTestSql" >
< value > select CURRENT_DATE </ value >
</ property >
< property name ="testBeforeUse" >
< value > true </ value >
</ property >
< property name ="alias" >
< value > mysqlProxoolDataSource </ value >
</ property >
<!--
<property name="maximumActiveTime">
<value>10000</value>
</property>
-->
< property name ="simultaneousBuildThrottle" >
< value > 1000 </ value >
</ property >
< property name ="trace" >
< value > false </ value >
</ property >
</ bean >
建议使用DBCP,配置在tomcat中,然后在spring中使用jndi的形式获取。
c3p0(c3p0-0.9.0):
1
<
bean
id
="dataSource"
class
="com.mchange.v2.c3p0.ComboPooledDataSource"
2
destroy-method
="close"
>
3
<
property
name
="driverClass"
>
4
<
value
>
com.mysql.jdbc.Driver
</
value
>
5
</
property
>
6
<
property
name
="jdbcUrl"
>
7
<
value
>
jdbc:mysql://192.168.0.225:3306/sendinmdb?useUnicode=true
&
characterEncoding=utf8
&
autoReconnect=true
</
value
>
8
</
property
>
9
<
property
name
="user"
>
10
<
value
>********
</
value
>
11
</
property
>
12
<
property
name
="password"
>
13
<
value
>********
</
value
>
14
</
property
>
15
<
property
name
="maxPoolSize"
>
16
<
value
>
100
</
value
>
17
</
property
>
18
<
property
name
="maxIdleTime"
>
19
<
value
>
50
</
value
>
20
</
property
>
21
<
property
name
="maxStatementsPerConnection"
>
22
<
value
>
100
</
value
>
23
</
property
>
24
<
property
name
="numHelperThreads"
>
25
<
value
>
1000
</
value
>
26
</
property
>
27
<
property
name
="idleConnectionTestPeriod"
>
28
<
value
>
30
</
value
>
29
</
property
>
30
</
bean
>
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
2
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
3
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
4
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
5
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
6
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
7
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
8
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
9
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
10
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
11
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
12
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
13
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
14
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
15
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
16
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
17
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
18
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
19
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
20
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
21
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
22
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
23
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
24
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
25
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
26
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
27
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
28
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
29
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
30
![](https://i-blog.csdnimg.cn/blog_migrate/4f1150b881333f12a311ae9ef34da474.gif)
直接copy & paste到spring配置文件里就可以使用了。
配置一些额外的tomcat 的DBCP连接池参数,也可以更好的使用到类似proxool提供的功能,只是dbcp更加稳定而已。
tomcat/conf/context.xml中插入一个Resource元素:
<
Resource
name
="jdbc/mysql"
auth
="Container"
type
="javax.sql.DataSource"
maxActive ="1000" maxIdle ="30" maxWait ="10000"
username ="user" password ="password" driverClassName ="com.mysql.jdbc.Driver"
validationQuery = "select current_date()"
testOnBorrow = "true" testOnReturn = "false" testWhileIdle = "true"
timeBetweenEvictionRunsMillis = "15000" numTestsPerEvictionRun = "10" minEvictableIdleTimeMillis = "600000"
url ="jdbc:mysql://ip:3306/sendinmdb?useUnicode=true&characterEncoding=utf8&autoReconnect=true" />
maxActive ="1000" maxIdle ="30" maxWait ="10000"
username ="user" password ="password" driverClassName ="com.mysql.jdbc.Driver"
validationQuery = "select current_date()"
testOnBorrow = "true" testOnReturn = "false" testWhileIdle = "true"
timeBetweenEvictionRunsMillis = "15000" numTestsPerEvictionRun = "10" minEvictableIdleTimeMillis = "600000"
url ="jdbc:mysql://ip:3306/sendinmdb?useUnicode=true&characterEncoding=utf8&autoReconnect=true" />
解释一下以下这些参数的含义:
validationQuery = "select current_date()"
testOnBorrow = "true"
testOnReturn = "false"
testWhileIdle = "true"
当 从池中获取一个Connection后使用 select current_date() 来测试该数据库连接的可用性,如果SQL语句返回结果则认为是一个有效的连接,否则将继续测试知道可以拿到有效的连接。当返回Connection给池的 时候不进行验证,但是Connection空闲的时候就要进行认证。
timeBetweenEvictionRunsMillis = "15000"
DBCP 清空线程睡眠的间隙,如值为负数则不运行该线程
numTestsPerEvictionRun = "10"
清空线程每次验证的连接对象个数
minEvictableIdleTimeMillis = "600000"
详细配置请访问
http://jakarta.apache.org/commons/dbcp/configuration.html