I have a class with a mapping document as follows:
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="DigitalObject" table="OBJECT_DEFINITION">
<set name="childrenIds" table="OBJECT_PACK">
<key column="PARENT_OBJECT_DEFINITION_ID" not-null="true"/>
<element type="long" column="OBJECT_DEFINITION_ID"/>
</set>
</class>
</hibernate-mapping>
This is a set of scalar long values. I need to query the DigitalObject class by values of that collection. My first (successful) attempt was the following:
Code:
String hql = "from DbDigitalObject as do where :child_id in elements(do.childrenIds)";
Query q = getSession().createQuery(hql);
q.setLong("child_id", object.getObjectDefinitionId());
return (DbDigitalObject) q.uniqueResult();
The problem is that this produces SQL that makes use of a subselect for this simple query:
Code:
from OBJECT_DEFINITION dbdigitalo0_
where :1 in
(select childrenid1_.OBJECT_DEFINITION_ID from OBJECT_PACK childrenid1_
where dbdigitalo0_.OBJECT_DEFINITION_ID=childrenid1_.PARENT_OBJECT_DEFINITION_ID)
I would like for it to look like the following:
Code:
from OBJECT_DEFINITION dbdigitalo0_
inner join OBJECT_PACK childrenid1_ on dbdigitalo0_.OBJECT_DEFINITION_ID=childrenid1_.OBJECT_DEFINITION_ID
where childrenid1_.OBJECT_DEFINITION_ID=:1
However, I have been unable to find much of any documentation on the subject (see
http://opensource.atlassian.com/projects/hibernate/browse/HHH-869 for the best I could find). My attempt is as follows, resulting in the stack trace below.
Code:
from DbDigitalObject as do where do.childrenIds.value=:child_id
Can anyone suggest a way to do this?
Hibernate version: 3.0.5
Full stack trace of any exception that occurs:
org.hibernate.QueryException: cannot dereference scalar collection element: value [from com.pogo.db.digitalobject.DbDigitalObject as do where do.childrenIds.value=:child_id]
at org.hibernate.persister.collection.ElementPropertyMapping.toType(ElementPropertyMapping.java:33)
at org.hibernate.persister.collection.AbstractCollectionPersister.toType(AbstractCollectionPersister.java:1126)
at org.hibernate.hql.ast.FromElementType.getPropertyType(FromElementType.java:273)
at org.hibernate.hql.ast.FromElement.getPropertyType(FromElement.java:349)
at org.hibernate.hql.ast.DotNode.getDataType(DotNode.java:474)
at org.hibernate.hql.ast.DotNode.prepareLhs(DotNode.java:208)
at org.hibernate.hql.ast.DotNode.resolve(DotNode.java:166)
at org.hibernate.hql.ast.FromReferenceNode.resolve(FromReferenceNode.java:87)
at org.hibernate.hql.ast.FromReferenceNode.resolve(FromReferenceNode.java:83)
at org.hibernate.hql.ast.HqlSqlWalker.resolve(HqlSqlWalker.java:463)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.expr(HqlSqlBaseWalker.java:863)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.exprOrSubquery(HqlSqlBaseWalker.java:3713)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.comparisonExpr(HqlSqlBaseWalker.java:3190)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1405)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:599)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:404)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:201)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:151)
at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:189)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:130)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83)
at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:427)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:884)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:834)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:603)
Name and version of the database you are using: Oracle 9i