Spring Batch Example – MySQL Database To XML

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

Maven 3
Eclipse 4.2
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

P.S This example – MySQL jdbc (reader) – XML (writer).

1. Project Directory Structure

Review the final project structure, a standard Maven project.

2. Database

A “users” table, contains 5 records only, later read it with jdbc.

users table


id, user_login, password, age

'1','mkyong','password','30'
'2','user_a','password','25'
'3','user_b','password','10'
'4','user_c','password','25'
'5','user_d','password','40'

3. Item Reader

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

User.java


package com.mkyong;

public class User {

	int id;
	String username;
	String password;
	int age;

	//... getter and setter methods

}

UserRowMapper.java


package com.mkyong;

import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;

public class UserRowMapper implements RowMapper<User> {

	@Override
	public User mapRow(ResultSet rs, int rowNum) throws SQLException {

		User user = new User();

		user.setId(rs.getInt("id"));
		user.setUsername(rs.getString("user_login"));
		user.setPassword(rs.getString("user_pass"));
		user.setAge(rs.getInt("age"));

		return user;
	}

}

Example to read data from database.

job.xml


  <bean id="itemReader"
	class="org.springframework.batch.item.database.JdbcCursorItemReader"
	scope="step">
	<property name="dataSource" ref="dataSource" />
	<property name="sql"
		value="select ID, USER_LOGIN, USER_PASS, AGE from USERS" />
	<property name="rowMapper">
		<bean class="com.mkyong.UserRowMapper" />
	</property>
  </bean>

For big records, you can use JdbcPagingItemReader.

job.xml


  <bean id="pagingItemReader"
	class="org.springframework.batch.item.database.JdbcPagingItemReader"
	scope="step">
	<property name="dataSource" ref="dataSource" />
	<property name="queryProvider">
	  <bean
		class="org.springframework.batch.item.database.support.SqlPagingQueryProviderFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="selectClause" value="select id, user_login, user_pass, age" />
		<property name="fromClause" value="from users" />
		<property name="whereClause" value="where user_login=:name" />
		<property name="sortKey" value="id" />
	  </bean>
	</property>
	<property name="parameterValues">
	   <map>
		<entry key="name" value="#{jobParameters['name']}" />
	   </map>
	</property>
	<property name="pageSize" value="10" />
	<property name="rowMapper">
		<bean class="com.mkyong.UserRowMapper" />
	</property>
  </bean>

4. Item Writer

Write data to an XML file.

job.xml


	<bean id="itemWriter" 
                class="org.springframework.batch.item.xml.StaxEventItemWriter">
		<property name="resource" value="file:xml/outputs/users.xml" />
		<property name="marshaller" ref="userUnmarshaller" />
		<property name="rootTagName" value="users" />
	</bean>
	
	<bean id="userUnmarshaller" 
                class="org.springframework.oxm.xstream.XStreamMarshaller">
		<property name="aliases">
			<util:map id="aliases">
				<entry key="user" value="com.mkyong.User" />
			</util:map>
		</property>
	</bean>

5. Spring Batch Jobs

A job to read data from MySQL and write it XML file.

resources/spring/batch/jobs/job-extract-users.xml


<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:batch="http://www.springframework.org/schema/batch" 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/batch 
		http://www.springframework.org/schema/batch/spring-batch-2.2.xsd
		http://www.springframework.org/schema/beans 
		http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
		">
        
  <import resource="../config/context.xml" />
  <import resource="../config/database.xml" />

  <bean id="itemReader"
	class="org.springframework.batch.item.database.JdbcCursorItemReader"
	scope="step">
	<property name="dataSource" ref="dataSource" />
	<property name="sql"
		value="select ID, USER_LOGIN, USER_PASS, AGE from USERS where age > #{jobParameters['age']}" />
	<property name="rowMapper">
		<bean class="com.mkyong.UserRowMapper" />
	</property>
  </bean>

  <job id="testJob" xmlns="http://www.springframework.org/schema/batch">
	<step id="step1">
	  <tasklet>
		<chunk reader="pagingItemReader" writer="itemWriter"
			commit-interval="1" />
	  </tasklet>
	</step>
  </job>

  <bean id="itemWriter" class="org.springframework.batch.item.xml.StaxEventItemWriter">
	<property name="resource" value="file:xml/outputs/users.xml" />
	<property name="marshaller" ref="userUnmarshaller" />
	<property name="rootTagName" value="users" />
  </bean>
	
  <bean id="userUnmarshaller" class="org.springframework.oxm.xstream.XStreamMarshaller">
	<property name="aliases">
		<util:map id="aliases">
			<entry key="user" value="com.mkyong.User" />
		</util:map>
	</property>
  </bean>	
</beans>

resources/spring/batch/config/database.xml


<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-3.2.xsd
		http://www.springframework.org/schema/jdbc 
		http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.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/test" />
		<property name="username" value="root" />
		<property name="password" value="" />
	</bean>

	<bean id="transactionManager"
	class="org.springframework.batch.support.transaction.ResourcelessTransactionManager" />
	
</beans>

6. Run It

Create a Java class and run the batch job.

App.java


package com.mkyong;

