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.

You should use [projections](https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#projections). Please check my answers [1](https://stackoverflow.com/a/46878744), [2](https://stackoverflow.com/a/44437850) how to return arbitrary objects from repo. — Cepr0, Jul 03 '18 at 09:31

htshame · Accepted Answer · 2021-04-04T21:44:13.353

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

davidxxx · Answer 2 · 2018-07-03T09:52:12.313

According to the Spring Data documentation, Map doesn't make part of Supported Query Return Types.
And even JPA (even the 2 version) doesn't support Map as return type in executed queries.

So you have two ways to solve your issue :

1) keep the Map as return type. In this case, don't use the Spring Data feature that relieves you from writing boiler plate code.
Instead : create the query from an EntityManager, execute it and apply a post processing to map the result into a Map.
If the Map has a reasonable size and you really need to retrieve a Map from your repository, using this way should be favored.

2) Don't return a Map as return type.

In both cases, you will have to choose the return type of the executed query. You have broadly two alternatives :

1) List<Object[]> as return type but it is not necessarily meaningful and not type safe either.

2) A custom class representing structure of the rows

public class LoanOfferStats{
  private LocalDate startDate;
  private Long count;

  public LoanOfferStats(LocalDate startDate, Long count) {
    this.startDate = startDate;
    this.count  = count;
  }

  public LocalDate getStartDate(){
     return startDate;
  }

  public Long getCount(){
     return count;
  }

}

And annotate your method such as :

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

Note that converting a List to Map is really straight in Java 8 :

List<LoanOfferStats> loanOfferStats = loanOfferRepository.getLastMonthLoans(...);
Map<LocalDate, Long> map = 
        loanOfferStats.stream()
                      .collect(Collectors.toMap(LoanOfferStats::getStartDate, LoanOfferStats::getCount));

NonUniqueResultException: JPARepository Spring boot

2 Answers2