Spring batch tables in a different schema

Question

I want to use a different schema to save Spring Batch tables. I can see that my new datasource in set in the JobRepositoryFactoryBean. But still the tables are been created in the other shcema where I have business tables. I read soemwhere that I can use dataSource.setValidationQuery to alter the schema, but still doesn't work. I can solve this. Below is the JobRepositoryFactoryBean and Datasource prop.

 @Bean
 @Qualifier("batchDataSource")
 protected JobRepository createJobRepository() throws Exception {
    JobRepositoryFactoryBean factory = createJobRepositoryFactoryBean();    
    factory.setDataSource(getDataSource());
    if (getDbType() != null) {
      factory.setDatabaseType(getDbType());
    }
    factory.setTransactionManager(getTransactionManager());
    factory.setIsolationLevelForCreate(getIsolationLevel());
    factory.setMaxVarCharLength(maxVarCharLength);
    factory.setTablePrefix(getTablePrefix());
    factory.setValidateTransactionState(validateTransactionState);
    factory.afterPropertiesSet();
    return factory.getObject();
  }

 spring.datasource.url=url
 spring.datasource.username=username
 spring.datasource.password=pwd
spring.datasource.driver-class-name:oracle.jdbc.driver.OracleDriver
spring.datasource.validation-query=ALTER SESSION SET 
 CURRENT_SCHEMA=schemaname

#batch setting
spring.batch.datasource.url=burl
spring.batch.datasource.username=busername
spring.batch.datasource.password=bpwd
spring.batch.datasource.driver-class-name:oracle.jdbc.driver.OracleDriver
spring.batch.datasource.validation-query=ALTER SESSION SET 
CURRENT_SCHEMA=batchschema

 org.apache.tomcat.jdbc.pool.DataSource dataSource = new org.apache.tomcat.jdbc.pool.DataSource();
      dataSource.setName("batchDataSourceName");
      dataSource.setDriverClassName(batchDataSourceProperties.getDriverClassName());
      dataSource.setUrl(batchDataSourceProperties.getUrl());
      dataSource.setUsername(batchDataSourceProperties.getUsername());
      dataSource.setPassword(batchDataSourceProperties.getPassword());
     // dataSource.setValidationQuery(batchDataSourceProperties.getValidationQuery());

Answer 1

Below property in application.properties is working for me.This will create meta schema tables under new_schema in your DB.

spring.batch.tablePrefix=new_schema.BATCH_

Below is the version of springBoot I am using.

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

Answer 2

When using Spring Batch's @EnableBatchProcessing, the DataSource used by the Spring Batch tables is the one provided by the BatchConfigurer. If you are using more than one DataSource in your application, you must create your own BatchConfigurer (either by extending DefaultBatchConfigurer or implementing the interface) so that Spring Batch knows which to use. You can read more about this customization in the reference documentation here: https://docs.spring.io/spring-batch/4.0.x/reference/html/job.html#configuringJobRepository

Answer 3

Duplicate your existing data source properties and override BatchConfigurer to return this new data source. Then, in the new data source's properties, change either

1. The user connecting to the database to one with a default schema defined as the desired schema for the Spring Batch tables

2. The connection url to include the desired schema for the Spring Batch tables.

The option you choose will depend on your database type as follows:

For SQL Server you can define the default schema for the user you are using to connect to the database (I did this one).

CREATE SCHEMA batchschema;

USE database;
CREATE USER batchuser;
GRANT CREATE TABLE TO batchuser;    
ALTER USER batchuser WITH DEFAULT_SCHEMA = batchschema;
ALTER AUTHORIZATION ON SCHEMA::batchschema TO batchuser;

For Postgres 9.4 you can specify schema in the connection URL using currentSchema parameter: jdbc:postgresql://host:port/db?currentSchema=batch

For Postgres before 9.4 you can specify schema in the connection URL using searchpath parameter: jdbc:postgresql://host:port/db?searchpath=batch

For Oracle it looks like the schema would need to be set on the session. I'm not exactly sure how this one would work...

ALTER SESSION SET CURRENT_SCHEMA batchschema

Qualify each DataSource, set one you wish to use for the Batch tables as @Primary, and set your datasource for the DefaultBatchConfigurer as follows:

@Bean(name="otherDataSource")
public DataSource otherDataSource() {
    //...
}

@Primary
@Bean(name="batchDataSource")
public DataSource batchDataSource() {
    //...
}

@Bean
BatchConfigurer configurer(@Qualifier("batchDataSource") DataSource dataSource){
    return new DefaultBatchConfigurer(dataSource);
}