title: Hibernate's session.connection Error: "Too many users"
body: <p>I am developing a Java web application using Hibernate, but there is a part of it where I want to use JDBC because I am creating look-up tables dynamically.</p>
  
  <p>After a few minutes of using my web app I get this error: </p>
  
  <blockquote>
    <p>com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException:
    Data source rejected establishment of
    connection,  message from server: "Too
    many connections"</p>
  </blockquote>
  
  <p>I know that using session.connection() is deprecated but I just want to get the underlying JDBC connection. I experimented using the session.doWork() but the error still occurred as it had before.</p>
  
  <p>Here is what the code looks like:</p>
  
  <p>Domain Layer:</p>
  
  <pre><code>/**
   * Goes through the list of lookupTableAbstractions and persists each one
   * @param lookupData
   */
  public void updateLookupValues( List&lt;LookupTableAbstraction&gt; lookupData )
  {
      lookupTablesData.dropAllLookupTables(lookupData);
  
      lookupTablesData.createLookupTables(lookupData);
  
      for (LookupTableAbstraction lookupTable : lookupData)
          lookupTablesData.persistLookupTableValues(lookupTable);
  }
  </code></pre>
  
  <p>Data Layer:</p>
  
  <pre><code>public LookupTableAbstraction getLookupTable( String tableName )
  {
      LookupTableAbstraction lookupTable = new LookupTableAbstraction();
  
      Session session = getSessionFactory().openSession();
  
      String sqlQuery = "select value from " + tableName;
  
      List&lt;String&gt; lookupTableValues = session.createSQLQuery(sqlQuery).list();
  
      session.close(); 
  
      lookupTable.setTableName(tableName);
      for (String value : lookupTableValues)
          lookupTable.addValue(value);
  
      return lookupTable;
  }
  
  /**
   * Persists the passed in lookup table.
   * The lookup table that is used is determine by
   * the tableName field of the passed in lookupTable
   * @param lookupTable
   */
  public void persistLookupTableValues( LookupTableAbstraction lookupTable )
  {
      Session session = getSessionFactory().openSession();
      Transaction tx = session.beginTransaction();
      Connection connection = null;
      try
      {
          connection = getJDBCConnectionFromHibernate(session);
  
          Statement stmt = connection.createStatement();
  
          String tableName = lookupTable.getTableName();
          for (String value : lookupTable.getValues() ) 
          {
              String sql = " insert into " + tableName + 
                           " (value) " +
                           " values " +
                           "('" + value + "')";
  
              System.out.println(sql);
  
              stmt.executeUpdate(sql);
          }
          stmt.close();
      }
      catch( Exception e )
      {
          System.out.println("Exception(persistLookupTableValues): " + e.getMessage());
          e.printStackTrace();
      }
      finally
      {
  
          try {
              tx.commit();
              connection.close();
              session.close();
          } catch (SQLException e) {
              System.out.println("Exception(persistLookupTableValues): " + e.getMessage());
              e.printStackTrace();
          }
      }
  }
  
  /**
   * Drop's all lookup tables.
   * It drops each table based off the lookupTableAbstractions in the passed in list
   * @param lookupData
   */
  public void dropAllLookupTables( List&lt;LookupTableAbstraction&gt; lookupData )
  {
      Session session = getSessionFactory().openSession();
      Transaction tx = session.beginTransaction();
  
      Connection connection = null;
      try
      {
          connection = getJDBCConnectionFromHibernate(session);
  
          Statement stmt = null;
          for (LookupTableAbstraction lookupTableAbstraction : lookupData) {
              stmt = connection.createStatement();
              stmt.executeUpdate("drop table " + lookupTableAbstraction.getTableName());
          }
  
          stmt.close();
      }
      catch( Exception e )
      {
          System.out.println("Exception(dropAllLookupTables): " + e.getMessage());
          e.printStackTrace();
      }
      finally
      {
          try {
              tx.commit();
              connection.close();
              session.close();
          } catch (SQLException e) {
              System.out.println("Exception(dropAllLookupTables): " + e.getMessage());
              e.printStackTrace();
          }
      }
  }
  
  /**
   * Creates all lookup tables, one for each lookupTableAbstraction
   * in the passed in list
   * @param lookupData
   */
  public void createLookupTables( List&lt;LookupTableAbstraction&gt; lookupData )
  {
      Session session = getSessionFactory().openSession();
      Transaction tx = session.beginTransaction();
  
      Connection connection = null;
      try
      {
          connection = getJDBCConnectionFromHibernate(session);
  
          Statement stmt = null;
          for (LookupTableAbstraction lookupTableAbstraction : lookupData) {
              stmt = connection.createStatement();
              stmt.executeUpdate("create table " + lookupTableAbstraction.getTableName() + 
                                 " ( ID  int(11) auto_increment, " +
                                 "  value text, " +
                                 "  primary key (ID) )");
          }
  
          stmt.close();
      }
      catch( Exception e )
      {
          System.out.println("Exception(createLookupTables): " + e.getMessage());
          e.printStackTrace();
      }
      finally
      {
          try {
              tx.commit();
              connection.close();
              session.close();
          } catch (SQLException e) {
              System.out.println("Exception(createLookupTables): " + e.getMessage());
              e.printStackTrace();
          }
      }
  }
  
  protected Connection getJDBCConnectionFromHibernate( Session session )
  {
      return session.connection();
  }
  </code></pre>
  
  <p>Thanks for any suggestions</p>