CODEWITHSUNDEEP
jsonspringobjectjpanames
rexxar2
rexxar2

Reputation: 97

Spring data JPA , result object has numbers instead of column names

i would like some help trying to do the following.I want to get the number of purchases of each user in the database grouped by his name and id.But it's very hard to do compared to simple sql.

I have the following code in my PurchaseRepository that extends CrudRepository

@Query("SELECT p.user.name as Name,p.user.id as Id,Count(p) as Purchases from Transaction p GROUP BY p.user.id")
 List<Object> purchaseNumOfEachUser();

First of all im not sure if this is the right query because i wanted to do Count(*) but says its not valid.

Secondly , the object i get returned when converted to Json via a controller is like

0:{
0:"John",
1:2, //id
2:5 //purchases
}

What i would like to get is

0:{
"Name" : "John",
"Id" : 1 ,
"Purchases" : 2
},
1:{
....
}

Any ideas?

Upvotes: 0

Views: 871

Answers (1)

Simon Martinelli
Simon Martinelli

Reputation: 36223

1) The query:

SELECT p.user.name as Name, p.user.id as Id, Count(p) as Purchases 
from Transaction p GROUP BY p.user.id

should be

SELECT p.user.name as Name, p.user.id as Id, Count(p) as Purchases 
from Transaction p GROUP BY p.user.name, p.user.id

You must group by all rows you are selecting.

2) the result

The result of the query is List if you want to have something meaningful you should consider the constructor expression that let's you create your own objects.

For example

package mypackage;

public class PurchaseInfo {

  private final String name;
  private final Integer id;
  private final Long count;

  public PurchaseInfo(String name, Integer id, Long count) {
    this.name = name;
    this.id = id;
    this.cound = count;
  }
  // getters
}

This class can then be use in the query (please notice the fully qualified class name after NEW):

SELECT NEW mypackage.PurchaseInfo(p.user.name, p.user.id, Count(p))
from Transaction p GROUP BY p.user.name, p.user.id

The result will then be List and you will get it nicely serialized to JSON.

Read more about the Constructor Expression here:

https://vladmihalcea.com/the-best-way-to-map-a-projection-query-to-a-dto-with-jpa-and-hibernate/

Upvotes: 1

Related Questions