我正在嘗試使用標準構建器動態實作此本機查詢
@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-10-19 01:21:00,322 錯誤 [測驗人員] [] ohhql.internal.ast.ErrorTracker：-第 1:230 行：意外令牌：其中 2022-10-19 01:21:00,322 錯誤 [測驗人員] [] ohhql.internal.ast.ErrorTracker：-第 1:230 行：意外令牌：antlr.NoViableAltException：意外令牌：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) at antlr.MismatchedTokenException: 期待 EOF，在 org.hibernate.hql.internal.antlr.HqlBaseParser.statement(HqlBaseParser.statement(HqlBaseParser. java:217) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:294) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:189) at org.hibernate .hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.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)211) 在 org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:294) 在 org.hibernate.hql 的 org.hibernate.hql.internal.antlr.HqlBaseParser.statement(HqlBaseParser.java:217) .internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:189) 在 org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.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)211) 在 org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:294) 在 org.hibernate.hql 的 org.hibernate.hql.internal.antlr.HqlBaseParser.statement(HqlBaseParser.java:217) .internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:189) 在 org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.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)java:189) 在 org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.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)java:189) 在 org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.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 列[從 no.posten.ph.unit.domain.Unit 中選擇 generateAlias0 注冊為 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 )和 ( (:param3) 中的 generatedAlias1.id.returnCode ) 和 ( generatedAlias1.startDate<=:param4 ) 和 ( coalesce(generatedAlias1.endDate, :param5)>=:param6 ))]; 嵌套例外是 java.lang.IllegalArgumentException：org.hibernate.hql.internal.ast.QuerySyntaxException：意外令牌：在第 1 行附近，第 230 列 [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 ) 和 ( generatedAlias1.id.returnCode in (:param3) ) 和 ( generatedAlias1.startDate<=:param4 ) 和 ( coalesce(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”之前沒有表/物體名稱，并且在 where 子句中只添加了 1 個謂詞，盡管有 6 個謂詞
uj5u.com熱心網友回復：
您的代碼將根添加到外部查詢
Root<LocalAreaRegister> subRoot = criteriaQuery.from(LocalAreaRegister.class);
當您想將其添加到 時subQuery，即
Root<LocalAreaRegister> subRoot = subQuery.from(LocalAreaRegister.class);
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/521168.html
上一篇：創建沒有資料庫連接的JPA物體