Configuring Spring Batch with Separate Oracle Schemas

When working with Spring Batch in a complex application, you may need to separate the batch metadata tables from your application's main working tables by placing them in different Oracle schemas. This separation can enhance security, maintainability, and scalability. Additionally, externalizing configuration properties like connection strings into properties files allows for greater flexibility and easier environmental changes. In this article, we'll explore how to configure Spring Batch to use separate Oracle schemas for its metadata tables and the application's main working tables, utilizing properties files for all configuration settings. Table of Contents Prerequisites Externalizing Configuration Properties Creating Properties Files Defining Multiple DataSources Batch DataSource Configuration Application DataSource Configuration Configuring Spring Batch to Use the Batch DataSource Using the Application DataSource in Business Logic Creating Spring Batch Metadata Tables Running the Application Conclusion References Prerequisites Before we begin, ensure you have the following: Java Development Kit (JDK) 8 or higher Spring Boot and Spring Batch dependencies added to your project. Oracle Database access with two schemas: One schema for Spring Batch metadata tables (batch_schema_user). Another schema for the application's main working tables (app_schema_user). Basic understanding of Spring Boot, Spring Batch, and Oracle databases. Externalizing Configuration Properties Externalizing configurations helps keep sensitive data like database credentials out of the codebase and makes it easier to manage different environments (development, testing, production). Creating Properties Files Create a file named application.properties in src/main/resources/ if it doesn't already exist. application.properties # Import additional property files spring.config.import=classpath:batch-datasource.properties,classpath:app-datasource.properties # Other common properties can go here Next, create two separate properties files for the batch and application data sources. batch-datasource.properties # Batch DataSource Properties batch.datasource.url=jdbc:oracle:thin:@//your_batch_db_url:1521/your_batch_db_service batch.datasource.username=batch_schema_user batch.datasource.password=batch_schema_password batch.datasource.driver-class-name=oracle.jdbc.OracleDriver app-datasource.properties # Application DataSource Properties app.datasource.url=jdbc:oracle:thin:@//your_app_db_url:1521/your_app_db_service app.datasource.username=app_schema_user app.datasource.password=app_schema_password app.datasource.driver-class-name=oracle.jdbc.OracleDriver Important: Replace your_batch_db_url, your_app_db_url, and other placeholders with actual connection details specific to your Oracle database schemas. Defining Multiple DataSources We need to define two separate DataSource beans and ensure Spring knows about both. Batch DataSource Configuration Create a configuration class for the batch data source. BatchDataSourceConfig.java package com.example.config; import javax.sql.DataSource; import org.springframework.beans.factory.annotation.Value; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.jdbc.datasource.DriverManagerDataSource; @Configuration public class BatchDataSourceConfig { @Value("${batch.datasource.url}") private String url; @Value("${batch.datasource.username}") private String username; @Value("${batch.datasource.password}") private String password; @Value("${batch.datasource.driver-class-name}") private String driverClassName; @Bean(name = "batchDataSource") public DataSource batchDataSource() { DriverManagerDataSource dataSource = new DriverManagerDataSource(); dataSource.setDriverClassName(driverClassName); dataSource.setUrl(url); dataSource.setUsername(username); dataSource.setPassword(password); return dataSource; } } Application DataSource Configuration Similarly, create a configuration class for the application data source. AppDataSourceConfig.java package com.example.config; import javax.sql.DataSource; import org.springframework.beans.factory.annotation.Value; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.jdbc.datasource.DriverManagerDataSource; @Configuration public class AppDataSourceConfig { @Value("${app.datasource.url}") private String url; @Value("${app.datasource.username}") private String username; @Value("${app.datasource.password}") private String password; @Value("${app.datasource.driver-class-name}") private String driverClassName; @

May 5, 2025 - 07:43

Configuring Spring Batch with Separate Oracle Schemas

When working with Spring Batch in a complex application, you may need to separate the batch metadata tables from your application's main working tables by placing them in different Oracle schemas. This separation can enhance security, maintainability, and scalability. Additionally, externalizing configuration properties like connection strings into properties files allows for greater flexibility and easier environmental changes.

In this article, we'll explore how to configure Spring Batch to use separate Oracle schemas for its metadata tables and the application's main working tables, utilizing properties files for all configuration settings.

