title: WildFly/JPA database connection leak in multitenant software
body: <p>I want to use a multitenant architecture for our Java EE software.
  We use WildFly 8.2 (JPA with Hibernate 4.3.7) with PostgreSQL 9.3</p>
  
  <p>I know Hibernate provide some API for multitenant : <a href="http://docs.jboss.org/hibernate/orm/4.2/devguide/en-US/html/ch16.html" rel="nofollow">http://docs.jboss.org/hibernate/orm/4.2/devguide/en-US/html/ch16.html</a></p>
  
  <p>and that we can integrate it with JPA in the persistence.xml file like this :</p>
  
  <pre><code>&lt;property name="hibernate.multiTenancy" value="SCHEMA"/&gt;
  &lt;property name="hibernate.tenant_identifier_resolver" value="mycompany.hibernate.multitenant.SchemaResolver"/&gt;
  &lt;property name="hibernate.multi_tenant_connection_provider" value="mycompany.hibernate.multitenant.MultiTenantProvider"/&gt;
  </code></pre>
  
  <p>But i have some problems with my "hibernate.multi_tenant_connection_provider".</p>
  
  <p>I want to use the datasource pool from my WildFly configuration (standalone.xml), but if i use it, it result in connection leak in pool.
  The connections are never released to the pool.</p>
  
  <p>Do i need to use another library to manage my database connection pool like C3P0 ? I though that WildFly can manage it correctly.
  Is there a better way to integrate multitenant with JPA ?</p>
  
  <p>Or maybe a problem with my JNDI lookup ?</p>
  
  <p>And if i use SCHEMA for the multitenant configuration, the connection is always to the same database/datasource so why we need to write some connection provider ?</p>
  
  <p>Do you have any ideas ?
  Thank in advance for your help.</p>
  
  <pre><code>public class MultiTenantProvider implements MultiTenantConnectionProvider {
  
  @Override
  public boolean supportsAggressiveRelease() {
      return false;
  }
  
  @Override
  public boolean isUnwrappableAs(Class clazz) {
      return false;
  }
  
  @Override
  public &lt;T&gt; T unwrap(Class&lt;T&gt; clazz) {
      return null;
  }
  
  @Override
  public Connection getAnyConnection() throws SQLException {
      Context initContext;
      Connection connection = null;
      try {
          initContext = new InitialContext();
          DataSource ds = (DataSource) initContext.lookup("java:/MyPostgresDS");
          connection = ds.getConnection();
      } catch (NamingException e) {
          e.printStackTrace();
      }
      return connection;
  }
  
  @Override
  public Connection getConnection(String tenantIdentifier) throws SQLException {
      final Connection connection = getAnyConnection();
      try {
          connection.createStatement().execute("SET SCHEMA '" + tenantIdentifier + "'");
      }
      catch (SQLException e) {
          throw new HibernateException("Could not alter JDBC connection to specified schema [" + tenantIdentifier + "]", e);
      }
      return connection;
  }
  
  @Override
  public void releaseAnyConnection(Connection connection) throws SQLException {
      try {
          connection.createStatement().execute("SET SCHEMA 'public'");
      }
      catch (SQLException e) {
          throw new HibernateException("Could not alter JDBC connection to specified schema [public]", e);
      }
      connection.close();
  }
  
  @Override
  public void releaseConnection(String tenantIdentifier, Connection connection) throws SQLException {
      releaseAnyConnection(connection);
  }
  </code></pre>
  
  <p>}</p>
  
  <p>Datasource in the standalone.xml file of WildFly</p>
  
  <pre><code>&lt;datasource jndi-name="java:/MyPostgresDS" pool-name="MyPostgresDS" enabled="true" use-java-context="true"&gt;
                  &lt;connection-url&gt;jdbc:postgresql:demo&lt;/connection-url&gt;
                  &lt;driver&gt;posgresqlDriver&lt;/driver&gt;
                  &lt;pool&gt;
                      &lt;min-pool-size&gt;10&lt;/min-pool-size&gt;
                      &lt;max-pool-size&gt;50&lt;/max-pool-size&gt;
                      &lt;prefill&gt;true&lt;/prefill&gt;
                  &lt;/pool&gt;
                  &lt;security&gt;
                      &lt;user-name&gt;postgres&lt;/user-name&gt;
                      &lt;password&gt;postgres&lt;/password&gt;
                  &lt;/security&gt;
                  &lt;timeout&gt;
                      &lt;blocking-timeout-millis&gt;20000&lt;/blocking-timeout-millis&gt;
                      &lt;idle-timeout-minutes&gt;5&lt;/idle-timeout-minutes&gt;
                  &lt;/timeout&gt;
              &lt;/datasource&gt;
  </code></pre>