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?

Answer 1

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

Answer 2

Hibernate Optional findTopByClientIdAndStatusOrderByCreateTimeDesc(Integer clientId, Integer status);

"findTop"!! The only one result!