title: How do I close database connections in a JBOSS application with a hibernate datasource?
body: <p>I maintain a JBOSS-9/Spring-4/Hibernate-4 application which apparently fails to close/release any database connections. Whatever size I set my database connection pool, once that many users connect to our server and make a request, the pool runs out of connections and any further requests fail due to the server being unable to make any new database connection.</p>
  <p>I have followed the suggestions from this post:
  <a href="https://stackoverflow.com/questions/28365722/cached-connection-manager-in-jboss-wildfly">Cached connection manager in jboss/wildfly</a></p>
  <p>When I enable the cached connection manager (use-ccm=&quot;true&quot;) turn on debug (debug=&quot;true&quot;), and increase the log level to DEBUG, I see messages that indicate that with that configuration, my connections are indeed released. When I turn off the cached connection manager, I start running out of connections again. Despite the recommendation from the link above, is there any reason why I shouldn't just enable the cached connection manager full time?</p>
  <p>Since the application's Java code never explicitly opens a connection (it just implicitly uses a dataSource bean via a Hibernate session), I didn't believe that the code needed to do anything explicit to close a connection.</p>
  <p>Thanks for any input to help me resolve this issue.</p>
  <p>For the record, here is my datasource configuration in standalone.xml:</p>
  <pre><code>                &lt;datasource jta=&quot;false&quot; jndi-name=&quot;java:jboss/datasources/dct-service-db&quot; pool-name=&quot;MySqlDS&quot; enabled=&quot;true&quot; use-ccm=&quot;false&quot;&gt;
                  &lt;connection-url&gt;jdbc:mysql://localhost:3306/dct&lt;/connection-url&gt;
                  &lt;driver-class&gt;com.mysql.jdbc.Driver&lt;/driver-class&gt;
                  &lt;driver&gt;mysql-connector-java-5.1.39-bin.jar_com.mysql.jdbc.Driver_5_1&lt;/driver&gt;
                  &lt;pool&gt;
                      &lt;max-pool-size&gt;100&lt;/max-pool-size&gt;
                      &lt;flush-strategy&gt;IdleConnections&lt;/flush-strategy&gt;
                  &lt;/pool&gt;
                  &lt;security&gt;
                      &lt;user-name/&gt;
                      &lt;password/&gt;
                  &lt;/security&gt;
                  &lt;validation&gt;
                      &lt;valid-connection-checker class-name=&quot;org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker&quot;/&gt;
                      &lt;background-validation&gt;true&lt;/background-validation&gt;
                      &lt;exception-sorter class-name=&quot;org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter&quot;/&gt;
                  &lt;/validation&gt;
                  &lt;timeout&gt;
                      &lt;idle-timeout-minutes&gt;2&lt;/idle-timeout-minutes&gt;
                  &lt;/timeout&gt;
              &lt;/datasource&gt;
  </code></pre>
  <p>Here is some of my spring-config.xml:</p>
  <pre><code>&lt;bean id=&quot;dataSource&quot; class=&quot;org.springframework.jndi.JndiObjectFactoryBean&quot;&gt;
    &lt;property name=&quot;jndiName&quot; value=&quot;java:jboss/datasources/dct-service-db&quot;/&gt;
   &lt;/bean&gt;
  &lt;bean id=&quot;sessionFactory&quot;
      class=&quot;org.springframework.orm.hibernate4.LocalSessionFactoryBean&quot;&gt;
      &lt;property name=&quot;dataSource&quot; ref=&quot;dataSource&quot; /&gt;
      &lt;property name=&quot;annotatedClasses&quot;&gt;
          &lt;list&gt;
              &lt;value&gt;com.hmhco.dct.entity.District&lt;/value&gt;
              &lt;!--value&gt;...&lt;/value--&gt;
          &lt;/list&gt;
      &lt;/property&gt;
      &lt;property name=&quot;hibernateProperties&quot;&gt;
          &lt;props&gt;
              &lt;prop key=&quot;hibernate.dialect&quot;&gt;org.hibernate.dialect.MySQL5Dialect&lt;/prop&gt;
              &lt;prop key=&quot;hibernate.show_sql&quot;&gt;${hibernate.show_sql}&lt;/prop&gt;
              &lt;prop key=&quot;hibernate.hbm2ddl.auto&quot;&gt;update&lt;/prop&gt;
          &lt;/props&gt;
      &lt;/property&gt;
  &lt;/bean&gt;
  </code></pre>
  <p>Finally - here is some of BaseDAO.java which manages the sessionFactory property and uses it to access the datasource:</p>
  <pre><code>public class BaseDAO {
  
  private static final Logger log = Logger.getLogger(BaseDAO.class);
  
  protected SessionFactory sessionFactory;
  
  
  public void setSessionFactory(SessionFactory sessionFactory) {
    this.sessionFactory = sessionFactory;
  }
  
  public BaseEntity findById(Class entityClass, Integer id) throws Exception {
      Session session = sessionFactory.openSession();
      Criteria crit = session.createCriteria(entityClass);
      crit.add(Restrictions.eq(&quot;id&quot;, id));
      BaseEntity entity = (BaseEntity) crit.uniqueResult();
      if (entity != null) {
        entity.setLastAccessed(new Date());
        session.update(entity);
      }
      session.close();
      return entity;
   }
  </code></pre>
  <p>Let me know if this is enough information to help diagnose the problem - or if you want/need more.</p>
  <p>I did just find this information on a hibernate page: <a href="https://docs.jboss.org/hibernate/orm/3.3/reference/en-US/html/session-configuration.html" rel="nofollow noreferrer">https://docs.jboss.org/hibernate/orm/3.3/reference/en-US/html/session-configuration.html</a></p>
  <blockquote>
  <p>Hibernate's own connection pooling algorithm is, however, quite rudimentary.
  It is intended to help you get started and is not intended for use in a
  production system, or even for performance testing. You should use a third
  party pool for best performance and stability. Just replace the
  hibernate.connection.pool_size property with connection pool
  specific settings. This will turn off Hibernate's internal pool.
  For example, you might like to use c3p0.</p>
  <p>C3P0 is an open source JDBC connection pool distributed along with Hibernate
  in the lib directory. Hibernate will use its
  org.hibernate.connection.C3P0ConnectionProvider for connection pooling
  if you set hibernate.c3p0.* properties</p>
  </blockquote>