Code Example

Other examples of Tomcat configuration for JDBC usage can be found in the Tomcat documentation.

Plain Ol’ Java

Here is a simple example of how to create and use a data source.

 

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Statement;

 

import org.apache.tomcat.jdbc.pool.DataSource;

import org.apache.tomcat.jdbc.pool.PoolProperties;

 

public class SimplePOJOExample {

 

public static void main(String[] args) throws Exception {

PoolProperties p = new PoolProperties();

p.setUrl(“jdbc:mysql://localhost:3306/mysql”);

p.setDriverClassName(“com.mysql.jdbc.Driver”);

p.setUsername(“root”);

p.setPassword(“password”);

p.setJmxEnabled(true);

p.setTestWhileIdle(false);

p.setTestOnBorrow(true);

p.setValidationQuery(“SELECT 1”);

p.setTestOnReturn(false);

p.setValidationInterval(30000);

p.setTimeBetweenEvictionRunsMillis(30000);

p.setMaxActive(100);

p.setInitialSize(10);

p.setMaxWait(10000);

p.setRemoveAbandonedTimeout(60);

p.setMinEvictableIdleTimeMillis(30000);

p.setMinIdle(10);

p.setLogAbandoned(true);

p.setRemoveAbandoned(true);

p.setJdbcInterceptors(

“org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;”+

“org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer”);

DataSource datasource = new DataSource();

datasource.setPoolProperties(p);

 

Connection con = null;

try {

con = datasource.getConnection();

Statement st = con.createStatement();

ResultSet rs = st.executeQuery(“select * from user”);

int cnt = 1;

while (rs.next()) {

System.out.println((cnt++)+”. Host:” +rs.getString(“Host”)+

” User:”+rs.getString(“User”)+” Password:”+rs.getString(“Password”));

}

rs.close();

st.close();

} finally {

if (con!=null) try {con.close();}catch (Exception ignore) {}

}

}

 

}

As a Resource

And here is an example on how to configure a resource for JNDI lookups

 

<Resource name=”jdbc/TestDB”

auth=”Container”

type=”javax.sql.DataSource”

factory=”org.apache.tomcat.jdbc.pool.DataSourceFactory”

testWhileIdle=”true”

testOnBorrow=”true”

testOnReturn=”false”

validationQuery=”SELECT 1″

validationInterval=”30000″

timeBetweenEvictionRunsMillis=”30000″

maxActive=”100″

minIdle=”10″

maxWait=”10000″

initialSize=”10″

removeAbandonedTimeout=”60″

removeAbandoned=”true”

logAbandoned=”true”

minEvictableIdleTimeMillis=”30000″

jmxEnabled=”true”

jdbcInterceptors=”org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;

org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer”

username=”root”

password=”password”

driverClassName=”com.mysql.jdbc.Driver”

url=”jdbc:mysql://localhost:3306/mysql”/>

Asynchronous Connection Retrieval

The Tomcat JDBC connection pool supports asynchronous connection retrieval without adding additional threads to the pool library. It does this by adding a method to the data source called Future<Connection> getConnectionAsync(). In order to use the async retrieval, two conditions must be met:

• You must configure the fairQueue property to be true.
• You will have to cast the data source to org.apache.tomcat.jdbc.pool.DataSource

An example of using the async feature is show below.

Connection con = null;

try {

Future<Connection> future = datasource.getConnectionAsync();

while (!future.isDone()) {

System.out.println(“Connection is not yet available. Do some background work”);

try {

Thread.sleep(100); //simulate work

}catch (InterruptedException x) {

Thread.currentThread().interrupt();

}

}

con = future.get(); //should return instantly

Statement st = con.createStatement();

ResultSet rs = st.executeQuery(“select * from user”);

Interceptors

Interceptors are a powerful way to enable, disable or modify functionality on a specific connection or its sub components. There are many different use cases for when interceptors are useful. By default, and for performance reasons, the connection pool is stateless. The only state the pool itself inserts are defaultAutoCommit, defaultReadOnly, defaultTransactionIsolation, defaultCatalog if these are set. These 4 properties are only set upon connection creation. Should these properties be modified during the usage of the connection, the pool itself will not reset them.

An interceptor has to extend the org.apache.tomcat.jdbc.pool.JdbcInterceptor class. This class is fairly simple, You will need to have a no arg constructor

 

public JdbcInterceptor() {

}

 

When a connection is borrowed from the pool, the interceptor can initialize or in some other way react to the event by implementing the

 

public abstract void reset(ConnectionPool parent, PooledConnection con);

 

method. This method gets called with two parameters, a reference to the connection pool itself ConnectionPool parent and a reference to the underlying connection PooledConnection con.

When a method on the java.sql.Connection object is invoked, it will cause the

public Object invoke(Object proxy, Method method, Object[] args) throws Throwable

method to get invoked. The Method method is the actual method invoked, and Object[] args are the arguments. To look at a very simple example, where we demonstrate how to make the invocation to java.sql.Connection.close() a noop if the connection has been closed

if (CLOSE_VAL==method.getName()) {

if (isClosed()) return null; //noop for already closed.

}

return super.invoke(proxy,method,args);

There is an observation being made. It is the comparison of the method name. One way to do this would be to do “close”.equals(method.getName()). Above we see a direct reference comparison between the method name and static final String reference. According to the JVM spec, method names and static final String end up in a shared constant pool, so the reference comparison should work. One could of course do this as well:

if (compare(CLOSE_VAL,method)) {

if (isClosed()) return null; //noop for already closed.

}

return super.invoke(proxy,method,args);

 

The compare(String,Method) will use the useEquals flag on an interceptor and do either reference comparison or a string value comparison when the useEquals=true flag is set.

Pool start/stop

When the connection pool is started or closed, you can be notified. You will only be notified once per interceptor class even though it is an instance method. and you will be notified using an interceptor currently not attached to a pool.

public void poolStarted(ConnectionPool pool) {

}

 

public void poolClosed(ConnectionPool pool) {

}

 

When overriding these methods, don’t forget to call super if you are extending a class other thanJdbcInterceptor

Configuring interceptors

Interceptors are configured using the jdbcInterceptors property or the setJdbcInterceptors method. An interceptor can have properties, and would be configured like this

 

String jdbcInterceptors=

“org.apache.tomcat.jdbc.pool.interceptor.ConnectionState(useEquals=true,fast=yes)”

Interceptor properties

Since interceptors can have properties, you need to be able to read the values of these properties within your interceptor. Taking an example like the one above, you can override the setProperties method.

 

public void setProperties(Map<String, InterceptorProperty> properties) {

super.setProperties(properties);

final String myprop = “myprop”;

InterceptorProperty p1 = properties.get(myprop);

if (p1!=null) {

setMyprop(Long.parseLong(p1.getValue()));

}

}

Getting the actual JDBC connection

Connection pools create wrappers around the actual connection in order to properly pool them. We also create interceptors in these wrappers to be able to perform certain functions. If there is a need to retrieve the actual connection, one can do so using the javax.sql.PooledConnection interface.

Connection con = datasource.getConnection();

Connection actual = ((javax.sql.PooledConnection)con).getConnection();