Learn to use Spring batch to read records from CSV file and insert into database using JdbcBatchItemWriter
. I am using embedded database H2
to demo this example.
Table of Contents Project Overview Maven Dependencies CSV Reader and Database Writer Configuration Configure H2 Database Demo
Project Overview
In this application, we will perform following tasks:
- Read employee records from CSV file with
FlatFileItemReader
- Configure
H2
database and createEMPLOYEE
table in it - Write employee records to
EMPLOYEE
table withJdbcBatchItemWriter
- Log items inserted to database using
ItemProcessor
- Verify inserted records using
H2
console
Package Structure

Maven Dependencies
Quickly go through maven dependencies needed to build this example. spring-boot-starter-web
is needed to verify data in H2
console from browser window.
<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> <groupId>com.howtodoinjava</groupId> <artifactId>App</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>jar</packaging> <name>App</name> <url>http://maven.apache.org</url> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.0.3.RELEASE</version> </parent> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> </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-web</artifactId> </dependency> <dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> <scope>runtime</scope> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> <repositories> <repository> <id>repository.spring.release</id> <name>Spring GA Repository</name> <url>http://repo.spring.io/release</url> </repository> </repositories> </project>
CSV Reader and Database Writer Configuration
- We will use
FlatFileItemReader
for reading the CSV file. we will use it’s standard configuration involvingDefaultLineMapper
,DelimitedLineTokenizer
andBeanWrapperFieldSetMapper
classes. - For writing the records in DB, we will use
JdbcBatchItemWriter
which is standard writer for executing batch queries in database for Spring batch jobs.
package com.howtodoinjava.demo.config; 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.ItemProcessor; 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.LineMapper; 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.beans.factory.annotation.Value; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.core.io.Resource; import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder; import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType; import com.howtodoinjava.demo.model.Employee; @Configuration @EnableBatchProcessing public class BatchConfig { @Autowired private JobBuilderFactory jobBuilderFactory; @Autowired private StepBuilderFactory stepBuilderFactory; @Value("classPath:/input/inputData.csv") private Resource inputResource; @Bean public Job readCSVFileJob() { return jobBuilderFactory .get("readCSVFileJob") .incrementer(new RunIdIncrementer()) .start(step()) .build(); } @Bean public Step step() { return stepBuilderFactory .get("step") .<Employee, Employee>chunk(5) .reader(reader()) .processor(processor()) .writer(writer()) .build(); } @Bean public ItemProcessor<Employee, Employee> processor() { return new DBLogProcessor(); } @Bean public FlatFileItemReader<Employee> reader() { FlatFileItemReader<Employee> itemReader = new FlatFileItemReader<Employee>(); itemReader.setLineMapper(lineMapper()); itemReader.setLinesToSkip(1); itemReader.setResource(inputResource); return itemReader; } @Bean public LineMapper<Employee> lineMapper() { DefaultLineMapper<Employee> lineMapper = new DefaultLineMapper<Employee>(); DelimitedLineTokenizer lineTokenizer = new DelimitedLineTokenizer(); lineTokenizer.setNames(new String[] { "id", "firstName", "lastName" }); lineTokenizer.setIncludedFields(new int[] { 0, 1, 2 }); BeanWrapperFieldSetMapper<Employee> fieldSetMapper = new BeanWrapperFieldSetMapper<Employee>(); fieldSetMapper.setTargetType(Employee.class); lineMapper.setLineTokenizer(lineTokenizer); lineMapper.setFieldSetMapper(fieldSetMapper); return lineMapper; } @Bean public JdbcBatchItemWriter<Employee> writer() { JdbcBatchItemWriter<Employee> itemWriter = new JdbcBatchItemWriter<Employee>(); itemWriter.setDataSource(dataSource()); itemWriter.setSql("INSERT INTO EMPLOYEE (ID, FIRSTNAME, LASTNAME) VALUES (:id, :firstName, :lastName)"); itemWriter.setItemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider<Employee>()); return itemWriter; } @Bean public DataSource dataSource(){ EmbeddedDatabaseBuilder embeddedDatabaseBuilder = new EmbeddedDatabaseBuilder(); return embeddedDatabaseBuilder.addScript("classpath:org/springframework/batch/core/schema-drop-h2.sql") .addScript("classpath:org/springframework/batch/core/schema-h2.sql") .addScript("classpath:employee.sql") .setType(EmbeddedDatabaseType.H2) .build(); } }
Also create DBLogProcessor
which will log the employee records before writing to database. It’s optional.
package com.howtodoinjava.demo.config; import org.springframework.batch.item.ItemProcessor; import com.howtodoinjava.demo.model.Employee; public class DBLogProcessor implements ItemProcessor<Employee, Employee> { public Employee process(Employee employee) throws Exception { System.out.println("Inserting employee : " + employee); return employee; } }
Model class
package com.howtodoinjava.demo.config; package com.howtodoinjava.demo.model; public class Employee { String id; String firstName; String lastName; //Setter and getter methods }
Application properties
#Disable batch job's auto start spring.batch.job.enabled=false spring.main.banner-mode=off #batch input files location input.dir=c:/temp/input
Logging configuration
<?xml version="1.0" encoding="UTF-8"?> <configuration scan="true"> <appender name="consoleAppender" class="ch.qos.logback.core.ConsoleAppender"> <encoder> <charset>UTF-8</charset> <Pattern>%d{yyyy-MM-dd HH:mm:ss} %p %X{TXNID} - %m%n</Pattern> </encoder> </appender> <root level="INFO"> <appender-ref ref="consoleAppender" /> </root> </configuration>
Configure H2 Database
We have already configured the datasource in BatchConfig.java
.
@Bean public DataSource dataSource(){ EmbeddedDatabaseBuilder embeddedDatabaseBuilder = new EmbeddedDatabaseBuilder(); return embeddedDatabaseBuilder.addScript("classpath:org/springframework/batch/core/schema-drop-h2.sql") .addScript("classpath:org/springframework/batch/core/schema-h2.sql") .addScript("classpath:employee.sql") .setType(EmbeddedDatabaseType.H2) .build(); }
Create EMPLOYEE table
Above configuration will generate default tables automatically. To generate EMPLOYEE
table, create schema file employee.sql
and put in resources
folder.
DROP TABLE EMPLOYEE IF EXISTS; CREATE TABLE EMPLOYEE ( ID VARCHAR(10), FIRSTNAME VARCHAR(100), LASTNAME VARCHAR(100) ) ;
Enable H2 Console
To enable H2 console, register org.h2.server.web.WebServlet
with with Spring web.
package com.howtodoinjava.demo.config; import org.h2.server.web.WebServlet; import org.springframework.boot.web.servlet.ServletRegistrationBean; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; @SuppressWarnings({"rawtypes","unchecked"}) @Configuration public class WebConfig { @Bean ServletRegistrationBean h2servletRegistration(){ ServletRegistrationBean registrationBean = new ServletRegistrationBean( new WebServlet()); registrationBean.addUrlMappings("/console/*"); return registrationBean; } }
Demo
Our application configuration is done and Job is ready to be executed. Let’s create input CSV file.
id,firstName,lastName 1,Lokesh,Gupta 2,Amit,Mishra 3,Pankaj,Kumar 4,David,Miller 5,David,Walsh
Run the demo
To run the demo and batch job, create Spring boot application class and start application.
package com.howtodoinjava.demo; 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.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.scheduling.annotation.EnableScheduling; import org.springframework.scheduling.annotation.Scheduled; @SpringBootApplication @EnableScheduling public class App { @Autowired JobLauncher jobLauncher; @Autowired Job job; public static void main(String[] args) { SpringApplication.run(App.class, args); } @Scheduled(cron = "0 */1 * * * ?") public void perform() throws Exception { JobParameters params = new JobParametersBuilder() .addString("JobID", String.valueOf(System.currentTimeMillis())) .toJobParameters(); jobLauncher.run(job, params); } }
Verify batch job results
To verify the successful batch job execution, check the logs and the H2 console.
2018-07-11 19:11:00 INFO - Job: [SimpleJob: [name=readCSVFileJob]] launched with the following parameters: [{JobID=1531316460004}] 2018-07-11 19:11:00 INFO - Executing step: [step] Inserting employee : Employee [id=1, firstName=Lokesh, lastName=Gupta] Inserting employee : Employee [id=2, firstName=Amit, lastName=Mishra] Inserting employee : Employee [id=3, firstName=Pankaj, lastName=Kumar] Inserting employee : Employee [id=4, firstName=David, lastName=Miller] Inserting employee : Employee [id=5, firstName=David, lastName=Walsh] 2018-07-11 19:11:00 INFO - Job: [SimpleJob: [name=readCSVFileJob]] completed with the following parameters: [{JobID=1531316460004}] and the following status: [COMPLETED]
H2 Console


Drop me your questions in comments section.
Happy Learning !!