JDBC Data Sources

JDBC Data Sources

0.  Introduction

Many web applications need to access a database via a JDBC driver,    to support the functionality required by that application.  The Java EE    Platform Specification requires Java EE Application Servers to make    available aDataSource implementation (that is, a connection    pool for JDBC connections) for this purpose.  Tomcat offers exactly    the same support, so that database-based applications you develop on    Tomcat using this service will run unchanged on any Java EE server.

For information about JDBC, you should consult the following:

• http://java.sun.com/products/jdbc/ -        Home page for information about Java Database Connectivity.
• http://java.sun.com/j2se/1.3/docs/guide/jdbc/spec2/jdbc2.1.frame.html -        The JDBC 2.1 API Specification.
• http://java.sun.com/products/jdbc/jdbc20.stdext.pdf -        The JDBC 2.0 Standard Extension API (including the        javax.sql.DataSource API).  This package is now known        as the "JDBC Optional Package".
• http://java.sun.com/j2ee/download.html -        The Java EE Platform Specification (covers the JDBC facilities that        all Java EE platforms must provide to applications).

NOTE - The default data source support in Tomcat    is based on theDBCP connection pool from the     Commons    project.  However, it is possible to use any other connection pool    that implementsjavax.sql.DataSource, by writing your    own custom resource factory, as described    below.

1.  Install Your JDBC Driver

Use of the JDBC Data Sources JNDI Resource Factory requires    that you make an appropriate JDBC driver available to both Tomcat internal    classes and to your web application.  This is most easily accomplished by    installing the driver's JAR file(s) into the     $CATALINA_HOME/lib directory, which makes the driver    available both to the resource factory and to your application.

2.  Declare Your Resource Requirements

