Spring Batch - read from CSV file and write to DB
In this example, we will show you read from the CSV file and write into the DB. We've used FlatFileItemReader to read from the CSV file and JdbcBatchItemWriter to write into the MySQL DB. (You can use DB as per your choice)
Ref: Always refer: https://docs.spring.io/spring-batch/4.0.x/reference/html/index.html for changes
Technology used:
1) Spring Boot 2.1.1.RELEASE
2) Spring Batch
3) Spring JDBC
4) Spring OXM
5) Lombok
6) mysql-connector
Create Customer table:
CREATE TABLE `test`.`customer` (
`id` MEDIUMINT(8) unsigned NOT NULL,
`firstName` VARCHAR(255) default NULL,
`lastName` VARCHAR(255) default NULL,
`birthdate` DATE NULL,
PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;
Customer.java
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Data
public class Customer {
private Long id;
private String firstName;
private String lastName;
private Date birthdate;
}
application.properties
# MYSQL DB Details
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.schema=org/springframework/batch/core/schema-mysql.sql
spring.batch.initialize-schema=ALWAYS
CustomerFieldSetMapper.java
public class CustomerFieldSetMapper implements FieldSetMapper<Customer> {
@Override
public Customer mapFieldSet(FieldSet fieldSet) throws BindException {
return Customer.builder()
.id(fieldSet.readLong("id"))
.firstName(fieldSet.readRawString("firstName"))
.lastName(fieldSet.readRawString("lastName"))
.birthdate(fieldSet.readDate("birthdate", "dd-MM-yyyy HH:mm:ss"))
.build();
}
}
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 http://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.1.1.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>databaseOutput</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>databaseOutput</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</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-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.2</version>
<optional>true</optional>
</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>
Data loaded into DB
In this example, we will show you read from the CSV file and write into the DB. We've used FlatFileItemReader to read from the CSV file and JdbcBatchItemWriter to write into the MySQL DB. (You can use DB as per your choice)
Ref: Always refer: https://docs.spring.io/spring-batch/4.0.x/reference/html/index.html for changes
Technology used:
1) Spring Boot 2.1.1.RELEASE
2) Spring Batch
3) Spring JDBC
4) Spring OXM
5) Lombok
6) mysql-connector
Create Customer table:
CREATE TABLE `test`.`customer` (
`id` MEDIUMINT(8) unsigned NOT NULL,
`firstName` VARCHAR(255) default NULL,
`lastName` VARCHAR(255) default NULL,
`birthdate` DATE NULL,
PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;
Customer.java
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Data
public class Customer {
private Long id;
private String firstName;
private String lastName;
private Date birthdate;
}
application.properties
# MYSQL DB Details
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.schema=org/springframework/batch/core/schema-mysql.sql
spring.batch.initialize-schema=ALWAYS
CustomerFieldSetMapper.java
public class CustomerFieldSetMapper implements FieldSetMapper<Customer> {
@Override
public Customer mapFieldSet(FieldSet fieldSet) throws BindException {
return Customer.builder()
.id(fieldSet.readLong("id"))
.firstName(fieldSet.readRawString("firstName"))
.lastName(fieldSet.readRawString("lastName"))
.birthdate(fieldSet.readDate("birthdate", "dd-MM-yyyy HH:mm:ss"))
.build();
}
}
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 http://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.1.1.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>databaseOutput</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>databaseOutput</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</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-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.2</version>
<optional>true</optional>
</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>
customer.csv
id,firstName,lastName,birthdate
1,John,Doe,10-10-1952 10:10:10
2,Amy,Eugene,05-07-1985 17:10:00
3,Laverne,Mann,11-12-1988 10:10:10
4,Janice,Preston,19-02-1960 10:10:10
5,Pauline,Rios,29-08-1977 10:10:10
6,Perry,Burnside,10-03-1981 10:10:10
7,Todd,Kinsey,14-12-1998 10:10:10
8,Jacqueline,Hyde,20-03-1983 10:10:10
9,Rico,Hale,10-10-2000 10:10:10
10,Samuel,Lamm,11-11-1999 10:10:10
11,Robert,Coster,10-10-1972 10:10:10
12,Tamara,Soler,02-01-1978 10:10:10
13,Justin,Kramer,19-11-1951 10:10:10
14,Andrea,Law,14-10-1959 10:10:10
15,Laura,Porter,12-12-2010 10:10:10
16,Michael,Cantu,11-04-1999 10:10:10
17,Andrew,Thomas,04-05-1967 10:10:10
18,Jose,Hannah,16-09-1950 10:10:10
19,Valerie,Hilbert,13-06-1966 10:10:10
20,Patrick,Durham,12-10-1978 10:10:10
JobConfig.java
@Configuration
public class JobConfig {
@Autowired
private StepBuilderFactory stepBuilderFactory;
@Autowired
private JobBuilderFactory jobBuilderFactory;
@Autowired
private DataSource dataSource;
@Bean
public FlatFileItemReader<Customer> customerItemReader(){
FlatFileItemReader<Customer> reader = new FlatFileItemReader<>();
reader.setLinesToSkip(1);
reader.setResource(new ClassPathResource("/data/customer.csv"));
DelimitedLineTokenizer tokenizer = new DelimitedLineTokenizer();
tokenizer.setNames(new String[] {"id", "firstName", "lastName", "birthdate"});
DefaultLineMapper<Customer> customerLineMapper = new DefaultLineMapper<>();
customerLineMapper.setLineTokenizer(tokenizer);
customerLineMapper.setFieldSetMapper(new CustomerFieldSetMapper());
customerLineMapper.afterPropertiesSet();
reader.setLineMapper(customerLineMapper);
return reader;
}
@Bean
public JdbcBatchItemWriter<Customer> customerItemWriter(){
JdbcBatchItemWriter<Customer> writer = new JdbcBatchItemWriter<>();
writer.setDataSource(this.dataSource);
writer.setSql("INSERT INTO CUSTOMER VALUES (:id, :firstName, :lastName, :birthdate)");
writer.setItemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider<>());
writer.afterPropertiesSet();
return writer;
}
@Bean
public Step step1() {
return stepBuilderFactory.get("step1")
.<Customer, Customer> chunk(10)
.reader(customerItemReader())
.writer(customerItemWriter())
.build();
}
@Bean
public Job job() {
return jobBuilderFactory.get("job")
.start(step1())
.build();
}
}
DatabaseOutputApplication.java
@SpringBootApplication
@EnableBatchProcessing
public class DatabaseOutputApplication implements CommandLineRunner{
@Autowired
private JobLauncher jobLauncher;
@Autowired
private Job job;
public static void main(String[] args) {
SpringApplication.run(DatabaseOutputApplication.class, args);
}
@Override
public void run(String... args) throws Exception {
JobParameters jobParameters = new JobParametersBuilder()
.addDate("date", new Date())
.addLong("JobId",System.currentTimeMillis())
.addLong("time",System.currentTimeMillis()).toJobParameters();
JobExecution execution = jobLauncher.run(job, jobParameters);
System.out.println("STATUS :: "+execution.getStatus());
}
}
Data loaded into DB
Logs:
. ____ _ __ _ _
/\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
\\/ ___)| |_)| | | | | || (_| | ) ) ) )
' |____| .__|_| |_|_| |_\__, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot :: (v2.1.1.RELEASE)
2018-12-19 22:00:02.841 INFO 8132 --- [ main] com.example.DatabaseOutputApplication : Starting DatabaseOutputApplication on DESKTOP-NQ639DU with PID 8132 (F:\spring-boot-spring-batch-master\Spring-Batch-by-Michael-Minella\databaseOutput\target\classes started by pc in F:\spring-boot-spring-batch-master\Spring-Batch-by-Michael-Minella\databaseOutput)
2018-12-19 22:00:02.845 INFO 8132 --- [ main] com.example.DatabaseOutputApplication : No active profile set, falling back to default profiles: default
2018-12-19 22:00:03.834 INFO 8132 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2018-12-19 22:00:04.062 INFO 8132 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
2018-12-19 22:00:04.446 INFO 8132 --- [ main] o.s.b.c.r.s.JobRepositoryFactoryBean : No database type set, using meta data indicating: MYSQL
2018-12-19 22:00:04.650 INFO 8132 --- [ main] o.s.b.c.l.support.SimpleJobLauncher : No TaskExecutor has been set, defaulting to synchronous executor.
2018-12-19 22:00:04.862 INFO 8132 --- [ main] com.example.DatabaseOutputApplication : Started DatabaseOutputApplication in 2.403 seconds (JVM running for 2.883)
2018-12-19 22:00:05.164 INFO 8132 --- [ main] o.s.b.c.l.support.SimpleJobLauncher : Job: [SimpleJob: [name=job]] launched with the following parameters: [{date=1545237004864, JobId=1545237004865, time=1545237004865}]
2018-12-19 22:00:05.330 INFO 8132 --- [ main] o.s.batch.core.job.SimpleStepHandler : Executing step: [step1]
2018-12-19 22:00:05.759 INFO 8132 --- [ main] o.s.b.c.l.support.SimpleJobLauncher : Job: [SimpleJob: [name=job]] completed with the following parameters: [{date=1545237004864, JobId=1545237004865, time=1545237004865}] and the following status: [COMPLETED]
STATUS :: COMPLETED
2018-12-19 22:00:05.775 INFO 8132 --- [ Thread-2] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated...
2018-12-19 22:00:05.784 INFO 8132 --- [ Thread-2] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed.
Comments
Post a Comment