SpringData/HHibernate:按字符而不是索引按枚举排序

Question

我有一个场景，根据数据库中枚举的字符而不是索引，应用基于枚举列的排序，并使用querydsl过滤数据以获得分页数据。

数据库中的表：-

CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT primary key,
`task_status` enum('open','expired','approved','rejected') DEFAULT NULL
);

通过task_status asc从测试订单中选择*；

id  taskStatus
3   open
4   expired
2   approved
1   rejected

上面的结果是根据枚举的索引号而不是文本排序的

但是查询

select * from test order by  CAST(task_status AS CHAR) asc; sorts based on text

需要同样的应用于春季数据jpa和我的我正在使用

It can have multiple filter its just a dummy code
@GetMapping("/get")
public Page<Test> getTest()  {
QTest test = QTest.test;
List<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(2);
ids.add(3);
ids.add(4);
BooleanExpression booleanExp = test.id.in(ids);
Sort sort = new Sort(Direction.ASC, "taskStatus");
return testRepo.findAll(booleanExp, PageRequest.of(0, 100, sort));
}

返回基于枚举索引而非文本排序的数据

{
"content": [
{
"id": 3,
"taskStatus": "OPEN"
},
{
"id": 4,
"taskStatus": "EXPIRED"
},
{
"id": 2,
"taskStatus": "APPROVED"
},
{
"id": 1,
"taskStatus": "REJECTED"
}
],
"pageable": {
"sort": {
"unsorted": false,
"sorted": true,
"empty": false
},
"offset": 0,
"pageNumber": 0,
"pageSize": 100,
"paged": true,
"unpaged": false
},
"last": true,
"totalPages": 1,
"totalElements": 4,
"size": 100,
"number": 0,
"sort": {
"unsorted": false,
"sorted": true,
"empty": false
},
"numberOfElements": 4,
"first": true,
"empty": false
}

类别为

public enum TaskStatus {
OPEN("open", "Open"), APPROVED("approved", "Approved"), REJECTED("rejected", "Rejected"), EXPIRED("expired",
"Expired");
}
@Getter
@Setter
@Entity
@Table(name = "test")
public class Test implements Serializable {
private static final long serialVersionUID = -8697601034719624001L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
@Enumerated(EnumType.STRING)
private TaskStatus taskStatus;
}

请帮我一下。

Answer 1

您可以使用@Query声明您的查询

@Query("query select * from test order by CAST(task_status AS CHAR) asc")
public List<Test> getTest()

参考：https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-方法。查询

Answer 2

如果您使用的是JPA存储库，您可以将此方法添加到存储库界面：List<Test> findAllByOrderByTaskStatusAsc();