SQL equivalent of Javax Cache 'put' (INSERT or UPDATE)

Question

I am using javax cache along with database. I uses cache's APIs to get/put/delete entities and the database is behind this cache. For this,I am using CacheLoader and CacheWriter.

So, following are SQL's construct equivalent to cache API

1. SELECT -> get
2. INSERT -> put
3. DELETE -> delete

If I have entry already present in cache and I updated it, then I will get that value 'write' method only. But, since the value is present in database, I need to use UPDATE query.

How to identify which database operation to perform in cache's 'put' operation ?

Note : UPSERT is not good option from performance point of view.

Answer 1

If you put the value in the cache you can first check if the key is already there, in that case you need an UPDATE. If the key was not present, you need an INSERT. It sounds like you could benefit from an ORM with an L2 cache, such as Hibernate, which handles all these scenarios (and many more) for you.

Answer 2

There are several ways I can think of. Basically these are variations of:

Metadata in the database

Within an entity I have typically additional fields which are timestamps for insert and update and a modification counter which are handled by the object to relational mapper (ORM). That is very useful for debugging. The CacheWriter can check whether the insert timestamp is set, if yes, it is an update, if no it is an insert.

It does not matter whether the value gets evicted meanwhile, if your application is reading the latest contents through the cache and writes a modified version of it.

If your application does not read the data before modifying or this happens very often, I suggest to cache a flag that like insertedAlready. That leads to three way logic: isnerted, not inserted, not in the cache = don't know yet. In the letter case you need to do a read before update or insert in the cache writer.

Metadata in the cache only

The cached object stores additional data whether the object was read from the database before. Like:

class CachedDbValue<V> {
  boolean insertedAlready;
  V databaseContent;
}

The code facing your application needs to wrap the database data into the cached value.

Side note 1: Don't read the object from the cache and modify the instance directly, always make a copy. Modifying the object directly may have different unwanted effects with different JCache implementations. Also check my explanation here: javax.cache store by reference vs. store by value

Side note 2: You are building a caching ORM layer by yourself. Maybe use an existing one.