Issue
I am trying to achieve this native query dynamically using a criteria builder
@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);
This is my implementation
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);
});
}
But its failing with below error message:
******6
2022-10-19 01:21:00,322 ERROR [Test worker] [] o.h.hql.internal.ast.ErrorTracker: - line 1:230: unexpected token: where
2022-10-19 01:21:00,322 ERROR [Test worker] [] o.h.hql.internal.ast.ErrorTracker: - line 1:230: unexpected token: where
antlr.NoViableAltException: unexpected token: where
at org.hibernate.hql.internal.antlr.HqlBaseParser.fromRange(HqlBaseParser.java:1674)
at org.hibernate.hql.internal.antlr.HqlBaseParser.fromClause(HqlBaseParser.java:1473)
at org.hibernate.hql.internal.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java:1171)
at org.hibernate.hql.internal.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:825)
at org.hibernate.hql.internal.antlr.HqlBaseParser.subQuery(HqlBaseParser.java:4356)
at org.hibernate.hql.internal.antlr.HqlBaseParser.compoundExpr(HqlBaseParser.java:3729)
at org.hibernate.hql.internal.antlr.HqlBaseParser.inList(HqlBaseParser.java:3454)
at
antlr.MismatchedTokenException: expecting EOF, found ')'
at antlr.Parser.match(Parser.java:211)
at org.hibernate.hql.internal.antlr.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)
at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:113)
at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:73)
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:162)
org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: where near line 1, column 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 ) and ( generatedAlias1.id.returnCode in (:param3) ) and ( generatedAlias1.startDate<=:param4 ) and ( coalesce(generatedAlias1.endDate, :param5)>=:param6 ))]; nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: where near line 1, column 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 ) and ( generatedAlias1.id.returnCode in (:param3) ) and ( generatedAlias1.startDate<=:param4 ) and ( coalesce(generatedAlias1.endDate, :param5)>=:param6 ))]
app//org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:748) ... 127 more
To make the error understand simply:
[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 ))]
This is the query that is being generated and here before the "where" there is no table/entity name and in the where clause there is only 1 predicate being added, despite there are 6 predicates
Solution
Your code adds a root to the outer query
Root<LocalAreaRegister> subRoot = criteriaQuery.from(LocalAreaRegister.class);
while you want to add it to the subQuery
, that is
Root<LocalAreaRegister> subRoot = subQuery.from(LocalAreaRegister.class);
Answered By - Mihe
Answer Checked By - David Marino (JavaFixing Volunteer)