时间戳

首页 > 解决方案 > Spring Boot QueryDSL BooleanExpression - “OR”条件取决于表中的值

spring-boot - Spring Boot QueryDSL BooleanExpression - “OR”条件取决于表中的值

问题描述

我使用 Spring Boot 和 QueryDSL 来组合 sql 查询和谓词。问题是我必须创建一个谓词来根据电子邮件从表中获取数据但是：

  1. 电子邮件可以在 Freight.sender.email 中
  2. 或在 Freight.message.senderAddress

其中 Freight、Message、Sender 当然是表。在表 Freight 中，我们可以有空的 sender_id 或 message_id 并且根据此我必须通过电子邮件从Freight.sender.emailOR获取行Freight.message.senderAddress（如果Freight.sender为空）

是否可以创建这样一个谓词，将来自请求查询参数的电子邮件与 Freight.sender.email 进行比较，并且仅当 Freight.sender.email 不存在时，我的谓词才会在 Freight.message.senderAddress 中搜索电子邮件

public Predicate build(Map<String, String> filters) {
        return new OptionalBooleanBuilder(Expressions.asBoolean(true).isTrue())
                .notNullAnd(qFreight.loadingAddress::containsIgnoreCase, filters.get(LOADING_ADDRESS))
                .notNullAnd(qFreight.unloadingAddress::containsIgnoreCase, filters.get(UNLOADING_ADDRESS))
                .notNullAnd(qFreight.loadingDate.eq(filters.get(LOADING_DATE) != null ? LocalDate.parse(filters.get(LOADING_DATE)) : now()), filters.get(LOADING_DATE))
                .notNullAnd(qFreight.unloadingDate.eq(filters.get(UNLOADING_DATE) != null ? LocalDate.parse(filters.get(UNLOADING_DATE)) : now()), filters.get(UNLOADING_DATE))
//MY ATTEMPT - NOT WORKING:
        .notNullAnd(qFreight.sender.email.eq(filters.get(SENDER_EMAIL)).or(qFreight.emailMessage.senderAddress.eq(SENDER_EMAIL)), filters.get(SENDER_EMAIL))
                .build();
}

public class OptionalBooleanBuilder {

    private BooleanExpression predicate;

    public OptionalBooleanBuilder(BooleanExpression predicate) {
        this.predicate = predicate;
    }

    public <T> OptionalBooleanBuilder notNullAnd(Function<T, BooleanExpression> expressionFunction, T value) {
        if (nonNull(value)) {
            return new OptionalBooleanBuilder(predicate.and(expressionFunction.apply(value)));
        }
        return this;
    }

    public BooleanExpression build() {
        return predicate;
    }

    public <T>OptionalBooleanBuilder notNullAnd(BooleanExpression expression, T value) {
        if(nonNull(value)){
            return new OptionalBooleanBuilder(predicate.and(expression));
        }
        return this;
    }
}

更新

根据以下建议：

private Predicate addPredicate(OptionalBooleanBuilder builder, String email) {
    if (nonNull(email)) {
        return builder.notNullAnd(qFreight.sender.email.coalesce(qFreight.emailMessage.senderAddress.eq(email)).asBoolean(), email).build();

    return builder.build();
}

我得到错误：

    antlr.NoViableAltException: unexpected AST node: (
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:2169) ~[hibernate-core-5.4.17.Final.jar:5.4.17.Final]
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:2089) ~[hibernate-core-5.4.17.Final.jar:5.4.17.Final]
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:827) ~[hibernate-core-5.4.17.Final.jar:5.4.17.Final]
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:621) ~[hibernate-core-5.4.17.Final.jar:5.4.17.Final]
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:325) ~[hibernate-core-5.4.17.Final.jar:5.4.17.Final]
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:273) ~[hibernate-core-5.4.17.Final.jar:5.4.17.Final]
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:276) ~[hibernate-core-5.4.17.Final.jar:5.4.17.Final]
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:192) ~[hibernate-core-5.4.17.Final.jar:5.4.17.Final]


2020-12-24 00:45:35.535 ERROR 16068 --- [nio-9090-exec-8] p.a.m.s.filter.JwtAuthorizationFilter    : Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: ( near line 3, column 52 [select freight
from pl.appwise.mtf.freight.domain.model.Freight freight
where ?1 = ?1 and freight.user.id = ?2 and coalesce(freight.sender.email, freight.emailMessage.senderAddress = ?3)
order by freight.loadingDate desc]; nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: ( near line 3, column 52 [select freight
from pl.appwise.mtf.freight.domain.model.Freight freight
where ?1 = ?1 and freight.user.id = ?2 and coalesce(freight.sender.email, freight.emailMessage.senderAddress = ?3)
order by freight.loadingDate desc]
2020-12-24 00:45:37.906  INFO 16068 --- [   scheduling-1] ilAccoun

标签： spring-bootquerydslcriteriaquery

解决方案

使用Freight.sender.email.coalesce(Freight.message.senderAddress). Freight.message.senderAddress将是NULL如果Freight.message是NULL。对于可选关联，默认情况下使用左外连接，因此这不会导致任何问题。否则，请自己明确使用左连接。

推荐阅读