Spring-batch metadata tables in different schema

Question

I have a datasource that connects to an Oracle database in my application. Is it possible to access to another schema that includes the Spring-batch metadata tables through this datasource? The user of this datasource has all rights to access to the other schema.

I have already tried "tablePrefix" attribute of the JobRepository such as "Schema.batch_". But it does not work. Briefly, I search for the way to tell the Spring-batch to access to the metadata tables like "select ....from Schema.batch_.." not "select ...from batch_...".

In fact, I am not getting an error. But I see that Spring Batch tries to query with tables names without any schema prefix, even I define the tablePrefix property as "schema.batch_". — Softengilker, Jun 24 '15 at 19:00

score 17 · Answer 1 · edited Aug 19 '20 at 01:19

I faced the same issue since I want to keep application tables in one schema and batch tables in a separate one (using postgres).

tablePrefix didn't work for me either (I tried different cases - none of it solves the issue).

So finally I deciced to configure a separate DataSource for Spring Batch pointing to a batch schema. Here is how I did it.

In application.properties file I have standard props like spring.datasource.* which is used for the application as a primary datasource.

And props like spring.batch.datasource.* is non-standard and is a secondary datasource used only in the code provided below.

Here is example of 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 BatchConfiguration.java which is a part of app sources I added getBatchDataSource method which reads spring.batch.datasource.* properties:

@Configuration
@EnableBatchProcessing
public class BatchConfiguration {

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

  ...

}

This makes Spring Batch to use a separate data source.

Now important thing is to setup spring.batch.datasource.* correctly:

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

Or you can create a separate postgres user/role for batch schema and setup 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 no way to set schema in the connection URL like for postgres (I may be wrong): jdbc:oracle:thin:@//host:port/sid

So you need to create a separate user for batch schema in Oracle. Another way is to use spring.batch.datasource.validation-query=ALTER SESSION SET CURRENT_SCHEMA=batch (I didn't try this)

So in this way Spring Batch uses a separate datasource configured to use dedicated batch schema. Batch queries still look like select ...from batch_... however it runs against batch schema. And application is using regular datasource pointing to an application dedicated schema app.

This solution was tested using Spring Boot v1.2.5.RELEASE and Postgres 9.4.1

Hope this helps.

Thank you alextunick. I have already solved the problem with the way you described. I used separate data sources. But It would be better if I did not have to define another data source and use the same datasource with granting the other schema. — Softengilker, Nov 10 '15 at 07:50
Any solution for above problem other than two datasources ? I have same requirement I want to have metadatatables in different schema and also I actually dont want spring batch to create tables rather I have to create that upfront. I use latest sprint boot(2.x) . — user3444718, Oct 28 '18 at 01:59
I found a solution https://better-coding.com/spring-batch-metadata-tables-in-different-database-schema/ — Deepak, Nov 22 '18 at 12:08
@user3444718 - I also the need the solution around your requirement. Were u able to solve this ? — PAA, Mar 27 '19 at 15:58
setting spring.batch.tableprefix=.batch_ should solve problem — user3444718, Mar 28 '19 at 02:09
@alex - Could you please guide me here: https://stackoverflow.com/questions/61086749/spring-batch-unable-to-create-metadata-tables-on-postgres-and-load-actual-data ? — PAA, Apr 07 '20 at 18:41
`datasource.jdbc-url` worked for me instead of `datasource.url`. — user3399000, Aug 18 '20 at 07:53

score 1 · Answer 2 · answered May 28 '20 at 05:39

Because spring.batch.table-prefix=<yourschema>.batch_ did not work for me and I could not immediately configure the second datasource, I took a look at the actual table generation done by Spring Batch.

The sql-Script which generates the tables is static (at least in the release 4.2.2 and for Postgresql). Therefore it is no wonder that spring.batch.table-prefix does not work. At least in my case as the tables do not yet exist in the database.

To fix it I copied schema-postgresql.sql to my resource folder and modified it to my needs (creating the schema and referencing it explicitly).

CREATE SCHEMA SPRING_BATCH;

CREATE TABLE SPRING_BATCH.BATCH_JOB_INSTANCE  (
    JOB_INSTANCE_ID BIGINT  NOT NULL PRIMARY KEY ,
    -- and so on

In my application.properties I added:

spring.batch.initialize-schema=always
spring.batch.table-prefix=SPRING_BATCH.BATCH_
spring.batch.schema=classpath:db/create_spring_batch_tables.sql

Spring-batch metadata tables in different schema

2 Answers2

Linked