How to read data using Hibernate after many to many mapping

Question

I have two tables Role and Right, and hibernate created 1 table role_right as a result of Many to Many mapping.

Now I want to get all RightName corresponding to RoleName seperately like

admin--> create user, data entry, login, logout

data entry operator--> data entry, logout, login

data analyst--> data analysis, logout, login

Help me to solve this. here is my code

UserRole class

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

    @Id @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name="RoleID", unique=true, nullable=false)
    private int roleId;
    @Column(name="RoleName", nullable=false)
    private String roleName;
    @Column(name="RoleRight")
    private String roleRight;

    @ManyToMany
    @JoinTable(name="Role_Right",joinColumns=@JoinColumn(name="Role_Id"),
            inverseJoinColumns=@JoinColumn(name="Right_Id"))
    private Collection<UserRights> userRoleMapping = new ArrayList<UserRights>();

    public Collection<UserRights> getUserRoleMapping() {
        return userRoleMapping;
    }

    public void setUserRoleMapping(Collection<UserRights> userRoleMapping) {
        this.userRoleMapping = userRoleMapping;
    }

    public String getRoleRight() {
        return roleRight;
    }

    public void setRoleRight(String roleRight) {
        this.roleRight = roleRight;
    }

    public int getRoleId() {
        return roleId;
    }

    public void setRoleId(int roleId) {
        this.roleId = roleId;
    }

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    } 
}

UserRole class

@Entity
@Table(name="UserRights")
public class UserRights implements Serializable {

    @Id @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name="RightID", unique=true, nullable=false)
    private int rightId;
    @Column(name="RightName", nullable=false)
    private String rightName;

    @ManyToMany(mappedBy="userRoleMapping")
    private Collection<UserRole> userRightsMapping = new ArrayList<UserRole>();

    public Collection<UserRole> getUserRightsMapping() {
        return userRightsMapping;
    }

    public void setUserRightsMapping(Collection<UserRole> userRightsMapping) {
        this.userRightsMapping = userRightsMapping;
    }

    public int getRightId() {
        return rightId;
    }

    public void setRightId(int rightId) {
        this.rightId = rightId;
    }

    public String getRightName() {
        return rightName;
    }

    public void setRightName(String rightName) {
        this.rightName = rightName;
    } 
}

I tried some thing like this

session.getUserRightsMapping()

this query is very close to what i want

String query = "select Role_Id, RightName FROM userrights join role_right on " + "userrights.RightID = role_right.Right_Id";

but don't know what to do further

Answer 1

First of all, to make things clearer, I would start by renaming your entities and fields:

• UserRole, with a collection of rights named... rights
• UserRight (without s), with a collection of roles named... roles

Now, the simplest thing to do is just to get all the roles, and to fetch the rights of each role in a single query:

String hql = "select role from UserRole role left join fetch role.rights";
List<UserRole> roles = session.createQuery(hql).list()
// now you can iteratr through the roles, and for each role, iterate through its rights

If you really just want to have all the role_name/right_name associations, then use the following code:

String hql = "select role.name, right.name from UserRole role"
             + " inner join role.rights right";
List<Object[]> rows = session.createQuery(hql).list();

And each Object[] element of the returned list contains two elements: the role name and the right name. Note that the roles which don't have any right are not returned by this query.

All this is very well explained in the Hibernate documentation. Read it.