Home
Spring Batch Example MySQL Database To XML
February 27, 2014
No Comments

Spring Batch Example MySQL Database To XML

A typical batch program generally reads a large number of records from a database, file, or queue, processes the data in some fashion, and then writes back data in a modified form to database,file-system, mailer etc.

Spring Batch automates this basic batch iteration, providing the capability to process similar transactions as a set, typically in an offline environment without any user interaction.

In this tutorial, we will show you how to read data from a MySQL database, with JdbcCursorItemReader and JdbcPagingItemReader, and write it into an XML file.

Tools and libraries used

  • Spring Tool Suite (STS)
  • JDK 1.6
  • Spring Core 3.2.2.RELEASE
  • Spring OXM 3.2.2.RELEASE
  • Spring Batch 2.2.0.RELEASE
  • MySQL Java Driver 5.1.25

Work flow of this Example-How it works?

Spring Batch works like read data in some chunk size[configurable] from data source, and write that chunk to some resource. Here data source for reader could be flat files[text file, xml file, csv file etc], relational database[e.g. mysql], mongodb. Similarly writer could write data read by reader to flat files, relation database, mongodb, mailer etc.

Reading, processing, writing all together is termed as Job.

Sample Example of Spring Batch MySQL Database To XML-

1. Project Directory Structure

Spring Batch Example MySQL Database To XML

2. Database
Employee table contain some data in DAVDB database.

CREATE TABLE `employeet` (
  `empid` int(11) NOT NULL AUTO_INCREMENT,
  `empaddress` varchar(255) DEFAULT NULL,
  `empAge` int(11) DEFAULT NULL,
  `empname` varchar(255) DEFAULT NULL,
  `salary` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`empid`)
) 

//Insert data from following query

INSERT INTO `DAVDB`.`employeet` 
 (`empid`, 
 `empaddress`, 
 `empAge`, 
 `empname`, 
 `salary`
 )
 VALUES
 ('empid', 
 'empaddress', 
 'empAge', 
 'empname', 
 'salary'
 );

3. Item Reader

Create a row mapper to map database values to “Employee” object.
Employee.java

package com.doj.batch.bean;

import javax.xml.bind.annotation.XmlAccessOrder;
import javax.xml.bind.annotation.XmlAccessorOrder;
import javax.xml.bind.annotation.XmlRootElement;

/**
 * @author Dinesh Rajput
 *
 */
@XmlRootElement(name="employee")
@XmlAccessorOrder(XmlAccessOrder.UNDEFINED)
public class Employee {
 private int empid;
 private String name;
 private int age;
 private float salary;
 private String address;
 /**
  * @return the empid
  */
 public int getEmpid() {
  return empid;
 }
 /**
  * @param empid the empid to set
  */
 public void setEmpid(int empid) {
  this.empid = empid;
 }
 /**
  * @return the name
  */
 public String getName() {
  return name;
 }
 /**
  * @param name the name to set
  */
 public void setName(String name) {
  this.name = name;
 }
 /**
  * @return the age
  */
 public int getAge() {
  return age;
 }
 /**
  * @param age the age to set
  */
 public void setAge(int age) {
  this.age = age;
 }
 /**
  * @return the salary
  */
 public float getSalary() {
  return salary;
 }
 /**
  * @param salary the salary to set
  */
 public void setSalary(float salary) {
  this.salary = salary;
 }
 /**
  * @return the address
  */
 public String getAddress() {
  return address;
 }
 /**
  * @param address the address to set
  */
 public void setAddress(String address) {
  this.address = address;
 }
}

EmployeeRowMapper.java

package com.doj.batch.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

import com.doj.batch.bean.Employee;

/**
 * @author Dinesh Rajput
 *
 */
public class EmployeeRowMapper implements RowMapper<Employee>{

 @Override
 public Employee mapRow(ResultSet resultSet, int rowNum) throws SQLException {
  Employee employee = new Employee();
  employee.setEmpid(resultSet.getInt("empid"));
  employee.setName(resultSet.getString("empname"));
  employee.setSalary(resultSet.getLong("salary"));
  employee.setAddress(resultSet.getString("empaddress"));
  employee.setAge(resultSet.getInt("empAge"));
  return employee;
 }

}

Example to read data from database.

<bean id="itemReader" class="org.springframework.batch.item.database.JdbcCursorItemReader" scope="step">
  <property name="dataSource" ref="dataSource"/>
  <property name="sql" value="select empid, empname, empAge, empaddress, salary from employeet" />
  <property name="rowMapper">
   <bean class="com.doj.batch.mapper.EmployeeRowMapper" />
  </property>
   </bean>

4. Item Writer
Write data to an XML file.

<bean id="itemWriter" class="org.springframework.batch.item.xml.StaxEventItemWriter">
  <property name="resource" value="file:xml/outputs/employees.xml" />
  <property name="marshaller" ref="empMarshaller" />
  <property name="rootTagName" value="employees" />
  </bean> 
    
    <bean id="empMarshaller" class="org.springframework.oxm.jaxb.Jaxb2Marshaller">
  <property name="classesToBeBound">
   <value>com.doj.batch.bean.Employee</value>
  </property>
   </bean>

