I'm trying to debug what seems to be an excessive number of database connections being opened and closed despite the fact that we are using the Tomcat 7 JDBC connection pool. How can I log when calling getConnection() on the data source results in a new connection being opened versus an existing connection being borrowed from the pool?
Java – How to log Tomcat 7 JDBC connection pool, connection creation
connection-poolingjavajdbctomcattomcat7
Best Answer
I know of two ways to look at the Tomcat DB Connection Pool info.
1. Using JMX Monitoring
The Tomcat Connection Pool by default will register itself as an MBean (JMX Bean). This feature can be turned on/off with the jmxEnabled attribute on the tomcat-jdbc-pool. See The Tomcat JDBC Connection Pool.
You can use various external JMX tools to monitor the DB Connection Pool and other JMX resources. I would suggest starting with JConsole, which comes with Java. Launch JConsole, connect to your Tomcat (Catalina) JVM, select MBeans header, open Catalina/DataSource/... see pic below.
Read more on Monitoring Tomcat.
2. Write a JdbcInterceptor class that logs DB Connection Pool info
The Tomcat connection pool allows you to register interceptors for JDBC Connections. Below, I will show how to write a JdbcInterceptor class that logs connection usage. The example is for Tomcat 8, but it might work for Tomcat 7 as well.
Add tomcat-jdbc.jar as a provided dependency to your project.
Create a JdbcInterceptor class
This class uses commons logging, you may want to use something else.
Register this interceptor class in Context.xml
JdbcInterceptor classes can be registered as either a Resource, as shown above, or as a POJO.
Log Samples
Below are some sample logs from Tomcat when accessing the Connection Pool