Overview

In this tutorial i will use Spring Batch framework to read data from csv file and write these data into Mysql database.

Technology Used

• spring-boot-spring-batch
• JDK 1.8
• Maven 3
• Mysql
• IDEA

Project Structure

Sql

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;

users.csv

jackrutorial1,male
jackrutorial2,female
jackrutorial3,male
asfg

User.java

•
public class User {
 private String name;
 public String getName() {
  return name;
 }
 public void setName(String name) {
  this.name = name;
 }
}

UserItemProcessor.java

import com.zhiyueinfo.springbatch.csvtomysql.model.User;
import org.springframework.batch.item.ItemProcessor;
​
public class UserItemProcessor implements ItemProcessor<User, User> {
 @Override
 public User process(User user) throws Exception {
  return user;
 }
} 

BatchConfiguration.java

import com.zhiyueinfo.springbatch.csvtomysql.listener.LogProcessListener;
import com.zhiyueinfo.springbatch.csvtomysql.listener.LogSkipListener;
import com.zhiyueinfo.springbatch.csvtomysql.model.User;
import com.zhiyueinfo.springbatch.csvtomysql.processor.UserItemProcessor;
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.FlatFileParseException;
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;
​
​
@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:3307/springbatch");
    dataSource.setUsername("root");
    dataSource.setPassword("123456");
​
    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", "gender"});
      }});
      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,gender) VALUES (:name,:gender)");
    writer.setDataSource(dataSource);
​
    return writer;
  }
​
  @Bean
  public Step step1() {
    return stepBuilderFactory.get("step1").<User, User>chunk(3)
        .reader(reader())
        .faultTolerant()
        .skipLimit(1)
        .skip(FlatFileParseException.class)
        .listener(logSkipListener())
        .processor(processor())
        .listener(logProcessListener())
        .writer(writer())
        .build();
  }
​
  @Bean
  public Job importUserJob() {
    return jobBuilderFactory.get("importUserJob")
        .incrementer(new RunIdIncrementer())
        .flow(step1())
        .end()
        .build();
  }
​
  @Bean
  public LogProcessListener logProcessListener() {
    return new LogProcessListener();
  }
​
  @Bean
  public LogSkipListener logSkipListener() {
    return new LogSkipListener();
  }
}

Run it

Source code download

springbatchtutorial_csvtomysql-master

Github