Spring Data JPA: What is wrong or how to correct this jpql query

Question

The entity relationships are - A student belongs to one college And a college can have multiple students. So there is ManyToOne relationship between Student --> College And a OneToMany relationship between College --> Student.

The entities are as below.

@Entity
public class College {

    @Id
    @GeneratedValue
    private int collegeId;

    private String collegeName;

    @OneToMany(targetEntity = Student.class, mappedBy = "college") 
    private List<Student> students;

and

@Entity
public class Student {

    @Id
    @GeneratedValue
    private int studentId;

    private String studentName;

    @ManyToOne
    @JoinColumn(name = "collegeId_fk")
    private College college;

1) I am using the below jpql query in spring data jpa repository.

@Query("SELECT c FROM College c LEFT JOIN FETCH c.students where c.collegeId IN (2)")
public List<College> findByCollegeIdsJPQL();

I am expecting that a list with single college entity to be returned as collegeId is primary key for the College Entity and I am giving only one id for the IN. But What I am getting is a list of colleges all with same primary key (collegeId=2). The size of returned list is equal to the number students in the college.

System.err.println("collegeRepo.findByCollegeIdsJPQL().size(): " + collegeRepo.findByCollegeIdsJPQL().size()); 
//output: collegeRepo.findByCollegeIdsJPQL().size(): 6

And for

collegeRepo.findByCollegeIdsJPQL().forEach( System.err::println );

output:
College [collegeId=2, collegeName=college2]
College [collegeId=2, collegeName=college2]
College [collegeId=2, collegeName=college2]
College [collegeId=2, collegeName=college2]
College [collegeId=2, collegeName=college2]
College [collegeId=2, collegeName=college2]

2) Another issue i noticed,
calling collegeRepo.findByCollegeIdsJPQL() for second time is causing another sql query ie DB hit. Can not this be served from session(first level) cache. I have annotated the calling method with

  @Transactional
  public void run(String... args) throws Exception {

output log -

2022-02-20 15:10:59.140[0;39m [32mTRACE[0;39m [35m18416[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.h.type.descriptor.sql.BasicExtractor  [0;39m [2m:[0;39m extracted value ([student_1_12_0__] : [INTEGER]) - [6]
collegeRepo.findByCollegeIdsJPQL().size(): 6
[2m2022-02-20 15:10:59.153[0;39m [32mTRACE[0;39m [35m18416[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.s.t.i.TransactionInterceptor          [0;39m [2m:[0;39m No need to create transaction for [org.springframework.data.jpa.repository.support.SimpleJpaRepository.findByCollegeIdsJPQL]: This method is not transactional.
Hibernate: 
    select
        college0_.college_id as college_1_1_0_,
        students1_.student_id as student_1_12_1_,
        college0_.college_name as college_2_1_0_,
        students1_.college_id_fk as college_3_12_1_,
        students1_.student_name as student_2_12_1_,
        students1_.college_id_fk as college_3_12_0__,
        students1_.student_id as student_1_12_0__ 
    from
        college college0_ 
    left outer join
        student students1_ 
            on college0_.college_id=students1_.college_id_fk 
    where
        college0_.college_id in (
            2
        )
[2m2022-02-20 15:10:59.245[0;39m [32m INFO[0;39m [35m18416[0;39m [2m---[0;39m [2m[on(3)-127.0.0.1][0;39m [36mo.a.c.c.C.[Tomcat].[localhost].[/]      [0;39m [2m:[0;39m Initializing Spring DispatcherServlet 'dispatcherServlet'
[2m2022-02-20 15:10:59.246[0;39m [32m INFO[0;39m [35m18416[0;39m [2m---[0;39m [2m[on(3)-127.0.0.1][0;39m [36mo.s.web.servlet.DispatcherServlet       [0;39m [2m:[0;39m Initializing Servlet 'dispatcherServlet'
[2m2022-02-20 15:10:59.247[0;39m [32m INFO[0;39m [35m18416[0;39m [2m---[0;39m [2m[on(3)-127.0.0.1][0;39m [36mo.s.web.servlet.DispatcherServlet       [0;39m [2m:[0;39m Completed initialization in 1 ms
[2m2022-02-20 15:10:59.403[0;39m [32mTRACE[0;39m [35m18416[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.h.type.descriptor.sql.BasicExtractor  [0;39m [2m:[0;39m extracted value ([college_1_1_0_] : [INTEGER]) - [2]
[2m2022-02-20 15:10:59.404[0;39m [32mTRACE[0;39m [35m18416[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.h.type.descriptor.sql.BasicExtractor  [0;39m [2m:[0;39m extracted value ([student_1_12_1_] : [INTEGER]) - [1]
[2m2022-02-20 15:10:59.404[0;39m [32mTRACE[0;39m [35m18416[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.h.type.descriptor.sql.BasicExtractor  [0;39m [2m:[0;39m extracted value ([college_3_12_0__] : [INTEGER]) - [2]
[2m2022-02-20 15:10:59.404[0;39m [32mTRACE[0;39m [35m18416[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.h.type.descriptor.sql.BasicExtractor  [0;39m [2m:[0;39m extracted value ([college_1_1_0_] : [INTEGER]) - [2]
[2m2022-02-20 15:10:59.404[0;39m [32mTRACE[0;39m [35m18416[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.h.type.descriptor.sql.BasicExtractor  [0;39m [2m:[0;39m extracted value ([student_1_12_1_] : [INTEGER]) - [2]
[2m2022-02-20 15:10:59.404[0;39m [32mTRACE[0;39m [35m18416[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.h.type.descriptor.sql.BasicExtractor  [0;39m [2m:[0;39m extracted value ([college_3_12_0__] : [INTEGER]) - [2]
[2m2022-02-20 15:10:59.404[0;39m [32mTRACE[0;39m [35m18416[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.h.type.descriptor.sql.BasicExtractor  [0;39m [2m:[0;39m extracted value ([college_1_1_0_] : [INTEGER]) - [2]
[2m2022-02-20 15:10:59.405[0;39m [32mTRACE[0;39m [35m18416[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.h.type.descriptor.sql.BasicExtractor  [0;39m [2m:[0;39m extracted value ([student_1_12_1_] : [INTEGER]) - [3]
[2m2022-02-20 15:10:59.405[0;39m [32mTRACE[0;39m [35m18416[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.h.type.descriptor.sql.BasicExtractor  [0;39m [2m:[0;39m extracted value ([college_3_12_0__] : [INTEGER]) - [2]
[2m2022-02-20 15:10:59.405[0;39m [32mTRACE[0;39m [35m18416[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.h.type.descriptor.sql.BasicExtractor  [0;39m [2m:[0;39m extracted value ([college_1_1_0_] : [INTEGER]) - [2]
[2m2022-02-20 15:10:59.406[0;39m [32mTRACE[0;39m [35m18416[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.h.type.descriptor.sql.BasicExtractor  [0;39m [2m:[0;39m extracted value ([student_1_12_1_] : [INTEGER]) - [4]
[2m2022-02-20 15:10:59.406[0;39m [32mTRACE[0;39m [35m18416[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.h.type.descriptor.sql.BasicExtractor  [0;39m [2m:[0;39m extracted value ([college_3_12_0__] : [INTEGER]) - [2]
[2m2022-02-20 15:10:59.406[0;39m [32mTRACE[0;39m [35m18416[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.h.type.descriptor.sql.BasicExtractor  [0;39m [2m:[0;39m extracted value ([college_1_1_0_] : [INTEGER]) - [2]
[2m2022-02-20 15:10:59.406[0;39m [32mTRACE[0;39m [35m18416[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.h.type.descriptor.sql.BasicExtractor  [0;39m [2m:[0;39m extracted value ([student_1_12_1_] : [INTEGER]) - [5]
[2m2022-02-20 15:10:59.406[0;39m [32mTRACE[0;39m [35m18416[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.h.type.descriptor.sql.BasicExtractor  [0;39m [2m:[0;39m extracted value ([college_3_12_0__] : [INTEGER]) - [2]
[2m2022-02-20 15:10:59.406[0;39m [32mTRACE[0;39m [35m18416[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.h.type.descriptor.sql.BasicExtractor  [0;39m [2m:[0;39m extracted value ([college_1_1_0_] : [INTEGER]) - [2]
[2m2022-02-20 15:10:59.406[0;39m [32mTRACE[0;39m [35m18416[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.h.type.descriptor.sql.BasicExtractor  [0;39m [2m:[0;39m extracted value ([student_1_12_1_] : [INTEGER]) - [6]
[2m2022-02-20 15:10:59.406[0;39m [32mTRACE[0;39m [35m18416[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.h.type.descriptor.sql.BasicExtractor  [0;39m [2m:[0;39m extracted value ([college_3_12_0__] : [INTEGER]) - [2]
College [collegeId=2, collegeName=college2]
College [collegeId=2, collegeName=college2]
College [collegeId=2, collegeName=college2]
College [collegeId=2, collegeName=college2]
College [collegeId=2, collegeName=college2]
College [collegeId=2, collegeName=college2]
[2m2022-02-20 15:10:59.407[0;39m [32mTRACE[0;39m [35m18416[0;39m [2m---[0;39m [2m[           main][0;39m [36mo.s.t.i.TransactionInterceptor          [0;39m [2m:[0;39m Completing transaction for [com.demo.MyRunner.run]

In my experience, using `@ManyToOne` is bad practice, maybe only persisting `collegeId` in `Student.class` and remove field `students` under `College.class` would be a better choice — 袁文涛, Feb 20 '22 at 10:22

score 1 · Accepted Answer · answered Feb 20 '22 at 11:40

1

(1) Because LEFT JOIN will return the combination of a college and its student. So if a college has N students , N records will be returned. You have to add DISTINCT to remove the duplication :

@Query("SELECT distinct c FROM College c LEFT JOIN FETCH c.students where c.collegeId IN (2)")
public List<College> findByCollegeIdsJPQL();

However, it will cause the generated SQL has distinct keyword which may has performance impact. So since Hibernate 5.2, they provides a query hint called hibernate.query.passDistinctThrough which can configure not to add distinct keyword in the generated SQL and Hibernate will help to remove the duplication instead :

@Query("SELECT distinct c FROM College c LEFT JOIN FETCH c.students where c.collegeId IN (2)")
@QueryHints(@QueryHint(name = org.hibernate.jpa.QueryHints.HINT_PASS_DISTINCT_THROUGH, value = "false"))
public List<College> findByCollegeIdsJPQL();

For details , please see this blog post.

(2) It is normal. You have to configure 2nd level cache and query cache to prevent another DB hit. 1st level cache mainly works when getting an entity by ID using EntityManager#get() in the same transaction. It does not work when using JPQL query.

answered Feb 20 '22 at 11:40

Ken Chan

84,777
26
143
172

right, 1) so i have use the jpql with distinct. ++1. And 2) when i do `collegeRepo.getById(2)` i can see only query is executed as you explained. – samshers Feb 20 '22 at 13:58
Eclipse suggested these - `javax.persistence.QueryHint` and `org.springframework.data.jpa.repository.QueryHints`;. why one is from jpa and the other from spring – samshers Feb 20 '22 at 14:41
because spring-data-jpa is built on top of jpa – Ken Chan Feb 20 '22 at 15:20
what if i change from List to Set = `public Set findByCollegeIdsJPQL(); `, i see the duplicates are not coming, but not sure if this is recommended way? and if there will be performance impact if there are millions of record?. – samshers Feb 21 '22 at 01:36
could you upvote the Q if you like it as it seems to be very much valid and your answer addresses it. I see this could be quite a normal situation that every spring data jpa user should be aware of. – samshers Feb 21 '22 at 01:38
sure. It depends on if the generated query will has distinct on it or not . i did not try it and hence don't know – Ken Chan Feb 21 '22 at 03:31
if you got time - would you mind helping with this [Criteria API and JPQL API with GROUP BY and CONCAT?](https://stackoverflow.com/questions/73129333/criteria-api-and-jpql-api-with-group-by-and-concat/73163002#73163002) – samshers Jul 30 '22 at 17:08

Spring Data JPA: What is wrong or how to correct this jpql query

1 Answers1