org.hibernate.NonUniqueResultException: query did not return a unique result: 2?

Question

I have below code in my DAO:

String sql = "SELECT COUNT(*) FROM CustomerData " +
             "WHERE custId = :custId AND deptId = :deptId";
Query query = session.createQuery(sql);
query.setParameter("custId", custId);
query.setParameter("deptId", deptId);
long count = (long) query.uniqueResult(); // ERROR THROWN HERE

Hibernate throws below exception at the marked line:

org.hibernate.NonUniqueResultException: query did not return a unique result:

I am not sure whats happening as count(*) will always return only one row.

Also when i run this query on db directly, it return result as 1. So whats the issue?

the issue is that you ask for a uniqueResult, meaning there may only be one row returned, but the query finds more than 1 result. — Stultuske, May 26 '14 at 09:16
I just told you what triggers a NonUniqueResultException. I agree that for a count it is weird, but still. have you tried without the UniqueResult constraint? — Stultuske, May 26 '14 at 09:20
@user3198603 have you resolved the issue? Did you get the reason of problem? — cнŝdk, Aug 28 '17 at 13:37
As @Grox13 answered here [enter link description here](https://stackoverflow.com/a/40233705/8338411) Alternatively, If you are using `Optional<>` change it to `List<>` — Paul Kunda, Jul 22 '21 at 08:41
If you have more then one row in your query result, you should use f.e.: NHibernate.IQuery.ListAsync instead of UniqueResultAsync — peter70, Nov 09 '22 at 09:31
Dou you have another class extending CustomerData ? Is this class also mapped? — Lluis Martinez, Apr 06 '23 at 15:26

GROX13 · Answer 1 · 2016-10-26T06:20:02.370

47

It seems like your query returns more than one result check the database. In documentation of query.uniqueResult() you can read:

Throws: org.hibernate.NonUniqueResultException - if there is more than one matching result

If you want to avoid this error and still use unique result request, you can use this kind of workaround query.setMaxResults(1).uniqueResult();

edited Oct 26 '16 at 06:20

answered Oct 25 '16 at 07:19

GROX13

4,605
4
27
41

score 25 · Answer 2 · answered Jun 15 '19 at 01:56

25

Hibernate Optional findTopByClientIdAndStatusOrderByCreateTimeDesc(Integer clientId, Integer status);

"findTop"!! The only one result!

answered Jun 15 '19 at 01:56

李凉杰

259
3
3

score 22 · Answer 3 · answered Nov 29 '18 at 08:54

22

I don't think other answers explained the key part: why "COUNT(*)" returns more than one result?

I just encountered the same issue today, and what I found out is that if you have another class extending the target mapped class (here "CustomerData"), Hibernate will do this magic.

Hope this will save some time for other unfortunate guys.

answered Nov 29 '18 at 08:54

Ian Wang

221
2
4

1

This should be the accapted answer. And count always return 1, no excuse. – Al Kasih Aug 03 '21 at 18:04
1

This should be the accepted answer. Hibernate is probably doing a UNION with both classes CustomerData and its child. – Lluis Martinez Apr 06 '23 at 15:23

score 12 · Answer 4 · answered May 06 '20 at 06:32

12

Generally This exception is thrown from Oracle when query result (which is stored in an Object in your case), can not be cast to the desired object. for example when result is a

List<T>

and you're putting the result into a single T object.

In case of casting to long error, besides it is recommended to use wrapper classes so that all of your columns act the same, I guess a problem in transaction or query itself would cause this issue.

answered May 06 '20 at 06:32

Majid Roustaei

1,556
1
20
39

it is the answer! – გენო მუმლაძე Jun 18 '21 at 06:38

score 7 · Answer 5 · answered Apr 23 '18 at 11:41

7

It means that the query you wrote returns more than one element(result) while your code expects a single result.

answered Apr 23 '18 at 11:41

rex roy

1,019
12
5

score 1 · Answer 6 · answered Jul 21 '21 at 16:21

Received this error while doing otherwise correct hibernate queries. The issue was that when having a class extend another hibernate was counting both. This error can be "fixed" by adding a method to your repository class.

By overriding the class count you can manually determine the way this is counted.

@Override 
public Integer count(Page<MyObject> page) {
   // manual counting method here
}

score 1 · Answer 7 · edited Aug 13 '21 at 11:33

1

I was using JPQL and wanted to return Map. In my case, the reason was that I wanted to get Map<String, String>, but had to expect List<Map<String, String>> :)

