Don't use 'Query.list()' when you really need 'Query.uniqueResult()' in Hibernate.

I was motivated to discuss a little about this topic, since I am seeing a usage of hibernate which is capable of making server issues in your application. What I am going to discuss here, is very simple, but some developers are tend to do it in wrong way. Sometime, they are doing this intentionally for safe side.

When we are using hibernate Query to fetch a single instance, Query.uniqueResult() is being used except special scenarios. To use Query.uniqueResult(), we must make sure that query will return a single object. But some programmers are still used to do it with Query.list() method which can pull your application out into very inconsistency situation and issues which are very hard to discover the reason for failure. 

Just have a look into the following example.

public Account getAccountByAccountIdAndType(Long accountId, AccountType accountType) throws FetchAccountException {
 
    Account account = null;
    Query query = getSession().getNamedQuery("getAccountByAccountId");
    query.setLong("accountId", accountId); 
    query.setString("accountType", AccountType.SAVING.toString());
    List<Account> list = query.list();
    if (null != list && list.size() > 0) {
         account = list.get(0);
    }
    return account;
}

The above function is responsible to get Account instance for a given account number and type. Let's assume there can not be two accounts with the same type having same account number. 

If you carefully see the above code, the developer has applied many safe factors to minimize the exceptions. Some are even ambiguous. The above method will be functioning well until there is one Account with same type and account number and you expects that too. And also, it will execute well when there are more than one Account with same type and account number, which results wrong output and we don't actually need. 

What will happen, if there are more than one accounts with same type and same account number?

That will be a total failure of your application and you will have to compensate if banking system will be on this kind of state. Let's see how the above method will behave in this kind of situation. The above method will hide this critical exception and keeps application flow functioning. The function will get a list which is having the accounts and will return the first Account instance. Ultimately, this will result wrong outputs in your application. 

The programmer has added a safe factor to keep application flow functioning even with more than one accounts with same type and number. This is unnecessary and a kind of cheat programming. The programmer can survive for some time, the company who is working for, will have to compensate for loses. This is very poor programming. Some programmers do this intentionally as a safe factor, some are doing this as a quick fix for a defect. But this is very very dangerous.

Further the programmer has checked the "list" for NULL which is ambiguous, because hibernate does not return NULL list, instead it always returns empty list.

And other shortage of above method is, it may return NULL as the output which is not a good programming practice. Instead, you can throw an exception.

The getAccountByAccountIdAndType() method must return an exception in state. If the programmer used Query.uniqueResult() for this purpose, it returns the following exception keeping every one enlighten. We can take immediate action to get rid of this killer situation.

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

We can improve the above function as follows.

public Account getAccountByAccountIdAndType(Long accountId, AccountType accountType) {
 
    Query query = getSession().getNamedQuery("getAccountByAccountId");
    query.setLong("accountId", accountId); 
    query.setString("accountType", AccountType.SAVING.toString());
    Account account = (Account)Query.uniqueResult();
    
    if (account == null) {
         throw new RuntimeException("Unable to find Account for account number :" + accountId);
    }
    return account;
}

Now the above method will return the exception when there are more than one account with same type and number.

If you want to send more specific error message rather than a hibernate exception message when you are having more than one account with same account number and type, you can put a try-catch block as follows.

public Account getAccountByAccountIdAndType(Long accountId, AccountType accountType) {
     
    Account account = null;
    try {
        Query query = getSession().getNamedQuery("getAccountByAccountId");
        query.setLong("accountId", accountId); 
        query.setString("accountType", AccountType.SAVING.toString());
        Account account = (Account)Query.uniqueResult();
    } catch(NonUniqueResultException) {
        throw new RuntimeException("Two account found with same account number and type : Acc No-" + accountId);
    }
    if (account == null) {
         throw new RuntimeException("Unable to find Account for account number :" + accountId);
    }
    return account;
}

As a conclusion, always use Query.uniqueResult() when you want to fetch a single object with hibernate Query and you should make sure your query will return a single object too.