In this example, we’ll read the data from MySQL DB and write it into CSV using Spring Batch framework. I’ve taken traditional approach here and have created beans in Spring XML ways.
pom.xml — This includes all the dependencies needed to run the app.
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.1</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>spring-xml-batch-mysql-csv</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>spring-xml-batch-mysql-csv</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>11</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-batch</artifactId>
</dependency>
<dependency>
<groupId>com.github.javafaker</groupId>
<artifactId>javafaker</artifactId>
<version>1.0.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.batch</groupId>
<artifactId>spring-batch-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>Employee.java — Model class
@AllArgsConstructor(staticName = "create")
@NoArgsConstructor
@Data
@Builder
public class Employee {
private Long empId;
private String firstName;
private String lastName;
private Integer age;
private String email;
@Override
public String toString() {
return empId + "," + firstName + "," + lastName + "," + age + "," + email;
}
}EmployeeMapper.java — An interface used by JdbcTemplate for mapping rows of a ResultSet on a per-row basis. Implementations of this interface perform the actual work of mapping each row to a result object but don't need to worry about exception handling. SQLExceptions will be caught and handled by the calling JdbcTemplate.
public class EmployeeMapper implements RowMapper<Employee>{
@Override
public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
Employee result = new Employee();
result.setEmpId(rs.getLong("empId"));
result.setFirstName(rs.getString("firstName"));
result.setLastName(rs.getString("lastName"));
result.setAge(rs.getInt("age"));
result.setEmail(rs.getString("email"));
return result;
}
}job.xml — All the beans created to run the app.
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:batch="http://www.springframework.org/schema/batch"
xmlns:task="http://www.springframework.org/schema/task"
xmlns:util="http://www.springframework.org/schema/util"
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
http://www.springframework.org/schema/util
http://www.springframework.org/schema/util/spring-util-3.2.xsd">
<batch:job id="employeeJob">
<batch:step id="step1">
<batch:tasklet>
<batch:chunk reader="employeeItemReader"
writer="cvsFileItemWriter"
commit-interval="1">
</batch:chunk>
</batch:tasklet>
</batch:step>
</batch:job>
<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="Password" />
</bean>
<bean id="employeeItemReader" class="org.springframework.batch.item.database.JdbcCursorItemReader">
<property name="dataSource" ref="dataSource" />
<property name="sql" value="SELECT * FROM test.employee" />
<property name="rowMapper">
<bean class="com.example.mapper.EmployeeMapper" />
</property>
</bean>
<!-- JobRepository and JobLauncher are configuration/setup classes -->
<bean id="jobRepository"
class="org.springframework.batch.core.repository.support.MapJobRepositoryFactoryBean" />
<bean id="jobLauncher"
class="org.springframework.batch.core.launch.support.SimpleJobLauncher">
<property name="jobRepository" ref="jobRepository" />
</bean>
<bean id="transactionManager"
class="org.springframework.batch.support.transaction.ResourcelessTransactionManager" />
<bean id="cvsFileItemWriter" class="org.springframework.batch.item.file.FlatFileItemWriter">
<property name="resource" value="file:cvs/employee.csv" />
<property name="shouldDeleteIfExists" value="true" />
<property name="lineAggregator">
<bean class="org.springframework.batch.item.file.transform.DelimitedLineAggregator">
<property name="delimiter" value="," />
<property name="fieldExtractor">
<bean class="org.springframework.batch.item.file.transform.BeanWrapperFieldExtractor">
<property name="names" value="empId, firstName, lastName, age, email" />
</bean>
</property>
</bean>
</property>
</bean>
</beans>MainApp.java — To loan the beans from xml file and run the app.
package com.example;
import org.springframework.batch.core.Job;
import org.springframework.batch.core.JobExecution;
import org.springframework.batch.core.JobExecutionException;
import org.springframework.batch.core.JobParameters;
import org.springframework.batch.core.launch.JobLauncher;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
@SpringBootApplication
public class SpringXmlBatchMysqlCsvApplication implements CommandLineRunner{
public static void main(String[] args) {
SpringApplication.run(SpringXmlBatchMysqlCsvApplication.class, args);
}
@Override
public void run(String... args) throws Exception {
ApplicationContext context = new ClassPathXmlApplicationContext("spring-batch-context.xml");
JobLauncher jobLauncher = (JobLauncher) context.getBean("jobLauncher");
Job job = (Job) context.getBean("employeeJob");
try {
JobExecution execution = jobLauncher.run(job, new JobParameters());
System.out.println("Job Exit Status : "+ execution.getStatus());
} catch (JobExecutionException e) {
System.out.println("Job ExamResult failed");
e.printStackTrace();
}
}
}Here is the output of run
. ____ _ __ _ _
/\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
\\/ ___)| |_)| | | | | || (_| | ) ) ) )
' |____| .__|_| |_|_| |_\__, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot :: (v2.7.1)
2024-06-04 15:06:40.458 INFO 4066 --- [ main] c.e.SpringXmlBatchMysqlCsvApplication : Starting SpringXmlBatchMysqlCsvApplication using Java 17.0.1 on Prateeks-MacBook-Pro.local with PID 4066 (/Users/prats/Downloads/spring-xml-batch-mysql-csv/target/classes started by prateekashtikar in /Users/prats/Downloads/spring-xml-batch-mysql-csv)
2024-06-04 15:06:40.460 INFO 4066 --- [ main] c.e.SpringXmlBatchMysqlCsvApplication : No active profile set, falling back to 1 default profile: "default"
2024-06-04 15:06:41.500 INFO 4066 --- [ main] c.e.SpringXmlBatchMysqlCsvApplication : Started SpringXmlBatchMysqlCsvApplication in 1.473 seconds (JVM running for 2.29)
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
2024-06-04 15:06:41.893 INFO 4066 --- [ main] o.s.b.c.l.support.SimpleJobLauncher : No TaskExecutor has been set, defaulting to synchronous executor.
2024-06-04 15:06:41.943 INFO 4066 --- [ main] o.s.b.c.l.support.SimpleJobLauncher : Job: [FlowJob: [name=employeeJob]] launched with the following parameters: [{}]
2024-06-04 15:06:41.996 INFO 4066 --- [ main] o.s.batch.core.job.SimpleStepHandler : Executing step: [step1]
2024-06-04 15:06:42.513 INFO 4066 --- [ main] o.s.batch.core.step.AbstractStep : Step: [step1] executed in 517ms
2024-06-04 15:06:42.540 INFO 4066 --- [ main] o.s.b.c.l.support.SimpleJobLauncher : Job: [FlowJob: [name=employeeJob]] completed with the following parameters: [{}] and the following status: [COMPLETED] in 567ms
Job Exit Status : COMPLETEDOutput —
1,Pranali,Pimplikar,21,pp@gmail.com
2,Harshita,Dekate,29,harshita.dekate@gmail.com