edited Aug 13 '21 at 11:33

Ole Pannier

3,208
9
22
33

answered Aug 13 '21 at 09:15

Maks Chornyi

89
1
3
11

madhav · Answer 8 · 2021-11-23T09:21:23.453

Check your table, where one entity occurring multiple time's.

I had the same error, with this data :

id	amount	clientid	createdate	expiredate
428	100	427	19/11/2021	19/12/2021
464	100	459	22/11/2021	22/12/2021
464	100	459	22/11/2021	22/12/2021

You see here clientid occurring two times with 464.

I solved it by deleting one row :

id	amount	clientid	createdate	expiredate
428	100	427	19/11/2021	19/12/2021
464	100	459	22/11/2021	22/12/2021

score 1 · Answer 9 · answered Nov 26 '21 at 09:21

1

I have found the core of the problem:

result of SELECT COUNT(*) can be a list, if there is a GROUP BY in the query,

and sometimes Hibernate rewrite your HQL and put a GROUP BY into it, just for fun.

answered Nov 26 '21 at 09:21

bence of outer space

198
2
4

score 0 · Answer 10 · answered Apr 03 '19 at 12:48

Basically your query returns more than one result set. In API Docs uniqueResult() method says that Convenience method to return a single instance that matches the query, or null if the query returns no results

uniqueResult() method yield only single resultset

James · Answer 11 · 2019-12-12T12:53:18.160

0

Could this exception be thrown during an unfinished transaction, where your application is attempting to create an entity with a duplicate field to the identifier you are using to try find a single entity?

In this case the new (duplicate) entity will not be visible in the database as the transaction won't have, and will never be committed to the db. The exception will still be thrown however.

edited Dec 12 '19 at 12:53

answered Dec 12 '19 at 12:29

James

512
3
7
14

score 0 · Answer 12 · answered Apr 26 '20 at 12:17

0

Thought this might help to someone, it happens because "When the number of data queries is greater than 1".reference

answered Apr 26 '20 at 12:17

pl-jay

970
1
16
33

score 0 · Answer 13 · answered Aug 03 '21 at 18:07

As what Ian Wang said, I suspect you are using a repository from spring. And a few days ago you just copy past a class and forgot to delete it when it is finally unused. Check that repository, and see if there is multiple same class of table you use. The count is not the count of rows, but the count of the table problem.

expelliarmus · Answer 14 · 2021-10-12T12:12:21.423

0

This means that orm technology is not preprogrammed to give you which results you are looking for because there are too many of the same results in the database. for example If there is more than one same value in my database and I want to get it back, you will encounter the error you get with the select query.

edited Oct 12 '21 at 12:12

answered Oct 12 '21 at 12:03

expelliarmus

39
4

3

Your post is unreadable but seems to be an attempt to answer the question. I recommend to [edit] with some help from a more fluent person to improve this according to [answer]. – Yunnosch Oct 12 '21 at 12:05

score 0 · Answer 15 · answered May 09 '22 at 11:56

For me the error is caused by spring.jpa.hibernate.ddl-auto=update in application.properties file replacing it with spring.jpa.hibernate.ddl-auto=create solved the issue, but it still depends on your needs to decide which configuration you need in your project, for more insights on the topic check this.

score -6 · Answer 16 · edited Feb 26 '15 at 10:23

-6

First you must test the query list size; here a example:

long count;
if (query.list().size() > 0)
    count=(long) criteria.list().get(0);   
else
    count=0;            
return count;

edited Feb 26 '15 at 10:23

Tobias

3,026
20
34

answered Feb 26 '15 at 09:45

Maodo

11

org.hibernate.NonUniqueResultException: query did not return a unique result: 2?

16 Answers16

Linked