[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>