JPA - getSingleResult vs LIMIT 1

Question

What is the difference between .getSingleResult() and LIMIT 1 for example in the below hibernate 6 query:

@Override
public Test customQuery3(Long id) {
return (Test) entityManager.createQuery(
                "SELECT NEW com.view.Test(a.id, b) FROM Author a " +
                        "LEFT JOIN Book b ON b.author.id=a.id " +
                        "WHERE b.id=:id " +
                        "GROUP BY a, b ORDER BY b.newestBook DESC LIMIT 1")
        .setParameter("id", id)
        .getSingleResult();

}

From reading SQL documentation LIMIT 1 restricts the results to one and in the example above the newest.

The documentation on .getSingleResult() appears to do the same albeit with some errors thrown if the id is not found.

So why are both needed?

I think both are same albeit limit is in query level while getSingleResult() is api level. Also if someone messes up the query, the method ensures that is returns single result always. — hermit, Sep 01 '23 at 00:50
`getSingleResult` was meant for queries that are *guaranteed* to return a single result, like `SELECT SUM / AVG` etc. For all queries that *might* return a single result (but might also return no results, depending on a business condition), `getResultList` was intended. Also remember that sometimes (e.g. with `FETCH JOIN`), **1 result = multiple result rows**, so `LIMIT 1` does not always make sense. For similar reasons, `setFirstResult() / setMaxResults()` is not always equivalent to setting `OFFSET / LIMIT / ROWNUM` — crizzis, Sep 01 '23 at 08:38

score 2 · Accepted Answer · answered Sep 01 '23 at 01:36

2

The doc says if the query returned more than one row, it throws NonUniqueResultException. Besides, when using getResultList(), it returns a List rather than an Object. When you know it SHOULD return one row, better to use getSingleResult(), because returning more rows in this case is a logic error or the data is in an inconsistent state, better to stop processing.

answered Sep 01 '23 at 01:36

Cavor Kehl

38
2

So are they doing the same thing, albeit some have better exception messages? – Ray Bond Sep 01 '23 at 01:53
So LIMIT 1 just limits the results 1 (and has the sort) - whereas getOneResult doesnt get a single result from a resultlist, but is a check that the query returns 1. – Ray Bond Sep 01 '23 at 02:01
@RayBond Yes, it basically act as a wrapper like: 1. Execute getResultList() and get the result. 2. Check if the result has and only has one row.(turns out it throws Exception too when 0 rows is returned, since null has other meaning. Here's [another question on this topic](https://stackoverflow.com/questions/2002993/jpa-getsingleresult-or-null) ) 3. If everything goes as expected, return the row. – Cavor Kehl Sep 01 '23 at 03:04

JPA - getSingleResult vs LIMIT 1

1 Answers1