JBatch examples: bulk loading from database to CSV file

In previous entry, we looked how to load data from CSV file to database. in this entry, we will look how to load data from database to CSV file. we’ll use JdbcItemReader to read data from database and CsvItemWriter to write data to as a CSV file.

Setup

In this setup we’ll use WildFly 9.0.0.CR1.

Assume we already have a table forex and data in H2 database that created and populated in previous entry.

For JdbcItemReader, we need an another datasource which is Non-JTA, references the same database to JTA one. for detail see this conversation.

data-source add \
 --name=MyNonJtaDS \
 --driver-name=h2 \
 --jndi-name=java:jboss/datasources/MyNonJtaDS \
 --user-name=sa \
 --password=sa \
 --connection-url=jdbc:h2:/tmp/myds;AUTO_SERVER=TRUE \
 --jta=false

Next, create job artifacts.

/src/main/resources/META-INF/batch-jobs/save-csv.xml

Note that MyNonJtaDS is used, not MyDS. and all of classes that used in the job are supplied within jberet-support.

<job id="save-csv" version="1.0" xmlns="http://xmlns.jcp.org/xml/ns/javaee">
    <step id="save">
        <chunk>
            <reader ref="jdbcItemReader">
                <properties>
                    <property name="dataSourceLookup" value="java:jboss/datasources/MyNonJtaDS"/>
                    <property name="sql"
                              value="SELECT symbol, ts, bid_open, bid_high, bid_low, bid_close, volume FROM forex ORDER BY symbol, ts"/>
                    <property name="beanType" value="java.util.List"/>
                </properties>
            </reader>
            <writer ref="csvItemWriter">
                <properties>
                    <property name="resource" value="#{jobParameters['resource']}"/>
                    <property name="header"
                              value="symbol, ts, bid_open, bid_high, bid_low, bid_close, volume"/>
                    <property name="beanType" value="java.util.List"/>
                </properties>
            </writer>
        </chunk>
    </step>
</job>

Run the job

Issue following command. this saves a CSV file into /tmp/save.csv:

curl 'http://localhost:8080/jbatch-example-1.0-SNAPSHOT/jbatch/rest/start/save-csv?resource=/tmp/save.csv'

After job execution is done, check the CSV file is created as expected:

symbol,ts,bid_open,bid_high,bid_low,bid_close,volume
USDJPY,2015-04-01 00:00:00.0,119.566,119.566,119.551,119.565,0
USDJPY,2015-04-01 00:01:00.0,119.566,119.581,119.565,119.579,0
USDJPY,2015-04-01 00:02:00.0,119.581,119.586,119.581,119.583,0
...

The project which used in this entry can be obtained from my GitHub repository.

Kohei Nozaki's blog