JPA/Hibernate query: load eagerly with subselect

Question

Situation: "Parent" entity has multiple "Child" entities (@OneToMany, @Lazy) - two way relationship. No foreign key ("Child#parentId") field on entity.

Goal: Avoid N+1 problem by retrieving fully loaded Parent collection using sub-selects. If I understand theory of Subselect, this is my goal (2 resulting SQL queries):

select * from Parent ...;
select * from Child where parent_id in ...;

Question 1: What is the best practice to achieve this? Could you provide examples in both JPQL/HSQL and Criteria?

Question 2 (bonus): Can API manage second query division into "batches" - e.g. limit batches to 500: if 1st query loads 1000 Parents, 2a. loads Children for 500 Parents, 2b. loads for next 500.

I have tried: Both result in SQL JOINs, it seems that I cannot use Child's foreign key without JOIN.

// 2nd query:
criteria
.createAlias("parent", "p")
.add(Property.forName("p.id")
        .in(parentCriteria.setProjection(Projections.property("id"))))
.list();

// 2nd query (manual):
criteria
.createAlias("parent", "p")
.add(Property.forName("p.id").in(parentIdList))
.list();

Update (2015-04-05)

I checked that what it indeed works in EclipseLink via hints:

query.setHint("eclipselink.batch.type", "EXISTS");

This link http://blog.ringerc.id.au/2012/06/jpa2-is-very-inflexible-with-eagerlazy.html suggests that this is not possible via Hibernate and suggests manual fetching. However I cannot understand how to achieve it via HQL or Criteria, specifically how to get child.parent_id column that is not on Entity, but exists only on Database. That is, avoiding JOIN that would result from child.parent.id.

Zielu Zielu · Accepted Answer · 2015-03-30T23:37:23

To avoid N+1 queries you can annotate relationship with

@BatchFetch(BatchFetchType.JOIN)  //in eclipselink or
@BatchSize //in hibernate.

Inside queries, you can add fetch to join clause:

select p from Parent p join fetch p.children c where ...

You can add also query hints

query.setHint("eclipselink.batch", "p.children");

Or use EntityGraphs.

JPA/Hibernate query: load eagerly with subselect

Update (2015-04-05)

1 Answers