Database connection pools

Tomcat connection pool

The main application connection pool is managed by Tomcat, and is configured in the context.xml file inside the WAR META-INF directory. The relevant info is in the Resource tag:

<Resource name="jdbc/EMFDB" auth="Container"
      type="javax.sql.DataSource"
      driverClassName="org.postgresql.Driver"
      url="jdbc:postgresql://localhost:5432/EMF?autoReconnect=true"
      username="********"
      password="********"
      maxActive="92"
      maxIdle="30"
      maxWait="30000"
      validationQuery="select 1"
      />

This creates a pool with a maximum of 92 connections, with a max of 30 idle.

To use this pool in the application, a mapping is added to the web.xml file in the WAR's WEB-INF directory:

<resource-ref>
  <description>DB Connection</description>
  <res-ref-name>jdbc/EMFDB</res-ref-name>
  <res-type>javax.sql.DataSource</res-type>
  <res-auth>Container</res-auth>
</resource-ref>

This sets up the JNDI resource and can be used in Java as a DataSource:

Context ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:/comp/env/jdbc/EMFDB");

The EMF uses direct connections like this for various tasks in the services classes.

Hibernate connection

The EMF also uses Hibernate for object-relational mapping and persistence. The Hibernate database connection is configured in hibernate.cfg.xml. Currently, the EMF application uses the following Hibernate database configuration:

<property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
<property name="hibernate.connection.url">jdbc:postgresql://localhost:5432/EMF?autoReconnect=true</property>
<property name="hibernate.connection.password">********</property>
<property name="hibernate.connection.username">********</property>

<!-- c3p0 -->
<property name="hibernate.c3p0.acquire_increment">2</property> 
<property name="hibernate.c3p0.idle_test_period">300</property> <!-- seconds --> 
<property name="hibernate.c3p0.max_size">80</property> 
<property name="hibernate.c3p0.max_statements">0</property> 
<property name="hibernate.c3p0.min_size">5</property> 
<property name="hibernate.c3p0.timeout">3000</property> <!-- seconds -->

This configuration causes Hibernate to set up its own connections to the database and its own connection pool, with a maximum of 80 connections and 5 idle.

Instead, Hibernate can be configured to use the connection pool managed by Tomcat. The above properties are removed, and replaced with:

<property name="hibernate.connection.datasource">java:/comp/env/jdbc/EMFDB</property>

Spring framework

The file download tasks within the EMF make use of the Spring framework and use the gov.epa.emissions.framework.services.spring.AppConfig class to set up database connections. This class replicates the Tomcat configuration, which causes another database connection pool to be created:

@Bean
public DataSource dataSource() {
   BasicDataSource dataSource = new BasicDataSource();
   dataSource.setDriverClassName("org.postgresql.Driver");
   dataSource.setUrl("jdbc:postgresql://localhost:5432/EMF?autoReconnect=true");
   dataSource.setUsername("********");
   dataSource.setPassword("********");
   dataSource.setMaxActive(92);
   dataSource.setMaxIdle(30);
   dataSource.setMaxWait(30000);
   dataSource.setValidationQuery("select 1");

Looking at the code, maybe the Spring configuration can use the existing JNDI resource (similar to the revised Hibernate connection):

//    @Bean
//    public DataSource dataSource() {
//        try {
//            Context ctx = new InitialContext();
//            return (DataSource) ctx.lookup("java:/comp/env/jdbc/EMFDB");
//        } catch (NamingException e) {
//            // NOTE Auto-generated catch block
//            e.printStackTrace();
//        }
//        return null;
//    }

EMF services

There are some service classes in the EMF that hold on to a database connection for the lifetime of the application, instead of releasing it back to the pool.