如何通过连接和基于行的限制(分页)在休眠中获得不同的结果? [英] How to get distinct results in hibernate with joins and row-based limiting (paging)?

问题描述

我试图在 Hibernate Criteria 查询上使用基于行的限制(例如:setFirstResult(5) 和 setMaxResults(10))实现分页连接到其他表.

可以理解，数据被随机截断；其原因在

如何将现有条件查询(使用 createAlias() 进行连接)转换为使用嵌套选择?

解决方案

您可以通过请求不同 id 的列表而不是不同的水合对象列表来实现所需的结果.

只需将此添加到您的标准中:

criteria.setProjection(Projections.distinct(Projections.property("id")));

现在，您将根据基于行的限制获得正确数量的结果.这样做的原因是因为投影将执行差异性检查作为 sql 查询的一部分，而不是 ResultTransformer 所做的，即过滤结果的差异性已执行sql查询.

值得注意的是，您现在将获得一个 id 列表，而不是获取对象列表，稍后您可以使用它来从休眠状态中水合对象.

I'm trying to implement paging using row-based limiting (for example: setFirstResult(5) and setMaxResults(10)) on a Hibernate Criteria query that has joins to other tables.

Understandably, data is getting cut off randomly; and the reason for that is explained here.

As a solution, the page suggests using a "second sql select" instead of a join.

How can I convert my existing criteria query (which has joins using createAlias()) to use a nested select instead?

解决方案

You can achieve the desired result by requesting a list of distinct ids instead of a list of distinct hydrated objects.

Simply add this to your criteria:

criteria.setProjection(Projections.distinct(Projections.property("id")));

Now you'll get the correct number of results according to your row-based limiting. The reason this works is because the projection will perform the distinctness check as part of the sql query, instead of what a ResultTransformer does which is to filter the results for distinctness after the sql query has been performed.

Worth noting is that instead of getting a list of objects, you will now get a list of ids, which you can use to hydrate objects from hibernate later.

这篇关于如何通过连接和基于行的限制(分页)在休眠中获得不同的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！