Spring batch boot Multiple datasources Multiple schemas

Question

I have a spring batch job using spring boot which has 2 datasources. Each datasource again has 2 schemas each. I need to specify default schema for both the datasources. I know of property spring.jpa.properties.hibernate.default_schema which i am using to specify default schema for one datasource. Is there a way to specify default schema for another schema?

Currently, to specify default schema for the other datasource , i am using alter session query to switch schema as required. I am trying to get rid of this alter session query from my java code. Any suggestions on it is greatly appreciated.

edit 1: Both are ORACLE databases

if the dbs are postgres then you can define it in the jdbc url like this jdbc:postgresql://localhost:5432/mydatabase?currentSchema=myschema — cool
You could create 4 datasources, one for each database / schema combination? — Joe Chiavaroli

pasquale pasquale · Accepted Answer · 2019-03-20T10:13:37

If you use multiple datasources, then you probably has a @Configuration class for each datasource. In this case you can set additional properties to the entityManager. This configuration is needed:

props.put("spring.datasource.schema", "test");

Full example

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "testEntityManagerFactory", transactionManagerRef = "testTransactionManager",
    basePackages = {"com.test.repository"})
public class TestDbConfig {

  @Bean(name = "testDataSource")
  @ConfigurationProperties(prefix = "test.datasource")
  public DataSource secondaryDataSource() {
    return DataSourceBuilder.create().build();
  }

  @Bean(name = "testEntityManagerFactory")
  public LocalContainerEntityManagerFactoryBean entityManagerFactory(EntityManagerFactoryBuilder builder, @Qualifier("testDataSource") DataSource dataSource) {
    return builder.dataSource(dataSource).packages("com.test.model").persistenceUnit("test").properties(jpaProperties()).build();
  }

  private Map<String, Object> jpaProperties() {
    Map<String, Object> props = new HashMap<>();
    props.put("hibernate.physical_naming_strategy", SpringPhysicalNamingStrategy.class.getName());
    props.put("hibernate.implicit_naming_strategy", SpringImplicitNamingStrategy.class.getName());
    props.put("spring.datasource.schema", "test");
    return props;
  }

  @Bean(name = "testTransactionManager")
  public PlatformTransactionManager transactionManager(@Qualifier("testEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
    return new JpaTransactionManager(entityManagerFactory);
  }
}

Spring batch boot Multiple datasources Multiple schemas

1 Answers