QueryDSLNullPointerexceptionleftjoin無關表

我有兩張不相關的表。下面是物體類。

用戶分期

@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;

角色表

@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;

}

我正在嘗試通過在用戶名列上使用左連接來撰寫以下查詢。只要 UsersStaging 表有謂詞（exp），查詢就可以正常作業。但是，如果我為 userRoles 表 (roleExp) 添加謂詞，則查詢失敗。

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]

uj5u.com熱心網友回復：

我能夠解決它。問題是角色欄位是一個json 型別的列，并且Hibernate 還不支持JSON_CONTAINS函式。

為了解決這個問題，我實作了 MetadataBuilderContributor 介面。

public class SQLFunctionContributor implements MetadataBuilderContributor {
    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction("json_contains_key",
                new SQLFunctionTemplate(BooleanType.INSTANCE, "JSON_CONTAINS(?1, JSON_QUOTE(?2))"));
    }

}

在 application.properties 中添加以下行，以便 Hibernate 可以注冊 contrib SQL 函式。

spring.jpa.properties.hibernate.metadata_builder_contributor=com.xxx.xxx.utils.SQLFunctionContributor

我以以下方式修改了我的查詢，沒有問題。

BooleanExpression roleExp = Expressions.booleanTemplate("json_contains_key({0}, {1})", userRolesPath.roles,
                        Expressions.constant(criteria.getValue())).isTrue();

標籤：弹簧靴 休眠 jpa 查询DSL

上一篇：hibernate.MappingException：未知物體

下一篇：Flutter中MasonryGridView內的ListView