CODEWITHSUNDEEP
oracle-databasejpa
mameo
mameo

Reputation: 659

JPA add a condition to every single query automatically

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?

Upvotes: 2

Views: 1723

Answers (1)

crizzis
crizzis

Reputation: 10716

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.

Upvotes: 2

Related Questions