- DriverManager, the service provider mechanism and memory leaks
- Database Connection Pool (DBCP 2) Configurations
- Non-DBCP Solutions
- Oracle 8i with OCI client
- Common Problems
JNDI Datasource How-To
Table of Contents
JNDI Datasource configuration is covered extensively in the
JNDI-Resources-HOWTO. However, feedback from
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
provider mechanism. This feature is that all the available JDBC drivers
that announce themselves by providing a
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_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
However, it is expected that applications do this for themselves via
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
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.
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:
The default for both of these attributes is
false. Note that
removeAbandonedOnMaintenance has no effect unless pool
maintenance is enabled by setting
to a positive value. See the
DBCP 2 documentation for full documentation on these attributes.
removeAbandonedTimeout attribute to set the number
of seconds a database connection has been idle before it is considered abandoned.
The default timeout for removing abandoned connections is 300 seconds.
logAbandoned attribute can be set to
if you want DBCP 2 to log a stack trace of the code which abandoned the
database connection resources.
The default is
MySQL DBCP 2 Example
Versions of MySQL and JDBC drivers that have been reported to work:
- MySQL 3.23.47, MySQL 3.23.47 using InnoDB,, MySQL 3.23.58, MySQL 4.0.1alpha
- Connector/J 3.0.11-stable (the official JDBC Driver)
- mm.mysql 2.0.14 (an old 3rd party JDBC Driver)
Before you proceed, don't forget to copy the JDBC Driver's jar into
1. MySQL configuration
Ensure that you follow these instructions as variations can cause problems.
Create a new test user, a new database and a single test table. Your MySQL user must have a password assigned. The driver will fail if you try to connect with an empty password.
mysql> GRANT ALL PRIVILEGES ON *.* TO javauser@localhost -> IDENTIFIED BY 'javadude' WITH GRANT OPTION; mysql> create database javatest; mysql> use javatest; mysql> create table testdata ( -> id int not null auto_increment primary key, -> foo varchar(25), -> bar int);
Note: the above user should be removed once testing is complete!
Next insert some test data into the testdata table.
mysql> insert into testdata values(null, 'hello', 12345); Query OK, 1 row affected (0.00 sec) mysql> select * from testdata; +----+-------+-------+ | ID | FOO | BAR | +----+-------+-------+ | 1 | hello | 12345 | +----+-------+-------+ 1 row in set (0.00 sec) mysql>