Hello,
I'm not sure how to write a Criteria query that would use multiple inner joins on the same table.
Here is a mapping on which this problem is based:
Code:
<class name="Item" table="ITEMS">
<id name="id" type="long" column="ID">
<generator class="increment" />
</id>
<property name="name" type="string" column="Name" length="50" unique="true" />
<set name="attributes" inverse="true">
<key column="ItemID" />
<one-to-many class="Attribute" />
</set>
</class>
<class name="Attribute" table="ATTRIBUTES">
<composite-id>
<key-many-to-one name="item" column="ItemID" class="Item" />
<key-property name="name" column="Name" type="string" length="100"/>
</composite-id>
<property name="value" type="java.lang.String">
<column name="Value" length="1024" default="null" not-null="false"/>
</property>
</class>
The ATTRIBUTES table contains rows (IdemId, Name, Value) such as:
(1, 'X', 'A'),
(1, 'Y', 'A'),
(2, 'X', 'A'),
(2, 'Y', 'B'),
(3, 'X', 'A'),
(3, 'Y', 'C'),
(4, 'X', 'B'),
(4, 'Y', 'A'),
(5, 'X', 'B'),
(6, 'Y', 'B'),
(7, 'X', 'B')
I would like to be able to select Items based on what attributes and attribute values they
have. This would be something representing "find all the Items that have an attribute X with value A and an attribute Y with value B".
This MySQL query seems to produce the required result:
Code:
SELECT DISTINCT (I1.ID) FROM ITEMS I1
INNER JOIN ATTRIBUTES T1 ON T1.ItemID = I1.ID
INNER JOIN ATTRIBUTES T2 ON T2.ItemID = I1.ID
WHERE (T1.Name LIKE 'X' AND T1.Value LIKE 'A')
AND (T2.Name LIKE 'Y' AND T2.Value LIKE 'B')
GROUP BY I1.ID
I've tried to express a similar query using Hiberate's Criteria (based on section 15.4 of
the manual), but I can't get it to work.
I've tried the following (and a few variants):
Code:
Criteria criteria =
session.createCriteria(Item.class)
.createAlias("attributes","att1")
.createAlias("attributes","att2")
.add(Restrictions.conjunction()
.add(Restrictions.conjunction().add(Restrictions.like("att1.name", "X")).add(Restrictions.like("att1.value","A")))
.add(Restrictions.conjunction().add(Restrictions.like("att2.name", "Y")).add(Restrictions.like("att2.value","B")))
);
However, criteria.list() produces an exception:
org.hibernate.QueryException: duplicate association path: attributes
Is it possible to express this type of queries using criteria? Is there another way to achieve this?
Thank you,
Bruno.
Login
FAQ
Search
