JPA using multiple database schemas

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="$") . @Entity @Table(name = "TABLE2", schema="$") .

so that schemas are replaced based on a property file residing in the environment? Cheers asked Aug 19, 2009 at 17:18 752 2 2 gold badges 13 13 silver badges 25 25 bronze badges

7 Answers 7

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

  . . . ormOne.xml . . . . . . ormTwo.xml . . .

now you can create a EntityManagerFactory for your special schema

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

answered Feb 21, 2011 at 13:56 6,263 1 1 gold badge 38 38 silver badges 41 41 bronze badges what about if there is a relation between ormOne and OrmTwo ? Commented Sep 25, 2017 at 13:09

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

answered Aug 19, 2009 at 17:49 DataNucleus DataNucleus 15.6k 3 3 gold badges 33 33 silver badges 37 37 bronze badges

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 Mapconfig; public void setConfig(Map config) < this.config = config; >public Map 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".