Spring Boot Batch read from database and write to database example

Spring boot batch can read data from a database and write to another database by connecting multiple data sources in a single application. Spring boot batch reads table data from the source database using jpa in item reader, transforms it to destination table format in the item processor and stores the data in another database table. In this post, we’ll look at how to use spring boot batch to read data from a database and write it to an another database table.

In many circumstances, the data in a table must be copied to another database table. The data is utilized for reporting and analysis. The standard method is to convert data to a csv file, which is then exported to another database. Spring boot batch allows an application to connect to many databases and transfer data from one database table to another. Schedulers can be used to run the spring boot batch. As a result, this will be done automatically on a regular basis.



Database SQL Query

There are two different database is created to read data from one database table and write data to another database table. The first database will contain employee table. The table contains three columns id, name and salary. The second database contains manager table. The manager table contains three columns id, name and salary.

Database – testdb1

create table employee (
id int primary key,
name varchar(100),
salary int
);

insert into employee values (1,'name01',1000);
insert into employee values (2,'name02',2000);
insert into employee values (3,'name03',3000);

Database – testdb2

create table manager (
id int primary key auto_increment,
name varchar(100),
salary int
);


Application properties details

The application properties will be placed in the resources folder’s application.properties files. The application.properties now has two database configurations. The hibernate JPA related configurations also added in the application.properties file.

application.properties

spring.datasource.jdbc-url=jdbc:mysql://localhost/testdb1
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

spring.seconddatasource.jdbc-url=jdbc:mysql://localhost/testdb2
spring.seconddatasource.username=root
spring.seconddatasource.password=root
spring.seconddatasource.driver-class-name=com.mysql.cj.jdbc.Driver

spring.jpa.database=default
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update


Data Model class

Two packages are created for two database related configuration classes. The packages are com.yawintutor.primary and com.yawintutor.secondary. The employee table related configurations are configured in the primary package. The manager table related configurations are configured in the secondary package.

com.yawintutor.primary.Employee.java

package com.yawintutor.primary;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class Employee {
	@GeneratedValue(strategy = GenerationType.AUTO)
	@Id
	private int id;
	private String name;
	private int salary;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getSalary() {
		return salary;
	}
	public void setSalary(int salary) {
		this.salary = salary;
	}	
}

com.yawintutor.secondary.Manager.java

package com.yawintutor.secondary;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class Manager {
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Id
	private int id;
	private String name;
	private int salary;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getSalary() {
		return salary;
	}
	public void setSalary(int salary) {
		this.salary = salary;
	}	
}


JPA Repository

The employee jpa repository is created to access the employee table from the primary database. The manager jpa repository is used to connect the manager table from the secondary database.

com.yawintutor.primary.EmployeeRepository.java

package com.yawintutor.primary;

import org.springframework.data.jpa.repository.JpaRepository;

public interface EmployeeRepository extends JpaRepository<Employee, Long> {

}

com.yawintutor.secondary.ManagerRepository.java

package com.yawintutor.secondary;

import org.springframework.data.jpa.repository.JpaRepository;

public interface ManagerRepository extends JpaRepository<Manager, Long> {

}


Database Configurations

The multiple database configurations should be created in different spring boot configuration java classes. Two database configuration classes are created to connect two database. The PrimaryDBConfig java class contains the testdb1 database configurations such as data source, entity manager factory and transaction manager. The SecondaryDBConfig java class contains the testdb2 database configurations.

com.yawintutor.PrimaryDBConfig.java

package com.yawintutor;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
  entityManagerFactoryRef = "primaryEntityManagerFactory",
  transactionManagerRef = "primaryTransactionManager",
  basePackages = { "com.yawintutor.primary" }
)
public class PrimaryDBConfig {
	
	@Bean(name="primaryDataSource")
	@Primary
	@ConfigurationProperties(prefix="spring.datasource")
	public DataSource primaryDataSource() {
	    return DataSourceBuilder.create().build();
	}