Next, modify the web application deployment descriptor    (/WEB-INF/web.xml) to declare the JNDI name under    which you will look up preconfigured data source.  By convention, all such    names should resolve to the jdbc subcontext (relative to the    standardjava:comp/env naming context that is the root of    all provided resource factories.  A typicalweb.xml entry    might look like this:

<resource-ref>  <description>    Resource reference to a factory for java.sql.Connection    instances that may be used for talking to a particular    database that is configured in the <Context>    configurartion for the web application.  </description>  <res-ref-name>    jdbc/EmployeeDB  </res-ref-name>  <res-type>    javax.sql.DataSource  </res-type>  <res-auth>    Container  </res-auth></resource-ref>

WARNING - Be sure you respect the element ordering    that is required by the DTD for web application deployment descriptors!    See the    Servlet    Specification for details.

3.  Code Your Application's Use Of This Resource

A typical use of this resource reference might look like this:

Context initCtx = new InitialContext();Context envCtx = (Context) initCtx.lookup("java:comp/env");DataSource ds = (DataSource)  envCtx.lookup("jdbc/EmployeeDB");Connection conn = ds.getConnection();... use this connection to access the database ...conn.close();

Note that the application uses the same resource reference name that was    declared in the web application deployment descriptor. This is matched up    against the resource factory that is configured in the    <Context> element for    the web application as described below.

4.  Configure Tomcat's Resource Factory

To configure Tomcat's resource factory, add an element like this to the     <Context> element for    the web application.

<Context ...>  ...  <Resource name="jdbc/EmployeeDB"            auth="Container"            type="javax.sql.DataSource"            username="dbusername"            password="dbpassword"            driverClassName="org.hsql.jdbcDriver"            url="jdbc:HypersonicSQL:database"            maxActive="8"            maxIdle="4"/>  ...</Context>

Note that the resource name (here, jdbc/EmployeeDB) must    match the value specified in the web application deployment descriptor.

This example assumes that you are using the HypersonicSQL database    JDBC driver.  Customize thedriverClassName and    driverName parameters to match your actual database's    JDBC driver and connection URL.

The configuration properties for Tomcat's standard data source    resource factory    (org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory) are    as follows:

• driverClassName - Fully qualified Java class name        of the JDBC driver to be used.
• username - Database username to be passed to our        JDBC driver.
• password - Database password to be passed to our        JDBC driver.
• url - Connection URL to be passed to our JDBC driver.        (For backwards compatibility, the propertydriverName        is also recognized.)
• initialSize - The initial number of connections        that will be created in the pool during pool initialization. Default: 0
• maxActive - The maximum number of connections        that can be allocated from this pool at the same time. Default: 8
• minIdle - The minimum number of connections that        will sit idle in this pool at the same time. Default: 0
• maxIdle - The maximum number of connections that        can sit idle in this pool at the same time. Default: 8
• maxWait - The 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. Default: -1 (infinite)

Some additional properties handle connection validation:

• validationQuery - SQL query that can be used by the        pool to validate connections before they are returned to the        application.  If specified, this query MUST be an SQL SELECT        statement that returns at least one row.
• validationQueryTimeout - Timeout in seconds        for the validation query to return. Default: -1 (infinite)
• testOnBorrow - true or false: whether a connection        should be validated using the validation query each time it is        borrowed from the pool. Default: true
• testOnReturn - true or false: whether a connection        should be validated using the validation query each time it is        returned to the pool. Default: false

The optional evictor thread is responsible for shrinking the pool    by removing any conections which are idle for a long time. The evictor    does not respectminIdle. Note that you do not need to    activate the evictor thread if you only want the pool to shrink according    to the configuredmaxIdle property.

The evictor is disabled by default and can be configured using    the following properties:

• timeBetweenEvictionRunsMillis - The number of        milliseconds between consecutive runs of the evictor.        Default: -1 (disabled)
• numTestsPerEvictionRun - The number of connections        that will be checked for idleness by the evitor during each        run of the evictor. Default: 3
• minEvictableIdleTimeMillis - The idle time in        milliseconds after which a connection can be removed from the pool        by the evictor. Default: 30*60*1000 (30 minutes)
• testWhileIdle - true or false: whether a connection        should be validated by the evictor thread using the validation query        while sitting idle in the pool. Default: false

Another optional feature is the removal of abandoned connections.    A connection is called abandoned if the application does not return it    to the pool for a long time. The pool can close such connections    automatically and remove them from the pool. This is a workaround    for applications leaking connections.

The abandoning feature is disabled by default and can be configured    using the following properties:

• removeAbandoned - true or false: whether to        remove abandoned connections from the pool. Default: false
• removeAbandonedTimeout - The number of        seconds after which a borrowed connection is assumed to be abandoned.        Default: 300
• logAbandoned - true or false: whether to log        stack traces for application code which abandoned a statement        or connection. This adds serious overhead. Default: false

Finally there are various properties that allow further fine tuning    of the pool behaviour:

• defaultAutoCommit - true or false: default        auto-commit state of the connections created by this pool.        Default: true
• defaultReadOnly - true or false: default        read-only state of the connections created by this pool.        Default: false
• defaultTransactionIsolation - This sets the        default transaction isolation level. Can be one of        NONE, READ_COMMITTED,        READ_UNCOMMITTED,REPEATABLE_READ,        SERIALIZABLE. Default: no default set
• poolPreparedStatements - true or false: whether to        pool PreparedStatements and CallableStatements. Default: false
• maxOpenPreparedStatements - The maximum number of open        statements that can be allocated from the statement pool at the same time.        Default: -1 (unlimited)
• defaultCatalog - The name of the default catalog.        Default: not set
• connectionInitSqls - A list of SQL statements        run once after a Connection is created. Separate multiple statements        by semicolons (;). Default: no statement
• connectionProperties - A list of driver specific        properties passed to the driver for creating connections. Each        property is given asname=value, multiple properties        are separated by semicolons (;). Default: no properties
• accessToUnderlyingConnectionAllowed - true or false: whether        accessing the underlying connections is allowed. Default: false

For more details, please refer to the commons-dbcp documentation.