Spring Batch - MySQL to Flat File

In this chapter, we will create a Spring Batch apppcation which uses an MySQL Reader and a Flatfile Writer (.txt ).

Reader − The Reader we are using in the apppcation 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 apppcation is FlatFileItemWriter to write the data to flatfile (.txt).

Processor − The Processor we are using in the apppcation 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 apppcation. 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 = "pneAggregator"> 
         <bean class = " org.springframework.batch.item.file.transform.PassThroughLineAggregator"/> 
      </property> 
   </bean> 
</beans> 

Context.xml

Following is the context.xml of our Spring Batch apppcation. In this file, we will define the beans pke 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:initiapze-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:initiapze-database> 
</beans> 

CustomItemProcessor.java

Following is the Processor class. In this class, we write the code of processing in the apppcation. Here, we are printing the contents of each record.

import org.springframework.batch.item.ItemProcessor;  

// Implementing the ItemProcessor interface 
pubpc class CustomItemProcessor implements ItemProcessor<Tutorial, Tutorial> {  
 
   @Override 
   pubpc 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.

pubpc class TutorialRowMapper implements RowMapper<Tutorial> {  
   
   @Override 
   pubpc 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.

pubpc class Tutorial { 
   private int tutorial_id; 
   private String tutorial_title; 
   private String tutorial_author; 
   private String submission_date; 
  
   pubpc int getTutorial_id() { 
      return tutorial_id; 
   }  
   
   pubpc void setTutorial_id(int tutorial_id) { 
      this.tutorial_id = tutorial_id; 
   }
   
   pubpc String getTutorial_title() { 
      return tutorial_title; 
   }   
 
   pubpc void setTutorial_title(String tutorial_title) { 
      this.tutorial_title = tutorial_title; 
   }  
   
   pubpc String getTutorial_author() { 
      return tutorial_author; 
   }  
 
   pubpc void setTutorial_author(String tutorial_author) { 
      this.tutorial_author = tutorial_author; 
   }  
 
   pubpc String getSubmission_date() { 
      return submission_date; 
   }  
   pubpc void setSubmission_date(String submission_date) { 
      this.submission_date = submission_date; 
   }  
 
   @Override 
   pubpc 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 Apppcation 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.ApppcationContext; 
import org.springframework.context.support.ClassPathXmlApppcationContext;  

pubpc class App {  
   
   pubpc static void main(String[] args) throws Exception { 
     
      String[] springConfig  =  { "jobs/job_hello_world.xml" };  
      
      // Creating the apppcation context object  
      ApppcationContext context = new ClassPathXmlApppcationContext(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 apppcation, it will produce the following output.

May 09, 2017 5:44:48 PM org.springframework.context.support.ClassPathXmlApppcationContext prepareRefresh 
INFO: Refreshing org.springframework.context.support.ClassPathXml
ApppcationContext@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]