Spring Batch - MySQL to Flat File
In this chapter, we will create a Spring Batch application which uses an MySQL Reader and a Flatfile Writer (.txt ).
Reader − The Reader we are using in the application is JdbcCursorItemReader to read data from MySQL database.
Assume we have created a table in the MySQL database as shown below.
CREATE TABLE details.xml_mysql(
person_id int(10) NOT NULL,
sales VARCHAR(20),
qty int(3),
staffName VARCHAR(20),
date VARCHAR(20)
);
Assume we have inserted the following records into it.
mysql> select * from tutorialsdata;
+-------------+-----------------+----------------+-----------------+
| tutorial_id | tutorial_author | tutorial_title | submission_date |
+-------------+-----------------+----------------+-----------------+
| 101 | Sanjay | Learn Java | 06-05-2007 |
| 102 | Abdul S | Learn MySQL | 19-04-2007 |
| 103 | Krishna Kasyap | Learn JavaFX | 06-07-2017 |
+-------------+-----------------+----------------+-----------------+
3 rows in set (0.00 sec)
Writer − The Writer we are using in the application is FlatFileItemWriter to write the data to flatfile (.txt).
Processor − The Processor we are using in the application is a custom processor which just prints the records read from the CSV file.
jobConfig.xml
Following is the configuration file of our sample Spring Batch application. In this file, we will define the Job and the Steps. In addition to these, we also define the beans for ItemReader, ItemProcessor, and ItemWriter. (Here, we associate them with respective classes and pass the values for the required properties to configure them.)
<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"
xmlns:util = "http://www.springframework.org/schema/util"
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 = "../jobs/context.xml" />
<bean id = "tutorial" class = "Tutorial" scope = "prototype" />
<bean id = "itemProcessor" class = "CustomItemProcessor" />
<batch:job id = "helloWorldJob">
<batch:step id = "step1">
<batch:tasklet>
<batch:chunk reader = "mysqlItemReader"
writer = "flatFileItemWriter" processor = "itemProcessor"
commit-interval = "10">
</batch:chunk>
</batch:tasklet>
</batch:step>
</batch:job>
<bean id = "mysqlItemReader"
class = "org.springframework.batch.item.database.JdbcCursorItemReader" >
<property name = "dataSource" ref = "dataSource" />
<property name = "sql" value = "select * from details.tutorialsdata" />
<property name = "rowMapper">
<bean class = "TutorialRowMapper" />
</property>
</bean>
<bean id = "flatFileItemWriter"
class = " org.springframework.batch.item.file.FlatFileItemWriter">
<property name = "resource" value = "file:target/outputfiles/employee_output.txt"/>
<property name = "lineAggregator">
<bean class = " org.springframework.batch.item.file.transform.PassThroughLineAggregator"/>
</property>
</bean>
</beans>
Context.xml
Following is the context.xml of our Spring Batch application. In this file, we will define the beans like job repository, job launcher, and transaction manager.
<beans xmlns = "http://www.springframework.org/schema/beans"
xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"
xmlns:jdbc = "http://www.springframework.org/schema/jdbc"
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 ">
<!-- stored job-meta in database -->
<bean id = "jobRepository"
class = "org.springframework.batch.core.repository.support.JobRepositoryFactoryBean">
<property name = "dataSource" ref = "dataSource" />
<property name = "transactionManager" ref = "transactionManager" />
<property name = "databaseType" value = "mysql" />
</bean>
<bean id = "transactionManager"
class = "org.springframework.batch.support.transaction.ResourcelessTransactionManager" />
<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/details" />
<property name = "username" value = "myuser" />
<property name = "password" value = "password" />
</bean>
<bean id = "jobLauncher"
class = "org.springframework.batch.core.launch.support.SimpleJobLauncher">
<property name = "jobRepository" ref = "jobRepository" />
</bean>
<!-- create job-meta tables automatically -->
<jdbc:initialize-database data-source = "dataSource">
<jdbc:script location = "org/springframework/batch/core/schema-drop-mysql.sql" />
<jdbc:script location = "org/springframework/batch/core/schema-mysql.sql" />
</jdbc:initialize-database>
</beans>
CustomItemProcessor.java
Following is the Processor class. In this class, we write the code of processing in the application. Here, we are printing the contents of each record.
import org.springframework.batch.item.ItemProcessor;
// Implementing the ItemProcessor interface
public class CustomItemProcessor implements ItemProcessor<Tutorial, Tutorial> {
@Override
public Tutorial process(Tutorial item) throws Exception {
System.out.println("Processing..." + item);
return item;
}
}
TutorialRowMapper.java
Following is the TutorialRowMapper class which sets the data to the Tutorial class.
public class TutorialRowMapper implements RowMapper<Tutorial> {
@Override
public Tutorial mapRow(ResultSet rs, int rowNum) throws SQLException {
Tutorial tutorial = new Tutorial();
tutorial.setTutorial_id(rs.getInt("tutorial_id"));
tutorial.setTutorial_title(rs.getString("tutorial_title"));
tutorial.setTutorial_author(rs.getString("tutorial_author"));
tutorial.setSubmission_date(rs.getString("submission_date"));
return tutorial;
}
}
Tutorial.java
Following is the Tutorial class. It is a simple Java class with setter and getter methods. In this class, we are using annotations to associate the methods of this class with the tags of the XML file.
public class Tutorial {
private int tutorial_id;
private String tutorial_title;
private String tutorial_author;
private String submission_date;
public int getTutorial_id() {
return tutorial_id;
}
public void setTutorial_id(int tutorial_id) {
this.tutorial_id = tutorial_id;
}
public String getTutorial_title() {
return tutorial_title;
}
public void setTutorial_title(String tutorial_title) {
this.tutorial_title = tutorial_title;
}
public String getTutorial_author() {
return tutorial_author;
}
public void setTutorial_author(String tutorial_author) {
this.tutorial_author = tutorial_author;
}
public String getSubmission_date() {
return submission_date;
}
public void setSubmission_date(String submission_date) {
this.submission_date = submission_date;
}
@Override
public String toString() {
return " [id=" + tutorial_id + ", title=" +
tutorial_title + ",
author=" + tutorial_author + ", date=" +
submission_date + "]";
}
}
App.java
Following is the code which launces the batch process. In this class, we will launch the Batch Application by running the JobLauncher.
import org.springframework.batch.core.Job;
import org.springframework.batch.core.JobExecution;
import org.springframework.batch.core.JobParameters;
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) throws Exception {
String[] springConfig = { "jobs/job_hello_world.xml" };
// Creating the application context object
ApplicationContext context = new ClassPathXmlApplicationContext(springConfig);
// Creating the job launcher
JobLauncher jobLauncher = (JobLauncher) context.getBean("jobLauncher");
// Creating the job
Job job = (Job) context.getBean("helloWorldJob");
// Executing the JOB
JobExecution execution = jobLauncher.run(job, new JobParameters());
System.out.println("Exit Status : " + execution.getStatus());
}
}
On executing this application, it will produce the following output.
May 09, 2017 5:44:48 PM org.springframework.context.support.ClassPathXmlApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXml
ApplicationContext@3d646c37: startup date [Tue May
09 17:44:48 IST 2017]; root of context hierarchy
May 09, 2017 5:44:48 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
May 09, 2017 5:44:56 PM org.springframework.batch.core.launch.support.SimpleJobLauncher run
INFO: Job: [FlowJob: [name=helloWorldJob]] launched
with the following parameters: [{}]
May 09, 2017 5:44:56 PM org.springframework.batch.core.job.SimpleStepHandler handleStep
INFO: Executing step: [step1]
Processing...Report [id=101, title=Learn Java, author=Sanjay, date=06-05-2007]
Processing...Report [id=102, title=Learn MySQL, author=Abdul S, date=19-04-2007]
Processing...Report [id=103, title=Learn JavaFX, author=Krishna Kasyap, date=0607-2017]
May 09, 2017 5:44:57 PM org.springframework.batch.core.launch.support.SimpleJobLauncher run
INFO: Job: [FlowJob: [name=helloWorldJob]] completed with the following parameters:
[{}] and the following status: [COMPLETED]
Hello
Exit Status : COMPLETED
This will generate a .txt file with the following contents.
Report [id=101, title=Learn Java, author=Sanjay, date=06-05-2007]
Report [id=102, title=Learn MySQL, author=Abdul S, date=19-04-2007]
Report [id=103, title=Learn JavaFX, author=Krishna Kasyap, date=06-07-2017]
