XML to MySQL

在本章中，我们将创建一个使用XML Reader和MySQL Writer的Spring Batch应用程序。

Reader - 我们在应用程序中使用的阅读器是StaxEventItemReader ，用于从XML文档中读取数据。

以下是我们在此应用程序中使用的输入XML文档。 本文档包含数据记录，这些记录指定了教程ID，教程作者，教程标题，提交日期，教程图标和教程描述等详细信息。

<?xml version="1.0" encoding="UTF-8"?> 
<tutorials> 
   <tutorial>      
      <tutorial_id>1001</tutorial_id> 
      <tutorial_author>Sanjay</tutorial_author> 
      <tutorial_title>Learn Java</tutorial_title> 
      <submission_date>06-05-2007</submission_date> 
      <tutorial_icon>https://www.xnip.cn/wp-content/uploads/2019/07/java-1.jpg</tutorial_icon> 
      <tutorial_description>Java is a high-level programming language originally 
         developed by Sun Microsystems and released in 1995. 
         Java runs on a variety of platforms. 
         This tutorial gives a complete understanding of Java.');</tutorial_description> 
   </tutorial> 
   <tutorial>      
      <tutorial_id>1002</tutorial_id> 
      <tutorial_author>Abdul S</tutorial_author> 
      <tutorial_title>Learn MySQL</tutorial_title> 
      <submission_date>19-04-2007</submission_date> 
      <tutorial_icon>https://www.xnip.cn/mysql/images/mysql-minilogo.jpg</tutorial_icon> 
      <tutorial_description>MySQL is the most popular 
         Open Source Relational SQL database management system. 
         MySQL is one of the best RDBMS being used for developing web-based software applications. 
         This tutorial will give you quick start with MySQL 
         and make you comfortable with MySQL programming.</tutorial_description> 
   </tutorial> 
   <tutorial>
      <tutorial_id>1003</tutorial_id> 
      <tutorial_author>Krishna Kasyap</tutorial_author> 
      <tutorial_title>Learn JavaFX</tutorial_title> 
      <submission_date>06-07-2017</submission_date> 
      <tutorial_icon>https://www.xnip.cn/wp-content/uploads/2019/07/java-1.jpg</tutorial_icon> 
      <tutorial_description>JavaFX is a Java library used to build Rich Internet Applications. 
         The applications developed using JavaFX can run on various devices 
         such as Desktop Computers, Mobile Phones, TVs, Tablets, etc. 
         This tutorial, discusses all the necessary elements of JavaFX that are required
         to develop effective Rich Internet Applications</tutorial_description> 
   </tutorial> 
</tutorials>

Writer - 我们在应用程序中使用的编写器是JdbcBatchItemWriter用于将数据写入MySQL数据库。 假设我们在MySQL中创建了一个名为"details"的数据库中的表。

CREATE TABLE details.TUTORIALS( 
   tutorial_id int(10) NOT NULL, 
   tutorial_author VARCHAR(20), 
   tutorial_title VARCHAR(50), 
   submission_date VARCHAR(20), 
   tutorial_icon VARCHAR(200), 
   tutorial_description VARCHAR(1000) 
);

Processor - 我们在应用程序中使用的处理器是一个自定义处理器，它将每个记录的数据写入PDF文档。

在批处理过程中，如果读取了"n"记录或数据元素，那么对于每个记录，它将读取数据，处理它，并在Writer中写入数据。 为了处理数据，它在传递的处理器上进行中继。 在这种情况下，在自定义处理器类中，我们编写了代码来加载特定的PDF文档，创建新页面，以表格格式将数据项写入PDF。

最后，如果您执行此应用程序，它将从XML文档中读取所有数据项，将它们存储在MySQL数据库中，并在给定的PDF文档中将它们打印在各个页面中。

jobConfig.xml

以下是我们的示例Spring Batch应用程序的配置文件。 在此文件中，我们将定义作业和步骤。 除此之外，我们还为ItemReader，ItemProcessor和ItemWriter定义了bean。 （这里，我们将它们与各自的类相关联，并传递所需属性的值以进行配置。）

