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_...".
The meta table's scripts are stored in the spring-batch. jar , you need to create it manually. Run your Spring batch jobs again, those meta tables will be created automatically.
Overview. The Spring Batch Metadata tables closely match the Domain objects that represent them in Java. For example, JobInstance , JobExecution , JobParameters , and StepExecution map to BATCH_JOB_INSTANCE , BATCH_JOB_EXECUTION , BATCH_JOB_EXECUTION_PARAMS , and BATCH_STEP_EXECUTION , respectively.
Basic structure of Spring BatchA single execution unit that summarises a series of processes for batch application in Spring Batch. Reusing a process, parallelization, conditional branching can be performed by dividing 1 job process in multiple steps.
Spring Batch by default uses a database to store metadata on the configured batch jobs. In this example, we will run Spring Batch without a database. Instead, an in-memory Map based repository is used.
The Spring Batch Metadata tables closely match the Domain objects that represent them in Java. For example, JobInstance, JobExecution, JobParameters, and StepExecution map to BATCH_JOB_INSTANCE, BATCH_JOB_EXECUTION, BATCH_JOB_EXECUTION_PARAMS, and BATCH_STEP_EXECUTION, respectively.
Spring Batch Metadata Table on Different Schema of the Same DB This is an in-depth article related to the Spring Batch. Spring Batch Framework is an open-source library for batch processing. Batch Processing is the execution of a series of jobs.
Show activity on this post. When using Spring Batch's @EnableBatchProcessing, the DataSource used by the Spring Batch tables is the one provided by the BatchConfigurer.
Spring Batch Meta-Data ERD The Spring Batch Core JAR file contains example scripts to create the relational tables for a number of database platforms (which are, in turn, auto-detected by the job repository factory bean or namespace equivalent). These scripts can be used as is or modified with additional indexes and constraints as desired.
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 setupsearch_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 usespring.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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With