Spring Batch Boot Tutorial - XML file to MySQL Database Example

Spring Batch Boot Tutorial - XML file to MySQL Database Example

in published on March 30, 2018

Overview

In this tutorial, we show you how to create a simple example using Spring Batch Boot to Read XML file and Write to MySQL Database. Configure a Spring Batch Boot using StaxEventItemReader for reading xml data, JdbcBatchItemWriter to write data to mysql database, with spring-oxm maven.



Spring-Boot-Batch-Tutorial-XML-file-to-MySQL-Database-Example


Follow the steps mentioned below to develop the Spring Batch Boot application. 

Watch Tutorial



Prerequisites

Create Database and Table
Execute the following MySQL script in order to create a database named springbatch with a table named user.
 
CREATE DATABASE `springbatch` /*!40100 DEFAULT CHARACTER SET utf8 */;

CREATE TABLE  `springbatch`.`user` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(45) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Create project directory structure

The following screenshot shows final structure of the project.

Project Structure Spring Batch XML to Database

Creating the Spring Batch Boot Project

Launch Eclipse IDE. Go to File -> New -> Other... Select Spring Starter Project under Spring Boot category then click Next as shown below


Spring Starter Project

In the next screen, you enter the content as shown below then click Next

new spring starter project
 In the next step, you choose Spring Boot Version is 1.5.10 and choose the Batch dependencies I/O -> Batch, then click Finish


new spring starter project dependencies

Update pom.xml file

Add the following to your project's pom.xml 
<dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
 <version>5.1.46</version>
</dependency>

<dependency>
 <groupId>org.springframework</groupId>
 <artifactId>spring-oxm</artifactId>
 <version>4.3.0.RELEASE</version>
</dependency>

Following is the updated pom.xml file

<?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>

 <groupId>com.jackrutorial</groupId>
 <artifactId>SpringBatchXmlToDatabase</artifactId>
 <version>0.0.1-SNAPSHOT</version>
 <packaging>jar</packaging>

 <name>SpringBatchXmlToDatabase</name>
 <description>Demo project for Spring Boot</description>

 <parent>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-parent</artifactId>
  <version>1.5.10.RELEASE</version>
  <relativePath /> <!-- lookup parent from repository -->
 </parent>

 <properties>
  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
  <java.version>1.8</java.version>
 </properties>

 <dependencies>
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-batch</artifactId>
  </dependency>

  <dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>5.1.46</version>
  </dependency>

  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-oxm</artifactId>
   <version>4.3.0.RELEASE</version>
  </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>

Create the XML file

We create the user.xml file in src/main/resources folder. This is the input xml whose data we will be saving in MySQL database.

<?xml version="1.0" encoding="UTF-8"?>
<users>
 <user>
  <id>1</id>
  <name>Jack Rutorial demo 1</name>
 </user>
 <user>
  <id>2</id>
  <name>Jack Rutorial demo 2</name>
 </user>
 <user>
  <id>3</id>
  <name>Jack Rutorial demo 3</name>
 </user>
</users>

Model

Create a User class under com.jackrutorial.model package and write the following code in it.

package com.jackrutorial.model;

public class User {
 private Integer id;
 private String name;
 
 public Integer getId() {
  return id;
 }
 public void setId(Integer id) {
  this.id = id;
 }
 public String getName() {
  return name;
 }
 public void setName(String name) {
  this.name = name;
 }
}

Spring Batch Boot Configuration

Create a ""importUserJob"" Job which will read from user.xml file and write into MySQL Database. Create a BatchConfiguration class under com.jackrutorial package and write the following code in it.

package com.jackrutorial;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

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.database.ItemPreparedStatementSetter;
import org.springframework.batch.item.database.JdbcBatchItemWriter;
import org.springframework.batch.item.xml.StaxEventItemReader;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.oxm.xstream.XStreamMarshaller;
import com.jackrutorial.model.User;


@Configuration
@EnableBatchProcessing
public class BatchConfiguration {
 
 @Autowired
 public JobBuilderFactory jobBuilderFactory;
 