5. Spring Batch Jobs
A job to read data from MySQL and write it XML file.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
 xmlns:context="http://www.springframework.org/schema/context"
 xmlns:p="http://www.springframework.org/schema/p" 
 xmlns:batch="http://www.springframework.org/schema/batch"
 xmlns:mvc="http://www.springframework.org/schema/mvc" 
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-4.0.xsd
http://www.springframework.org/schema/mvc
http://www.springframework.org/schema/mvc/spring-mvc-4.0.xsd
http://www.springframework.org/schema/batch 
http://www.springframework.org/schema/batch/spring-batch-2.0.xsd">

 <import resource="applicationContext.xml"/>
 <import resource="ApplicationDB.xml"/>
 
 <bean id="itemReader" class="org.springframework.batch.item.database.JdbcCursorItemReader" scope="step">
  <property name="dataSource" ref="dataSource"/>
  <property name="sql" value="select empid, empname, empAge, empaddress, salary from employeet" />
  <property name="rowMapper">
   <bean class="com.doj.batch.mapper.EmployeeRowMapper" />
  </property>
   </bean>
 
    <bean id="itemWriter" class="org.springframework.batch.item.xml.StaxEventItemWriter">
  <property name="resource" value="file:xml/outputs/employees.xml" />
  <property name="marshaller" ref="empMarshaller" />
  <property name="rootTagName" value="employees" />
  </bean> 
    
    <bean id="empMarshaller" class="org.springframework.oxm.jaxb.Jaxb2Marshaller">
  <property name="classesToBeBound">
   <value>com.doj.batch.bean.Employee</value>
  </property>
   </bean> 
  
    <batch:job id="simpleDojJob" job-repository="jobRepository" parent="simpleJob">
     <batch:step id="step1">
      <batch:tasklet transaction-manager="transactionManager">
       <batch:chunk reader="itemReader" writer="itemWriter" commit-interval="1"/>
      </batch:tasklet>
     </batch:step>
    </batch:job>   
</beans>

ApplicationDB.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
 xmlns:jdbc="http://www.springframework.org/schema/jdbc" 
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://www.springframework.org/schema/beans 
  http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
  http://www.springframework.org/schema/jdbc 
  http://www.springframework.org/schema/jdbc/spring-jdbc-4.0.xsd">
 
        <!-- connect to database -->
 <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
  <property name="driverClassName" value="com.mysql.jdbc.Driver" />
  <property name="url" value="jdbc:mysql://localhost:3306/DAVDB" />
  <property name="username" value="root" />
  <property name="password" value="root" />
 </bean>
 
 <bean id="transactionManager" class="org.springframework.batch.support.transaction.ResourcelessTransactionManager" />
 
</beans>

applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
 xmlns:context="http://www.springframework.org/schema/context"
 xmlns:p="http://www.springframework.org/schema/p" 
 xmlns:mvc="http://www.springframework.org/schema/mvc" 
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-4.0.xsd
http://www.springframework.org/schema/mvc
http://www.springframework.org/schema/mvc/spring-mvc-4.0.xsd">

 <bean id="transactionManager" class="org.springframework.batch.support.transaction.ResourcelessTransactionManager"/>
 
    <bean id="jobLauncher" class="org.springframework.batch.core.launch.support.SimpleJobLauncher">
        <property name="jobRepository" ref="jobRepository"/>
    </bean>
 
    <bean id="jobRepository" class="org.springframework.batch.core.repository.support.MapJobRepositoryFactoryBean">
        <property name="transactionManager" ref="transactionManager"/>
    </bean>
 
    <bean id="simpleJob" class="org.springframework.batch.core.job.SimpleJob" abstract="true">
        <property name="jobRepository" ref="jobRepository" />
    </bean>
 
</beans>

6. Launching Batch Job-

Spring Batch comes with a simple utility class called CommandLineJobRunner which has a main() method which accepts two arguments. First argument is the spring application context file containing job definition and the second is the name of the job to be executed.

Now run as a java application with both two arguments.
org.springframework.batch.core.launch.support.CommandLineJobRunner
simple-job.xml simpleDojJob

Output. Extracts all employees into an XML file.
employees.xml

<?xml version="1.0" encoding="UTF-8"?>
<employees>
 <employee>
  <address>delhi</address>
  <age>17</age>
  <empid>1</empid>
  <name>ATUL KUMAR</name>
  <salary>300000.0</salary>
 </employee>
 <employee>
  <address>delhi</address>
  <age>21</age>
  <empid>2</empid>
  <name>ASHUTOSH RAJPUT</name>
  <salary>300000.0</salary>
 </employee>
 <employee>
  <address>delhi</address>
  <age>17</age>
  <empid>3</empid>
  <name>Dinesh Rajput</name>
  <salary>300000.0</salary>
 </employee>
</employees>

Download Source Code with libs
SpringBatchDatabaseToXml.zip

 

Previous
Next
Spring Batch Spring Batch3 Spring Framework

Related Posts

About The Author

Dinesh Rajput

Dinesh Rajput is the chief editor of a website Dineshonjava, a technical blog dedicated to the Spring and Java technologies. It has a series of articles related to Java technologies. Dinesh has been a Spring enthusiast since 2008 and is a Pivotal Certified Spring Professional, an author of a book Spring 5 Design Pattern, and a blogger. He has more than 10 years of experience with different aspects of Spring and Java design and development. His core expertise lies in the latest version of Spring Framework, Spring Boot, Spring Security, creating REST APIs, Microservice Architecture, Reactive Pattern, Spring AOP, Design Patterns, Struts, Hibernate, Web Services, Spring Batch, Cassandra, MongoDB, and Web Application Design and Architecture. He is currently working as a technology manager at a leading product and web development company. He worked as a developer and tech lead at the Bennett, Coleman & Co. Ltd and was the first developer in his previous company, Paytm. Dinesh is passionate about the latest Java technologies and loves to write technical blogs related to it. He is a very active member of the Java and Spring community on different forums. When it comes to the Spring Framework and Java, Dinesh tops the list!