Spring Batch 2.0 - Part III - From Database to Flat File

In Part-II of this series on Spring Batch, I went through an example of reading from a flat file and persisting into the database. In this article I will go through the reverse. Read 200,000 rows from the database and export it into a comma separated flat file.

The export from the database to the flat file took around 10 seconds. That is excellent for Java-based batch processing. Again I must point out that this is relatively fast since I am using a local MySQL database and there is no processing related logic being performed during the entire process.

The file is a comma separated file with format => receiptDate,memberName,checkNumber,checkDate,paymentType,depositAmount,paymentAmount,comments

DDL for the database table :
 create table ledger (
 ID INT NOT NULL AUTO_INCREMENT,
 rcv_dt date,
 mbr_nm VARCHAR(100) not null,
 chk_nbr VARCHAR(10) not null,
 chk_dt date,
 pymt_typ VARCHAR(50) not null,
 dpst_amt double,
 pymt_amt double,
 comments VARCHAR(100),
 PRIMARY KEY (ID)
 

Here is the spring application context xml file...

<?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:aop="http://www.springframework.org/schema/aop"

      xmlns:tx="http://www.springframework.org/schema/tx"xmlns:context="http://www.springframework.org/schema/context"

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

 

      xsi:schemaLocation="

    http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd

    http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.0.xsd

    http://www.springframework.org/schema/aop www.springframework.org/schema/aop/spring-aop-2.0.xsd

    http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-2.0.xsd

    http://www.springframework.org/schema/batch http://www.springframework.org/schema/batch/spring-batch-2.0.xsd

   http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd" >

 

      <!-- 1) USE ANNOTATIONS TO CONFIGURE SPRING BEANS -->

      <context:component-scan base-package="com.batch"/>

 

      <!-- 2)DATASOURCE, TRANSACTION MANAGER AND JDBC TEMPLATE  -->

      <bean id="dataSource"

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

            <property name="driverClassName"value="com.mysql.jdbc.Driver" />

            <property name="url"value="jdbc:mysql://localhost/seamdb" />

            <property name="username"value="root" />

            <property name="password"value="root" />

      </bean>

 

      <bean id="transactionManager"

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

            <property name="dataSource"ref="dataSource" />

      </bean>

      <tx:annotation-driven transaction-manager="transactionManager"/>

 

      <bean id="jdbcTemplate"class="org.springframework.jdbc.core.JdbcTemplate">

            <property name="dataSource"ref="dataSource" />

      </bean>

 

 

      <!-- 3) JOBREPOSITORY - WE USE IN-MEMORY REPOSITORY FOR OUR EXAMPLE -->

      <bean id="jobRepository"

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

            <property name="transactionManager"ref="transactionManager" />

      </bean>

 

      <!-- 4)LAUNCH JOBS FROM A REPOSITORY -->

      <bean id="jobLauncher"

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

            <property name="jobRepository"ref="jobRepository" />

      </bean>

 

 

      <!--

            5) Define the job and its steps. In our case I use one step. Configure

            its readers and writers

      -->

      <batch:job id="simpleJob">

            <batch:step id="step1">

                  <batch:tasklet>

                        <batch:chunk reader="cursorReader"writer="flatFileWriter"

                              commit-interval="1000" />

                  </batch:tasklet>

            </batch:step>

      </batch:job>

 

      <!--======================================================= -->

      <!--  6) READER -->

      <!--======================================================= -->

      <bean id="cursorReader"

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

            <property name="dataSource"ref="dataSource" />

            <property name="sql"value="select * from ledger" />

            <property name="rowMapper"ref="ledgerRowMapper" />

      </bean>

 

 

      <!--======================================================= -->

      <!--  7) WRITER -->

      <!--======================================================= -->

      <bean id="flatFileWriter"class="org.springframework.batch.item.file.FlatFileItemWriter">

            <property name="resource"value="file:c:/temp/ledgers-output.txt" />

            <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="id,receiptDate,memberName" />

                              </bean>

                        </property>

                  </bean>

            </property>

      </bean>

</beans>

 

 
  • 1 through 4 are the same as the previous blog.
  • 5 - Defines the step job and its steps.
  • 6 - Registers a JdbcCursorItemReader which will read the rows from the database. It will then write them out to the flat file. The latest version also has a new reader JdbcPagingItemReader. This is a better option since it will read a predefined set of rows rather than making round trips for each row.
  • 8 - Configure the writer to write to a flat file

Here is the Java code:
 
// ====================================================
// Ledger BEAN
// ====================================================
package com.batch.todb;

import java.util.Date;

public class Ledger {
    private int id;
    private Date receiptDate;
    private String memberName;
    private String checkNumber;
    private Date checkDate;
    private String paymentType;
    private double depositAmount;
    private double paymentAmount;
    private String comments;

    public int getId() {
        return id;
    }

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

    public Date getReceiptDate() {
        return receiptDate;
    }

    public void setReceiptDate(Date receiptDate) {
        this.receiptDate = receiptDate;
    }

    public String getMemberName() {
        return memberName;
    }

    public void setMemberName(String memberName) {
        this.memberName = memberName;
    }

    public String getCheckNumber() {
        return checkNumber;
    }

    public void setCheckNumber(String checkNumber) {
        this.checkNumber = checkNumber;
    }

    public Date getCheckDate() {
        return checkDate;
    }

    public void setCheckDate(Date checkDate) {
        this.checkDate = checkDate;
    }

    public String getPaymentType() {
        return paymentType;
    }

    public void setPaymentType(String paymentType) {
        this.paymentType = paymentType;
    }

