How to find first in JPQL

Question

I have a query to find if a value already exists within the database. I am using it to validate a resource before it is inserted onto the database. it looks like this:

  @NamedQuery(name = "findByName", query = "SELECT g FROM Group g where g.value= :value")

and on the data access the implementation is as follows:

final TypedQuery<Group> query = entityManager.createNamedQuery("findByName", Group.class);
    query.setParameter("value", value);
    return !query.getResultList().isEmpty();

It does work and does the job, however I think that query.getResultList().isEmpty() is not the right syntax that I need plus I was looking to make it faster and return once the value is found rather than looping through every row on the database. Any suggestions will be appreciated.

Have you thought about count? **[Spring Data JPA and Exists query](http://stackoverflow.com/questions/30392129/spring-data-jpa-and-exists-query)** — Karolis, Mar 15 '17 at 15:14
Have you thought about COUNT? **[Solution](http://stackoverflow.com/questions/30392129/spring-data-jpa-and-exists-query?answertab=active#tab-top)** — Karolis, Mar 15 '17 at 15:16
unfortunately Spring is not an option as we went the other way. Now doing a COUNT definitely improves it a bit. However I am still not sure about query.getResultList() for this query. cheers — Wil Ferraciolli, Mar 15 '17 at 15:22

score 2 · Answer 1 · answered Mar 15 '17 at 15:16

You have two options:

Use the COUNT function:
```
@NamedQuery(name = "findByName", query = "SELECT COUNT(g) FROM Group g where g.value= :value")
```
This removes the penalty for creating the actual entities (plus eager loading, etc.)
Use TypedQuery.setMaxResults() to set the maximum number of results retrieved to 1.
```
query.setMaxResults(1);
```

Roma Khomyshyn · Accepted Answer · 2017-03-15T15:26:24.403

2

What about:

 @NamedQuery(name = "existsByName", query = "SELECT CASE WHEN COUNT(g) > 0 THEN true ELSE false END FROM Group g where g.value= :value")

boolean exists = entityManager.createNamedQuery("existsByName",Boolean.class).setParameter("value",value).getSingleResult();

edited Mar 15 '17 at 15:26

answered Mar 15 '17 at 15:23

Roma Khomyshyn

1,112
6
9

Interesting, that looks very clean as well. Thank you very much. – Wil Ferraciolli Mar 15 '17 at 15:25

MrSansoms · Answer 3 · 2017-03-15T15:18:15.393

0

Maybe you could look at using pagination to improve the speed.

query.setMaxResults(1).getResultList() or setFirstResult()

edited Mar 15 '17 at 15:18

answered Mar 15 '17 at 15:12

MrSansoms

55
1
5

How to find first in JPQL

3 Answers3