CODEWITHSUNDEEP
javahibernate
Geo Thomas
Geo Thomas

Reputation: 1159

hibernate query combining 3 tables to get object corresponding to one table

I have 3 tables named role,permission and role_permission and their corresponding pojos 

    public class RoleTO 
{

    private int roleId;
    private String roleName;
    private int applicationId;
}

,

  public class RolePermissionsTO 
{
    private int role_permissionId;
    private int roleId;
    private int permissionId;
}

,

public class PermissionTO 
{

    private int permissionId;
    private String permission;
    private String permissionDesc;
    private int applicationId;
}

Now I have a method in my dao class 

public List<PermissionTO> getPermissions(int applicationId, String roleName)throws HibernateException
{
     Session session = getCurrentSession();

    String hql = "SELECT P FROM PermissionTO P,Role R where P.applicationId=R.applicationId and P.applicationId=:applicationId and P.roleName=:roleName";
    Query query = session.createQuery(hql);
    query.setParameter("applicationId",applicationId);
    query.setParameter("roleName",roleName);

    return query.list();

}

But I need these three tables to be connected so that I get all the permissions in the permission class for the given application Id and the roleName linking the three tables.

Can anybody help

Upvotes: 0

Views: 184

Answers (2)

Gautam Viradiya
Gautam Viradiya

Reputation: 513

SELECT P FROM PermissionTO P,Role R where P.applicationId=R.applicationId AND R.applicationId=:applicationId AND R.roleName=:roleName";

OR

Criteria cr = session.createCriteria(PermissionTO.class, "permissionTo"); cr.createAlias("permissionTo.permissionId", "rolePermissionsTo"); // inner join by default cr.createAlias("rolePermissionsTo.roleId", "roleTo"); cr.add(Restrictions.eq("roleTo.applicationId", applicationId)); // applicationId set value to parameter cr.add(Restrictions.eq("roleTo.roleName", roleName)); // roleName set value to parameter

List list = cr.list();

Upvotes: 2

user4458270
user4458270

Reputation:

I also have a similar issue, but couldnt find a solution.

but in your query I find that you should be using some constraint to connect the RoleTO to RolePermissinTo using RoleId and ApplicationId and then use this result to get the corresponding PermissionTO list for each given role. I am not expert with queries, i guess this should be the logic

Upvotes: 0

Related Questions