2

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
HopeKing
  • 3,317
  • 7
  • 39
  • 62
  • 2
    How does the actual SQL look like? Please add it to your question. – SpaceTrucker May 17 '17 at 11:18
  • You are having a `Bidirectioanl` mapping? – Abdullah Khan May 17 '17 at 11:41
  • Yes, Bidirectional mapping with appropriate mappedBy on one side. – HopeKing May 17 '17 at 11:42
  • 2
    Quite unclear with this little amount of code to look at. The multiple selects can also be the result of lazy fetches, or an improperly placed EAGER fetch. Please post the logged queries (or a subset) and the entity code; just the properties with the mapping annotations would be enough. – Gimby May 17 '17 at 12:15

3 Answers3

2

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.

xenteros
  • 15,586
  • 12
  • 56
  • 91
  • 1
    That is a nice suggestion. However, i have many use cases where i would like to use JPQL and am trying to understand the behaviour through my question above. Especially why cross joins are happening and multiple selects are happening. – HopeKing May 17 '17 at 11:09
  • I don't think that you really need JPQL queries. The engine for queries parsed from interface method signature is very strong. If you want the query to use joins, annotate properties with appropriate @Fetch – xenteros May 17 '17 at 11:24
1

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.

Community
  • 1
  • 1
Abdullah Khan
  • 12,010
  • 6
  • 65
  • 78
  • This query gives the exact same number of selects that my Query gives. Just to clarify, my query does work. It is quite inefficient in the number of Selects that eventually happen. – HopeKing May 17 '17 at 11:22
  • @CaptainHackSparrow Check my updated answer. The query is running exactly one select statement to fetch the `children`. You must be having an n+1/lazy fetch issue with your queries. – Abdullah Khan May 17 '17 at 12:34
0

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")
Maxim Tulupov
  • 1,621
  • 13
  • 8