JPA using multiple database schemas

Question

I'm having a bit of trouble with one particular issue using JPA/Spring:

How can I dynamically assign a schema to an entity?

We have TABLE1 that belongs to schema AD and TABLE2 that is under BD.

@Entity
@Table(name = "TABLE1", schema="S1D")
...

@Entity
@Table(name = "TABLE2", schema="S2D")
...

The schemas may not be hardcoded in an annotation attribute as it depends on the environment (Dev/Acc/Prd). (In acceptance the schemas are S1A and S2A)

How can I achieve this? Is it possible to specify some kind of placeholders like this:

@Entity
@Table(name = "TABLE1", schema="${schema1}")
...

@Entity
@Table(name = "TABLE2", schema="${schema2}")
...

so that schemas are replaced based on a property file residing in the environment?

Cheers

Answer 1

I had the same problem I solved that with a persistence.xml in which I refer to the needed orm.xml files within I declared the db shema

<persistence
xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
version="2.0" >
<persistence-unit name="schemaOne">
    . . .
    <mapping-file>ormOne.xml</mapping-file>
    . . .
</persistence-unit>

<persistence-unit name="schemaTwo">
    . . .
    <mapping-file>ormTwo.xml</mapping-file>
    . . .
 </persistence-unit>
</persistence>

now you can create a EntityManagerFactory for your special schema

EntityManagerFactory emf = Persistence.createEntityManagerFactory("schemaOne");

Answer 2

One thing you can do if you know at deployment is to have 2 orm.xml files. One for schema1 and one for schema2 and then in the persistence.xml you have 2 persistence-units defined. Putting annotations is an anti-pattern if needing to change things like schema

Answer 3

I posted this on github but also cross posting it here.

I found you can accomplish a dynamic schema configuration for multiple schemas in different environments 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".

Answer 4

Try following:

puplic class MyClass {
  public static final String S1D="S1D";
  public static final String S2D="S2D";
}

@Entity
@Table(name = "TABLE1", schema=MyClass.S1D)
...

@Entity
@Table(name = "TABLE2", schema=MyClass.S2D)
...

Answer 5

You could have two DataSource declarations (one for each schema) in your context.xml and define two persistence units using this datasources. The context.xml can then be different on the appservers of the different environments.

Answer 6

when you create a datasource, you may be able to initialize the connection to use different schema

e.g. for weblogic

Specify default schema for JDBC pool in weblogic/oracle

Answer 7

Annotation arguments have to be final and can therefore not be changed at runtime.