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.