This tutorial will show you how to read a csv file data and write to MySQL database with some modification. You can read the tutorial Spring Batch to read what is Spring Batch and what are the usages of Spring Batch. In my previous tutorial I have shown how to read csv file and write to xml file using spring batch.

What we will do

We’ll build a service that imports data from a CSV file, transforms it with custom code, and stores the final results in MySQL Database.

Prerequisites

Eclipse

Maven 3.2.1

JDK 1.8

Spring Batch and MySQL connector dependencies in pom.xml file

Step 1. Create Maven project(standalone or quickstart) in Eclipse IDE and necessary project structure gets created

Group Id: com.roytuts

Artifact Id: spring-batch

Step 2. Modify pom.xml file so that it looks like below. It downloads all jars from maven repository.

<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.roytuts</groupId>
	<artifactId>spring-batch</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>spring-batch</name>
	<url>http://maven.apache.org</url>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<java.version>1.8</java.version>
		<spring.version>4.3.5.RELEASE</spring.version>
		<spring.batch.version>3.0.7.RELEASE</spring.batch.version>
		<mysql.version>5.1.27</mysql.version>
	</properties>

	<dependencies>
		<!-- spring core -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version>${spring.version}</version>
		</dependency>

		<!-- spring context -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${spring.version}</version>
		</dependency>

		<!-- spring batch -->
		<dependency>
			<groupId>org.springframework.batch</groupId>
			<artifactId>spring-batch-core</artifactId>
			<version>${spring.batch.version}</version>
		</dependency>

		<!-- spring jdbc -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${spring.version}</version>
		</dependency>

		<!-- mysql driver -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>${mysql.version}</version>
		</dependency>

	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<configuration>
					<source>${java.version}</source>
					<target>${java.version}</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

Step 3. Create a model class Person.java which will represent a row of data for inputs and outputs.

package com.roytuts.spring.batch.model;

public class Person {

	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() {
		return "Person [id=" + id + ", firstName=" + firstName + ", lastName=" + lastName + "]";
	}

}

Step 4. FielSetMapper is no longer required so create an intermediate processor. A common paradigm in batch processing is to ingest data, transform it, and then pipe it out somewhere else. Here we write a simple transformer that converts the initial characters of the names to uppercase.

package com.roytuts.spring.batch.processor;

import org.springframework.batch.item.ItemProcessor;

import com.roytuts.spring.batch.model.Person;

public class PersonItemProcessor implements ItemProcessor<Person, Person> {

	@Override
	public Person process(Person person) throws Exception {
		System.out.println("Processing: " + person);
		final String initCapFirstName = person.getFirstName().substring(0, 1).toUpperCase()
				+ person.getFirstName().substring(1);
		final String initCapLastName = person.getLastName().substring(0, 1).toUpperCase()
				+ person.getLastName().substring(1);
		Person transformedPerson = new Person();
		transformedPerson.setId(person.getId());
		transformedPerson.setFirstName(initCapFirstName);
		transformedPerson.setLastName(initCapLastName);
		return transformedPerson;
	}

}

Step 5. Create below CSV file under src/main/resources directory

1000,soumitra,roy
1001,souvik,sanyal
1002,arup,chatterjee
1003,suman,mukherjee
1004,debina,guha
1005,liton,sarkar
1006,debabrata,poddar

Step 6. Create below spring config XML files under src/main/resources directory

spring-beans.xml

This file defines beans for Person class and PersonItemProcessor class.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">

	<bean id="person" class="com.roytuts.spring.batch.model.Person"
		scope="prototype" />

	<bean id="itemProcessor" class="com.roytuts.spring.batch.processor.PersonItemProcessor"
		scope="prototype" />

</beans>

spring-datasource.xml

This file defines the bean for spring datasource. It also initializes the database for automatically creating spring batch job meta tables in MySQL database.

<?xml version="1.0" encoding="UTF-8"?>
<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"
	xmlns:p="http://www.springframework.org/schema/p" xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:jee="http://www.springframework.org/schema/jee" xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:task="http://www.springframework.org/schema/task"
	xsi:schemaLocation="http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task.xsd http://www.springframework.org/schema/jdbc
	http://www.springframework.org/schema/jdbc/spring-jdbc.xsd">

	<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/roytuts" />
		<property name="username" value="root" />
		<property name="password" value="" />
	</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>

spring-batch.xml

This files defines beans for spring batch job, csv file reader, MySQL database writer, job repository, transaction manager, job launcher.

A default simple implementation of the Job interface is provided by Spring Batch in the form of the SimpleJob class which creates some standard functionality on top of Job, however the batch namespace abstracts away the need to instantiate it directly.

A Step is a domain object that encapsulates an independent, sequential phase of a batch job. Therefore, every Job is composed entirely of one or more steps. A Step contains all of the information necessary to define and control the actual batch processing.

