Hibernate criteria api join table problem

Question

I cannot use sorting for connection tables. Let me explain:

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

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

actually the output I want is: "SELECT * FROM user_roles ur JOIN users u ON u.ID = ur.UserID ORDER BY u.name;"

so I'm trying to use the sleep criteria criteria API.

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

Error failed to resolve property: pk.user.name: models.UserRole

How can I use the criteria API for connection tables?

Answer 1

If a class refers to other classes, you cannot simply access their properties in "Constraints and Orders". You will need to create an alias for reference objects, and then use an alias to define restrictions and orders for 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(); 

Alias ​​definitions will create a connection to tables of other classes. Only properties that are directly mapped to a class table or aliases can be used in restrictions or orders. If your class contains components, they can be accessed directly without an alias.

Answer 2

Well, first of all, I think we should see object mappings (Java classes)

On the other hand, I assume that your UserRole class has a reference to the user instance.

  @Entity @Table (name="UserRole") public class UserRole{ User user; @OneToMany //correct mapping here public User getUser(){return this.user;} } 

You can use createCriteria to move around 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 is that it will bring up instances of UserRole , but you can go 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 useful!

Answer 3

just use createCriteria for associations:

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

This will combine the two tables for the Entites User and their associated "roles" collection. and returns all users who have a role associated with them called "test".

hope this helped