I'm still fairly new to Hibernate and I've run into a problem building a query. I'm using Hibernate 4.1.3.
I'm trying to select all of the items in a table based on the value of one of the elements of the composite-id. Here are the relevant code snippets:
// Coupon
Code:
<hibernate-mapping>
<class name="Coupon" table="coupon" catalog="myCatalogue">
<id name="couponId" type="int">
<column name="coupon_id" />
<generator class="increment" />
</id>
<many-to-one name="asset" class="Asset" lazy="false" update="false" insert="false" fetch="select">
<column name="asset_id" not-null="true" />
<column name="client_id" length="20" not-null="true" />
</many-to-one>
.....
</hibernate-mapping>
//Asset
Code:
<hibernate-mapping>
<class name="Asset" table="asset" catalog="myCatalogue">
<composite-id name="id" class="AssetId">
<key-property name="assetId" type="int">
<column name="asset_id" />
</key-property>
<key-property name="clientId" type="string">
<column name="client_id" length="20" />
</key-property>
</composite-id>
...
<set name="coupons" table="coupon" inverse="true" lazy="true" fetch="select">
<key>
<column name="asset_id" not-null="true" />
<column name="client_id" length="20" not-null="true" />
</key>
<one-to-many class="Coupon" />
</set>
</class>
</hibernate-mapping>
In plain SQL, I'm trying to do this:
"Select * from Coupon where asset_id in (select asset_id from Asset where client_id = ?)"
However that doesn't work (or at least I can't manage to make it work). I've tried many variations of this. I've tried things such as:
Code:
Query q = hSession.createQuery("from Coupon c where c.asset.id.assetId in (select a.id.assetId from Asset a where a.id.clientId = '" + client.getClientId() + "')");
Query q = hSession.createQuery("from Coupon c where c.asset.id.assetId in (select a.assetId from Asset a where a.clientId = '" + client.getClientId() + "')");
Query q = hSession.createQuery("from Coupon where (asset.id.assetId, asset.id.clientId) in (select assetId, clientId from Asset where clientId = '" + client.getClientId() + "')");
How do I properly structure this query?
Login
FAQ
Search