[java] JPA getSingleResult() or null

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

Thats works to me:

Optional<Object> opt = Optional.ofNullable(nativeQuery.getSingleResult());
return opt.isPresent() ? opt.get() : null;

I solved this by using List<?> myList = query.getResultList(); and checking if myList.size() equals to zero.

Look this code :

return query.getResultList().stream().findFirst().orElse(null);

When findFirst() is called maybe can be throwed a NullPointerException.

the best aproach is:

return query.getResultList().stream().filter(Objects::nonNull).findFirst().orElse(null);

Here's a typed/generics version, based on Rodrigo IronMan's implementation:

 public static <T> T getSingleResultOrNull(TypedQuery<T> query) {
    query.setMaxResults(1);
    List<T> list = query.getResultList();
    if (list.isEmpty()) {
        return null;
    }
    return list.get(0);
}

The undocumented method uniqueResultOptional in org.hibernate.query.Query should do the trick. Instead of having to catch a NoResultException you can just call query.uniqueResultOptional().orElse(null).

Here's the same logic as others suggested (get the resultList, return its only element or null), using Google Guava and a TypedQuery.

public static <T> getSingleResultOrNull(final TypedQuery<T> query) {
    return Iterables.getOnlyElement(query.getResultList(), null); 
}

Note that Guava will return the unintuitive IllegalArgumentException if the result set has more than one result. (The exception makes sense to clients of getOnlyElement(), as it takes the result list as its argument, but is less understandable to clients of getSingleResultOrNull().)

Spring has a utility method for this:

TypedQuery<Profile> query = em.createNamedQuery(namedQuery, Profile.class);
...
return org.springframework.dao.support.DataAccessUtils.singleResult(query.getResultList());

Combining the useful bits of the existing answers (limiting the number of results, checking that the result is unique) and using the estabilshed method name (Hibernate), we get:

/**
 * Return a single instance that matches the query, or null if the query returns no results.
 *
 * @param query query (required)
 * @param <T> result record type
 * @return record or null
 */
public static <T> T uniqueResult(@NotNull TypedQuery<T> query) {
    List<T> results = query.setMaxResults(2).getResultList();
    if (results.size() > 1) throw new NonUniqueResultException();
    return results.isEmpty() ? null : results.get(0);
}

There is an alternative which I would recommend:

Query query = em.createQuery("your query");
List<Element> elementList = query.getResultList();
return CollectionUtils.isEmpty(elementList ) ? null : elementList.get(0);

This safeguards against Null Pointer Exception, guarantees only 1 result is returned.

Here's another extension, this time in Scala.

customerQuery.getSingleOrNone match {
  case Some(c) => // ...
  case None    => // ...
}

With this pimp:

import javax.persistence.{NonUniqueResultException, TypedQuery}
import scala.collection.JavaConversions._

object Implicits {

  class RichTypedQuery[T](q: TypedQuery[T]) {

    def getSingleOrNone : Option[T] = {

      val results = q.setMaxResults(2).getResultList

      if (results.isEmpty)
        None
      else if (results.size == 1)
        Some(results.head)
      else
        throw new NonUniqueResultException()
    }
  }

  implicit def query2RichQuery[T](q: TypedQuery[T]) = new RichTypedQuery[T](q)
}

Try this in Java 8:

Optional first = query.getResultList().stream().findFirst();

So all of the "try to rewrite without an exception" solution in this page has a minor problem. Either its not throwing NonUnique exception, nor throw it in some wrong cases too (see below).

I think the proper solution is (maybe) this:

public static <L> L getSingleResultOrNull(TypedQuery<L> query) {
    List<L> results = query.getResultList();
    L foundEntity = null;
    if(!results.isEmpty()) {
        foundEntity = results.get(0);
    }
    if(results.size() > 1) {
        for(L result : results) {
            if(result != foundEntity) {
                throw new NonUniqueResultException();
            }
        }
    }
    return foundEntity;
}

Its returning with null if there is 0 element in the list, returning nonunique if there are different elements in the list, but not returning nonunique when one of your select is not properly designed and returns the same object more then one times.

Feel free to comment.

Here's a good option for doing this:

public static <T> T getSingleResult(TypedQuery<T> query) {
    query.setMaxResults(1);
    List<T> list = query.getResultList();
    if (list == null || list.isEmpty()) {
        return null;
    }

    return list.get(0);
}

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.

If you wish to use the try/catch mechanism to handle this problem.. then it can be used to act like if/else. I used the try/catch to add a new record when I didn't find an existing one.

try {  //if part

    record = query.getSingleResult();   
    //use the record from the fetched result.
}
catch(NoResultException e){ //else part
    //create a new record.
    record = new Record();
    //.........
    entityManager.persist(record); 
}

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

I've done (in Java 8):

query.getResultList().stream().findFirst().orElse(null);

From JPA 2.2, instead of .getResultList() and checking if list is empty or creating a stream you can return stream and take first element.

.getResultStream()
.findFirst()
.orElse(null);

I achieved this by getting a result list then checking if it is empty

public boolean exist(String value) {
        List<Object> options = getEntityManager().createNamedQuery("AppUsers.findByEmail").setParameter('email', value).getResultList();
        return !options.isEmpty();
    }

It is so annoying that getSingleResult() throws exceptions

Throws:

1. NoResultException - if there is no result
2. NonUniqueResultException - if more than one result and some other exception that you can get more info on from their documentation