How to upload CSV file to oracle database using Spring Batch

In this post, we will see how to read excel file and upload CSV file to oracle database using Spring Batch. Consider we have below records in excel below. We will read this record and insert it into the Oracle database using Spring Batch and Spring Boot.

A high-level overview of example.

• Spring Batch ItemReader is getting used to reading data from resources(for example excel files).
• Spring Batch ItemProcessor is used to modify items before sending them back to ItemWriter.
• The ItemWriter is used getting used to write items to Database.

Example for how to upload CSV file to oracle database using Spring Batch

Spring batch maven dependency

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.javatute.com</groupId>
    <artifactId>springbatch</artifactId>
    <version>1.0-SNAPSHOT</version>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.1.RELEASE</version>
    </parent>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.batch</groupId>
            <artifactId>spring-batch-core</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-batch</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
    </dependencies>
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
</project>

Directory structure

Define model class i.e Student.java

package com.springbatchexample.entity;

public class Student {

    private Long id;
    private String name;
    private String rollNumber;

    public Student() {

    }
    public Student(Long id, String name, String rollNumber) {
        this.id = id;
        this.name = name;
        this.rollNumber = rollNumber;
    }
    //gettet & setter
}

Define custom StudentItemProcessor class implementing ItemProcessor interface and override process() method.

Note – The ItemProcessor is an optional component, used to transform, validate and filter the data.

package com.springbatchexample.config;

import com.springbatchexample.entity.Student;
import org.springframework.batch.item.ItemProcessor;

public class StudentItemProcessor implements ItemProcessor<Student, Student> {

    @Override
    public Student process(Student student) throws Exception {
        return student;
    }
}

The ItemProcessorinterface is the top-level interface in the hierarchy.

Define springBatchConfig.java class

package com.springbatchexample.config;

import com.springbatchexample.entity.Student;
import org.springframework.batch.core.Job;
import org.springframework.batch.core.Step;
import org.springframework.batch.core.configuration.annotation.EnableBatchProcessing;
import org.springframework.batch.core.configuration.annotation.JobBuilderFactory;
import org.springframework.batch.core.configuration.annotation.StepBuilderFactory;
import org.springframework.batch.core.job.builder.FlowJobBuilder;
import org.springframework.batch.core.job.builder.JobBuilder;
import org.springframework.batch.core.launch.support.RunIdIncrementer;
import org.springframework.batch.core.step.builder.SimpleStepBuilder;
import org.springframework.batch.core.step.builder.StepBuilder;
import org.springframework.batch.item.database.BeanPropertyItemSqlParameterSourceProvider;
import org.springframework.batch.item.database.JdbcBatchItemWriter;
import org.springframework.batch.item.file.FlatFileItemReader;
import org.springframework.batch.item.file.LineMapper;
import org.springframework.batch.item.file.mapping.BeanWrapperFieldSetMapper;
import org.springframework.batch.item.file.mapping.DefaultLineMapper;
import org.springframework.batch.item.file.transform.DelimitedLineTokenizer;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;

import javax.sql.DataSource;

@EnableBatchProcessing
@Configuration
public class SpringBatchConfig {
    @Autowired
    private DataSource dataSource;

    @Autowired
    private JobBuilderFactory jobBuilderFactory;

    @Autowired
    private StepBuilderFactory stepBuilderFactory;

    public FlatFileItemReader<Student> reader() {
        FlatFileItemReader<Student> itemReader = new FlatFileItemReader<>();
        itemReader.setResource(new ClassPathResource("data.csv"));
        itemReader.setLineMapper(getLineMapper());
        itemReader.setLinesToSkip(1);
        return itemReader;
    }

    private LineMapper<Student> getLineMapper() {
        DefaultLineMapper<Student> lineMapper = new DefaultLineMapper<>();
        DelimitedLineTokenizer tokenizer = new DelimitedLineTokenizer();

        String[] columnsToBeInserted = new String[]{"id", "roll_number", "name"};
        int[] fields = new int[]{0, 1, 2};
        tokenizer.setNames(columnsToBeInserted);
        tokenizer.setIncludedFields(fields);
        BeanWrapperFieldSetMapper<Student> fieldSetMapper = new BeanWrapperFieldSetMapper<>();
        fieldSetMapper.setTargetType(Student.class);
        lineMapper.setLineTokenizer(tokenizer);
        lineMapper.setFieldSetMapper(fieldSetMapper);
        return lineMapper;
    }

