Here I am going to show a simple Spring Boot Batch example, which will read data from csv file and write into a database using JdbcTemplate.

Spring Boot Batch CSV to Database:

1. Technologies:

  • Spring Boot 2.1.3
  • Spring Batch 2.1.3
  • MySql
  • JDBC Template
  • Java 8

2. Project Structure

Spring Boot Batch CSV to Database Example-min

Spring Boot Batch Example:

3 Dependencies:

pom.xml
<?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>
  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.1.3.RELEASE</version>
    <relativePath/> <!-- lookup parent from repository -->
  </parent>
  <groupId>com.onlinetutorialspoint</groupId>
  <artifactId>SpringBoot-Batch-Example</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <name>SpringBoot-Batch-Example</name>
  <description>Spring Boot Batch CSV to Database Example</description>

  <properties>
    <java.version>1.8</java.version>
  </properties>

  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-batch</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>

    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>6.0.5</version>
    </dependency>
  </dependencies>

  <build>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
      </plugin>
    </plugins>
  </build>

</project>

4. Preparing Configurations:

4.1 Database Schema:

MySql Terminal
CREATE TABLE employee  (
    first_name VARCHAR(40),
    last_name VARCHAR(40),
    company_name VARCHAR(40),
    address VARCHAR(40),
    city VARCHAR(40),
    county VARCHAR(40),
    state VARCHAR(40),
    zip VARCHAR(10)
);

4.2 Sample employee.csv data:

employee.csv
first_name,last_name,company_name,address,city,county,state,zip
James,Butt,"Benton, John B Jr",6649 N Blue Gum St,New Orleans,Orleans,LA,70116
Josephine,Darakjy,"Chanay, Jeffrey A Esq",4 B Blue Ridge Blvd,Brighton,Livingston,MI,48116
Art,Venere,"Chemel, James L Cpa",8 W Cerritos Ave #54,Bridgeport,Gloucester,NJ,8014
Lenna,Paprocki,Feltz Printing Service,639 Main St,Anchorage,Anchorage,AK,99501
Donette,Foller,Printing Dimensions,34 Center St,Hamilton,Butler,OH,45011
Simona,Morasca,"Chapman, Ross E Esq",3 Mcauley Dr,Ashland,Ashland,OH,44805
..........
..........

5. Application properties:

application.properties
spring.datasource.url=jdbc:mysql://localhost:3306/otp
spring.datasource.username=root
spring.datasource.password=12345
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

spring.datasource.initialize=true
spring.datasource.schema=classpath:schema.sql
spring.batch.initialize-schema=ALWAYS

6. Transfer Objects:

Employee.java – representing CSV file data and EmployeeDTO.java – representing a database table.

6.1 Employee.java

Employee.java
package com.onlinetutorialspoint.model;

import java.io.Serializable;

public class Employee implements Serializable {
    private String firstName;
    private String lastName;
    private String companyName;
    private String address;
    private String city;
    private String county;
    private String state;
    private String zip;


    public Employee() {
    }

    public Employee(String firstName, String lastName, String companyName, String address, String city, String county, String state, String zip) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.companyName = companyName;
        this.address = address;
        this.city = city;
        this.county = county;
        this.state = state;
        this.zip = zip;
    }
  // getters() and setters()
}

6.2 EmployeeDTO.java

EmployeeDTO.java
package com.onlinetutorialspoint.model;

public class EmployeeDTO {
    private String firstName;
    private String lastName;
    private String companyName;
    private String address;
    private String city;
    private String county;
    private String state;
    private String zip;


    public EmployeeDTO() {
    }

    public EmployeeDTO(String firstName, String lastName, String companyName, String address, String city, String county, String state, String zip) {
        this.firstName = firstName;
        this.lastName = lastName;
        this.companyName = companyName;
        this.address = address;
        this.city = city;
        this.county = county;
        this.state = state;
        this.zip = zip;
    }
    // getters() and setters()
}

7. Spring Batch Configuration:

Java-based spring boot batch configuration class.

FlatFileItemReader – Reads lines from input setResource(Resource r) for our case input file (employee.csv) is reading from classpath. And Line mapped with an item using setLineMapper(LineMapper).

