SpringBoot Allow for @Table annotations to use configuration parameters

For example:

@Table(schema="${myApp.schemaName}")

We have multiple applications which use the same database schema in production, but need to use different schemas in development. The @Table annotation provided by hibernate-jpa accepts a schema, or can use a default schema from application.properties, but there isn't an easy way to configure these schemas dynamically. Schemas are fixed at compile time.

Comment From: philwebb

It might be possible to hook into the way hibernate reads these values using a custom org.hibernate.annotations.common.reflection.AnnotationReader. It doesn't look like a particularly trivial task to wire this up.

These links might also help

http://stackoverflow.com/questions/2737420/how-to-set-up-default-schema-name-in-jpa-configuration

https://forum.hibernate.org/viewtopic.php?p=2265044#2265044

Comment From: berlin-ab

We're using multiple schemas in our application, so using a default schema name doesn't quite help in our case.

Comment From: berlin-ab

I think this will be a recurring issue for enterprise clients, especially those using Oracle. I'd really like to get a fix in for this so that we can have isolated tests between projects while having only one installation of Oracle. I'd prioritize this as higher priority than most other issues.

Comment From: philwebb

I've assigned this for 1.1.0.M2, I'll try and take a look but we have a hard deadline for the 1.1 release so I might need to push it back again.

Comment From: dsyer

What's wrong with spring.jpa.properties.hibernate.default_schema=${myApp.schemaName}? Surely you don't need more than one schema in a single app very often?

Comment From: berlin-ab

@dsyer I definitely have a use-case of 2+ projects that have needed this feature.

Comment From: dsyer

So why can't they use the Hibernate properties? Do they use multiple schemas?

Comment From: philwebb

I may have a solution for this, but finding a way to hook into Hibernate turned out to be quite convoluted.

Prototype code is here: https://github.com/philwebb/spring-boot/tree/gh-829

This adds a hook to process the Hibernate Configuration then replaces the annotation reader with a new version that can resolve placeholders. So far the only annotation attribute that I have registered is Table.schema, but others should also work if we want.

I'm in two minds if adding this is good idea or not, it seems like it could be quite brittle.

Comment From: dsyer

My $0.02: Hibernate has had the @Table annotation for nearly ten years and no-one needed this feature yet. If it might be brittle we should be cautious and I haven't seen the code that needs it.

Comment From: philwebb

I've found stackoverflow questions asking about this - just because it isn't supported by Hibernate doesn't mean it isn't wanted.

I'm not so familiar with the "Oracle way" when it comes to databases so I don't really know when or why you would want to use two schemas in a single application. Perhaps some more background on the underlying reasons would us help decide if we should put this in.

Just to be clear, the fragility of the code is because there is no obvious way to hook in a custom AnnotationReader. It will certainly work today, but if Hibernate change any internals it may break on an upgrade.

@berlin-ab If we don't put this into Boot, you can probably still crib the code and put it in your own library.

Comment From: berlin-ab

@philwebb We were working with a client that had data stored across multiple Oracle schemas for different domains of their business. Some applications within their organization reach across schema boundaries.

We wanted the ability to configure schema names for our separate applications so that there wouldn't be any collision between them at runtime/migration time in development.

Comment From: dsyer

