How to read data from database and write to CSV file in Spring Boot Batch

Spring boot batch provides the ability to read data from a database and writes it to a CSV file or write to database using jpa. CSV files are the most convenient way to move data from one application to another. The source app will read data from the database and write it to a CSV file. In the target application, the CSV file is uploaded. In this article, we’ll be using the MySQL database. Spring boot batch provides the ability to read data from a MySQL database and writes it to a CSV file. In this post, we’ll look at how to use spring boot batch to read data from a database and write it to a csv file.

The data is stored in a table in the MySQL Database. Spring boot batch will read and process data from the table. The processed data will be saved in the form of a CSV file. The JPA repository is used to read data from the MySQL database. The CVS (comma separated values) file contains the table data in each line as a comma separated string. The spring boot batch will write the table data in each line of the CSV file as a comma separated string. The FlatFileItemWriter class helps in the writing of table data to a CSV file.



Database SQL Query

The employee table is created in MySQL Database. The table contains three columns id, name and salary. Three sample data is inserted in the table using sql insert statement. Spring boot batch will read data from the employee table and creates a cvs file and adds the employee details as a comma separated string.

create table employee (
id int primary key,
name varchar(100),
salary int
);

insert into employee values (1,'name01',1000);
insert into employee values (2,'name02',2000);
insert into employee values (3,'name03',3000);


Data Model class

In this example, the java bean data model class is created. The employee class has fields for id, name, and salary. The Employee class will be assigned the data from the example csv file. The spring boot batch reads data from a csv file and assigns it to the Employee class.

Employee.java

package com.yawintutor;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class Employee {
	@GeneratedValue(strategy = GenerationType.AUTO)
	@Id
	private int id;
	private String name;
	private int salary;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getSalary() {
		return salary;
	}
	public void setSalary(int salary) {
		this.salary = salary;
	}	
}


JPA Repository

The Employee JPA Repository stores employee information in the MySQL Database. Database configurations are added to the application.properties file. The JPA Repository will run the save command to save the employee data in the database.

EmployeeRepository.java

package com.yawintutor;

import org.springframework.data.jpa.repository.JpaRepository;

public interface EmployeeRepository extends JpaRepository<Employee, Long> {

}


Spring Boot Batch Configurations

The batch job and batch step configurations are stored in the spring boot batch configuration file. The JdbcCursorItemReader reads data from a database table. The FlatFileItemWriter is used to write the table data as a comma separated string on each line to a csv file.

The data from the MySQL Database table will be read using the JdbcCursorItemReader reader. The read data will be converted into a java bean object. The java bean object is used to process the data. In this example, the employee object is created. The employee object is transformed to a comma-separated string and saved to a CSV file.

BatchConfig.java

package com.yawintutor;

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.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

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

	@Autowired
	public StepBuilderFactory stepBuilderFactory;
		
	@Autowired
	MyCustomReader myCustomReader;
	
	@Autowired
	MyCustomWriter myCustomWriter;

	@Bean
	public Job createJob() {
		return jobBuilderFactory.get("MyJob")
				.incrementer(new RunIdIncrementer())
				.flow(createStep()).end().build();
	}

	@Bean
	public Step createStep() {
		return stepBuilderFactory.get("MyStep")
				.<Employee, Employee> chunk(1)
				.reader(myCustomReader)
				.writer(myCustomWriter)
				.build();
	}
}


ItemReader Implementation

The Reader class is created by extending the JdbcCursorItemReader class and implementing the ItemReader Interface. The datasource and sql query should be added to the reader class JdbcCursorItemReader. To identify and convert to a java bean object, a RowMapper implementation class is required. The EmployeeRowMapper is created by implementing the RowMapper interface.

MyCustomReader.java

package com.yawintutor;

import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.springframework.batch.item.ItemReader;
import org.springframework.batch.item.database.JdbcCursorItemReader;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

@Component
public class MyCustomReader extends JdbcCursorItemReader<Employee> implements ItemReader<Employee>{
	
	public MyCustomReader(@Autowired DataSource dataSource) {
		setDataSource(dataSource);
		setSql("SELECT id, name, salary FROM employee");
		setFetchSize(100);
		setRowMapper(new EmployeeRowMapper());
	}
	
	public class EmployeeRowMapper implements RowMapper<Employee> {
		@Override
		public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
			Employee employee  = new Employee();
			employee.setId(rs.getInt("id"));
			employee.setName(rs.getString("name"));
			employee.setSalary(rs.getInt("salary"));
			return employee;
		}
	}
}


ItemWriter Implementation

The Writer class is created by extending FlatFileItemWriter and implementing the ItemWriter Interface. The FlatFileItemWriter class helps in the conversion of a java bean object to a csv file. The csv file name should be added to the FlatFileItemWriter class. The column names will be included in the csv file by include the FlatFileItemWriter class.

MyCustomWriter.java

package com.yawintutor;

import org.springframework.batch.item.file.FlatFileItemWriter;
import org.springframework.batch.item.file.transform.BeanWrapperFieldExtractor;
import org.springframework.batch.item.file.transform.DelimitedLineAggregator;
import org.springframework.core.io.FileSystemResource;
import org.springframework.stereotype.Component;

