I have four tables (mappings below):
DATABASE
ORGANIZATION
SITE_USER
SITE_USER_ORGANIZATION
There is a one-to-many relationship between ORGANIZATION and DATABASE, and a many-to-many relationship between ORGANIZATION and SITE_USER. I want to construct an HQL query that returns all of the databases for a given site user. In SQL, I would write:
Code:
select d.*
from database d, site_user_organization suo
where d.organization_id = suo.organization_id
and suo.site_user_id = :suid;
What is the right way to write an HQL query to retrieve the same result? I've been through the documentation repeatedly; where in the docs would I get a clue as how to do this? Any help with this is much appreciated....
Hibernate version is 2.1.2
Database is Oracle9i
Mappings:DATABASE
Code:
<hibernate-mapping package="com.twocoast.tcsc.model">
<class name="Database" table="DATABASE">
<id name="databaseId" column="DATABASE_ID" type="long" unsaved-value="0">
<generator class="sequence">
<param name="sequence">OBJECT_SEQ</param>
</generator>
</id>
<property name="databaseName" column="DATABASE_NAME"/>
</hibernate-mapping>
ORGANIZATION
Code:
<hibernate-mapping package="com.twocoast.tcsc.model">
<class name="Organization" table="SUPPORTED_ORGANIZATION">
<id name="organizationId" column="ORGANIZATION_ID" type="long" unsaved-value="0">
<generator class="sequence">
<param name="sequence">OBJECT_SEQ</param>
</generator>
</id>
<property name="organizationShortName" column="ORGANIZATION_SHORT_NAME"/>
<set name="databases" inverse="true" lazy="true" cascade="all-delete-orphan" order-by="database_name asc">
<key column="organization_id"/>
<one-to-many class="Database"/>
</set>
</class>
</hibernate-mapping>
SITE_USER
Code:
<hibernate-mapping package="com.twocoast.tcsc.model">
<class name="SiteUser" table="SITE_USER">
<id name="siteUserId" column="SITE_USER_ID" type="long" unsaved-value="0">
<generator class="sequence">
<param name="sequence">SITE_USER_SEQ</param>
</generator>
</id>
<property name="username" column="USERNAME"/>
<set name="organizations" table="SITE_USER_ORGANIZATION" lazy="true">
<key column="SITE_USER_ID"/>
<many-to-many column="ORGANIZATION_ID" class="Organization"/>
</set>
</class>
</hibernate-mapping>
Login
FAQ
Search
