Spring Batch: Writing data to a database

 

In this post, I am going to explain how to write data to a database.

 

To demonstrate the application I am going to read data from an xml file and populate employee instances.

 

public class Employee {

         private int id;

         private String firstName;

         private String lastName;

 

         ......

         ......

}

 

Once Employee instances are populated via reader, you can use ‘JdbcBatchItemWriter’ to write data to table ‘EMPLOYEE’.

@Bean
public JdbcBatchItemWriter<Employee> employeeItemWriter() {
	JdbcBatchItemWriter<Employee> writer = new JdbcBatchItemWriter<>();

	writer.setDataSource(dataSource);
	writer.setSql("INSERT INTO EMPLOYEE VALUES (:id, :firstName, :lastName)");
	writer.setItemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider<Employee>());
	writer.afterPropertiesSet();

	return writer;
}

Find the below working application.

 

Step 1: Create new maven project ‘write-to-database’.

 

Step 2: Update pom.xml with maven dependencies.

 

pom.xml

<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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.sample.app</groupId>
	<artifactId>write-to-database</artifactId>
	<version>1</version>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.1.6.RELEASE</version>
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</properties>

	<dependencies>

		<!-- https://mvnrepository.com/artifact/org.springframework.batch/spring-batch-core -->
		<dependency>
			<groupId>org.springframework.batch</groupId>
			<artifactId>spring-batch-core</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
		</dependency>

		<dependency>
			<groupId>com.thoughtworks.xstream</groupId>
			<artifactId>xstream</artifactId>
			<version>1.4.11.1</version>
		</dependency>

		<!-- https://mvnrepository.com/artifact/org.springframework/spring-oxm -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-oxm</artifactId>
		</dependency>

	</dependencies>
</project>

Step 3: Create ‘schema.sql’ file in src/main/resources folder.

 

schema.sql

CREATE TABLE `EMPLOYEE` (
	`id` INT(11) NOT NULL,
	`firstName` varchar(255),
	`lastName`  varchar(255),
	PRIMARY KEY(`id`)
);

Step 4: Create application.properties file under src/main/resources folder.

 

application.properties

logging.level.root=ERROR
logging.level.org.hibernate=ERROR

## H2 specific properties
spring.h2.console.enabled=true
spring.h2.console.path=/h2

spring.jpa.generate-ddl=false
spring.jpa.hibernate.ddl-auto=none
spring.datasource.url=jdbc:h2:file:~/db/myOrg.db;DB_CLOSE_ON_EXIT=FALSE;DB_CLOSE_DELAY=-1;
spring.datasource.username=krishna
spring.datasource.password=password123
spring.datasource.initialization-mode=always


spring.batch.initialize-schema=always

Step 5: Create emps.xml file under src/main/resources/xml folder.

 

emps.xml

<employees>
	<employee>
		<id>1</id>
		<firstName>Ram</firstName>
		<lastName>Gurram</lastName>
	</employee>
	<employee>
		<id>2</id>
		<firstName>Sailaja</firstName>
		<lastName>Dokku</lastName>
	</employee>
	<employee>
		<id>3</id>
		<firstName>Harika</firstName>
		<lastName>Raghuram</lastName>
	</employee>
	<employee>
		<id>4</id>
		<firstName>Gopi</firstName>
		<lastName>Battu</lastName>
	</employee>
	<employee>
		<id>5</id>
		<firstName>Siva</firstName>
		<lastName>Prathipati</lastName>
	</employee>
	<employee>
		<id>6</id>
		<firstName>Sharief</firstName>
		<lastName>Khan</lastName>
	</employee>
	<employee>
		<id>7</id>
		<firstName>Joel</firstName>
		<lastName>Chelli</lastName>
	</employee>
</employees>

Step 6: Create new package ‘com.sample.app.model’ and define Employee class.

 

Employee.java

package com.sample.app.model;

public class Employee {
	private int id;
	private String firstName;
	private String lastName;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getFirstName() {
		return firstName;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}

	@Override
	public String toString() {
		StringBuilder builder = new StringBuilder();
		builder.append("Employee [id=");
		builder.append(id);
		builder.append(", firstName=");
		builder.append(firstName);
		builder.append(", lastName=");
		builder.append(lastName);
		builder.append("]");
		return builder.toString();
	}

}

Step 7: Create package ‘com.sample.app.configuration’ and define JobConfiguration.

 

JobConfiguration.java

package com.sample.app.configuration;

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.repository.JobRepository;
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;
import org.springframework.transaction.PlatformTransactionManager;

import com.sample.app.model.Employee;

@Configuration
@EnableBatchProcessing
public class JobConfiguration {
	@Autowired
	private JobBuilderFactory jobBuilderFactory;

	@Autowired
	private StepBuilderFactory stepBuilderFactory;

	@Autowired
	private DataSource dataSource;

	@Bean
	public StaxEventItemReader<Employee> reader() {

		StaxEventItemReader<Employee> staxEventItemReader = new StaxEventItemReader<>();

		Map<String, Class> aliases = new HashMap<>();
		aliases.put("employee", Employee.class);

		XStreamMarshaller unMarshaller = new XStreamMarshaller();
		unMarshaller.setAliases(aliases);

		staxEventItemReader.setResource(new ClassPathResource("/xml/emps.xml"));
		staxEventItemReader.setFragmentRootElementName("employee");
		staxEventItemReader.setUnmarshaller(unMarshaller);

		return staxEventItemReader;
	}

	@Bean
	public JdbcBatchItemWriter<Employee> employeeItemWriter() {
		JdbcBatchItemWriter<Employee> writer = new JdbcBatchItemWriter<>();

		writer.setDataSource(dataSource);
		writer.setSql("INSERT INTO EMPLOYEE VALUES (:id, :firstName, :lastName)");
		writer.setItemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider<Employee>());
		writer.afterPropertiesSet();

		return writer;
	}

	@Bean
	public Step step1() {
		return this.stepBuilderFactory.get("step1").<Employee, Employee>chunk(5).reader(reader())
				.writer(employeeItemWriter()).build();
	}

	@Bean
	public Job myJob(JobRepository jobRepository, PlatformTransactionManager platformTransactionManager) {

		return jobBuilderFactory.get("My-First-Job").start(step1()).build();
	}

}

Step 8: Define App.java

 

App.java

package com.sample.app;

import org.springframework.batch.core.configuration.annotation.EnableBatchProcessing;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@EnableBatchProcessing
@SpringBootApplication
public class App {

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

Total project structure looks like below.

Run App.java.

 

Open the url 'http://localhost:8080/h2/' in browser.

 

Login with user name ‘krishna’ and password ‘password123’.

Select EMPLOYEE table and see the contents.

You can download complete working application from this link.

https://github.com/harikrishna553/springboot/tree/master/batch/write-to-database

 

Previous                                                    Next                                                    Home