JdbcBatchItemWriter – It implements the ItemWriter interface: uses the batching features from NamedParameterJdbcTemplate to execute a batch of statements for all items provided. We must provide an SQL query and a special callback in the form of either ItemPreparedStatementSetter or ItemSqlParameterSourceProvider.

JobBuilderFactory – Used to building jobs of various kinds.

StepBuilderFactory – Entry point for building all kinds of steps.

SpringBatchConfig.java
package com.onlinetutorialspoint.config;

import com.onlinetutorialspoint.listener.JobListener;
import com.onlinetutorialspoint.model.Employee;
import com.onlinetutorialspoint.model.EmployeeDTO;
import com.onlinetutorialspoint.processor.EmployeeProcessor;
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.launch.support.RunIdIncrementer;
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.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;

@Configuration
@EnableBatchProcessing
public class SpringBatchConfig {
    @Autowired
    public JobBuilderFactory jobBuilderFactory;

    @Autowired
    public StepBuilderFactory stepBuilderFactory;

    @Autowired
    public DataSource dataSource;

    @Bean
    public FlatFileItemReader<Employee> reader() {
        FlatFileItemReader<Employee> reader = new FlatFileItemReader<Employee>();
        reader.setResource(new ClassPathResource("employee.csv"));

        reader.setLineMapper(new DefaultLineMapper<Employee>() {{
            setLineTokenizer(new DelimitedLineTokenizer() {{
                setNames(new String[] { "first_name", "last_name","company_name","address","city","county","state","zip" });
            }});
            setFieldSetMapper(new BeanWrapperFieldSetMapper() {{
                setTargetType(Employee.class);
            }});
        }});
        return reader;
    }


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

    @Bean
    public JdbcBatchItemWriter<EmployeeDTO> writer() {
        JdbcBatchItemWriter<EmployeeDTO> writer = new JdbcBatchItemWriter<EmployeeDTO>();
        writer.setItemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider<>());
        writer.setSql("INSERT INTO employee (first_name,last_name,company_name,address,city,county,state,zip) " +
                "VALUES (:firstName, :lastName,:companyName,:address,:city,:county,:state,:zip)");
        writer.setDataSource(dataSource);
        return writer;
    }

    @Bean
    public Job importUserJob(JobListener listener) {
        return jobBuilderFactory.get("importUserJob")
                .incrementer(new RunIdIncrementer())
                .listener(listener)
                .flow(step1())
                .end()
                .build();
    }

    @Bean
    public Step step1() {
        return stepBuilderFactory.get("step1")
                .<Employee, EmployeeDTO> chunk(10)
                .reader(reader())
                .processor(processor())
                .writer(writer())
                .build();
    }

}

8. Spring batch ItemProcessor:

Processing the Batch item based on the chunk size: For our case reading employee data from employee object and transforming it into employee dto object. Typical business logic goes here! based on your requirement.

EmployeeProcessor.java
package com.onlinetutorialspoint.processor;

import com.onlinetutorialspoint.model.Employee;

import com.onlinetutorialspoint.model.EmployeeDTO;
import org.springframework.batch.item.ItemProcessor;

public class EmployeeProcessor implements ItemProcessor<Employee, EmployeeDTO> {

    @Override
    public EmployeeDTO process(final Employee employee) throws Exception {
        System.out.println("Transforming Employee(s) to EmployeeDTO(s)..");
        final EmployeeDTO empployeeDto = new EmployeeDTO(employee.getFirstName(), employee.getLastName(),
                employee.getCompanyName(), employee.getAddress(),employee.getCity(),employee.getCounty(),employee.getState()
        ,employee.getZip());

        return empployeeDto;
    }

}

9. Spring Batch Listener:

JobExecutionListenerSupport: Provides callbacks at specific points in the lifecycle of a Job when before and after job execution. afterjob() Callback after completion of a job. Called after both successful and failed executions.

To perform logic on a particular status, use “if (jobExecution.getStatus() == BatchStatus.X)“.

JobListener.java
package com.onlinetutorialspoint.listener;

