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());

Fulfill

Are you using @EnableBatchProcessing? – Shantel

@Michael, Yes I am using it – Fulfill

any one have an idea why's it not saving in different schema – Fulfill

Then are you using a custom BatchConfigurer to identify which DataSource to use for the batch schema? – Shantel

@Michael, No. All I do is what is given in the above code – Fulfill

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>

Eosinophil

Could you please elaborate your answer ? Please complete code snippet – Crucify

Its giving me error Caused by: java.sql.SQLSyntaxErrorException: Table 'batchmetadata.batch_job_instance' doesn't exist – Crucify

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

Shantel

Ok thanks. So now I extended DefaultBatchConfigurer and provided the datasource. Now it says TABLE DOES NOT EXIST. Does that mean we need to create tabled by ourself. I think it shouldn't be the case as it created tables by its own on the other datasource before using DefaultBatchConfigurer . SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = ? order by JOB_INSTANCE_ID desc]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist – Fulfill

I don't see you configuring the script to be used in your properties so if you don't run it yourself manually, you'd need to use the initializer functionality to have Spring run the schema script for you. – Shantel

how do we initialize. I do have public void setDataSource(@Qualifier("batchDataSource") DataSource batchDataSource) { super.setDataSource(batchDataSource); } which is overridden after extending DefaultBatchConfigurer – Fulfill

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);
}

Snobbery

Answer 4

In case that

spring.batch.jdbc.table-prefix=something.prefix_

still wont fix it

Extend DefaultBatchConfiguration and register it somehow (e.g. using @Configuration). Example below (in Kotlin)

import org.springframework.batch.core.configuration.support.DefaultBatchConfiguration
import org.springframework.beans.factory.annotation.Value
import org.springframework.context.annotation.Configuration

@Configuration
class BatchConfiguration: DefaultBatchConfiguration(){

    @Value("\${spring.batch.jdbc.table-prefix}")
    lateinit var prefixFromAppConfig: String

    override fun getTablePrefix() = prefixFromAppConfig
}

Be shure to set spring.batch.jdbc.table-prefix in your application.yaml/properties (or use any other way to inject the schema)

OOP & Override FTW :-)

Weaks