Today, I show you how to create a Spring Batch Scheduler CSV to Mysql Example. It includes a job scheduler that is triggered periodically with a cron expression. This job scheduler will read data from a CSV file then insert data into Mysql Database. It is an interesting example, isn’t it?. Let’s dive into it.
Spring Batch Scheduler CSV to Mysql Example
Project Setup
Tools and frameworks that we use:
Spring Boot 2.2.2 RELEASE
Spring Batch 4.2.1
Maven 3.6
Java 8
Mysql 5.7
Project Directory
Our project will have a structure like below
Project Dependencies
We’re using the dependencies like below
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
... <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.2.2.RELEASE</version> </parent> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <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-web</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> </dependencies> ... |
DataSource
It’s really easy to configure data source with spring boot
|
1 2 3 4 5 6 7 |
spring.jpa.generate-ddl=false spring.jpa.hibernate.ddl-auto=none spring.datasource.url=jdbc:mysql://localhost:3306/demo?useSSL=true spring.datasource.username=root spring.datasource.password=root spring.datasource.initialization-mode=always spring.batch.initialize-schema=ALWAYS |
Creating the Model
Here, we assume that we need to read employee information from CSV file named employees.csv, its format looks like below:
|
1 2 3 |
David, Pham, Java James, Bon, PHP Tom, Hank, Objective-C |
This file is located in the project folder resources /csv/employees.csv
Then, we create an Entity class that its object map with data in the above CSV file
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
package com.javabycode.model; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.Table; @Entity @Table(name = "employee") public class Employee { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) Integer id; String firstName; String lastName; String department; } |
Configuring Spring Batch Jobs
We assume that we have a data CSV file that contains a list of employees, its format looks like below.
Create BatchConfig class and add the @EnableBatchProcessing annotation for this class to get the support of Spring Batch features.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 |
package com.javabycode.batch; 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 com.javabycode.model.Employee; @Configuration @EnableBatchProcessing public class BatchJobConfig { @Autowired private JobBuilderFactory jobBuilder; @Autowired private StepBuilderFactory stepBuilder; @Value("classPath:/csv/employees.csv") private Resource csvResource; @Autowired private DataSource dataSource; @Bean public Job readCSVFile() { return jobBuilder .get("readCSVFile") .incrementer(new RunIdIncrementer()) .start(step()) .build(); } @Bean public Step step() { return stepBuilder .get("step") .<Employee, Employee>chunk(5) .reader(reader()) .processor(processor()) .writer(writer()) .build(); } @Bean public ItemProcessor<Employee, Employee> processor() { return new EmployeeProcessor(); } // reading from csv file @Bean public FlatFileItemReader<Employee> reader() { FlatFileItemReader<Employee> itemReader = new FlatFileItemReader<>(); itemReader.setLineMapper(lineMapper()); itemReader.setLinesToSkip(1); itemReader.setResource(csvResource); return itemReader; } //convert csv rows to beans @Bean public LineMapper<Employee> lineMapper() { DefaultLineMapper<Employee> lineMapper = new DefaultLineMapper<>(); DelimitedLineTokenizer lineTokenizer = new DelimitedLineTokenizer(); lineTokenizer.setNames(new String[] { "firstName", "lastName", "department"}); 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; } // writting into mysql database @Bean public JdbcBatchItemWriter<Employee> writer() { JdbcBatchItemWriter<Employee> itemWriter = new JdbcBatchItemWriter<>(); itemWriter.setDataSource(dataSource); itemWriter.setSql("INSERT INTO EMPLOYEE (FIRSTNAME, LASTNAME, DEPARTMENT) VALUES (:firstName, :lastName, :department)"); itemWriter.setItemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider<Employee>()); return itemWriter; } } |
Processing the Data
In this post, we only create a simple ItemProcessor below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
package com.javabycode.batch; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.batch.item.ItemProcessor; import com.javabycode.model.Employee; public class EmployeeProcessor implements ItemProcessor<Employee, Employee>{ private static final Logger LOGGER = LoggerFactory.getLogger(EmployeeProcessor.class); public Employee process(Employee employee) throws Exception{ LOGGER.info("Inserting employee '{}'", employee); return employee; } } |
Configuring SpringBootApplication class
To configure Spring Boot application we create a simple class below. If you haven’t had knowledge about Spring Boot yet. Let’s check out the post Spring Boot Tutorial for Beginners
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
package com.javabycode; 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 MySpringBatchApplication { @Autowired JobLauncher jobLauncher; @Autowired Job job; public static void main(String[] args) { SpringApplication.run(MySpringBatchApplication.class, args); } @Scheduled(cron = "0/10 * * * * ?") public void perform() throws Exception { JobParameters params = new JobParametersBuilder() .addString("JobID", String.valueOf(System.currentTimeMillis())) .toJobParameters(); jobLauncher.run(job, params); } } |
Dig deeper: Above, we implement the job scheduler with cron pattern */10 * * * * *? . That means it runs every ten seconds. If you want to know more cron pattern, check out the post Spring Batch Scheduler with Spring Boot Example.
Running Application
To demo our Spring Batch Scheduler CSV to Mysql Example, we run the below command in the console.
|
1 |
mvn spring-boot:run |
Note: You must be in your project directory before run that command.
While running that command, you will see the output like below
Now, we have a look into Mysql database
That’s all about Spring Batch Scheduler CSV to Mysql Example.
If you find this post useful, don't hesitate to share it with your friends or other people for growing knowledge together and is an effort to contribute us.
References
Spring Boot Tutorial for Beginners
Spring Batch Frameworks
Spring Batch Scheduler with Spring Boot Example