import com.onlinetutorialspoint.model.EmployeeDTO;
import org.springframework.batch.core.BatchStatus;
import org.springframework.batch.core.JobExecution;
import org.springframework.batch.core.listener.JobExecutionListenerSupport;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
public class JobListener extends JobExecutionListenerSupport {
    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public JobListener(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    @Override
    public void afterJob(JobExecution jobExecution) {
        if(jobExecution.getStatus() == BatchStatus.COMPLETED) {
            System.out.println("In Completion Listener ..");
            List<EmployeeDTO> results = jdbcTemplate.query("SELECT first_name,last_name,company_name,address,city,county,state,zip FROM employee",
                    (rs,rowNum)->{
                        return new EmployeeDTO(rs.getString(1), rs.getString(2),rs.getString(3),rs.getString(4),
                                rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8));
                    }
            );
            results.forEach(System.out::println);
        }
    }
}

10. Spring Boot Main:

SpringBootBatchExampleApplication.java
package com.onlinetutorialspoint;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SpringBootBatchExampleApplication {

  public static void main(String[] args) {
    SpringApplication.run(SpringBootBatchExampleApplication.class, args);
  }

}

11. Build the Application:

Terminal
cgoka@work:~/Documents/Work/Spring_Examples/SpringBoot-Batch-Example$ mvn clean install
WARNING: An illegal reflective access operation has occurred
WARNING: Illegal reflective access by com.google.inject.internal.cglib.core.$ReflectUtils$1 (file:/usr/share/maven/lib/guice.jar) to method java.lang.ClassLoader.defineClass(java.lang.String,byte[],int,int,java.security.ProtectionDomain)
WARNING: Please consider reporting this to the maintainers of com.google.inject.internal.cglib.core.$ReflectUtils$1
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
WARNING: All illegal access operations will be denied in a future release
[INFO] Scanning for projects...
[INFO] 
[INFO] ------------------------------------------------------------------------
[INFO] Building SpringBoot-Batch-Example 0.0.1-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO] 
[INFO] --- maven-clean-plugin:3.1.0:clean (default-clean) @ SpringBoot-Batch-Example ---
[INFO] Deleting /home/cgoka/Documents/Work/Spring_Examples/SpringBoot-Batch-Example/target
[INFO] 
[INFO] --- maven-resources-plugin:3.1.0:resources (default-resources) @ SpringBoot-Batch-Example ---
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] Copying 1 resource
[INFO] Copying 2 resources
[INFO] 
[INFO] --- maven-compiler-plugin:3.8.0:compile (default-compile) @ SpringBoot-Batch-Example ---
[INFO] Changes detected - recompiling the module!
[INFO] Compiling 5 source files to /home/cgoka/Documents/Work/Spring_Examples/SpringBoot-Batch-Example/target/classes
.........
.........

12. Run the Application:

Terminal
cgoka@work:~/Documents/Work/Spring_Examples/SpringBoot-Batch-Example$ mvn spring-boot:run
WARNING: An illegal reflective access operation has occurred
WARNING: Illegal reflective access by com.google.inject.internal.cglib.core.$ReflectUtils$1 (file:/usr/share/maven/lib/guice.jar) to method java.lang.ClassLoader.defineClass(java.lang.String,byte[],int,int,java.security.ProtectionDomain)
WARNING: Please consider reporting this to the maintainers of com.google.inject.internal.cglib.core.$ReflectUtils$1
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
WARNING: All illegal access operations will be denied in a future release
[INFO] Scanning for projects...
[INFO] 
[INFO] ------------------------------------------------------------------------
[INFO] Building SpringBoot-Batch-Example 0.0.1-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO] 
[INFO] >>> spring-boot-maven-plugin:2.1.3.RELEASE:run (default-cli) > test-compile @ SpringBoot-Batch-Example >>>
[INFO] 
[INFO] --- maven-resources-plugin:3.1.0:resources (default-resources) @ SpringBoot-Batch-Example ---
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] Copying 1 resource
[INFO] Copying 2 resources
[INFO] 
[INFO] --- maven-compiler-plugin:3.8.0:compile (default-compile) @ SpringBoot-Batch-Example ---
[INFO] Changes detected - recompiling the module!
[INFO] Compiling 6 source files to /home/cgoka/Documents/Work/Spring_Examples/SpringBoot-Batch-Example/target/classes
[INFO] /home/cgoka/Documents/Work/Spring_Examples/SpringBoot-Batch-Example/src/main/java/com/onlinetutorialspoint/config/SpringBatchConfig.java: /home/cgoka/Documents/Work/Spring_Examples/SpringBoot-Batch-Example/src/main/java/com/onlinetutorialspoint/config/SpringBatchConfig.java uses unchecked or unsafe operations.
[INFO] /home/cgoka/Documents/Work/Spring_Examples/SpringBoot-Batch-Example/src/main/java/com/onlinetutorialspoint/config/SpringBatchConfig.java: Recompile with -Xlint:unchecked for details.
[INFO] 
[INFO] --- maven-resources-plugin:3.1.0:testResources (default-testResources) @ SpringBoot-Batch-Example ---
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] skip non existing resourceDirectory /home/cgoka/Documents/Work/Spring_Examples/SpringBoot-Batch-Example/src/test/resources
[INFO] 
[INFO] --- maven-compiler-plugin:3.8.0:testCompile (default-testCompile) @ SpringBoot-Batch-Example ---
[INFO] Nothing to compile - all classes are up to date
[INFO] 
[INFO] <<< spring-boot-maven-plugin:2.1.3.RELEASE:run (default-cli) < test-compile @ SpringBoot-Batch-Example <<<
[INFO] 
[INFO] 
[INFO] --- spring-boot-maven-plugin:2.1.3.RELEASE:run (default-cli) @ SpringBoot-Batch-Example ---

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.1.3.RELEASE)

