Tomcat MySQL Connection – Using JDBC to Connect Tomcat to MySQL

Stumped by Tomcat JDBC connections? Can’t get your head around MySQL drivers, connection pools, and JNDI resources? This article will guide you step-by-step through connecting your MySQL database to Tomcat, in plain English. Along the way, we’ll also cover setting up connection pooling, programmatic configuration, and global naming resources, to ensure that your configuration is the best for your needs.

Need to configure MySQL / JDBC on more than one Tomcat instance? Tcat allows you to save common configurations and apply them to any instance instantly. Using clusters? Use Tcat to manage their configurations as a group.

How Tomcat Connects to Databases

Before we start setting up the MySQL connection, let’s take a quick look at the framework Tomcat uses to handle database connections, a technology called Java Database Connectivity, or JDBC.

How JDBC Works

JDBC provides an abstraction layer between Java applications and database servers, so that an application’s code does not need to be altered in order for it to communicate with multiple database formats. Rather than connecting to the database directly, the applications send requests to the JDBC API, which in turn communicates with the specified database through a driver that converts the API calls into the proper dialect for the database to understand.

If a developer wishes to access two different database formats in the same program, they don’t need to add any additional syntax to their code; they simply call two different JDBC drivers.

Preventing Bottlenecks using DBCP

The problem with using JDBC in a high-load environment is that it can become overwhelmed with requests, and begin dropping them or delaying them, causing a bottleneck in the architecture that slows down the entire application. To avoid this situation, Tomcat uses a technique called connection thread pooling, implemented through a technology called Commons DBCP. Rather than creating new connections every time a new request comes in, the requests are queued, and matched with a pool of pre-generated connections as they become available. Using JDBC also allows developers to assume that each request will receive its own JDBC connection, which significantly simplifies the transaction code.

Tomcat makes it easy to use this technology by allowing it to be defined as a JNDI Resource, and handling the actual transaction of the connections itself. If this still seems confusing, don’t worry – we’ll go over all of the steps required to get these technologies working for you in the next section.

Connecting Tomcat to a MySQL Database

Now that we have a basic understanding of how Tomcat uses JDBC, DBCP, and JNDI to connect to databases, it’s time to look at the specific steps required to get Tomcat talking to MySQL.

Step 1: Download the MySQL JDBC Driver

The driver that JDBC needs to connect to MySQL is called Connector/J. It is developed by the MySQL engineering team, and the latest version is available for free from their website.

Download the binary distribution appropriate for your platform, extract the JAR file, and copy it to “$CATALINA_HOME/lib”. This makes the driver available to JDBC.

(Note: if you’ve read instructions that talk about Classpath, just ignore them – they’re not applicable to recent versions of Tomcat.)

Step 2: Configure Your MySQL Database as a JNDI Resource

Once you’ve installed the MySQL driver, all you have left to do to begin accessing your database is to configure the database as a JNDI Resource. The Java Servlet specification that Tomcat implements requires resources to be declared in two places:

As a Resource in your application’s Context element. This will direct Tomcat to create the resource when the application launches. Alternatively, if you want the database to be available to multiple applications, you can provide this information in Tomcat’s server.xml file. We’ll cover both situations.
As a Resource Reference in your application’s “WEB-INF/web.xml” file. This provides a references to your database that does not contain information specific to your servers, so that the application can easily be ported to another server environment.

Step 2a: JDBC Resource Configuration

The Resource Element defines server-specific information about your database, including URL, database type , driver, and authentication information. As mentioned above, there are two ways to define this information – as a GlobalNamingResource, if multiple Contexts will need access to a single database, or on a Context by Context basis.

As the second of these is the more common usage, let’s look at that first. If you’ll need to use a GlobalNamingResource, click here to skip ahead.

Per-Context JDBC Resource Configuration

All actively supported versions of Tomcat allow configuration of Contexts through either an entry in Server.xml or through a Context XML fragment in your application’s “META-INF/context.xml” file. We strongly recommend you use the second option, as this will allow you to make changes to your database configuration without restarting your entire server, and also make your application more portable.

The Resource element is used to define the JNDI/JDBC resource, as well as set connection pooling attributes. (You can find more information on determining the proper connection pooling attributes later in this article). It is nested inside the Context element.

Here is a sample MySQL Resource configuration entry. Replace the [placeholders] with the proper values for your server. For most users, there should be no need to modify the “type”, “driverClassName”, or “auth” attributes:

<Resource name=”jdbc/[YourDatabaseName]”

auth=”Container”

type=”javax.sql.DataSource”

username=”[DatabaseUsername]”

password=”[DatabasePassword]”

driverClassName=”com.mysql.jdbc.Driver”

url=”jdbc:mysql://[yourserver]:3306/[yourapplication]”

maxActive=”15″

maxIdle=”3″/>

</Context>

It is important to note that:

Your MySQL configuration MUST specify a username and password for JDBC to connect.
If your application will connect to more than one database, you can define the additional databases in additional Resource elements, but each must have a unique name.
If you want to use Tomcat’s Container Managed Security to provide additional security for your database, you can use a Realm element in conjunction with security constraints defined in WEB-INF/web.xml. This is outside the scope of this article, but you can find full documentation on the Apache project site.

