what is better: getSingleResult, or getResultList JPA

Question

i need to retrieve single row from table, and i was interested what approach is better. On the one side getSingleResult is designed for retrieving single result, but it raises exception. Does this method have benefit in performance related to getResultList with

query.setFirstResult(0);
query.setMaxResults(1);

"but it raises ecxeption." What exception is it raising? (Actually both of them can raise exception). I don't know about the performance (whichever is more performant, it should be negligible) but think `getSingleResult` makes your code more readable. — Bhesh Gurung, Dec 14 '11 at 05:58
yes, i agree that getSingleResult much more readable. I just want to find out what aproach is more efficient. — Andrey, Dec 14 '11 at 06:18

Tomasz · Answer 1 · 2012-08-23T15:22:57.117

24

According to Effective Java by Joshua Bloch:

Use checked exceptions for conditions from wich the caller can reasonably be expected to recover. Use runtime exceptions to indicate programming errors.

Credit to the source: Why you should never use getSingleResult() in JPA

@Entity
@NamedQuery(name = "Country.findByName", 
            query = "SELECT c FROM Country c WHERE c.name = :name"
public class Country {
  @PersistenceContext
  transient EntityManager entityManager;

  public static Country findByName(String name) {
      List<Country> results = entityManager
            .createNamedQuery("Country.findByName", Country.class)
            .setParameter("name", name).getResultList();
      return results.isEmpty() ? null : results.get(0);
  }
}

edited Aug 23 '12 at 15:22

answered Aug 23 '12 at 15:16

Tomasz

5,269
8
56
65

1

Thanks alot, very reasonable explanation – Andrey Aug 29 '12 at 10:45
2

There are very few absolutes in Java and the one this article preaches is not valid for all use cases. If for example the query should always return one row to satisfy some biz requirement why spend the cycles to marshall a list of results to check the count to see if the biz condition was violated when you can call getSingleResult and catch the NoResultException/NoUniqueResultException exceptions and take appropriate action? Its less code and it uses exceptions to enforce exceptional biz logic conditions. – NBW May 31 '13 at 21:26
1

@NBW short answer because exception handling is more resource consuming and generally considered an anti-pattern in thsi case http://programmers.stackexchange.com/questions/107723/arguments-for-or-against-using-try-catch-as-logical-operators http://programmers.stackexchange.com/questions/189222/are-exceptions-as-control-flow-considered-a-serious-antipattern-if-so-why http://c2.com/cgi/wiki?DontUseExceptionsForFlowControl – Tomasz Jun 01 '13 at 23:20
1

Any reason for no getSingleOrNullResult method ? Seems as it's a rather common need. – TheBakker Feb 15 '17 at 14:01

score 10 · Accepted Answer · edited Dec 14 '11 at 07:21

10

getSingleResult throws NonUniqueResultException, if there are multiple rows. It is designed to retrieve single result when there is truly a single result.

The way you did is fine and JPA is designed to handle this properly. At the same time, you cannot compare it against getSingleResult any way, since it won't work.

However, depend on the code you are working on, it is always better to refine the query to return single result, if that's all what you want - then you can just call getSingleResult.

edited Dec 14 '11 at 07:21

Bhesh Gurung

50,430
22
93
142

answered Dec 14 '11 at 06:06

勿绮语

9,170
1
29
37

1

but i need to handle notFound exception. This part of code needs to get user account entity by email confirmation code there. I understand, that much clearer to use getSingleResult for this, but i'm aware of exception handling overhead – Andrey Dec 14 '11 at 06:14
In combination with `fetch()` the usage of `setMaxResults(1)` can lead to a partially initialised objects. See below for an example. – Leukipp Aug 30 '16 at 19:36

score 3 · Answer 3 · answered Jul 02 '12 at 16:12

3

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.

answered Jul 02 '12 at 16:12

aces.

3,902
10
38
48

1

why CollectionUtils instead of just elementList.isEmpty()? – Tomasz Aug 21 '12 at 22:22
@Tomasz Using the [http://commons.apache.org/collections/apidocs/org/apache/commons/collections/CollectionUtils.html#isEmpty(java.util.Collection)], the isEmpty() returns true if list is null or empty. CollectionUtils provides easy code readability instead of having to write this: `(elementList != null) && !elementList.isEmpty().` – aces. Aug 22 '12 at 05:46
1

Which provider are you using? EclipseLink's [EJBQueryImpl.getResultList()](http://grepcode.com/file/maven.glassfish.org/content/repositories/eclipselink/org.eclipse.persistence/org.eclipse.persistence.jpa/2.1.1/org/eclipse/persistence/internal/jpa/EJBQueryImpl.java#EJBQueryImpl.getResultList%28%29) returns an empty list to begin with never null – Tomasz Aug 22 '12 at 19:06
@Tomasz You are correct in case of EclipseLink, `getResultList()` does not return null and null check is not needed. I was talking in general, with different provider implementations,it's better to have a null check IMO. – aces. Aug 22 '12 at 19:24
1

Hibernate as a provider never returns a null list either. I'm curious what provider would, exactly – Alkanshel Jan 29 '16 at 19:13

score 1 · Answer 4 · answered Sep 11 '13 at 14:20

1

getSingleResult throws NonUniqueResultException, if there are multiple rows or no any rows . It is designed to retrieve single result when there is truly a single result.

answered Sep 11 '13 at 14:20

nir

171
2
9

score 1 · Answer 5 · answered Aug 30 '16 at 19:39

In combination with fetch() the usage of setMaxResults(1) can lead to a partially initialised objects. For example,

CriteriaQuery<Individual> query = cb.createQuery(Individual.class);
Root<Individual> root = query.from(Individual.class);
root.fetch(Individual_.contacts);

query.where(cb.equal(root.get(Individual_.id), id));

Individual i = em.createQuery(query)
    .setMaxResults(1) // assertion fails if individual has 2 contacts
    .getResultList()
    .get(0);
assertEquals(2, i.getContacts().size());

So, I am using getResultList() without limit -- a bit unsatisfying.

what is better: getSingleResult, or getResultList JPA

5 Answers5

Linked