ChatGPT解决这个技术问题 Extra ChatGPT

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

getSingleResult() forces you to use exception handling in absence of a value, even though the absence of a value is a common and natural situation. Best practices are that exceptions should only be used for exceptional situations, which the absence of a value is not. A lot of people don't like getSingleResult() for this reason. Even the authors of Hibernate, from which JPA was born, criticize getSingleResult(). If you also don't like it, please upvote: github.com/eclipse-ee4j/jpa-api/issues/298

S
Sk8erPeter

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.


I don't agree, getSingleResult() is used in situations like: "I am totally sure that this record exists. Shoot me if it doesn't". I don't want to test for null every time I use this method because I am sure that it will not return it. Otherwise it causes a lot of boilerplate and defensive programming. And if the record really does not exist (as opposite to what we've assumed), it is much better to have NoResultException compared to NullPointerException few lines later. Of course having two versions of getSingleResult() would be awesome, but if I have to pick up one...
@cletus Null is indeed a valid return value for a database.
@TomaszNurkiewicz that's a good point. However, seems like there should be some type of "getSingleResultOrNull". I guess you could create a wrapper for such.
Here is some info in term of benefit of exception begin thrown from getSingleResult(): Queries can be used to retrieve almost anything including the value of a single column in a single row. If getSingleResult() would return null, you could not tell whether the query did not match any row or whether the query matched a row but the selected column contains null as its value. from: stackoverflow.com/a/12155901/1242321
It should return Optional. That is a good way to indicate missing values.
P
Pang

Try this in Java 8:

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

You can get rid of the Optional by adding .orElse(null)
It can be better: em.createQuery("...").getResultStream().findFirst().orElse(null);.
E
Eugene Katz

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

Note that you can be a bit more optimal by calling Query.setMaxResults(1). Sadly, since Query is stateful, you'll want to capture the value of Query.getMaxResults() and fix up the object in a try-finally block, and maybe just fail altogether if Query.getFirstResult() returns anything interesting.
that's how we have it implemented on our project. Never had any issues with this implementation
n
nbrooks

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

Neat! I'd accept TypedQuery<T> though, in which case the getResultList() is then already correctly typed as a List<T>.
In combination with fetch() the entity might not be completely populated. See stackoverflow.com/a/39235828/661414
This is a very nice approach. Note that setMaxResults() has a fluent interface so you can write query.setMaxResults(1).getResultList().stream().findFirst().orElse(null). This should be the most efficient call scheme in Java 8+.
D
Didier L

Spring has a utility method for this:

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

Keep in mind, that using DataAccessUtils.singleResult will throw an exception when there is more than one element.
L
LovaBill

I've done (in Java 8):

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

what you mean by query?
You mean HibernateQuery? What if I want to use the pure JPA api? There ain't such a method in javax.persistence.Query
@EnricoGiurin, I've edited the snippet. Work fine. No try-catch, and no list.size check. Nicest one liner solution.
g
geisterfurz007

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

or simply .getResultStream().findFirst() for an Optional
S
Sorter

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

E
Emmanuel Touzery

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

a
aces.

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.


C
Carl Smotricz

So don't do that!

You have two options:

Run a selection to obtain the COUNT of your result set, and only pull in the data if this count is non-zero; or 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.


Option 3 Try/catching NoResultException
P
Peter Walser

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

a
at_sof

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


T
TylerH

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


L
Leandro Ferreira

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


t
tpdi

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


P
Pete Montgomery

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

t
tg44

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.


Thank God someone pointed out an obvious truth: if OP is calling getSingleResult() he is expecting the result to be unique, not to just get which happens to be the first in a (possibly unordered) query! With Java8 it's even cleaner: getResultList().stream().distinct().reduce((a, b) -> {throw new NonUniqueResultException();}).orElse(null);
U
Uchephilz

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:

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


s
splashout

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

M
Mohitkatta
            `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.


p
peterzinho16

Thats works to me:

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