Hi,
I'm trying to execute a particular query either in HQL or using the Criteria API. I have four tables, a User table (srm_user_t), a Groups table (srm_group_t), a table that maps users to groups (srm_group_member_t) and a permissions table that maps the permissions that a group has (srm_group_permissions_t). Here is my mapping:
Code:
<hibernate-mapping>
<class name="com.domain.User" table="srm_user_t" lazy="false">
<id name="id" column="id"/>
<property name="username" column="login"/>
<property name="email" column="email"/>
<property name="firstName" column="first_name"/>
<property name="lastName" column="last_name"/>
<set name="groups" table="srm_group_member_t" lazy="false">
<key column="uid"/>
<many-to-many column="gid"
class="com.domain.Group"/>
</set>
</class>
<class name="com.domain.Group" table="srm_group_t" lazy="false">
<id name="id" column="id"/>
<property name="name" column="name"/>
<set name="permissions" table="srm_group_permissions_t" lazy="false">
<key column="gid"/>
<element column="perm_key">
<type name="com.domain.PermissionUserType">
</type>
</element>
</set>
</class>
</hibernate-mapping>
I can retrieve Users and their child objects without a problem, but wish to have query that returns a list of users that contain a certain permission. So I have a method called getUsersWithPermission(Permission permission) that should return a collection of users. Note that Permission uses a custom user type. The following sql does that:
Code:
SELECT
srmdb.srm_user_t.id,
srmdb.srm_user_t.email,
srmdb.srm_user_t.first_name,
srmdb.srm_user_t.last_name,
FROM
srmdb.srm_user_t
INNER JOIN srmdb.srm_group_member_t
ON
(
srmdb.srm_user_t.id = srmdb.srm_group_member_t.uid
)
INNER JOIN srmdb.srm_group_permissions_t
ON
(
srmdb.srm_group_member_t.gid = srmdb.srm_group_permissions_t.gid
)
WHERE
srmdb.srm_group_permissions_t.perm_key = 'ROLE_PCC_EDIT_GLID' ;
Can anyone help me come up with either the HQL or Criteria to perform this query?
Thanks.
Login
FAQ
Search