Hibernate DetachedCriteria Java中的多个结果 [英] Hibernate DetachedCriteria multiple results in java

问题描述

这是我拥有的SQL语句.

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

我正在尝试执行下面的代码，我认为这与休眠的DetachedCriteria等效，并且预期结果只有两个数据.

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.

1. 本·琼斯|用户|
2. 本·琼斯|用户|
3. 汤姆·荷马|访客|
4. 汤姆·荷马|访客|

有谁知道确切的等效DetachedCriteria或对此的解决方案?

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

推荐答案

首先，您的SQL看起来不正确.它返回多行的原因是因为您要针对USER_LOGIN_STATUS表联接，该表每个USER_PROFILE可能有多行.因为您不是从USER_LOGIN_STATUS表中选择任何字段，所以您看不到为什么会有多行.您为什么首先加入此表?

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?

第二，由于您正在执行不在SQL中的子查询，因此您正在执行的分离条件与您提供的SQL不相同.

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:

• 对于UserField和UserLoginStatus之间的关系，您具有正确的映射.
• 该getDetachedCriteria()实际上返回了DetachedCriteria.forClass(UserField.class).

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

您现在也可以这样引用UserLoginStatus中的字段:

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

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

同样，如果您的查询得到整理，并且您仍然返回多个实体，那么dinukadev的答案将起作用:

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屋！