Why you should never use getSingleResult() in JPA

As long as I’m using JPA I notice that the use of getSingleResult and getResultList is often confused. In the javadoc there is no clear specification of what to use.

Execute a SELECT query and return the query results as an untyped List.


Execute a SELECT query that returns a single untyped result.

So this doesn’t get us much further. So lets explore the problem. Both methods are used to retrieve Entities from our database. We use the getResultList() if there is no guarantee how many results we want to retrieve, the getSingleResult is used to retrieve exactly one row.
So the only doubt for what method to use, is when we retrieve 1 row from our database. A perfect example for this use-case is a findById query, where we retrieve an Entity based upon its ID.
There are 3 ways to do this. The most appropriate way is to use the EntityManger.find() method. This method returns a typed instance of the entity when it is found, null when it is not found.
The other 2 ways is by a NamedQuery. Why would we want to use a NamedQuery when we have the find method? Sometimes we need to retrieve a lazy collection, by adding afetch join. An other example is when we have a composite key, we can still use the EntityManager.find() method, but I often see the use of a named query in this case. (don’t do this, bad programming)
So we created our named query

final Query query = getEntityManager().createNamedQuery("Entity.findById");
query.setParameter("id", id);

The most natural reaction is to call the getSingleResult() method on the query. This returns only one row and the result of a ‘findById’ query should only be one row. But what if the row isn’t in the database (anymore)? We get an unchecked exception: NoResultException. Is this what we expect?
If you look in Effective Java by Joshua Bloch, we read: ‘Use checked exceptions for conditions from wich the caller can reasonably be expected to recover. Use runtime exceptions to indicate programming errors’.
So what does this tell us? When a getSingleResult Query returns no rows, we get an unchecked exception, thus a programmer error, there is no way to recover from this. This is not correct. We never know for sure what we can expect from our database, so throwing an unchecked exception seems the wrong choice for this use-case.
The only use for getSingleResult() is when we are executing a scalar (count, sum, avg) query. This is a query wich will alway return a row, otherwise we are in an exceptional case, so the exception is allowed.
So, how do we solve our example with our named query? Simple

List results = query.getResultList();
Entity foundEntity = null;
    // ignores multiple results
    foundEntity = results.get(0);

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s