CODEWITHSUNDEEP
javaspringjpajdbc
nathan.hunt
nathan.hunt

Reputation: 127

Native @Query in JpaRepository returning a Map—I get javax.persistence.NonUniqueResultException

I have a question about using a native @Query annotation on my JpaRepository. Right now, I have the repository autowired, using my own interface that extends JpaRepository. I'm trying to query a database for a count of entries, and I'm trying to make sure all the heavy lifting is done one the database side.

public interface PersonRepository extends JpaRepository<Person, Long>{
    
    @Query(value="SELECT protocol, COUNT(Distinct personID) from person_counts WHERE(datetime BETWEEN :beginDate and :endDate)and personID NOT LIKE '%test%' GROUP by protocol", nativeQuery=true)
    Map<String, BigInteger> findPersonCountsByDate(Date beginDate, Date endDate);

}

is how I tried to set it up. I made sure the SQL query worked in the database.

As you can see, I just want a count of people, broken down by protocol. I keep running into exceptions, though:

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

javax.persistence.NonUniqueResultException: query did not return a unique result: 2
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:128) ~[hibernate-core-5.4.27.Final.jar!/:5.4.27.Final]

Any hints as to what I'm doing wrong? Thanks so much!

Upvotes: 0

Views: 351

Answers (1)

nathan.hunt
nathan.hunt

Reputation: 127

Aha, I knew I was probably missing something obvious! Thanks to M. Deinum for pointing me toward the right answer:

You would need to return a List<Map<String, BigInteger>> to make it work. With a Map<String, BigInteger> it will map a single row, where the String would be the column name and the BigInteger the result. I'm not sure if making that a list would actually work (you might need a List<Map<String, String>> instead).

Upvotes: 1

Related Questions