Hibernate extra query after JOIN FETCH on the same entity

Question

I'm trying to query a large set of entity though Hibernate by using this query:

        "Select * " +
        " From Dossier as dossier" +
        " LEFT  JOIN FETCH dossier.candidat as candidat " +
        " LEFT  JOIN FETCH candidat.roles as role " +
        " LEFT  JOIN FETCH dossier.infoPerso as infoPerso " +
        " LEFT  JOIN FETCH dossier.etablissementOrigine as etablissementOrigine " +
        " LEFT  JOIN FETCH etablissementOrigine.filieres as filieres " +
        " LEFT OUTER JOIN FETCH etablissementOrigine.ville as villeOrigine " +
        " LEFT  JOIN FETCH dossier.etatDossier as etatDossier " +
        " LEFT OUTER JOIN FETCH infoPerso.fichierCNIRecto as fichierCNIRecto " +
        " LEFT OUTER JOIN FETCH fichierCNIRecto.type " +
        " LEFT OUTER JOIN FETCH infoPerso.fichierCNIVerso as fichierCNIVerso " +
        " LEFT OUTER JOIN FETCH fichierCNIVerso.type " +
        " LEFT OUTER JOIN FETCH infoPerso.fichierCV as fichierCV " +
        " LEFT OUTER JOIN FETCH fichierCV.type " +
        " LEFT OUTER JOIN FETCH infoPerso.fichierJAPD as fichierJAPD " +
        " LEFT OUTER JOIN FETCH fichierJAPD.type " +
        " LEFT OUTER JOIN FETCH infoPerso.fichierCNIVerso as fichierCNIVerso " +
        " LEFT OUTER JOIN FETCH fichierCNIVerso.type " +
        " LEFT OUTER JOIN FETCH infoPerso.situationFamilliale as situation "


            dossiers = getEntityManager()
                    .createQuery(sql, Dossier.class)
                    .getResultList();

I can see hibernate doing the first big native SQL query. But just after that, Hibernate generates 1 more query for each row to load DOssier, I don't know why, Dossier is already a part of fetchs elements...

 /* load org.ema.ecandidature.dossier.Dossier */ select
    dossier0_.id as id1_61_0_,
    dossier0_.version as version2_61_0_,
    dossier0_.valid as valid3_61_0_,
    dossier0_.validSecretariat as validSec4_61_0_,
    dossier0_.candidat_id as candidat7_17_0_,
    dossier0_.casParticulier as casParti1_17_0_,
    dossier0_.dateInscription as dateInsc2_17_0_,
    dossier0_.dateSoumission as dateSoum3_17_0_,
    dossier0_.entreprise_id as entrepri8_17_0_,
    dossier0_.etablissementOrigine_id as etabliss9_17_0_,
    dossier0_.etatDossier_id as etatDos10_17_0_,
    dossier0_.infoPaiement_id as infoPai11_17_0_,
    dossier0_.infoPerso_id as infoPer12_17_0_,
    dossier0_.listCursusAcademique_id as listCur13_17_0_,
    dossier0_.listDocumentsSupplementaires_id as listDoc14_17_0_,
    dossier0_.listExpEntreprise_id as listExp15_17_0_,
    dossier0_.listFormations_id as listFor16_17_0_,
    dossier0_.listLangues_id as listLan17_17_0_,
    dossier0_.listReferents_id as listRef18_17_0_,
    dossier0_.listSejourEtranger_id as listSej19_17_0_,
    dossier0_.motivationCentreInteret_id as motivat20_17_0_,
    dossier0_.secretariatChangeDate as secretar4_17_0_,
    dossier0_.secretariatChangeDateBackup as secretar5_17_0_,
    dossier0_.validationCommentaire as validati6_17_0_ 
from
    Dossier dossier0_ 
where
    dossier0_.candidat_id=?

Dossier.class:

@Entity
@BatchSize(size=100)
public class Dossier extends ValidableEntity {

    /** The Constant serialVersionUID. */
    private static final long serialVersionUID = 1L;

    /** The etablissement origine. */
    @SecretaryExport
    @ManyToOne( fetch=FetchType.LAZY)
    @JoinColumn()
    private Etablissement etablissementOrigine;

    /** The date inscription. */
    @SecretaryExport
    private Date dateInscription;

    /** The date soumission. */
    @SecretaryExport
    private Date dateSoumission;

    /** The date modification. */
    @SecretaryExport
    private Date secretariatChangeDate;

    /** The date de modification backup. */
    @SecretaryExport
    private Date secretariatChangeDateBackup;

    /** The cas particulier. */
    @SecretaryExport
    private Boolean casParticulier;

    /** The etat dossier. */
    @SecretaryExport
    @ManyToOne( fetch=FetchType.LAZY)
    @JoinColumn()
    private EtatDossier etatDossier;

    /** The candidat. */
    @SecretaryExport
    @OneToOne(fetch=FetchType.LAZY)
    private Candidat candidat;

    /** The info perso. */
    @SecretaryExport
    @Obligatoire
    @ObligatoireSecretariat
    @OneToOne(cascade = { CascadeType.ALL },fetch=FetchType.LAZY,orphanRemoval = true)
    private InfoPerso infoPerso;

    /** The list formations. */
    //@SecretaryExport
    @Obligatoire
    @ObligatoireSecretariat
    @OneToOne(cascade = { CascadeType.ALL },fetch=FetchType.LAZY,orphanRemoval = true)
    private ListFormations listFormations;

    /** The list cursus academique. */
    @SecretaryExport
    @Obligatoire
    @ObligatoireSecretariat
    @OneToOne(cascade = { CascadeType.ALL },fetch=FetchType.LAZY,orphanRemoval = true)
    private ListCursusAcademique listCursusAcademique;