    @Bean
    public StudentItemProcessor processor() {
        return new StudentItemProcessor();
    }

    @Bean
    public JdbcBatchItemWriter<Student> writer() {
        JdbcBatchItemWriter<Student> writer = new JdbcBatchItemWriter<>();
        writer.setItemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider<>());
        writer.setSql("insert into student(id,roll_number,name) values (:id,:rollNumber,:name)");
        writer.setDataSource(dataSource);
        return writer;
    }

    @Bean
    public Job writeStudentDataIntoSqlDb() {
        JobBuilder jobBuilder = jobBuilderFactory.get("STUDENT_JOB");
        jobBuilder.incrementer(new RunIdIncrementer());
        FlowJobBuilder flowJobBuilder = jobBuilder.flow(getFirstStep()).end();
        Job job = flowJobBuilder.build();
        return job;
    }

    @Bean
    public Step getFirstStep() {
        StepBuilder stepBuilder = stepBuilderFactory.get("getFirstStep");
        SimpleStepBuilder<Student, Student> simpleStepBuilder = stepBuilder.chunk(1);
        return simpleStepBuilder.reader(reader()).processor(processor()).writer(writer()).build();
    }
}

Define SpringMain.java

package com.springbatchexample.main;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.domain.EntityScan;
import org.springframework.context.annotation.ComponentScan;

@SpringBootApplication
@ComponentScan(basePackages = "com.springbatchexample.*")
public class SpringMain {
    public static void main(String[] args) {
        SpringApplication.run(SpringMain.class, args);
    }
}

application.properties file for mysqldb

spring.datasource.url=jdbc:mysql://localhost:3306/springbootcrudexample
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
server.port = 9091
spring.batch.initialize-schema=always	

application.properties for oracle database

spring.datasource.url=jdbc:oracle:thin:@localhost:1521:XE
spring.datasource.username=SYSTEM
spring.datasource.password=oracle
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
# Show or not log for each sql query
spring.jpa.show-sql = true
spring.jpa.properties.hibernate.format_sql=true 
 
spring.jpa.hibernate.ddl-auto =create
spring.jpa.properties.hibern

data.csv

id,roll_number,name
1,0126,Daenerys
2,0127,Bran
3,0128,Sam
4,0129,Eddard
5,0130,Jon
6,0131,Cersei
7,0132,Tyrion
8,0133,Sansa
9,0134,Arya
10,0135,Jaime

Run SpringMain class and Verify the Oracle database. We should able to see record in Database.

That’s all about how to upload CSV file to oracle database using Spring Batch.

Download the source code from GitHub.

Spring Batch Docs.

Other Spring Batch examples.

• Spring Batch FlatFileItemReader Example
• JpaPagingItemReader Example
• JdbcPagingItemReader spring batch example
• StaxEventItemReader Example
• JsonItemReader Spring Batch Example
• JdbcCursorItemReader Spring Batch Example
• CompositeItemProcessor Spring Batch Example
• Spring Batch ItemReader Example
• StoredProcedureItemReader example
• AsyncItemProcessor Spring Batch Example

Spring Data JPA tutorial.

• Spring Data JPA CrudRepository findById()
• Spring Data findById() Vs getOne()
• Spring Data JPA JpaRepository getOne()
• Spring Data CrudRepository saveAll() and findAll().
• Spring Data CrudRepository existsById()
• Spring Data JPA delete() vs deleteInBatch()
• Spring Data JPA deleteAll() Vs deleteAllInBatch()
• Spring Data JPA JpaRepository deleteAllInBatch()
• Spring Data JPA deleteInBatch() Example
• Spring Data JPA JpaRepository saveAndFlush() Example
• Spring Data JPA CrudRepository count() Example
• Spring Data JPA CrudRepository delete() and deleteAll()