Exporting Data from Oracle Database to CSV using Spring Batch

Exporting Data from Oracle Database to CSV using Spring Batch

In this blog, we'll learn how to use Spring Batch to export data from an Oracle database to a CSV file. We'll break it down step-by-step and explain the configuration in detail.

💾 Step 1: Add Maven Dependencies

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-batch</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc11</artifactId>
    <scope>runtime</scope>
</dependency>

⚙️ Step 2: application.properties

spring.datasource.url=jdbc:oracle:thin:@localhost:1521:xe
spring.datasource.username=your_username
spring.datasource.password=your_password
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

spring.jpa.hibernate.ddl-auto=none
spring.jpa.show-sql=true
spring.batch.job.enabled=true

📋 Step 3: Oracle Table Structure

This example works with the auth_user table. Below is the table definition:

Name        Null?     Type
----------  --------  ---------------------
ID          NOT NULL  NUMBER(19)
EMAIL                 VARCHAR2(255 CHAR)
FIRST_NAME            VARCHAR2(255 CHAR)
LAST_NAME             VARCHAR2(255 CHAR)

📦 Step 4: Entity Class

import jakarta.persistence.*;

@Entity
@Table(name = "auth_user")
public class User {

    @Id
    private Long id;

    private String email;

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;

    // Getters and setters
}

🛠️ Step 5: Spring Batch Configuration

@Configuration
public class SpringBatchConfig {

    @Autowired
    private JobRepository repository;

    @Autowired
    private PlatformTransactionManager platformTransactionManager;

    @Autowired
    private DataSource dataSource;

    @Bean
    public Job databaseToCSVJob() {
        return new JobBuilder("database-to-csv-job", repository)
                .incrementer(new RunIdIncrementer())
                .start(exportStep())
                .build();
    }

    @Bean
    public Step exportStep() {
        return new StepBuilder("database-to-csv-step", repository)
                .chunk(10, platformTransactionManager)
                .reader(reader())
                .writer(writer())
                .build();
    }

    @Bean
    public FlatFileItemWriter writer() {
        FlatFileItemWriter writer = new FlatFileItemWriter<>();
        writer.setResource
        	(new FileSystemResource("F://blogger//user.csv"));

        BeanWrapperFieldExtractor extractor =
        	new BeanWrapperFieldExtractor<>();
        
        extractor.setNames(new String[]
        	{"id", "email", "firstName", "lastName"});

        DelimitedLineAggregator aggregator = 
        	new DelimitedLineAggregator<>();
        aggregator.setDelimiter(",");
        aggregator.setFieldExtractor(extractor);

        writer.setLineAggregator(aggregator);
        return writer;
    }

    @Bean
    public JdbcCursorItemReader reader() {
        JdbcCursorItemReader reader = new JdbcCursorItemReader<>();
        reader.setDataSource(dataSource);
        reader.setSql
        	("SELECT ID, EMAIL, FIRST_NAME, 
            	LAST_NAME FROM AUTH_USER");
        reader.setRowMapper((rs, rowNum) -> {
            User user = new User();
            user.setId(rs.getLong("ID"));
            user.setEmail(rs.getString("EMAIL"));
            user.setFirstName(rs.getString("FIRST_NAME"));
            user.setLastName(rs.getString("LAST_NAME"));
            return user;
        });
        return reader;
    }
}

🔍 Understanding the Configuration

• Job: The Spring Batch job named database-to-csv-job is the entry point to the batch process. It is uniquely identified by RunIdIncrementer so it can be re-run with different parameters.
• Step: The job has one step called database-to-csv-step which reads users from the Oracle DB and writes them to a CSV file in chunks of 10.
• Reader: Uses JdbcCursorItemReader to fetch records using SQL from the table auth_user.
• Writer: Uses FlatFileItemWriter to write user data to a file F://blogger//user.csv.
• Field Mapping: The CSV includes columns: ID, EMAIL, FIRST_NAME, LAST_NAME, PASSWORD.

✅ Output

Once you run the Spring Boot application, the job will create a file at:

F://blogger//user.csv

This CSV file will have the exported data like:

1,john@example.com,John,Doe,password123
2,jane@example.com,Jane,Smith,secret456

🎯 Conclusion

In this guide, we showed how to use Spring Batch to export data from an Oracle database to a CSV file, including how each part works and how to customize the export fields.

📤 Related Spring Batch Data Export Guides

Learn how data export jobs fit into the Spring Batch ecosystem by exploring related topics such as processing logic, error handling, job flow control, and performance optimization.

📡 Spring Batch SFTP File Upload

Upload files to an SFTP server using Spring Batch tasklets and Spring Integration. Includes real-world configuration and best practices.

🧱 Spring Batch Core Components

Understand how ItemReader, ItemProcessor, and ItemWriter work together when exporting data to CSV files.

🔄 Spring Batch ItemProcessor Example

Apply transformation and formatting logic before writing records into CSV output files.

🔁 CSV to Database with Spring Batch

Compare inbound (CSV → DB) and outbound (DB → CSV) batch processing patterns.

🚫 Skip Policy & Error Handling

Handle write failures and formatting errors gracefully while exporting large datasets.

🔀 Conditional Flow in Spring Batch Jobs

Control job execution paths based on CSV generation success or failure.

🧵 Multithreaded Step in Spring Batch

Improve export performance by parallelizing data processing and CSV writing steps.