JPA 2 - INNER JOIN calls Query again

Question

I'm trying to do some query in JPA 2 but it keeps calling another query to get informatinos of a dependency

My Classes are:

@Entity
@Table(name = "tbltranslados")
public class Traslado implements Serializable {

    @OneToOne(optional = true, mappedBy = "traslado", fetch = FetchType.LAZY)
    private TrasladoHora hora;
}

@Entity
@Table(name = "tbltranshora")
public class TrasladoHora implements Serializable {

    @OneToOne(optional = false,fetch=FetchType.LAZY)
    @JoinColumn(nullable = false, name = "thr_TransladosID")
    private Traslado traslado;
}

I call the query like this:

@SuppressWarnings("unchecked")
@Override
public List<Traslado> listarAtivos() {
    return this.session.createQuery("SELECT t , h FROM Traslado t LEFT JOIN t.hora h WHERE t.status=:status ORDER BY t.origem , t.destino")
            .setParameter("status", true)
            .getResultList();
}

But my result is:

Hibernate: select traslado0_.TransladosID as Translad1_14_0_, trasladoho1_.TranshoraID as Transhor1_13_1_, traslado0_.tra_Para as tra_Para2_14_0_, traslado0_.tra_De as tra_De3_14_0_, traslado0_.tra_Status as tra_Stat4_14_0_, trasladoho1_.thr_qtdHoras as thr_qtdH2_13_1_, trasladoho1_.thr_TransladosID as thr_Tran3_13_1_ from tbltranslados traslado0_ left outer join tbltranshora trasladoho1_ on traslado0_.TransladosID=trasladoho1_.thr_TransladosID where traslado0_.tra_Status=? order by traslado0_.tra_De, traslado0_.tra_Para Hibernate: select trasladoho0_.TranshoraID as Transhor1_13_0_, trasladoho0_.thr_qtdHoras as thr_qtdH2_13_0_, trasladoho0_.thr_TransladosID as thr_Tran3_13_0_ from tbltranshora trasladoho0_ where trasladoho0_.thr_TransladosID=? Hibernate: select trasladoho0_.TranshoraID as Transhor1_13_0_, trasladoho0_.thr_qtdHoras as thr_qtdH2_13_0_, trasladoho0_.thr_TransladosID as thr_Tran3_13_0_ from tbltranshora trasladoho0_ where trasladoho0_.thr_TransladosID=? Hibernate: select trasladoho0_.TranshoraID as Transhor1_13_0_, trasladoho0_.thr_qtdHoras as thr_qtdH2_13_0_, trasladoho0_.thr_TransladosID as thr_Tran3_13_0_ from tbltranshora trasladoho0_ where trasladoho0_.thr_TransladosID=? Hibernate: select trasladoho0_.TranshoraID as Transhor1_13_0_, trasladoho0_.thr_qtdHoras as thr_qtdH2_13_0_, trasladoho0_.thr_TransladosID as thr_Tran3_13_0_ from tbltranshora trasladoho0_ where trasladoho0_.thr_TransladosID=? Hibernate: select trasladoho0_.TranshoraID as Transhor1_13_0_, trasladoho0_.thr_qtdHoras as thr_qtdH2_13_0_, trasladoho0_.thr_TransladosID as thr_Tran3_13_0_ from tbltranshora trasladoho0_ where trasladoho0_.thr_TransladosID=? Hibernate: select trasladoho0_.TranshoraID as Transhor1_13_0_, trasladoho0_.thr_qtdHoras as thr_qtdH2_13_0_, trasladoho0_.thr_TransladosID as thr_Tran3_13_0_ from tbltranshora trasladoho0_ where trasladoho0_.thr_TransladosID=? Hibernate: select trasladoho0_.TranshoraID as Transhor1_13_0_, trasladoho0_.thr_qtdHoras as thr_qtdH2_13_0_, trasladoho0_.thr_TransladosID as thr_Tran3_13_0_ from tbltranshora trasladoho0_ where trasladoho0_.thr_TransladosID=? Hibernate: select trasladoho0_.TranshoraID as Transhor1_13_0_, trasladoho0_.thr_qtdHoras as thr_qtdH2_13_0_, trasladoho0_.thr_TransladosID as thr_Tran3_13_0_ from tbltranshora trasladoho0_ where trasladoho0_.thr_TransladosID=? Hibernate: select trasladoho0_.TranshoraID as Transhor1_13_0_, trasladoho0_.thr_qtdHoras as thr_qtdH2_13_0_, trasladoho0_.thr_TransladosID as thr_Tran3_13_0_ from tbltranshora trasladoho0_ where trasladoho0_.thr_TransladosID=? Hibernate: select trasladoho0_.TranshoraID as Transhor1_13_0_, trasladoho0_.thr_qtdHoras as thr_qtdH2_13_0_, trasladoho0_.thr_TransladosID as thr_Tran3_13_0_ from tbltranshora trasladoho0_ where trasladoho0_.thr_TransladosID=? Hibernate: select trasladoho0_.TranshoraID as Transhor1_13_0_, trasladoho0_.thr_qtdHoras as thr_qtdH2_13_0_, trasladoho0_.thr_TransladosID as thr_Tran3_13_0_ from tbltranshora trasladoho0_ where trasladoho0_.thr_TransladosID=? Hibernate: select trasladoho0_.TranshoraID as Transhor1_13_0_, trasladoho0_.thr_qtdHoras as thr_qtdH2_13_0_, trasladoho0_.thr_TransladosID as thr_Tran3_13_0_ from tbltranshora trasladoho0_ where trasladoho0_.thr_TransladosID=? Hibernate: select trasladoho0_.TranshoraID as Transhor1_13_0_, trasladoho0_.thr_qtdHoras as thr_qtdH2_13_0_, trasladoho0_.thr_TransladosID as thr_Tran3_13_0_ from tbltranshora trasladoho0_ where trasladoho0_.thr_TransladosID=? Hibernate: select trasladoho0_.TranshoraID as Transhor1_13_0_, trasladoho0_.thr_qtdHoras as thr_qtdH2_13_0_, trasladoho0_.thr_TransladosID as thr_Tran3_13_0_ from tbltranshora trasladoho0_ where trasladoho0_.thr_TransladosID=? Hibernate: select trasladoho0_.TranshoraID as Transhor1_13_0_, trasladoho0_.thr_qtdHoras as thr_qtdH2_13_0_, trasladoho0_.thr_TransladosID as thr_Tran3_13_0_ from tbltranshora trasladoho0_ where trasladoho0_.thr_TransladosID=? Hibernate: select trasladoho0_.TranshoraID as Transhor1_13_0_, trasladoho0_.thr_qtdHoras as thr_qtdH2_13_0_, trasladoho0_.thr_TransladosID as thr_Tran3_13_0_ from tbltranshora trasladoho0_ where trasladoho0_.thr_TransladosID=? Hibernate: select trasladoho0_.TranshoraID as Transhor1_13_0_, trasladoho0_.thr_qtdHoras as thr_qtdH2_13_0_, trasladoho0_.thr_TransladosID as thr_Tran3_13_0_ from tbltranshora trasladoho0_ where trasladoho0_.thr_TransladosID=? Hibernate: select trasladoho0_.TranshoraID as Transhor1_13_0_, trasladoho0_.thr_qtdHoras as thr_qtdH2_13_0_, trasladoho0_.thr_TransladosID as thr_Tran3_13_0_ from tbltranshora trasladoho0_ where trasladoho0_.thr_TransladosID=? Hibernate: select trasladoho0_.TranshoraID as Transhor1_13_0_, trasladoho0_.thr_qtdHoras as thr_qtdH2_13_0_, trasladoho0_.thr_TransladosID as thr_Tran3_13_0_ from tbltranshora trasladoho0_ where trasladoho0_.thr_TransladosID=? Hibernate: select trasladoho0_.TranshoraID as Transhor1_13_0_, trasladoho0_.thr_qtdHoras as thr_qtdH2_13_0_, trasladoho0_.thr_TransladosID as thr_Tran3_13_0_ from tbltranshora trasladoho0_ where trasladoho0_.thr_TransladosID=?

score 1 · Answer 1 · edited May 23 '17 at 12:06

You are facing the select n+1 problem.

The first query that you get retrieves the list you want but in order to instance the objects of entity Traslado, Hibernate invoke one select statement per object to find out if it has a TrasladoHora associated because the relationship is mapped in the TrasladoHora's table (I mean that TrasladoHora.traslado is the owner side of this bi-directional association).

Here is a answer commented by a lot of people with suggestions but the problem and some workaround is mention in Hibernate documentation in section Tuning fetch strategies

Edit: I also respond another question related to this problem, perhaps you find a more clear explanation there.

JPA 2 - INNER JOIN calls Query again

1 Answers1