JPA getSingleResult() or null

Question

Hi. I have an insertOrUpdate method which inserts an Entity when it doesn't exist or update it if it does. To enable this, I have to findByIdAndForeignKey, if it returned null insert if not then update. The problem is how do I check if it exists? So I tried getSingleResult. But it throws an exception if the

public Profile findByUserNameAndPropertyName(String userName, String propertyName) {
    String namedQuery = Profile.class.getSimpleName() + ".findByUserNameAndPropertyName";
    Query query = entityManager.createNamedQuery(namedQuery);
    query.setParameter("name", userName);
    query.setParameter("propName", propertyName);
    Object result = query.getSingleResult();
    if(result==null)return null;
    return (Profile)result;
}

but "getSingleResult" throws an exception.

Thanks

Answer 1

Throwing an exception is how getSingleResult() indicates it can't be found. Personally I can't stand this kind of API. It forces spurious exception handling for no real benefit. You just have to wrap the code in a try-catch block.

Alternatively you can query for a list and see if its empty. That doesn't throw an exception. Actually since you're not doing a primary key lookup technically there could be multiple results (even if one, both or the combination of your foreign keys or constraints makes this impossible in practice) so this is probably the more appropriate solution.

Answer 2

So don't do that!

You have two options:

1. Run a selection to obtain the COUNT of your result set, and only pull in the data if this count is non-zero; or

2. Use the other kind of query (that gets a result set) and check if it has 0 or more results. It should have 1, so pull that out of your result collection and you're done.

I'd go with the second suggestion, in agreement with Cletus. It gives better performance than (potentially) 2 queries. Also less work.

Answer 3

I encapsulated the logic in the following helper method.

public class JpaResultHelper {
    public static Object getSingleResultOrNull(Query query){
        List results = query.getResultList();
        if (results.isEmpty()) return null;
        else if (results.size() == 1) return results.get(0);
        throw new NonUniqueResultException();
    }
}