Basic configuration of apache-DBCP

This article mainly introduces the configuration of basic parameters and application examples of apache-dbcp. Currently, the latest version of dbcp is 1.4 and needs to be run in the jdk1.6 environment. If you want to run in the jdk1.4 and 1.5 environments, you need to download version 1.3 of the previous version. For details, refer to its official website.
The structure directory of this article is divided into two parts:
1. Basic parameter description
Ii. Configuration example of dbcp in spring, hibernate and other applications
In the future, we will provide introduction to parameters such as c3p0 and proxool and application configuration instances.

[1] parameter Introduction
[1] basic parameter description
DefaultAutoCommit: whether the transaction is autoCommit. The default value is true.
DefaultReadOnly: whether the database can only be read. The default value is false.
InitialSize: number of initial connections created when the connection pool is started (default value: 0)
DriverClassName: JDBC Driver Class used to connect to the database,
Url: the URL to connect to the database
Username: account used to log on to the database
Password: password used to log on to the database
MaxActive: the maximum number of connections that can be connected at the same time in the connection pool. If it is 0, there is no limit. The default value is 8.
MaxIdle: the maximum number of idle connections in the connection pool (the default value is 8 and the value 0 indicates no limit). If the number of idle connections exceeds the limit, if it is set to a negative number, there is no limit (maxIdle cannot be set too small, because in the case of high load, the connection opening time is faster than the closing time, this will increase the number of idle in the connection pool by more than maxIdle, and cause frequent connection destruction and creation)
MinIdle: the minimum number of idle connections in the connection pool (the default value is 0, which can be adjusted to 5). A new connection is created if the number is lower than this value (the closer this parameter is to maxIdle, the better the performance, because the creation and destruction of connections consume resources, but they cannot be too large, because when the machine is idle, it will also create connections smaller than the number of minidle)
MaxWait: The maximum wait time (in MS) for the error message is rolled out when the time limit is exceeded. When no connection is available, the maximum wait time for the connection pool to wait for the connection to be released is thrown if the time limit is exceeded, if-1 is set, it indicates unlimited waiting (the default value is-1, which can be adjusted to 60000 ms to avoid requests being suspended without restrictions due to insufficient thread pool)
ValidationQuery: verify whether the connection is successful. The SQL SELECT command must return at least one row.
RemoveAbandoned: Indicates whether to recycle useless connections after the removeAbandonedTimeout time is exceeded (false by default)
RemoveAbandonedTimeout: If the time limit is exceeded, call back the connections (300 seconds by default). removeAbandoned must be true.
LogAbandoned: Indicates whether to record the interrupt event. The default value is false.
General Optimization Configuration example:
Xml Code
<Bean id = "dbcpDataSource"
Class = "org. apache. commons. dbcp. BasicDataSource"
Destroy-method = "close">
<Property name = "driverClassName"
Value = "$ {jdbc. driverClassName}"/>
<Property name = "url" value = "$ {jdbc. url}"/>
<Property name = "username" value = "$ {jdbc. username}"/>
<Property name = "password" value = "$ {jdbc. password}"/>
<Property name = "maxActive" value = "20"/>
<Property name = "initialSize" value = "1"/>
<Property name = "maxWait" value = "60000"/>
<Property name = "maxIdle" value = "15"/>
<Property name = "minIdle" value = "5"/>
<Property name = "removeAbandoned" value = "true"/>
<Property name = "removeAbandonedTimeout" value = "180"/>
<Property name = "connectionProperties">
<Value> clientEncoding = UTF-8 </value>
</Property>
</Bean>

[2]. validate configuration parameters
MinEvictableIdleTimeMillis: If the value is greater than 0, the idle connection time is determined or 0. The idle connection is not verified. The default value is 30 minutes.
TimeBetweenEvictionRunsMillis: Specifies the interval between failures of check threads. If the value is less than or equal to 0, check threads are not started. The default value is-1.
TestOnBorrow: when processing a borrowObject, perform validateObject verification on the obtained connection. The default value is false.
TestOnReturn: Implements validateObject verification on the returned connection during returnObject. The default value is false.
TestWhileIdle: In the GenericObjectPool for pool management, an Evict TimerTask scheduled thread is controlled (you can set the parameter timeBetweenEvictionRunsMillis> 0) to regularly validate the link in the thread pool by validateObject, after an invalid link is closed, ensureMinIdle is called. Set up a link appropriately to ensure the minimum number of minIdle connections. The default value is false.
NumTestsPerEvictionRun: indicates the number of links to be checked each time. We recommend that you set a value as large as maxActive so that all links can be effectively checked each time.
ValidateQuery, which indicates the SQL statement to be checked
ValidateQueryTimeout indicates the statement. setQueryTimeout (validationQueryTimeout) set during the check)
Configuration example:
Xml Code
<Property name = "testWhileIdle" value = "true"/>
<Property name = "testOnBorrow" value = "false"/>
<Property name = "testOnReturn" value = "false"/>
<Property name = "validationQuery">
<Value> select sysdate from dual </value>
</Property>
<Property name = "validationQueryTimeout" value = "1"/>
<Property name = "timeBetweenEvictionRunsMillis" value = "30000"/>
<Property name = "numTestsPerEvictionRun" value = "20"/>

[2] configuration example of dbcp in spring, hibernate and other applications
Dbcp. jdbc. properties
Java code
Jdbc. driverClassName = com. mysql. jdbc. Driver
Jdbc. url = jdbc: mysql: // localhost/iecms
Jdbc. username = root
Jdbc. password =

[1]. Basic Applications
Java code
Package michael. jdbc. dbcp;

Import javax. SQL. DataSource;
Import java. SQL. Connection;
Import java. SQL. Statement;
Import java. SQL. ResultSet;
Import java. SQL. SQLException;
Import java. util. Properties;

Import org. apache. commons. dbcp. BasicDataSource;

/**
* @ See http://sjsky.iteye.com
* @ Author michael sjsky007@gmail.com
*/
Public class DbcpDataSourceExample {

/**
* @ Param args
*/
Public static void main (String [] args ){

String testSql = "select * from TB_MYTEST ";
String parameter filename = "dbcp. jdbc. properties ";

System. out. println ("Setting up data source .");
DataSource dataSource = setupDataSource (optional filename );
System. out. println ("dataSource Done .");

PrintDataSourceStats (dataSource );

Connection conn = null;
Statement stmt = null;
ResultSet rset = null;

Try {
System. out. println ("Creating connection start .");
Conn = dataSource. getConnection ();

System. out. println ("Creating statement start .");
Stmt = conn. createStatement ();

System. out. println ("Executing statement start .");
Rset = stmt.exe cuteQue