[Spring Batch] Output table data to CSV file

Introduction

I tried batch processing using tasklet. I also use MyBatis cursors to handle mass loading of tables.

Creating a project

Create a project using Spring Initializr. In Dependencies, select Batch, Lombok, MyBatis, and the JDBC driver for your DB (My article uses MySQL).

setting file

application.properties


#Log level
logging.level.com.sample.demo=debug

#DB connection information
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/demo?characterEncoding=UTF-8&serverTimezone=JST&sslMode=DISABLED
spring.datasource.username=hoge
spring.datasource.password=hoge

#Fetch size
#mybatis.configuration-properties.default-fetch-size=10000
mybatis.configuration-properties.fetch-size=10000

#File write size
demo.write-size=1000

Source

Read the DB and output the file.

DemoTasklet.java


package com.sample.demo;

import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.cursor.Cursor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.batch.core.StepContribution;
import org.springframework.batch.core.scope.context.ChunkContext;
import org.springframework.batch.core.step.tasklet.Tasklet;
import org.springframework.batch.item.ItemStreamWriter;
import org.springframework.batch.repeat.RepeatStatus;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

@Component()
public class DemoTasklet implements Tasklet {
	private Logger logger = LoggerFactory.getLogger(DemoTasklet.class);

	@Autowired
	private DemoRepository demoRepository;

	@Autowired
	private ItemStreamWriter<DemoDTO> writer;

	@Autowired
	private DemoContext demoContext;

	public RepeatStatus execute(StepContribution stepContribution, ChunkContext chunkContext) throws Exception {
		logger.debug("DemoTasklet execute start");

		writer.open(chunkContext.getStepContext().getStepExecution().getExecutionContext());
		try(Cursor<DemoDTO> result = demoRepository.select()) {
			List<DemoDTO> data = new ArrayList<>();
			for(DemoDTO dto: result) {
				data.add(dto);
				if (data.size() >= demoContext.getWriteSize()) {
					writer.write(data);
					data.clear();
				}
			}
			if (data.size() > 0) writer.write(data);
		}
		writer.close();

		logger.debug("DemoTasklet execute finished");
		return RepeatStatus.FINISHED;
	}

}

Register the Tasklet and set the CSV file.

BatchConfig.java


package com.sample.demo;

import java.io.IOException;
import java.io.Writer;

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.file.FlatFileHeaderCallback;
import org.springframework.batch.item.file.FlatFileItemWriter;
import org.springframework.batch.item.file.transform.BeanWrapperFieldExtractor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.FileSystemResource;
import org.springframework.core.io.Resource;

@Configuration
@EnableBatchProcessing
public class BatchConfig {
	@Autowired
    private JobBuilderFactory jobBuilderFactory;

    @Autowired
    private StepBuilderFactory stepBuilderFactory;

    @Autowired
    private DemoTasklet demoTasklet;

    private Resource outputResource = new FileSystemResource("output/data.csv");

    @Bean
    public DemoContext createDemoContext() {
    	return new DemoContext();
    }

	@Bean
	public FlatFileItemWriter<DemoDTO> writer()
	{
		FlatFileItemWriter<DemoDTO> writer = new FlatFileItemWriter<>();
		writer.setResource(outputResource);
		writer.setEncoding("UTF-8");
		writer.setLineSeparator("\r\n");
		writer.setAppendAllowed(false);

		writer.setHeaderCallback(new FlatFileHeaderCallback() {
			public void writeHeader(Writer arg0) throws IOException {
				arg0.append("\"ID\",\"name\",\"mail address\"");
			}
		});

		writer.setLineAggregator(new CsvLineAggregator<DemoDTO>() {
			{
				setFieldExtractor(new BeanWrapperFieldExtractor<DemoDTO>() {
					{
						setNames(new String[] { "id", "name", "mailAddress" });
					}
				});
			}
		});

		return writer;
    }

	@Bean
	public Step step1() {
		return stepBuilderFactory.get("step1").tasklet(demoTasklet).build();
	}

	@Bean
	public Job job(Step step1) {
		return jobBuilderFactory.get("job").incrementer(new RunIdIncrementer()).start(step1).build();
	}
}

The key-value defined in [demo.] In application.properties is set.

DemoContext.java


package com.sample.demo;

import org.springframework.boot.context.properties.ConfigurationProperties;

import lombok.Data;

@ConfigurationProperties(prefix="demo")
@Data
public class DemoContext {
	private int writeSize;
}

If you use the class prepared by spring, you cannot set the enclosing character, so if you want to set the enclosing character, you need to make your own.

CsvLineAggregator.java


package com.sample.demo;

import java.util.Arrays;

import org.springframework.batch.item.file.transform.ExtractorLineAggregator;
import org.springframework.util.StringUtils;

public class CsvLineAggregator<T> extends ExtractorLineAggregator<T> {

	private String enclose = "\"";

	private String delimiter = ",";

	public void setEnclose(String enclose) {
		this.enclose = enclose;
	}

	public void setDelimiter(String delimiter) {
		this.delimiter = delimiter;
	}

	@Override
	protected String doAggregate(Object[] fields) {
		return StringUtils.collectionToDelimitedString(Arrays.asList(fields), this.delimiter, this.enclose, this.enclose);
	}

}

DTO

DemoDTO.java


package com.sample.demo;

import lombok.Getter;
import lombok.Setter;
import lombok.ToString;

@Data
public class DemoDTO {
	private String id;
	private String name;
	private String mailAddress;
}

Interface for SQL

DemoRepository.java


package com.sample.demo;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.cursor.Cursor;

@Mapper
public interface DemoRepository {

	Cursor<DemoDTO> select();

}

SQL

DemoRepository.xml


<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.sample.demo.DemoRepository">

    <select id="select" resultType="com.sample.demo.DemoDTO" fetchSize="${fetch-size}">
      SELECT
        id,
        name,
        mail_address as mailAddress
      FROM
        users
    </select>
</mapper>

Recommended Posts

[Spring Batch] Output table data to CSV file
Flow until output table data to view with Spring Boot
csv file output with opencsv
Output javadoc to word file
Output XML tree to file
How to use Spring Data JDBC
[How to install Spring Data Jpa]
Log output to file in Java
Spring Data JPA SQL log output
I made a tool to output the difference of CSV file
Migration file to add comment to Rails table
To write Response data directly in Spring
Ruby: CSV :: How to use Table Note
How to split Spring Boot message file
File output bean as JSON in spring
Sample to batch process data on DB with Apache Camel Spring Boot starters
[Ruby] How to convert CSV file to Yaml (Yml)
How to output CSV created by Rails to S3
Spring Data REST HAL Browser to check Spring REST operation
An introduction to Spring Boot + in-memory data grid
I tried to output multiplication table in Java
How to bind to property file in Spring Boot
[Spring Boot] How to refer to the property file
I tried to implement file upload with Spring MVC
I tried to read and output CSV with Outsystems
Output log to external file with slf4j + logback with Maven
I tried to get started with Spring Data JPA
[Java] [Spring] [Spring Batch] Do not create / use Spring Batch metadata table
Convert csv file to fixed length record file using enum
How to store Rakuten API data in a table
How to test file upload screen in Spring + Selenium
How to use In-Memory Job repository in Spring Batch
Gzip-compress byte array in Java and output to file