NonUniqueResultException: JPARepository Spring boot

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:

SpringBoot

JPA

QueryDSL

HQL

Exception

@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:

getLastMonthLoans()

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.

2 Answers
2

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

Map<LocalDate,Integer>

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

List<Object>

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.

List<Object>

Map

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[2];
mappedResult.put(ld, count);
}


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.

Map

Map

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.

Map

EntityManager

Map

Map

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

Map

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.

List<Object>

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

Map

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


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.