So imagine the following scenario… we have two different entities, connected with a @OneToOne annotation-
@javax.persistence.Entity
@Table(name = "spawner")
@Access(value = AccessType.FIELD)
public class Spawner extends HibernateComponent {
@OneToOne
@JoinColumn(name = "location_id", referencedColumnName = "identity_id")
@Fetch(FetchMode.JOIN)
public Location location;
public Spawner() { }
@Entity
@Table(name = "location")
@Access(AccessType.FIELD)
public class Location extends HibernateComponent {
@ManyToOne
@JoinColumn(name = "chunk_id", referencedColumnName = "identity_id", updatable = false)
@Fetch(FetchMode.JOIN)
public Chunk chunk;
public Location() {}
}
When we select the Spawner with the following statement session.createQuery("select s from Spawner s where s in (...)"); hibernate generates a output looking like this.
Hibernate: select identity0_.id as id1_2_0_, location1_.identity_id as identity1_6_1_, identity0_.tag as tag2_2_0_, identity0_.typeID as typeid3_2_0_, location1_.chunk_id as chunk_id4_6_1_, location1_.x as x2_6_1_, location1_.y as y3_6_1_ from identity identity0_ inner join location location1_ on (location1_.identity_id=identity0_.id and (identity0_.id in (8326589099709645653 , 6287325594386187920 , 5407611297503526929 , 8523982519594665733 , 4028725686660451036 , 4729790674605130415 , 6901230747306707572 , 89683718271946391 , 663547951024983400 , 487871300966958647 , 2986281183600263327 , 7129934223659254130 , 7423461182852450838 , 6258927419235452915 , 4756503673250530721 , 7989242675644875262 , 7502164321920434546 , 9125201177335319448 , 5276750027792075277 , 2958385004616501743 , 8131213851438696494 , 5378786184839581257 , 4800722432645154706)))
Hibernate: select chunk0_.identity_id as identity1_0_0_, chunk0_.createdOn as createdo2_0_0_, chunk0_.x as x3_0_0_, chunk0_.y as y4_0_0_, inchunk1_.Chunk_identity_id as chunk_id1_1_1_, identity2_.id as inchunk_2_1_1_, identity2_.id as id1_2_2_, identity2_.tag as tag2_2_2_, identity2_.typeID as typeid3_2_2_ from chunk chunk0_ left outer join chunk_identity inchunk1_ on chunk0_.identity_id=inchunk1_.Chunk_identity_id left outer join identity identity2_ on inchunk1_.inChunk_id=identity2_.id where chunk0_.identity_id=?
Hibernate: select identity0_.id as id1_2_0_, identity0_.tag as tag2_2_0_, identity0_.typeID as typeid3_2_0_ from identity identity0_ where identity0_.id=?
Hibernate: select chunk0_.identity_id as identity1_0_0_, chunk0_.createdOn as createdo2_0_0_, chunk0_.x as x3_0_0_, chunk0_.y as y4_0_0_, inchunk1_.Chunk_identity_id as chunk_id1_1_1_, identity2_.id as inchunk_2_1_1_, identity2_.id as id1_2_2_, identity2_.tag as tag2_2_2_, identity2_.typeID as typeid3_2_2_ from chunk chunk0_ left outer join chunk_identity inchunk1_ on chunk0_.identity_id=inchunk1_.Chunk_identity_id left outer join identity identity2_ on inchunk1_.inChunk_id=identity2_.id where chunk0_.identity_id=?
Hibernate: select identity0_.id as id1_2_0_, identity0_.tag as tag2_2_0_, identity0_.typeID as typeid3_2_0_ from identity identity0_ where identity0_.id=?
Hibernate: select chunk0_.identity_id as identity1_0_0_, chunk0_.createdOn as createdo2_0_0_, chunk0_.x as x3_0_0_, chunk0_.y as y4_0_0_, inchunk1_.Chunk_identity_id as chunk_id1_1_1_, identity2_.id as inchunk_2_1_1_, identity2_.id as id1_2_2_, identity2_.tag as tag2_2_2_, identity2_.typeID as typeid3_2_2_ from chunk chunk0_ left outer join chunk_identity inchunk1_ on chunk0_.identity_id=inchunk1_.Chunk_identity_id left outer join identity identity2_ on inchunk1_.inChunk_id=identity2_.id where chunk0_.identity_id=?
Hibernate: select identity0_.id as id1_2_0_, identity0_.tag as tag2_2_0_, identity0_.typeID as typeid3_2_0_ from identity identity0_ where identity0_.id=?
Hibernate: select chunk0_.identity_id as identity1_0_0_, chunk0_.createdOn as createdo2_0_0_, chunk0_.x as x3_0_0_, chunk0_.y as y4_0_0_, inchunk1_.Chunk_identity_id as chunk_id1_1_1_, identity2_.id as inchunk_2_1_1_, identity2_.id as id1_2_2_, identity2_.tag as tag2_2_2_, identity2_.typeID as typeid3_2_2_ from chunk chunk0_ left outer join chunk_identity inchunk1_ on chunk0_.identity_id=inchunk1_.Chunk_identity_id left outer join identity identity2_ on inchunk1_.inChunk_id=identity2_.id where chunk0_.identity_id=?
Hibernate: select identity0_.id as id1_2_0_, identity0_.tag as tag2_2_0_, identity0_.typeID as typeid3_2_0_ from identity identity0_ where identity0_.id=?
Hibernate: select chunk0_.identity_id as identity1_0_0_, chunk0_.createdOn as createdo2_0_0_, chunk0_.x as x3_0_0_, chunk0_.y as y4_0_0_, inchunk1_.Chunk_identity_id as chunk_id1_1_1_, identity2_.id as inchunk_2_1_1_, identity2_.id as id1_2_2_, identity2_.tag as tag2_2_2_, identity2_.typeID as typeid3_2_2_ from chunk chunk0_ left outer join chunk_identity inchunk1_ on chunk0_.identity_id=inchunk1_.Chunk_identity_id left outer join identity identity2_ on inchunk1_.inChunk_id=identity2_.id where chunk0_.identity_id=?
Hibernate: select identity0_.id as id1_2_0_, identity0_.tag as tag2_2_0_, identity0_.typeID as typeid3_2_0_ from identity identity0_ where identity0_.id=?
Hibernate: select chunk0_.identity_id as identity1_0_0_, chunk0_.createdOn as createdo2_0_0_, chunk0_.x as x3_0_0_, chunk0_.y as y4_0_0_, inchunk1_.Chunk_identity_id as chunk_id1_1_1_, identity2_.id as inchunk_2_1_1_, identity2_.id as id1_2_2_, identity2_.tag as tag2_2_2_, identity2_.typeID as typeid3_2_2_ from chunk chunk0_ left outer join chunk_identity inchunk1_ on chunk0_.identity_id=inchunk1_.Chunk_identity_id left outer join identity identity2_ on inchunk1_.inChunk_id=identity2_.id where chunk0_.identity_id=?
Hibernate: select identity0_.id as id1_2_0_, identity0_.tag as tag2_2_0_, identity0_.typeID as typeid3_2_0_ from identity identity0_ where identity0_.id=?
Hibernate: select chunk0_.identity_id as identity1_0_0_, chunk0_.createdOn as createdo2_0_0_, chunk0_.x as x3_0_0_, chunk0_.y as y4_0_0_, inchunk1_.Chunk_identity_id as chunk_id1_1_1_, identity2_.id as inchunk_2_1_1_, identity2_.id as id1_2_2_, identity2_.tag as tag2_2_2_, identity2_.typeID as typeid3_2_2_ from chunk chunk0_ left outer join chunk_identity inchunk1_ on chunk0_.identity_id=inchunk1_.Chunk_identity_id left outer join identity identity2_ on inchunk1_.inChunk_id=identity2_.id where chunk0_.identity_id=?
Hibernate: select identity0_.id as id1_2_0_, identity0_.tag as tag2_2_0_, identity0_.typeID as typeid3_2_0_ from identity identity0_ where identity0_.id=?
Hibernate: select chunk0_.identity_id as identity1_0_0_, chunk0_.createdOn as createdo2_0_0_, chunk0_.x as x3_0_0_, chunk0_.y as y4_0_0_, inchunk1_.Chunk_identity_id as chunk_id1_1_1_, identity2_.id as inchunk_2_1_1_, identity2_.id as id1_2_2_, identity2_.tag as tag2_2_2_, identity2_.typeID as typeid3_2_2_ from chunk chunk0_ left outer join chunk_identity inchunk1_ on chunk0_.identity_id=inchunk1_.Chunk_identity_id left outer join identity identity2_ on inchunk1_.inChunk_id=identity2_.id where chunk0_.identity_id=?
Hibernate: select identity0_.id as id1_2_0_, identity0_.tag as tag2_2_0_, identity0_.typeID as typeid3_2_0_ from identity identity0_ where identity0_.id=?
As you can see hibernate runs into the n+1 issue and executes one “select chunk query” for each loaded location…
How do we prevent this and force them to batch/join it into fewer queries being executed ? We still want that “location” reference aswell as the “chunk” reference being loaded eager… so we dont wanna ignore them when loading the “spawners”, instead our goal is to batch the queries getting executed to select the “location” and “chunk” inside the location.
Any ideas on this ? How do we achieve this in hibernate ?