Databases and Tomcat, Page 3
To improve database performance, you should use a technique known as connection pooling. This approach involves implementing a broker class that encapsulates access to the database. Typically the connection broker class has a getConnection() method that returns a proxy object that can partake in connection pooling. In other words, the connections do not connect directly to the database but will share a pool of available connections. New connections are added to the pool if demand exceeds current capacity, and excess connections can be closed down during idle periods.
As far as your written code is concerned, the connection behaves like a normal JDBC connection but the connection broker manages the pool of actual connections to the database.
Connection pooling is a well-understood technique, and many JDBC driver suppliers provide connection pooling implementations with their drivers. The J2EE specification has extended the JDBC support to include the DataSource class that may be used to support connection pooling in a portable manner.
Tomcat has adopted the J2EE specification and uses the javax.sql.DataSource class to provide connection pooling as discussed in the next section, "Tomcat Data Sources."
Note - If you want to use connection pooling outside of Tomcat, or do not want to use the Tomcat data source implementation, then there are several open-source connection pooling implementations available from the Internet. A popular, freely available broker that works with any JDBC driver is DbConnectionBroker, which is available from http://www.javaexchange.com.
Tomcat Data Sources
The recommended approach for accessing databases from Tomcat is to use the Database Connection Pool (DBCP) connection broker incorporated into Tomcat. DBCP is part of the Jakarta commons subproject that can be found at http://jakarta.apache.org/commons. DBCP has many advantages for Web application developers:
It supports connection pooling.
It manages the database driver and connection URL information.
It is J2EE compliant.
It is a standard component of Tomcat.
Not all beta releases of the Tomcat 4.1 archive included the DBCP package. If the DBCP package is not included in your Tomcat 4.1 archive, you will need to download and install DBCP yourself. If your Tomcat archive contains the file <CATALINA_HOME>/common/lib/commons-dbcp.jar, DBCP is included with Tomcat.
If DBCP is not included with your Tomcat archive, download it from http://jakarta.apache.org/builds/jakarta-commons. At the time of writing this book (mid-2002), DBCP had not yet reached the release milestone build and must be downloaded from the latest nightly build at http://jakarta.apache.org/builds/jakarta-commons/nightly/commons-dbcp/. If a release version is available from http://jakarta.apache.org/builds/jakarta-commons/release/commons-dbcp/, you should use it in preference to a nightly build.
Download and extract the commons-dbcp.tar archive and copy the commons-dbcp.jar to <CATALINA_HOME>/common/lib.
DBCP uses the Jakarta Commons Pool package. At the time of this writing, the commons-pool package was at release 1.0, if a later release is available you should use it. The commons-pool-1.0.tar archive must be downloaded from http://builds/jakarta-commons/release/commons-pool/. Move the commons-pool.jar from the extracted archive to <CATALINA_HOME>/common/lib.
After the commons-dbcp.jar and commons-pool.jar files are stored in <CATALINA_HOME>/common/lib, you can use the Tomcat data sources as described in the rest of this section. If the DBCP classes are not included with Tomcat, you will receive a "javax.naming.NamingException: Cannot create resource instance" error when accessing the data source.
Note - Tomcat 4.0 uses a third-party connection broker called Tyrex (see http://www.tyrex.com) which has been replaced by the DBCP connection broker. Tyrex has the same functionality and advantages as DBCP and is included with the Tomcat 4.0 download.
A minor complexity to using DBCP is that it uses the Java Naming Directory Interface (JNDI), and you have to configure the JNDI data source before you can use it. Here you will be shown how to define a JDBC data source without any unnecessary background information about JNDI.
To use a Tomcat data source you will have to
Define a JNDI resource reference in your Web application deployment descriptor
Map the JNDI resource reference onto a real resource (database connection) in the context of your application
Look up the JNDI data source resource reference in your code to obtain a pooled database connection
Defining a Resource Reference
First, you will need a JNDI name for your database connection; conventionally it should begin with jdbc/. The example uses the name jdbc/conversion. Now add a <resource-ref> element to the web.xml file for your Web application to define the data source as follows:
<resource-ref> <res-ref-name>jdbc/conversion</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> </resource-ref>
The <res-ref-name> element identifies the resource reference name, the <res-type> entry defines the reference as a JDBC data source, and the <res-auth> specifies that resource authentication is applied by the Container. The <resource-ref> element must be added after any <servlet-mapping> elements in the deployment descriptor.
Defining the Resource
Second, you must add an entry for the database resource to the <CATALINA_HOME>/conf/server.xml file by adding a <ResourceParams> element to define the database connection information. The <ResourceParams> can be defined inside the <DefaultContext> tag to be available to all Web applications, or it can be defined inside the <Context> element for a specific application.
A suitable Tomcat 4.1 <ResourceParams> element for the example database is shown in the following listing (Tomcat 4.0 uses different attribute names as explained in the notes):
<Context path="/database" docBase="database" debug="0" reloadable="true" > <ResourceParams name="jdbc/conversion"> <parameter> <name>username</name> <value>root</value> </parameter> <parameter> <name>password</name> <value>secret</value> </parameter> <parameter> <name>driverClassName</name> <value>org.gjt.mm.mysql.Driver</value> </parameter> <parameter> <name>url</name> <value>jdbc:mysql://localhost/test</value> </parameter> </ResourceParams> </Context>
In the <ResourceParams> element example, the name attribute must exactly match the <res-ref-name> value you defined in the <resource-ref> element of the web.xml file.
Note - As an alternative to using resource references in an application's web.xml file, you can define the resource using a <Resource> element in the server.xml file. You can add a <resource> element to the <Context> element for an individual application or to the <DefaultContext> element to define the resource for all Web applications. The <Resource> definition for the example JDBC data source is
The various <ResourceParams> components define the database connection parameters as follows:
Username for the database (root); under Tomcat 4.0 this attribute is called user
Password for the user root (secret)
Driver class for the database connection (org.gjt.mm.mysql.Driver)
JDBC connection string for the database (jdbc:mysql://localhost/test); under Tomcat 4.0 this attribute is called driverName
Caution - The server.xml file contains the unencrypted password for accessing the database. This file should be secured so that unauthorized users cannot read it.
The <ResourceParams> element in the server.xml file encapsulates all the information required to access the database. When moving your application from a development environment to a live environment, you need only change the values for the parameters in the server.xml file. Neither the Java code nor the web.xml resource reference entry need to be modified.
Using Tomcat data sources decouples the database vendor connection details from the servlet code and gives you the performance benefits gained from using a connection pool. Put simply, always use Tomcat's data sources when accessing a database.
Caution - When using data sources with both Tomcat 4.1 and Tomcat 4.0, you must place the JDBC JAR file for your database driver in the <CATALINA_HOME>/common/lib directory; otherwise, Tomcat will not be able to load the JDBC driver.