Hibernate WTF - joins and max results

Don't mix eager or subselect joins with max results; the by product is pretty nasty.  

Subselects can select all entities in the associated table, and potentially issue an additional select for each parent, while joins often load duplicate entities due to the max results being applied to the row and not the parent.

CriteriaBasic.java

Criteria criteria = session.createCriteria(Person.class);
criteria.addOrder(Order.asc("id"));
criteria.setFirstResult(0).setMaxResults(2);
    
@SuppressWarnings("unchecked")
List<Person> persons = criteria.list();

CriteriaBasicWithDistinct.java

Criteria criteria = session.createCriteria(Person.class);
criteria.addOrder(Order.asc("id"));
// filters the parents to avoid duplicate entries
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
criteria.setFirstResult(0).setMaxResults(2);

@SuppressWarnings("unchecked")
List<Person> persons = criteria.list();

CriteriaBasicWithDistinct.output

[Person{id: {1}, name: {Ian}, things: {[Thing{id: {1}, name: {Laptop}}, Thing{id: {2}, name: {Car}}]}}]

HqlSelect.java

Session hqlSession = ...
hqlSession.beginTransaction();
Query query = hqlSession.createQuery(
  "select distinct p from Person p left outer join fetch p.things order by p.id");
query.setFirstResult(0);
query.setMaxResults(2);
@SuppressWarnings("unchecked")
List<Person> peeps = query.list();

HqlSelect.sql

select 
  distinct person0_.id as id0_0_, 
  things1_.id as id1_1_, 
  person0_.name as name0_0_, 
  things1_.name as name1_1_, 
  things1_.person_id as person3_1_1_, 
  things1_.PERSON_ID as PERSON3_0_0__, 
  things1_.id as id0__ 
from PERSON person0_ 
left outer join THING things1_ 
  on person0_.id=things1_.PERSON_ID 
order by person0_.id

HqlSelect.warning

2013-07-28 16:22:21 WARN  QueryTranslatorImpl:355 - firstResult/maxResults specified with collection fetch; applying in memory!

person-data

ID NAME
-- --------------------
 1 Ian
 2 Daniel
 3 Neil
 4 Tom

Person.java

import java.util.List;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

import org.hibernate.annotations.Fetch;

@Entity
@Table(name = "PERSON")
public class Person {
  
  @Id
  private int id;
  
  public int getId() { return id; }
  public void setId(int id) { this.id = id; }
  
  @Column
  private String name;
  
  public String getName() { return name; }
  public void setName(String name) { this.name = name; }
  
  /**
   * These will change through the example
   */
  @OneToMany(...)
  @Fetch(...)
  private List<Thing> things;
  
  public List<Thing> getThings() { return things; }
  public void setThings(List<Thing> things) { this.things = things; }
  
  
}

PersonOneToManyEagerJoinColSubselect.java

@OneToMany(fetch = FetchType.EAGER)
@JoinColumn(name = "PERSON_ID")
@Fetch(FetchMode.SUBSELECT)
private List<Thing> things;

PersonOneToManyEagerJoinColSubselect.sql

select 
  this_.id as id0_0_, 
  this_.name as name0_0_ 
from PERSON this_ 
order by this_.id asc limit ?

select 
  things0_.PERSON_ID as PERSON3_0_2_, 
  things0_.id as id2_, 
  things0_.id as id1_1_, 
  things0_.name as name1_1_, 
  things0_.person_id as person3_1_1_, 
  person1_.id as id0_0_, 
  person1_.name as name0_0_ 
from THING things0_ 
left outer join PERSON person1_ 
  on things0_.person_id=person1_.id 
where things0_.PERSON_ID in (select this_.id from PERSON this_ )

PersonOneToManyEagerMappedByJoin.java

@OneToMany(fetch = FetchType.EAGER, mappedBy = "person")
@Fetch(FetchMode.JOIN)
private List<Thing> things;

PersonOneToManyEagerMappedByJoin.output

[Person{id: {1}, name: {Ian}, things: {[Thing{id: {1}, name: {Laptop}}, Thing{id: {2}, name: {Car}}]}}, 
Person{id: {1}, name: {Ian}, things: {[Thing{id: {1}, name: {Laptop}}, Thing{id: {2}, name: {Car}}]}}]

