org.hibernate.NonUniqueResultException: query did

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

Answer 1

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.

Answer 2

Hibernate Optional findTopByClientIdAndStatusOrderByCreateTimeDesc(Integer clientId, Integer status);

"findTop"!! The only one result!

Answer 3

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;

Answer 4

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

Answer 5

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

Answer 6

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