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.
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);
}