Hibernate - 限制嵌套集合的大小 [英] Hibernate - Limit size of nested collection
问题描述
我有以下型号:
@Entity
@Data
public class Book {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String author;
private String title;
@OneToMany(cascade = CascadeType.MERGE)
private List<Comment> comments;
}
@Entity
@Data
public class Comment {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String body;
private LocalDateTime timestamp;
}
我正在努力实现的是获取所有带分页的书籍，但所有书籍都应将其评论集限制为 5 个最新条目.我想出了以下本机查询(mssql):
What i'm struggling to achieve is to fetch all books with pagination, but all of them should have their comments collection limited to 5 latest entries. I came up with following native query (mssql):
select *
from book b
left join book_comments bc on bc.book_id = b.id and bc.comments_id in (
select top 5 c2.id
from comment c2
join book_comments bc2 on c2.id = bc2.comments_id
join book b2 on bc2.book_id = b2.id
where bc2.book_id = b.id
order by c2.timestamp desc
)
left join comment c
on bc.comments_id = c.id
当我在控制台中运行它时，这个查询返回正确的结果集，但是当它像这样由应用程序运行时:
This query return correct result set when i run it in console, but when it's runned by the app like this:
public interface BookRepository extends JpaRepository<Book, Long> {
@Query(value = "select * " +
"from book b " +
" left join book_comments bc on bc.book_id = b.id and bc.comments_id in ( " +
" select top 5 c2.id " +
" from comment c2 " +
" join book_comments bc2 on c2.id = bc2.comments_id " +
" join book b2 on bc2.book_id = b2.id " +
" where bc2.book_id = b.id " +
" order by c2.timestamp desc " +
") " +
" left join comment c " +
" on bc.comments_id = c.id",
nativeQuery = true)
Page<Book> findAll(Pageable pageable);
}
抛出语法错误:
"localizedMessage": "Incorrect syntax near 'id'.",
"message": "Incorrect syntax near 'id'.",
"suppressed": []
},
"localizedMessage": "could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet",
我观察到在执行计数查询时出现此语法错误.我还尝试提供 countQuery 属性(相同的查询，但不是 * 而是 count(*)).这样我就不会出现语法错误，但它返回了不正确的结果集 - 书重复了 N 次，其中 N 是评论集合的大小.
I observed that this syntax error is appearing when count query is executed. I also tried providing countQuery attribute (same query but instead of * there is count(*)). This way i get no syntax error, but it returns incorrect result set - book is repeated N times where N is size of comments collection.
我该如何解决?这种情况有没有更好的办法?
How do i fix that? Is there any better approach on this case?
计数查询:
select count(*)
from book b
left join book_comments bc on bc.book_id = b.id and bc.comments_id in (
select top 5 c2.id
from comment c2
join book_comments bc2 on c2.id = bc2.comments_id
join book b2 on bc2.book_id = b2.id
where bc2.book_id = b.id
order by c2.timestamp desc
)
left join comment c
on bc.comments_id = c.id
编辑(获取查询):
select comments0_.book_id as book_id1_1_0_, comments0_.comments_id as comments2_1_0_, comment1_.id as id1_2_1_, comment1_.body as body2_2_1_, comment1_.timestamp as timestam3_2_1_ from book_comments comments0_ inner join comment comment1_ on comments0_.comments_id=comment1_.id where comments0_.book_id=?
推荐答案
Blaze-Persitence 是一个基于 JPA 的查询构建器，它支持基于 JPA 模型的许多高级 DBMS 功能.我在它之上创建了实体视图，以允许在 JPA 模型和自定义接口定义模型之间轻松映射，例如类固醇上的 Spring Data Projections.这个想法是您按照自己喜欢的方式定义目标结构，并通过 JPQL 表达式将属性(getter)映射到实体模型.由于属性名称用作默认映射，因此您通常不需要显式映射，因为 80% 的用例是拥有作为实体模型子集的 DTO.
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();
}
查询是将实体视图应用于查询的问题，最简单的就是通过 id 查询.
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);
但是 Spring Data 集成允许您几乎像 Spring Data Projections 一样使用它:https://persistence.blazebit.com/documentation/1.4/entity-view/manual/en_US/#spring-data-features
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
@Limit 注释是在几周前引入的，它将成为未来几天发布的新版本 1.5.0-Alpha2 的一部分.如果您愿意，您可以同时使用 1.5.0-SNAPSHOT.生成的SQL大致是这样的
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();
这篇关于Hibernate - 限制嵌套集合的大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
