HIbernate fetch join issuing additional sql statements

Question

Consider the following Parent class that has two ManyToOne references.

@Entity @Table(name = "PARENT") public class Parent { private static final long serialVersionUID = 3730163805206219313L; @Id @SequenceGenerator(name = "SEQ#PARENT", sequenceName = "SEQ#PARENT", allocationSize = 1) @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQ#PARENT") @Column(name = "ID") private long id; @ManyToOne(optional = false, fetch=FetchType.EAGER) @Fetch(FetchMode.JOIN) @JoinColumn(name="CHILD_1_ID", referencedColumnName="FK_COLUMN_NAME") private Child childInstance1; @ManyToOne(optional = false, fetch=FetchType.EAGER) @Fetch(FetchMode.JOIN) @JoinColumn(name="CHILD_2_ID", referencedColumnName="FK_COLUMN_NAME") private Child childInstance2; @Column(name = "USER_ID") private String userId; }

I am using the following hql query to load the Parent instance:

"from Parent p join fetch p.childInstance1 join fetch p.childInstance2 where p.userId = :userId"

This always results in hibernate issuing separate sql statements to load the childInstance1 rows even though it does the fetch join.

Any help in avoiding the extra sql statements is appreciated.

Pascal Thivent · Accepted Answer · 2010-09-21 23:03:13Z

I cannot reproduce. Given the following entities:

@Entity public class Parent3764122 { @Id @GeneratedValue private long id; @ManyToOne(optional = false/*, fetch=FetchType.EAGER*/) // EAGER by default //@Fetch(FetchMode.JOIN) // unnecessary @JoinColumn(name="CHILD_1_ID") private Child3764122 childInstance1; @ManyToOne(optional = false/*, fetch=FetchType.EAGER*/) // EAGER by default //@Fetch(FetchMode.JOIN) // unnecessary @JoinColumn(name="CHILD_2_ID") private Child3764122 childInstance2; @Column(name = "USER_ID") private String userId; ... }

and

@Entity public class Child3764122 { @Id @GeneratedValue private Long id; ... }

The following HQL query:

from Parent3764122 p join fetch p.childInstance1 join fetch p.childInstance2 where p.id = :id

Generates the following SQL:

select parent3764x0_.id as id129_0_, child37641x1_.id as id130_1_, child37641x2_.id as id130_2_, parent3764x0_.CHILD_1_ID as CHILD3_129_0_, parent3764x0_.CHILD_2_ID as CHILD4_129_0_, parent3764x0_.USER_ID as USER2_129_0_ from Parent3764122 parent3764x0_ inner join Child3764122 child37641x1_ on parent3764x0_.CHILD_1_ID=child37641x1_.id inner join Child3764122 child37641x2_ on parent3764x0_.CHILD_2_ID=child37641x2_.id where parent3764x0_.id=?

Works as expected with Hibernate 3.3.0.SP1, Hibernate Annotations 3.4.0.GA.

Actually i found the issue...seems like in your test you ignored the referencedColumnName which was causing the issue. I was referencing a non-primary key column. Once i modified the referencedColmumnName to be the primary key column of the child the extra sql statements went away.
@Sasi I didn't ignore it, I used the default :) (because I had no idea what FK_COLUMN_NAME was and because I like defaults). But if you specify it, it has indeed to be the PK column of the referenced table.

Collectives™ on Stack Overflow

HIbernate fetch join issuing additional sql statements

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related