2

Use-case: My spring application reads data from a DB, but accesses the cache for the same which is pre-loaded. Although rarely, these tables might undergo changes. On such an event, the cached needs to be invalidated and pre-loaded. Also not that the application only reads the data.

After a bit of research, I have found two options..

Option 1: Database trigger to call Spring task.

I would need to configure triggers on insert/update of each of the tables which should call the spring @Scheduled task. This would take care of refreshing the cache. But then how do I call the Spring's @Scheduled task on a databse trigger?

Option 2: Using Oracle's DatabaseChangeNotification

I would have to register a listener with the Oracle DB. This would listen to all changes and my application would be notified whenever there is a change. But then how do I do this using Spring?

Which is the better option? Is there a better option available? Also any help in proceeding further on the options?

Note: I am using Spring + JPA + Oracle 10g

Chillax
  • 4,418
  • 21
  • 56
  • 91
  • what did you eventually go with? – sawe Mar 01 '17 at 12:34
  • @sawe I couldn't come up with a good solution. I ended up doing a refresh every day at midnight. The database size being small - I could afford to do it – Chillax Apr 26 '17 at 13:49

1 Answers1

0

I recognize that this question is six years old, but since I'm going through the same issue, thought I might suggest an option:

Many databases (MySQL, in my use case) include the ability to create and execute UDFs (user defined functions) for a variety of purposes.

https://dev.mysql.com/doc/extending-mysql/8.0/en/adding-functions.html

Could be utilized in this way: Calling an url from a trigger in mysql

If you were to add a UDF that called a Spring endpoint that was exposed in your application, you could use the call to that endpoint to trigger your refresh.

Process flow, top to bottom: DB UDF triggers on CRUD operation to specific table, calls a Spring endpoint, which triggers the refresh.

ehlJay
  • 71
  • 4