如何在Spring JPA中使用条件生成器实现子查询

Question

我正在尝试使用标准生成器动态地实现这个本机查询

@Query(nativeQuery = true, value =
"""
SELECT * FROM unit_register
WHERE unit_nr IN (
SELECT DISTINCT(unit_nr)
FROM local_area_register
WHERE (service_code = :service_code or :service_code is null)
AND (country_code = :country_code or :country_code is null)
AND (postal_code = :postal_code or :postal_code is null)
AND return_code IN ('F', 'U')
AND start_date <= CURRENT_DATE
AND COALESCE(end_date, CURRENT_DATE) >= CURRENT_DATE )
""")
List<UnitRegister> getUnitCodeDetails(@Param("service_code") String serviceCode,
@Param("country_code") String countryCode,
@Param("postal_code") String postalCode);

这是我的实现

public List<UnitRegister> findUnitRegister(String serviceCode, String countryCode, String postalCode) {
return unitRegisterRepository.findAll((Specification<UnitRegister>) (Root<UnitRegister> root,
   CriteriaQuery<?> criteriaQuery,
   CriteriaBuilder criteriaBuilder) -> {
Subquery<LocalAreaRegister> subQuery = criteriaQuery.subquery(LocalAreaRegister.class);
Root<LocalAreaRegister> subRoot = criteriaQuery.from(LocalAreaRegister.class);
List<Predicate> predicates = new ArrayList<>();
List<Predicate> subPredicates = new ArrayList<>();
var p = criteriaBuilder.conjunction();
if (isNotBlank(serviceCode)) {
subPredicates.add(criteriaBuilder.equal(subRoot.get("id").get("serviceCode"), serviceCode));
}
if (isNotBlank(countryCode)) {
subPredicates.add(criteriaBuilder.equal(subRoot.get("id").get("countryCode"), countryCode));
}
if (isNotBlank(postalCode)) {
subPredicates.add(criteriaBuilder.equal(subRoot.get("id").get("postalCode"), postalCode));
}
CriteriaBuilder.Coalesce<LocalDate> coalesce = criteriaBuilder.coalesce();
coalesce.value(subRoot.get("endDate"));
coalesce.value(LocalDate.now());
subPredicates.add(criteriaBuilder.in(subRoot.get("id").get("returnCode")).value(RETURN_CODES_F_U));
subPredicates.add(criteriaBuilder.lessThanOrEqualTo(subRoot.get("startDate"), LocalDate.now()));
subPredicates.add(criteriaBuilder.greaterThanOrEqualTo(coalesce, LocalDate.now()));
System.out.println("******" + subPredicates.size());
subQuery.select(subRoot.get("id").get("unitNR")).distinct(true).where(subPredicates.toArray(new Predicate[]{}));

return criteriaBuilder.in(root.get("unitNr")).value(subQuery);
});
}

但它失败了，错误信息如下：

******6
2022-19 01:21:00322错误[测试工作人员][]o.h.hql.internal.ast.ErrorTracker:第1:230行：意外标记：其中2022-10-19 01:21:00322错误[测试工作人员][]o.h.hql.internal.ast.ErrorTracker:-line 1:230:意外令牌：whereantlr。NoViableAltException：意外的令牌：where位于org.hibernate.hql.internal.antlr.HqlBaseParser.fromRange(HqlBaseParser.java：1674(位于org.hibernate.hql.internal.antlr.HqlBaseParser.fromClause(HqlBaseParser.java：1473(位于org.hibernate.hql.internal.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java：1171(网址：org.hibernate.hql.internal.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:825(位于org.hibernate.hql.internal.antlr.HqlBaseParser.subQuery(HqlBaseParser.java：4356(网址：org.hibernate.hql.internal.antlr.HqlBaseParser.compoundExpr(HqlBaseParser.java：3729(位于org.hibernate.hql.internal.antlr.HqlBaseParser.inList(HqlBaseParser.java：3454(在antlr。MismatchedTokenException：应为EOF，找到了"("在antlr。Parser.match(Parser.java:211(位于org.hibernate.hql.internal.antlr.HqlBaseParser.statement(HqlBaseParser.java:217(位于org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslator Impl.java:294(位于org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslator Impl.java:189(位于org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslator Impl.java:144(网址：org.hibernate。engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:113(网址：org.hibernate。engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:73(网址：org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:162(

org.hibernate.hql.internal.ast.QuerySyntaxException：意外的令牌：在第1行附近，列230[select generatedAlias0 from no.ph.unit.domain.UnitRegister as generatedAlias0，no.ph.unit.domain.LocalArea Register as generatedAlias1 where generatedAlias.unitNr in(select distinct generatedAliasa.id.unitNr from where(generatedAliass1.id.serviceCode=：param0(and(generated阿利as1.id.countryCode=：param1(and((：param3(中的generatedAlias1.id.returnCode(和(generatedAlias1.startDate<=：param4(以及(coalize(generatedAlias1.endDate，：param5(>=：param6((]；嵌套异常为java.lang.IllegalArgumentException:org.hibernate.hql.internal.ast.QuerySyntaxException:意外标记：在第1行附近，列230[从no-posten.ph.unit.domain.UnitRegister as generatedAlias0，no-posten.ph.unit.docmain.LocalAreaRegister as generatedAlias1 where generatedAlias.unitNr in(从其中(generatedAlias1.id.serviceCode=：param0(和(generated阿利as1.id.countryCode=：param1(以及(generatedAlias1.id.postalCode=：aram2(选择不同的generatedAlia1.id.unitNr((：param3(中的generatedAlias1.id.returnCode(和(generatedAlias1.startDate<=：param4(以及(coalize(generatedAlias1.endDate，：param5(>=：param6((]

app//org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:748(…127更多

简单地理解错误：

[select generatedAlias0 from no.posten.ph.unit.domain.UnitRegister as generatedAlias0, no.posten.ph.unit.domain.LocalAreaRegister as generatedAlias1 where generatedAlias0.unitNr in (select distinct generatedAlias1.id.unitNR from  **where** ( generatedAlias1.id.serviceCode=:param0 ) and ( generatedAlias1.id.countryCode=:param1 ) and ( generatedAlias1.id.postalCode=:param2 ) and ( generatedAlias1.id.returnCode in (:param3) ) and ( generatedAlias1.startDate<=:param4 ) and ( coalesce(generatedAlias1.endDate, :param5)>=:param6 ))]

这是正在生成的查询；其中"；没有表/实体名称，在where子句中只添加了1个谓词，尽管有6个谓词

Answer 1

您的代码将根添加到外部查询

Root<LocalAreaRegister> subRoot = criteriaQuery.from(LocalAreaRegister.class);

当您想将其添加到subQuery时，即

Root<LocalAreaRegister> subRoot = subQuery.from(LocalAreaRegister.class);