Hibernate version: Hibernate 2.1.6

Im new to Hibernate and I am having trouble comming up with with an HQL statement that would tell me the following.

In my db I have a table that has a list of users, a table that has a list of user groups, and a join table that stores which users are members of which user groups.

How would I contruct an HQL statement that tells me the user groups that a User is not a member of?


XML table configs


<class
name="com.metlife.ins.crdm.model.businessobjects.TercrdmJoinUsrGrpToUsr"
table="TERCRDM_JOIN_USR_GRP_TO_USR"
>

<id
name="joinId"
type="java.lang.Integer"
column="JOIN_ID"
unsaved-value="0"
>
<generator class="identity" />
</id>

<property
name="grpId"
type="java.lang.Integer"
column="GRP_ID"
length="10"
/>
<property
name="usrId"
type="java.lang.String"
column="USR_ID"
length="30"
/>

<!-- Associations -->
</class>


<class
name="com.metlife.ins.crdm.model.businessobjects.TercrdmUsrGrp"
table="TERCRDM_USR_GRP"
>

<id
name="grpId"
type="java.lang.Integer"
column="GRP_ID"
>
<generator class="sequence">
<param name="sequence">SEQ_USR_GROUP</param>
</generator>
</id>

<set name="users" table="TERCRDM_JOIN_USR_GRP_TO_USR" lazy="true" cascade="save-update">
<key >
<column name="GRP_ID"/>
</key>
<many-to-many class="com.metlife.ins.crdm.model.businessobjects.TerauthUsr">
<column name="METNET_ID"/>
</many-to-many>
</set>

<set name="reports" table="TERCRDM_JOIN_USR_GRP_TO_RPT" lazy="true" cascade="save-update">
<key column="GRP_ID"/>
<many-to-many class="com.metlife.ins.crdm.model.businessobjects.TercrdmRptObj" column="OBJ_ID"/>
</set>

<set name="rights" table="TERCRDM_JOIN_USR_GRP_TO_APP_FEAT" lazy="true" cascade="save-update">
<key column="GRP_ID"/>
<many-to-many class="com.metlife.ins.crdm.model.businessobjects.TercrdmLkup" column="LID"> </many-to-many>
</set>

<property
name="grpCd"
type="java.lang.String"
column="GRP_CD"
length="15"
/>
<property
name="grpDesc"
type="java.lang.String"
column="GRP_DESC"
length="40"
/>
<property
name="sysAcct"
type="java.lang.String"
column="SYS_ACCT"
length="3"
/>

<!-- Associations -->


</class>


<class
name="com.metlife.ins.crdm.model.businessobjects.TerauthUsr"
table="TERAUTH_USR"
>

<!--
<composite-id name="comp_id" class="com.metlife.ins.crdm.model.businessobjects.TerauthUsrPK">
<key-property name="metnetId" column="METNET_ID"
type="java.lang.String" length="30" />
<key-property name="grpNm" column="GRP_NM"
type="java.lang.String" length="20" />
</composite-id>
-->
<id name="metnetId" column="METNET_ID" type="java.lang.String" length="30">
<generator class="assigned"/>
</id>

<set name="groups" table="TERCRDM_JOIN_USR_GRP_TO_USR" inverse="true" cascade="save-update">
<key >
<column name="USR_ID"/>
</key>
<many-to-many class="com.metlife.ins.crdm.model.businessobjects.TercrdmUsrGrp">
<column name="GRP_ID"/>
</many-to-many>
</set>

<set name="audits" table="TERLOG_APP_SESS" lazy="true" inverse="true" cascade="save-update">
<key >
<column name="METNET_ID"/>
</key>
<one-to-many class="com.metlife.ins.crdm.model.businessobjects.TerlogAppSess"/>
</set>

<set name="favorites" table="TERCRDM_USR_PRFR" lazy="true" inverse="true" cascade="save-update">
<key >
<column name="METNET_ID"/>
</key>
<one-to-many class="com.metlife.ins.crdm.model.businessobjects.TercrdmUsrPrfr"/>
</set>

<property name="grpNm" column="GRP_NM"
type="java.lang.String" length="20"
/>


Many more columns exist, but for shortness they are omited

Code between sessionFactory.openSession() and session.close():None

Full stack trace of any exception that occurs:

Name and version of the database you are using:DB2 8



[/b]

or



ideally in SQL this would be handled by a MINUS query, but HQL does not (yet) support MINUS / UNION operations.

Thanks for the example code Steve! I now have it runnning!