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

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.

Try this in Java 8:

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

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

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

Spring has a utility method for this:

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

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

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

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

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.

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.

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

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

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

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

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.

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

I prefer @Serafins answer if you can use the new JPA features, but this is one fairly straight forward way to do it which I'm surprised hasn't been mentioned here before:

	try {
		return (Profile) query.getSingleResult();
	} catch (NoResultException ignore) {
		return null;
	}

			`public Example validate(String param1) {
				// TODO Auto-generated method stub

				Example example = new Example();
				
				Query query =null;
				Object[] myResult =null;
				
				try {

					  query = sessionFactory.getCurrentSession()
				      .createQuery("select column from table where 
                      column=:p_param1");
				      query.setParameter("p_param1",param1);
				  }
				
					  	myResult = (Object[])query.getSingleResult();//As your problem occurs here where the query has no records it is throwing an exception
					
					    String obj1 = (String) myResult[0];
					    String obj2 = (String) myResult[1];
					    
					    

example.setobj1(ISSUtil.convertNullToSpace(obj1)) example.setobj2(ISSUtil.convertNullToSpace(obj2));

				return example;
				
				}catch(Exception e) {
					e.printStackTrace();
					  
                 example.setobj1(ISSUtil.convertNullToSpace(""));//setting 
                 objects to "" in exception block
			     example.setobj1(ISSUtil.convertNullToSpace(""));
				}
				return example;
			}`

> Answer : Obviously when there is no records getsingleresult will throw an exception i have handled it by setting the objects to "" in the exception block even though it enter the exception you JSON object will set to ""/empty > Hope this is not a perfect answer but it might help If some needs to modify my code more precisely and correct me always welcome.

Thats works to me:

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