JPA-按准入表单的状态计数(状态类型枚举)

Question

我已经声明了一个Enum类型的StatusNum，如下所示：

public enum StatusEnum {
PENDING, ACCEPTED, REJECTED, IN_PROGRESS, COMPLETED

}

我正试图从他们的状态中获得入场表格的计数，所以我尝试了查询(在存储库中(：

@Query("SELECT COUNT(a.status), a.status FROM AdmissionForms a GROUP BY a.status ORDER BY a.status ASC")
List<Object[]> admissionFormCountByStatus();

然后我将其解析为我指定的DTO列表(在服务类中(，

List<Object[]> objects = admissionFormRepository.admissionFormCountByStatus();
for (Object[] singleObject : objects ) {
admissionFormStatusCountList.add(new AdmissionFormStatusCountResponseDTO(
((Number)singleObject[0]).intValue(), (StatusEnum)singleObject[1]));
}

我的DTO(录取表格状态计数响应DTO(喜欢：

public class AdmissionFormStatusCountResponseDTO {
private int count;
private StatusEnum status;
public AdmissionFormStatusCountResponseDTO(int count, StatusEnum status) {
this.count = count;
this.status = status;
}
}

资源类别：

@GetMapping("/workorders/countByStatus")
@Timed
public List<AdmissionFormStatusCountResponseDTO> admissionFormCountByStatus() {
return admissionFormService.workorderCountByStatus();
}

我得到的结果是：

[
{
"count": 6,
"status": "ACCEPTED"
},
{
"count": 2,
"status": "COMPLETED"
},
{
"count": 3,
"status": "IN_PROGRESS"
}
]

只有当该状态值存在时，它才会以count作为响应，但对于尚未分配给任何录取表格的Enum状态，我也需要count=0作为结果。

Answer 1

准备好admissionFormStatusCountList后，手动查看其中缺少哪些枚举，并将其添加为值0。你不会把它从数据库里拿出来的。

Answer 2

我做了如下所需的更改。

我将admissionFormStatusCountResponseDTO定义为：

public class AdmissionFormStatusCountResponseDTO {
private Long accepted;
private Long completed;
private Long inProgress;
private Long pending;
private Long rejected;
public AdmissionFormStatusCountResponseDTO() {
}
public AdmissionFormStatusCountResponseDTO(Long accepted, Long completed, Long inProgress, Long pending, Long rejected) {
this.accepted = accepted;
this.completed = completed;
this.inProgress = inProgress;
this.pending = pending;
this.rejected = rejected;
}
//getters and setters
}

然后使用此DTO将存储库查询更新为：

@Query("select new com.purvik.app1.service.dto.AdmissionFormStatusCountResponseDTO(" +
"SUM(CASE WHEN a.status='ACCEPTED' then 1 else 0 END)," +
"SUM(CASE WHEN a.status='COMPLETED' then 1 else 0 END)," +
"SUM(CASE WHEN a.status='IN_PROGRESS' then 1 else 0 END)," +
"SUM(CASE WHEN a.status='PENDING' then 1 else 0 END)," +
"SUM(CASE WHEN a.status='REJECTED' then 1 else 0 END)" +
") from AdmissionForm a")
AdmissionFormStatusCountResponseDTO admissionFormCountByStatusNew();

服务类别：

public AdmissionFormStatusCountResponseDTO admissionFormCountByStatus(){
log.debug("Request to get count of AdmissionForm by it's Status ");
return admissionFormRepository.admissionFormCountByStatusNew();
}

资源终结点：

@GetMapping("/admissionForms/countByStatus")
@Timed
public AdmissionFormStatusCountResponseDTO admissionFormCountByStatus() {
log.debug("get count of status from stored admissionForm");
return admissionFormService.admissionFormCountByStatus();
}

现在我有了回应：

{
"accepted": 5,
"completed": 0,
"inProgress": 4,
"pending": 1,
"rejected": 1
}

我不需要在Enum中定义所有静态。它是一个基于构造函数的JPQL查询，可以带来预期的结果。希望这能帮助其他人。

@如果你想在Enum中定义所有的静态响应，Robert Niestroj是对的。只是编辑我的代码来帮助我。