Apache Tomcat 7-9: JNDI JDBC Connection Pool Configuration

Product: Apache Tomcat
Version: 7.0 - 9.x

Since Apache Tomcat 7, the build-in JNDI/JDBC connection pool manager has 2 options:

1. Apache Common DBCP - class name is org.apache.tomcat.dbcp.dbcp2.BasicDataSource
2. Tomcat's light weight DBCP - recommended setting, and I will refer to it as non-DBCP. Class name is org.apache.tomcat.jdbc.pool.DataSource

Tomcat's jdbc-pool.html page documented various benefits to use Tomcat's light-weight DBCP connection pool manager in their homepage: https://tomcat.apache.org/tomcat-9.0-doc/jdbc-pool.html. In summary, you need to configure extra <Resource> parameter org.apache.tomcat.jdbc.pool.DataSourceFactory to switch to the light-weight driver, else it will be using DBCP.

In Tomcat's jndi-datasource-examples-howto.html homepage, it failed to document this additional factor=org.apache.tomcat.jdbc.pool.DataSourceFactory parameter, and many people are using Common DBCP connection pool manager.

This post going to give clear example how to configure both using MS SQL Server

Using Apache Common DBCP

File: [Tomcat]/conf/context.xml (for global), [Tomcat]/webapps/[webapp]/META-INF/context.xml (for specific webapp)

Example: [Tomcat]/conf/context.xml

<?xml version="1.0" encoding="UTF-8"?>
<!-- The contents of this file will be loaded for each web application -->

<Context>

    <!-- Default set of monitored resources. If one of these changes, the    -->
    <!-- web application will be reloaded.                                   -->
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
    <WatchedResource>WEB-INF/tomcat-web.xml</WatchedResource>
    <WatchedResource>${catalina.base}/conf/web.xml</WatchedResource>

    <!-- Uncomment this to disable session persistence across Tomcat restarts -->
    <Manager pathname="" />
<Resources cacheMaxSize="512000" />
    <Resource name="jdbc/pc1001DataSource" auth="Container"
      type="javax.sql.DataSource"
  username = "[DB username]"
          password = "[DB user password"
          driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" 
          url="jdbc:sqlserver://[MS SQL Server hostname]:1433;DatabaseName=pc1001"
          maxTotal="100"
  validationQuery="select 1004"
  connectionProperties="sendStringParametersAsUnicode=false"
    />

</Context>

Note: If using tomcat/conf/context.xml, then msjdbc.jar must resides in tomcat/lib directory, else Tomcat will complain ClassNotFound

Using Tomcat Non-DBCP Connection Pool

File: [Tomcat]/conf/context.xml (for global), [Tomcat]/webapps/[webapp]/META-INF/context.xml (for specific webapp)

Example: [Tomcat]/conf/context.xml

<?xml version="1.0" encoding="UTF-8"?>
<!-- The contents of this file will be loaded for each web application -->

<Context>

    <!-- Default set of monitored resources. If one of these changes, the    -->
    <!-- web application will be reloaded.                                   -->
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
    <WatchedResource>WEB-INF/tomcat-web.xml</WatchedResource>
    <WatchedResource>${catalina.base}/conf/web.xml</WatchedResource>

    <!-- Uncomment this to disable session persistence across Tomcat restarts -->
    <Manager pathname="" />
<Resources cacheMaxSize="512000" />
    <Resource name="jdbc/pc1001DataSource" auth="Container"
      type="javax.sql.DataSource"
  factory = "org.apache.tomcat.jdbc.pool.DataSourceFactory"
  username = "[DB username]"
          password = "[DB user password"
          driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" 
          url="jdbc:sqlserver://[MS SQL Server hostname]:1433;DatabaseName=pc1001"
          maxTotal="100"
  validationQuery="select 1004"
  connectionProperties="sendStringParametersAsUnicode=false"
    />

</Context>

Note: If using tomcat/conf/context.xml, then msjdbc.jar must resides in tomcat/lib directory, else Tomcat will complain ClassNotFound

Monitoring

I compared the monitoring for both DBCP and non-DBCP connection pool manager using Tomcat 9.0 using Oracle Java 1.8.0_111 on Windows OS.  JConsole.exe (Java Monitoring & Management Console) is capable of monitoring both connection pool manager through its "MBeans" tab.

When comparing how each to monitor both of them, I found that DBCP is updating "MBeans" faster and more accurate than non-DBCP.  Non-DBCP sometimes won't show the detail of the connected connections, i.e. PooledConnect[array], for hours, in other word, it never update it.

The slow, delay, and inconsistency for non-DBCP connection pool make JConsole can't be reliabiliy used to monitor it via this JMX utility.  I didn't write a program to directly grab the JDBC connection pool JMX metrics, which I suspect there could be a delay as well