我试图使用基于行的限制来实现分页(例如: setFirstResult(5)和 setMaxResults(10))在已连接到其他表的Hibernate Criteria查询上。可以理解的是,数据正在被随机切断;并解释了原因此处。
作为解决方案,页面建议使用第二个sql select而不是联接。
如何将我现有的条件查询(使用 createAlias())进行连接以使用嵌套的select取而代之的是什么?
解决方案您可以通过请求一个不同的ID列表来取得所需的结果,而不是一个不同的水合对象列表。 / p>
只需将其添加到您的标准:
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.
更多推荐
如何在连接和基于行的限制(分页)中获得不同的结果?
发布评论