Hibernate DetachedCriteria multiple results in java

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 |

Ben Jones | User |
Ben Jones | User |
Tom Homer | Guest |
Tom Homer | Guest |

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

Solution

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.