apache-DBCP基本配置介绍
apache-DBCP基本配置介绍
博客分类:Java
ApacheJava连接池JDBCdbcp
blog迁移至:http://www.micmiu.com
本文主要介绍apache-dbcp基本参数配置和应用示例,dbcp目前最新版是1.4需要在jdk1.6的环境下运行,如果要在jdk1.4、1.5环境下运行,需要下载前一版本1.3,具体详细可以查看它的官网。
本文结构目录分两大部分:
一、基础参数说明
二、dbcp在spring、hibernate等应用中的配置示例
以后会陆续给出有关c3p0、proxool等相关参数介绍和应用配置实例。
[一]、参数介绍
[1]、基础参数说明
defaultAutoCommit:对于事务是否autoCommit,默认值为true
defaultReadOnly:对于数据库是否只能读取,默认值为false
initialSize:连接池启动时创建的初始化连接数量(默认值为0)
driverClassName:连接数据库所用的JDBCDriverClass,
url:连接数据库的URL
username:登陆数据库所用的帐号
password:登陆数据库所用的密码
maxActive:连接池中可同时连接的最大的连接数,为0则表示没有限制,默认为8
maxIdle:连接池中最大的空闲的连接数(默认为8,设0为没有限制),超过的空闲连接将被释放,如果设置为负数表示不限制(maxIdle不能设置太小,因为假如在高负载的情况下,连接的打开时间比关闭的时间快,会引起连接池中idle的个数上升超过maxIdle,而造成频繁的连接销毁和创建)
minIdle:连接池中最小的空闲的连接数(默认为0,一般可调整5),低于这个数量会被创建新的连接(该参数越接近maxIdle,性能越好,因为连接的创建和销毁,都是需要消耗资源的;但是不能太大,因为在机器很空闲的时候,也会创建低于minidle个数的连接)
maxWait:超过时间会丟出错误信息最大等待时间(单位为ms),当没有可用连接时,连接池等待连接释放的最大时间,超过该时间限制会抛出异常,如果设置-1表示无限等待(默认为-1,一般可调整为60000ms,避免因线程池不够用,而导致请求被无限制挂起)
validationQuery:验证连接是否成功,SQLSELECT指令至少要返回一行
removeAbandoned:超过removeAbandonedTimeout时间后,是否进行没用连接的回收(默认为false)
removeAbandonedTimeout:超过时间限制,回收五用的连接(默认为300秒),removeAbandoned必须为true
logAbandoned:是否记录中断事件,默认为false
一般优化的配置示例:
Xml代码收藏代码
<beanid="dbcpDataSource"
class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<propertyname="driverClassName"
value="${jdbc.driverClassName}"/>
<propertyname="url"value="${jdbc.url}"/>
<propertyname="username"value="${jdbc.username}"/>
<propertyname="password"value="${jdbc.password}"/>
<propertyname="maxActive"value="20"/>
<propertyname="initialSize"value="1"/>
<propertyname="maxWait"value="60000"/>
<propertyname="maxIdle"value="15"/>
<propertyname="minIdle"value="5"/>
<propertyname="removeAbandoned"value="true"/>
<propertyname="removeAbandonedTimeout"value="180"/>
<propertyname="connectionProperties">
<value>clientEncoding=utf-8</value>
</property>
</bean>
[2]、validate配置参数说明
minEvictableIdleTimeMillis:大于0,进行连接空闲时间判断,或为0,对空闲的连接不进行验证;默认30分钟
timeBetweenEvictionRunsMillis:失效检查线程运行时间间隔,如果小于等于0,不会启动检查线程,默认-1
testOnBorrow:在进行borrowObject进行处理时,对拿到的connection进行validateObject校验,默认为false
testOnReturn:就是在进行returnObject对返回的connection进行validateObject校验,默认为false
testWhileIdle:GenericObjectPool中针对pool管理,起了一个Evict的TimerTask定时线程进行控制(可通过设置参数timeBetweenEvictionRunsMillis>0),定时对线程池中的链接进行validateObject校验,对无效的链接进行关闭后,会调用ensureMinIdle,适当建立链接保证最小的minIdle连接数,默认为false
numTestsPerEvictionRun:每次检查链接的数量,建议设置和maxActive一样大,这样每次可以有效检查所有的链接.
validateQuery:验证检查执行的sql
validateQueryTimeout:在执行检查时超时设置,通过statement设置,statement.setQueryTimeout(validationQueryTimeout)
配置示例:
Xml代码收藏代码
<propertyname="testWhileIdle"value="true"/>
<propertyname="testOnBorrow"value="false"/>
<propertyname="testOnReturn"value="false"/>
<propertyname="validationQuery">
<value>selectsysdatefromdual</value>
</property>
<propertyname="validationQueryTimeout"value="1"/>
<propertyname="timeBetweenEvictionRunsMillis"value="30000"/>
<propertyname="numTestsPerEvictionRun"value="20"/>
[二]、dbcp在spring、hibernate等应用中的配置示例
dbcp.jdbc.properties
Java代码收藏代码
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost/iecms
jdbc.username=root
jdbc.password=
[1]、最基本的应用
Java代码收藏代码
packagemichael.jdbc.dbcp;
importjavax.sql.DataSource;
importjava.sql.Connection;
importjava.sql.Statement;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.util.Properties;
importorg.apache.commons.dbcp.BasicDataSource;
/**
*/
publicclassDbcpDataSourceExample{
/**
*@paramargs
*/
publicstaticvoidmain(String[]args){
StringtestSql="select*fromTB_MYTEST";
StringcfgFilename="dbcp.jdbc.properties";
System.out.println("Settingupdatasource.");
DataSourcedataSource=setupDataSource(cfgFileName);
System.out.println("dataSourceDone.");
printDataSourceStats(dataSource);
Connectionconn=null;
Statementstmt=null;
ResultSetrset=null;
try{
System.out.println("Creatingconnectionstart.");
conn=dataSource.getConnection();
System.out.println("Creatingstatementstart.");
stmt=conn.createStatement();
System.out.println("Executingstatementstart.");
rset=stmt.executeQuery(testSql);
System.out.println("executeQueryResults:");
intnumcols=rset.getMetaData().getColumnCount();
while(rset.next()){
for(inti=1;i<=numcols;i++){
System.out.print("\t"+rset.getString(i));
}
System.out.println("");
}
System.out.println("Resultsdisplaydone.");
}catch(SQLExceptione){
e.printStackTrace();
}finally{
try{
if(rset!=null)
rset.close();
}catch(Exceptione){
}
try{
if(stmt!=null)
stmt.close();
}catch(Exceptione){
}
try{
if(conn!=null)
conn.close();
shutdownDataSource(dataSource);
}catch(Exceptione){
}
}
}
/**
*@paramcfgFileName
*@returnDataSource
*/
publicstaticDataSourcesetupDataSource(StringcfgFileName){
BasicDataSourceds=newBasicDataSource();
try{
Propertiescfgpp=newProperties();
cfgpp.load(DbcpDataSourceExample.class
.getResourceAsStream(cfgFileName));
ds.setDriverClassName(cfgpp.getProperty("jdbc.driverClassName"));
ds.setUrl(cfgpp.getProperty("jdbc.url"));
ds.setUsername(cfgpp.getProperty("jdbc.username"));
ds.setPassword(cfgpp.getProperty("jdbc.password"));
}catch(Exceptione){
e.printStackTrace();
returnnull;
}
returnds;
}
/**
*@paramds
*/
publicstaticvoidprintDataSourceStats(DataSourceds){
BasicDataSourcebds=(BasicDataSource)ds;
System.out.println("NumActive:"+bds.getNumActive());
System.out.println("NumIdle:"+bds.getNumIdle());
}
/**
*@paramds
*@throwsSQLException
*/
publicstaticvoidshutdownDataSource(DataSourceds)throwsSQLException{
BasicDataSourcebds=(BasicDataSource)ds;
bds.close();
}
}
运行结果:
引用
Settingupdatasource.
dataSourceDone.
NumActive:0
NumIdle:0
Creatingconnectionstart.
Creatingstatementstart.
Executingstatementstart.
executeQueryResults:
1batchadd02011-06-1614:29:08.0
2batchadd12011-06-1614:29:08.0
3batchadd22011-06-1614:29:08.0
4batchadd32011-06-1614:29:08.0
5batchadd42011-06-1614:29:08.0
Resultsdisplaydone.
[2]、结合spring的应用测试
Xml代码收藏代码
<?xmlversion="1.0"encoding="UTF-8"?>
<!DOCTYPEbeansPUBLIC"-//SPRING//DTDBEAN//EN""http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
<beanid="propertyConfigurer"
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<propertyname="locations">
<list>
<value>
classpath:michael/jdbc/dbcp/dbcp.jdbc.properties
</value>
</list>
</property>
</bean>
<beanid="dbcpDataSource"
class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<propertyname="driverClassName"
value="${jdbc.driverClassName}"/>
<propertyname="url"value="${jdbc.url}"/>
<propertyname="username"value="${jdbc.username}"/>
<propertyname="password"value="${jdbc.password}"/>
<propertyname="maxActive"value="20"/>
<propertyname="initialSize"value="1"/>
<propertyname="maxWait"value="60000"/>
<propertyname="maxIdle"value="15"/>
<propertyname="minIdle"value="5"/>
<propertyname="removeAbandoned"value="true"/>
<propertyname="removeAbandonedTimeout"value="180"/>
<propertyname="connectionProperties">
<value>clientEncoding=utf-8</value>
</property>
</bean>
</beans>
Java代码收藏代码
packagemichael.jdbc.dbcp;
importjava.sql.Connection;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.sql.Statement;
importjavax.sql.DataSource;
importorg.apache.commons.dbcp.BasicDataSource;
importorg.springframework.context.ApplicationContext;
importorg.springframework.context.support.ClassPathXmlApplicationContext;
/**
*/
publicclassDbcpSpringExample{
/**
*@paramargs
*/
publicstaticvoidmain(String[]args){
System.out.println("springxmlinitstart");
ApplicationContextappCt=newClassPathXmlApplicationContext(
"michael/jdbc/dbcp/dbcp.ds.spring.xml");
System.out.println("springbeancreateDataSource");
DataSourcedataSource=(BasicDataSource)appCt
.getBean("dbcpDataSource");
StringtestSql="select*fromTB_MYTEST";
Connectionconn=null;
Statementstmt=null;
ResultSetrset=null;
try{
System.out.println("Creatingconnectionstart.");
conn=dataSource.getConnection();
System.out.println("Creatingstatementstart.");
stmt=conn.createStatement();
System.out.println("Executingstatementstart.");
rset=stmt.executeQuery(testSql);
System.out.println("executeQueryResults:");
intnumcols=rset.getMetaData().getColumnCount();
while(rset.next()){
for(inti=1;i<=numcols;i++){
System.out.print("\t"+rset.getString(i));
}
System.out.println("");
}
System.out.println("Resultsdisplaydone.");
}catch(SQLExceptione){
e.printStackTrace();
}finally{
try{
if(rset!=null)
rset.close();
}catch(Exceptione){
}
try{
if(stmt!=null)
stmt.close();
}catch(Exceptione){
}
try{
if(conn!=null)
conn.close();
}catch(Exceptione){
}
}
}
}
运行结果:
引用
springxmlinitstart
springbeancreateDataSource
Creatingconnectionstart.
Creatingstatementstart.
Executingstatementstart.
executeQueryResults:
1batchadd02011-06-1614:29:08.0
2batchadd12011-06-1614:29:08.0
3batchadd22011-06-1614:29:08.0
4batchadd32011-06-1614:29:08.0
5batchadd42011-06-1614:29:08.0
Resultsdisplaydone.
[3]、结合hibernate的应用测试
DBCP在Hibernate2中受支持,目前在hibernate3.0,已经不再支持dbcp了,hibernate的作者在hibernate.org中,明确指出在实践中发现dbcp有BUG,在某些种情会产生很多空连接不能释放,所以推荐使用c3p0连接,本文重点只是描述其基本使用配置,不是比较两者的优劣,故不过多展开描述。
Xml代码收藏代码
<!DOCTYPEhibernate-configuration
PUBLIC"-//Hibernate/HibernateConfigurationDTD//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<!--databaseconnectionsettings-->
<propertyname="connection.driver_class">
com.mysql.jdbc.Driver
</property>
<propertyname="connection.url">
jdbc:mysql://localhost/iecms
</property>
<propertyname="connection.username">root</property>
<propertyname="connection.password"></property>
<!--dbcp-->
<!--连接池的最大活动个数-->
<propertyname="dbcp.maxActive">100</property>
<!--当连接池中的连接已经被耗尽的时候,DBCP将怎样处理(0=失败,1=等待,2=增长)-->
<propertyname="dbcp.whenExhaustedAction">1</property>
<!--最大等待时间(ms)-->
<propertyname="dbcp.maxWait">60000</property>
<!--最大闲置的连接个数-->
<propertyname="dbcp.maxIdle">10</property>
<!--下面是对preparedstatement-->
<propertyname="dbcp.ps.maxActive">100</property>
<propertyname="dbcp.ps.whenExhaustedAction">1</property>
<propertyname="dbcp.ps.maxWait">60000</property>
<propertyname="dbcp.ps.maxIdle">10</property>
<propertyname="current_session_context_class">thread</property>
<propertyname="show_sql">false</property>
<propertyname="dialect">
org.hibernate.dialect.MySQL5Dialect
</property>
<propertyname="hbm2ddl.auto">update</property>
</session-factory>
</hibernate-configuration>
Java代码收藏代码
packagemichael.jdbc.dbcp;
importjava.util.List;
importorg.hibernate.Session;
importorg.hibernate.SessionFactory;
importorg.hibernate.Transaction;
importorg.hibernate.cfg.AnnotationConfiguration;
importorg.hibernate.cfg.Configuration;
/**
*/
publicclassDbcpHibernateExample{
/**
*@paramargs
*/
publicstaticvoidmain(String[]args){
SessionFactorysessionFactory=null;
try{
System.out.println("hibernateconfigstart");
Configurationconfig=newAnnotationConfiguration()
.configure("michael/jdbc/dbcp/hibernate.cfg.xml");
System.out.println("createsessionFactory");
sessionFactory=config.buildSessionFactory();
System.out.println("createsession");
Sessionsession=sessionFactory.getCurrentSession();
StringtestSql="select*fromTB_MYTEST";
System.out.println("beginTransaction");
Transactionta=session.beginTransaction();
org.hibernate.Queryquery=session.createSQLQuery(testSql);
List<Object[]>list=query.list();
System.out.println("createSQLQuerylist:");
for(Object[]objArr:list){
for(Objectobj:objArr){
System.out.print("\t"+obj);
}
System.out.println("");
}
System.out.println("beginTransactioncommit");
ta.commit();
}catch(Exceptione){
e.printStackTrace();
}finally{
if(null!=sessionFactory){
sessionFactory.close();
}
}
}
}
运行结果:
引用
hibernateconfigstart
createsessionFactory
createsession
beginTransaction
createSQLQuerylist:
1batchadd02011-06-1614:29:08.0
2batchadd12011-06-1614:29:08.0
3batchadd22011-06-1614:29:08.0
4batchadd32011-06-1614:29:08.0
5batchadd42011-06-1614:29:08.0
beginTransactioncommit