Hibernate's session.connection Error: "Too many users"

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.

After a few minutes of using my web app I get this error:

com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"

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.

Here is what the code looks like:

Domain Layer:

/**
 * Goes through the list of lookupTableAbstractions and persists each one
 * @param lookupData
 */
public void updateLookupValues( List<LookupTableAbstraction> lookupData )
{
    lookupTablesData.dropAllLookupTables(lookupData);

    lookupTablesData.createLookupTables(lookupData);

    for (LookupTableAbstraction lookupTable : lookupData)
        lookupTablesData.persistLookupTableValues(lookupTable);
}

Data Layer:

public LookupTableAbstraction getLookupTable( String tableName )
{
    LookupTableAbstraction lookupTable = new LookupTableAbstraction();

    Session session = getSessionFactory().openSession();

    String sqlQuery = "select value from " + tableName;

    List<String> lookupTableValues = session.createSQLQuery(sqlQuery).list();

    session.close(); 

    lookupTable.setTableName(tableName);
    for (String value : lookupTab开发者_JAVA技巧leValues)
        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<LookupTableAbstraction> 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<LookupTableAbstraction> 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();
}

Thanks for any suggestions

Same problem here. A lot of the examples on the internet forget to close out the session factory. If you don't close this out you will get the mysql "too many connections" error. Close it out with this line of code:

fact.close();

Assuming you named it fact like this:

SessionFactory fact = new Configuration().configure().buildSessionFactory();

I fixed the problem.

I was creating the session factory over and over.

So, I did this:

protected static SessionFactory sessionFactory = null;

static 
{
    sessionFactory = new Configuration().configure().buildSessionFactory();
}

/**
 * Returns a Hibernate session factory
 * @return
 */
protected static SessionFactory getSessionFactory()
{       
    return sessionFactory;
}