Hi there,
I'm having a small but annoying issue with HQL, subqueries and passing in an entity object as query parameter.
Suppose I have two classes: User and Role, where a user points to a set of roles. Given a specific role I would now like to find all users that have that role assigned.
I would expect the following code to give me that result:
Code:
Role r;
// ...
Query q = s.createQuery("from " + User.class.getName() + " user where :role in (select user1.roles from " + User.class.getName() + " user1 where user1 = user)");
q.setParameter("role", r);
Unfortunately Hibernate doesn't create valid SQL from that HQL query:
Code:
Hibernate: select user0_.id as id0_, user0_.user_name as user2_0_, user0_.default_role_id as default3_0_ from c_user user0_ where ? in (select . from c_user user1_, c_users_roles roles2_, c_role role3_ where user1_.id=roles2_.user_id and roles2_.role_id=role3_.id and user1_.id=user0_.id)
SQL Error: 936, SQLState: 42000
ORA-00936: missing expression
(Note the select . from in the subquery)
When I rephrase my query to use the role id instead of the role itself, everything works fine:
Code:
// ...
Query q = s.createQuery("from " + User.class.getName() + " user where :roleid in (select user1.roles.id from " + User.class.getName() + " user1 where user1 = user)");
q.setParameter("roleid", r.getId());
My problem here is, that I actually don't want to expose the id property on my Role class because it is an auto-generated surrogate key and callers should not need to worry about it. Beyond that it is quite unintuitive from the Java perspective to have to worry about primary keys at that level.
So my question: Is that an expected behaviour of HQL? And if so, why does passing in entity objects as parameters work fine in other situations?
Cheers,
Volker
Environment:
Java 1.5
Hibernate 3.1
Oracle XE 10g
Mapping file:
Code:
<hibernate-mapping package="net.morgenwind.criteria.model">
<class name="User" table="c_user">
<id name="id" column="id" unsaved-value="-1">
<generator class="sequence">
<param name="sequence">c_user_seq</param>
</generator>
</id>
<property name="userName">
<column name="user_name" length="64" not-null="true"/>
</property>
<set name="roles" table="c_users_roles">
<key column="user_id"/>
<many-to-many column="role_id" class="Role"/>
</set>
<many-to-one name="defaultRole" column="default_role_id" class="Role"/>
</class>
<class name="Role" table="c_role">
<id name="id" column="id" unsaved-value="-1">
<generator class="sequence">
<param name="sequence">c_role_seq</param>
</generator>
</id>
<property name="roleName">
<column name="role_name" length="64" not-null="true"/>
</property>
</class>
</hibernate-mapping>
Login
FAQ
Search