Problems with selection from join table with Criteria API

Hibernate ORM
1

Hi,

we have a problem with the CriteriaQuery to load some objects that have a set of itself as property.

This is what we have modeled:
grafik

Now what we want to do is to query a list of IdpEntites with all groups which have RoleAssignments and all users which have RoleAssignments over one or more groups. We only want to use the jpa criteria queries.

Note that IdpEntity can be a user or a group. If it is a group it has users as members else it can have groups.

In SQL this is what we want to do:

SELECT DISTINCT TOP 1000 
		e.id,
		e.display_name,
		e.id_type
  FROM [idp_entity] e,
  [user_group] g,
  [role_assignment] r
  WHERE g.group_id = r.group_id AND (e.id = g.group_id Or e.id = g.user_id)

The user_group table is the many to many relation between the IdpEntities.

What we tried so far:

  • join all relevant tables and then select the specific paths from idpEntity but this was not possible, because we had multiple of those in the joined table and failed to address them individually
  • subselects failed because of the sampe problem

Thanks in advance!

2

You need to add the entity mappings and the Criteria API you have tried as well as some test data which proves exactly what you need to select.

3

Thanks for your response!

Here some test data:

[IdpEntity table]	[created by @Entity annotation in IdpEntity.java]

| id | displayName | type |  
|  1 |   "aaa"     | USER |
|  2 |   "bbb"     | USER |
|  3 |   "ccc"     | GROUP|
|  4 |   "ddd"     | GROUP|

[user_group table]	[created by @JoinTable annotation in IdpEntity.java]

| user_id | group_id |
|    1    |    3     |
|    1    |    4     |
|    2    |    3     |

[role_assignment table] [created by @Entity annotation in RoleAssignment.java]

| role | group_id |
| ADMIN|    3     |
| USER |    4     |

The problem: select all users and groups which are admin

idea:

  1. select all group_id from role_assignment where role = ADMIN
  2. join user_group table to get members of admin group
  3. join member infos from idpEntity table

expected result:

| id | displayName | type |
|  3 |   "ccc"     | GROUP|
|  1 |   "aaa"     | USER |	[since these are members of admin group]
|  2 |   "bbb"     | USER |

And here our entity classes:

@Entity
@Table( name = CMConstants.DBConstants.TABLE_NAME_PREFIX + "idp_entities")
public class IdpEntity implements Serializable, Comparable<IdpEntity> {
	@Id
	@Column( name = "id")
	private String				id;

	@Enumerated( EnumType.STRING)
	@Column( name = "id_type")
	private IdpEntityType		idpEntityType;

	@Column( name = "display_name")
	private String				displayName;

	@ManyToMany(fetch = FetchType.EAGER)
	@JoinTable( name = CMConstants.DBConstants.TABLE_NAME_PREFIX + "user_group", joinColumns = @JoinColumn( name = "group_id"), inverseJoinColumns = @JoinColumn( name = "user_id"))
	private Set<IdpEntity>		groups;
	
	@ManyToMany(fetch = FetchType.EAGER)
	@JoinTable( name = CMConstants.DBConstants.TABLE_NAME_PREFIX + "group_user", joinColumns = @JoinColumn( name = "user_id"), inverseJoinColumns = @JoinColumn( name = "group_id"))
	private Set<IdpEntity>		members;

... getter and setter ...

@Entity
@Audited
@Table( name = CMConstants.DBConstants.TABLE_NAME_PREFIX + "role_assignment")
public class RoleAssignment implements Serializable {
	@Id
	@Enumerated( EnumType.STRING)
	@Column( name = "role")
	private Role				role;

	@Id
	@ManyToOne
	@JoinColumn( name = "group_id", referencedColumnName = "id")
	private IdpEntity			group;
... getter and setter ...

What we tried in jpa:

public List<IdpEntity> getAllIdpEntitiesWithRoles() {
		DatabaseSession dbSession = getDatabaseSession( currentTenant);
		try {
			JPAObjects<RoleAssignment, IdpEntity> jpaObject = getJPAObjects( dbSession, RoleAssignment.class, IdpEntity.class);
			CriteriaBuilder builder = dbSession.getSession().getCriteriaBuilder();
			CriteriaQuery<RoleAssignment> roleQuery = builder.createQuery( RoleAssignment.class);
			Root<RoleAssignment> queryRoot = roleQuery.from( RoleAssignment.class);
			Root<IdpEntity> idpQuery = roleQuery.from( IdpEntity.class);
			Join<RoleAssignment, IdpEntity> join = queryRoot.join( RoleAssignment_.group);
			SetJoin<IdpEntity, IdpEntity> join2 = idpQuery.join( IdpEntity_.members);
			// Now we have a join table with all informations about all groups and users with roles. The join table should look like this:
			// | role | g_id | g_displayName | g_type | m_id | m_displayName | m_type |
			// But how can we select the g_id column or the m_id column?
                } catch ( Exception e) {
                        ...
                }
}
4

This query cannot be written with JPQL or Criteria API.

You need a recursive CTE query if you want to traverse the User/Group hierarchy.

Therefore, you’re only option is to write a native SQL query, and if you need to build it dynamically, you can use either jOOQ or QueryDSL.