JDBC Data source uses Jndi connection pooling to implement database connections

0. Introduction

Many Web applications require access to the database through the JDBC driver to support the functionality required by the application. The Java EE Platform specification requires a Java EE application Server to provide a DataSource implementation for this purpose (that is, a connection pool for JDBC connections). Tomcat provides the exact same support, so database-based applications developed on Tomcat using this service will remain unchanged on any Java EE server.

For information on JDBC, refer to the following:

• Http://www.oracle.com/technetwork/java/javase/jdbc/index.html-Home page for Java database connection information.

• Http://java.sun.com/j2se/1.3/docs/guide/jdbc/spec2/jdbc2.1.frame.html-JDBC 2.1 API specification.

• Http://java.sun.com/products/jdbc/jdbc20.stdext.pdf-JDBC 2.0 standard extension API (including javax.sql.DataSource API). This package is now referred to as the "JDBC Optional package".

• Http://www.oracle.com/technetwork/java/javaee/overview/index.htm-Java EE Platform Specification (includes JDBC tools that all Java EE platforms must provide to the application).

Note -The default data source in Tomcat supports DBCP connection pooling based on the Commons project. However, you can also use the implementation of any other connection pool, by writing your own custom resource factory, as described below.javax.sql.DataSource

1. Installing the JDBC Driver

Using the JDBC data source Jndi Resource Factory requires that you provide the appropriate JDBC driver for the Tomcat internal classes and Web applications. This is easy to do by installing the driver's jar file into the $CATALINA_HOME/lib directory, which makes the driver available both for the resource factory and for your application.

2. Statement of your resource requirements

Next, modify the Web application Deployment Descriptor ( /WEB-INF/web.xml ) to declare the Jndi name, where you will find the preconfigured data source. By convention, all such names should be parsed into jdbc child contexts (as opposed to a java:comp/env standard naming context as the root of all the provided resource factories, a typical web.xml entry might be this:

<Resource-ref>  <Description>Resource reference to a factory for java.sql.Connection instances so may is used for talking to a particular Database that's configured in the<Context>configuration 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 -Make sure that you follow the ordering of the elements required by the DTD for the Web application Deployment Descriptor! For more information, see the servlet specification.

3. Write your application to use this resource

A typical usage of this resource reference might be as follows:

New= (Context) initctx.lookup ("java:comp/env"= (DataSource)  envctx.lookup (" Jdbc/employeedb "= This connection to access the database ... conn.close ();

Note that the application uses the same resource reference name declared in the Web application deployment descriptor. This <Context> matches the resource factory configured in the elements of the Web application, as described below.

4. Configure the Tomcat resource factory

To configure the resource factory for Tomcat, add such elements to the <Context> elements of the Web application.

<Context...>  ...  <Resourcename= "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 driverClassName and driverName parameters to match the JDBC driver and connection URL of your actual database.

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

• Driverclassname -The fully qualified Java class name of the JDBC driver to use.

• username -the database user name to pass to our JDBC driver.

• Password -The database password is passed to our JDBC driver.

• URL -the connection URL to pass to our JDBC driver. (This property is also recognized for backwards compatibility driverName .) ）

• InitialSize -The number of initial connections that will be created in the pool during pool initialization. Default value: 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 are idle in this pool at the same time. Default value: 0

• Maxidle -The maximum number of connections that can be idle in this pool at the same time. Default: 8

• maxwait -The maximum number of milliseconds the pool waits before throwing an exception (when no connection is available). Default value:-1 (unlimited)

Some additional properties handle connection validation:

• validationquery -SQL query that can be used to validate the connection before the pool is returned to the application. If specified, the query must be a SQL SELECT statement that returns at least one row.

• validationquerytimeout -Verify that the query returns the number of seconds in time. Default value:-1 (unlimited)

• Testonborrow -True or false: verifies that the connection is validated each time a validation query is borrowed from the pool. Default value: True

• Testonreturn -True or false: Verifies the connection with a validation query each time the connection is returned to the pool. Default: False

The optional Evictor thread is responsible for shrinking the pool by removing any connections that are in an idle state for a long time. Veterans do not respect minIdle . Note that if you only want the pool to shrink based on the configured maxIdle properties, you do not need to activate the evictor thread.

Evictor is disabled by default and can be configured with the following properties:

• Timebetweenevictionrunsmillis -The number of milliseconds between successive runs of the eject device. Default value:-1 (disabled)

• Numtestsperevictionrun -The number of idle connections will be checked by the inspector during each run of the eviction. Default: 3

• Minevictableidletimemillis -The idle time, in seconds, after which the connection can be removed from the pool by an eviction. Default value: 30 * 60 * 1000 (30 minutes)

• Testwhileidle -True or false: If the connection should be validated by the evictor thread when it is idle in the pool, use a validation query. Default: False

Another optional feature is to delete an obsolete connection. If the application has not returned it to the pool for a long time, the connection is said to be discarded. The swimming pool can automatically turn off this type of connection and remove it from the pool. This is a workaround for leaking connected applications.

The discard feature is disabled by default and can be configured with the following properties:

• removeabandoned -True or false: whether to remove discarded connections from the pool. Default: False

• Removeabandonedtimeout -The number of seconds that the borrowed connection was discarded. Default value: 300

• logabandoned -True or false: the stack trace is logged for the discard statement or the connected application code. This adds a serious overhead. Default: False

Finally, there are various properties that can further adjust the behavior of the pool:

• Defaultautocommit -True or false: the default autocommit status for connections created by this pool. Default value: True

• defaultreadonly -True or false: the default read-only state of connections created by this pool. Default: False

• defaulttransactionisolation -This sets the default transaction isolation level. Can be a NONE ,,, READ_COMMITTED READ_UNCOMMITTED REPEATABLE_READ , SERIALIZABLE . Default: No default settings

• poolpreparedstatements -True or false: whether to pool preparedstatements and callablestatements. Default: False

• maxopenpreparedstatements -The maximum number of open statements that can be allocated concurrently from the statement pool. Default value:-1 (unlimited)

• Defaultcatalog -the name of the default directory. Default: Not set

• Connectioninitsqls -runs the SQL statement once after the connection is created. ;separate multiple statements with semicolons (). Default: No statement

• connectionproperties -a list of driver-specific properties that are passed to the driver to create the connection. Each property is given as name=value multiple attributes separated by semicolons ( ; ). Default value: No attributes

• accesstounderlyingconnectionallowed -True or false: allows access to the underlying connection. Default: False

JDBC Data source uses Jndi connection pooling to implement database connections