JPA: How do I sort by field customising order when using findAll with specification-CodePudding

I`m using Spring Tool Suite 4 (4.12.0.RELEASE) with Java 8 and I have the following piece of code to get results from a table filtered, paged and sorted:

// Define pageable
    Sort sort = JpaSort.unsafe(Sort.Direction.ASC, Arrays.asList("FIELD(Status, '0','6','1','3','7')"));
    Pageable pageRequest = PageRequest.of(searchCriteria.getPageNo(), searchCriteria.getPageSize(), sort);
    
    // Initialise specification
    Specification<BestPracticeAdminList> spec = Specification.where(null);

    // If search criteria contains searchText field, add it to the specification
    if(searchCriteria.getSearchText() != null)
        spec = spec.and(BestPracticeAdminListFilter.titleLike(searchCriteria.getSearchText()));
    
    // If search criteria contains filters field, inspect filters field
    if(searchCriteria.getFilters() != null) {
        
        // If search criteria contains subjectID field, add it to the specification
        if(searchCriteria.getFilters().getSubjectID() != null)
            spec = spec.and(BestPracticeAdminListFilter.hasAlternativeSubjectID(searchCriteria.getFilters().getSubjectID()));
        
        // If search criteria contains categoryID field, add it to the specification
        if(searchCriteria.getFilters().getCategoryID() != null)
            spec = spec.and(BestPracticeAdminListFilter.hasAlternativeSubjectCategoryID(searchCriteria.getFilters().getCategoryID()));
        
        // If search criteria contains status field, add it to the specification
        if(searchCriteria.getFilters().getStatus() != null)
            spec = spec.and(BestPracticeAdminListFilter.hasStatus(searchCriteria.getFilters().getStatus()));
        
    }
    
    // Get results
    Page<BestPracticeAdminList> pagedResults = bestPracticeAdminListRepository.findAll(spec, pageRequest);

Problem is adding a custom sorting. I need to sort by a status field, which is an enumerator, but in an specific order, not the order the enumerator is written.

Custom sorting works in a different situation when using a native query on a repository with @Query and nativeQuery=true but not in this case when I want to use the findAll method with a specification for filtering.

This is the entity class:

@Entity
@Table
public class BestPracticeAdminList implements Serializable {

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(name = "BestPracticeAdminListID")
private int bestPracticeAdminListID;

@Column(name = "Title")
private String title;

@Column(name = "BestPracticeID")
private int bestPracticeID;

@Column(name = "AlternativeSubjectID")
private int alternativeSubjectID;

@Column(name = "AlternativeSubjectCategoryID")
private int alternativeSubjectCategoryID;

@Column(name = "Status")
@Enumerated(EnumType.ORDINAL)
private LookupReportingProvisionWorkflowStatus status;

@Column(name = "IsPreviousVersionPublished")
private Boolean isPreviousVersionPublished; 

public BestPracticeAdminList() {
}

Currently, exception I get is:

 org.springframework.data.mapping.PropertyReferenceException: No property fIELD(Status, '0','6','1','3','7') found for type BestPracticeAdminList!

How could I solve this problem? Thanks

CodePudding user response：

Since Spring is complaining you don't have a sorting field, you could solve this by creating it using the FIELD function in a @Formula in your BestPracticeAdminList

@Formula("FIELD(Status, '0','6','1','3','7')")
private String sortingStatus;

and using it for sorting

Sort sort = JpaSort.unsafe(Sort.Direction.ASC, Arrays.asList("sortingStatus"));