JNDI Datasource How-To

Table of Contents

Introduction

JNDI Datasource configuration is covered extensively in the JNDI-Resources-HOWTO. However, feedback from tomcat-user has shown that specifics for individual configurations can be rather tricky.

Here then are some example configurations that have been posted to tomcat-user for popular databases and some general tips for db usage.

You should be aware that since these notes are derived from configuration and/or feedback posted to tomcat-user YMMV :-). Please let us know if you have any other tested configurations that you feel may be of use to the wider audience, or if you feel we can improve this section in anyway.

Please note that JNDI resource configuration changed somewhat between Tomcat 7.x and Tomcat 8.x as they are using different versions of Apache Commons DBCP library. You will most likely need to modify older JNDI resource configurations to match the syntax in the example below in order to make them work in Tomcat 8. See Tomcat Migration Guide for details.

Also, please note that JNDI DataSource configuration in general, and this tutorial in particular, assumes that you have read and understood the Context and Host configuration references, including the section about Automatic Application Deployment in the latter reference.

DriverManager, the service provider mechanism and memory leaks

java.sql.DriverManager supports the service provider mechanism. This feature is that all the available JDBC drivers that announce themselves by providing a META-INF/services/java.sql.Driver file are automatically discovered, loaded and registered, relieving you from the need to load the database driver explicitly before you create a JDBC connection. However, the implementation is fundamentally broken in all Java versions for a servlet container environment. The problem is that java.sql.DriverManager will scan for the drivers only once.

The JRE Memory Leak Prevention Listener that is included with Apache Tomcat solves this by triggering the driver scan during Tomcat startup. This is enabled by default. It means that only libraries visible to the common class loader and its parents will be scanned for database drivers. This include drivers in $CATALINA_HOME/lib, $CATALINA_BASE/lib, the class path and (where the JRE supports it) the endorsed directory. Drivers packaged in web applications (in WEB-INF/lib) and in the shared class loader (where configured) will not be visible and will not be loaded automatically. If you are considering disabling this feature, note that the scan would be triggered by the first web application that is using JDBC, leading to failures when this web application is reloaded and for other web applications that rely on this feature.

Thus, the web applications that have database drivers in their WEB-INF/lib directory cannot rely on the service provider mechanism and should register the drivers explicitly.

The list of drivers in java.sql.DriverManager is also a known source of memory leaks. Any Drivers registered by a web application must be deregistered when the web application stops. Tomcat will attempt to automatically discover and deregister any JDBC drivers loaded by the web application class loader when the web application stops. However, it is expected that applications do this for themselves via a ServletContextListener.

Database Connection Pool (DBCP 2) Configurations

The default database connection pool implementation in Apache Tomcat relies on the libraries from the Apache Commons project. The following libraries are used:

  • Commons DBCP 2
  • Commons Pool 2

These libraries are located in a single JAR at $CATALINA_HOME/lib/tomcat-dbcp.jar. However, only the classes needed for connection pooling have been included, and the packages have been renamed to avoid interfering with applications.

DBCP 2 provides support for JDBC 4.1.

Installation

See the DBCP 2 documentation for a complete list of configuration parameters.

Preventing database connection pool leaks

A database connection pool creates and manages a pool of connections to a database. Recycling and reusing already existing connections to a database is more efficient than opening a new connection.

There is one problem with connection pooling. A web application has to explicitly close ResultSet's, Statement's, and Connection's. Failure of a web application to close these resources can result in them never being available again for reuse, a database connection pool "leak". This can eventually result in your web application database connections failing if there are no more available connections.

There is a solution to this problem. The Apache Commons DBCP 2 can be configured to track and recover these abandoned database connections. Not only can it recover them, but also generate a stack trace for the code which opened these resources and never closed them.

To configure a DBCP 2 DataSource so that abandoned database connections are removed and recycled, add one or both of the following attributes to the Resource configuration for your DBCP 2 DataSource: 

removeAbandonedOnBorrow=true
removeAbandonedOnMaintenance=true

The default for both of these attributes is false. Note that removeAbandonedOnMaintenance has no effect unless pool maintenance is enabled by setting timeBetweenEvictionRunsMillis to a positive value. See the DBCP 2 documentation for full documentation on these attributes.

Use the removeAbandonedTimeout attribute to set the number of seconds a database connection has been idle before it is considered abandoned. 

removeAbandonedTimeout="60"

The default timeout for removing abandoned connections is 300 seconds.

The logAbandoned attribute can be set to true if you want DBCP 2 to log a stack trace of the code which abandoned the database connection resources. 

logAbandoned="true"

The default is false.

MySQL DBCP 2 Example

0. Introduction

Versions of MySQL and JD