ItemReader is an abstraction that represents the retrieval of input for a Step, one item at a time.

ItemWriter is an abstraction that represents the output of a Step, one batch or chunk of items at a time. Generally, an item writer has no knowledge of the input it will receive next, only the item that was passed in its current invocation.

ItemProcessor is an abstraction that represents the business processing of an item. While the ItemReader reads one item, and the ItemWriter writes them, the ItemProcessor provides access to transform or apply other business processing. If, while processing the item, it is determined that the item is not valid, returning null indicates that the item should not be written out.

transaction-manager – Spring’s PlatformTransactionManager that will be used to begin and commit transactions during processing.

commit-interval – The number of items that will be processed before the transaction is committed.

The Tasklet is a simple interface that has one method, execute, which will be a called repeatedly by the TaskletStep until it either returns RepeatStatus.FINISHED or throws an exception to signal a failure. Each call to the Tasklet is wrapped in a transaction. Tasklet implementors might call a stored procedure, a script, or a simple SQL update statement. To create a TaskletStep, the ‘ref’ attribute of the <tasklet/> element should reference a bean defining a Tasklet object; no <chunk/> element should be used within the <tasklet/>

JobRepository is the persistence mechanism. It provides CRUD operations for JobLauncher, Job, and Step implementations. When a Job is first launched, a JobExecution is obtained from the repository, and during the course of execution StepExecution and JobExecution implementations are persisted by passing them to the repository.

JobLauncher represents a simple interface for launching a Job with a given set of JobParameters.

A batch namespace has been provided for ease of configuration and as long as the batch namespace has been declared, any of its elements can be used.

<?xml version="1.0" encoding="UTF-8"?>
<beans:beans xmlns="http://www.springframework.org/schema/batch"
	xmlns:beans="http://www.springframework.org/schema/beans" 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.xsd
           http://www.springframework.org/schema/batch
           http://www.springframework.org/schema/batch/spring-batch.xsd">

	<beans:import resource="classpath:spring-datasource.xml" />
	<beans:import resource="classpath:spring-beans.xml" />

	<job id="ioSampleJob">
		<step id="step1">
			<tasklet transaction-manager="transactionManager">
				<chunk reader="cvsFileItemReader" writer="mySqlItemWriter"
					processor="itemProcessor" commit-interval="2" />
			</tasklet>
		</step>
	</job>

	<beans:bean id="cvsFileItemReader"
		class="org.springframework.batch.item.file.FlatFileItemReader">
		<beans:property name="resource" value="classpath:person.csv" />
		<beans:property name="lineMapper">
			<beans:bean
				class="org.springframework.batch.item.file.mapping.DefaultLineMapper">
				<beans:property name="lineTokenizer">
					<beans:bean
						class="org.springframework.batch.item.file.transform.DelimitedLineTokenizer">
						<beans:property name="names" value="id,firstName,lastName" />
					</beans:bean>
				</beans:property>
				<beans:property name="fieldSetMapper">
					<beans:bean	class="org.springframework.batch.item.file.mapping.BeanWrapperFieldSetMapper">
						<beans:property name="prototypeBeanName" value="person" />
					</beans:bean>
				</beans:property>
			</beans:bean>
		</beans:property>
	</beans:bean>
	
	<beans:bean id="mySqlItemWriter" class="org.springframework.batch.item.database.JdbcBatchItemWriter">
		<beans:property name="dataSource" ref="dataSource" />
		<beans:property name="sql">
			<beans:value>
	            <![CDATA[
	            	insert into person(id,firstName,lastName)
								values (:id, :firstName, :lastName)
	            ]]>
			</beans:value>
		</beans:property>
		<beans:property name="itemSqlParameterSourceProvider">
			<beans:bean
				class="org.springframework.batch.item.database.BeanPropertyItemSqlParameterSourceProvider" />
		</beans:property>
	</beans:bean>

	<!-- stored job-meta in database -->
	<beans:bean id="jobRepository"
		class="org.springframework.batch.core.repository.support.JobRepositoryFactoryBean">
		<beans:property name="dataSource" ref="dataSource" />
		<beans:property name="transactionManager" ref="transactionManager" />
		<beans:property name="databaseType" value="mysql" />
	</beans:bean>

	<beans:bean id="transactionManager"
		class="org.springframework.batch.support.transaction.ResourcelessTransactionManager" />

	<beans:bean id="jobLauncher"
		class="org.springframework.batch.core.launch.support.SimpleJobLauncher">
		<beans:property name="jobRepository" ref="jobRepository" />
	</beans:bean>

</beans:beans>

Step 7. Create below class for launching spring batch job.

package com.roytuts.spring.batch.runner;

