JPA getSingleResult() or null

Question

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 — Devabc
– Devabc, Commented Nov 29, 2020 at 23:47

Sk8erPeter · Accepted Answer · 2014-04-02 01:03:26Z

306

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.

edited Apr 2, 2014 at 1:03

Sk8erPeter

7,0059 gold badges51 silver badges67 bronze badges

answered Jan 4, 2010 at 23:19

cletus

627k169 gold badges922 silver badges945 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

Tomasz Nurkiewicz Over a year ago

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

Bill Rosmus Over a year ago

@cletus Null is indeed a valid return value for a database.

cbmeeks Over a year ago

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

user1242321 Over a year ago

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

Vivek Kothari Over a year ago

It should return Optional<T>. That is a good way to indicate missing values.

|

Pang · Accepted Answer · 2016-12-31 05:55:06Z

57

Try this in Java 8:

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

edited Dec 31, 2016 at 5:55

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Dec 31, 2016 at 5:49

Impala67

5874 silver badges2 bronze badges

3 Comments

Justin Rowe Over a year ago

You can get rid of the Optional by adding .orElse(null)

rios0rios0 Over a year ago

It can be better: em.createQuery("...").getResultStream().findFirst().orElse(null);.

basin Over a year ago

You lose NonUniqueResultException and there's no good workaround stackoverflow.com/questions/22694884/…

Eugene Katz · Accepted Answer · 2010-05-21 19:33:50Z

36

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

answered May 21, 2010 at 19:33

Eugene Katz

5,2927 gold badges44 silver badges49 bronze badges

2 Comments

Patrick Linskey Over a year ago

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.

walv Over a year ago

that's how we have it implemented on our project. Never had any issues with this implementation

nbrooks · Accepted Answer · 2014-11-25 22:02:16Z

25

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

edited Nov 25, 2014 at 22:02

nbrooks

18.2k5 gold badges58 silver badges68 bronze badges

answered Jun 16, 2011 at 20:14

Rodrigo IronMan

2593 silver badges2 bronze badges

3 Comments

Rup Over a year ago

Neat! I'd accept TypedQuery<T> though, in which case the getResultList() is then already correctly typed as a List<T>.

Leukipp Over a year ago

In combination with fetch() the entity might not be completely populated. See stackoverflow.com/a/39235828/661414

Dirk Hillbrecht Over a year ago

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

geisterfurz007 · Accepted Answer · 2019-10-21 07:33:57Z

23

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

edited Oct 21, 2019 at 7:33

geisterfurz007

5,9246 gold badges37 silver badges59 bronze badges

answered Jul 23, 2019 at 9:15

Serafins

1,2471 gold badge17 silver badges38 bronze badges

3 Comments

Markus Pscheidt Over a year ago

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

Kamil Bęben Over a year ago

This defaults to getResultList().stream(). Unless you're confident that the implementation you're using overrides it, this can lead to performance issues, because fetching all rows vs fetching one row can be a difference between 20 ms and 20 minutes.

Brad Over a year ago

Yes, this example just needs .setMaxResults(1) adding to it, to make it efficient for querying single results. Ideally the API should be extended to include something like ... Optional<T> getSingleResultMaybe()

LovaBill · Accepted Answer · 2018-05-17 13:00:33Z

22

I've done (in Java 8):

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

edited May 17, 2018 at 13:00

LovaBill

5,1291 gold badge26 silver badges32 bronze badges

answered Sep 21, 2017 at 12:32

Zhurov Konstantin

7621 gold badge8 silver badges14 bronze badges

3 Comments

Enrico Giurin Over a year ago

what you mean by query?

Enrico Giurin Over a year ago

You mean HibernateQuery? What if I want to use the pure JPA api? There ain't such a method in javax.persistence.Query

LovaBill Over a year ago

@EnricoGiurin, I've edited the snippet. Work fine. No try-catch, and no list.size check. Nicest one liner solution.

Didier L · Accepted Answer · 2015-04-28 09:37:56Z

20

Spring has a utility method for this:

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

edited Apr 28, 2015 at 9:37

Didier L

20.8k10 gold badges70 silver badges113 bronze badges

answered Jul 23, 2014 at 19:05

heenenee

20.2k1 gold badge67 silver badges90 bronze badges

1 Comment

Serafins Over a year ago

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

Sorter · Accepted Answer · 2013-01-12 17:02:52Z

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

Emmanuel Touzery · Accepted Answer · 2013-03-27 12:54:06Z

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

aces. · Accepted Answer · 2012-07-02 16:16:07Z

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.

Carl Smotricz · Accepted Answer · 2010-01-04 23:19:35Z

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.

Peter Walser · Accepted Answer · 2016-04-08 08:01:56Z

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

at_sof · Accepted Answer · 2017-05-18 09:02:54Z

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

Leandro Ferreira · Accepted Answer · 2019-02-16 04:28:18Z

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

TylerH · Accepted Answer · 2018-01-22 16:59:03Z

2

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

edited Jan 22, 2018 at 16:59

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Jan 22, 2018 at 14:35

Сергій Катрюк

617 bronze badges

Comments

tpdi · Accepted Answer · 2011-03-17 07:05:35Z

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

Pete Montgomery · Accepted Answer · 2012-08-07 10:17:00Z

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

tg44 · Accepted Answer · 2015-11-04 15:01:48Z

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

splashout · Accepted Answer · 2021-07-20 22:09:47Z

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

Uchephilz · Accepted Answer · 2019-05-12 21:45:53Z

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

Mohitkatta · Accepted Answer · 2022-02-23 03:58:48Z

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

peterzinho16 · Accepted Answer · 2019-01-10 18:04:35Z

-3

Thats works to me:

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

answered Jan 10, 2019 at 18:04

peterzinho16

9891 gold badge16 silver badges19 bronze badges

1 Comment

Alexander R. Over a year ago

The whole point of Optional is to not return null. Why not return the Optional directly.

Collectives™ on Stack Overflow

JPA getSingleResult() or null

22 Answers 22

14 Comments

3 Comments

2 Comments

3 Comments

3 Comments

3 Comments

1 Comment

Comments

Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

22 Answers 22

14 Comments

3 Comments

2 Comments

3 Comments

3 Comments

3 Comments

1 Comment

Comments

Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

Comments

Comments

1 Comment

Linked

Related