How to change the formatting of the output of Hibernate HQL query-CodePudding

I'm developing a Spring Boot application with Spring Data JPA. I'm using a custom JPQL query to group by some field and get the count. Following is my repository method.

@Query("SELECT v.status.name, count(v) as cnt FROM Pet v GROUP BY v.status.name")
List<Object[]> countByStatus();

It's working and result is obtained as follows:

[
    [
        "pending",
        1
    ],
    [
        "available",
        4
    ]
]

However, I would like my Rest endpoint to respond with an output which is formatted like this

{
    "pending": 1,
    "available": 4
}

How can I achieve this?

CodePudding user response：

Basically you want to produce a JSON where its properties ("pending", "available") are dynamic and come from the SELECT v.status.name part of the query.

Create a DTO to hold the row values:

package com.example.demo;

public class ResultDTO {

    private final String key;
    private final Long value;

    public ResultDTO(String key, Long value) {
        this.key = key;
        this.value = value;
    }

    public String getKey() {
        return key;
    }

    public Long getValue() {
        return value;
    }
}

Change your query to create a new ResultDTO per row:

@Query("SELECT new com.example.demo.ResultDTO(v.status.name, count(v)) as cnt FROM Pet v GROUP BY v.status.name")
List<ResultDTO> countByStatus();

"com.example.demo" is my package, you should change it to yours.

Then from your service class or from your controller you have to convert the List<ResultDTO> to a Map<String, Long> holding all rows' keys and values.

final List<ResultDTO> repositoryResults = yourRepository.countByStatus();
final Map<String, Long> results = repositoryResults.stream().collect(Collectors.toMap(ResultDTO::getKey, ResultDTO::getValue));

Your controller should be able to transform final Map<String, Long> results to the desired JSON