java performance hibernate join hibernate-criteria

How to get batching using the old hibernate criteria?

I'm still using the old org.hibernate.Criteria and get more and more confused about fetch modes. In various queries, I need all of the following variants, so I can't control it via annotations. I'm just switching everything to @ManyToOne(fetch=FetchType.LAZY), as otherwise, there's no change to change anything in the query.

What I could find so far either concerns HQL or JPA2 or offers just two choices, but I need it for the old criteria and for (at least) the following three cases:

Do a JOIN, and fetch from both tables. This is OK unless the data is too redundant (e.g., the master data is big or repeated many times in the result). In SQL, I'd write
SELECT * FROM item JOIN order on item.order_id = order.id
WHERE ...;
Do a JOIN, fetch from the first table, and the separation from the other. This is usually the more efficient variant of the previous query. In SQL, I'd write
SELECT item.* FROM item JOIN order on item.order_id = order.id
WHERE ...;

SELECT order.* FROM order WHERE ...;
Do a JOIN, but do not fetch the joined table. This is useful e.g., for sorting based on data the other table. In SQL, I'd write
SELECT item.* FROM item JOIN order on item.order_id = order.id
WHERE ...
ORDER BY order.name, item.name;

It looks like without explicitly specifying fetch=FetchType.LAZY, everything gets fetched eagerly as in the first case, which is sometimes too bad. I guess, using Criteria#setFetchMode, I can get the third case. I haven't tried it out yet, as I'm still missing the second case. I know that it's somehow possible, as there's the @BatchSize annotation.

Am I right with the above?
Is there a way how to get the second case with the old criteria?

Update

It looks like using createAlias() leads to fetching everything eagerly. There are some overloads allowing to specify the JoinType, but I'd need to specify the fetch type. Now, I'm confused even more.

Solution

Yes you can satisfy all three cases using FetchType.LAZY, BatchSize, the different fetch modes, and projections (note I just made up a 'where' clause with Restrictions.like("name", "%s%") to ensure that I retrieved many rows):

Do a JOIN, and fetch from both tables.

Because the order of an item is FetchType.LAZY, the default fetch mode will be 'SELECT' so it just needs to be set as 'JOIN' to fetch the related entity data from a join rather than separate query:

Session session = entityManager.unwrap(org.hibernate.Session.class);
Criteria cr = session.createCriteria(Item.class);
cr.add(Restrictions.like("name", "%s%"));
cr.setFetchMode("order", FetchMode.JOIN);
List results = cr.list();
results.forEach(r -> System.out.println(((Item)r).getOrder().getName()));

The resulting single SQL query:

select
    this_.id as id1_0_1_,
    this_.name as name2_0_1_,
    this_.order_id as order_id3_0_1_,
    order2_.id as id1_1_0_,
    order2_.name as name2_1_0_ 
from
    item_table this_ 
left outer join
    order_table order2_ 
        on this_.order_id=order2_.id 
where
    this_.name like ?

Do a JOIN, fetch from the first table and the separately from the other.

Leave the fetch mode as the default 'SELECT', create an alias for the order to use it's columns in sorting, and use a projection to select the desired subset of columns including the foreign key:

Session session = entityManager.unwrap(org.hibernate.Session.class);
Criteria cr = session.createCriteria(Item.class);
cr.add(Restrictions.like("name", "%s%"));
cr.createAlias("order", "o");
cr.addOrder(org.hibernate.criterion.Order.asc("o.id"));
cr.setProjection(Projections.projectionList()
        .add(Projections.property("id"), "id")
        .add(Projections.property("name"), "name")
        .add(Projections.property("order"), "order"))
        .setResultTransformer(org.hibernate.transform.Transformers.aliasToBean(Item.class));
List results = cr.list();
results.forEach(r -> System.out.println(((Item)r).getOrder().getName()));

The resulting first SQL query:

select
    this_.id as y0_,
    this_.name as y1_,
    this_.order_id as y2_ 
from
    item_table this_ 
inner join
    order_table o1_ 
        on this_.order_id=o1_.id 
where
    this_.name like ? 
order by
    o1_.id asc

and subsequent batches (note I used @BatchSize(value=5) on the Order class):

select
    order0_.id as id1_1_0_,
    order0_.name as name2_1_0_ 
from
    order_table order0_ 
where
    order0_.id in (
        ?, ?, ?, ?, ?
    )

Do a JOIN, but do not fetch the joined table.

Same as the previous case, but don't do anything to prompt the loading of the lazy-loaded orders:

Session session = entityManager.unwrap(org.hibernate.Session.class);
Criteria cr = session.createCriteria(Item.class);
cr.add(Restrictions.like("name", "%s%"));
cr.createAlias("order", "o");
cr.addOrder(Order.asc("o.id"));
cr.setProjection(Projections.projectionList()
        .add(Projections.property("id"), "id")
        .add(Projections.property("name"), "name")
        .add(Projections.property("order"), "order"))
        .setResultTransformer(org.hibernate.transform.Transformers.aliasToBean(Item.class));
List results = cr.list();
results.forEach(r -> System.out.println(((Item)r).getName()));

The resulting single SQL query:

select
    this_.id as y0_,
    this_.name as y1_,
    this_.order_id as y2_ 
from
    item_table this_ 
inner join
    order_table o1_ 
        on this_.order_id=o1_.id 
where
    this_.name like ? 
order by
    o1_.id asc

My entities for all cases remained the same:

@Entity
@Table(name = "item_table")
public class Item {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    @ManyToOne(fetch = FetchType.LAZY)
    private Order order;

    // getters and setters omitted
}

@Entity
@Table(name = "order_table")
@BatchSize(size = 5)
public class Order {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    // getters and setters omitted
}