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 Posts

🔧 Spring Batch Core Components

Understand how ItemReader, ItemProcessor, and ItemWriter form the core of Spring Batch jobs.

🔄 ItemProcessor with Example

Learn how to transform data between reading from the DB and writing to a CSV file.

🚫 Skip Policy & Error Handling

Skip bad records or handle exceptions during batch exports gracefully.

🔁 Conditional Flow in Jobs

Control step execution based on success/failure logic during export processes.