1

Before anything, i must say this first: This table design is not my decision. We protest but to no avail, so please don't tell me, don't create a table like that.

We have a database with each table have a flag. This flag used to indicate which environment this row belong to, production or test data.

For server side, we have one variable which currently stored in ThreadLocal to indicate which environment this request belong to, same value as the flag in database.

Our requirement is that if my request belong to test environment then we must select only record belong to this environment. We would need to add a condition to every query we made to database, something like:

SELECT t FROM TABLE t WHERE t.flag = :environment

But we have to update every single query, update every object to set this flag before insert/update into database. This will require a lot of effort as our system already built long ago, not on progress. Also this will bring a lots of risk if someone forgot to add this to any new query.

So is there anyway to insert a condition to check this flag value for every query without have to manually edit the query string? Like an interceptor or something to put this condition in?

mameo
  • 639
  • 8
  • 25

1 Answers1

2

Which JPA provider?

With Hibernate, you could try using a @Filter.

Multitenancy could be another option, but probably an overkill in your scenario.

Finally, since you flagged the question with Oracle, perhaps the easiest approach would be to provide dedicated schemas (per environment) with views for every single table in your db, filtered by the flag column. Not sure if you're allowed to do that, though.

With some of the above, you would need a global entity listener to populate the flag field of your entities before they are persisted.

crizzis
  • 9,978
  • 2
  • 28
  • 47
  • We do not allow to have multiple schema, nor multiple table with same structure but different name. Why? Please don't ask about it as the reason is way too stupid. I think i will try @Filter tomorrow, with Hibernate interceptor to replace the value when they prepare the statement. This might work nicely for us. Today is too busy to try anything. I will give back result a.s.a.p. – mameo Aug 09 '18 at 12:13
  • Thank you, it work. Using Where annotation instead. But it is a pity that MappedSuperclass annotation not work with Where so i could not create a base class with this Where clause and let every class extend it. – mameo Aug 10 '18 at 04:01