如何获得“真实”的体验? sql 与 hibernate 标准查询不同吗?
我有一个 Hibernate 标准查询,它错误地提取了最大结果。在许多情况下,当我指定 20 个最大结果时,查询实际上只返回 1 或 5 个结果,因为限制会返回许多重复项。
Criteria c = session.createCriteria(DomainObject.class);
c.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
c.createAlias("tags.list", "tag");
c.createAlias("names", "name");
c.createAlias("site", "site");
c.createAlias("site.tags.list", "siteTag");
// loads of or/and eq/like restrictions.
c.setFirstResult(0);
c.setMaxResults(20);
return c.list();
有没有办法修复此查询,以便如果我说 20 个最大结果,它确实会返回 20 个地区结果? hibernate 将查询限制为 20 个结果,并在之后而不是在数据库级别进行不同的过滤,这似乎非常疯狂。
帮助?
I have a Hibernate criteria query that is incorrectly pulling out max results. In many cases, when I specify 20 max results, the query actually only returns 1 or 5 results, because the restrictions return many duplicates.
Criteria c = session.createCriteria(DomainObject.class);
c.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
c.createAlias("tags.list", "tag");
c.createAlias("names", "name");
c.createAlias("site", "site");
c.createAlias("site.tags.list", "siteTag");
// loads of or/and eq/like restrictions.
c.setFirstResult(0);
c.setMaxResults(20);
return c.list();
Is there any way to fix this query so that if I say 20 max results, it really does return 20 district results? It seems pretty crazy that hibernate limits the query to 20 results, and does the distinct filtering AFTER instead of at the database level.
Help?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
我认为你已经很接近了,你只需要一个不同的 ID 列表,而不是不同的对象。
尝试将此添加到您的标准中:
以上是 300491
I think you are close, you just need a list of distinct Ids instead if distinct objects.
Try adding this to your criteria:
the above is a quote from the answer at 300491
我从另一个论坛得到这个答案
这似乎是最好的解决方案。基本上,您首先创建一个子查询(DetachedCriteria)。这将获取所有不同的 id。
然后,将该子查询的结果应用于主查询。
由此生成的 SQL 非常干净,并且 hibernate 将结果作为对象列表返回给您。
I got this answer from another forum
It appears to be the best solution. Basically you create a subquery first (the DetachedCriteria). This will fetch all the distinct ids.
Then, you apply the results of that subquery to your main query.
The resulting SQL generated by this is quite clean, and hibernate returns the results to you as a list of objects.
您是否尝试过在 ID 上使用投影并根据这些结果创建子查询,如 此页面?
编辑:(请注意,Oracle 11.2.0.1.0 中似乎存在 一个错误 如果您正在使用的话,这可能会阻止您获得您想要的结果。)
Have you tried to use a Projection on the ID and create a subquery based on those results, as described on this page?
EDIT: (Note that there seems to be a bug in Oracle 11.2.0.1.0 which may prevent you from getting the results you want if that's what you're using.)
下面是我们可以通过多重投影来执行 Distinct
ExtraProjections.java
示例用法:
引用自 https ://forum.hibernate.org/viewtopic.php?t=964506
Below is the way we can do Multiple projection to perform Distinct
ExtraProjections.java
Sample Usage:
Referenced from https://forum.hibernate.org/viewtopic.php?t=964506