5

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?

Improve this question

3 Answers 3

Reset to default
8

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I liked the idea of using ResultTransformer for getting DTO graph, however it would not be a viable option in case the requirement is to use Pagination option (i.e., offset, limit) to load a set of records from DB.
And, why fo you think uou can't use a ResultTransformer with pagination? The ResultTransformer works on any query ResultSet.
In case of @OneToMany example, Projection is going to join the parent, child table columns and parent rows will be repeated based on the child rows, in that case pagination will retrieve X (e.g., 10) records including duplicated parent records which will be transformed by ResultTransformer and final DTOs may be Y (e.g., 4) only. Other option is to retrieve all the records and process them using ResultTransformer and apply pagination on top of that which will hit the performance.
Your use case can b very easily address using window functions, as I explained in this article. Basically, you need to use dense_rank to choose all parents along with their children filtered by the number of parents you need to filter.
2

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.

Improve this answer

Comments

1

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.