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

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?

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

Hibernate Optional findTopByClientIdAndStatusOrderByCreateTimeDesc(Integer clientId, Integer status);

"findTop"!! The only one result!

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.

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.

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

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
}

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.

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

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.

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

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.

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

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.

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

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.

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;