How sort by string field as numeric value in JpaSpecificationExecutor#findAll

Question

I have entity with string field:

@Data
@Entity
@Table(name = "document")
public class Document {
  ...
  private String docNumber;
  ...
}

And I use JpaSpecificationExecutor#findAll to find rows:

Page<T> findAll(@Nullable Specification<T> spec, Pageable pageable);

I need to sort by docNumber as numeric value. I try to set to pageable#sort value new JpaSort("length(docNumber)") for it (like in answer from here - https://stackoverflow.com/a/46215275), but get exception:

org.springframework.data.mapping.PropertyReferenceException: No property (length(docNumber)) found for type Payroll!
    at org.springframework.data.mapping.PropertyPath.<init>(PropertyPath.java:94)
    at org.springframework.data.mapping.PropertyPath.create(PropertyPath.java:358)
    at org.springframework.data.mapping.PropertyPath.create(PropertyPath.java:334)
    at org.springframework.data.mapping.PropertyPath.lambda$from$0(PropertyPath.java:287)
    at java.util.concurrent.ConcurrentMap.computeIfAbsent(ConcurrentMap.java:324)
    at org.springframework.data.mapping.PropertyPath.from(PropertyPath.java:269)
    at org.springframework.data.mapping.PropertyPath.from(PropertyPath.java:252)
    at org.springframework.data.jpa.repository.query.QueryUtils.toJpaOrder(QueryUtils.java:563)
    at org.springframework.data.jpa.repository.query.QueryUtils.toOrders(QueryUtils.java:516)
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.getQuery(SimpleJpaRepository.java:630)
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.getQuery(SimpleJpaRepository.java:584)
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:387)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:377)
    at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:200)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:641)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:605)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:590)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
    at com.sun.proxy.$Proxy285.findAll(Unknown Source)

How can I sort by docNumber as numeric value?

PS: Specification<T> spec param with non-trivial logic and, I think, cant replaced with @Query (like in answer from here - https://stackoverflow.com/a/46215275)

`length` is a Java function and doesn't exist in JPQL (also it doesn't convert a `VARCHAR` to a `NUMBER` in the conventional sense. Try the suggested methods here: https://stackoverflow.com/a/20196068/66686 — Jens Schauder, Jul 21 '20 at 07:58
Yes, `length` doesn't convert to a `number`, but with length we can create needed sort. Example: `select * from document order by length(doc_number), doc_number;` — zbender, Jul 21 '20 at 08:01

score 2 · Accepted Answer · answered Jul 21 '20 at 12:55

I implemented a variant in which to initial specification added specification with orderBy converted from sort object, and removed sort from PageRequest:

    private Page<E> search(Specification<E> spec, PageRequest pageRequest) {
        spec = addOrderByToSpecification(spec, pageRequest.getSort());
        return repository.findAll(spec, pageRequestWithoutSort(pageRequest));
    }

    private Specification<E> addOrderByToSpecification(Specification<E> specification, Sort sort) {
        if (sort.isUnsorted()) {
            return specification;
        }
        Specification<E> orderSpecification = (root, query, criteriaBuilder) -> {
            List<Order> criteriaOrders = sort.stream()
                    .map(order -> mapSortOrderToCriteriaOrder(order, root, criteriaBuilder))
                    .collect(Collectors.toList());
            query.orderBy(criteriaOrders);
            return criteriaBuilder.and();
        };
        return specification.and(orderSpecification);
    }

    private Order mapSortOrderToCriteriaOrder(Sort.Order sortOrder, Root<?> root, CriteriaBuilder criteriaBuilder) {
        Expression<?> expression = root.get(sortOrder.getProperty());
        if (DOC_NUMBER.equals(sortOrder.getProperty())) {
            expression = expression.as(Integer.class);
        }
        return sortOrder.isAscending() ? criteriaBuilder.asc(expression) : criteriaBuilder.desc(expression);
    }

    private PageRequest pageRequestWithoutSort(PageRequest pageRequest) {
        return PageRequest.of(pageRequest.getPageNumber(), pageRequest.getPageSize());
    }

Perfect! I try to add `substring` function to `SORT BY` via `JpaSort.unsafe`, but it doesn't work with specifications. I try to find a solution in stackoverflow for a long time and only your approach heps me, thanks! — Дима Годиков, Apr 08 '22 at 07:48

How sort by string field as numeric value in JpaSpecificationExecutor#findAll

1 Answers1