Dynamic schema in Hibernate @Table Annotation

Question

Imagine you have four MySQL database schemas across two environments:

• foo (the prod db),
• bar (the in-progress restructuring of the foo db),
• foo_beta (the test db),
• and bar_beta (the test db for new structures).

Further, imagine you have a Spring Boot app with Hibernate annotations on the entities, like so:

@Table(name="customer", schema="bar")
public class Customer { ... }

@Table(name="customer", schema="foo")
public class LegacyCustomer { ... }

When developing locally it's no problem. You mimic the production database table names in your local environment. But then you try to demo functionality before it goes live and want to upload it to the server. You start another instance of the app on another port and realize this copy needs to point to "foo_beta" and "bar_beta", not "foo" and "bar"! What to do!

Were you using only one schema in your app, you could've left off the schema all-together and specified hibernate.default_schema, but... you're using two. So that's out.

Spring EL--e.g. @Table(name="customer", schema="${myApp.schemaName}") isn't an option--(with even some snooty "no-one needs this" comments), so if dynamically defining schemas is absurd, what does one do? Other than, you know, not getting into this ridiculous scenario in the first place.

Answer 1

I have fixed such kind of problem by adding support for my own schema annotation to Hibernate. It is not very hard to implement by extending LocalSessionFactoryBean (or AnnotationSessionFactoryBean for Hibernate 3). The annotation looks like this

@Target(TYPE)
@Retention(RUNTIME)
public @interface Schema {

    String alias() default "";

    String group() default "";

}

Example of using

@Entity
@Table
@Schema(alias = "em", group = "ref")
public class SomePersistent {

}

And a schema name for every combination of alias and group is specified in a spring configuration.

Answer 2

you can try with interceptors

public class CustomInterceptor extends EmptyInterceptor {
@Override
    public String onPrepareStatement(String sql) {
          String prepedStatement = super.onPrepareStatement(sql);
          prepedStatement = prepedStatement.replaceAll("schema", "Schema1");
          return prepedStatement;
    }
}

add this interceptor in session object as

Session session = sessionFactory.withOptions().interceptor(new MyInterceptor()).openSession();  

so what happens is when ever onPrepareStatement is executed this block of code will be called and schema name will be changed from schema to schema1.