navigating n-m relation in hql

tombaeyens · **Posted:** Fri Apr 22, 2005 4:52 am

Hibernate version: 3.0 final

Hsqldb version: 1.7.3.3

Problem: the query generated by hibernate does not work on hsqldb.

Error msg: (below is a formatted version of the SQL-query) java.sql.SQLException: Column not found: ID in statement [select user0_.id as id, user0_.name as name0_ from USER user0_, ROLE role1_ where (role1_.name='admin' and (user0_.id in (select users2_.USER from USER_ROLES users2_ where role1_.id=users2_.ROLE)))]

Questions:
* is this a problem in my code, hsqldb or hibernate ? (ordered by likeliness:-)
* is there a way that hibernate generates join-queries instead of subselects for navigating n-m relations ? i only found the "in elements(collection-property)" to be used in where clauses, which generates a subselect.

The minimal example i created to reproduce the problem models the n-m relation between users and roles.

HQL query:

Code:

Query q = s.createQuery(
  "select u " +
  "from org.test.User as u," +
  "     org.test.Role as r " +
  "where r.name = 'admin'" +
  "  and u in elements(r.users)" );
q.list();

Generated SQL:

Code:

select 
  user0_.id as id, 
  user0_.name as name0_ 

from 
  USER user0_, 
  ROLE role1_ 

where 
( role1_.name='admin' 
  and (user0_.id in (select users2_.USER 
                     from USER_ROLES users2_ 
                     where role1_.id=users2_.ROLE
                    )
       )
)

Classes:

Code:

public class User {
  long id;
  String name;
  Set roles = new HashSet();
  ...
}

public class Role {
  long id;
  String name;
  Set users = new HashSet();
  ...
}

Mapping documents:

Code:

  <class name="org.test.User" 
         table="USER" >
    <id name="id"><generator class="native" /></id>
    <property name="name" />
    <set name="roles"
         table="USER_ROLES" 
         inverse="true"
         cascade="all" >
      <key column="USER" />
      <many-to-many class="org.test.Role" column="ROLE" />
    </set>
  </class>

  <class name="org.test.Role" 
         table="ROLE" >
    <id name="id"><generator class="native" /></id>
    <property name="name" />
    <set name="users"
         table="USER_ROLES">
      <key column="ROLE" />
      <many-to-many class="org.test.User" column="USER" />
    </set>
  </class>

marco-p · **Posted:** Fri Apr 22, 2005 6:36 am

You didn't specify the column attribute of the <id>. Does the User table have a ID column ?

tombaeyens · **Posted:** Fri Apr 22, 2005 6:41 am

yes, USER has an id column.

(by default hibernate takes the name of the id property as the column name)

regards, tom.

marco-p · **Posted:** Fri Apr 22, 2005 7:05 am

The only thing I can suggest is to avoid User as table/column name, since it's a reserved word in sql.

emmanuel · **Posted:** Fri Apr 22, 2005 11:06 am

how about

Code:

select distinct u from Role r join r.users u where r.name='admin'

And avoid tables named User, you'll be in trouble for portability.

tombaeyens · **Posted:** Fri Apr 22, 2005 11:30 am

exactly what i needed. thanks !