NonUniqueResultException: JPARepository Spring boot

Question

I am using SpringBoot with JPA and QueryDSL. I have written a HQL to fetch some custom records from the table but it is throwing Exception. Below I am mentioning code of repository:

@Repository
public interface LoanOfferRepository extends JpaRepository<LoanOffer, Long>, QuerydslPredicateExecutor<LoanOffer> {

    @Query("select lo.startDate,count(*) from LoanOffer lo where lo.loan.fsp= :fsp and lo.startDate between :fromDate and :toDate Group by lo.startDate")
    public Map<LocalDate,Integer> getLastMonthLoans(@Param("fsp")Fsp fsp,@Param("fromDate")LocalDate fromDate,@Param("toDate")LocalDate toDate);
}

Whenever I call this method getLastMonthLoans() I am getting following exception:

Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.IncorrectResultSizeDataAccessException: query did not return a unique result: 9; nested exception is javax.persistence.NonUniqueResultException: query did not return a unique result: 9] with root cause

javax.persistence.NonUniqueResultException: query did not return a unique result: 9

Is there anything wrong with code or Query or Return type ? Query seems to work fine though.

Answer 1

Your query result can not be mapped to Map<LocalDate,Integer>.

You may try to return List<Object[]> instead of Map.

@Query("select lo.startDate,count(*) from LoanOffer lo where lo.loan.fsp= :fsp and lo.startDate between :fromDate and :toDate Group by lo.startDate")
public List<Object[]> getLastMonthLoans(@Param("fsp")Fsp fsp,@Param("fromDate")LocalDate fromDate,@Param("toDate")LocalDate toDate);

And then parse the List<Object[]> to the Map you need.

As such:

Map<LocalDate, Integer> mappedResult = new HashMap<>();
List<Object[]> queryResult = loanOfferRepository.getLastMonthLoans(fsp, fromDate, toDate);
for (Object[] obj : queryResult ) {
    LocalDate ld = (LocalDate) obj[0];
    Integer count = (Integer) obj[1];
    mappedResult.put(ld, count);
}