Topic: Tomcat Connection Pooling in Tooltwist
Applications are penalized when they try to create new connections to a database. But if an application keeps a connection as long as it can use it, the database may soon run out of connections. A workaround is to keep a pool of open connections. When the application needs to connect to the database, it checks the pool for a connection. When the application is finished with the connection it returns the connection to the pool. The connection pooling library has many responsibilities. It should determine the number of active connections to keep, it should know when to create and destroy connections, etc. Writing one for an application will take considerable effort. Luckily, tomcat provides a built-in connection pooling library. Here are the steps to use it.
1. Create a file named context.xml under WebContent/META-INF. This file should contain the connection settings to the database. An example is given below:
<?xml version="1.0" encoding="UTF-8"?>
<Context path="/ttsvr" docBase="ttsvr" crossContext="true" reloadable="true" debug="1">
<Resource name="jdbc/postgres"
auth="Container"
type="javax.sql.DataSource"
driverClassName="org.postgresql.Driver"
url="jdbc:postgresql://10.1.1.153:5432/qvas_coc"
username="postgres"
password="vsc"
maxActive="10"
maxIdle="5"
maxWait="-1"
removeAbandoned="true"
removeAbandonedTimeout="60"
logAbandoned="true"
/>
</Context>2. Get a datasource from the connection pool. The example below shows how to get a datasource for a jndi resource named: jdbc/postgres.
InitialContext initialContext = new InitialContext();
if (initialContext == null) {
String message = "InitialContext was not setup properly.";
logger.error(message);
throw new SQLException(message);
}
// actual jndi name is "jdbc/postgres"
DataSource datasource = (DataSource) initialContext.lookup( "java:/comp/env/jdbc/postgres" );
if ( datasource == null ) {
String message = "WebApp resource was not setup properly.";
logger.error(message);
throw new SQLException(message);
}3. Get a connection from the datasource
Connection con = datasource.getConnection();For the QVAS project a utility class named JdbcUtil was created. This class is used in XPC's to get a connection from the pool.
public class JdbcUtil {
private static DataSource datasource = null;
private static Logger logger = Logger.getLogger(JdbcUtil.class);
private synchronized static DataSource getDataSource() throws NamingException, SQLException {
if (datasource == null) {
InitialContext initialContext = new InitialContext();
if (initialContext == null) {
String message = "InitialContext was not setup properly.";
logger.error(message);
throw new SQLException(message);
}
// actual jndi name is "jdbc/postgres"
datasource = (DataSource) initialContext.lookup( "java:/comp/env/jdbc/postgres" );
if ( datasource == null ) {
String message = "WebApp resource was not setup properly.";
logger.error(message);
throw new SQLException(message);
}
}
return datasource;
}
public synchronized static Connection getConnection() throws SQLException, NamingException {
return getDataSource().getConnection();
}
public static synchronized void freeConnection(Connection connection) throws SQLException {
connection.close();
}
}Usage:
Connection con = JdbcUtil.getConnection();
Statement stm = con.createStatement();
ResultSet rst = stm.executeQuery(sql);