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
H2database and createEMPLOYEEtable in it - Write employee records to
EMPLOYEEtable withJdbcBatchItemWriter - Log items inserted to database using
ItemProcessor - Verify inserted records using
H2console
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 http://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
FlatFileItemReaderfor reading the CSV file. we will use it’s standard configuration involvingDefaultLineMapper,DelimitedLineTokenizerandBeanWrapperFieldSetMapperclasses. - For writing the records in DB, we will use
JdbcBatchItemWriterwhich 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 !!
Was this post helpful?
Let us know if you liked the post. That’s the only way we can improve.
followed the same example but the records are not being sent to the h2 database. is there anything that is missing
Eshwar, check if you are setting the correct JDBC URL.