@Component
public class MyCustomWriter extends FlatFileItemWriter<Employee> {

	public MyCustomWriter() {
		setResource(new FileSystemResource("data/output.csv"));
		setLineAggregator(getDelimitedLineAggregator());
	}
	
	public DelimitedLineAggregator<Employee> getDelimitedLineAggregator() {
		BeanWrapperFieldExtractor<Employee> beanWrapperFieldExtractor = new BeanWrapperFieldExtractor<Employee>();
		beanWrapperFieldExtractor.setNames(new String[] {"id", "name", "salary"});

		DelimitedLineAggregator<Employee> delimitedLineAggregator = new DelimitedLineAggregator<Employee>();
		delimitedLineAggregator.setDelimiter(",");
		delimitedLineAggregator.setFieldExtractor(beanWrapperFieldExtractor);
		return delimitedLineAggregator;
		
	}
}


Scheduler class

SchedulerConfig.java

package com.yawintutor;

import java.text.SimpleDateFormat;
import java.util.Calendar;

import org.springframework.batch.core.Job;
import org.springframework.batch.core.JobParameters;
import org.springframework.batch.core.JobParametersBuilder;
import org.springframework.batch.core.launch.JobLauncher;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;

@Configuration
@EnableScheduling
public class SchedulerConfig {

	@Autowired
	JobLauncher jobLauncher;

	@Autowired
	Job job;

	SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");

	@Scheduled(fixedDelay = 5000, initialDelay = 5000)
	public void scheduleByFixedRate() throws Exception {
		System.out.println("Batch job starting");
		JobParameters jobParameters = new JobParametersBuilder()
				.addString("time", format.format(Calendar.getInstance().getTime())).toJobParameters();
		jobLauncher.run(job, jobParameters);
		System.out.println("Batch job executed successfully\n");
	}
}


Spring boot Batch Main Class

The main class for the spring boot batch will be shown as below. The default implementation will be used for the main class. The main method in this file will be executed when the spring boot batch application starts.

SpringBootBatch4Application.java

package com.yawintutor;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SpringBootBatch4Application {

	public static void main(String[] args) {
		SpringApplication.run(SpringBootBatch4Application.class, args);
	}
}


Application properties details

The application properties will be placed in the resources folder’s application.properties files. The application.properties now has two types of setting. One is for the mysql database configuration, and the other is for allowing batch-related tables to be created in the database.

application.properties

spring.datasource.url=jdbc:mysql://localhost/testdb
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update
spring.batch.initialize-schema=ALWAYS


Application configuration using pom.xml file

The required application configurations and dependencies are created using maven pom.xml file. The Maven POM.xml file contains dependencies for spring boot batch, JPA Repository and mysql database. If you are using some other database other than MySQL Database, you need to add the dependency to the respective database in the pom.xml file.

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.5.3</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.yawintutor</groupId>
	<artifactId>SpringBootBatchCSVToDB</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>SpringBootBatchCSVToDB</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>11</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-batch</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.batch</groupId>
			<artifactId>spring-batch-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>


How to run

When. the spring boot application starts, the following logs will be shown in the console window.

2021-07-27 12:33:35.356  INFO 55819 --- [           main] o.s.batch.core.step.AbstractStep         : Step: [MyStep] executed in 76ms
2021-07-27 12:33:35.369  INFO 55819 --- [           main] o.s.b.c.l.support.SimpleJobLauncher      : Job: [FlowJob: [name=MyJob]] completed with the following parameters: [{run.id=1, time=2021-07-27 12:32:14.800}] and the following status: [COMPLETED] in 103ms
Batch job starting
2021-07-27 12:33:40.197  INFO 55819 --- [   scheduling-1] o.s.b.c.l.support.SimpleJobLauncher      : Job: [FlowJob: [name=MyJob]] launched with the following parameters: [{time=2021-07-27 12:33:40.175}]
2021-07-27 12:33:40.215  INFO 55819 --- [   scheduling-1] o.s.batch.core.job.SimpleStepHandler     : Executing step: [MyStep]
2021-07-27 12:33:40.257  INFO 55819 --- [   scheduling-1] o.s.batch.core.step.AbstractStep         : Step: [MyStep] executed in 42ms
2021-07-27 12:33:40.265  INFO 55819 --- [   scheduling-1] o.s.b.c.l.support.SimpleJobLauncher      : Job: [FlowJob: [name=MyJob]] completed with the following parameters: [{time=2021-07-27 12:33:40.175}] and the following status: [COMPLETED] in 64ms
Batch job executed successfully

Batch job starting
2021-07-27 12:33:45.288  INFO 55819 --- [   scheduling-1] o.s.b.c.l.support.SimpleJobLauncher      : Job: [FlowJob: [name=MyJob]] launched with the following parameters: [{time=2021-07-27 12:33:45.270}]
2021-07-27 12:33:45.302  INFO 55819 --- [   scheduling-1] o.s.batch.core.job.SimpleStepHandler     : Executing step: [MyStep]


Output file

The output csv file will be created in data folder. The output.csv file is created in the data folder.

data/output.csv

id,name.salary
1,name01,1000
2,name02,2000
3,name03,3000



Leave a Reply