How to implement sub queries with criteria builder in Spring JPA-CodePudding

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

CodePudding user response：

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