Query Cache and Find in DB before insert

Hibernate ORM
1

Hello
I am new the working with hibernate second level cache and query cache.

In my current work, I am working with a legacy application. There is lot of code that does following

  • Execute a complex query (legacy have to look in few tables to make sure it does not exist)
  • if no results returned then insert

We were thinking of using query cache to cache these expensive queries. However from what I have read is that once a query is executed its result will be cached.

Therefore in above scenario - if we re-execute same query we will always get a empty result.

What will be the proper way to do this?

a. Do not search - insert and catch ConstraintViolationException to determine entity exist?
b. Put these query in a region and purge that region on insert
c. Change CacheMode to refresh after insert and run the query for new entity to be cached
d. Do not cache these queries

Thank You for the help

2

This depends on many factors. You have to be aware that query caching is not going to magically solve all your problems. Depending on how you “change” the data that is needed for the query, a query cache can be invalidated quite often.

Hibernate will take care of clearing the query cache when you do a insert/update/delete to a table that affects the query, so you don’t need to worry about this. What you have to worry about though is, if the data changes frequently, the caching might have a bad effect on performance due to invalidations.

If all you want to do is a “upsert” you can try to use @SQLInsert and use database native features for doing the upsert. See here for example: spring - Hibernate Transactions and Concurrency Using attachDirty (saveOrUpdate) - Stack Overflow