CODEWITHSUNDEEP
javaspringjpaspring-data-jpa
HopeKing
HopeKing

Reputation: 3503

Inefficient JPA query using compound Where clause

I have a tables with One-Many Relationships as follows

City->School->Teacher->Children

and my JPQL for retrieving children from a city is as below

@Query("Select c  from Children c where c.teacher.school.city=:city")
Set<Children> findChildrenFromCity(@Param("city") City city);

This reference here about Where clause says that

"Compound path expressions make the where clause extremely powerful."

However, upon observing the logs I realise that the above query is doing strange things like

  1. Generate multiple Selects instead of one Select

  2. Some cross joins can be seen in the logs

I am trying to understand if I am defining my query correctly and if the compound Where is indeed so powerful, why is my query so inefficient.

Upvotes: 2

Views: 507

Answers (3)

Abdullah Mohammad Motiullah
Abdullah Mohammad Motiullah

Reputation: 12552

Try this

@Query("Select c from City city join city.schools s join s.teachers t join t.childrens c where city = :city")
Set<Children> findChildrenFromCity(@Param("city") City city);

This query is running exactly one Select query to fetch the Children entities. Check the below mentioned logs.

HIBERNATE: SELECT childrens3_.id AS id1_0_, childrens3_.date_created AS date_cre2_0_, childrens3_.date_updated AS date_upd3_0_, childrens3_.NAME AS name4_0_, childrens3_.teacher_id AS teacher_5_0_ FROM city city0_ INNER JOIN school schools1_ ON city0_.id = schools1_.city_id INNER JOIN teacher teachers2_ ON schools1_.id = teachers2_.school_id INNER JOIN children childrens3_ ON teachers2_.id = childrens3_.teacher_id WHERE city0_.id = ?

Now what you have is an n+1 issue. To fix such issue you can use join fetch instead of simple joins.

Upvotes: 1

Maxim Tulupov
Maxim Tulupov

Reputation: 1786

If you want use Query annotation try this approach

@Query("Select c  from Children c join fetch c.teacher t join fetch t.school s join fetch s.city ct where ct.id = :id")

Upvotes: 0

xenteros
xenteros

Reputation: 15842

You can use the following method:

Set<Children> findAllByTeacherSchoolCity(String city);

assuming, that your class Children has field Teacher teacher, Teacher has School school and School has String city.

In case there are differences, please ask in comments for clarification.

Upvotes: 2

Related Questions