An in condition query problem in the HQL of Hibernate multi-correlation mapping

There's a friend in the group. To solve a problem, high score for a HQL multiple query statement.

For a description of the problem, see http://topic.csdn.net/u/20090621/16/4eac6fe0-bf3e-422e-a697-f7584732f66e.html, as follows:

A discipline table (Field), a user table (users), many-to-many relationships.

Subject table has two fields, ID and Descripiton, User table has many fields, not listed

Set set in field with users

Set set fields in user

Now check out the female user id like ' 520% '

I wrote this:

From User U,field f where u.gender= ' m ' in (select F.users to Field where f.id like ' 520% ')

Keep the error!

My project code happens to have a Many-to-many association mapping class: User and role (roles), where there is a roles collection in the user class and a users collection in the Role class. I tested it with my code.

First wrote the following sentence:

Select User from user user where user.sex= ' man ' and user in (select role.users from role where role.id>5)

No way, no error. The reason is that the "in condition" sentence in Hibernate's HQL statement is as follows: X in (' A ', ' B ', ' C '), which requires x to be an element in (' A ', ' B ', ' C '), and the Select Role.users is a collection of collections, The elements within it should be a set:set<user> in (select Role.users ...), not an object: User in (select Role.users ...).

Unfortunately, the HQL statement does not have the set<user> in (select Role.users ...) clause, and later, check the Hibernate reference documentation and get the solution. Originally, in the Hibernate HQL statement in the "in condition", can add "elements" keyword, namely the above query statement becomes:

Select User from user User,role role where user.sex= ' man ' and user in elements (role.users) and role.id>5

That's fine.

However, there is a small problem, that is, this query out, there will be a lot of duplicate records, because the query out of each of the role, through the role.getusers () to find the Users collection, so, of course, there may be several role has the same user phenomenon. Easy to solve, add more than a "distinct" keyword on the line.

Select DISTINCT user from user User,role role where user.sex= ' man ' and user in elements (role.users) and role.id>5

Okay, it's settled. The Csdn friend's question was solved, and its HQL statement was as follows:

SELECT DISTINCT u from User u,field f where u.gender= ' M ' and U in Elements (f.users) and f.id like ' 520% '