CODEWITHSUNDEEP
springjackson
Cedrix Cedrix
Cedrix Cedrix

Reputation: 113

Converting response to JSON format spring

Hi I am struggling to solve this problem when I do a normal SELECT statement it returns a JSON response but when I do a SELECT COUNT statement it returns like this

enter image description here

I want it to be on JSON format too

@Repository
public interface FinanceRepository extends JpaRepository<Finance, Long>{
  List<Finance> findTop5ByOrderByRevenueDesc();
  List<Finance> findTop5ByOrderByProfitDesc();
  @Query(
        value = "SELECT currency, COUNT(currency) AS tally FROM finance "
                + "GROUP BY currency ORDER BY tally DESC LIMIT 5",
                nativeQuery=true)
  List<?> findByCurrency();
}

Controller

@GetMapping("/topCurrency")
  private List<?> getTopCurrency(){
  return financeRepository.findByCurrency();
}

This is what I did so far

public class CurrencyResponse {

@JsonProperty("Currency")
private List<?> currency;

public List<?> getCurrency() {
    return currency;
}

public void setCurrency(List<?> currency) {
    this.currency = currency;
}
}

Controller

@GetMapping("/topCurrency")
private ResponseEntity<?> getTopCurrency(){
    CurrencyResponse response = new CurrencyResponse();
    response.setCurrency(financeRepository.findByCurrency());
    return new ResponseEntity<>(response, HttpStatus.OK);
}

and it returns this

Currency:[
     "USD",
     3,
     "EURO",
     2,
     "PESO",
     1
]

but I want it to be like this

{
     "USD":3,
     "EURO":2,
     "PESO":1
}

I already have the jackson dependency on my POM, thanks in advance

Upvotes: 0

Views: 347

Answers (2)

Cedrix Cedrix
Cedrix Cedrix

Reputation: 113

I have fixed it by changing

List<?> findByCurrency();

into

List<Object[]> findByCurrency();

Controller

@GetMapping("/topCurrency")
private ResponseEntity<List<Object>> getTopCurrency(){
    List<Object[]> results = financeRepository.findByCurrency();
    List<Object> currencyList = new ArrayList<>();
    
    for(Object[] object : results) {
        Map<Object, Object> currencies = new LinkedHashMap<>();
        currencies.put("Currency", object[0]);
        currencies.put("Tally", object[1]);
        
        currencyList.add(currencies);
   }
   return new ResponseEntity<>(currencyList, HttpStatus.OK);
}

since the value that the query returns is like a 2d array I used List<Object[]> instead of List< Object> and I manually put the results into JSON format by using Map

Upvotes: 1

mib1870116
mib1870116

Reputation: 1

You should include Jackson dependency with the following, in your pom.xml file ...

<dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-databind</artifactId>
    <version>2.5.0</version>
</dependency>

... ..then in your controller should have the following ...

@RestController
@RequestMapping("/topCurrency")

... finally ResponseEntity model should look like this, try writing a similar utility class for mapping , you should see the resulting right format ...

 List<JSONObject> entities = new ArrayList<JSONObject>();
        for (Entity n : entityList) {
            JSONObject entity = new JSONObject();
            entity.put("aa", "bb");
            entities.add(entity);
        }
        return new ResponseEntity<Object>(entities, HttpStatus.OK);
    }

...

Upvotes: 0

Related Questions