Java – hibernate extra query after join fetch on the same entity
I try to query a large number of entities by using this query, although Hibernate:
"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 that hibernate is doing the first large native SQL query But after that, hibernate generated more than one query for each row to load dossier. I don't know why. Dossier is already a part of the fetches element
/* 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 },orphanRemoval = true)
private ListFormations listFormations;
/** The list cursus academique. */
@SecretaryExport
@Obligatoire
@ObligatoireSecretariat
@OneToOne(cascade = { CascadeType.ALL },orphanRemoval = true)
private ListCursusAcademique listCursusAcademique;
/** The motivation centre interet. */
@SecretaryExport
@Obligatoire
@ObligatoireSecretariat
@OneToOne(cascade = { CascadeType.ALL },orphanRemoval = true)
private MotivationCentreInteret motivationCentreInteret;
/** The entreprise. */
@SecretaryExport
@Obligatoire
@ObligatoireSecretariat
@OneToOne(cascade = { CascadeType.ALL },orphanRemoval = true)
private Entreprise entreprise;
/** The list langues. */
@SecretaryExport
@Obligatoire
@ObligatoireSecretariat
@OneToOne(cascade = { CascadeType.ALL },orphanRemoval = true)
private ListLangues listLangues;
/** The list sejour etranger. */
@SecretaryExport
@Obligatoire
@ObligatoireSecretariat
@OneToOne(cascade = { CascadeType.ALL },orphanRemoval = true)
private ListSejourEtranger listSejourEtranger;
/** The list exp entreprise. */
@SecretaryExport
@Obligatoire
@ObligatoireSecretariat
@OneToOne(cascade = { CascadeType.ALL },orphanRemoval = true)
private ListExpEntreprise listExpEntreprise;
/** The list referents. */
@SecretaryExport
@Obligatoire
@ObligatoireSecretariat
@OneToOne(cascade = { CascadeType.ALL},orphanRemoval = true)
private ListReferents listReferents;
/** The info paiement. */
@SecretaryExport
@Obligatoire
@ObligatoireSecretariat
@OneToOne(cascade = { CascadeType.ALL},orphanRemoval = true)
private InfoPaiement infoPaiement;
/** The avis jury. */
@OneToMany(mappedBy= "dossier",cascade = { CascadeType.ALL},orphanRemoval = true)
private Set<AvisJury> avisJury = new HashSet<>();
/** The list documents supplementaires. */
@Obligatoire
@ObligatoireSecretariat
@OneToOne(cascade = { CascadeType.ALL },orphanRemoval = true)
private ListDocumentsSupplementaires listDocumentsSupplementaires;
/** The list fichier. */
@OneToMany(mappedBy = "dossier",cascade = { CascadeType.ALL },orphanRemoval = true)
private Set<Fichier> listFichier;
/** The list avis examinateur. */
@OneToMany(mappedBy= "dossier",orphanRemoval = true)
private Set<AvisExaminateur> listAvisExaminateur;
/** The list commentaire. */
@OneToMany(cascade = { CascadeType.ALL},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's the problem?
Solution
As I explained in my book high performance Java persistence, you should never get entities unless you intend to modify them Therefore, if you only need a read-only view, you should use dto project instead
Assuming that you do need to get the entire drawing because you plan to modify it, you must use the following extraction strategy:
You can get as many sub @ onetoone and @ manytoone entity associations as possible in the first query, or in atott @ onetomany or @ manytomany
For the remaining @ onetomany or @ manytomany, you must use auxiliary query However, you don't want to do this in N + 1 fashion, so you need to run a jpql query when passing the root entity obtained using the first query
Remember that regrouping a secondary set onto the root entity will trigger some unnecessary modifications on the root entity Therefore, if you want to pass root entities to the web tier, you should get them in read-only mode
Similarly, if you do not need entities, you should only get dto projections and use resulttransfomer to convert tabular projections into dto graphics, as described in this article
From these mappings, it is not clear why the query should be executed, but you can easily debug it in Hibernate at the datasource proxy level and view the stack trace to see what triggered it