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 : 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<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


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 : 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<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

