数据库连接池实现：jakarta commons dbcp

ruknow

[color="#b22222"]链接地址：
http://jakarta.apache.org/commons/dbcp/

[color="#000000"]实现代码：
http://cvs.apache.org/viewcvs.cgi/jakarta-commons/dbcp/doc/BasicDataSourceExample.java?rev=1.2&view=markup
[color="#b22222"]
/*
* Copyright 1999-2004 The Apache Software Foundation.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
*      http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
[color="#a020f0"]import javax.sql.DataSource;
[color="#a020f0"]import java.sql.Connection;
[color="#a020f0"]import java.sql.Statement;
[color="#a020f0"]import java.sql.ResultSet;
[color="#a020f0"]import java.sql.SQLException;
[color="#b22222"]//
[color="#b22222"]// Here are the dbcp-specific classes.
[color="#b22222"]// Note that they are only used in the setupDataSource
[color="#b22222"]// method. In normal use, your classes interact
[color="#b22222"]// only with the standard JDBC API
[color="#b22222"]//
[color="#a020f0"]import org.apache.commons.dbcp.BasicDataSource;
[color="#b22222"]//
[color="#b22222"]// Here's a simple example of how to use the BasicDataSource.
[color="#b22222"]// In this example, we'll construct the BasicDataSource manually,
[color="#b22222"]// but you could also configure it using an external conifguration file.
[color="#b22222"]//

[color="#b22222"]//
[color="#b22222"]// Note that this example is very similiar to the PoolingDriver
[color="#b22222"]// example.

[color="#b22222"]//
[color="#b22222"]// To compile this example, you'll want:
[color="#b22222"]//  * commons-pool.jar
[color="#b22222"]//  * commons-dbcp.jar
[color="#b22222"]//  * j2ee.jar (for the javax.sql classes)
[color="#b22222"]// in your classpath.
[color="#b22222"]//
[color="#b22222"]// To run this example, you'll want:
[color="#b22222"]//  * commons-collections.jar
[color="#b22222"]//  * commons-pool.jar
[color="#b22222"]//  * commons-dbcp.jar
[color="#b22222"]//  * j2ee.jar (for the javax.sql classes)
[color="#b22222"]//  * the classes for your (underlying) JDBC driver
[color="#b22222"]// in your classpath.
[color="#b22222"]//
[color="#b22222"]// Invoke the class using two arguments:
[color="#b22222"]//  * the connect string for your underlying JDBC driver
[color="#b22222"]//  * the query you'd like to execute
[color="#b22222"]// You'll also want to ensure your underlying JDBC driver
[color="#b22222"]// is registered.  You can use the "jdbc.drivers"
[color="#b22222"]// property to do this.
[color="#b22222"]//
[color="#b22222"]// For example:
[color="#b22222"]//  java -Djdbc.drivers=oracle.jdbc.driver.OracleDriver
[color="#b22222"]//       -classpath commons-collections.jar:commons-pool.jar:commons-dbcp.jar:j2ee.jar:oracle-jdbc.jar:.
[color="#b22222"]//       ManualPoolingDataSourceExample
[color="#b22222"]//       "jdbc:oracle:thin:scott/tiger@myhost:1521:mysid"
[color="#b22222"]//       "SELECT * FROM DUAL"
[color="#b22222"]//
[color="#a020f0"]public [color="#a020f0"]class BasicDataSourceExample {
    [color="#a020f0"]public [color="#a020f0"]static [color="#a020f0"]void main(String[] args) {
        [color="#b22222"]// First we set up the BasicDataSource.
        [color="#b22222"]// Normally this would be handled auto-magically by
        [color="#b22222"]// an external configuration, but in this example we'll
        [color="#b22222"]// do it manually.
        [color="#b22222"]//
        System.out.println([color="#bc8f8f"]"Setting up data source.");
        DataSource dataSource = setupDataSource(args[0]);
        System.out.println([color="#bc8f8f"]"Done.");
        [color="#b22222"]//
        [color="#b22222"]// Now, we can use JDBC DataSource as we normally would.
        [color="#b22222"]//
        Connection conn = [color="#a020f0"]null;
        Statement stmt = [color="#a020f0"]null;
        ResultSet rset = [color="#a020f0"]null;
        [color="#a020f0"]try {
            System.out.println([color="#bc8f8f"]"Creating connection.");
            conn = dataSource.getConnection();
            System.out.println([color="#bc8f8f"]"Creating statement.");
            stmt = conn.createStatement();
            System.out.println([color="#bc8f8f"]"Executing statement.");
            rset = stmt.executeQuery(args[1]);
            System.out.println([color="#bc8f8f"]"Results:");
            [color="#a020f0"]int numcols = rset.getMetaData().getColumnCount();
            [color="#a020f0"]while(rset.next()) {
                [color="#a020f0"]for([color="#a020f0"]int i=1;i
[color="#b22222"]
[color="#b22222"]参数：
http://jakarta.apache.org/commons/dbcp/configuration.html
ParameterDescriptionusernameThe connection username to be passed to our JDBC driver to establish a connection.passwordThe connection password to be passed to our JDBC driver to establish a connection.urlThe connection URL to be passed to our JDBC driver to establish a connection.driverClassNameThe fully qualified Java class name of the JDBC driver to be used.connectionPropertiesThe connection properties that will be sent to our JDBC driver when establishing new connections.
Format of the string must be [propertyName=property;]*
NOTE - The "user" and "password" properties will be passed explicitly, so they do not need to be included here. ParameterDefaultDescriptiondefaultAutoCommittrueThe default auto-commit state of connections created by this pool.defaultReadOnlydriver defaultThe default read-only state of connections created by this pool. If not set then the setReadOnly method will not be called. (Some drivers don't support read only mode, ex: Informix) defaultTransactionIsolationdriver defaultThe default TransactionIsolation state of connections created by this pool. One of the following: (see
javadoc
)

• NONE
• READ_COMMITTED
• READ_UNCOMMITTED
• REPEATABLE_READ
• SERIALIZABLE

defaultCatalogThe default catalog of connections created by this pool.ParameterDefaultDescriptioninitialSize0The initial number of connections that are created when the pool is started.
Since: 1.2 maxActive8The maximum number of active connections that can be allocated from this pool at the same time, or zero for no limit. maxIdle8The maximum number of active connections that can remain idle in the pool, without extra ones being released, or zero for no limit. minIdle0The minimum number of active connections that can remain idle in the pool, without extra ones being created, or zero to create none. maxWaitindefinitelyThe maximum number of milliseconds that the pool will wait (when there are no available connections) for a connection to be returned before throwing an exception, or -1 to wait indefinitely. ParameterDefaultDescriptionvalidationQueryThe SQL query that will be used to validate connections from this pool before returning them to the caller. If specified, this query MUST be an SQL SELECT statement that returns at least one row. testOnBorrowtrueThe indication of whether objects will be validated before being borrowed from the pool. If the object fails to validate, it will be dropped from the pool, and we will attempt to borrow another. testOnReturnfalseThe indication of whether objects will be validated before being returned to the pool. testWhileIdlefalseThe indication of whether objects will be validated by the idle object evictor (if any). If an object fails to validate, it will be dropped from the pool. timeBetweenEvictionRunsMillis-1The number of milliseconds to sleep between runs of the idle object evictor thread. When non-positive, no idle object evictor thread will be run. numTestsPerEvictionRun3The number of objects to examine during each run of the idle object evictor thread (if any). minEvictableIdleTimeMillis1000 * 60 * 30The minimum amount of time an object may sit idle in the pool before it is eligable for eviction by the idle object evictor (if any). ParameterDefaultDescriptionpoolPreparedStatementsfalseEnable prepared statement pooling for this pool.maxOpenPreparedStatementsunlimitedThe maximum number of open statements that can be allocated from the statement pool at the same time, or zero for no limit.
This component has also the ability to pool PreparedStatements. When enabled a statement pool will be created for each Connection and PreparedStatements created by one of the following methods will be pooled:

• public PreparedStatement prepareStatement(String sql)
• public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency)

NOTE - Make sure your connection has some resources left for the other statements.
ParameterDefaultDescriptionaccessToUnderlyingConnectionAllowedfalseControls if the PoolGuard allows access to the underlying connection.
When allowed you can access the underlying connection using the following construct:
    Connection conn = ds.getConnection();
    Connection dconn = ((DelegatingConnection) conn).getInnermostDelegate();
    ...
    conn.close()
Default is false, it is a potential dangerous operation and misbehaving programs can do harmfull things. (closing the underlying or continue using it when the guarded connection is already closed) Be carefull and only use when you need direct access to driver specific extentions.
NOTE: Do not close the underlying connection, only the original one.
ParameterDefaultDescriptionremoveAbandonedfalseFlag to remove abandoned connections if they exceed the removeAbandonedTimout.
If set to true a connection is considered abandoned and eligible for removal if it has been idle longer than the removeAbandonedTimeout. Setting this to true can recover db connections from poorly written applications which fail to close a connection. removeAbandonedTimeout300Timeout in seconds before an abandoned connection can be removed.logAbandonedfalseFlag to log stack traces for application code which abandoned a Statement or Connection.
Logging of abandoned Statements and Connections adds overhead for every Connection open or new Statement because a stack trace has to be generated.
If you have enabled "removeAbandoned" then it is possible that a connection is reclaimed by the pool because it is considered to be abandoned. This mechanism is triggered when (getNumIdle()  getMaxActive() - 3) For example maxActive=20 and 18 active connections and 1 idle connection would trigger the "removeAbandoned". But only the active connections that aren't used for more then "removeAbandonedTimeout" seconds are removed, default (300 sec). Traversing a resultset doesn't count as being used.


本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u/598/showart_12942.html