CODEWITHSUNDEEP
javadatabasejpajpql
Wil Ferraciolli
Wil Ferraciolli

Reputation: 477

How to find first in JPQL

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.

Upvotes: 4

Views: 5770

Answers (3)

Roma Khomyshyn
Roma Khomyshyn

Reputation: 1152

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

Upvotes: 2

MrSansoms
MrSansoms

Reputation: 55

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

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

Upvotes: 0

SJuan76
SJuan76

Reputation: 24780

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

Upvotes: 2

Related Questions