PersonOneToManyEagerMappedByJoin.sql

select 
  this_.id as id0_1_, 
  this_.name as name0_1_, 
  things2_.person_id as person3_0_3_, 
  things2_.id as id3_, 
  things2_.id as id1_0_, 
  things2_.name as name1_0_, 
  things2_.person_id as person3_1_0_ 
from PERSON this_ 
left outer join THING things2_ 
  on this_.id=things2_.person_id 
order by this_.id asc limit ?

PersonOneToManyEagerMappedBySubselect.java

@OneToMany(fetch = FetchType.EAGER, mappedBy = "person")
@Fetch(FetchMode.SUBSELECT)
private List<Thing> things;

PersonOneToManyEagerMappedBySubselect.output

[Person{id: {1}, name: {Ian}, things: {[Thing{id: {1}, name: {Laptop}}, Thing{id: {2}, name: {Car}}]}}, 
Person{id: {2}, name: {Daniel}, things: {[Thing{id: {3}, name: {TV}}, Thing{id: {4}, name: {Motorcycle}}]}}]

PersonOneToManyEagerMappedBySubselect.sql

select 
  this_.id as id0_0_, 
  this_.name as name0_0_ 
from PERSON this_ 
order by this_.id asc limit ?

select 
  things0_.person_id as person3_0_1_, 
  things0_.id as id1_, 
  things0_.id as id1_0_, 
  things0_.name as name1_0_, 
  things0_.person_id as person3_1_0_ 
from THING things0_ 
where things0_.person_id in (select this_.id from PERSON this_ )

select 
  person0_.id as id0_0_, 
  person0_.name as name0_0_ 
from PERSON person0_ 
where person0_.id=?

select 
  person0_.id as id0_0_, 
  person0_.name as name0_0_ 
from PERSON person0_ 
where person0_.id=?

PersonOneToManyEagerMappedBySubselectIn.sql

where things0_.person_id in (select this_.id from PERSON this_ )

PersonOneToManySelectBatch.java

// works with FetchType.LAZY and FetchType.EAGER
@OneToMany(fetch = FetchType.LAZY)
@JoinColumn(name = "PERSON_ID")
@Fetch(FetchMode.SELECT)
@BatchSize(size = 2)
private List<Thing> things;

PersonOneToManySelectBatch.output

[Person{id: {1}, name: {Ian}, things: {[Thing{id: {1}, name: {Laptop}}, Thing{id: {2}, name: {Car}}]}}, 
Person{id: {2}, name: {Daniel}, things: {[Thing{id: {3}, name: {TV}}, Thing{id: {4}, name: {Motorcycle}}]}}]

PersonOneToManySelectBatch.sql

select 
  this_.id as id0_0_, 
  this_.name as name0_0_ 
from PERSON this_ 
order by this_.id asc limit ?

select 
  things0_.PERSON_ID as PERSON3_0_2_, 
  things0_.id as id2_, 
  things0_.id as id1_1_, 
  things0_.name as name1_1_, 
  things0_.person_id as person3_1_1_, 
  person1_.id as id0_0_, 
  person1_.name as name0_0_ 
from THING things0_ 
left outer join PERSON person1_ 
  on things0_.person_id=person1_.id 
where things0_.PERSON_ID in (?, ?)

PersonPersister.java

import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Session;

public class PersonPersister {

  public List<Person> getFivePeople() {
    Session session = ...; // your session factory goes here
    Criteria criteria = session.createCriteria(Person.class);
    return criteria.setMaxResults(5).list();
  }

}

thing-data

ID NAME   	 	PERSON_ID
-- -------------------- ----------
 1 Laptop			 1
 2 Car				 1
 3 TV				 2
 4 Motorcycle			 2
 5 House			 3
 6 Cat				 3
 7 Dog				 4
 8 PC				 4

Thing.java

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
import javax.persistence.Table;


@Entity
@Table(name = "THING")
public class Thing {
  
  @Id
  private int id;
  
  public int getId() { return id; }
  public void setId(int id) { this.id = id; }
  
  @Column
  private String name;
  
  public String getName() { return name; }
  public void setName(String name) { this.name = name; }
  
  @ManyToOne
  private Person person;
  
  public Person getPerson() { return person; }
  public void setPerson(Person person) { this.person = person; }
  
}