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.
getResultList()

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

getSingleResult()

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 a fetch 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;
if(!results.isEmpty()){
    // ignores multiple results
    foundEntity = results.get(0);
}

13 thoughts on “Why you should never use getSingleResult() in JPA

  1. Erwin Ravau

    This was the argument to convince me :”When a getSingleResult Query returns no rows, we get an unchecked exception, thus a programmer error”
    thx for the post.

    Reply
  2. Lukas

    You didn’t get a NoResultException if using getResultList(). In this case, no result means the result List is empty.

    Reply
  3. venu

    Hi i am using jpa query for change password scenario ,i am getting the single result ,
    if i have only one user every thing works fine for me ,but when i have 2 users,first i changed the first user’s password,and very next i try to change the second user’s password i am getting the exception there is more than one result exception.please suggest me what i done the mistake,this is my method calling when i click submit

    @Transactional(propagation = Propagation.REQUIRED)
    @RequestMapping(value = “/resetPass”, method = RequestMethod.POST)
    public String resetPassword(HttpServletRequest request,
    HttpServletResponse response) throws Exception {

    SecurityEngine engine = new SecurityEngineImpl();

    String currentPass = request.getParameter(“current”);

    currentPass = engine.encrypt(currentPass, AlgorithmTypes.DES);

    String newPass = request.getParameter(“newPass”);

    MobeeRobotParam query = (MobeeRobotParam) entityManager.createQuery(
    ” from Mobee_Robot_Param where paramValue =:CURRENT”)
    .setParameter(“CURRENT”, currentPass).getSingleResult();

    if (currentPass.equals(query.getParamValue())) {
    newPass = engine.encrypt(newPass, AlgorithmTypes.DES);

    int mobeeList = entityManager
    .createQuery(
    ” update Mobee_Robot_Param set paramValue=:NEWPASS where id=:ID”)
    .setParameter(“NEWPASS”, newPass).setParameter(“ID”,
    query.getId()).executeUpdate();

    } else {

    throw new Exception(
    “current password and database password or not matching”);
    }

    return “redirect:/mainPage”;

    }

    Reply
  4. Martin

    If you use getResultList(), you should also use setMaxResults(1) (or setMaxResults(2) if you want to check if the result is unique). Otherwise you might retrieve a bunch of data and just throw it away.

    getSingleResult() throws an exception if the result is not unique, which I find appropriate.

    I usually do this in a service class:
    public Foo find(int id) {
    try {
    return em.find(Foo.class, int id);
    } catch(NoResultException ex) {
    return null;
    }
    }

    Reply
  5. Jai

    getSingleResult will be helpful sometimes if you use native querys. In some situation we have go with native queries and getSingleResult method will be handy.

    Reply
  6. Doug

    setMaxResult(1) is a horrible idea. You expected to get one thing back. If more than one could *possibly* come back, you have a completely unpredictable system. setMaxResult(2) isn’t bad, but are you really saying that you’d prefer to get a list, check the size, then get the first item, instead of just getSingleResult?

    Yes, try/catch is a bit ugly, but blindly following the Effective Java mantra is not healthy. Just about any “why you should never [...]” advice in software engineering can be ignored because it is narrow-minded. Why not just use AOP to intercept the exception and translate it to null? I’m afraid this article is not of much value.

    Reply
  7. Rick O'Shea

    Entitling this “why you should never” followed by an enumeration of “why you would” use getSingleResult is nonsensical. Moreover, you missed the most obvious case: when the data should only ever have one result, regardless of the query or whether you are using an aggregate function. It’s crystal clear when to use getSingleResult. The real issue here is more Java plumbing madness rather than simple extension. Using .NET, you have SingleOrDefault, FirstOrDefault. Instead, JPA asumes that 100,000 or 1,000,000 programmers should write their own version of that obvious gap. Regardless, the correct solution is to write a version of SingleOrDefault.

    Reply
  8. Pingback: » Por que no deberíamos utilizar getSingleResult() en JPA

  9. Manuel Pourtois

    I find that throwing a runtime exception is the correct expected behavior of getSingleResult.
    I don’t agree with the statement ‘We never know for sure what we can expect from our database’. Attempt to recover automagicaly from a missing expected Entity may induce infinite trouble. Incoherent database or faulty program assertion is not a recoverable issue
    My exception mantra :
    - never use checked exception
    - only catch runtime to rethrow a more documented runtime exception

    Reply
  10. M

    When you’re loading entity using primary key or any unique alternative key, then there’s no way that you would receive more than one record, so the exception indicates invalid database state which must never occur.

    On the other hand when you receive 0 zero records, you can always catch exception and handle it. In some cases though you may be 100% sure that the entity is there in the DB and if it’s not you should see exception due to invalid database state. One example is when you’re looking up entity using a key obtained from other entity. If the entity is not found, this is a problem with database consistiency and is a purely technical issue.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>