connection problem in mysql

[Khushwinder Chahal]

Hi All,

I have done pagination with hibernate and jsp pager tag lib. It is working fine.
The queries i am using are as follow:

Method for getting employees based on query related...
	public List getAllEmployees(int pageSize, int pageNumber) {
		Session session = HibernateUtil.getSession();
		pageSize = 10;
		try {
			session.beginTransaction();
			Query query = session
					.createQuery("from Employee as e left join fetch e.department d ");
			
			query = query.setFirstResult(pageSize * (pageNumber - 1));
			query.setMaxResults(pageSize).list();
			empList = query.list();
			return empList;
		} finally {
			session.close();
		}
	}

Method for getting total count for the employees.

	public int getEmployeesCount() {
		
			Session session = HibernateUtil.getSession();
			int size = 0;
			try {
		    Criteria crit = session.createCriteria(Employee.class);
	        crit.setProjection(Projections.rowCount());
	        List results = crit.list();
	        size = ((Integer) crit.list().get(0)).intValue(); 
	   		}finally {
				session.close();
			}
	        return size;
	        
	}

Now i am testing it against 1,00,000 records in the database(mysql).
But when i do the rigrous testing i.e. by clicking on the next button so many times and very fastly i get the following err :

SEVERE: Servlet.service() for servlet action threw exception
java.sql.SQLException: Data source rejected establishment of connection,  message from server: "Too many connections"
	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1018)
	at com.mysql.jdbc.Connection.createNewIO(Connection.java:2572)
	at com.mysql.jdbc.Connection.<init>(Connection.java:1485)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
	at java.sql.DriverManager.getConnection(Unknown Source)
	at java.sql.DriverManager.getConnection(Unknown Source)
	at org.hibernate.connection.DriverManagerConnectionProvider.getConnection(DriverManagerConnectionProvider.java:110)
	at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:417)
	at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:144)
	at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:119)
	at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:57)
	at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1326)
	at net.reumann.demo.persistence.EmployeeHibernateDao.getAllEmployees(EmployeeHibernateDao.java:37)
	at net.reumann.demo.service.EmployeeDaoService.getAllEmployees(EmployeeDaoService.java:25)
	at net.reumann.demo.action.LinkAction.populateEmployees(LinkAction.java:70)
	at net.reumann.demo.action.LinkAction.employee(LinkAction.java:53)
	at sun.reflect.GeneratedMethodAccessor95.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:274)
	at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:194)
	at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419)
	at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
	at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
	at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:263)
	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:584)
	at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
	at java.lang.Thread.run(Unknown Source)

My hibernate.xml file is given below:

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
  "-//Hibernate/Hibernate Configuration DTD//EN"
  "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
     <session-factory>
     	<property name="connection.useUnicode">true</property> 
        <property name="connection.characterEncoding">UTF-8</property>
        <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="connection.url">jdbc:mysql://localhost:3306/xdb</property>
        <property name="connection.username">root</property>
        <property name="connection.password">admin</property>
        
        <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
        <property name="current_session_context_class">thread</property>
		<property name="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
		<!--<property name="show_sql">true</property>
		--><!--<property name="format_sql">true</property>     
        -->
        <property name="hibernate.connection.pool_size">50</property>
        <mapping resource="net/reumann/demo/vo/Department.hbm.xml"/>
        <mapping resource="net/reumann/demo/vo/Employee.hbm.xml"/>

      </session-factory>
</hibernate-configuration>

[Paul Sturrock]

Sounds like you are probably not closing your connections properly (or since this is Hibernate your Sessions).

[Rahul Babbar]

You are creating the transactions but are not commiting or rolling them back.
In the "Method for getting employees based on query",
do
"session.getTransaction().commit();"
after line 12.

[Khushwinder Chahal]

Hi All,
Thanks a lot for your replies.


I solved this problem with the following modification in my code :

public class HibernateUtil {

	private static SessionFactory sessionFactory;

	/*
	 * public static void createSessionFactory() {
	 * 
	 * try {
	 * 
	 * sessionFactory = new Configuration().configure().buildSessionFactory();
	 * 
	 * } catch (HibernateException hne) { throw new
	 * ExceptionInInitializerError(hne+"=exception"); }
	 * 
	 * }
	 */

	static {
		try {
			// Create the SessionFactory from hibernate.cfg.xml
			sessionFactory = new Configuration().configure()
					.buildSessionFactory();
			//System.out.println("in static");
		} catch (Throwable ex) {
			// Make sure you log the exception, as it might be swallowed
			System.err.println("Initial SessionFactory creation failed." + ex);
			throw new ExceptionInInitializerError(ex);
		}
	}

	public static Session getSession() {

		// createSessionFactory();
		return sessionFactory.openSession();
	}

}

The code which is commented is replaced by the static block.

Thanks and Regards.