    /** The motivation centre interet. */
    @SecretaryExport
    @Obligatoire
    @ObligatoireSecretariat
    @OneToOne(cascade = { CascadeType.ALL },fetch=FetchType.LAZY,orphanRemoval = true)
    private MotivationCentreInteret motivationCentreInteret;

    /** The entreprise. */
    @SecretaryExport
    @Obligatoire
    @ObligatoireSecretariat
    @OneToOne(cascade = { CascadeType.ALL },fetch=FetchType.LAZY,orphanRemoval = true)
    private Entreprise entreprise;

    /** The list langues. */
    @SecretaryExport
    @Obligatoire
    @ObligatoireSecretariat
    @OneToOne(cascade = { CascadeType.ALL },fetch=FetchType.LAZY,orphanRemoval = true)
    private ListLangues listLangues;

    /** The list sejour etranger. */
    @SecretaryExport
    @Obligatoire
    @ObligatoireSecretariat
    @OneToOne(cascade = { CascadeType.ALL },fetch=FetchType.LAZY,orphanRemoval = true)
    private ListSejourEtranger listSejourEtranger;

    /** The list exp entreprise. */
    @SecretaryExport
    @Obligatoire
    @ObligatoireSecretariat
    @OneToOne(cascade = { CascadeType.ALL },fetch=FetchType.LAZY,orphanRemoval = true)
    private ListExpEntreprise listExpEntreprise;

    /** The list referents. */
    @SecretaryExport
    @Obligatoire
    @ObligatoireSecretariat
    @OneToOne(cascade = { CascadeType.ALL},fetch=FetchType.LAZY,orphanRemoval = true)
    private ListReferents listReferents;

    /** The info paiement. */
    @SecretaryExport
    @Obligatoire
    @ObligatoireSecretariat
    @OneToOne(cascade = { CascadeType.ALL},fetch=FetchType.LAZY,orphanRemoval = true)
    private InfoPaiement infoPaiement;

    /** The avis jury. */
    @OneToMany(mappedBy= "dossier" , cascade = { CascadeType.ALL},fetch=FetchType.LAZY,orphanRemoval = true)
    private Set<AvisJury> avisJury = new HashSet<>();

    /** The list documents supplementaires. */
    @Obligatoire
    @ObligatoireSecretariat
    @OneToOne(cascade = { CascadeType.ALL },fetch=FetchType.LAZY,orphanRemoval = true)
    private ListDocumentsSupplementaires listDocumentsSupplementaires;

    /** The list fichier. */
    @OneToMany(mappedBy = "dossier", cascade = { CascadeType.ALL },fetch=FetchType.LAZY,orphanRemoval = true)
    private Set<Fichier> listFichier;

    /** The list avis examinateur. */
    @OneToMany(mappedBy= "dossier" , cascade = { CascadeType.ALL},fetch=FetchType.LAZY,orphanRemoval = true)
    private Set<AvisExaminateur> listAvisExaminateur;

    /** The list commentaire. */
    @OneToMany(cascade = { CascadeType.ALL},fetch=FetchType.LAZY,orphanRemoval = true)
    private Set<Commentaire> listCommentaire;

    /** The validation commentaire. */
    @Column(length = 500)
    @Pattern(regexp="^(.|\n|\r|\t)*$")//accepte tous les caractères et les retours lignes
    private String validationCommentaire;

}

What is wrong with that?

Answer 1

What is wrong with that?

You should not fetch entities unless you plan to modify them. So, if you only need a read-only view, then you should use a DTO projection instead.

Assuming that you really need to fetch the entire graph because you plan on doing modifications across it, then you have to use the following fetching strategy:

You can fetch as many child @OneToOne and @ManyToOne entity associations in your first query as well as AT MOST one @OneToMany or @ManyToMany.

For the remaining @OneToMany or @ManyToMany, you have to use secondary queries. But, you don't want to do it in a N+1 fashion, so you need to run the JPQL queries for those while passing the root entities that you fetched with the first query.

Keep in mind that if you reassemble the secondary collections onto the root entity, you would trigger some unneeded modifications on the root entity. So, if you want to pass the root entities to the web layer, then you should fetch those in read-only mode.

Again, if you don't need the entities, then you should just fetch a DTO projection and use a ResultTransfomer to transform the table-like projection into a DTO graph.

But just after that, Hibernate generates 1 more query for each row to load DOssier, I don't know why, Dossier is already a part of fetchs elements...

From these mappings, it's not clear why that query will be executed, but you can easily debug it, at datasource-proxy level, in Hibernate and see go up the stack-trace to see what has triggered it.

Answer 2

I guess that additional query is issued because you are using EAGER fetching on a *ToOne association which can happen accidently since it's the default fetch strategy for *ToOne associations. The DTO approach Vlad mentions is the way to go, but I understand that wiring all up will be a lot of work. To reduce the error prone boilerplate code, I can only recommend you take a look at what Blaze-Persistence Entity Views have to offer.

The library works on top of JPA and handles the DTO mappings efficiently for you. You'd only have to define the target structure as interfaces.

You can even use different fetch strategies(JOIN, SELECT and SUBSELECT) much like the strategies Hibernate offers for by-id loading, yet this works with any kind of query, not just by-id.

Answer 3

I have encountered same behavior. Lets say you have master class

@OneToMany
@Fetch(FetchMode.JOIN)
private List<Detail> details = new ArrayList<>();

with this config if i pull all details hibernates executes select join first and then selects every one of detail with their ids. The solution was to remove @Fetch(FetchMode.JOIN) in Master class.