<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 
      http://www.springframework.org/schema/util     
      http://www.springframework.org/schema/util/spring-util-3.0.xsd ">  
   <import resource = "../jobs/context.xml" /> 
   <bean id = "itemProcessor" class = "CustomItemProcessor" /> 
   <batch:job id = "helloWorldJob"> 
      <batch:step id = "step1"> 
         <batch:tasklet>           
            <batch:chunk reader = "xmlItemReader" writer = "mysqlItemWriter" processor = "itemProcessor">
            </batch:chunk> 
         </batch:tasklet> 
      </batch:step> 
   </batch:job> 
   <bean id = "xmlItemReader" 
      class = "org.springframework.batch.item.xml.StaxEventItemReader"> 
      <property name = "fragmentRootElementName" value = "tutorial" /> 
      <property name = "resource" value = "classpath:resources/tutorial.xml" /> 
      <property name = "unmarshaller" ref = "customUnMarshaller" /> 
   </bean> 
   <bean id = "customUnMarshaller" class = "org.springframework.oxm.xstream.XStreamMarshaller">
      <property name = "aliases"> 
         <util:map id = "aliases"> 
            <entry key = "tutorial" value = "Tutorial" />            
         </util:map> 
      </property> 
   </bean>  
   <bean id = "mysqlItemWriter" class = "org.springframework.batch.item.database.JdbcBatchItemWriter"> 
      <property name = "dataSource" ref = "dataSource" /> 
      <property name = "sql"> 
         <value> 
            <![CDATA[insert into details.tutorials (tutorial_id, tutorial_author, tutorial_title, 
               submission_date, tutorial_icon, tutorial_description) 
               values (:tutorial_id, :tutorial_author, :tutorial_title, :submission_date, 
               :tutorial_icon, :tutorial_description);]]>
         </value> 
      </property>   
      <property name = "itemSqlParameterSourceProvider"> 
         <bean class = "org.springframework.batch.item.database.BeanPropertyItemSqlParameterSourceProvider" /> 
      </property> 
   </bean> 
</beans>     

Context.xml

以下是我们的Spring Batch应用程序的context.xml 。 在此文件中，我们将定义诸如作业存储库，作业启动程序和事务管理器之类的bean。

<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"> 
   <!-- 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.ResourcelessTransactionMana ger" />  
   <bean id = "jobLauncher" 
      class = "org.springframework.batch.core.launch.support.SimpleJobLauncher"> 
      <property name = "jobRepository" ref = "jobRepository" /> 
   </bean> 
   <!-- connect to MySQL 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/details" /> 
      <property name = "username" value = "myuser" /> 
      <property name = "password" value = "password" /> 
   </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

以下是processor类。 在这个类中，我们在应用程序中编写处理代码。 在这里，我们正在加载PDF文档，创建新页面，创建表格，并为每条记录插入以下值:教程ID，教程名称，作者，表格中的提交日期。

import java.io.File; 
import java.io.IOException;  
import org.apache.pdfbox.pdmodel.PDDocument; 
import org.apache.pdfbox.pdmodel.PDPage; 
import org.apache.pdfbox.pdmodel.PDPageContentStream; 
import org.apache.pdfbox.pdmodel.font.PDType1Font; 
import org.springframework.batch.item.ItemProcessor;  
public class CustomItemProcessor implements ItemProcessor<Tutorial, Tutorial> {  
   public static void drawTable(PDPage page, PDPageContentStream contentStream, 
      float y, float margin, String[][] content) throws IOException { 
      final int rows = content.length; 
      final int cols = content[0].length; 
      final float rowHeight = 50; 
      final float tableWidth = page.getMediaBox().getWidth()-(2*margin); 
      final float tableHeight = rowHeight * rows; 
      final float colWidth = tableWidth/(float)cols; 
      final float cellMargin=5f;  
      // draw the rows 
      float nexty = y ; 
      for (int i = 0; i <= rows; i++) {   
         contentStream.drawLine(margin,nexty,margin+tableWidth,nexty); 
         nexty-= rowHeight; 
      }  
      //draw the columns 
      float nextx = margin; 
      for (int i = 0; i <= cols; i++) {
         contentStream.drawLine(nextx,y,nextx,y-tableHeight); 
         nextx += colWidth; 
      }  
      // now add the text    
      contentStream.setFont(PDType1Font.HELVETICA_BOLD,12);  
      float textx = margin+cellMargin; 
      float texty = y-15; 
      for(int i = 0; i < content.length; i++){ 
         for(int j = 0 ; j < content[i].length; j++){ 
            String text = content[i][j]; 
            contentStream.beginText(); 
            contentStream.moveTextPositionByAmount(textx,texty); 
            contentStream.drawString(text); 
            contentStream.endText(); 
            textx += colWidth; 
         } 
         texty-=rowHeight; 
         textx = margin+cellMargin; 
      } 
   }  
   @Override 
   public Tutorial process(Tutorial item) throws Exception { 
      System.out.println("Processing..." + item); 
      // Creating PDF document object 
      PDDocument doc = PDDocument.load(new File("C:/Examples/test.pdf"));     
      // Creating a blank page 
      PDPage page = new PDPage(); 
      doc.addPage( page ); 
      PDPageContentStream contentStream =  new PDPageContentStream(doc, page);  
      String[][] content = {{"Id",""+item.getTutorial_id()},
      {"Title", item.getTutorial_title()}, 
      {"Authour", item.getTutorial_author()}, 
      {"Submission Date", item.getSubmission_date()}} ;  
      drawTable(page, contentStream, 700, 100, content);       
      contentStream.close(); 
      doc.save("C:/Examples/test.pdf" ); 
      System.out.println("Hello"); 
      return item; 
   }    
}      

TutorialFieldSetMapper.java

以下是ReportFieldSetMapper类，它将数据设置为Tutorial类。

