'Spring Data JPA: What is wrong or how to correct this jpql query
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]
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|