JPA handling of null values in construct query with selectcase

Question

I have three Entities and a ternary relationship.

    public class User{}
    public class UserGroup{}
    public class Role{}
    public class User2Role2Group{
        @ManyToOne(fetch = FetchType.EAGER)
        @JoinColumn(name = "USER_ID", nullable = false)
        private User user;

        @ManyToOne(fetch = FetchType.EAGER)
        @JoinColumn(name = "ROLE_ID", nullable = false)
        private Role role;

        @ManyToOne(fetch = FetchType.EAGER)
        @JoinColumn(name = "USER_GROUP_ID")
        private UserGroup group;
    }

The group of User2Role2Group is nullable.

This is my testcase data.

+----------+----------------+---------+---------+ | URG_ID | USER_GROUP_ID | USER_ID | ROLE_ID | +----------+----------------+---------+---------+ | urg_id1 | user_group_id1 | userId | roleID1 | | urg_id2 | user_group_id2 | userId | roleID1 | | urg_id3 | null | userId | roleID1 | | urg_id4 | user_group_id2 | userId | roleID2 | | urg_id5 | user_group_id1 | userId | roleID2 | +----------+----------------+---------+---------+

Now I want to construct a list of value objects "RoleGroup", which contain the UserGroups and Roles belonging to a User.

This is the code I have written. When the group is not null, I take the group found in the database, otherwise I construct a nullliteral for the UserGroup.

    final CriteriaBuilder builder = em.getCriteriaBuilder();
    final CriteriaQuery<RoleGroup> criteria = builder.createQuery(RoleGroup.class);
    final Root<User2Role2Group> from = criteria.from(User2Role2Group.class);
    criteria.select(builder.construct(RoleGroup.class, from.get(User2Role2Group_.role),
            builder.selectCase()
                    .when(builder.isNotNull(from.get(User2Role2Group_.group)), from.get(User2Role2Group_.group))
                    .otherwise(builder.nullLiteral(UserGroup.class))));
    criteria.where(builder.equal(from.get(User2Role2Group_.user).get(User_.userId), userId));
    final TypedQuery<RoleGroup> query = em.createQuery(criteria);
    return query.getResultList();

The problem I am facing is when the group is indeed null, as in my testdata, I only get four RoleGroups as return.

If I switch the when and otherwise cases around I even get a SerializationException.

Am I misunderstanding selectCase?

Answer 1

Using User2Role2Group_.group creates an inner join which by definition only joins if the USER_GROUP_ID field contains a non null value.

Add an explicit join to your query and mark it as a left join to get null values too. I haven't tested the code, but something like this should get you going.

Join<User2Role2Group, Group> join = from.join(User2Role2Group_.group, JoinType.LEFT);