Dvds Den

info@dvdsden.com

 
 

 Standalone Tomcat 7 Jdbc-Pool
 
 
January 30, 2011

Recently I ran across a reference to the SQL pooling in Tomcat 7. It is called Tomcat Jdbc-Pool. It is intended to be set up by putting a Resource in the server.xml context. The system I was working on was a legacy that used a standalone connection pool. I decided to try to use the newer jdbc-pool as a standalone.

The jar file is tomcat-jdbc.jar which can be found in the Tomcat 7 lib. In the beginning an attempt was made to reference this jar from a Java class. Very strange--it would work from a class but not if called from a jsp. From a jsp the driver could not be found. I found by googling a statement that the class loader had to be the same for the jar and the calling program. It must be that the class loader is different for jsp's. So I cut the jar from the Tomcat lib directory and pasted it in the application's WEB-INF/lib directory. That worked--the jdbc-pool could now find the driver, the latest jtds. The only dependency encountered was for tomcat-juli.jar, for which juli-6.0.33.jar was substituted.

A class was written to provide a pooling datasource:

package dbconnection;

import config.LoadDbProperties;
import java.util.Properties;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;

/**
 *
 * @author Gary L. Harris
 */

public class JdbcPoolDataSource {
    
    public org.apache.tomcat.jdbc.pool.DataSource getDataSource() throws Exception {
        // connection properties
        LoadDbProperties jdckprops = LoadDbProperties.getInstance();
        Properties props = jdckprops.getProperties();
        String user=props.getProperty("user");
        String password=props.getProperty("password");
        String connecturi=props.getProperty("connecturi");
        String driver=props.getProperty("driver");     
        
        // set jdbc-pool properties
        PoolProperties p = new PoolProperties();
        p.setUrl(connecturi);
        p.setDriverClassName(driver);
        p.setUsername(user);
        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.setFairQueue(true);
        p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"
                + "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
        DataSource datasource = new DataSource();
        datasource.setPoolProperties(p);
        
        return datasource;
    }
}

LoadDbProperties is a utility class used to get the username, password, and connection strings. The string values were put in a properties file with the name db.properties. This file was located in the classpath, so that it could be easily found by GetResourceAsStream:
package config;

/**
 *
 * @author Gary L. Harris
 */
import utils.GetResourceAsStream2;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

public class LoadDbProperties {
    static org.slf4j.Logger log = org.slf4j.LoggerFactory.getLogger("");

    public Properties getProperties() {
        Properties props = null;
        String user = "";
        String password = "";
        String connecturi = "";
        String driver = "";
        int maxsize = 0;
        int maxconnections = 0;
        int logintimeout = 0;
        Object bind = null;
        try {
            String propfilename = "/db.properties";
            GetResourceAsStream2 gras = new GetResourceAsStream2();  // utility
            InputStream is = gras.getResourceAsStream(propfilename);
            props = new Properties();

            props.load(is);
            boolean isempty = props.isEmpty();
            if (isempty) {
                log.error("LoadDbProperties: could not load property file " + propfilename);
            }
            bind = null;
            user = props.getProperty("user");
            password = props.getProperty("password");
            connecturi = props.getProperty("connecturi");
            driver = props.getProperty("driver");

        } catch (IOException ex) {
            log.error("JdcDataConnection: IOException: " + ex.getMessage());
        }
        return props;
    }

    public static LoadDbProperties getInstance() {
        return new LoadDbProperties();
    }
}
 
package tests;

import dbconnection.JdbcPoolDataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.DataSource;

/**
 *
 * @author Gary L. Harris
 */

public class TestJdbcPool {
    static org.slf4j.Logger log = org.slf4j.LoggerFactory.getLogger("");
    
    public static void main(String[] args) {
        Connection conn = null;
        try {
            JdbcPoolDataSource jpds = new JdbcPoolDataSource();
            DataSource ds = jpds.getDataSource();
            conn = ds.getConnection();
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("select * from DVDEPIDS");
            int cnt = 1;
            while (rs.next()) {
                System.out.println((cnt++) + ". EPID:" + rs.getString("EPID"));
            }
        } catch (Exception ex) {
            log.error("TestJdbcPool: Exception: "+ex.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception ignore) {
                }
            }
        }
    }
}
     --Gary