In the below Hibernate generated query, the student_id and college_id_fk fields are selected twice, why is it so and what is the purpose? Can this be fixed.

2022-02-21 07:12:33.213 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([student_1_12_0_] : [INTEGER]) - [6]
Hibernate: 
    select
        students0_.college_id_fk as college_3_12_0_,
        students0_.student_id as student_1_12_0_,
        students0_.student_id as student_1_12_1_,
        students0_.college_id_fk as college_3_12_1_,
        students0_.student_name as student_2_12_1_ 
    from
        student students0_ 
    where
        students0_.college_id_fk=?
2022-02-21 07:12:33.214 TRACE 19824 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [INTEGER] - [1]

The calling code is as below -

  collegeRepo.findAll().forEach( c -> System.err.println("college wit students: "   c.getStudents() ) );

The above is spring data jpa provided method, so it has implementation from spring data jpa.

I have gone through similar Questions which suggested there could be some things wrong with entities like wrong mapping or wrong usage with @Id. So, I am pasting my entity relations here.

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.
(small edit 1 - following advice from Ken, i have commented eager loading for College.students entity, But "selecting twice" problem still exists. End of edit 1)

@Entity
public class College {

    @Id
    @GeneratedValue
    private int collegeId;

    private String collegeName;

    @OneToMany(targetEntity = Student.class, mappedBy = "college") //, cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    //as you can see students is loaded eagerly.
    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;

I searched on search engines for "Hibernate is selecting same column twice." but there are no useful result. So, SOF is the only means to understand or fix this issue.

as requested by @tgdavies: the spring boot version is 2.6.1 and the hibernate-core version is 5.6.1

CodePudding user response：

The select SQL that you mentioned is triggered by the eager loading of the students for a college. I briefly trace the source codes and it boils down to OneToManyPersister to generate the select clauses at here. I have no ideas why the author will generate the select clause which include some duplicated columns for this case, and I don't think there are anything that you can do to change this behaviour unless you use your own patched version of hibernate.

If you really don't want it to happen , my advise is not to use eager fetching for the college 's students. After all , it is a bad idea because eager fetching will introduce N 1 query issue. If you have 100 colleges , such SQL will repeat 100 times to load the students for these 100 colleges one by one after you load the colleges.

To avoid N 1 query issue and the duplicated columns in SQL , you can write a JPQL query to fetch join the college and its students together:

@Query("select distinct c from College c left join fetch c.students")
List<College> findAllCollegeWithStudents();

Or use @EntityGraph to do it declaratively if you like :

@EntityGraph(value = "students")
List<College> findAll(); 

CodePudding user response：

Hibernate before version 6.0 relied on aliases generated for every column usage. So if you have multiple associations or even just an inverse to-many association, you will see duplicate column selects because Hibernate before 6.0 fetches values based on aliases. Hibernate 6.0 switched to position based fetching and de-duplicates selections for the same column.