Is it not possible to use the Criteria API when querying on same association twice and using aliases?
I have a HQL version that works as expected, but I really like to use the Criteria API for this, to avoid a lot of string concatenation.
I have an association called "properties" from Person and I want to query for people with propery "a" having value "x" and property "b" having value "y".
Are sub selects better in this situation? Good query performance is important.
Hibernate version: 3.0rc1
Mapping documents:
Code:
<class name="Person" table="person">
...
<set name="properties" table="personproperty" inverse="true" cascade="all,delete-orphan">
<key column="owner"/>
<one-to-many class="PersonProperty"/>
</set>
...
Code:
<class name="PersonProperty" table="personproperty">
...
<many-to-one name="metadata" not-null="true"/>
<many-to-one name="owner" class="Person" access="field" not-null="true"/>
<property name="dateValue" type="date" access="field">
<column name="datevalue"/>
</property>
<property name="numericValue" type="double" access="field">
<column name="numericvalue"/>
</property>
<property name="stringValue" type="string" access="field">
<column name="stringvalue" length="255"/>
</property>
...
Code:
<class name="PropertyMetadata" table="propertymetadata">
...
<property name="code">
<column name="code" length="20" not-null="true"/>
</property>
<property name="description">
<column name="description" length="100" not-null="true"/>
</property>
This code works, it is using HQL.
Code:
PropertyMetadata md1 = getMetadata(session, "age", PropertyMetadata.NUMERIC);
PropertyMetadata md2 = getMetadata(session, "date", PropertyMetadata.DATE);
Collection list = session.createQuery("select distinct p from Person as p
join p.properties as a join p.properties as b
where (a.metadata = :md1 and a.numericValue = :num) and (b.metadata = :md2 and b.dateValue <= :date)")
.setEntity("md1", md1).setEntity("md2", md2).setInteger("num",42).setDate("date", new Date()).list();
This code does not work, it uses the Criteria API but I'm note sure I got it right.
Code:
PropertyMetadata md1 = getMetadata(session, "age", PropertyMetadata.NUMERIC);
PropertyMetadata md2 = getMetadata(session, "date", PropertyMetadata.DATE);
Criteria crit0 = session.createCriteria(Person.class);
crit0.setResultTransformer(new DistinctRootEntityResultTransformer());
Criteria crit1 = crit0.createAlias("properties", "p1");
Criteria crit2 = crit0.createAlias("properties", "p2");
crit1.add(Restrictions.eq("metadata", md1));
crit1.add(Restrictions.eq("numericValue", new Double(42)));
crit2.add(Restrictions.eq("metadata", md2));
crit2.add(Restrictions.le("dateValue", new Date()));
Collection list = crit0.list();
Full stack trace of any exception that occurs:Code:
org.hibernate.QueryException: duplicate association path: properties
at org.hibernate.loader.criteria.CriteriaQueryTranslator.createAssociationPathCriteriaMap(CriteriaQueryTranslator.java:138)
at org.hibernate.loader.criteria.CriteriaQueryTranslator.<init>(CriteriaQueryTranslator.java:80)
at org.hibernate.loader.criteria.CriteriaLoader.<init>(CriteriaLoader.java:69)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1228)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:299)
at research.test.PropertyTest.testFindMultiplePropertyCriteria(PropertyTest.java:187)
...
Name and version of the database you are using: MySQL 4.1
The generated SQL (show_sql=true):
This is the SQL generated from the HQL example that works. The failing example does not output any SQL since it fails early.
select distinct person0_.id as id, person0_.first_name as first2_9_, person0_.middle_name as middle3_9_,
person0_.last_name as last4_9_ from person person0_ inner join personproperty properties1_ on person0_.id=properties1_.owner
inner join personproperty properties2_ on person0_.id=properties2_.owner where (properties1_.metadata=?
and properties1_.numericvalue=? and properties2_.metadata=? and properties2_.datevalue<=?)
Login
FAQ
Search