Using a ResourceLink Element

If you will be accessing a globally declared JDBC resource, as outlined in the next section, use a ResourceLink entry in place of the Resource element, as in the example below:

<ResourceLink name=”jdbc/[ContextSpecificName]”

global=”jdbc/[GlobalResourceName]”

type=”javax.sql.DataSource”/>

</Context>

The “name” attribute allows you to define a per-Context name for the resource, that can be used in your application code, as long as the “global” value correctly identifies the referenced resource. ResourceLink elements can also be defined on a per-host basis, and referenced via web.xml entries, as outlined later in this article. Isn’t flexibility great?

Configuring JDBC Resources as GlobalNamingResources

If you want a single database to be accessible to multiple Contexts, you can define it in server.xml, and access it on a per-context basis through a ResourceLink element.

Here is a sample GlobalNamingResources configuration. The only major difference between this example and the per-Context configuration is its location:

<Resource name=”jdbc/[YourDatabaseName]”

auth=”Container”

type=”javax.sql.DataSource”

username=”[DatabaseUsername]”

password=”[DatabasePassword]”

driverClassName=”com.mysql.jdbc.Driver”

description=”Global Address Database”

url=”jdbc:mysql://[yourserver]:3306/[yourapplication]”

maxActive=”15″

maxIdle=”3″/>

</GlobalNamingResources>

It’s important to note that:

If you will be using Realms to manage the security of the database, they should always refer to the Resource by the name you define in GlobalNamingResources, even if you have defined any per-context names in “WEB-INF/web.xml”, or the Realm will not function correctly.
If you will be accessing this resource via a Resource Reference on a per-context basis, you MUST provide a value for the “auth” attribute. Valid values for this attribute are “Container”, in which case the Container will login on behalf of the application, or Application, in which case the application will provide credentials. The correct configuration will vary dependent on your application code.
For improved portability, Global Resources can be renamed on a per-Context or per-Host basis using the ResourceLink element, as outlined in the previous section.

Step 2b: Configuring Resource References

Now that you’ve configured the main Resource entry, it’s time to provide Resource Reference information, which will make your application more portable. This is done on a per-Context basis, in “WEB-INF/web.xml”.

Here is an example Resource Reference configuration entry:

<web-app>

<resource-ref>

<description>[Your Description]</description>

<res-ref-name>jdbc/[YourDatabaseName]</res-ref-name>

<res-type>javax.sql.DataSource</res-type>

<res-auth>Container</res-auth>

</resource-ref>

</web-app>

It is important to note that:

You cannot define a new name for your Resource here. The value of “res-ref-name” must refer either to a Resource directly configured in a Context element, or to a renamed Global Resource, as defined by a ReferenceLink element nested within a Context or Host element.
Any additional deployment descriptors must be ordered as defined in the Servlet Specification.
The <res-ref-name> element defines the name that you will use in your application will use to access the code, so MAKE SURE IT’S RIGHT. Here’s an example resource reference you might use in your application code:

Context initCtx = new InitialContext();

Context envCtx = (Context) initCtx.lookup(“java:comp/env”);

DataSource ds = (DataSource)

envCtx.lookup(“jdbc/[YourResourceName]”);

That’s it! After you have restarted Tomcat and/or re-deployed your application, depending on how you chose to configure your database, Tomcat should be able to access MySQL as a JDBC Resource.

Common Problems And How To Fix Them

Like all pooling implementations, connection pooling can cause a few annoying problems if it is not configured correctly. Here are a few common connection pooling issues and how to fix them.

Connection Closed Exceptions

To successfully use a connection pool, you have to write code that respects the fact that connections will need to be re-used by other requests. This means that your code should include provisions for gracefully returning connections to the pool after using them.

If you are experiencing a lot of Connection Closed exceptions, chances are that your requests are attempting to close connections twice, as if they were normal connections, instead of closing them once and then checking to make sure they are closed.

This distinction is important, because closing a connection that is part of a pool does not simply close the connection – it returns it to the pool. If your code simply closes the connection twice with a “finally” statement, your old requests may be closing connections that are in use by new requests, which is what is causing your exceptions. Adding “conn = null” statements to your code both before the “finally” statement and as a part of the “finally” loop will check the status of the connection, to ensure that this does not occur.

dB Connection Time Out

The purpose of connection timeout is to keep invalid requests from tying up the server. Unfortunately, sometimes the server can tie up the server, and blame it on the request. This results in valid requests timing out. The most common reason that this occurs is JVM garbage collection. The JVM temporarily freezes all its running processes when it collects garbage, which can result in request timeout. There are two ways to mitigate this issue.

The first and best method is to properly tune your JVM’s garbage collection so that it does not interfere with Tomcat. There is really no reason your garbage collection should take more than a second if you have configured your JVM correctly for your server load and applications.

For more information about tuning your JVM, please visit our handy guide to Tomcat JVM Tuning.

The second thing that can cause this problem is an improperly configured timeout threshold, which is configured in the Resource element with the “maxWait” attribute. Typically, this should be set to around 10-15 seconds to avoid any problems, although this varies by situation.

Andrew's Garage