How to return map with multiple sum in jpql jpa?

Question

Here is my query

select SUM(d.day) as totalDay, SUM(d.month) as totalMonth from record d where d.userId = ?1
Integer getRecod(Long id);

As the query is not returning integer, error occurs. What should i replace with integer?

Answer 1

Solution 1: Create a model with totalDay and totalMonth and create an all args constructor.

public class UserDataModel {
    Long totalDay;
    Long totalMonth;

    public UserDataModel(Long totalDay, Long totalMonth) {
        this.totalDay = totalDay;
        this.totalMonth = totalMonth;
    }
    
    //getter and setter
}

Change your query like

@Query(value = "select 
new com.package.UserDataModel(SUM(d.day) as totalDay, SUM(d.month) as totalMonth) 
from Record d where d.userId = ?1 ")
    UserDataModel getRecord(Long id);

Solution 2: using spring projection. Create an interface like this. Make sure to follow proper camcelCase.

public interface UserDataModelV2 {
    Long getTotalDay();
    Long getTotalMonth();
}

Change your moethod like this.

    @Query(value = "select " +
            " SUM(d.day) as totalDay, SUM(d.month) as totalMonth " +
            "from Record d where d.userId = ?1")
    List<UserDataModelV2> getRecord(Long id);

If you want to return a HashMap instead of a POJO, you can extend UserDataModel with hashMap and put data in the map in the constructor.

public class UserDataModel extends HashMap<String, Object>{
    Long totalDay;
    Long totalMonth;

    public UserDataModel(Long totalDay, Long totalMonth) {
        this.totalDay = totalDay;
        this.totalMonth = totalMonth;
        put("totalDay",totalDay); 
        put("totalMonth",totalMonth); 
    }
    
    //getter and setter
}

Or you can replace the Interface in Solution 2 with a Map<Stirng, Object>.

@Query(value = "select " +
            " SUM(d.day) as totalDay, SUM(d.month) as totalMonth " +
            "from Record d where d.userId = ?1")
    List<Map<Stirng, Object>> getRecord(Long id);