Sleep Connection in MySql for long time leads to Slow process in Hibernate

Question

I am using Hibernate in my application,

My connection code is as follows,

public class DBConnection{

    private static SessionFactory factory; 
    private static ServiceRegistry serviceRegistry;

    public  DBConnection()
       {
        try
          { 
                      factory = new Configuration().configure().buildSessionFactory();

          }
          catch (Throwable ex) { 
                    System.err.println("Failed to create   sessionFactory object." + ex);
                    throw new ExceptionInInitializerError(ex); 
                }
       }

    public Session getSession() 
    {
          return factory.openSession();
    }


   // Call this during shutdown
  public static void close() {
        factory.close();
        serviceRegistry=null;
        factory=null;

   }
}

My Hibernate config file is ,

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration SYSTEM 
"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>


   <session-factory>
   <property name="hibernate.dialect">
      org.hibernate.dialect.MySQLDialect
   </property>
   <property name="hibernate.connection.driver_class">
      com.mysql.jdbc.Driver

   </property>

   <!-- Assume test is the database name -->
   <property name="hibernate.connection.url">
      jdbc:mysql://localhost:3307/Test
   </property>
   <property name="hibernate.connection.username">
      root
   </property>
   <property name="hibernate.connection.password">
      root
   </property>
     <property name=" hibernate.dbcp.max Wait">100</property>
</session-factory>


</hibernate-configuration>

The sample code for this process is

 public String showurl(int id)
    {
        int i = 1;
        String url= "";
        Transaction tx= null;
        Session session = null;
        try
        {
            session = new DBConnection().getSession();
            tx = session.beginTransaction();
            Query query = session.createQuery(" FROM Test WHERE ID = :id");
            query.setInteger("id", id);

            List<Test> testlist= query.list();
            for(Test nl:testlist)
            {
                url= nl.geturl();
            }


        tx.commit();
        session.close();
        DBConnection.close();


    }catch(Exception ex)
    {
        if(tx!=null) tx.rollback();
        session.close();
        DBConnection.close();
        ex.printStackTrace();
    }
    finally
    {
        if(tx!=null)
        {
        tx=null;
        }
        if(session.isOpen())
        {
            session.close();
        }
        if(session!=null)
        {
            session= null;
        }
    }

        return url; 

    }

My problem here is , due to lot of sleep queries in the Database leads to slow down the process. How to solve this in my Application. I have more than 50 users using the application at the same time. how to solve this problem?

While Using this I am getting the following error frequently,

SEVERE: Servlet.service() for servlet [jsp] in context with path [] threw exception [org.hibernate.service.UnknownServiceException: Unknown service requested [org.hibernate.stat.spi.StatisticsImplementor]] with root cause
org.hibernate.service.UnknownServiceException: Unknown service requested [org.hibernate.stat.spi.StatisticsImplementor]
        at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:126)
        at org.hibernate.internal.SessionFactoryImpl.getStatisticsImplementor(SessionFactoryImpl.java:1468)
        at org.hibernate.internal.SessionFactoryImpl.getStatistics(SessionFactoryImpl.java:1464)
        at org.hibernate.internal.SessionImpl.close(SessionImpl.java:346)

Any suggestion is highly appreciated.

Cheers!!

Answer 1

I would start from using other connection pool manager than Hibernate's build-in one - It it not design for production use. Try to use C3P0. Here is little hint how to configure it for hibernate. More informations can be found here

EDIT: I have just noticed, that you are creating new session factory everytime you want to operate od db. That is SO WRONG! You should have only ONE instance of session factory, as service or application scope static field, and later use that for creating sessions as needed.

 public static SessionFactory getSessionFactory(){
     if (factory == null) {

         try {
             factory = new Configuration().configure().buildSessionFactory();

         } catch (Throwable ex) {
             System.err.println("Failed to create   sessionFactory object." + ex);
             throw new ExceptionInInitializerError(ex);
         }
     }
     return factory;

 }

 public static Session getSession() {
     return getSessionFactory().openSession();
 }

from code use getSession() directly.