2019-03-20 02:55:12.956  INFO 30969 --- [           main] c.o.SpringBootBatchExampleApplication    : Starting SpringBootBatchExampleApplication on work with PID 30969 (/home/cgoka/Documents/Work/Spring_Examples/SpringBoot-Batch-Example/target/classes started by cgoka in /home/cgoka/Documents/Work/Spring_Examples/SpringBoot-Batch-Example)
2019-03-20 02:55:12.961  INFO 30969 --- [           main] c.o.SpringBootBatchExampleApplication    : No active profile set, falling back to default profiles: default
2019-03-20 02:55:14.298  INFO 30969 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
Wed Mar 20 02:55:14 IST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
2019-03-20 02:55:14.693  INFO 30969 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
.....
.....
2019-03-20 02:55:16.287  INFO 30969 --- [           main] o.s.b.a.b.JobLauncherCommandLineRunner   : Running default command line with: []
2019-03-20 02:55:16.611  INFO 30969 --- [           main] o.s.b.c.l.support.SimpleJobLauncher      : Job: [FlowJob: [name=importUserJob]] launched with the following parameters: [{run.id=7}]
2019-03-20 02:55:16.788  INFO 30969 --- [           main] o.s.batch.core.job.SimpleStepHandler     : Executing step: [step1]
Transforming Employee(s) to EmployeeDTO(s)..
Transforming Employee(s) to EmployeeDTO(s)..
Transforming Employee(s) to EmployeeDTO(s)..
Transforming Employee(s) to EmployeeDTO(s)..
Transforming Employee(s) to EmployeeDTO(s)..
Transforming Employee(s) to EmployeeDTO(s)..
Transforming Employee(s) to EmployeeDTO(s)..
Transforming Employee(s) to EmployeeDTO(s)..
.....
.....

Happy Learning 🙂

Download Example

Related Posts

  1. Spring Boot H2 Database + JDBC Template Example
  2. Spring Boot How to change the Tomcat to Jetty Server
  3. Spring Boot Actuator Database Health Check
  4. Spring Boot Security MySQL Database Integration Example
  5. Spring Boot Apache ActiveMq In Memory Example
  6. Spring Boot JdbcTemplate CRUD Operations Mysql
  7. Spring Boot Environment Properties reading based on activeprofile
  8. SSL Spring Boot HTTPs Enabling Example
  9. How to set Spring Boot SetTimeZone
  10. Spring Boot JNDI Configuration – External Tomcat
  11. External Apache ActiveMQ Spring Boot Example
  12. Spring Boot MongoDB + Spring Data Example
  13. Simple Spring Boot Example
  14. Spring Boot PDF iText integration with String Template
  15. Spring Boot JPA Integration Example