In this post, we will show you how to configure a Spring Batch job to read data from an XML file and write into mysql database.
Project structure
This is a directory structure of the standard gradle project.
Project dependencies
task wrapper(type: Wrapper) {
gradleVersion = '3.2.1'
}
apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'org.springframework.boot'
sourceCompatibility = 1.8
repositories {
mavenLocal()
mavenCentral()
}
dependencies {
compile 'org.springframework:spring-oxm:4.3.7.RELEASE'
compileOnly('org.projectlombok:lombok:1.16.12')
runtime('mysql:mysql-connector-java')
compile('org.springframework.boot:spring-boot-starter-batch:1.5.2.RELEASE')
testCompile('org.springframework.boot:spring-boot-starter-test:1.5.2.RELEASE')
}
buildscript {
repositories {
mavenLocal()
jcenter()
}
dependencies {
classpath "org.springframework.boot:spring-boot-gradle-plugin:1.5.2.RELEASE"
}
}
application.properties file
spring.datasource.url=jdbc:mysql://localhost:3306/spring_batch spring.datasource.username=root spring.datasource.password=santosh spring.jpa.hibernate.ddl-auto=update
Spring Batch Jobs
XML file
<?xml version="1.0" encoding="UTF-8" ?>
<report>
<record id="1">
<date>03/28/2017</date>
<impression>139,237</impression>
<clicks>50</clicks>
<earning>220.90</earning>
</record>
<record id="2">
<date>03/29/2017</date>
<impression>339,100</impression>
<clicks>60</clicks>
<earning>320.88</earning>
</record>
<record id="3">
<date>03/30/2017</date>
<impression>431,436</impression>
<clicks>86</clicks>
<earning>270.80</earning>
</record>
</report>
write a SQL script to create a table to store the data.
DROP TABLE IF EXISTS report;
CREATE TABLE report (
id INT NOT NULL PRIMARY KEY,
date DATETIME,
impression BIGINT,
clicks INT,
earning DECIMAL(12,4)
)ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
Create a job which will read XML file and write into mysql database.
package com.walking.techie.xmltomysql.jobs;
import com.walking.techie.xmltomysql.converter.ReportConverter;
import com.walking.techie.xmltomysql.listener.CustomJobCompletionListener;
import com.walking.techie.xmltomysql.model.Report;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.batch.core.Job;
import org.springframework.batch.core.Step;
import org.springframework.batch.core.configuration.annotation.EnableBatchProcessing;
import org.springframework.batch.core.configuration.annotation.JobBuilderFactory;
import org.springframework.batch.core.configuration.annotation.StepBuilderFactory;
import org.springframework.batch.core.launch.support.RunIdIncrementer;
import org.springframework.batch.item.database.BeanPropertyItemSqlParameterSourceProvider;
import org.springframework.batch.item.database.JdbcBatchItemWriter;
import org.springframework.batch.item.xml.StaxEventItemReader;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.oxm.xstream.XStreamMarshaller;
@Configuration
@EnableBatchProcessing
public class XmlToMySqlJob {
@Autowired
private JobBuilderFactory jobBuilderFactory;
@Autowired
private StepBuilderFactory stepBuilderFactory;
@Autowired
private ReportConverter reportConverter;
@Autowired
private DataSource dataSource;
@Bean
public Job reportJob(CustomJobCompletionListener listener) {
return jobBuilderFactory.get("reportJob").incrementer(new RunIdIncrementer()).listener(listener)
.flow(step1())
.end().build();
}
@Bean
public Step step1() {
return stepBuilderFactory.get("step1").<Report, Report>chunk(10).reader(reader())
.writer(writer()).build();
}
@Bean
public StaxEventItemReader<Report> reader() {
StaxEventItemReader<Report> reader = new StaxEventItemReader<>();
reader.setResource(new ClassPathResource("report.xml"));
reader.setFragmentRootElementName("record");
reader.setUnmarshaller(unmarshaller());
return reader;
}
@Bean
public XStreamMarshaller unmarshaller() {
XStreamMarshaller unmarshal = new XStreamMarshaller();
Map<String, Class> aliases = new HashMap<String, Class>();
aliases.put("record", Report.class);
unmarshal.setAliases(aliases);
unmarshal.setConverters(reportConverter);
return unmarshal;
}
@Bean
public JdbcBatchItemWriter<Report> writer() {
JdbcBatchItemWriter<Report> writer = new JdbcBatchItemWriter<Report>();
writer.setItemSqlParameterSourceProvider(
new BeanPropertyItemSqlParameterSourceProvider<Report>());
writer.setSql(
"INSERT INTO report (id, date, impression, clicks, earning) VALUES (:id, :date, :impression, :clicks, :earning)");
writer.setDataSource(dataSource);
return writer;
}
}
Map XML file values to Report object and write to mysql database.
A Java model class
package com.walking.techie.xmltomysql.model;
import java.math.BigDecimal;
import java.util.Date;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
@Data
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class Report {
private int id;
private Date date;
private long impression;
private int clicks;
private BigDecimal earning;
}
This ReportConverter will convert the complex data type like Date and
BigDecimal
package com.walking.techie.xmltomysql.converter;
import com.thoughtworks.xstream.converters.Converter;
import com.thoughtworks.xstream.converters.MarshallingContext;
import com.thoughtworks.xstream.converters.UnmarshallingContext;
import com.thoughtworks.xstream.io.HierarchicalStreamReader;
import com.thoughtworks.xstream.io.HierarchicalStreamWriter;
import com.walking.techie.xmltomysql.model.Report;
import java.math.BigDecimal;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Locale;
import org.springframework.stereotype.Component;
@Component
public class ReportConverter implements Converter {
@Override
public void marshal(Object source, HierarchicalStreamWriter writer, MarshallingContext context) {
}
@Override
public Object unmarshal(HierarchicalStreamReader reader, UnmarshallingContext context) {
Report report = new Report();
report.setId(Integer.valueOf(reader.getAttribute("id")));
reader.moveDown();// move down
Date date = null;
try {
date = new SimpleDateFormat("MM/dd/yyyy").parse(reader.getValue());
} catch (ParseException e) {
e.printStackTrace();
}
report.setDate(date);
reader.moveUp();
reader.moveDown();//get impression
String impression = reader.getValue();
NumberFormat format = NumberFormat.getInstance(Locale.US);
Number number = 0;
try {
number = format.parse(impression);
} catch (ParseException e) {
e.printStackTrace();
}
report.setImpression(number.longValue());
reader.moveUp();
reader.moveDown();//get click
report.setClicks(Integer.valueOf(reader.getValue()));
reader.moveUp();
reader.moveDown();
report.setEarning(new BigDecimal(reader.getValue()));
reader.moveUp();
return report;
}
@Override
public boolean canConvert(Class type) {
return type.equals(Report.class);
}
}
JobExecutionListenerSupport is a listener class that listen to job before job start and job
completed.
package com.walking.techie.xmltomysql.listener;
import com.walking.techie.xmltomysql.model.Report;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import lombok.extern.slf4j.Slf4j;
import org.springframework.batch.core.BatchStatus;
import org.springframework.batch.core.JobExecution;
import org.springframework.batch.core.listener.JobExecutionListenerSupport;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;
@Component
@Slf4j
public class CustomJobCompletionListener extends JobExecutionListenerSupport {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public void afterJob(JobExecution jobExecution) {
if (jobExecution.getStatus() == BatchStatus.COMPLETED) {
log.info("!!! JOB FINISHED! Time to verify the results");
List<Report> results = jdbcTemplate
.query("SELECT id, date, impression, clicks, earning FROM report",
new RowMapper<Report>() {
@Override
public Report mapRow(ResultSet rs, int row) throws SQLException {
return new Report(rs.getInt(1), rs.getDate(2), rs.getLong(3), rs.getInt(4),
rs.getBigDecimal(5));
}
});
for (Report report : results) {
log.info("Found <" + report + "> in the database.");
}
}
}
}
Run Application
package com.walking.techie;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
Output
This application will read data from report.xml file and write records in report table. you can verify the records from console output.
output on console
2017-03-29 18:43:05.127 INFO 41660 --- [ main] o.s.b.c.l.support.SimpleJobLauncher : Job: [FlowJob: [name=reportJob]] launched with the following parameters: [{run.id=4}]
2017-03-29 18:43:05.148 INFO 41660 --- [ main] o.s.batch.core.job.SimpleStepHandler : Executing step: [step1]
2017-03-29 18:43:05.205 INFO 41660 --- [ main] c.w.t.x.l.CustomJobCompletionListener : !!! JOB FINISHED! Time to verify the results
2017-03-29 18:43:05.207 INFO 41660 --- [ main] c.w.t.x.l.CustomJobCompletionListener : Found <Report(id=1, date=2017-03-28, impression=139237, clicks=50, earning=220.9000)> in the database.
2017-03-29 18:43:05.208 INFO 41660 --- [ main] c.w.t.x.l.CustomJobCompletionListener : Found <Report(id=2, date=2017-03-29, impression=339100, clicks=60, earning=320.8800)> in the database.
2017-03-29 18:43:05.208 INFO 41660 --- [ main] c.w.t.x.l.CustomJobCompletionListener : Found <Report(id=3, date=2017-03-30, impression=431436, clicks=86, earning=270.8000)> in the database.
2017-03-29 18:43:05.211 INFO 41660 --- [ main] o.s.b.c.l.support.SimpleJobLauncher : Job: [FlowJob: [name=reportJob]] completed with the following parameters: [{run.id=4}] and the following status: [COMPLETED]
Note : This code has been compiled and run on mac notebook and intellij IDEA.
No comments :
Post a Comment