Criteria API join problem: entity which does not hold relationship column can not "join"

Question

The required entities are roughly as follows:

@Entity
@Table(name = "tb_users")
public class User {

    @Id
    @GeneratedValue
    private UUID userId;

    // Omitted other fields and getters/setters ...
}

@Entity
@Table(name = "tb_groups")
public class Group {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer groupId;

    // ....
}

@Entity
@Table(name = "tb_group_members")
public class GroupMember {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id")
    private User user;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "group_id")
    private Group group;

    // getters & setters ...
}

This is a many-to-many relationship with an intermediate table:

Group <----- 1:N -----> GroupMember <----- N:1 -----> User

The query I want to achieve (through Criteria API) is basically the same as the following JPQL:

select g from Group g join GroupMember gm on g = gm.group where gm.user = ? and  ...or whatever...

I try to construct the CriteriaQuery as follows:

User userToQuery = ...;
// ....
CriteriaQuery<Group> query = criteriaBuilder.createQuery(Group.class);
Root<Group> groupRoot = query.from(Group.class);
Root<GroupMember> gmRoot = query.from(GroupMember.class);
groupRoot.join(...).on(criteriaBuilder.equal(groupRoot, gmRoot.get("group"))); // I can't find a suitable join() method to specify the relationship
Predicate predicate = criteriaBuilder.equal(gmRoot.get("user"), userToQuery);
// ......

Since the Group entity does not hold an association relationship, I do not know how to call the join(...) method.

I must declare the association in reverse, that is, from the GroupMember entity:

CriteriaQuery<GroupMember> query = criteriaBuilder.createQuery(GroupMember.class);
Root<GroupMember> gmRoot = query.from(GroupMember.class);
gmRoot.join("group");
// ......

Does JPA's Criteria API have this restriction? How does an entity that does not contain an association field join with other entity?

Does your JPQL work? Have you tried a JPQL like SELECT gm.group FROM GroupMember gm WHERE gm.user=:user? I think this, if it works indeed, is easier to translate to criteria API. — Nikos Paraskevopoulos
Does this answer your question? JPA - left join 2 tables without association — Jens Schauder
Thanks very much~ Jens & Nikos! The answer uses JPQL directly and I have given my JPQL, it works fine. I hope to use the Creteria API to solve this problem, because this can solve the problem of concat query parameters — Aslan
I have given up using the Creteria APIs. I will try to use Querydsl for more flexible queries. — Aslan

Aslan Aslan · Accepted Answer · 2020-05-13T09:34:28

Implementation based on Querydsl:

private EntityManager em;

......

User user = ...; // User to query (may be null)
...
QGroup qGroup = QGroup.group;
QGroupMemeber qGMember = QGroupMember.groupMember;

JPAQueryFactory queryFactory = new JPAQueryFactory(em);
JPAQuery<Group> query = (user == null) ?
        queryFactory.selectFrom(qGroup) :
        queryFactory.select(qGroup).from(qGroup).join(qGMember).on(qGroup.eq(qGMember.group));

BooleanBuilder builder = new BooleanBuilder();
if (user != null) {
    builder.and(qGMember.user.eq(user));
}
// if (...) {
//     Other conditions that need to be added to the where clause
// }
query.where(builder);
// Paging condition or something
// ....

List<Group> result = query.fetch(); // search result

Criteria API join problem: entity which does not hold relationship column can not "join"

1 Answers