hibernate criteria api join table problem

Question

Hi, i cannot use sorting on join tables. Let me explain;

i have three tables. users, roles and user_roles. my JPA entities are User, UserRole, UserRolePK, Role.

|User    |     | UserRole |     | UserRolePK |     | Role |
|--------|     |----------|     --------------     --------
|id      |     | pk       |     | user       |     | id   |
|name    |                      | role       |     | name |

in fact the output that i want is: "SELECT * FROM user_roles ur JOIN users u ON u.ID = ur.UserID ORDER BY u.name;"

so i try to use hibernate criteria API.

CriteriaImpl criteria = (CriteriaImpl) session.createCriteria(UserRole.class);
criteria.addOrder(Order.asc("pk.user.name"));
List userRoles = criteria.list();

The error is could not resolve property: pk.user.name of: models.UserRole

how can i use criteria API on join tables?

Answer 1

Well, first of all I think we should see your entities mappings (Java Classes)

On the other hand, i'll assume your UserRole class has a reference to a User instance.

   @Entity
    @Table (name="UserRole")
    public class UserRole{

        User user;

        @OneToMany //correct mapping here
        public User getUser(){return this.user;}


    }

You should use createCriteria to navigate the association.

public List<UserRole> getRolesWithUser(){
    Criteria userRoleCriteria = session.createCriteria(UserRole.class);
    userRoleCriteria.createCriteria("user"); //joins the table.
    userRoleCriteria.addOrder("user.name"); //adds order

    return criteria.list();
}

The problem with this is that it will bring instances of UserRole, but you can navigate to User.

List<UserRole> userRoles = getRolesWithUser();

for (UserRole role:userRoles){

   User user = role.getUser();
   //do something with role , user
}

Take a look at the Criteria API. Its always helpful !

Answer 2

quite easy just use createCriteria on the associations:

Criteria c=session.createCriteria(User.class)
                  .createCriteria("roles")
                  .add(Restrictions.eq("name","test")
                  .list();

This would join two tables for the Entites User and their associated Collection "roles". and would return all users which have a role associated with themselves called "test".

hope that helped

Answer 3

If a class references other classes you can not simply access their properties in Restrictions and Orders. If will have to create an alias for the references objects and then use the alias to define Restrictions and Orders on the other objects.

Criteria criteria = (CriteriaImpl) session.createCriteria(UserRole.class);
criteria.createAlias("pk", "a1");
criteria.createAlias("a1.user", "a2");
criteria.addOrder(Order.asc("a2.name"));
List userRoles = criteria.list();

The alias definitions will create a join with the tables of the other classes. Only properties that are directly mapped to the table of the class or an alias can be used in Restrictions or Orders. If your class contains components they can be accessed directly without an alias.