Hibernate - Limit Size Of Nested Collection

i have following model: @Entity @Data public class Book { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; private String author; privat

Solution 1:

This is a perfect use case for Blaze-Persistence Entity Views.

Blaze-Persitence is a query builder on top of JPA which supports many of the advanced DBMS features on top of the JPA model. I created Entity Views on top of it to allow easy mapping between JPA models and custom interface defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure the way you like and map attributes(getters) via JPQL expressions to the entity model. Since the attribute name is used as default mapping, you mostly don't need explicit mappings as 80% of the use cases is to have DTOs that are a subset of the entity model.

A mapping for your model could look as simple as the following

@EntityView(Book.class)
interface BookDto {
  @IdMapping
  Long getId();
  String getAuthor();
  String getTitle();
  @Limit(limit = 5, order = { "timestamp DESC", "id DESC"})
  List<CommentDto> getComments();
}
@EntityView(Comment.class)
interface CommentDto {
  @IdMapping
  Long getId();
  String getBody();
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

BookDto dto = entityViewManager.find(entityManager, BookDto.class, id);

But the Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/1.4/entity-view/manual/en_US/#spring-data-features

The @Limit annotation was introduced a few weeks ago and will be part of the new release 1.5.0-Alpha2 which will be published in the next few days. You can use 1.5.0-SNAPSHOT in the meantime if you like. The generated SQL roughly looks like this

select b.id, b.author, b.title, c.id, c.body
from book b
cross apply (
  select c.id, c.body, c.timestamp
  from book_comments bc 
  join comment c on bc.comments_id = c.id
  where bc.book_id = b.id
  order by timestamp desc, id desc
  limit 5
) c

You can also write that query by hand with the query builder which roughly looks like this

criteriaBuilderFactory.create(entityManager, Tuple.class)
  .from(Book.class, "b")
  .leftJoinLateralEntitySubquery("b.comments", "c", "subC")
    .orderByDesc("subC.timestamp")
    .orderByDesc("subC.id")
    .setMaxResults(5)
  .end()
  .getResultList();

Solution 2:

Simply you can use a @size annotation on the foreign key property to give the upper and lower bound and give the order you want as well.