InvalidPathException while sorting with org.springframework.data.domain.Pageable

Question

I am trying to sort my table's content on the backend side, so I am sending org.springframework.data.domain.Pageable object to controller. It arrives correctly, but at the repository I am getting org.hibernate.hql.internal.ast.InvalidPathException. Somehow the field name I would use for sorting gets an org. package name infront of the filed name.

The Pageable object logged in the controller:

Page request [number: 0, size 10, sort: referenzNumber: DESC]

Exception in repository:

Invalid path: 'org.referenzNumber'","logger_name":"org.hibernate.hql.internal.ast.ErrorTracker","thread_name":"http-nio-8080-exec-2","level":"ERROR","level_value":40000,"stack_trace":"org.hibernate.hql.internal.ast.InvalidPathException: Invalid path: 'org.referenzNumber'\n\tat org.hibernate.hql.internal.ast.util.LiteralProcessor.lookupConstant(LiteralProcessor.java:111)

My controller endpoint:

@GetMapping(value = "/get-orders", params = { "page", "size" }, produces = { MediaType.APPLICATION_JSON_VALUE })
    public ResponseEntity<PagedModel<KryptoOrder>> getOrders(
            @ApiParam(name = "searchrequest", required = true) @Validated final OrderSearchRequest orderSearchRequest,
            @PageableDefault(size = 500) final Pageable pageable, final BindingResult bindingResult,
            final PagedResourcesAssembler<OrderVo> pagedResourcesAssembler) {

        if (bindingResult.hasErrors()) {
            return ResponseEntity.badRequest().build();
        }

        PagedModel<Order> orderPage = PagedModel.empty();
        try {
            var orderVoPage = orderPort.processOrderSearch(resourceMapper.toOrderSearchRequestVo(orderSearchRequest), pageable);
            orderPage = pagedResourcesAssembler.toModel(orderVoPage, orderAssembler);
        } catch (MissingRequiredField m) {
            log.warn(RESPONSE_MISSING_REQUIRED_FIELD, m);
            return ResponseEntity.badRequest().build();
        }

        return ResponseEntity.ok(orderPage);
    }

the repository:

@Repository
public interface OrderRepository extends JpaRepository<Order, UUID> {

    static final String SEARCH_ORDER = "SELECT o" //
            + " FROM Order o " //
            + " WHERE (cast(:partnerernumber as org.hibernate.type.IntegerType) is null or o.tradeBasis.account.retailpartner.partnerbank.partnerernumber = :partnerernumber)"
            + "   and (cast(:accountnumber as org.hibernate.type.BigDecimalType) is null or o.tradeBasis.account.accountnumber = :accountnumber)"
            + "   and (cast(:orderReference as org.hibernate.type.LongType) is null or o.tradeBasis.referenceNumber = :orderReference)"
            + "   and (cast(:orderReferenceExtern as org.hibernate.type.StringType) is null or o.tradeBasis.kundenreferenceExternesFrontend = :orderReferenceExtern)"
            + "   and (cast(:dateFrom as org.hibernate.type.DateType) is null or o.tradeBasis.timestamp > :dateFrom) "
            + "   and (cast(:dateTo as org.hibernate.type.DateType) is null or o.tradeBasis.timestamp < :dateTo) ";

    @Query(SEARCH_ORDER)
    Page<Order> searchOrder(@Param("partnerernumber") Integer partnerernumber,
            @Param("accountnumber") BigDecimal accountnumber, @Param("orderReference") Long orderReference,
            @Param("orderReferenceExtern") String orderReferenceExtern, @Param("dateFrom") LocalDateTime dateFrom,
            @Param("dateTo") LocalDateTime dateTo, Pageable pageable);

}

Update: I removed the parameters from the sql query, and put them back one by one to see where it goes sideways. It seems as soon as the dates are involved the wierd "org." appears too.

Update 2: If I change cast(:dateTo as org.hibernate.type.DateType) to cast(:dateFrom as date) then it appends the filed name with date. instead of org..

Thanks in advance for the help

Answer 1

My guess is, Spring Data is confused by the query you are using and can't properly append the order by clause to it. I would recommend you to use a Specification instead for your various filters. That will not only improve the performance of your queries because the database can better optimize queries, but will also make use of the JPA Criteria API behind the scenes, which requires no work from Spring Data to apply an order by specification.

Since your entity Order is named as the order by clause of HQL/SQL, my guess is that Spring Data tries to do something stupid with the string to determine the alias of the root entity.