0

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 ?

genaray
  • 1,080
  • 1
  • 9
  • 30
  • 1
    I guess you can use join in your query so all the records will be fetched in a single query. – Ashiq K Aug 23 '20 at 18:29
  • @AshiqK Thanks for your fast reply ! Could you explain that a bit furhter ? A simple join does not work... it simply joins all spawners into a query but executes the "OneToOne" statements in the n+1 manner – genaray Aug 23 '20 at 18:30
  • Try using multiple joins like ' Spawner s join Location l on s.id = l.spawner_id join Chunk c on l.id = c.location_id`. I am using id to join since I'm not sure about your table structure. – Ashiq K Aug 23 '20 at 18:45
  • i think using joins will do the trick...it will fetch everything in a single query – Ashiq K Aug 23 '20 at 18:51
  • 1
    Have you tried using a `join fetch` like it is mentioned in [this question](https://stackoverflow.com/questions/17431312/what-is-the-difference-between-join-and-join-fetch-when-using-jpa-and-hibernate)? – Hernán Alarcón Aug 23 '20 at 19:04
  • Perhaps it is time to get Hibernate out of the way, and learn to code SQL. – Rick James Aug 23 '20 at 20:20
  • checkout the hibernate documentation on fetching, there are multiple examples for specific fetching strategies and it also highlights the 'problem' you are facing in example 3, as the previous comments have stated, it should work when join-fetching the related entities https://docs.jboss.org/hibernate/orm/5.2/userguide/html_single/chapters/fetching/Fetching.html – PaulD Aug 24 '20 at 08:20
  • Have you considered adding `NamedEntityGraph`? – SSK Aug 24 '20 at 12:36

0 Answers0