-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 
Author Message
 Post subject: Hibernate Sessions
PostPosted: Mon May 17, 2010 10:42 am 
Beginner
Beginner

Joined: Mon May 10, 2010 2:00 pm
Posts: 22
I am having problems with what seems to be unclosed database sessions. This is causing my websites to stop displaying information from the database about once a day. We are using Oracle 11g as the database and the DBA's are telling me that there is an Oracle Session open with the credentials that the website uses and that the open session is blocking other sessions with the same credentials.

I feel that I am doing something wrong in how I am using Hibernate. Here is some code from a small website that I am experiencing this problem with:

com.dao.HibernateUtil
Code:
package com.dao;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

public class HibernateUtil {

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

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

    public static void releaseSession(Session session) {
        // releases the session and clears any memory that may be
        // locked down for the hibernate session
        try {
        session.flush();
        session.clear();
        session.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void handleException(Exception e, Transaction trans, Session session) {
        try {
            e.printStackTrace();
            if (trans != null) trans.rollback();
            if (e instanceof RuntimeException) {
                throw new RuntimeException("Runtime type exception rethrown from HibernateUtil", e);
            }
        } catch (HibernateException e2) {
            e2.printStackTrace();
            throw new RuntimeException("Exception in HibernateUtil while handling the contained exception", e2);
        } finally {
            releaseSession(session);
        }
    }

}



com.dao.Dao
Code:
package com.dao;

import com.dao.entity.Person;
import com.dao.entity.Project;
import com.dao.entity.Report;
import com.util.Util;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.hibernate.Hibernate;
import org.hibernate.Session;
import org.hibernate.Transaction;

public class Dao {

    public Session session = null;
    public Transaction trans = null;
    private static String source_name = "Dao";
    private static String schema = "DW12FGHWEB01VW";


    // =================================================================
    // Functions used for retrieving and releasing the Hibernate Session
    // =================================================================
    private Session getSession() {
        return HibernateUtil.getSession();
    }

    private void closeSession(Session session) {
        HibernateUtil.releaseSession(session);
    }
    // =================================================================

    // =================================================================
    // Reusable Functions
    // =================================================================

    public Object storeObjectReturnObject(Object obj) {
        try {
            session = getSession();
            trans = session.beginTransaction();
            session.saveOrUpdate(obj);
            trans.commit();
            obj = session.merge(obj);
        } catch (Exception e) {
            Util.printError(e);
        } finally {
            closeSession(session);
        }
        return obj;
    }

    public boolean storeObjects(ArrayList objects) {
        boolean result = true;
        try {
            session = getSession();
            trans = session.beginTransaction();
            for (int i = 0; i < objects.size(); i++) {
                Object object = objects.get(i);
                session.saveOrUpdate(object);
            }
        } catch (Exception e) {
            result = false;
            Util.printError(e);
            trans.rollback();
        } finally {
            trans.commit();
            closeSession(session);
        }
        return result;
    }

    public boolean storeObject(Object obj) {
        boolean result = true;
        try {
            session = getSession();
            trans = session.beginTransaction();
            session.saveOrUpdate(obj);
            trans.commit();
        } catch (Exception e) {
            Util.printError(e);
            result = false;
        } finally {
            closeSession(session);
        }
        return result;
    }

    public boolean deleteObject(Object obj) {
        boolean result = true;
        try {
            session = getSession();
            trans = session.beginTransaction();
            session.delete(obj);
            trans.commit();
        } catch (Exception e) {
            Util.printError(e);
            result = false;
        } finally {
            closeSession(session);
        }
        return result;
    }

    public boolean deleteFrom(String hql) {
        boolean result = true;
        try {
            session = getSession();
            trans = session.beginTransaction();
            int rowCount = session.createQuery(hql).executeUpdate();
            trans.commit();
        } catch (Exception e) {
            Util.printError(e);
        } finally {
            closeSession(session);
        }
        return result;
    }

    public ArrayList getObjects(String hql) {
        ArrayList list = new ArrayList();

        try {
            session = getSession();
            trans = session.beginTransaction();
            list = (ArrayList) session.createQuery(hql).list();
            trans.commit();
        } catch (Exception e) {
            Util.printError(e);
        } finally {
            closeSession(session);
        }

        return list;
    }

    public boolean checkExists(String sql) {
        boolean exists = false;
        try {
            session = getSession();
            trans = session.beginTransaction();
            int count = (Integer) session.createSQLQuery(sql).addScalar("COUNT", Hibernate.INTEGER).uniqueResult();
            if (count > 0) {
                exists = true;
            }
        } catch (Exception e) {
            Util.printError(e);
        } finally {
            closeSession(session);
        }
        return exists;
    }

    public int countAny(String sql) {
        int count = 0;
        try {
            session = getSession();
            trans = session.beginTransaction();
            count = (Integer) session.createSQLQuery(sql).addScalar("COUNT", Hibernate.INTEGER).uniqueResult();
        } catch (Exception e) {
            Util.printError(e);
        } finally {
            closeSession(session);
        }
        return count;
    }

    public Object getMax(String sql, String returnType) {
        Object returnObject = new Object();
        try {
            session = getSession();
            trans = session.beginTransaction();
            if (returnType.equals("date")) {
                returnObject = (Date) session.createSQLQuery(sql).addScalar("MAX_VALUE", Hibernate.DATE).uniqueResult();
            }
            if (returnType.equals("integer")) {
                returnObject = (Integer) session.createSQLQuery(sql).addScalar("MAX_VALUE", Hibernate.INTEGER).uniqueResult();
            }
        } catch (Exception e) {
            Util.printError(e);
        } finally {
            closeSession(session);
        }
        return returnObject;
    }

    // =================================================================

    public ArrayList getAllProjects() {
        String hql = "from Project";
        return getObjects(hql);
    }

    public Project getProjectByProjectName(String project_name) {
        String hql = "from Project where project_name='" + project_name + "'";
        List<Project> project_list = null;
        Project project = new Project();
        try {
            session = getSession();
            trans = session.beginTransaction();
            project_list = session.createQuery(hql).list();
            for (int i = 0; i < project_list.size(); i++) {
                project = project_list.get(i);
            }
        } catch (Exception e) {
            Util.printError(e);
        } finally {
            closeSession(session);
        }
        return project;
    }

    public Project getProjectByProjectId(int project_id) {
        String hql = "from Project where project_id=" + project_id;
        List<Project> project_list = null;
        Project project = new Project();
        try {
            session = getSession();
            trans = session.beginTransaction();
            project_list = session.createQuery(hql).list();
            for (int i = 0; i < project_list.size(); i++) {
                project = project_list.get(i);
            }
        } catch (Exception e) {
            Util.printError(e);
        } finally {
            closeSession(session);
        }
        return project;
    }

    public boolean checkProjectExists(String project_name) {
        String sql = "select COUNT(*) AS COUNT from " + schema + ".project where project_name='" + project_name + "'";
        return checkExists(sql);
    }

    public boolean updateProject(Project project) {
        return storeObject(project);
    }

    public Person getPersonByFullName(String full_name) {
        String hql = "from Person where full_name='" + full_name.toUpperCase() + "'";
        ArrayList people = getObjects(hql);
        Person person = new Person();
        for (int i = 0; i < people.size(); i++) {
            person = (Person) people.get(i);
        }
        return person;
    }

    public Person getPersonByPersonId(int person_id) {
        String hql = "from Person where person_id=" + person_id;
        ArrayList people = getObjects(hql);
        Person person = new Person();
        for (int i = 0; i < people.size(); i++) {
            person = (Person) people.get(i);
        }
        return person;
    }

    public boolean updatePerson(Person person) {
        person.setFullName(person.getFullName().toUpperCase());
        return storeObject(person);
    }

    public boolean checkPersonExists(String full_name) {
        String sql = "select COUNT(*) AS COUNT from " + schema + ".person where full_name='" + full_name.toUpperCase() + "'";
        return checkExists(sql);
    }

    public ArrayList getReportsByProjectId(int project_id) {
        String hql = "from Report where project_id=" + project_id;
        List<Report> report_list = null;
        ArrayList<Report> list = new ArrayList<Report>();
        try {
            session = getSession();
            trans = session.beginTransaction();
            report_list = session.createQuery(hql).list();
            for (int i = 0; i < report_list.size(); i++) {
                Report report = report_list.get(i);
                Person person = getPersonByPersonId(report.getPerson().getPersonId());
                Project project = getProjectByProjectId(report.getProject().getProjectId());
                report.setPerson(person);
                report.setProject(project);
                list.add(report);
            }
        } catch (Exception e) {
            Util.printError(e);
        } finally {
            closeSession(session);
        }
        return list;
    }

    public ArrayList getReportsByProjectIdWithSort(int project_id, String sortby) {
        String hql = "from Report where project_id=" + project_id + " order by " + sortby;
        List<Report> report_list = null;
        ArrayList<Report> list = new ArrayList<Report>();
        try {
            session = getSession();
            trans = session.beginTransaction();
            report_list = session.createQuery(hql).list();
            for (int i = 0; i < report_list.size(); i++) {
                Report report = report_list.get(i);
                Person person = getPersonByPersonId(report.getPerson().getPersonId());
                Project project = getProjectByProjectId(report.getProject().getProjectId());
                report.setPerson(person);
                report.setProject(project);
                list.add(report);
            }
        } catch (Exception e) {
            Util.printError(e);
        } finally {
            closeSession(session);
        }
        return list;
    }

    public boolean updateReport(Report report) {
        report.getPerson().setFullName(report.getPerson().getFullName().toUpperCase());
        return storeObject(report);
    }

    public Report getReportByReportId(int report_id) {
        String hql = "from Report where report_id=" + report_id;
        List<Report> report_list = null;
        Report report = new Report();
        try {
            session = getSession();
            trans = session.beginTransaction();
            report_list = session.createQuery(hql).list();
            for (int i = 0; i < report_list.size(); i++) {
                report = report_list.get(i);
                Person person = getPersonByPersonId(report.getPerson().getPersonId());
                Project project = getProjectByProjectId(report.getProject().getProjectId());
                report.setPerson(person);
                report.setProject(project);
            }
        } catch (Exception e) {
            Util.printError(e);
        } finally {
            closeSession(session);
        }
        return report;
    }

}



hibernate.cfg.xml
Code:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
  <session-factory>
    <property name="hibernate.dialect">org.hibernate.dialect.OracleDialect</property>
    <property name="hibernate.connection.driver_class">oracle.jdbc.OracleDriver</property>
    <property name="hibernate.connection.url">jdbc:oracle:thin:@xxx.xxx.xxx.xxx:xxxx:DW12FGHWEB01VW</property>
    <property name="hibernate.default_schema">DW12FGHWEB01VW</property>
    <property name="hibernate.connection.username">********</property>
    <property name="hibernate.connection.password">********</property>
    <property name="hibernate.jdbc.batch_size">20</property>

    <mapping resource="dwd/dao/entity/Person.hbm.xml"/>
    <mapping resource="dwd/dao/entity/Report.hbm.xml"/>
    <mapping resource="dwd/dao/entity/Project.hbm.xml"/>
  </session-factory>
</hibernate-configuration>



Top
 Profile  
 
 Post subject: Re: Hibernate Sessions
PostPosted: Mon May 17, 2010 11:06 am 
Regular
Regular

Joined: Tue May 11, 2010 5:50 pm
Posts: 54
Location: Norman, Ok, U.S.A
You should look into C3P0 Connection Provider and also adding the property of hibernate.current_session_context_class and set it to thread


Top
 Profile  
 
 Post subject: Re: Hibernate Sessions
PostPosted: Tue May 18, 2010 1:11 pm 
Beginner
Beginner

Joined: Mon May 10, 2010 2:00 pm
Posts: 22
ivirani wrote:
You should look into C3P0 Connection Provider and also adding the property of hibernate.current_session_context_class and set it to thread


I am implementing. It will take some time to determine if the change makes a difference. Thank you for your suggestion.


Top
 Profile  
 
 Post subject: Re: Hibernate Sessions
PostPosted: Fri May 21, 2010 5:14 pm 
Beginner
Beginner

Joined: Mon May 10, 2010 2:00 pm
Posts: 22
It appears that adding CP30 to my project has cleared up the issues I was having with blocked and unclosed Oracle Database Sessions. I haven't had a problem with the site for a few days. Again, it hasn't been fully tested, but the connection pooling appears to have significantly improved the status of my web application.

Thank you.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.