QueryDSL关联无关联表左连接时添加关联表谓词触发空指针异常

实体类定义 #

UsersStaging表实体 #

@Entity
@Table(name = "users_staging")
@Data
public class Usersstaging implements Serializable{
    private static final long serialVersionUID = 1L;
    
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "native")
    @GenericGenerator(name = "native", strategy = "native")
    @Column(name = "id", updatable = false, nullable = false)
    private Integer id;
    
    @Column(name = "employee_id", length = 10)
    private String employeeId;
    
    @Column(name="user_name")
    private String userName;
    
    @Column(name = "email")
    private String email;
}

UserRoles表实体 #

@Entity
@Table(name = "user_roles")
@Data
@TypeDefs({ @TypeDef(name = "json", typeClass = JsonType.class) })
public class UserRoles implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @Column(name = "employeeId", length = 10)
    private String employeeId;

    @Column(name = "username")
    private String username;

    @Type(type = "json")
    @Column(name = "roles", columnDefinition = "json")
    private List<String> roles;

}

问题场景 #

两张无关联的数据库表，使用QueryDSL基于username字段做左连接查询。仅添加UsersStaging表的谓词时查询正常，但加入UserRoles表的谓词（判断roles包含"SUPERADMIN"）后触发空指针异常。

查询代码 #

JPAQuery<UsersStaging> query = new JPAQuery<>(entityManager);
QUsersStaging usersStagingPath = QUsersStaging.usersStaging;
QUserRoles userRolesPath = QUserRoles.userRoles;
BooleanExpression exp = usersStagingPath.legalFirstName
                .containsIgnoreCase(criteria.getValue());
BooleanExpression roleExp = userRolesPath.roles.any().eq("SUPERADMIN");
return query
        .select(Projections.constructor(UsersStagingDTO.class, usersStagingPath.employeeId,
                usersStagingPath.userName, usersStagingPath.email, usersStagingPath.isActive,
                usersStagingPath.legalFirstName, usersStagingPath.legalLastName, usersStagingPath.legalFullName,
                userRolesPath.roles))
        .from(usersStagingPath).leftJoin(userRolesPath).on(usersStagingPath.userName.eq(userRolesPath.username))
        .where(exp.and(roleExp)).limit(10).fetch();

异常信息 #

java.lang.NullPointerException: null
    at org.hibernate.hql.internal.ast.HqlSqlWalker.createFromJoinElement(HqlSqlWalker.java:432) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.joinElement(HqlSqlBaseWalker.java:4007) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3793) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:3671) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:746) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:602) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.collectionFunctionOrSubselect(HqlSqlBaseWalker.java:5020) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.comparisonExpr(HqlSqlBaseWalker.java:4713) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:2180) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:2108) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:841) ~[hibernate-core-5.6.10.Final.jar:5.6.10.Final]

解决方案 #

问题核心是左连接后直接在where子句过滤关联表数据，既破坏了左连接的特性，又因Hibernate处理JSON数组谓词时遇到null导致空指针。

方案1：将角色过滤条件移至左连接的on子句 #

保留左连接特性的同时过滤关联的UserRoles数据：

JPAQuery<UsersStaging> query = new JPAQuery<>(entityManager);
QUsersStaging usersStagingPath = QUsersStaging.usersStaging;
QUserRoles userRolesPath = QUserRoles.userRoles;
BooleanExpression exp = usersStagingPath.legalFirstName
                .containsIgnoreCase(criteria.getValue());
BooleanExpression roleExp = userRolesPath.roles.any().eq("SUPERADMIN");
return query
        .select(Projections.constructor(UsersStagingDTO.class, usersStagingPath.employeeId,
                usersStagingPath.userName, usersStagingPath.email, usersStagingPath.isActive,
                usersStagingPath.legalFirstName, usersStagingPath.legalLastName, usersStagingPath.legalFullName,
                userRolesPath.roles))
        .from(usersStagingPath)
        .leftJoin(userRolesPath)
            .on(usersStagingPath.userName.eq(userRolesPath.username).and(roleExp))
        .where(exp)
        .limit(10)
        .fetch();

方案2：给角色谓词添加非空判断 #

若必须在where子句过滤，需先判断关联表对象不为null：

BooleanExpression roleExp = userRolesPath.isNotNull().and(userRolesPath.roles.any().eq("SUPERADMIN"));

方案3：改用内连接（若需求允许） #

如果只需要保留有对应SUPERADMIN角色的用户，直接用内连接更高效，无需额外判空：

.from(usersStagingPath)
.join(userRolesPath)
    .on(usersStagingPath.userName.eq(userRolesPath.username))
.where(exp.and(roleExp))

内容的提问来源于stack exchange，提问作者Ladu anand