Sorting expression in spring data JPA

Question

I have the following entity class where objectId is alphanumeric string

@Data
@Entity
@Table(name = "CASE_INFO")
public class CaseInfo {
  @Id
  @Column(name = "ID")
  private UUID id;

  @Column(name = "OBJECT_ID")
  private String objectId;
}

I also have the following repository

@Repository
public interface CaseInfoRepository extends JpaRepository<CaseInfo, UUID>, JpaSpecificationExecutor<CaseInfo> { }

Now when I make a query based on Specification and Pageable objects like this

Specification<CaseInfo> specification = (Specification<CaseInfo>) (root, query, criteriaBuilder) -> criteriaBuilder.and();
Pageable pageable = PageRequest.of(0, 25, Sort.by("objectId"));
caseInfoRepository.findAll(specification, pageable);

I want to be able to sort result based on CaseInfo.objectId numerically so that the order by in sql query would look something like this

... order by to_number(OBJECT_ID) ...

If I do something like

PageRequest.of(0, 25, Sort.by("objectId"))

it orders alphabetically so that "100" is before "2" which is wrong for my use case.

I found https://www.baeldung.com/spring-data-jpa-query where it uses

JpaSort.unsafe("LENGTH(name)")

but this does not seem to work because if I do

PageRequest.of(0, 25, JpaSort.unsafe("to_number(objectId)"))

It gives error message "No property to found for type CaseInfo! Did you mean 'id'?"

Does anybody know a way to give custom expression to Sort object?

Answer 1

You could add a formula field casting the string value to the numeric one and then sort by this field or use default implementation findAll(Pageable pageable):

@Data
@Entity
@Table(name = "CASE_INFO")
public class CaseInfo {
  @Id
  @Column(name = "ID")
  private UUID id;

  @Column(name = "OBJECT_ID")
  private String objectId;

  @Formula(value = "CAST(OBJECT_ID AS NUMERIC(10, 0))")
  private int numObjectId;
}

Repository:

@Repository
public interface CaseInfoRepository extends 
                 JpaRepository<CaseInfo, UUID>, JpaSpecificationExecutor<CaseInfo> {

    // sort by formula field 
    List<CaseInfo> findAllByOrderByNumObjectId(Pageable pageable);
}

There is a trick with using CAST function in @Formula -- it is very likely to fail when casting to INT, INTEGER, BIGINT but it works when casting to NUMERIC(10, 0).

Numeric also should be large enough to contain the value within the string after cast otherwise an integer overflow may occur.