 @Autowired
 public StepBuilderFactory stepBuilderFactory;
 
 @Autowired
 public DataSource dataSource;
 
 @Bean
 public DataSource dataSource() {
  final DriverManagerDataSource dataSource = new DriverManagerDataSource();
  dataSource.setDriverClassName("com.mysql.jdbc.Driver");
  dataSource.setUrl("jdbc:mysql://localhost/springbatch");
  dataSource.setUsername("root");
  dataSource.setPassword("root");
  
  return dataSource;
 }
 
 @Bean
 public StaxEventItemReader<User> reader(){
  StaxEventItemReader<User> reader = new StaxEventItemReader<User>();
  reader.setResource(new ClassPathResource("user.xml"));
  reader.setFragmentRootElementName("user");
  
  Map<String, String> aliases = new HashMap<String, String>();
  aliases.put("user", "com.jackrutorial.model.User");
  
  XStreamMarshaller xStreamMarshaller = new XStreamMarshaller();
  xStreamMarshaller.setAliases(aliases);
  
  reader.setUnmarshaller(xStreamMarshaller);
  
  return reader;
 }
 
 @Bean
 public JdbcBatchItemWriter<User> writer(){
  JdbcBatchItemWriter<User> writer = new JdbcBatchItemWriter<User>();
  writer.setDataSource(dataSource);
  writer.setSql("insert into user(id,name) values(?, ?)");
  writer.setItemPreparedStatementSetter(new UserItemPreparedStmSetter());
  
  return writer;
 }
 
 private class UserItemPreparedStmSetter implements ItemPreparedStatementSetter<User>{

  @Override
  public void setValues(User user, PreparedStatement ps) throws SQLException {
   ps.setInt(1, user.getId());
   ps.setString(2, user.getName());
  }
  
 }
 
 @Bean
 public Step step1() {
  return stepBuilderFactory.get("step1")
    .<User, User> chunk(10)
    .reader(reader())
    .writer(writer())
    .build();
 }
 
 @Bean
 public Job importUserJob() {
  return jobBuilderFactory.get("importUserJob")
    .incrementer(new RunIdIncrementer())
    .flow(step1())
    .end()
    .build();
    
 }
}

In this example, we have setup a "importUserJob" Job and "step1" Step. Step uses StaxEventItemReader to read the records and JdbcBatchItemWriter to write the records in Mysql database.

Run Spring Batch Boot

Right click to the SpringBatchXmlToDatabaseApplication class, select Run As -> Java Application. This application will read data from user.xml file and write into user table in springbatch Database.
You will see following output:

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::       (v1.5.10.RELEASE)

2018-03-30 22:23:46.191  INFO 39396 --- [           main] o.s.b.c.l.support.SimpleJobLauncher      : Job: [FlowJob: [name=importUserJob]] launched with the following parameters: [{run.id=1}]
2018-03-30 22:23:46.257  INFO 39396 --- [           main] o.s.batch.core.job.SimpleStepHandler     : Executing step: [step1]
2018-03-30 22:23:46.385  INFO 39396 --- [           main] o.s.b.c.l.support.SimpleJobLauncher      : Job: [FlowJob: [name=importUserJob]] completed with the following parameters: [{run.id=1}] and the following status: [COMPLETED]
2018-03-30 22:23:46.389  INFO 39396 --- [           main] c.j.SpringBatchXmlToDatabaseApplication  : Started SpringBatchXmlToDatabaseApplication in 2.752 seconds (JVM running for 3.332)
2018-03-30 22:23:46.390  INFO 39396 --- [       Thread-2] s.c.a.AnnotationConfigApplicationContext : Closing org.springframework.context.annotation.AnnotationConfigApplicationContext@4ae82894: startup date [Fri Mar 30 22:23:44 ICT 2018]; root of context hierarchy
2018-03-30 22:23:46.413  INFO 39396 --- [       Thread-2] o.s.j.e.a.AnnotationMBeanExporter        : Unregistering JMX-exposed beans on shutdown


Previous Post
Next Post

post written by: