Overview
In this tutorial, we show you how to read information from a CSV file and write to MySQL Database using Spring Batch Boot and Spring tools Eclipse. Using Spring Batch Flat file reader to read CSV file and Jdbc Batch Item Writer to write MySQL Database.
Follow the steps mentioned below to develop the Spring batch sample application.
Spring Batch video tutorial
Setting up Database
Execute the following MySQL script in order to create a database named springbatch with a table named user.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;
Project Structure
The following screenshot shows final structure of the project.
Creating the Project
Open Eclipse Oxygen java and install the Spring STS 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
Maven Dependencies
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>SpringBatchCsvToDatabase</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>SpringBatchCsvToDatabase</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 and write the following content in it.users.csv
jackrutorial1 jackrutorial2 jackrutorial3
Model
Create a User class under com.jackrutorial.bean package and write the following code in it.package com.jackrutorial.bean;
public class User {
private String name;
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.bean.User;
public class UserItemProcessor implements ItemProcessor<User, User> {
@Override
public User process(User user) throws Exception {
return user;
}
}
Spring Batch Annotations Configuration
Create a Job which will read users CSV file and write into Mysql Database. Create a BatchConfiguration class under com.jackrutorial package and write the following code in it.package com.jackrutorial;
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.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 org.springframework.jdbc.datasource.DriverManagerDataSource;
import com.jackrutorial.bean.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 FlatFileItemReader<User> reader(){
FlatFileItemReader<User> reader = new FlatFileItemReader<User>();
reader.setResource(new ClassPathResource("users.csv"));
reader.setLineMapper(new DefaultLineMapper<User>() {{
setLineTokenizer(new DelimitedLineTokenizer() {{
setNames(new String[] { "name" });
}});
setFieldSetMapper(new BeanWrapperFieldSetMapper<User>() {{
setTargetType(User.class);
}});
}});
return reader;
}
@Bean
public UserItemProcessor processor(){
return new UserItemProcessor();
}
@Bean
public JdbcBatchItemWriter<User> writer(){
JdbcBatchItemWriter<User> writer = new JdbcBatchItemWriter<User>();
writer.setItemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider<User>());
writer.setSql("INSERT INTO user(name) VALUES (:name)");
writer.setDataSource(dataSource);
return writer;
}
@Bean
public Step step1() {
return stepBuilderFactory.get("step1").<User, User> chunk(3)
.reader(reader())
.processor(processor())
.writer(writer())
.build();
}
@Bean
public Job importUserJob() {
return jobBuilderFactory.get("importUserJob")
.incrementer(new RunIdIncrementer())
.flow(step1())
.end()
.build();
}
}
Run Spring Boot Batch Job
Right click to the SpringBatchCsvToDatabaseApplication class, select Run As -> Java Application. This application will read data from users.csv file and write records in user table.And finally, check the user table in database. Below is the snapshot from database after this job.
