6
8

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

【Spring Batch】テーブルデータをCSVファイルへ出力する

Posted at

#はじめに
taskletを使用したバッチ処理を試してみました。
テーブルの大量読み込みにも対応できるように、MyBatisのカーソルも使用しています。

##プロジェクトの作成
Spring Initializrを使用してプロジェクトを作成します。
DependenciesでBatchとLombok、MyBatis、使用するDBのJDBCドライバー(記事ではMySQLを使用)を選択してください。

##設定ファイル

application.properties
# ログレベル
logging.level.com.sample.demo=debug

# DB接続情報
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

# フェッチサイズ
#mybatis.configuration-properties.default-fetch-size=10000
mybatis.configuration-properties.fetch-size=10000

# ファイルの書込みサイズ
demo.write-size=1000

##ソース
DBの読込みとファイル出力を実行します。

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 開始");

		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 終了");
		return RepeatStatus.FINISHED;
	}

}

Taskletの登録とCSVファイルの設定を行います。

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\",\"名前\",\"メールアドレス\"");
			}
		});

		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();
	}
}

application.propertiesの[demo.]で定義されているkey-valueが設定されます。

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;
}

springで用意されているクラスだと囲み文字の設定が出来ないので、囲み文字の設定を行う場合、自作が必要になります。

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;
}

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>

6
8
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
6
8

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?