import org.springframework.batch.core.Job;
import org.springframework.batch.core.JobExecution;
import org.springframework.batch.core.JobParameters;
import org.springframework.batch.core.JobParametersBuilder;
import org.springframework.batch.core.launch.JobLauncher;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class App {

  public static void main(String[] args) {
	App obj = new App();
	obj.run();
  }

  private void run() {

	String[] springConfig = { "spring/batch/jobs/job-extract-users.xml" };

	ApplicationContext context = new ClassPathXmlApplicationContext(springConfig);

	JobLauncher jobLauncher = (JobLauncher) context.getBean("jobLauncher");
	Job job = (Job) context.getBean("testJob");

	try {

		JobParameters param = new JobParametersBuilder().addString("age", "20").toJobParameters();
			
		JobExecution execution = jobLauncher.run(job, param);
		System.out.println("Exit Status : " + execution.getStatus());
		System.out.println("Exit Status : " + execution.getAllFailureExceptions());

	} catch (Exception e) {
		e.printStackTrace();
	}
	System.out.println("Done");

  }

}

Output. Extracts all “user where age > 20” into an XML file.

xml/outputs/users.xml


<?xml version="1.0" encoding="UTF-8"?>
<users>
	<user>
		<id>1</id>
		<username>mkyong</username>
		<password>password</password>
		<age>30</age>
	</user>
	<user>
		<id>2</id>
		<username>user_a</username>
		<password>password</password>
		<age>25</age>
	</user>
	<user>
		<id>4</id>
		<username>user_c</username>
		<password>password</password>
		<age>25</age>
	</user>
	<user>
		<id>5</id>
		<username>user_d</username>
		<password>password</password>
		<age>40</age>
	</user>
</users>

Download Source Code

Download it – SpringBatch-MySQL-XML-Example.zip (22 kb)

References

20 comments on “Spring Batch Example – MySQL Database To XML”

Noman Naeem
September 5, 2019 at 7:56 am

Can you look at https://stackoverflow.com/questions/57795389/how-to-use-jdbcpagingitemreader-in-a-multithreaded-env
and help me find what’s missing.

Lawrence smith
August 20, 2019 at 11:24 pm

I got error,

Exception: Line 1 in XML document from class path resource [spring/batch/jobs/job-extract-users.xml] is invalid; nested exception is org.xml.sax.SAXParseException: XML version “2.0” is not supported, only XML 1.0 is supported.
at org.springframework.beans.factory.xml.XmlBeanDefinitionReader.doLoadBeanDefinitions(XmlBeanDefinitionReader.java:396)

What changes do i make to correct this error?

Anish Rauniyar
July 19, 2017 at 4:57 pm

Hello Mkyong,
Is there any way to use spring batch for mysql database to JSON?

Thanks in advance.

Thanks,
Anish

namrata
October 18, 2016 at 8:11 pm

thanks for the article can you please tell me how to return list grom row mapper

????Taher Tinwala
August 2, 2016 at 7:50 pm

I have single DB and have more than 1 tables to read so should I write itemReader for each table?

1. Meryem Taoui
  May 7, 2019 at 9:22 pm
  
  the same question
  
Ajay Soni
June 15, 2016 at 11:57 am

there no file for context.xml

Savani
April 4, 2016 at 3:20 am

Hello Mkyong,

I’ve few queries regarding this tutorial
1) How we can merge two tables data if we’re using CompositeReader and CompositeWritter
2) When wtrring Data to XML file I only get class reference not actual fields

Why we can fixed these two issue?

lalitha
December 30, 2015 at 3:01 pm

hi..i need one exaple to generate excel when job runs.
means write data from mysql to excel file.tq.

sumit
April 23, 2015 at 8:37 pm

Hi,

I am picking data from database using JdbcCursorItemReader from code. I have implemented a custom ItemReader. Is there a way to use parameterized query in JdbcCursorItemReader?

andre
March 19, 2015 at 10:07 pm

it seems like you’d need to write so many configuration for a pretty simple task..

CHANDRA
August 4, 2014 at 12:40 pm

Is there a way I can have a call to a storedprocedure and the corresponding rows mapped to the domain model using the rowmapper? I need to retrieve large set of data and retrieval is from joining multiple tables. I preferred storedprocedures as I can process the resultset applying all complex logic and let the spring batch just treat the data alone. I am also looking to partition it so I can have multiple threads processing them till the writer-my write is to write into a JMS/MQ.

Siva Prasad Thulabandula
August 3, 2014 at 2:10 am

Dude…you are just writing code…it would be great if you explain each and every class and usage of that class..

Stuart
July 24, 2014 at 1:42 am

Thank you so much for this Example. It was very valuable to me. The only issue I had was the SQL was case sensitive so I had to convert your queries to lower case for my MySQL DB.

Spring
July 11, 2014 at 2:32 am

If you name id as user_id, the tag in the XML file is (has two underscores). Anyone know how to solve this problem?

jw
June 10, 2014 at 6:46 am

Should be name pagingItemReader to map up with the ?

eminence
April 3, 2014 at 1:49 am

I want to write a xml in following format using staxItemWriter:-

Where ref and id can be retrieved from User Table.
User Table has columns:-REF,ID

Maciek
November 2, 2013 at 6:48 am

Whenever I’m trying to run this I gave a followed exception:

Exit Status : FAILED
Exit Status : [org.springframework.batch.item.ItemStreamException: Failed to initialize the reader, java.lang.NullPointerException]
Done

What can be issue?

1. Rajae Nassit
  January 19, 2016 at 1:51 am
  
  i have the same probleme, anyone can help me please.
  
amit christian
September 5, 2013 at 9:33 pm

I Like Your Site :

how much i could give you .
1,2,3,4,5,6,7,8,9,10 i would say all
numbers ….
this site is great .
2 1/2,4 1/5,….1/5 1/2……..

Spring Batch Example – MySQL Database To XML

1. Project Directory Structure

2. Database

3. Item Reader

4. Item Writer

5. Spring Batch Jobs

6. Run It

Download Source Code

References

20 comments on “Spring Batch Example – MySQL Database To XML”

Leave a Comment Cancel reply