user5776462 user5776462 - 3 months ago 13
Java Question

Spring and hibernate: query.list() does not get executed more than twice

I have a class in java with two more methods similar to the one here.

public Object noOfEmployees() {

List<Employee> emp = null;
String u = user.getUserName();

if ("user1".equals(u)) {

Query query = getHibernateTemplate().getSessionFactory().openSession()
.createSQLQuery("select * from employee where job='System Analyst'")
.addEntity(EMPLOYEE.class);

emp = query.list();

getHibernateTemplate().getSessionFactory().openSession().close();

} else if ("user2".equals(u)) {

Query query = getHibernateTemplate().getSessionFactory().openSession()
.createSQLQuery("select * from employee where job='DBA'")
.addEntity(EMPLOYEE.class);

emp = query.list();
getHibernateTemplate().getSessionFactory().openSession().close();

}
return emp.size();
}


When I ran the application, this is how I got the output:


  1. Logged in as 'user2'

    Hibernate: select * from employee where job='DBA'

    Hibernate: select * from employee where job='DBA' and rank='2'

    Hibernate: select * from employee where present='yes'

  2. Logged in as 'user1'

    Hibernate: select * from employee where job='System Analyst'

    Hibernate: select * from employee where job='System Analyst' and rank='3'

    Hibernate: select * from employee where present='yes'

  3. Again, logged in as 'user2', first two methods get executed.

    Hibernate: select * from employee where job='DBA'

    Hibernate: select * from employee where job='DBA' and rank='2'

  4. When I logged in as any user, this time even the first method did not get executed.



I have noticed that the code gets stuck when query.list() is encountered. I know that using hibernateTemplate is not recommended but the entire application is written using it. I have only started to learn about spring and hibernate. I will start making changes once I get comfortable with those.

Any suggestions related to the performance of query.list() and ways to improve the code are more than welcome.

Thank you!

Answer

Your code is flawed on many levels... For starters you shouldn't be using HibernateTemplate unless you are in a very old application, else use a plain SessionFactory. See http://docs.spring.io/spring/docs/current/spring-framework-reference/html/orm.html#orm-hibernate-straight for more information.

Second when using Spring use Spring to manage your resources, i.e. Session in this case, or if you want to do it yourself at least manage it properly!.

So in short use a SessionFactory and use getCurrentSession instead of openSession. And use a proper query.

@Autowired
private SessionFactory sf;

@Transactional
public Long noOfEmployees() {
    final String query = "select count(*) from employee where job=:job";
    Query q = sf.getCurrentSession().createSQLQuery(query);
    if ("user1".equals(u)) {
        q.setParameter("job", "System Analyst");
    } else if ("user2".equals(u) ) {
        q.setParameter("job", "DBA");
    }
    return (Long) query.uniqueResult();
}

The @Transactional will make spring manage the resources, assuming you have <tx:annotation-driven /> in your context and properly added the HibernateTransactionManager.