Prerequisites
Externalizing Configuration Properties
- Creating Properties Files
Defining Multiple DataSources
- Batch DataSource Configuration
- Application DataSource Configuration
Configuring Spring Batch to Use the Batch DataSource
Using the Application DataSource in Business Logic
Creating Spring Batch Metadata Tables
Running the Application
Conclusion
References

Prerequisites

Before we begin, ensure you have the following:

Java Development Kit (JDK) 8 or higher
Spring Boot and Spring Batch dependencies added to your project.
Oracle Database access with two schemas:
- One schema for Spring Batch metadata tables (batch_schema_user).
- Another schema for the application's main working tables (app_schema_user).
Basic understanding of Spring Boot, Spring Batch, and Oracle databases.

Externalizing Configuration Properties

Externalizing configurations helps keep sensitive data like database credentials out of the codebase and makes it easier to manage different environments (development, testing, production).

Creating Properties Files

Create a file named application.properties in src/main/resources/ if it doesn't already exist.

application.properties

# Import additional property files
spring.config.import=classpath:batch-datasource.properties,classpath:app-datasource.properties

# Other common properties can go here

Next, create two separate properties files for the batch and application data sources.

batch-datasource.properties

# Batch DataSource Properties
batch.datasource.url=jdbc:oracle:thin:@//your_batch_db_url:1521/your_batch_db_service
batch.datasource.username=batch_schema_user
batch.datasource.password=batch_schema_password
batch.datasource.driver-class-name=oracle.jdbc.OracleDriver

app-datasource.properties

# Application DataSource Properties
app.datasource.url=jdbc:oracle:thin:@//your_app_db_url:1521/your_app_db_service
app.datasource.username=app_schema_user
app.datasource.password=app_schema_password
app.datasource.driver-class-name=oracle.jdbc.OracleDriver

Important: Replace your_batch_db_url, your_app_db_url, and other placeholders with actual connection details specific to your Oracle database schemas.

Defining Multiple DataSources

We need to define two separate DataSource beans and ensure Spring knows about both.

Batch DataSource Configuration

Create a configuration class for the batch data source.

BatchDataSourceConfig.java

package com.example.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import org.springframework.jdbc.datasource.DriverManagerDataSource;

@Configuration
public class BatchDataSourceConfig {

    @Value("${batch.datasource.url}")
    private String url;

    @Value("${batch.datasource.username}")
    private String username;

    @Value("${batch.datasource.password}")
    private String password;

    @Value("${batch.datasource.driver-class-name}")
    private String driverClassName;

    @Bean(name = "batchDataSource")
    public DataSource batchDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(driverClassName);
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        return dataSource;
    }
}

Application DataSource Configuration

Similarly, create a configuration class for the application data source.

AppDataSourceConfig.java

package com.example.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import org.springframework.jdbc.datasource.DriverManagerDataSource;

@Configuration
public class AppDataSourceConfig {

    @Value("${app.datasource.url}")
    private String url;

    @Value("${app.datasource.username}")
    private String username;

    @Value("${app.datasource.password}")
    private String password;

    @Value("${app.datasource.driver-class-name}")
    private String driverClassName;

    @Bean(name = "applicationDataSource")
    public DataSource applicationDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(driverClassName);
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        return dataSource;
    }
}

Note: By annotating the beans with @Bean(name = "..."), we differentiate between the two DataSource beans.

Configuring Spring Batch to Use the Batch DataSource

Spring Batch needs to be configured to use the batchDataSource for its metadata operations.

BatchConfig.java

package com.example.config;

import javax.sql.DataSource;

import org.springframework.batch.core.configuration.annotation.DefaultBatchConfigurer;
import org.springframework.batch.core.configuration.annotation.EnableBatchProcessing;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Configuration;

@Configuration
@EnableBatchProcessing
public class BatchConfig extends DefaultBatchConfigurer {

    private final DataSource batchDataSource;

    public BatchConfig(@Qualifier("batchDataSource") DataSource batchDataSource) {
        this.batchDataSource = batchDataSource;
    }

    @Override
    public void setDataSource(DataSource dataSource) {
        // Override to set the batch-specific DataSource
        super.setDataSource(batchDataSource);
    }
}

By extending DefaultBatchConfigurer and overriding the setDataSource method, we specify which DataSource Spring Batch should use for its operations.

Using the Application DataSource in Business Logic

Your application's main business logic should use the applicationDataSource. This can be set up in various ways depending on your application's architecture. If you're using Spring Data JPA, you need to configure an EntityManagerFactory and a TransactionManager for the application data source.

