How to avoid multiple database queries for Hibernate Projection?

Question

I have two classes with a unidirectional many-to-one relationship. Querying for one of the classes based on criteria of the other with a projection generates extra queries (n+1). With Hibernate, how can a projection query avoid n+1 queries?

Here's my model:

@Entity
public class Person {
    @ManyToOne(targetEntity=PersonType.class, optional=false)
    private PersonType personType;

    @Id
    @GeneratedValue
    private Long id;

    private String name;

    /* other supporting code */
}

@Entity
public class PersonType {
    @Id
    @GeneratedValue
    private Long id;

    /* other supporting code */
}

Here's my criteria query

session.createCriteria(Person.class)
        .add(Restrictions.like("name", "%" + nameContains + "%"))
        .setProjection(Projections.distinct(Projections.property("personType")))
        .list()
        ;

And here's the Hibernate log for running the criteria:

Hibernate: select distinct this_.personType_id as y0_ from Person this_ where this_.name like ?
Hibernate: select persontype0_.id as id1_0_, persontype0_.name as name1_0_ from PersonType persontype0_ where persontype0_.id=?
Hibernate: select persontype0_.id as id1_0_, persontype0_.name as name1_0_ from PersonType persontype0_ where persontype0_.id=?

Hibernate does a query to get the PersonType ids, and then a query for each PersonType individually. These roundtrips are an expensive part of a web site request, and I'd like to avoid them.

I have published a full reproduction on github for anyone to experiment with.

Answer 1

A subquery can be used:

DetachedCriteria subquery = DetachedCriteria.forClass(Person.class)
        .add(Restrictions.like("name", "%" + nameContains + "%"))
        .setProjection(Projections.distinct(Projections.property("personType.id")))
        ;
return session.createCriteria(PersonType.class)
        .add(Subqueries.propertyIn("id", subquery))
        .list()
        ;

Here's the generated query:

Hibernate: select this_.id as id1_0_, this_.name as name1_0_ from PersonType this_ where this_.id in (select distinct this_.personType_id as y0_ from Person this_ where this_.name like ?)

The subquery reduces round trips, but still may not be the most efficient query to execute on the database.