Hibernate DetachedCriteria在java中有多个结果 [英] Hibernate DetachedCriteria mutiple results in java

问题描述

这是我拥有的SQL语句。

  SELECT USER_PROFILE.FIRST_NAME，USER_PROFILE.LAST_NAME，USER_PROFILE.USER_TYPE 
 FROM USER_PROFILE 
 INNER JOIN USER_LOGIN_STATUS 
 ON USER_PROFILE.USER_ID = USER_LOGIN_STATUS.USER_ID 
 ORDER BY USER_PROFILE.FIRST_NAME 
  

我试图执行下面的代码，我认为相当于休眠DetachedCriteria，因此预计只有两个数据。

  DetachedCriteria dc = getDetachedCriteria（）; 
 DetachedCriteria userLoginCriteria = DetachedCriteria.forClass（UserLoginStatus.class）; 
 userLoginCriteria.setProjection（Projections.distinct（Projections.property（userId）））; 
 dc.add（Subqueries.propertyIn（UserField.id.name（），userLoginCriteria））; 
 
 DetachedCriteria profileCriteria = dc.createCriteria（profile）; 
 profileCriteria.addOrder（Order.asc（firstName））; 
 return getAll（dc，pageSetting）; 
  

但不幸的是，这是意想不到的结果：我有多个数据结果。

名称|类型|

1. Ben Jones |用户|


2. Ben Jones |用户|


3. Tom Homer |客人|


4. Tom Homer |客户|
   

是否有人知道确切的等效DetachedCriteria或解决方案？

许多感谢，

xtian

解决方案

所有，你的SQL看起来不正确。它返回多行的原因是因为您正在加入 USER_LOGIN_STATUS 表，该表可能每个 USER_PROFILE 。由于你不是从 USER_LOGIN_STATUS 表中选择任何字段，所以你看不到为什么有多行。第一，你为什么加入这张表？第二，你正在执行的分离标准与你在做子分类时所提供的SQL并不相同-query，你不在SQL中。

你不需要这个子选择，因为我不明白你为什么要进行连接，我会假设一些要点给你下面的例子：

  DetachedCriteria dc = getDetachedCriteria（）; 
 dc.createAlias（userLoginStatus，uls）; 
 dc.add（Projections.property（firstName））; 
 dc.add（Projections.property（lastName））; 
 dc.add（Projections.property（userType））; 
 dc.addOrder（Order.asc（firstName））; 
 return getAll（dc，pageSetting）; 
  

现在大致相当，但我假设：

• 您在 UserField 和 UserLoginStatus 之间的关系具有正确的映射关系。


• getDetachedCriteria（）有效返回 DetachedCriteria.forClass（UserField.class）
  
  

  您现在也可以引用 UserLoginStatus 中的一个字段为所以：
  
  

    dc.add（Projections.property（uls.my_user_login_field））; 
    

  此外，如果您将查询整理出来并且仍然返回多个实体，那么dinukadev的答案将随后发挥作用：
  
  

    dc.setResultTransformer（Criteria.DISTINCT_ROOT_ENTITY）; 
    

  我怀疑这不适合你的原因是因为你的子选择。 p>
  
  

  抱歉，我无法帮到您。

  
  

  This is the SQL statement that I have.

  SELECT USER_PROFILE.FIRST_NAME, USER_PROFILE.LAST_NAME, USER_PROFILE.USER_TYPE
  FROM USER_PROFILE
  INNER JOIN USER_LOGIN_STATUS
  ON USER_PROFILE.USER_ID=USER_LOGIN_STATUS.USER_ID
  ORDER BY USER_PROFILE.FIRST_NAME
  

  And I'm trying to execute the code below that I thought the equivalent to hibernate DetachedCriteria and expected to only have two data as a result.

  DetachedCriteria dc = getDetachedCriteria();
  DetachedCriteria userLoginCriteria = DetachedCriteria.forClass(UserLoginStatus.class);
  userLoginCriteria.setProjection(Projections.distinct(Projections.property("userId")));
  dc.add(Subqueries.propertyIn(UserField.id.name(), userLoginCriteria));
  
  DetachedCriteria profileCriteria = dc.createCriteria("profile");
  profileCriteria.addOrder(Order.asc("firstName"));
  return getAll(dc, pageSetting);
  

  But unfortunately this is the unexpected result: I am having a multiple data result.

  Name | Type |

  1. Ben Jones | User |
  2. Ben Jones | User |
  3. Tom Homer | Guest |
  4. Tom Homer | Guest |

  Is anyone there knows the exact equivalent DetachedCriteria or a solution for this?

  Many Thanks,

  xtian

  解决方案

  First of all, your SQL looks incorrect. The reason it is returning multiple rows is because you're joining against the USER_LOGIN_STATUS table which may have multiple rows per USER_PROFILE. Because you are not selecting any fields from the USER_LOGIN_STATUS table, you cannot see why there are multiple rows. Why are you joining on this table in the first place?

  Secondly, the detached criteria you are performing is not equivalent to the SQL you have provided since you are doing a sub-query which you are not in the SQL.

  You don't need this sub-select and since I don't understand why you are doing the join I will assume some points to give you the following example:

  DetachedCriteria dc = getDetachedCriteria();
  dc.createAlias("userLoginStatus", "uls");
  dc.add(Projections.property("firstName")); 
  dc.add(Projections.property("lastName"));
  dc.add(Projections.property("userType")); 
  dc.addOrder(Order.asc("firstName")); 
  return getAll(dc, pageSetting);
  

  This is now roughly equivalent but I am assuming:

  • You have the correct mappings for your relationship between UserField and UserLoginStatus.
  • That getDetachedCriteria() is effectively returning DetachedCriteria.forClass(UserField.class).

  You can also now refer to a field in UserLoginStatus as so:

  dc.add(Projections.property("uls.my_user_login_field"));
  

  And as well, if you get your query sorted out and you still return multiple entities, then dinukadev's answer will then come into play with:

  dc.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
  

  I suspect the reason this isn't working for you is because of your sub-select.

  Sorry I cannot help you more.

  这篇关于Hibernate DetachedCriteria在java中有多个结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！