Spring Batch Boot Read From MySQL Database and Write into a CSV file Tutorial - Spring Boot Batch Tutorial

Overview

In this tutorial, we show you how to configure Spring Batch Boot Job to read information from a CSV file and write to MySQL Database using Eclipse Oxygen Java. Spring Batch Boot to read from MySQL database using JdbcCursorItemReader and write to a Flat file using FlatFileItemWriter.

Follow the steps mentioned below to develop this spring boot batch application 
Watch Tutorial

Create Database Table and populate it with sample data

Execute the following MySQL script in order to create a database named springbatch with a table named user and import user sample data

CREATE DATABASE `springbatch` /*!40100 DEFAULT CHARACTER SET utf8 */;

DROP TABLE IF EXISTS `springbatch`.`user`;
CREATE TABLE  `springbatch`.`user` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(45) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO user(id,name) VALUES(1, 'Jack Rutorial demo 1');
INSERT INTO user(id,name) VALUES(2, 'Jack Rutorial demo 2');
INSERT INTO user(id,name) VALUES(3, 'Jack Rutorial demo 3');

Project Structure

The following screenshot shows final structure of the project.

Creating the Project

Open Eclipse IDE and install Spring STS Eclipse - Spring tools Eclipse.

Go to File -> New -> Other... Select Spring Starter Project under Spring Boot category then click Next as shown below

In the next screen, you enter the content as shown below then click Next

In the next step, you choose Spring Boot Version is 1.5.10 and choose the Batch dependencies I/O -> Batch, then click Finish

Update pom.xml

Add the following to your project's pom.xml 

  
  <dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>5.0.4</version>
  </dependency>

The pom.xml file

<?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.jackrutorial</groupId>
 <artifactId>SpringBatchDatabaseToCsv</artifactId>
 <version>0.0.1-SNAPSHOT</version>
 <packaging>jar</packaging>

 <name>SpringBatchDatabaseToCsv</name>
 <description>Demo project for Spring Boot</description>

 <parent>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-parent</artifactId>
  <version>1.5.10.RELEASE</version>
  <relativePath/> <!-- lookup parent from repository -->
 </parent>

 <properties>
  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
  <java.version>1.8</java.version>
 </properties>

 <dependencies>
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-batch</artifactId>
  </dependency>
  
  <dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>5.0.4</version>
  </dependency>

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

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


</project>

Create the CSV file

We create the users.csv file in src/main/resources folder. Model
Create a User class under com.jackrutorial.model package and write the following code in it.

package com.jackrutorial.model;

public class User {
 private Integer id;
 private String name;
 
 public Integer getId() {
  return id;
 }
 public void setId(Integer id) {
  this.id = id;
 }
 public String getName() {
  return name;
 }
 public void setName(String name) {
  this.name = name;
 }
}

Processor

A custom user processor class that will process each and every User object. Create a UserItemProcessor class under com.jackrutorial package and write the following code in it.

package com.jackrutorial;

import org.springframework.batch.item.ItemProcessor;

import com.jackrutorial.model.User;

public class UserItemProcessor implements ItemProcessor<User, User> {

 @Override
 public User process(User user) throws Exception {
  return user;
 }

}

Spring Batch Boot Configuration
Create a Job which will read from database and write into csv file. Create a BatchConfiguration class under com.jackrutorial package and write the following code in it.

package com.jackrutorial;

import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.DataSource;

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.JdbcCursorItemReader;
import org.springframework.batch.item.file.FlatFileItemWriter;
import org.springframework.batch.item.file.transform.BeanWrapperFieldExtractor;
import org.springframework.batch.item.file.transform.DelimitedLineAggregator;
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 org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import com.jackrutorial.model.User;

@Configuration
@EnableBatchProcessing
public class BatchConfiguration {

 @Autowired
 public JobBuilderFactory jobBuilderFactory;
 
 @Autowired
 public StepBuilderFactory stepBuilderFactory;
 
 @Autowired
 public DataSource dataSource;
 
 @Bean
 public DataSource dataSource() {
  final DriverManagerDataSource dataSource = new DriverManagerDataSource();
  dataSource.setDriverClassName("com.mysql.jdbc.Driver");
  dataSource.setUrl("jdbc:mysql://localhost/springbatch");
  dataSource.setUsername("root");
  dataSource.setPassword("root");
  
  return dataSource;
 }
 
 @Bean
 public JdbcCursorItemReader<User> reader(){
  JdbcCursorItemReader<User> reader = new JdbcCursorItemReader<User>();
  reader.setDataSource(dataSource);
  reader.setSql("SELECT id,name FROM user");
  reader.setRowMapper(new UserRowMapper());
  
  return reader;
 }
 
 public class UserRowMapper implements RowMapper<User>{

  @Override
  public User mapRow(ResultSet rs, int rowNum) throws SQLException {
   User user = new User();
   user.setId(rs.getInt("id"));
   user.setName(rs.getString("name"));
   
   return user;
  }
  
 }
 
 @Bean
 public UserItemProcessor processor(){
  return new UserItemProcessor();
 }
 
 @Bean
 public FlatFileItemWriter<User> writer(){
  FlatFileItemWriter<User> writer = new FlatFileItemWriter<User>();
  writer.setResource(new ClassPathResource("users.csv"));
  writer.setLineAggregator(new DelimitedLineAggregator<User>() {{
   setDelimiter(",");
   setFieldExtractor(new BeanWrapperFieldExtractor<User>() {{
    setNames(new String[] { "id", "name" });
   }});
  }});
  
  return writer;
 }
 
 
 
 @Bean
 public Step step1() {
  return stepBuilderFactory.get("step1").<User, User> chunk(10)
    .reader(reader())
    .processor(processor())
    .writer(writer())
    .build();
 }
 
 @Bean
 public Job exportUserJob() {
  return jobBuilderFactory.get("exportUserJob")
    .incrementer(new RunIdIncrementer())
    .flow(step1())
    .end()
    .build();
 }
 
}

Run Spring Batch Boot 

Right click to the SpringBatchDatabaseToCsvApplication class, select Run As -> Java Application. This application will read data from user table and write into users.csv file.

Below is the generated users.csv found in {PROJECT_FOLDER}\SpringBatchDatabaseToCsv\target\classes folder

1,Jack Rutorial demo 1
2,Jack Rutorial demo 2
3,Jack Rutorial demo 3

References

• Spring Batch
• Spring Tools (aka Spring IDE and Spring Tool Suite)

• Tweet
• Share
• Share
• Share
• Share