Hibernate performance: several queries instead of one after form:select in jsp

Question

I was analyzing the performance of my aplication and I have notice the following. I have this query:

public List<Rol> findAll() {
        return mySessionFactory.getCurrentSession().createQuery("from Rol").list();
    }

And this returns this query:

SELECT rol0_._id as column1_2_, rol0_.description as descripc2_2_, rol0_.name as nombre6_2_, rol0_.status as status7_2_ FROM rol rol0_

This is fine, but in my jsp I have the following:

<form:select multiple="true" path="roles" required="true">
                    <form:options items="${roles}" itemValue="id" itemLabel="nombre" />
                </form:select>

And this creates a new query for each rol:

DEBUG: org.hibernate.SQL - select rol0_._id as column1_2_0_, rol0_.description as descripc2_2_0_, rol0_.name as name6_2_0_, rol0_.status as status7_2_0_ from rol rol0_ where rol0_._id=?
TRACE: org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [1] as [VARCHAR] - 1
DEBUG: org.hibernate.SQL - select rol0_._id as column1_2_0_, rol0_.description as descripc2_2_0_, rol0_.name as name6_2_0_, rol0_.status as status7_2_0_ from rol rol0_ where rol0_._id=?
TRACE: org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [1] as [VARCHAR] - 2
DEBUG: org.hibernate.SQL - select rol0_._id as column1_2_0_, rol0_.description as descripc2_2_0_, rol0_.name as name_2_0_, rol0_.status as status7_2_0_ from rol rol0_ where rol0_._id=?
TRACE: org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [1] as [VARCHAR] - 20130915150706256
DEBUG: org.hibernate.SQL - select rol0_._id as column1_2_0_, rol0_.description as descripc2_2_0_, rol0_.name as name6_2_0_, rol0_.status as status7_2_0_ from rol rol0_ where rol0_._id=?
TRACE: org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [1] as [VARCHAR] - 3

In my controller I have:

List<Rol> roles = rolDao.findAll();
ModelAndView mav = new ModelAndView("usuarioNew");
mav.getModelMap().put("roles", roles);

Is there any way to avoid this?. The first query has all the information that I need, I dont want extra queries.

Edit:

@Entity
@Table(name = "rol", uniqueConstraints = { @UniqueConstraint(columnNames = "name") })
public class Rol implements Serializable{

    @Id
    @Column(name = "_id")
    private String id;

    @Column(name = "name")
    @NotEmpty
    private String name;

    @Column(name = "description")
    private String description;

    @Column(name = "status")
    private String status;


    @NotEmpty
    @Fetch(FetchMode.JOIN)
    @OneToMany(mappedBy = "rolPermission_pk.rol", orphanRemoval = true, cascade=CascadeType.ALL)
    private Set<Rol_Permission> permissions = new HashSet<Rol_Permission>(0);

    ....//getters, setters
    }

Edit2:

public class UserRolCollectionEditor extends CustomCollectionEditor {

     private final RolDAO rolDao;


    public UserRolCollectionEditor (Class<?> collectionType, RolDAO rolDao) {

        super(collectionType);
        this.rolDao = rolDao;

    }

    @Override
    protected Object convertElement(Object element) {
        String rolId = (String) element;

        Rol rol = rolDao.findById(rolId);
        User_Rol usuRol = new User_Rol();


        User user = new User();

        usuRol.setUser(user);
        usuRol.setRol(rol);
        usuRol.setStatus("active");

        return usuRol;
    }

}

Edit3:

I have made some tests and the problem is with UserRolCollectionEditor and @Fetch(FetchMode.JOIN). If I delete the UserRolCollectionEditor and @Fetch(FetchMode.JOIN) from code, I get one query. If a delete only @Fetch(FetchMode.JOIN) I get extra queries. If a delete only UserRolCollectionEditor I get extra queries.. I dont know what to do..

My guess is that the queries you see aren't so much for the collection of roles returned from the findAll method but the roles already present on the User object. Do you have a `RolEditor` or `RolConverter` configured in an @InitBinder annotated method?! — M. Deinum, Sep 17 '13 at 05:32
yes!! you have a point!!. I totally forgot about the RolEditor, thats the reason for the queries!! I thought it had to do with hibernate it self. So, do I have another option for this?. Do I have to save the roles of an user by hand to avoid the extra queries?. I have edited the question. — kiduxa, Sep 17 '13 at 13:16
I have made some tests, and in fact RolEditor is making the extra queries but the @Fetch(FetchMode.JOIN) in rol table as well. — kiduxa, Sep 17 '13 at 14:20

Mikhail · Accepted Answer · 2013-09-19T02:21:17.483

1

I can't say why hibernate generate excessive queries, but they are created due to presence of persistence context (ie open session) when accessing ${roles} in your JSP. To prevent such behavior you can try to close session beforehand (I think it's more like a workaround than a solution in your case). There are several way to achieve that (using session-per-request pattern):

Use mySessionFactory.openSession() and mySessionFactory.closeSession() in your DAO;
Use getCurrentSession().beginTransaction() and getCurrentSession().commitTransaction() in your DAO;
Create service layer and let Spring manage transactions.

Great spring layered webapp example

Dealing with sessions and transactions in Hibernate

UPDATE

In UserRolCollectionEditor line Rol rol = rolDao.findById(rolId); can create select queries if you use session.get() in underlying rolDao. If it is the case you can change it to session.load() instead to avoid extra queries. In short, common usage scenario for session.load() is to create associations between entity objects - it is exactly what you are doing in UserRolCollectionEditor.convertElement() method.

Short article shows difference between get and load

edited Sep 19 '13 at 02:21

answered Sep 17 '13 at 04:36

Mikhail

246
3
10

If that is what is happening, I will get an exception when try to retrieve the roles, right? – kiduxa Sep 17 '13 at 04:53
@kiduxa after session is closed role objects will be in detached state (see http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/objectstate.html). You can access roles, but if you try to access associated lazy collection (such as permissions) you'll get exception. – Mikhail Sep 17 '13 at 05:43
yes, you are right. But my dao is like the one in the first link that you gave me. This extra queries have to be with the @Fetch(FetchMode.JOIN) annotation and because the UserRolCollectionEditor. See my edited question. – kiduxa Sep 17 '13 at 14:30
1

@kiduxa In `UserRolCollectionEditor` line `Rol rol = rolDao.findById(rolId);` can create select queries if you use session.get() in underlying rolDao. If it is the case you can change it to session.load() instead to avoid extra queries. (better to keep both session.get() and load() in separate dao methods available for different purposes) see http://stackoverflow.com/questions/8044963/hibernate-3-6-session-get-vs-session-load – Mikhail Sep 18 '13 at 03:50
Thanks for this. I didnt know the difference between load and get. Actually my findById in rolDao was using get. I changed to load and delete @Fetch(FetchMode.JOIN) in the model Rol and no extra queries. Please edit your answer, so anyone that have faced the problem can see the solution for this quickly. – kiduxa Sep 18 '13 at 16:07

Hibernate performance: several queries instead of one after form:select in jsp

1 Answers1