spring batch mysql_Spring Batch Example – CSV File To MySQL Database (四)

In this tutorial, we will show you how to configure a Spring Batch job to read data from a CSV file into a database.

Tools and libraries used :

Maven 3

Eclipse 4.2

JDK 1.6

Spring Core 3.2.2.RELEASE

Spring Batch 2.2.0.RELEASE

MySQL Java Driver 5.1.25

1. Java Project

Create a Java Project with Maven

$ mvn archetype:generate -DgroupId=com.mkyong -DartifactId=SpringBatchExample

-DarchetypeArtifactId=maven-archetype-quickstart -DinteractiveMode=false

Convert to Eclipse project, and imports it into Eclipse IDE.

$ cd SpringBatchExample/

$ mvn eclipse:eclipse

2. Project Dependencies

Declares all project dependencies in pom.xml.

pom.xml

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0

http://maven.apache.org/maven-v4_0_0.xsd">

4.0.0

com.mkyong

SpringBatchExample

jar

1.0-SNAPSHOT

SpringBatchExample

http://maven.apache.org

1.6

3.2.2.RELEASE

2.2.0.RELEASE

5.1.25

org.springframework

spring-core

${spring.version}

org.springframework

spring-jdbc

${spring.version}

org.springframework.batch

spring-batch-core

${spring.batch.version}

org.springframework.batch

spring-batch-infrastructure

${spring.batch.version}

mysql

mysql-connector-java

${mysql.driver.version}

spring-batch

org.apache.maven.plugins

maven-eclipse-plugin

2.9

true

false

org.apache.maven.plugins

maven-compiler-plugin

2.3.2

${jdk.version}

${jdk.version}

3. Project Directory Structure

Review the final project structure.

4. CSV File

This is the csv file in the resource folder.

report.csv

Date,Impressions,Clicks,Earning

6/1/13,"139,237",37,227.21

6/2/13,"149,582",55,234.71

6/3/13,"457,425",132,211.48

6/4/13,"466,870",141,298.40

6/5/13,"472,385",194,281.35

......

5. MySQL Database

Defines a “dataSource” bean for MySQL database. The jdbc:initialize-database is used to

create the metadata tables automatically, Spring Batch need it to store the job’s detail.

resources/spring/batch/config/database.xml

xmlns:jdbc="http://www.springframework.org/schema/jdbc"

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-3.2.xsd

http://www.springframework.org/schema/jdbc

http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd">

class="org.springframework.jdbc.datasource.DriverManagerDataSource">

class="org.springframework.batch.support.transaction.ResourcelessTransactionManager" />

6. Spring Batch Core Setting

Defines jobRepository and jobLauncher.

resources/spring/batch/config/context.xml

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-3.2.xsd">

class="org.springframework.batch.core.repository.support.JobRepositoryFactoryBean">

class="org.springframework.batch.core.launch.support.SimpleJobLauncher">

7. Spring Batch Jobs

This is the main xml file to configure the Spring batch job. This job-report.xml file define

a job to read a report.csv file, match it to report plain

pojo and write the data into MySQL database.

Read the comment, it should be self-explanatory. Btw, remember create the “RAW_REPORT” table manually.

resources/spring/batch/jobs/job-report.xml

xmlns:batch="http://www.springframework.org/schema/batch"

xmlns:task="http://www.springframework.org/schema/task"

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">

commit-interval="2">

class="org.springframework.batch.item.file.transform.DelimitedLineTokenizer">

class="org.springframework.batch.item.file.mapping.BeanWrapperFieldSetMapper">

class="org.springframework.batch.item.database.JdbcBatchItemWriter">

insert into RAW_REPORT(DATE,IMPRESSIONS,CLICKS,EARNING)

values (:date, :impressions, :clicks, :earning)

]]>

class="org.springframework.batch.item.database.BeanPropertyItemSqlParameterSourceProvider" />

com/mkyong/model/Report.java

package com.mkyong.model;

public class Report {

private String Date;

private String Impressions;

private String Clicks;

private String Earning;

//getter and setter methods

}

Note

For detail explanation, please refer to this Spring batch references.

8. Run It

Loads everything and run it jobLauncher. This is the simplest way to start and test it,

but, in real life, you may need to launch it with scheduler frameworks like Spring task, Quartz or system scheduler like “cron” command (I will show you in coming tutorials).

com/mkyong/App.java

package com.mkyong;

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 App {

public static void main(String[] args) {

String[] springConfig =

{"spring/batch/config/database.xml",

"spring/batch/config/context.xml",

"spring/batch/jobs/job-report.xml"

};

ApplicationContext context =

new ClassPathXmlApplicationContext(springConfig);

JobLauncher jobLauncher = (JobLauncher) context.getBean("jobLauncher");

Job job = (Job) context.getBean("reportJob");

try {

JobExecution execution = jobLauncher.run(job, new JobParameters());

System.out.println("Exit Status : " + execution.getStatus());

} catch (Exception e) {

e.printStackTrace();

}

System.out.println("Done");

}

}

Output. The Spring Batch metadata tables are created, and the content of report.cvs is inserted

into database table “RAW_REPORT“.

Done.