    public double getDepositAmount() {
        return depositAmount;
    }

    public void setDepositAmount(double depositAmount) {
        this.depositAmount = depositAmount;
    }

    public double getPaymentAmount() {
        return paymentAmount;
    }

    public void setPaymentAmount(double paymentAmount) {
        this.paymentAmount = paymentAmount;
    }

    public String getComments() {
        return comments;
    }

    public void setComments(String comments) {
        this.comments = comments;
    }
}
 
 
// ====================================================
// ROW MAPPER TO CONVERT DATABASE RECORD TO JAVA OBJECT
// ====================================================
package com.batch.fromdb;

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

import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import com.batch.todb.Ledger;

@Component("ledgerRowMapper")
public class LedgerRowMapper implements RowMapper {
    public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
        Ledger ledger = new Ledger();
        ledger.setId(rs.getInt("id"));
        ledger.setReceiptDate(rs.getDate("rcv_dt"));
        ledger.setMemberName(rs.getString("mbr_nm"));
        ledger.setCheckNumber(rs.getString("chk_nbr"));
        ledger.setCheckDate(rs.getDate("chk_dt"));
        ledger.setPaymentType(rs.getString("pymt_typ"));
        ledger.setDepositAmount(rs.getDouble("dpst_amt"));
        ledger.setPaymentAmount(rs.getDouble("pymt_amt"));
        ledger.setComments(rs.getString("comments"));
        return ledger;
    }

}

// ====================================================
// JUNIT CLASS
// ====================================================
package com.batch.fromdb;

import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.batch.core.Job;
import org.springframework.batch.core.JobParameters;
import org.springframework.batch.core.launch.JobLauncher;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.AbstractDependencyInjectionSpringContextTests;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.util.StopWatch;

@ContextConfiguration(locations = "classpath:com/batch/fromdb/contextFromDB.xml")
@RunWith(SpringJUnit4ClassRunner.class)
public class FromDBBatchTestCase extends
		AbstractDependencyInjectionSpringContextTests {

	private final static Logger logger = Logger
			.getLogger(FromDBBatchTestCase.class);

	@Autowired
	private JobLauncher launcher;

	@Autowired
	private Job job;
	private JobParameters jobParameters = new JobParameters();

	@Before
	public void setup() {
		PropertyConfigurator
				.configure("c:/mathew/springbatch2/src/com/batch/log4j.properties");
	}

	@Test
	public void testLaunchJob() throws Exception {
		StopWatch sw = new StopWatch();
		sw.start();
		launcher.run(job, jobParameters);
		sw.stop();
		logger.info(">>> TIME ELAPSED:" + sw.prettyPrint());
	}

	@Autowired
	public void setLauncher(JobLauncher bootstrap) {
		this.launcher = bootstrap;
	}

	@Autowired
	public void setJob(Job job) {
		this.job = job;
	}
}


After running the test case, you will see a file c:\temp\ledgers-output.txt with 200,000 rows.
INFO FromDBBatchTestCase:44 - >>> TIME ELAPSED:StopWatch '': running time (millis) = 8927


Download Project Files for Part I, II & III:

Here is the Eclipse project containing source code for all three parts (with dependencies): springbatch.jar






 del.icio.us  Stumbleupon  Technorati  Digg 

Posted by Mathew Thomas at 2/3/2009 1:30 AM
Categories: Spring
 

What did you think of this article?




Trackbacks
Trackback specific URL for this entry
  • Trackbacks are closed for this entry.
Comments
Display comments as (Linear | Threaded)

  • 2/25/2009 1:35 AM Tax2008 wrote:
    I have a large application on a mySQL platform - consisting of several different tables - I need to export to flat files and also import it back at frequent intervals. Thanks for the post, it made interesting reading.
    Reply to this
    1. 2/28/2009 6:28 PM Mathew Thomas wrote:
      Tax2008 (nice one there). When you settle on something I'd sure like to know what it is. Sometimes java can do a lot (if designed right) ... sometimes you may have to hand it to the database to crunch through faster. Just curious to know what your final selection would be. thx.

      Reply to this
  • 3/19/2009 9:05 AM java wrote:
    Hi,
    Thanks for detailed explanation.
    I am new to Spring Batch

    I need to convert a xml file to a comma delimeted flat file(.csv file), XML file will be input resource and expected ouput resource would be CSV flat file.
    I am using Spring Batch 1.4

    I am not sure how to achieve this? As i need to do this ASAP, could you please share some thoughts about how to do, or possibly share the resources you have done to achieve this.

    Awaiting for response.

    Thanks
    Reply to this
  • 8/12/2009 12:43 AM NewBie wrote:
    Hi,
    Thanks, very useful and easy to understand.
    Works fine with Spring Batch 1.1.4. Now i want to migrate my application to Spring Batch 2.0.0 and it does not work with it.

    Can you let us know the changes required for Spring Batch 2.0.0.

    Thanks In Advance.
    Reply to this
    1. 8/16/2009 8:45 PM Mathew Thomas wrote:
      I will definitely update the articles with Spring Batch 2.0. Just need to get the time...also nowadays i am having more fun flexing with Adobe Flex.

      Reply to this
  • 12/3/2009 3:13 AM fatih wrote:
    Thanks for this excellent spring batch example.Is there any example about spring bathch? For example, is there restartable ItemReader sample, when the power goes out, a batch job starts where it left off.

    Thanks in Advance.
    Reply to this
Leave a comment

Submitted comments will be subject to moderation before being displayed.

 Enter the above security code (required)

 Name

 Email (will not be published)

 Website

Your comment is 0 characters limited to 3000 characters.