boot spring 解析csv_Spring Boot Batch（学习读取CSV写入到DB中）

参照

import mysql data;

create database batch;

use batch;

DROP TABLE people IF EXISTS;

CREATE TABLE people (

person_id INT(11) NOT NULL AUTO_INCREMENT,

first_name VARCHAR(20),

last_name VARCHAR(20),

PRIMARY KEY (`person_id`)

);

spring boot project 作成

POM.xml dependencies追加

org.springframework.boot

spring-boot-starter-batch

org.springframework.boot

spring-boot-starter-test

mysql

mysql-connector-java

runtime

application.properties mysql连接配置追加

spring.datasource.url=jdbc:mysql://localhost:3306/batch?useUnicode=true&characterEncoding=utf8

spring.datasource.username=root

spring.datasource.password=

spring.datasource.driver-class-name=com.mysql.jdbc.Driver

resource/sample-data.csv作成

Jill,Doe

Joe,Doe

Justin,Doe

Jane,Doe

John,Doe

代码作成

domain:

package cn.learn.spring.batch.domain;

public class Person {

private String lastName;

private String firstName;

public Person() {

}

public Person(String firstName, String lastName) {

this.firstName = firstName;

this.lastName = lastName;

}

public void setFirstName(String firstName) {

this.firstName = firstName;

}

public String getFirstName() {

return firstName;

}

public String getLastName() {

return lastName;

}

public void setLastName(String lastName) {

this.lastName = lastName;

}

@Override

public String toString() {

return "firstName: " + firstName + ", lastName: " + lastName;

}

}

Job 配置:

package cn.learn.spring.batch.job;

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 cn.learn.spring.batch.domain.Person;

import cn.learn.spring.batch.listener.JobCompletionNotificationListener;

import cn.learn.spring.batch.processor.PersonItemProcessor;

@Configuration

@EnableBatchProcessing

public class CsvToDbJobConfiguation {

@Autowired

public JobBuilderFactory jobBuilderFactory;

@Autowired

public StepBuilderFactory stepBuilderFactory;

@Autowired

public DataSource dataSource;

/**

* 读操作(读取CSV文件内容，并作为上下文内容传递)

* @return

*/

@Bean

public FlatFileItemReader reader() {

FlatFileItemReader reader = new FlatFileItemReader<>();

// load csv file

reader.setResource(new ClassPathResource("sample-data.csv"));

// 读取使用，分割的csv文件内容，并保存到Person中

reader.setLineMapper(new DefaultLineMapper() {{

// 截取的内容放到firstName,lastName

setLineTokenizer(new DelimitedLineTokenizer() {{

setNames(new String[] { "firstName", "lastName" });

}});

// 保存到Person

setFieldSetMapper(new BeanWrapperFieldSetMapper() {{

setTargetType(Person.class);

}});

}});

return reader;

}

/**

* 调用自定义的Processor，把String的内容Upper

* @return

*/

@Bean

public PersonItemProcessor processor() {

return new PersonItemProcessor();

}

/**

* 写操作把读取的CSV内容写到DB中

* @return

*/

@Bean

public JdbcBatchItemWriter writer() {

JdbcBatchItemWriter writer = new JdbcBatchItemWriter();

writer.setItemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider());

writer.setSql("INSERT INTO people (first_name, last_name) VALUES (:firstName, :lastName)");

writer.setDataSource(dataSource);

return writer;

}

// tag::jobstep[]

@Bean

public Job importUserJob(JobCompletionNotificationListener listener) {

return jobBuilderFactory.get("importUserJob")

.incrementer(new RunIdIncrementer())

.listener(listener)

.flow(step1())

.end()

.build();

}

@Bean

public Step step1() {

return stepBuilderFactory.get("step1")

. chunk(10)

.reader(reader())

.processor(processor())

.writer(writer())

.build();

}

// end::jobstep[]

}

Listener:

package cn.learn.spring.batch.listener;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.List;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

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.jdbc.core.RowMapper;

import org.springframework.stereotype.Component;

import cn.learn.spring.batch.domain.Person;

/**

* 作成后的监听作成

* @author chunhui.li

*

*/

@Component

public class JobCompletionNotificationListener extends JobExecutionListenerSupport {

private static final Logger log = LoggerFactory.getLogger(JobCompletionNotificationListener.class);

private final JdbcTemplate jdbcTemplate;

@Autowired

public JobCompletionNotificationListener(JdbcTemplate jdbcTemplate) {

this.jdbcTemplate = jdbcTemplate;

}

@Override

public void afterJob(JobExecution jobExecution) {

if(jobExecution.getStatus() == BatchStatus.COMPLETED) {

log.info("!!! JOB FINISHED! Time to verify the results");

List results = jdbcTemplate.query("SELECT first_name, last_name FROM people", new RowMapper() {

@Override

public Person mapRow(ResultSet rs, int row) throws SQLException {

return new Person(rs.getString(1), rs.getString(2));

}

});

for (Person person : results) {

log.info("Found in the database.");

}

}

}

}

