Why does hibernate manage to use JOIN when searching on id but not on another field?

Question

I'm currently using Spring Data with repositories to fetch my data. When using the default :

myRepo.findById(id)

a single query using joins is generated :

Hibernate: select questionna0_.id as id1_6_0_, questionna0_.action as action2_6_0_, questionna0_.first_question_id as first_qu3_6_0_, answers1_.questionnaire_id as question5_0_1_, answers1_.id as id1_0_1_, answers1_.id as id1_0_2_, answers1_.order as order2_0_2_, answers1_.question_id as question3_0_2_, answers1_.value as value4_0_2_, validation2_.answer_id as answer_i2_1_3_, validation2_.id as id1_1_3_, validation2_.id as id1_1_4_, validation2_.answer_id as answer_i2_1_4_, validation2_.operator as operator3_1_4_, validation2_.value as value4_1_4_, questions3_.questionnaire_id as question7_5_5_, questions3_.id as id1_5_5_, questions3_.id as id1_5_6_, questions3_.answer_type as answer_t2_5_6_, questions3_.optional as optional3_5_6_, questions3_.question_key as question4_5_6_, questions3_.question_label as question5_5_6_, questions3_.question_type as question6_5_6_, answers4_.question_id as question3_0_7_, answers4_.id as id1_0_7_, answers4_.id as id1_0_8_, answers4_.order as order2_0_8_, answers4_.question_id as question3_0_8_, answers4_.value as value4_0_8_, branches5_.question_id as question3_2_9_, branches5_.id as id1_2_9_, branches5_.id as id1_2_10_, branches5_.question_id_target as question2_2_10_, conditions6_.branch_id as branch_i5_3_11_, conditions6_.id as id1_3_11_, conditions6_.id as id1_3_12_, conditions6_.operator as operator2_3_12_, conditions6_.question_id as question3_3_12_, conditions6_.value as value4_3_12_ from questionnaire questionna0_ left outer join answer answers1_ on questionna0_.id=answers1_.questionnaire_id left outer join answer_validation validation2_ on answers1_.id=validation2_.answer_id left outer join question questions3_ on questionna0_.id=questions3_.questionnaire_id left outer join answer answers4_ on questions3_.id=answers4_.question_id left outer join branch branches5_ on questions3_.id=branches5_.question_id left outer join branch_condition conditions6_ on branches5_.id=conditions6_.branch_id where questionna0_.id=?

but when using the same repository with a query on another field to do pretty much the same thing :

myRepo.findByAction(action)

I get multiple requests (n+1 problem...)

Hibernate: select questionna0_.id as id1_6_, questionna0_.action as action2_6_, questionna0_.first_question_id as first_qu3_6_ from questionnaire questionna0_ where questionna0_.action=?
Hibernate: select questions0_.questionnaire_id as question7_5_0_, questions0_.id as id1_5_0_, questions0_.id as id1_5_1_, questions0_.answer_type as answer_t2_5_1_, questions0_.optional as optional3_5_1_, questions0_.question_key as question4_5_1_, questions0_.question_label as question5_5_1_, questions0_.question_type as question6_5_1_ from question questions0_ where questions0_.questionnaire_id=?
Hibernate: select answers0_.question_id as question3_0_0_, answers0_.id as id1_0_0_, answers0_.id as id1_0_1_, answers0_.order as order2_0_1_, answers0_.question_id as question3_0_1_, answers0_.value as value4_0_1_ from answer answers0_ where answers0_.question_id=?
Hibernate: select validation0_.answer_id as answer_i2_1_0_, validation0_.id as id1_1_0_, validation0_.id as id1_1_1_, validation0_.answer_id as answer_i2_1_1_, validation0_.operator as operator3_1_1_, validation0_.value as value4_1_1_ from answer_validation validation0_ where validation0_.answer_id=?
Hibernate: select branches0_.question_id as question3_2_0_, branches0_.id as id1_2_0_, branches0_.id as id1_2_1_, branches0_.question_id_target as question2_2_1_ from branch branches0_ where branches0_.question_id=?
Hibernate: select conditions0_.branch_id as branch_i5_3_0_, conditions0_.id as id1_3_0_, conditions0_.id as id1_3_1_, conditions0_.operator as operator2_3_1_, conditions0_.question_id as question3_3_1_, conditions0_.value as value4_3_1_ from branch_condition conditions0_ where conditions0_.branch_id=?
Hibernate: select answers0_.question_id as question3_0_0_, answers0_.id as id1_0_0_, answers0_.id as id1_0_1_, answers0_.order as order2_0_1_, answers0_.question_id as question3_0_1_, answers0_.value as value4_0_1_ from answer answers0_ where answers0_.question_id=?
Hibernate: select validation0_.answer_id as answer_i2_1_0_, validation0_.id as id1_1_0_, validation0_.id as id1_1_1_, validation0_.answer_id as answer_i2_1_1_, validation0_.operator as operator3_1_1_, validation0_.value as value4_1_1_ from answer_validation validation0_ where validation0_.answer_id=?
Hibernate: select branches0_.question_id as question3_2_0_, branches0_.id as id1_2_0_, branches0_.id as id1_2_1_, branches0_.question_id_target as question2_2_1_ from branch branches0_ where branches0_.question_id=?
Hibernate: select answers0_.questionnaire_id as question5_0_0_, answers0_.id as id1_0_0_, answers0_.id as id1_0_1_, answers0_.order as order2_0_1_, answers0_.question_id as question3_0_1_, answers0_.value as value4_0_1_ from answer answers0_ where answers0_.questionnaire_id=?

