Criteria API返回的结果集太小 [英] Criteria API returns a too small resultset

问题描述

这怎么可能，我必须遵循以下标准：

 
  Criteria criteria = getSession（）。createCriteria C）; 
 criteria.setResultTransformer（Criteria.DISTINCT_ROOT_ENTITY）; 
 criteria.add（Restrictions.eq（active，true））; 
 List list = criteria.list（）;  
 

列表大小现在是20。如果我将最大结果添加到条件中，则可以使用以下条件：

  Criteria criteria = getSession（）。createCriteria（c）; 
 criteria.setResultTransformer（Criteria.DISTINCT_ROOT_ENTITY）; 
  criteria.setMaxResults（90）;  
 criteria.add（Restrictions.eq（active，true））; 
 List list = criteria.list（）;  

现在列表的大小是18！

我不明白在定义最大结果之后结果集大小如何变小，因为行数小于定义的最大值。这肯定看起来像一个错误，或者是有没有我不知道的一些奇怪的hibernate方面？

---

如果您正在寻找这个问题的答案，请务必阅读已接受的答案及其评论。

什么是通过在Hibernate中打开SQL调试并比较生成的查询，可以清楚地看到在这里发生的事情。

使用相当简单的 Sale → Item 一对多映射（希望不言自明），一个 Criteria -based像这样的查询：

  Criteria c = sessionFactory.getCurrentSession（）。createCriteria（Sale.class）; 
 c.createAlias（items，i）; 
 c.add（Restrictions.eq（i.name，doll））; 
 c.setResultTransformer（Criteria.DISTINCT_ROOT_ENTITY）; 
 c.setMaxResults（2）; 
  

产生这样的SQL：

  select top？ this_.saleId作为saleId1_1_，...从销售中获得
 this_ 
内部连接Sale_Item items3_ on this_.saleId = items3_.Sale_saleId 
内部连接项目items1_上的items3_.items_id = items1_.id 
 where items1_.name =？ 
  

而一个 Query 像这样： p>

 查询q = sessionFactory.getCurrentSession（）。createQuery（从销售s中选择不同的s加入s.items as i where i.name =：名称）; 
 q.setParameter（name，doll）; 
 q.setMaxResults（2）; 
  

产生类似于：

 选择顶部？ distinct hibernated0_.saleId as saleId1_ $ b $ from Sale hibernated0_ 
内部连接Sale_Item items1_ on hibernated0_.saleId = items1_.Sale_saleId 
内部连接项目hibernated2_ on项目1_.items_id = hibernated2_.id 
其中hibernated2_.name =？ 
  

请注意第一行的差异（ DISTINCT ）。 A ResultTransformer 类似于 DISTINCT_ROOT_ENTITY 是一个Java类，它在之后处理SQL行的结果 >执行SQL。因此，当您指定 maxResults 时，它将作为SQL的行限制应用; SQL包含到集合中元素的连接，因此您将SQL结果限制为90个子元素。一旦应用了 DISTINCT_ROOT_ENTITY 转换器，这可能导致少于20个根元素，纯粹取决于在90个连接结果中首先出现哪些根元素。

DISTINCT 在HQL中表现非常不同，因为它实际上使用了SQL DISTINCT 关键字，该关键字在行限制之前应用。因此，它的行为和你期望的一样，并解释了两者之间的区别。理论上你应该看看 setProjection 在SQL级别应用投影 - 例如 c.setProjection（Projections.distinct（Projections.rootEntity（））） - 但不幸的是 Projections.rootEntity（）不存在，我只是做了它。也许它应该！

How is this possible, I have to following criteria

Criteria criteria = getSession().createCriteria(c);
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
criteria.add(Restrictions.eq("active",true));
List list = criteria.list();

The size of list is now 20. If I add a max results to the criteria,

Criteria criteria = getSession().createCriteria(c);
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
criteria.setMaxResults(90);
criteria.add(Restrictions.eq("active",true));
List list = criteria.list();

.. now list's size is 18!

I don't understand how the resultsets size can be smaller after defining the max results, as the amount of rows is smaller than the defined max. This sure seems like a bug, or is there again some weird aspects of hibernate that I'm not aware of?

---

If you're looking for an answer to this question, make sure to read the accepted answer and its comments.

解决方案

What is happening here can be seen very clearly by turning on SQL debugging in Hibernate and comparing the generated queries.

Using a fairly simple Sale → Item one-to-many mapping (which is hopefully self-explanatory), a Criteria-based query like this:

Criteria c = sessionFactory.getCurrentSession().createCriteria(Sale.class);
c.createAlias("items", "i");
c.add(Restrictions.eq("i.name", "doll"));
c.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
c.setMaxResults(2);

produces SQL like this:

select top ? this_.saleId as saleId1_1_, ... 
from Sale this_ 
inner join Sale_Item items3_ on this_.saleId=items3_.Sale_saleId 
inner join Item items1_ on items3_.items_id=items1_.id 
where items1_.name=?

whereas a Query like this:

Query q = sessionFactory.getCurrentSession().createQuery("select distinct s from Sale s join s.items as i where i.name=:name");
q.setParameter("name", "doll");
q.setMaxResults(2);

produces something like:

select top ? distinct hibernated0_.saleId as saleId1_ 
from Sale hibernated0_ 
inner join Sale_Item items1_ on hibernated0_.saleId=items1_.Sale_saleId 
inner join Item hibernated2_ on items1_.items_id=hibernated2_.id 
where hibernated2_.name=?

Note the difference in the very first line (DISTINCT). A ResultTransformer like DISTINCT_ROOT_ENTITY is a Java class, which processes the results of the SQL rows after the SQL is executed. Therefore, when you specify a maxResults, that will be applied as a row limit on the SQL; the SQL includes a join onto the elements in the Collection, so you're limiting your SQL result to 90 sub-elements. Once the DISTINCT_ROOT_ENTITY transformer is applied, that may result in less than 20 root elements, purely dependent on which root elements happen to come out first in the 90 joined results.

DISTINCT in HQL behaves very differently, in that that actually uses the SQL DISTINCT keyword, which is applied before the row limit. Therefore, this behaves as you expect, and explains the difference between the 2.

In theory you should be looking at setProjection to apply a projection at the SQL level -- something like c.setProjection(Projections.distinct(Projections.rootEntity())) -- but unfortunately Projections.rootEntity() doesn't exist, I just made it up. Perhaps it should!

这篇关于Criteria API返回的结果集太小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！