DBCP Overview

<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="d3c44abe-bc4d-42f5-996b-7df041ed4cd6"><ac:plain-text-body><![CDATA[ {{

http://jakarta.apache.org/commons/dbcp/images/dbcp-logo-white.png

}}

Commons-DBCP provides database [http://jakarta.apache.org/commons/dbcp/ Commons-DBCP] provides database connection pooling services.[BR]
A lot of information is available on the [http://jakarta.apache.org/commons/dbcp/ DBCP website]. If you don't find the information you need you can always contact us using one of the [http://jakarta.apache.org/site/mail2.html#Commons mailing lists].]]></ac:plain-text-body></ac:structured-macro>

Release Plans

External Resources

Here's a little diagram showing what datastructure DBCP sets up and how that datastructure is used in case the DriverManager is employed to obtain Drivers at runtime. Please send any corrections (for now) to d.tonhofer@m-plify.com.

Wiki Markup\[http://rei1.m-plify.net/Apache_DBCP/_Structure.pdf
Apache_DBCP_Structure.vsd
pdf Apache_DBCP_Structure.pdf\] Wiki Markup\[png

Diagrams hosted by http://

...

public.m-plify.net

...

The Database Connection Pool (DBCP) component can be used in applications where JDBC resources need to be pooled. Apart from JDBC connections, this provides support for pooling Statement and PreparedStatement instances as well. Complete article can be found here http://www.devx.com/Java/Article/29795/0/page/2.

Security Manager settings

If you're running tomcat with the Security Manager, you will need to add to your catalina.policy:

No Format
grant {

...

Wiki Markup
\[http://rei1.m-plify.net/Apache_DBCP/Apache_DBCP_Structure.png Apache_DBCP_Structure.png\] \\

Diagrams hosted by http://rei1.m-plify.net

The Database Connection Pool (DBCP) component can be used in applications where JDBC resources need to be pooled. Apart from JDBC connections, this provides support for pooling Statement and PreparedStatement instances as well. Complete article can be found here http://www.devx.com/Java/Article/29795/0/page/2.

Security Manager settings

Wiki Markup
If you're running tomcat with the \[http://tomcat.apache.org/tomcat-5.5-doc/security-manager-howto.html Security Manager\], you will need to add to your catalina.policy:

No Format
grant { permission java.lang.RuntimePermission "accessClassInPackage.org.apache.tomcat.dbcp.*"; };

(This is in addition to any java.net.SocketPermission and java.sql.SQLPermission needed by the database itself)

Tomcat 5.Tomcat 5.0 Configuration examples

Wiki MarkupSome Tomcat JNDI Datasource examples (in addition to the \[http://jakarta.apache.org/tomcat/tomcat-5.0-doc/jndi-datasource-examples-howto.html Tomcat 5.0 JNDI datasource howto\]).the Tomcat 5.5 JNDI datasource howto).

BasicDataSource

No Format

<Resource name="jdbc/abandoned" auth="Container" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/abandoned">
   <parameter><name>factory</name><value>org.apache.commons.dbcp.BasicDataSourceFactory</value></parameter>
   <parameter><name>username</name><value>sa</value></parameter>
   <parameter><name>password</name><value></value></parameter>
   <parameter><name>driverClassName</name><value>org.hsqldb.jdbcDriver</value></parameter>
   <parameter><name>url</name><value>jdbc:hsqldb:database</value></parameter>
   <parameter><name>removeAbandoned</name><value>false</value></parameter>
   <parameter><name>removeAbandonedTimeout</name><value>300</value></parameter>
   <parameter><name>logAbandoned</name><value>true</value></parameter>
   <parameter><name>maxActive</name><value>10</value></parameter>
</ResourceParams>

...

2: Saying what class will actually create instances of the above, i.e. give the factory. This is actually not necessary if you want 'org.apache.commons.dbcp.BasicDataSourceFactory' as that is the default factory used by Tomcat whenever 'javax.sql.DataSource' objects should be created. By setting 'factory', you can override that default value. 'o.a.c.d.BasicDataSourceFactory' creates 'org.apache.commons.dbcp.BasicDataSource' instances. These use a resource pool of type 'org.apache.commons.pool.impl.GenericObjectPool'. And for this type of pool we can demand that objects be verified at borrowing time - which is what we want as it will prevent Tomcat getting its paws on stale database connections.

Wiki Markup*3*: Configuring the attributes of the 'BasicDataSourceFactory'. The allowed attributes can be found by looking for JavaBean-compliant set() methods and members in the source or the \[http://jakarta.apache.org/commons/dbcp/apidocs/index.html DBCP API doc\]. In particular: what driver shall be used by the factory to actually get database connections: the DBCP API doc. In particular: what driver shall be used by the factory to actually get database connections: 'com.mysql.jdbc.Driver'.

Additionally (and one level down, if you will) the 'Driver' named in '!driverClassName' is itself configured through the URL used when a new connection is created

I have put the 'Resource' element into the 'GlobalNamingResource' element instead of the 'Context' element. If you want to put it in the 'Context' element, the 'Realm' must be in the same 'Context' element and must additionally have the attribute 'localDataSource' set to 'true'.

Wiki Markup
\[http://jakarta.apache.org/tomcat/tomcat-5.5-doc/config/server.html Tomcat 5.5 'server' element configuration\].

Wiki Markup
\[http://jakarta.apache.org/tomcat/tomcat-5.5-doc/jndi-datasource-examples-howto.html Tomcat 5.5 JNDI-DataSource examples\]

Wiki Markup
\[http://jakarta.apache.org/tomcat/tomcat-5.5-doc/config/context.html#Resource%20Definitions The definition of 'resource'\]

Wiki Markup
\[http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-resources-howto.html Tomcat 4.1 JDNI resources howto\] \\

No Format


<Server ...>

    <GlobalNamingResources>

        <Resource name="jdbc/mydatabase"
                  auth="

No Format


<Server ...>

    <GlobalNamingResources>

        <Resource name="jdbc/mydatabase"
                  auth="Container"
                  type="javax.sql.DataSource"
                  factory="org.apache.commons.dbcp.BasicDataSourceFactory"
                  driverClassName="com.mysql.jdbc.Driver"
                  validationQuery="SELECT 1"
                  loginTimeout="10"
                  maxWait="5000"
                  username="i_am_tomcat"
                  password="my_password_is_foo"
                  testOnBorrow="true"
                  url="jdbc:mysql://127.0.0.1/mydatabase?connectTimeout=5000&amp;socketTimeout=8000&amp;useUsageAdvisor=true"
        />

    </GlobalNamingResources>

    ...something something...

</Server>

...

The Realm implementation is 'org.apache.catalina.realm.DataSourceRealm'; it will use a 'javax.sql.DataSource' interface found in the JNDI initial context. The location of that interface inside the JNDI namespace is given by 'dataSourceName': 'java:com/env/jdbc/mydatabase'.

Tomcat 5.5 Realm configuration
Tomcat 5.5 DataSourceRealm

Wiki Markup
\[http://jakarta.apache.org/tomcat/tomcat-5.5-doc/config/realm.html Tomcat 5.5 Realm configuration\]

Wiki Markup\[http://jakarta.apache.org/tomcat/tomcat-5.5-doc/realm-howto.html#DataSourceRealm Tomcat 5.5 DataSourceRealm\] \\

No Format

<Host ....>

  <Context ...> ... </Context>
 
  <Context ...> ... </Context>

  <Realm className="org.apache.catalina.realm.DataSourceRealm"
                    dataSourceName="jdbc/mydatabase"
                    digest="MD5"
                    roleNameCol="web_user_role_name"
                    userCredCol="account_md5_password"
                    userNameCol="account_login"
                    userRoleTable="web_user_role_t"
                    userTable="account_t" />

</Host>

Wiki MarkupFinally, beware \[http://issues.apache.org/bugzilla/show_bug.cgi?id=33357 bug 33357\] in 5.beware bug 33357 in 5.5.7 which should be fixed soon though :-P

Hibernate

Wiki Markup\[http://www.hibernate.org/ Hibernate\] is a powerful, ultra-high performance object/relational persistence and query service for Java. Hibernate lets you develop persistent classes following common Java idiom - including association, inheritance, polymorphism, composition and the Java collections framework.

Wiki Markup\[:DBCP/Hibernate\] configuration \\

FAQ

Q: Is this project still active or have they just not released anything in an excessive period of time?

...

Q: <nowiki>Does the current 1.1 release support the poolable/caching of PreparedStatements. I noted that the PoolableConnectionFactory can take a KeyedObjectPoolFactory as a statement pool factory. But there is not concrete implementation for the KeyedPoolableObjectFactory which is required when creating a GenericKeyedObjectPoolFactory. If I pass in a null as shown in the examples, does it cache prepared statements or should I do that in local objects?</nowiki>

A: ??

Q: Where do I get a concrete example of PerUserPoolDataSource? How do we use it in a situation where multiple pools are required for different modules of a project and their properties differ on the database transactional load?

Q: Can DBCP be compiled with JDK1.3?

A: The ant build has facilities to comment out the JDBC3 method making DBCP source compatible with JDK1.3. DBCP1.2 had some JDK1.4 method but those were removed, see issue 29454.

Q: Uh... what about PoolableConnectionFactory line 51 & others which use Boolean.valueOf(boolean) which was introduced in 1.4?

A: They were replaced: http://cvs.apache.org/viewcvs.cgi/jakarta-commons/dbcp/src/java/org/apache/commons/dbcp/PoolableConnectionFactory.java?r1=1.22&r2=1.23&diff_format=h

Q: Without using validation of connections (testOnBorrow = false, testOnReturn = false, timeBetweenEvictionRunsMillis = -1) and after shutdown and restarting the database again, it looks like the pool is cleaning its old connections by itself. So it turns out that we always have valid connections. How can you explain this and when is explicit validation necessary?

A: During the connection activation (when borrowing a connection) the setAutoCommit and other connection init methods are called. If one of these methods throws a SQLException then the connection is also considered broken and removed from the pool.

So if you are using one of the "default*" properties and the JDBC driver correctly reports the SQLExceptions on the "set*" methods then you don't need an extra validationQuery.

Notes on DBCP Connection Validation

The following links are of use for the discussion:

Yes, prepared statements are being cached. Here is a an example how to use the statement pool:

No Format


ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
    url, username, password);

GenericObjectPool connectionPool = new GenericObjectPool();

// null can be used as parameter because this parameter is set in 
// PoolableConnectionFactory when creating a new PoolableConnection
KeyedObjectPoolFactory statementPool = new GenericKeyedObjectPoolFactory(null);

final boolean defaultReadOnly = false;
final boolean defaultAutoCommit = false;
final String validationQuery = null;
new PoolableConnectionFactory(connectionFactory, connectionPool, statementPool,
      validationQuery, defaultReadOnly, defaultAutoCommit);

...

Q: Where do I get a concrete example of PerUserPoolDataSource? How do we use it in a situation where multiple pools are required for different modules of a project and their properties differ on the database transactional load?

...

Q: Can DBCP be compiled with JDK1.3?

A: The ant build has facilities to comment out the JDBC3 method making DBCP source compatible with JDK1.3. DBCP1.2 had some JDK1.4 method but those were removed, see issue 29454.

...

Q: Uh... what about PoolableConnectionFactory line 51 & others which use Boolean.valueOf(boolean) which was introduced in 1.4?

A: They were replaced: http://cvs.apache.org/viewcvs.cgi/jakarta-commons/dbcp/src/java/org/apache/commons/dbcp/PoolableConnectionFactory.java?r1=1.22&r2=1.23&diff_format=h

...

Q: Without using validation of connections (testOnBorrow = false, testOnReturn = false, timeBetweenEvictionRunsMillis = -1) and after shutdown and restarting the database again, it looks like the pool is cleaning its old connections by itself. So it turns out that we always have valid connections. How can you explain this and when is explicit validation necessary?

A: During the connection activation (when borrowing a connection) the setAutoCommit and other connection init methods are called. If one of these methods throws a SQLException then the connection is also considered broken and removed from the pool.

So if you are using one of the "default*" properties and the JDBC driver correctly reports the SQLExceptions on the "set*" methods then you don't need an extra validationQuery.

Notes on DBCP Connection Validation

The following links are of use for the discussion:

org.apache.commons.pool.PoolableObjectFactory.validateObject(java.lang.Object)
org.apache.commons.dbcp.PoolableConnectionFactory
org.apache.commons.dbcp.PoolableConnectionFactory.validateConnection(java.sql.Connection)
org.apache.commons.pool.StackObjectPool

Wiki Markup

\[http://jakarta.apache.org/commons/pool/apidocs/org/apache/commons/pool/PoolableObjectFactory.html#validateObject(java.lang.Object) org.apache.commons.pool.PoolableObjectFactory.validateObject(java.lang.Object)\]

Wiki Markup
\[http://jakarta.apache.org/commons/dbcp/apidocs/org/apache/commons/dbcp/PoolableConnectionFactory.html org.apache.commons.dbcp.PoolableConnectionFactory\]

Wiki Markup

\[http://jakarta.apache.org/commons/dbcp/apidocs/org/apache/commons/dbcp/PoolableConnectionFactory.html#validateConnection(java.sql.Connection) org.apache.commons.dbcp.PoolableConnectionFactory.validateConnection(java.sql.Connection)\]

Wiki Markup
\[http://jakarta.apache.org/commons/pool/apidocs/org/apache/commons/pool/impl/StackObjectPool.html org.apache.commons.pool.StackObjectPool\]

Wiki Markup\[http://jakarta.apache.org/commons/pool/apidocs/org/apache/commons/pool/impl/GenericObjectPool.html org.apache.commons.pool.GenericObjectPool\] \\

Q: What is "Validation of Connections"?

...

There is a 'autoReconnectForPools' starting from MySQL-Connector-J which I haven't looked into yet.

Wiki MarkupSee \[http://dev.mysql.com/doc/connector/j/en/index.html#id2425656 Connector-J Connection Properties\] for additional details, and also the source for _com.mysql.jdbc.Connection.execSQL()_.

Page tree

Versions Compared

Old Version 24

New Version Current

Key

DBCP Overview

Release Plans

External Resources

Security Manager settings

Security Manager settings

Tomcat 5.Tomcat 5.0 Configuration examples

Hibernate

FAQ

Notes on DBCP Connection Validation

Notes on DBCP Connection Validation

Page tree

Page History

Versions Compared

Old Version 24

New Version Current

Key

DBCP Overview

Release Plans

External Resources

Security Manager settings

Security Manager settings

Tomcat 5.Tomcat 5.0 Configuration examples

Hibernate

FAQ

Notes on DBCP Connection Validation

Notes on DBCP Connection Validation