how to sort the result whith sort_no in relation table

chengshwu · **Joined:** Tue May 15, 2007 1:13 pm **Posts:** 2

there are three tables
1
table name :user
field:
id user_name

2
table name:user-role
field:
user_id role_id sort_no

3
table name:role
field:

id role_name

user.hbm.xml

Code:

<set name="roles" table="user-role">
              <key column="user_id"/>
              <many-to-many column="id" class="Role"/>
</set>

role.hbm.xml

Code:

<set name="users" table="user-role">
              <key column="role_id"/>
              <many-to-many column="id" class="User"/>
</set>

now I get an Id int Role,I want to select all Users,
The question is how to sort the result with the sort_no in table user_role

the java code as following:

Code:

public List<Object> sort(Integer id) {
    List applications = (List) getHibernateTemplate().execute(new
        HibernateCallback() {
      public Object doInHibernate(Session session) throws HibernateException,
          SQLException {
        Criteria criteria = session.createCriteria(User.class);
        criteria.createAlias("roles","role");
        criteria.add(Restrictions.eq("role.id", id));
        /*
        the question is here ! how to sort it with sort_no in table user-role
        */
        return criteria.list();
      }
    });
    return applications;
  }

sethrohit1977 · **Joined:** Mon Jan 22, 2007 10:32 am **Posts:** 101

Not sure if this can be done as hibernate is not aware of sort_no column (as per mappings in post).

You can have a wrapper class for the join table user_role that would contain these additional fields. Alternatively, you can use SQLProjection (along with alias) to project the sort_no column and then sort the results on this projection.