import org.springframework.batch.core.Job;
import org.springframework.batch.core.JobExecution;
import org.springframework.batch.core.JobParameters;
import org.springframework.batch.core.launch.JobLauncher;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class SpringBatchRunner {

	public static void main(String[] args) {
		ApplicationContext context = new ClassPathXmlApplicationContext(new String[] { "spring-batch.xml" });

		JobLauncher jobLauncher = (JobLauncher) context.getBean("jobLauncher");
		Job job = (Job) context.getBean("ioSampleJob");

		try {
			JobExecution execution = jobLauncher.run(job, new JobParameters());
			System.out.println("Job Status : " + execution.getStatus());
		} catch (Exception ex) {
			ex.printStackTrace();
		}

		System.out.println("Done");
	}

}

Step 8. Create below person table in MySQL database

USE `roytuts`;

/*Table structure for table `person` */

DROP TABLE IF EXISTS `person`;

CREATE TABLE `person` (
  `id` int(11) NOT NULL,
  `firstName` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `lastName` varchar(20) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Step 9. Run the above class, you will see the below output.

Jan 14, 2017 5:50:54 PM org.springframework.context.support.ClassPathXmlApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@17dca04: startup date [Sat Jan 14 17:50:54 IST 2017]; root of context hierarchy
Jan 14, 2017 5:50:54 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [spring-batch.xml]
Jan 14, 2017 5:50:55 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [spring-datasource.xml]
Jan 14, 2017 5:50:55 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [spring-beans.xml]
Jan 14, 2017 5:50:55 PM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
INFO: Loaded JDBC driver: com.mysql.jdbc.Driver
Jan 14, 2017 5:50:55 PM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript
INFO: Executing SQL script from class path resource [org/springframework/batch/core/schema-drop-mysql.sql]
Jan 14, 2017 5:50:56 PM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript
INFO: Executed SQL script from class path resource [org/springframework/batch/core/schema-drop-mysql.sql] in 440 ms.
Jan 14, 2017 5:50:56 PM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript
INFO: Executing SQL script from class path resource [org/springframework/batch/core/schema-mysql.sql]
Jan 14, 2017 5:50:56 PM org.springframework.jdbc.datasource.init.ScriptUtils executeSqlScript
INFO: Executed SQL script from class path resource [org/springframework/batch/core/schema-mysql.sql] in 882 ms.
Jan 14, 2017 5:50:57 PM org.springframework.oxm.jaxb.Jaxb2Marshaller createJaxbContextFromClasses
INFO: Creating JAXBContext with classes to be bound [class com.roytuts.spring.batch.model.Person]
Jan 14, 2017 5:50:57 PM org.springframework.batch.core.launch.support.SimpleJobLauncher afterPropertiesSet
INFO: No TaskExecutor has been set, defaulting to synchronous executor.
Jan 14, 2017 5:50:57 PM org.springframework.batch.core.launch.support.SimpleJobLauncher run
INFO: Job: [FlowJob: [name=ioSampleJob]] launched with the following parameters: [{}]
Jan 14, 2017 5:50:57 PM org.springframework.batch.core.job.SimpleStepHandler handleStep
INFO: Executing step: [step1]
Processing: Person [id=1000, firstName=soumitra, lastName=roy]
Processing: Person [id=1001, firstName=souvik, lastName=sanyal]
Processing: Person [id=1002, firstName=arup, lastName=chatterjee]
Processing: Person [id=1003, firstName=suman, lastName=mukherjee]
Processing: Person [id=1004, firstName=debina, lastName=guha]
Processing: Person [id=1005, firstName=liton, lastName=sarkar]
Processing: Person [id=1006, firstName=debabrata, lastName=poddar]
Jan 14, 2017 5:50:58 PM org.springframework.batch.core.launch.support.SimpleJobLauncher run
INFO: Job: [FlowJob: [name=ioSampleJob]] completed with the following parameters: [{}] and the following status: [COMPLETED]
Job Status : COMPLETED
Done

In the above output you see the job name, step name and also which row item from csv file is being processed. You also see the sql scripts have been executed and below tables have been created in the MySQL database with job details.


spring batch tables
You will also see the person table has been populated with csv file data.


spring batch person

Thanks for reading.

Tags:

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on Roy Tutorials | TwitterFacebook Google PlusLinkedin | Reddit

1 thought on “Spring Batch – CSV to MySQL Database

  1. Thanks for your code share, We are running application with spring boot 2.0.4, on Eclipse IDE maven build with the Sql server datasource, getting below error. Did you tried this application by runnign with the sql server datasource? Please share the code base if you able to run this with sql server datasource successfully. Thanks in advance

    o.s.boot.autoconfigure.jdbc.Datasource.initailizer disabled(not running DDL scripts)

    at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49)
    Caused by: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE
    where JOB_NAME = ? order by JOB_INSTANCE_ID desc]; nested exception is java.sql.SQLServerException: Invalid object name ‘BATCH_JOB_INSTANCE’.

Leave a Reply

Your email address will not be published. Required fields are marked *