Hibernate generate m + 1 queries in Many-to-one

Question

My problem is very simple but I don't know how to make Hibernate does the way I want: - table MainTable have Many-2-One with ParentTable (with 100 rows). MainTable point to m=26 rows out of 100 rows in ParentTable

@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "PARENT_ID")
@Fetch(FetchMode.JOIN)

When I simply query "from MainTable"

it will generate 26 + 1 queries

When I trace the queries, the 1st query only load PARENT_ID used by 26 later queries. I guess it should have the way to load the whole PARENT_TABLE in the 1st query..

Please help by assuming that:

• FetchType.EAGER is a MUST
• Using from MainTable mt left join fetch mt.parent parent is ok but we have many association

Answer 1

 // Annotate ParentTable Persistance class file with a batch Size 
 @BatchSize(size=100)
 class ParentTable{
     ..
 }

---

@ManyToOne
@JoinColumn(name = "PARENT_ID")

This will reduce the number of queries by n/100+1.

The reason for this issue is that, the hibernate will fetch the data in lazy mode internally, (I am not talking about the FetchMode.Lazy). The Lazy mode can be excluded by using FetchMode.SUBSELECT, which is applicable only for collections. When it comes to @ManyToOne, you can select a batch of data, by specifying the batchSize.

A short Description about Fetch Startegies

FetchMode.SUBSELECT

One Query for parent, One Query for the Related table. Applicable only for Collections framework. Only 2 queries fired.

FetchMode.SELECT

One Query For Parent, N Queries for the Child.

FetchMode.JOIN

One Query for Parent, N Queries for Child, but the database retrieval take place upfront in JOIN.

FetchType.Batch

One Query for parent and n/batchSize + 1 number of queries fired.

---

There are two type of fetching, based on when the queries should be executed.

FetchType.EAGER :

The queries are fired instantly.

FetchType.LAZY :

The queries are fired when the child object is accessed. So number of of queries executed will depent on the number of child objects accessed.

How the Fetch Strategies work is better explained here.