Tag: MySQL

  • Add Connection Pool with auto connection cleanup functionality for Tomcat 7 and MySQL

    Add Connection Pool with auto connection cleanup functionality for Tomcat 7 and MySQL

    Since we don’t want to use external classes or libraries to handle database connections for our web applications we should stick to what comes built-in with the container server – in my case Apache Tomcat 7 and MySQL database server.

    To keep the database connection pooling and cleanup functionality on the server-side you can use the following approach:

    1.) In your web-app’s META-INF/context.xml add

    <?xml version="1.0" encoding="UTF-8"?>
    <Context antiJARLocking="true" path="/some-project">
    
        <Resource name="jdbc/your-db-resource" 
                  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" 
                  maxIdle="30" 
                  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="" 
                  driverClassName="com.mysql.jdbc.Driver"
                  url="jdbc:mysql://localhost:3306/your_db"/>
    </Context>
    

    2.) In your WEB-INF/web.xml add reference to JDNI resource from above:

    <resource-ref>
            <res-ref-name>jdbc/your-db-resource</res-ref-name>
            <res-type>javax.sql.DataSource</res-type>
            <res-auth>Container</res-auth>
        </resource-ref>
    

    Also make sure to put the corresponding mysql connector library jar into tomcat/lib folder so that you don’t you an exception like so:

    WARNING: Unexpected exception resolving reference
    java.sql.SQLException: com.mysql.jdbc.Driver
    at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:254)
    at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:182)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:699)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:631)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:485)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.(ConnectionPool.java:143)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:116)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:103)
    at org.apache.tomcat.jdbc.pool.DataSourceFactory.createDataSource(DataSourceFactory.java:539)
    at org.apache.tomcat.jdbc.pool.DataSourceFactory.getObjectInstance(DataSourceFactory.java:237)
    at org.apache.naming.factory.ResourceFactory.getObjectInstance(ResourceFactory.java:143)
    at javax.naming.spi.NamingManager.getObjectInstance(NamingManager.java:304)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:843)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:154)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:831)
    at org.apache.naming.NamingContext.lookup(NamingContext.java:168)
    at org.apache.catalina.core.NamingContextListener.addResource(NamingContextListener.java:1061)
    at org.apache.catalina.core.NamingContextListener.createNamingContext(NamingContextListener.java:671)
    at org.apache.catalina.core.NamingContextListener.lifecycleEvent(NamingContextListener.java:270)
    at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:119)
    at org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(LifecycleBase.java:90)
    at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5173)
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
    at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1559)
    at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1549)
    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
    at java.util.concurrent.FutureTask.run(FutureTask.java:138)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
    at java.lang.Thread.run(Thread.java:680)
    Caused by: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
    at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:247)
    at java.lang.Class.forName0(Native Method)
    at java.lang.Class.forName(Class.java:247)
    at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:246)
    … 29 more
    Oct 31, 2012 11:23:25 AM org.apache.catalina.core.NamingContextListener addResource
    WARNING: Failed to register in JMX: javax.naming.NamingException: com.mysql.jdbc.Driver
    Oct 31, 2012 11:23:25 AM org.apache.coyote.AbstractProtocol start
    INFO: Starting ProtocolHandler [“http-bio-8086”]
    Oct 31, 2012 11:23:25 AM org.apache.coyote.AbstractProtocol start
    INFO: Starting ProtocolHandler [“ajp-bio-8009”]
    Oct 31, 2012 11:23:25 AM org.apache.catalina.startup.Catalina start
    INFO: Server startup in 794 ms

    That’s it!

    A sample connection wrapper class would be:

    package at.kerstner;
    
    import java.sql.Connection;
    import javax.naming.Context;
    import javax.naming.InitialContext;
    import javax.sql.DataSource;
    
    abstract class DBConnection {
    
        public static Connection getConnection() {
            Connection conn = null;
            try {
                Context initContext = new InitialContext();
                Context envContext = (Context) initContext.lookup("java:/comp/env");
                DataSource ds = (DataSource) envContext.lookup("jdbc/your-db-resource");
                conn = ds.getConnection();
            } catch (Exception e) {
                System.err.println("Failed to get connection: " + e.getMessage());
            }
            return conn;
        }
    }
    

    Also make sure to have a look at my other post concerning problems when loading the resource factory class.