import org.springframework.batch.item.file.mapping.FieldSetMapper; 
import org.springframework.batch.item.file.transform.FieldSet; 
import org.springframework.validation.BindException;  
public class TutorialFieldSetMapper implements FieldSetMapper<Tutorial> { 
   @Override 
   public Tutorial mapFieldSet(FieldSet fieldSet) throws BindException {   
      // instantiating the Tutorial class 
      Tutorial tutorial = new Tutorial(); 
      // Setting the fields from XML 
      tutorial.setTutorial_id(fieldSet.readInt(0));   
      tutorial.setTutorial_title(fieldSet.readString(1)); 
      tutorial.setTutorial_author(fieldSet.readString(2)); 
      tutorial.setTutorial_icon(fieldSet.readString(3)); 
      tutorial.setTutorial_description(fieldSet.readString(4));   
      return tutorial;  
   }  
} 

Tutorial.java

以下是Tutorial类。 这是一个带有setter和getter方法的简单类。

public class Tutorial { 
   private int tutorial_id; 
   private String tutorial_author; 
   private String tutorial_title; 
   private String submission_date; 
   private String tutorial_icon; 
   private String tutorial_description;   
   @Override 
   public String toString() { 
      return " [id=" + tutorial_id + ", author=" + tutorial_author  
         + ", title=" + tutorial_title + ", date=" + submission_date + ", icon =" 
         +tutorial_icon +", description = "+tutorial_description+"]"; 
   }  
   public int getTutorial_id() { 
      return tutorial_id; 
   }  
   public void setTutorial_id(int tutorial_id) { 
      this.tutorial_id = tutorial_id; 
   }  
   public String getTutorial_author() { 
      return tutorial_author; 
   }  
   public void setTutorial_author(String tutorial_author) { 
      this.tutorial_author = tutorial_author; 
   }  
   public String getTutorial_title() { 
      return tutorial_title; 
   } 
   public void setTutorial_title(String tutorial_title) { 
      this.tutorial_title = tutorial_title; 
   }  
   public String getSubmission_date() { 
      return submission_date; 
   }  
   public void setSubmission_date(String submission_date) { 
      this.submission_date = submission_date; 
   }  
   public String getTutorial_icon() { 
      return tutorial_icon; 
   }  
   public void setTutorial_icon(String tutorial_icon) { 
      this.tutorial_icon = tutorial_icon; 
   }  
   public String getTutorial_description() { 
      return tutorial_description; 
   }  
   public void setTutorial_description(String tutorial_description) { 
      this.tutorial_description = tutorial_description; 
   } 
}

App.java

以下是启动批处理过程的代码。 在本课程中，我们将通过运行JobLauncher启动批处理应用程序。

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()); 
   }    
} 

在执行此应用程序时，它将生成以下输出。

May 05, 2017 4:39:22 PM org.springframework.context.support.ClassPathXmlApplicationContext 
prepareRefresh 
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@306a30c7: 
startup date [Fri May 05 16:39:22 IST 2017]; root of context hierarchy 
May 05, 2017 4:39:23 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions 
May 05, 2017 4:39:32 PM org.springframework.batch.core.job.SimpleStepHandler handleStep 
INFO: Executing step: [step1] 
<b>Processing</b>... [id=1001, author=Sanjay, title=Learn Java, date=06-05-2007, 
icon =https://www.xnip.cn/wp-content/uploads/2019/07/java-1.jpg, 
description = Java is a high-level programming language originally developed by Sun Microsystems 
and released in 1995. Java runs on a variety of platforms. 
This tutorial gives a complete understanding of Java.');] 
Hello 
<b>Processing</b>.. [id=1002, author=Abdul S, title=Learn MySQL, date=19-04-2007, 
icon =https://www.xnip.cn/wp-content/uploads/2019/06/mysql-logo.jpg, 
description = MySQL is the most popular Open Source Relational SQL database management system. 
MySQL is one of the best RDBMS being used for developing web-based software applications. 
This tutorial will give you quick start with MySQL and make you comfortable with MySQL programming.] 
Hello 
<b>Processing</b>... [id=1003, author=Krishna Kasyap, title=Learn JavaFX, date=06-072017, 
icon =https://www.xnip.cn/wp-content/uploads/2019/07/java-1.jpg,
description = JavaFX is a Java library used to build Rich Internet Applications. 
The applications developed using JavaFX can run on various devices 
such as Desktop Computers, Mobile Phones, TVs, Tablets, etc. 
This tutorial, discusses all the necessary elements of JavaFX 
that are required to develop effective Rich Internet Applications] 
Hello 
May 05, 2017 4:39:36 PM org.springframework.batch.core.launch.support.SimpleJobLauncher run 
INFO: Job: [FlowJob: [name=helloWorldJob]] completed with the following parameters: [{}] 
and the following status: [COMPLETED] 
Exit Status : COMPLETED 

如果验证数据库中的details.tutorial表，它将显示以下输出 -

这将生成一个PDF，其中包含每页上的记录，如下所示。