AppDatabaseConfig.java

package com.example.config;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;

import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
    basePackages = "com.example.repositories",
    entityManagerFactoryRef = "appEntityManagerFactory",
    transactionManagerRef = "appTransactionManager"
)
public class AppDatabaseConfig {

    @Bean(name = "appEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean appEntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("applicationDataSource") DataSource dataSource) {

        return builder
                .dataSource(dataSource)
                .packages("com.example.entities")
                .persistenceUnit("appPU")
                .build();
    }

    @Bean(name = "appTransactionManager")
    public PlatformTransactionManager appTransactionManager(
            @Qualifier("appEntityManagerFactory") EntityManagerFactory entityManagerFactory) {

        return new JpaTransactionManager(entityManagerFactory);
    }
}

In the configuration above:

@EnableJpaRepositories:
- basePackages: Specifies where your repository interfaces are located.
- entityManagerFactoryRef: References the entity manager factory bean defined for the application.
- transactionManagerRef: References the transaction manager bean.
appEntityManagerFactory:
- Configures the EntityManagerFactory to use applicationDataSource and scans for entity classes in the specified package.

Creating Spring Batch Metadata Tables

Spring Batch requires specific tables to store job and step metadata. These tables must be created in the batch_schema_user schema.

Steps to Create the Tables:

Locate the SQL Script:

The DDL scripts for creating the necessary tables are provided by Spring Batch in the spring-batch-core dependency.

The Oracle script is typically found at:

 org/springframework/batch/core/schema-oracle.sql

Execute the Script:

Run the script against the batch_schema_user schema using your preferred database tool (e.g., SQL*Plus, SQL Developer).

   -- Connect to the batch schema
   CONNECT batch_schema_user/batch_schema_password@your_batch_db;

   -- Run the schema script
   @path/to/schema-oracle.sql

Ensure all tables are created successfully.

Running the Application

Testing DataSource Connections

Before running the application, verify that both data sources can connect to their respective schemas.

Test batchDataSource:

Ensure that Spring Batch can connect to the batch schema and access the metadata tables.

Test applicationDataSource:

Confirm that your application logic can connect to the application schema and perform data operations.

Executing a Sample Job

Create a simple Spring Batch job to test the configuration.

SampleJobConfig.java

package com.example.jobs;

import org.springframework.batch.core.Job;
import org.springframework.batch.core.Step;

import org.springframework.batch.core.configuration.annotation.JobBuilderFactory;
import org.springframework.batch.core.configuration.annotation.StepBuilderFactory;

import org.springframework.batch.repeat.RepeatStatus;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class SampleJobConfig {

    private final JobBuilderFactory jobBuilderFactory;
    private final StepBuilderFactory stepBuilderFactory;

    public SampleJobConfig(JobBuilderFactory jobBuilderFactory,
                           StepBuilderFactory stepBuilderFactory) {

        this.jobBuilderFactory = jobBuilderFactory;
        this.stepBuilderFactory = stepBuilderFactory;
    }

    @Bean
    public Job sampleJob() {
        return jobBuilderFactory.get("sampleJob")
                .start(sampleStep())
                .build();
    }

    @Bean
    public Step sampleStep() {
        return stepBuilderFactory.get("sampleStep")
                .tasklet((contribution, chunkContext) -> {
                    // Business logic goes here
                    System.out.println("Executing sample step...");
                    return RepeatStatus.FINISHED;
                })
                .build();
    }
}

Note: The sample job simply prints a message. You can expand it to include actual business logic that interacts with the applicationDataSource.

Running the Application

Run your Spring Boot application using your IDE or build tool (e.g., Maven or Gradle).

Console Output:

Look for logs indicating successful connection to both data sources.

Database Verification:
- Check the BATCH_JOB_INSTANCE, BATCH_JOB_EXECUTION, and other metadata tables in the batch_schema_user schema to confirm that job execution details are being recorded.
- Ensure that any data manipulations performed by your business logic affect tables in the app_schema_user schema.

Conclusion

By defining multiple DataSource beans and externalizing configuration properties into properties files, we've successfully configured a Spring Batch application to use separate Oracle schemas for batch metadata and application data. This separation enhances the organization of your database structure and allows for more granular control over database access and security.

Externalizing configuration properties also simplifies the management of different environments and improves the maintainability of your application.