	@Primary
	@Bean(name = "primaryEntityManagerFactory")
	public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory(EntityManagerFactoryBuilder builder,
			@Qualifier("primaryDataSource") DataSource primaryDataSource) {
		return builder
				.dataSource(primaryDataSource)
				.packages("com.yawintutor.primary")
				.build();
	}

	@Bean(name = "primaryTransactionManager")
	public PlatformTransactionManager primaryTransactionManager(
			@Qualifier("primaryEntityManagerFactory") EntityManagerFactory primaryEntityManagerFactory) {
		return new JpaTransactionManager(primaryEntityManagerFactory);
	}
}

com.yawintutor.SecondaryDBConfig.java

package com.yawintutor;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
  entityManagerFactoryRef = "secondaryEntityManagerFactory",
  transactionManagerRef = "secondaryTransactionManager",
  basePackages = { "com.yawintutor.secondary" }
)
public class SecondaryDBConfig {
	
	@Bean(name="secondaryDataSource")
	@ConfigurationProperties(prefix="spring.seconddatasource")
	public DataSource secondaryDataSource() {
	    return DataSourceBuilder.create().build();
	}

	@Bean(name = "secondaryEntityManagerFactory")
	public LocalContainerEntityManagerFactoryBean secondaryEntityManagerFactory(EntityManagerFactoryBuilder builder,
			@Qualifier("secondaryDataSource") DataSource secondaryDataSource) {
		return builder
				.dataSource(secondaryDataSource)
				.packages("com.yawintutor.secondary")
				.build();
	}

	@Bean(name = "secondaryTransactionManager")
	public PlatformTransactionManager secondaryTransactionManager(
			@Qualifier("secondaryEntityManagerFactory") EntityManagerFactory secondaryEntityManagerFactory) {
		return new JpaTransactionManager(secondaryEntityManagerFactory);
	}
}


Spring Boot Batch Configurations

The batch job and batch step configurations are stored in the spring boot batch configuration file. The JdbcCursorItemReader reads data from a database table. The hibernate jpa repository is used to write the table data in another database.

The data from the MySQL Database table will be read using the JdbcCursorItemReader reader. The read data will be converted into a java bean object. The java bean object is used to process the data. In this example, the employee object is created. The employee object is transformed to manager object. The manager object is stored in another database table.

BatchConfig.java

package com.yawintutor;

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.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import com.yawintutor.primary.Employee;
import com.yawintutor.secondary.Manager;

@Configuration
@EnableBatchProcessing
public class BatchConfig {
	@Autowired
	public JobBuilderFactory jobBuilderFactory;

	@Autowired
	public StepBuilderFactory stepBuilderFactory;
		
	@Autowired
	MyCustomReader myCustomReader;
	
	@Autowired
	MyCustomWriter myCustomWriter;

	@Autowired
	MyCustomProcessor myCustomProcessor;
	
	@Bean
	public Job createJob() {
		return jobBuilderFactory.get("MyJob")
				.incrementer(new RunIdIncrementer())
				.flow(createStep()).end().build();
	}

	@Bean
	public Step createStep() {
		return stepBuilderFactory.get("MyStep")
				.<Employee, Manager> chunk(1)
				.reader(myCustomReader)
				.processor(myCustomProcessor)
				.writer(myCustomWriter)
				.build();
	}
}


ItemReader Implementation

The Reader class is created by extending the JdbcCursorItemReader class and implementing the ItemReader Interface. The datasource and sql query should be added to the reader class JdbcCursorItemReader. To identify and convert to a java bean object, a RowMapper implementation class is required. The EmployeeRowMapper is created by implementing the RowMapper interface.

MyCustomReader.java

package com.yawintutor;

import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.springframework.batch.item.ItemReader;
import org.springframework.batch.item.database.JdbcCursorItemReader;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import com.yawintutor.primary.Employee;

@Component
public class MyCustomReader extends JdbcCursorItemReader<Employee> implements ItemReader<Employee>{
	
	public MyCustomReader(@Autowired DataSource primaryDataSource) {
		setDataSource(primaryDataSource);
		setSql("SELECT id, name, salary FROM employee");
		setFetchSize(100);
		setRowMapper(new EmployeeRowMapper());
	}
	
