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;

/**

*@[email protected]

*/

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;

/**

*@[email protected]

*/

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;

/**

*@[email protected]

*/

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

相关推荐