[java] 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(); //line 1

Hibernate throws below exception at line 1

    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?

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.

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.

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

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;

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

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.

Hibernate Optional findTopByClientIdAndStatusOrderByCreateTimeDesc(Integer clientId, Integer status);

"findTop"!! The only one result!

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

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