Processor(自定义，把reader读取的内容转换):

package cn.learn.spring.batch.processor;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.batch.item.ItemProcessor;

import cn.learn.spring.batch.domain.Person;

/**

* 把Reader读取的Person内容Upper

* @author chunhui.li

*

*/

public class PersonItemProcessor implements ItemProcessor {

private static final Logger log = LoggerFactory.getLogger(PersonItemProcessor.class);

@Override

public Person process(final Person person) throws Exception {

final String firstName = person.getFirstName().toUpperCase();

final String lastName = person.getLastName().toUpperCase();

final Person transformedPerson = new Person(firstName, lastName);

log.info("Converting (" + person + ") into (" + transformedPerson + ")");

return transformedPerson;

}

}

执行后LOG

2017-09-27 13:04:22.400 INFO 3656 --- [ main] o.s.b.c.l.support.SimpleJobLauncher : Job: [FlowJob: [name=importUserJob]] launched with the following parameters: [{run.id=1, -spring.output.ansi.enabled=always}]

2017-09-27 13:04:22.437 INFO 3656 --- [ main] o.s.batch.core.job.SimpleStepHandler : Executing step: [step1]

2017-09-27 13:04:22.452 INFO 3656 --- [ main] c.l.s.b.processor.PersonItemProcessor : Converting (firstName: Jill, lastName: Doe) into (firstName: JILL, lastName: DOE)

2017-09-27 13:04:22.452 INFO 3656 --- [ main] c.l.s.b.processor.PersonItemProcessor : Converting (firstName: Joe, lastName: Doe) into (firstName: JOE, lastName: DOE)

2017-09-27 13:04:22.452 INFO 3656 --- [ main] c.l.s.b.processor.PersonItemProcessor : Converting (firstName: Justin, lastName: Doe) into (firstName: JUSTIN, lastName: DOE)

2017-09-27 13:04:22.452 INFO 3656 --- [ main] c.l.s.b.processor.PersonItemProcessor : Converting (firstName: Jane, lastName: Doe) into (firstName: JANE, lastName: DOE)

2017-09-27 13:04:22.452 INFO 3656 --- [ main] c.l.s.b.processor.PersonItemProcessor : Converting (firstName: John, lastName: Doe) into (firstName: JOHN, lastName: DOE)

2017-09-27 13:04:22.484 INFO 3656 --- [ main] .s.b.l.JobCompletionNotificationListener : !!! JOB FINISHED! Time to verify the results

2017-09-27 13:04:22.484 INFO 3656 --- [ main] .s.b.l.JobCompletionNotificationListener : Found in the database.

2017-09-27 13:04:22.484 INFO 3656 --- [ main] .s.b.l.JobCompletionNotificationListener : Found in the database.

2017-09-27 13:04:22.484 INFO 3656 --- [ main] .s.b.l.JobCompletionNotificationListener : Found in the database.

2017-09-27 13:04:22.484 INFO 3656 --- [ main] .s.b.l.JobCompletionNotificationListener : Found in the database.

2017-09-27 13:04:22.484 INFO 3656 --- [ main] .s.b.l.JobCompletionNotificationListener : Found in the database.

2017-09-27 13:04:22.484 INFO 3656 --- [ main] o.s.b.c.l.support.SimpleJobLauncher : Job: [FlowJob: [name=importUserJob]] completed with the following parameters: [{run.id=1, -spring.output.ansi.enabled=always}] and the following status: [COMPLETED]

2017-09-27 13:04:22.484 INFO 3656 --- [ main] c.l.s.batch.LearnSpringBatchApplication : Started LearnSpringBatchApplication in 2.687 seconds (JVM running for 3.306)

2017-09-27 13:04:22.499 INFO 3656 --- [ Thread-3] s.c.a.AnnotationConfigApplicationContext : Closing org.springframework.context.annotation.AnnotationConfigApplicationContext@58134517: startup date [Wed Sep 27 13:04:20 CST 2017]; root of context hierarchy

2017-09-27 13:04:22.515 INFO 3656 --- [ Thread-3] o.s.j.e.a.AnnotationMBeanExporter : Unregistering JMX-exposed beans on shutdown