Is the problem the fact that id is declared as @Id and not my action column ?

I can't seem to find documentation on the subject.

Here is my current entities :

@Entity
@Table(name = "questionnaire")
data class QuestionnaireEntity(
    @Id
    @GeneratedValue(generator = "UUID")
    @GenericGenerator(name = "UUID", strategy = "org.hibernate.id.UUIDGenerator")
    val id: UUID?,
    val action: String,
    val firstQuestionId: UUID?,
    @OneToMany(fetch = FetchType.EAGER)
    @JoinColumn(name = "questionnaireId")
    val questions: Set<QuestionEntity>,
    @OneToMany(fetch = FetchType.EAGER)
    @JoinColumn(name = "questionnaireId")
    val answers: Set<AnswerEntity>
)

@Entity
@Table(name = "question")
data class QuestionEntity(
    @Id
    @GeneratedValue(generator = "UUID")
    @GenericGenerator(name = "UUID", strategy = "org.hibernate.id.UUIDGenerator")
    val id: UUID?,
    val optional: Boolean,
    val questionLabel: String?,
    val questionKey: String?,
    @Enumerated(EnumType.STRING)
    val questionType: QuestionType,
    @Enumerated(EnumType.STRING)
    val answerType: AnswerType,
    @OneToMany(fetch = FetchType.EAGER)
    @JoinColumn(name = "questionId")
    val answers: Set<AnswerEntity>,
    @OneToMany(fetch = FetchType.EAGER)
    @JoinColumn(name = "questionId")
    val branches: Set<BranchEntity>
)

@Entity
@Table(name = "answer")
@TypeDefs(
    TypeDef(name = "jsonb", typeClass = JsonBinaryType::class)
)
data class AnswerEntity(
    @Id
    @GeneratedValue(generator = "UUID")
    @GenericGenerator(name = "UUID", strategy = "org.hibernate.id.UUIDGenerator")
    val id: UUID?,
    val questionId: UUID,
    @Type(type = "jsonb")
    val value: Map<String, Any>?,
    val order: Int,
    @OneToMany(fetch = FetchType.EAGER)
    @JoinColumn(name = "answerId")
    val validations: Set<AnswerValidationEntity>
)

Edited question. I skipped some of the entities since I can reproduce the problem with only these 3 — Pierre Fraisse, Feb 16 '21 at 14:02

score 2 · Answer 1 · answered Feb 16 '21 at 15:05

The reason why these methods behave differently is because they are implemented very differently.

findById is implemented using EntityManager.find. This is possible because it is implemented once in SimpleJpaRepository which implements the methods of CrudRepository. EntityManager.find in turn honours your lazy/eager configuration on the entities.

That configuration is ignored when a query of some form is used, which is exactly what happens with other findBy... methods you add to your repository. You may annotate them with @EntityGraph to proved an entity graph specifying the fetch strategy you want it to use.

Here is an example how to use it: https://www.baeldung.com/spring-data-jpa-named-entity-graphs

SternK · Answer 2 · 2021-02-16T16:52:23.947

Your problem related to this part of documentation:

If you forget to JOIN FETCH all EAGER associations, Hibernate is going to issue a secondary select for each and every one of those which, in turn, can lead to N+1 query issues. For this reason, you should prefer LAZY associations.

and further:

The Hibernate recommendation is to statically mark all associations lazy and to use dynamic fetching strategies for eagerness.

This is unfortunately at odds with the JPA specification which defines that all one-to-one and many-to-one associations should be eagerly fetched by default.

Hibernate, as a JPA provider, honors that default.

So, to avoid N+1 query problem, you should instead of:

myRepo.findByAction(action)

use query like this:

select q from QuestionnaireEntity q
join fetch q.questions
where q.action = :action

But please note that you can fetch at most one collection using JOIN FETCH. See this.

Look at the implementation of the `myRepo.findById(id)` method. This is not a query, under the hood it calls `entityManager.find` — SternK, Feb 16 '21 at 14:06

v.ladynev · Answer 3 · 2021-02-16T15:34:27.707

Better to use these design rules to not have such problems:

Don't use @OneToMany(fetch = FetchType.EAGER), use @OneToMany(fetch = FetchType.LAZY)
Don't fix fetching problems using Set in place of List for one-to-many association.
Don't use unidirectional associations like @OneToMany(fetch = FetchType.EAGER) @JoinColumn(name = "questionnaireId"), use bidirectional instead (with @JoinColumn on the @ManyToOne side and mappedBy). It is free and let you add elements and search them more effective.
Don't use @ManyToOne(fetch = FetchType.EAGER), use @ManyToOne(fetch = FetchType.LAZY)

what is @JoinColumn and how it is used in Hibernate

Why does hibernate manage to use JOIN when searching on id but not on another field?

3 Answers3