How to check if a record exists using JPA

Question

I want to know whether a given record is present in a database or not. so far I have achieved this by writing a JPA query and the running it by getSingleResult() method. this would throw a NoResultException if the record with the given parameter does not exist. Of course, it's not a must for the record to exist, so it's the normal behaviour sometimes, that's why I asked to myself, is it neccessary to throw an Exception which I have to handle by a catch block? As far as I know the cost of Exception handling is quite big, so I'm not very satisfied with this solution, also, I don't even need the object, I only need to know it's existence in the DB.

Is there a better way to check whether an object exist or not? eg. using getResultList() and checking it's size maybe?

Good question, but you should change the title to "How to check if a record exists using JPA" or something similar. — Jim Tough, Dec 07 '10 at 12:17
update: repository exist(id) method. repo.exist(id) returns true if record exist or else false — Maheshbabu Jammula, Oct 12 '17 at 16:50

score 32 · Accepted Answer · answered Dec 07 '10 at 08:41

32

If you just want to know whether the object exists, send a SELECT COUNT to your database. That will return 0 or 1.

The cost of the exception handling isn't that big (unless you do that millions of times during a normal operation), so I wouldn't bother.

But the code doesn't really reflect your intention. Since getSingleResult() calls getResultList() internally, it's clearer like so:

public boolean objExists(...) {
    return getResultList(...).size() == 1;
}

If you query by object id and you have caching enabled, that will become a simple lookup in the cache if the object has already been loaded.

answered Dec 07 '10 at 08:41

Aaron Digulla

321,842
108
597
820

oh, you're absolutely right about the SELECT COUNT solution, I overlooked it for some reason. but the second part of your answer contains the information what I really needed, and many thanks for that. – Balázs Németh Dec 07 '10 at 08:46
do you agree that if you want to get the first result of the query, and it's usual that the query doesn't return anything, it's better to do 'query.setMaxResults(1)' and then 'getResultList()'? This way we would avoid handling the exception. (NOTE: I'm running this in a job that's called MANY times). – GuilhermeA Dec 23 '10 at 14:50
@GuilhermeA: There are too many variables to say for sure. Things that affect the performance: JDBC driver, support for the DB in your OR mapper (how do they implement `setMaxResults()` on the SQL level?), table size, the query itself, etc. Run a couple of performance tests to find out. – Aaron Digulla Dec 25 '10 at 23:37

Radu · Answer 2 · 2016-05-23T10:25:30.563

24

Try to avoid loading the entity into the session (getSingleResult()) just to check for it's existence. A count is better here. With the Criteria Query API it would look something like this:

public <E extends AbstractEntity> boolean exists(final Class<E> entityClass, final int id) {
    final EntityManager em = getEntityManager();
    final CriteriaBuilder cb = em.getCriteriaBuilder();

    final CriteriaQuery<Long> cq = cb.createQuery(Long.class);
    final Root<E> from = cq.from(entityClass);

    cq.select(cb.count(from));
    cq.where(cb.equal(from.get(AbstractEntity_.id), id));

    final TypedQuery<Long> tq = em.createQuery(cq);
    return tq.getSingleResult() > 0;
}

edited May 23 '16 at 10:25

answered May 07 '15 at 09:45

Radu

2,022
3
17
28

What is `AbstractEntity_`? – krizajb Nov 08 '18 at 15:56
JPA meta-model. Check [this answser](https://stackoverflow.com/questions/3037593/how-to-generate-jpa-2-0-metamodel). – Radu Nov 19 '18 at 04:21

score 10 · Answer 3 · answered Aug 30 '17 at 10:27

Simply use count(e) in your query, so no NoResultException will be thrown and you will avoid loading the entity object
So the Java code can be as follow:

public boolean isRecordExist() {
    String query = "select count(e) from YOUR_ENTITY e where ....";
    // you will always get a single result
    Long count = (Long) entityManager.createQuery( query ).getSingleResult();
    return ( ( count.equals( 0L ) ) ? false : true );
}

Hope that helps someone :)

score 4 · Answer 4 · answered Dec 07 '10 at 08:46

4

If you are searching by primary key you can also use Entitymanger.find(entityClass, primaryKey) which returns null when the entity does not exist.

answered Dec 07 '10 at 08:46

Jörn Horstmann

33,639
11
75
118

7

If the entity exists it will be loaded into the session, just to be left there. – Radu May 07 '15 at 14:47
@Radu It's been a while since I worked with jpa, but the same problem would apply to solutions using `getResultList`, right? – Jörn Horstmann May 08 '15 at 15:57
1

Yes, considering `getSingleResult()` also works with a result list internally. But what we need here is to just check for an entity's existence, without the possible overhead of loading it into the session. A count operation would always return a number, so we even avoid the NoResultException. – Radu May 09 '15 at 18:58
I think better is to use "select item.id where.." and setMaxResults to 1 instead of count, because counting through big table might be not efficient (sometime query must go through all records - depends on query) – Marek F Nov 21 '19 at 16:09

score 2 · Answer 5 · answered Dec 13 '17 at 23:28

here is a generic approach to work with a type T and an arbitrary ID value

public boolean exists(Object key) { 
    EntityManager entityManager = getEntityManager();
    Metamodel metamodel = entityManager.getMetamodel();
    EntityType<T> entity = metamodel.entity(entityClass);
    SingularAttribute<T, ? extends Object> declaredId = entity.getDeclaredId(key.getClass());
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    javax.persistence.criteria.CriteriaQuery<T> cq = cb.createQuery(entityClass);
    Root<T> from = cq.from(entityClass);
    Predicate condition = cb.equal(from.get(declaredId), key);
    cq.where(condition);
    TypedQuery<T> q = entityManager.createQuery(cq);
    return q.getResultList().size() > 0; 
}

score 1 · Answer 6 · answered Feb 08 '20 at 06:42

1

Try this

public boolean exists(Object id){
    return getEntityManager().find(entityClass, id)!=null;
}

answered Feb 08 '20 at 06:42

Van Tyc

11
1

score 0 · Answer 7 · answered May 18 '22 at 16:04

A much easier solution to this problem is expanding the repository with a function of the return type Optional<Datatype>. Using this within a stream with a filter applied, you can easily check if the object exists with .isPresent()

For example:

public interface ChatArchiveRepo extends JpaRepository<ChatArchive, Long> {

    List<ChatArchive> findByUsername(String username);

    Optional<ChatArchive> findByConversationUid(String conversationUid);

}

And within your function or stream:

.filter(conversation -> !chatArchiveRepo.findByConversationUid(conversation.getUid()).isPresent())

Back in the days JpaRepository was just a fantasy :) – Balázs Németh May 18 '22 at 19:26 — Balázs Németh, May 18 '22 at 19:26

score 0 · Answer 8 · edited May 26 '23 at 09:22

0

I would just add a custom method in jpa repository

 @Query("SELECT COUNT(ID) FROM  'name' WHERE transactionId =:id")



int findIfTxnPresentById(@Param("id") Long txnId);

edited May 26 '23 at 09:22

Pranav MS

2,235
2
23
50

answered Sep 30 '22 at 06:52

Vinod

76
1
6

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 04 '22 at 07:23

How to check if a record exists using JPA

8 Answers8