	public class EmployeeRowMapper implements RowMapper<Employee> {
		@Override
		public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
			Employee employee  = new Employee();
			employee.setId(rs.getInt("id"));
			employee.setName(rs.getString("name"));
			employee.setSalary(rs.getInt("salary"));
			return employee;
		}
	}
}


ItemProcessor Implementation

The Processor class is used to transform table data from one database to another database table in a different format. This ItemProcessor class converts the data object created in the item reader to a different object format.

MyCustomProcessor.java

package com.yawintutor;

import org.springframework.batch.item.ItemProcessor;
import org.springframework.stereotype.Component;

import com.yawintutor.primary.Employee;
import com.yawintutor.secondary.Manager;

@Component
public class MyCustomProcessor implements ItemProcessor<Employee, Manager> {

	@Override
	public Manager process(Employee emp) throws Exception {
		System.out.println("MyBatchProcessor : Processing data : "+emp);
		Manager manager = new Manager();
		//manager.setId(emp.getId());
		manager.setName(emp.getName().toUpperCase());
		manager.setSalary(emp.getSalary());
		return manager;
	}
}


ItemWriter Implementation

The Writer class is created by implementing the ItemWriter Interface. The processed data from item processor class is available as a list. The list of data will be saved in another database table.

MyCustomWriter.java

package com.yawintutor;

import java.util.List;

import org.springframework.batch.item.ItemWriter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.yawintutor.secondary.Manager;
import com.yawintutor.secondary.ManagerRepository;

@Component
public class MyCustomWriter implements ItemWriter<Manager> {
	
	@Autowired
	ManagerRepository managerRepository ;
	
	@Override
	public void write(List<? extends Manager> list) throws Exception {
		for (Manager data : list) {
			System.out.println("MyCustomWriter    : Writing data    : " + data.getId()+" : "+data.getName()+" : "+data.getSalary());
			managerRepository.save(data);
		}
	}
}


Scheduler class

SchedulerConfig.java

package com.yawintutor;

import java.text.SimpleDateFormat;
import java.util.Calendar;

import org.springframework.batch.core.Job;
import org.springframework.batch.core.JobParameters;
import org.springframework.batch.core.JobParametersBuilder;
import org.springframework.batch.core.launch.JobLauncher;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;

@Configuration
@EnableScheduling
public class SchedulerConfig {

	@Autowired
	JobLauncher jobLauncher;

	@Autowired
	Job job;

	SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");

	@Scheduled(fixedDelay = 5000, initialDelay = 5000)
	public void scheduleByFixedRate() throws Exception {
		System.out.println("Batch job starting");
		JobParameters jobParameters = new JobParametersBuilder()
				.addString("time", format.format(Calendar.getInstance().getTime())).toJobParameters();
		jobLauncher.run(job, jobParameters);
		System.out.println("Batch job executed successfully\n");
	}
}


Spring boot Batch Main Class

The main class for the spring boot batch will be shown as below. The default implementation will be used for the main class. The main method in this file will be executed when the spring boot batch application starts.

SpringBootBatchDbToDbApplication.java

package com.yawintutor;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SpringBootBatchDbToDbApplication {

	public static void main(String[] args) {
		SpringApplication.run(SpringBootBatchDbToDbApplication.class, args);
	}
}


Application configuration using pom.xml file

The required application configurations and dependencies are created using maven pom.xml file. The Maven POM.xml file contains dependencies for spring boot batch, JPA Repository and mysql database. If you are using some other database other than MySQL Database, you need to add the dependency to the respective database in the pom.xml file.

pom.xml

<?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.5.4</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.yawintutor</groupId>
	<artifactId>SpringBootBatchDBToDB</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>SpringBootBatchDBToDB</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>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</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>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-configuration-processor</artifactId>
			<optional>true</optional>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>


How to run

When. the spring boot application starts, the employee table data from a database is copied to another database table manager. For each scheduler execution, three employee table data is inserted into manager table in another database.

select count(*) from manager;
-- 9 counts