Wsrtjtyk

I have declared an Enum type StatusEnum as like:

public enum StatusEnum {

PENDING, ACCEPTED, REJECTED, IN_PROGRESS, COMPLETED

}

I am trying to get count of Admission form from their status, so i tried with query (in Repository):

@Query("SELECT COUNT(a.status), a.status FROM AdmissionForms a GROUP BY a.status ORDER BY a.status ASC")

List<Object> admissionFormCountByStatus();

then i parsed it to List of DTO that I have specified (in service class),

List<Object> objects = admissionFormRepository.admissionFormCountByStatus();

    for (Object singleObject : objects ) {

        admissionFormStatusCountList.add(new AdmissionFormStatusCountResponseDTO(

            ((Number)singleObject[0]).intValue(), (StatusEnum)singleObject[1]));

    }

my DTO (AdmissionFormStatusCountResponseDTO)like:

public class AdmissionFormStatusCountResponseDTO {

private int count;

private StatusEnum status;



public AdmissionFormStatusCountResponseDTO(int count, StatusEnum status) {

    this.count = count;

    this.status = status;

  }

}

resource class:

@GetMapping("/workorders/countByStatus")

@Timed

public List<AdmissionFormStatusCountResponseDTO> admissionFormCountByStatus() {

    return admissionFormService.workorderCountByStatus();

}

results that I'm having is like:

[

  {

    "count": 6,

    "status": "ACCEPTED"

  },

  {

    "count": 2,

    "status": "COMPLETED"

  },

  {

    "count": 3,

    "status": "IN_PROGRESS"

  }

]

It's responding with count only if that status value is present but I also require count = 0 in result for status from Enum not assigned to any admission form yet.

After preparing your admissionFormStatusCountList look manually which enums are missing in there and add them with value 0. You are not gonna get it out of the DB.

I did the changes as below that i required.

I defined the admissionFormStatusCountResponseDTO like:

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

}

then update repository query using this DTO as:

@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();

Service class:

public AdmissionFormStatusCountResponseDTO admissionFormCountByStatus(){

    log.debug("Request to get count of AdmissionForm by it's Status ");

    return admissionFormRepository.admissionFormCountByStatusNew();

}

Resource endpoint:

@GetMapping("/admissionForms/countByStatus")

@Timed

public AdmissionFormStatusCountResponseDTO admissionFormCountByStatus() {

    log.debug("get count of status from stored admissionForm");

    return admissionFormService.admissionFormCountByStatus();

}

response that now I've:

{

"accepted": 5,

"completed": 0,

"inProgress": 4,

"pending": 1,

"rejected": 1

}

I do not require to have all the statics defined in Enum. It's a constructor based JPQL query that bring expected results. Hope this will help others.

@Robert Niestroj is right if you want to have all the statics in response defined in your Enum. Just editing my code that help me out.