JPA Change entity table name base on request parameter

Question

Our database at the moment have 2 schema, one for production, one for testing, identical table but different data.

For server side, we use RoutingDatasource to manage the data. Each request have a parameter to know which database to access, like "prod" or "test". The parameter then store in ThreadLocal. Our RoutingDatasource will get this parmeter out, depend on the value and return the correct datasource. So we only need one mapping entity and one CrudRepository for same table in both schema.

But for some (stupid) reason, we were asked to merge both schema into one schema, having a prefix in front of each table, like PROD_TABLE1, TEST_TABLE1, PROD_TABLE2, TEST_TABLE2, etc.

So i want to ask, how can we keep only 1 entity, 1 repository for each table with this requirement? We don't want to clone code, create too many class inherit the existing one just to map to the second table. @SecondaryTable seem not what we looking for.

===============================

UPDATE 1:

I'm looking at PhysicalNamingStrategy, able to add a prefix to table name. But this only run once when they init the EntityManager. So is there any other way to add a prefix to table on each request?

===============================

UPDATE 2: I try with something like this:

@Table(name = "##schema##TABLE1")

Then add a Hibernate interceptor to our project. When prepare statement, we get the parameter from ThreadLocal, then replace the "##schema##" part with correct prefix. Using this way, everything work, but i don't really like this solution.

Anyone have any better way to do it?

Answer 1

Putting my own workaround here for anyone if they need:

First put a prefix "##prefix##" into every entity object:

@Table(name = "##prefix##TEST_TABLE_1")

Create a hibernate interceptor class extend EmptyInterceptor and override method onPrepareStatement. Here get the prefix from ThreadLocal ( set when receive the request beforehand ) and replace it with the "##prefix##" in table name.

public class HibernateInterceptor extends EmptyInterceptor {

    @Override
    public String onPrepareStatement(String sql) {
        String prefix = ThreadLocal.getDbPrefix();
        sql = sql.replaceAll("##schema##", prefix + "_");
        return super.onPrepareStatement(sql);
    }
}

Like this, the query will point to correct table.

And config to EntityManagerFactory: (You can set this in xml file too)

properties.put("hibernate.ejb.interceptor", "mypackage.HibernateInterceptor");

Answer 2

From the point of view of the security I'd never recommend to mix productive and test data in one schema, but anyway if you plan to do so, here is a simple way how to pretend you access the data with different OWNERs.

Lets assume you have a application schema APP with two tables TEST_TAB1 and PROD_TAB1.

-- connect as APP
create table app.prod_tab1 as
select 'prod' col1 from dual;

create table app.test_tab1 as
select 'test' col1 from dual;

You have also two users, say PROD_USR and TEST_USR, that access the data as follows:

--- connect with test_usr
select * from app.test_tab1;
COL1
----
test 

--- connect with prod_usr
select * from app.prod_tab1;
COL1
----
prod 

So, now you have the problem, that the tables have different names for different environments.

What you need, is to have for both users test_usr and prod_usr the priviledge create synonym and define following synonyms:

-- test_usr
create synonym test_usr.tab1 for app.test_tab1;
-- prod_usr
create synonym prod_usr.tab1 for app.prod_tab1;

Now the access goes with a unique name and different schema:

--- connect with test_usr
select * from test_usr.tab1;
COL1
----
test

--- connect with prod_usr
select * from prod_usr.tab1;
COL1
----
prod 

If you say, no we do not have the two different access users, we access both environments with the schema owner APP, the please re-read my first sentence.