Spring - metadata tables in different schemas

I faced the same problem as I want to store application tables in one schema and package tables in a separate one (using postgres).

tablePrefix

didn't work for me either (I've tried different cases - none of them solve the problem).

So finally I decided to set up a separate DataSource for Spring Batch pointing to the schema batch

. This is how I did it.

In the application.properties file , I have a standard props, for example spring.datasource.*

, which is used for the application as the primary data source.

And the type props spring.batch.datasource.*

are non-standard and are a secondary data source used only in the code below.

Here is an example application.properties file :

spring.datasource.url=APP_DB_CONNECTION_URL
spring.datasource.username=APP_DB_USER
spring.datasource.password=APP_DB_PASS

spring.batch.datasource.url=BATCH_DB_CONNECTION_URL
spring.batch.datasource.username=BATCH_DB_USER
spring.batch.datasource.password=BATCH_DB_PASS

      

        
        
        
      

    

Then, in the BatchConfiguration.java , which is part of the app sources, I added a method getBatchDataSource

that reads the properties spring.batch.datasource.*

:

@Configuration
@EnableBatchProcessing
public class BatchConfiguration {

  @Bean
  @ConfigurationProperties(prefix="spring.batch.datasource")
  public DataSource getBatchDataSource(){
    return DataSourceBuilder.create().build();
  }

  ...

}

      

        
        
        
      

    

This allows Spring Batch to use a separate data source.

Now it's important to set it up spring.batch.datasource.*

correctly:

For Postgres 9.4, you can specify the schema in the connection url using the parameter currentSchema

:jdbc:postgresql://host:port/db?currentSchema=batch

For Postgres before 9.4, you can specify the schema in the connection url using the parameter searchpath

:jdbc:postgresql://host:port/db?searchpath=batch

Or you can create a separate postgres user / role for the schema batch

and configure search_path

for that user:ALTER USER BATCH_DB_USER SET search_path to 'batch';

In Oracle, each user has their own schema (as far as I know) and cannot set the schema in the connection url like for postgres (maybe I'm wrong): jdbc:oracle:thin:@//host:port/sid

So, you need to create a separate user for the schema batch

in Oracle. Another way is to use spring.batch.datasource.validation-query=ALTER SESSION SET CURRENT_SCHEMA=batch

(I haven't tried this)

Thus, Spring Bath uses a separate data source configured to use a dedicated schema batch

. Batch requests still look like select ...from batch_...

, however it works against the schema batch

. And the app uses a normal data source pointing to the app-allocated schema app

.

This solution has been tested with Spring Boot v1.2.5.RELEASE and Postgres 9.4.1

Hope it helps.