Searching within a collection - query build problem

czerwiu · **Joined:** Tue Jan 20, 2009 11:27 am **Posts:** 1

Hi All,
I'm new to Hibernate but I've spent a lot of time searching for a best solution for my issue, unfortunatelly I was not able to find one. Excuse me if it is a trivial, but I really need help with it.

I have a quite ordinary User object, which apart from simple fields has also a collection (set) of UserRole objects:

User.java

Code:

public class User implements Serializable {
 private Integer id;
 private String username;
 ...
 private Set<UserRole> roles;
 ...
}

UserRole.java

Code:

public class UserRole {
 private String name;
 private String description;
 ...
 public boolean equals(Object o) {
    if (o == null) return false;
    if (o instanceof UserRole) return name.equals(((UserRole) o).name);
    return false;
 }
}

I didn't want to treat UserRole as an entity so I used collection of elements and custom type (implementation of UserType):

Mapping:

Code:

<hibernate-mapping>

    <typedef name="role" class="org.package.UserRoleType" />

    <class name="org.package.User" table="users">
        <id name="id" column="id_user">
            <generator class="sequence">
                <param name="sequence">users_id_user_seq</param>
            </generator>
        </id>
        <property name="username" />
        ...
        <set name="roles" table="user_role">
            <key column="id_user" foreign-key="userid_role_fk" />
            <element column="rolename" not-null="true" type="role" />
        </set>
    </class>

</hibernate-mapping>

So as you can see, in the database I have the table 'user_role' with following columns: id_user <FK> and rolename.
The primary key is set on those two columns.

Now I would like to implement search functionality, where a user can select one OR MORE role names and browse all the users with matching roles.

As far as I know, in this case I cannot use Criteria queries - Hibernate does not support it. It's a pity because I would be the best option, but well.. I assume I need to use HQL. But - how to write appropriate query? All tutorials, examples, docs, etc. cover a case, when only one, single parameter is being passed to the query. And what about a set of parameters? Or maybe it would be better to reorganize a database scheme and treat UserRole as an entity? Would it be possible then to use Criteria queries? If so, than how?

I will appreciate any help or hint.
Greetings