Tomcat JDBC Connection Pooling

Connection pooling is a cool mechanism because it allows to us reuse db connections. In a DB server a database connection is an expensive thing because it consumes system resources. Also connection creation and closing are time consuming operations. Connection Pooling helps us to reduce number of concurrent connections and to escape from connection open/close by keeping connections alive.

Apache Tomcat is a great and ligthweight Java Application server for both development and production. Tomcat has an JDBC Connection Pooling mechanism. This feaute is configured in /META-INF/context.xml. Here is a sample:

<?xml version="1.0" encoding="UTF-8"?>
<Context docBase="/MyApp" path="/MyApp" reloadable="true">
<Resource name="jdbc/mydbresource" auth="Container" type="javax.sql.DataSource" factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
username="myusername" password="mypass" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/mydb?autoReconnect=true"
maxWait="1000"
removeAbandoned="true"
maxActive="30"
maxIdle="10"
removeAbandonedTimeout="10"
logAbandoned="true"/>
</Context>
docBase and path are your application.

Resource name is the datasource name to be called in connection setup at your application. (arbitrary but give a meaningful name)

autoReconnect: as the name implies , it causes to reopen the closed connections immediately. Mysql closes idle connections in 8 minutes. with this option connections reopened immediately when closed.

maxWait: timeout for your java code to gett a connection from pool. if timeout reaches , an exception will be thrown.

removeAbandoned: if set true , a connection is removed from the pool after removeAbandonedTimout idle seconds. Not for heavy load production environments. It is only useful for freshman developers who forget to close jdbc connections.

logAbandoned: logs abondoned connections or statements. It consumes system resources , so should be false in mature production environments.

At the java side , environment should be initialized first. This code can be run in static part :

javax.sql.DataSource.DataSource ds = null;
javax.naming.Context initContext;
try {
initContext = new javax.naming.InitialContex();
javax.naming.Context envContext = (javax.naming.Context) initContext.lookup("java:/comp/env");
ds = (javax.sql.DataSource.DataSource) envContext.lookup("jdbc/mydbresource");
} catch (Exception e) {
e.printStackTrace();

}

And when you need a connection , reuse ds and get a connection from ds.

Connection con = null;

try {
con = ds.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}