In this blog we will demonstrate how to use standalone JDBC connection pool without using JDNI resource lookups. We used MySQL drivers in Tomcat 7. The sample class shown below illustrates how the connection pool is created outside of the container. It is straightforward except for the connection attributes settings. 



<pre class="brush: java;highlight:[46,51,56]">
package PACKAGE-NAME;

import java.sql.Connection;
import org.apache.tomcat.jdbc.pool.DataSource;
import org.apache.tomcat.jdbc.pool.PoolProperties;

public class DatasourceConnection {

 private static DataSource datasource = null;

 DatasourceConnection() {
 }

 public static Connection getConnection() {
 try {
 if (datasource == null) {
 DatasourceConnection.initDatasource();
 }
 return datasource.getConnection();
 } catch (Exception e) {
 return null;
 }
 }

 public static void initDatasource() {

 PoolProperties p = new PoolProperties();

 p.setUrl("jdbc:mysql://localhost:3306/sample_database");
 p.setDriverClassName("com.mysql.jdbc.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(75);
 p.setMaxIdle(75);
 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 = new DataSource();
 datasource.setPoolProperties(p);

 }

 public static void closeDatasource() {
 datasource.close();
 }
}
</pre>



In line 56 we instantiated org.apache.tomcat.jdbc.pool.DataSource, it is a static object created and destroyed through a context listener class shown below.



<pre class="brush: java">
package PACKAGE-NAME;

import java.sql.DriverManager;
import java.sql.Driver;
import java.util.Enumeration;
import java.sql.SQLException;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;

public class DatasourceListener implements ServletContextListener {
 
 @Override
 public void contextInitialized(ServletContextEvent cs) {
 DatasourceConnection.initDatasource();
 // log
 }

 @Override
 public void contextDestroyed(ServletContextEvent ce) {

 DatasourceConnection.closeDatasource();
 
 Enumeration&lt;Driver> drivers = DriverManager.getDrivers();
 
 while (drivers.hasMoreElements()) {
 Driver driver = drivers.nextElement();
 try {
 DriverManager.deregisterDriver(driver);
 ce.getServletContext().log("(JDBC driver (" + driver.toString() +
 ") successfully deregistered");
 } catch (SQLException ex) {
 ce.getServletContext().log( ex.getMessage());
 }

 }
 }


}
</pre>



Update your web.xml to include the following.




<pre class="brush: xml">
&lt;listener>
 &lt;listener-class>PACKAGE-NAME.DatasourceListener&lt;/listener-class>
&lt;/listener>
</pre>



The snippet below shows how to use the preceeding DatasourceConnection class in your code. Notice that this usage snippet closes the connection "properly". Connections however are returned to the pool if connections are abandoned or unclosed for 60 seconds. See line 46 and 51 from our connection settings. 



<pre class="brush: java;highlight:2">

try (
 java.sql.Connection conn = DatasourceConnection.getConnection();
 java.sql.Statement stmt = conn.createStatement();
 java.sql.ResultSet set = stmt.executeQuery("select some from thing")) {

 // do the work;
 
} catch (Exception e) {
 // 
}

</pre>



The documentation for Tomcat JDBC connection pool can be found in <a href="http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html" target="_blank" 

class="news_link" title="Documentation">this link</a>. The documentation pages list the available connection attributes and how to use them both as JDNI resource or as standalone.




That's it. Good luck.

Using Tomcat JDBC Connection Pool as standalone instantiated bean