From what I remember (it's been a while) Oracle allows you to join transactions across multiple schemas, but only if they are hidden behind some sort of view layer ("materialized views" maybe?). No other RDBMS vendor lets you do anything that complicated. If the view layer is there then you don't need to address the server with multiple schema names (they are logically the same). If it isn't then you don't get any transaction support so you ought to be using multiple EntityManagers anyway (and, yes, it will get messy and complicated). Maybe @trisberg or @olivergierke has some insight?

Comment From: lacivert

@berlin-ab This may be some old issue and may not be suitable for you but have you tried AbstractRoutingDataSource ? I had a similar problem; many schemas with same tables. So I needed this kind of solution and I see that it works. Check it if you still need it

Comment From: trisberg

@dsyer AFAIK as long as the schemas are within the same Oracle database they should all be part of the same local transaction. It gets tricky if you access data located in different databases. Then you would need a database link to connect the two databases and Oracle would create a distributed transaction behind the scenes.

Comment From: philwebb

This issue is quite old now, so I'm guessing some kind of solution was found. I don't think we want to add changes to Hibernates annotation processing directly in Boot, it seems like quite an edge case and it's likely to cause maintenance headaches.

Comment From: madorb

Just FYI, i'm still looking to find some solution for this. Oracle really makes this unnecessarily difficult.

In say, mysql i can specify the database i want to connect to in the jdbc url. In oracle the equivalent to that database is a schema, which i cannot define in the connection url. My applicaiton needs to connect to two logical databases app1, app2. I'm not doing any joinging or anything between them, just two different sources of data.

Since the schemas are different per environment e.g. schemaADev, schemaATest, schemaBDev, schemaBTest... that leaves me with no clear option to enable the functionality i need.

Comment From: mknutty

Here is a real world usecase. I am running into this issue. My customer has a single database install, with multiple databases for all environments in the same database. I think it is a licensing issue. Why not MySQL or Postgres? .NET.

So, i have 4 datasources per enviroment. With Hikaricp that is 40 connections per enviroment (aka accept/prod). They freaked out. SQL server allows you do connect to one database but refer to another by fully qualified names - [dbname].['user'].[table].

Anyway, my solution is to reduce number of connections by setting the max to a low number :( I am going to try to get them to set up another server and seperate prod from accept.

Comment From: madorb

@wilkinsona This was closed due to age, but IMO there is still no simple solution for it (presently, i have to create two separate datasources and set the default schema property for each when building the LocalContainerEntitymanagerFactoryBean).

As it's closed but only referend as due to age... is it reasonable for me to open a new ticket for this issue? I don't want to waste the team's time if it's actually a "Won't Fix".

Thanks!

Comment From: madorb

Oops meant @philwebb as he is the one who closed it.

Comment From: snicoll

@madorb the actual reason for closing it is

I don't think we want to add changes to Hibernates annotation processing directly in Boot

That reason hasn't changed.

Comment From: mknutty

yeah, i know it was closed and the reasoning for not doing it in boot and i agree. But, I was just googling for a solution, came across this and figured i would provide a real world reason just in case it was ever revisited.

On Wed, Jan 10, 2018 at 11:23 AM, Stéphane Nicoll notifications@github.com wrote:

@madorb https://github.com/madorb the actual reason for closing it is

I don't think we want to add changes to Hibernates annotation processing directly in Boot

That reason hasn't changed.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/spring-projects/spring-boot/issues/829#issuecomment-356654019, or mute the thread https://github.com/notifications/unsubscribe-auth/AF8zLSyuD1fHux6IadnVeTtQVf0OHP8kks5tJOPngaJpZM4B5h-h .

Comment From: SyedHamzah

Not sure if i am too late to this discussion. But did anybody find a solution? My scenario is a multi tenant system where user/tenant data has to be kept in one base schema, therefore i need to configure these two tables with base schema only. Unfortunately i cannot keep base schema name harcoded, but rather fetch from application properties.

Comment From: pbuhrmann

No solution for this?

Comment From: PhanNN

@penrique https://dzone.com/articles/hibernate-dynamic-table-routin

It might helps?

Comment From: rajlokeshkumar

has any one got any solution on this ,I am looking solution for this badly ,can any one let me know how you handled the scenario. I have single database connection I have tables from multiple schema's Schema names are different for different environment When the environment is changed i do not want to go and touch java code.

Any way of achieving it

Comment From: isneezy

@rajlokeshkumar i dont know if this solves you problem but I've solved this problem by using hibernate interceptor.

public class PrimaveraSchemaInterceptor extends EmptyInterceptor {

    public static String PRIMARY_DB;
    public static String SECONDARY_DB;

    @Override
    public String onPrepareStatement(String sql) {
        sql = sql.replace("PRIMARY_DB", PRIMARY_DB)
                .replace("SECONDARY_DB", SECONDARY_DB);
        return sql;
    }
}

https://stackoverflow.com/a/25293683/4815174

Comment From: ribakored

This should work, while creating an entity manager for your custom data source , set the hibernate properties:

  @Value("${youSchema}")
   private String schema;  

   @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory( DataSource dataSource){
        LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
        factory.setPackagesToScan("you.dao.package");
        factory.setDataSource(coreDataSource);
        Properties hibernateProperties = new Properties();
        hibernateProperties.put("hibernate.default_schema", schema);
        factory.setJpaProperties(hibernateProperties);
        factory.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
        return factory;
    }

Not as elegant as putting it in @Table annotation, but works when you have to work with multiple schemas

Comment From: zistheman12

I found you can accomplish a dynamic schema configuration for multiple schemas by overriding the physical naming strategy.

Let's say you have two entities like this which are configured for two different schemas -

@Entity
@Table(name="TABLE1", schema="schema1")
public class FooEntity implements Serializable {
    ...
}

@Entity
@Table(name="TABLE2", schema="schema2")
public class BarEntity implements Serializable {
    ...
}

First create a configuration in your application.yml file:

multischema:
  config:
    schema1: FIRSTSCHEMA
    schema2: SECONDSCHEMA

Bind it to a ConfigurationProperties bean

@Component
@ConfigurationProperties("multischema")
public class MultiSchemaConfigurationProperties {

    private Map<String,String> config;

    public void setConfig(Map<String,String> config) {
        this.config = config;
    }

    public Map<String,String> getConfig() {
        return config;
    }

}

Create a custom physical naming strategy which injects MultiSchemaConfigurationProperties and extends the Spring Boot default for your version. In this case I'm using Spring Boot 2.6.4 which uses CamelCaseToUnderscoresNamingStrategy.

@Component
public class MultiSchemaPhysicalNamingStrategy extends CamelCaseToUnderscoresNamingStrategy {

    private final MultiSchemaConfigurationProperties schemaConfiguration;

    public MultiSchemaPhysicalNamingStrategy(MultiSchemaConfigurationProperties schemaConfiguration) {
        this.schemaConfiguration = schemaConfiguration;
    }

    @Override
    public Identifier toPhysicalSchemaName(Identifier name, JdbcEnvironment jdbcEnvironment) {
        if(name != null) {
            Identifier identifier = super.getIdentifier(schemaConfiguration.getConfig()
                    .get(name.getText()), name.isQuoted(), jdbcEnvironment);
            return super.toPhysicalSchemaName(identifier, jdbcEnvironment);
        }
        return name;
    }

}

When the application starts up Hibernate will invoke the custom physical naming strategy to apply the physical naming rule for your schema using the provided configuration in application.yml. https://docs.spring.io/spring-boot/docs/current/reference/html/howto.html#howto.data-access.configure-hibernate-naming-strategy

"schema1" in FooEntity will be replaced by the value "firstschema" and "schema2" in BarEntity will be replaced by the value "secondschema".