How to Read relational data from 3 tables in spring batch

Question

I have a requirement to read data from 3 tables (CONTRACT, LANE, RATE). where contract can be multiple and and for each CONTRACT I can have multiple LANE and For each LANE I can have multiple RATES. Tables has foreign key relation.

I want to read the data from table and set into Java Objects in related manner. Java POJO are like.

  public class Contract extends TICSection {

    private RTRate rtRate;

    private List<Lane> lanes = new ArrayList<Lane>();

    private String effectiveDate;
    private String expirationDate;
    private String contractNumber;
    private String contractTitle;
  }

Lane

public class Lane extends TICSection {  

private List<Rate> rate = new ArrayList<Rate>();
private String laneNumber ;

private String laneId ;
private String opFlag ;

private String depCntry ;
private String destCntry;
}   

Rate

public class Rate extends TICSection {

private String priceComponent ;
private String price ;

private String currency ;
private String quantityUnit ;

private String priceUnit ;
}   

In java object Contract I have list of Lanes and in Lane I have list of Rates. I want all the data from 3 corresponding tables in this format.

Please help me how can I set the data in this format using ItemReader.

Also if i set commit-interval=10 in my context xml how can I make sure spring batch reads and processes only 1 Contract and 10 Lane at a time. Is it possible ?

Problem is I am trying to read data from 3 different tables and I want to map them to Java objects in the similar way Like Contract having list of lanes associated to it and lane having list of rates associated to it. so that when I process the records I get all related lane and rates data.

My configuration looks like this for 1 table

<bean id="pagingItemReader"
    class="org.springframework.batch.item.database.JdbcPagingItemReader"
    scope="step">
    <property name="dataSource" ref="dataSource" />
    <property name="queryProvider">
        <bean
            class="org.springframework.batch.item.database.support.SqlPagingQueryProviderFactoryBean">
            <property name="dataSource" ref="dataSource" />
            <property name="selectClause" value="*" />
            <property name="fromClause" value="from gtn_lineitem" />
            <property name="whereClause" value="record_status in ('O','E') and contract_seq = :contractSeq" />
            <property name="sortKey" value="contract_seq" />
        </bean>
    </property>
    <!-- Inject via the ExecutionContext in rangePartitioner -->
    <property name="parameterValues">
      <map>
        <entry key="contractSeq" value="#{stepExecutionContext[contractSeq]}"/>
        <entry key="fromId" value="#{stepExecutionContext[fromId]}" />
        <entry key="toId" value="#{stepExecutionContext[toId]}" />
      </map>
    </property>
    <property name="pageSize" value="#{stepExecutionContext[laneCount]}" />
    <property name="rowMapper">
        <bean class="com.cat.srr.gtn.dao.mapper.GTNContractRowMapper" />
    </property>
</bean>


<!-- Actual Job -->


<bean id="contractReadPartitioner" class="com.cat.srr.gtn.batch.partitioner.ContractReadPartitioner">
    <property name="contractDao" ref="contractDao"></property>
</bean>
<bean id="taskExecutor" class="org.springframework.core.task.SyncTaskExecutor" />
<bean id="contractProcessor" class="com.cat.srr.gtn.batch.ContractProcessor" scope="step">
    <property name="threadName" value="#{stepExecutionContext[name]}"></property>
</bean>


<batch:job id="partitionJob" xmlns="http://www.springframework.org/schema/batch">
    <batch:step id="masterStep">
        <batch:partition step="slaveStep" partitioner="contractReadPartitioner">
            <batch:handler grid-size="1" task-executor="taskExecutor"/>
        </batch:partition>
    </batch:step>
    <batch:listeners>
        <batch:listener ref="jobListener" />
    </batch:listeners>
</batch:job>

 <batch:step id="slaveStep">
        <batch:tasklet transaction-manager="transactionManager">
            <batch:chunk reader="pagingItemReader" writer="contractWriter"
                processor="contractProcessor" commit-interval="1" />
        </batch:tasklet>
 </batch:step>

But if declare 3 readers for 3 different table how to merge the data into java objects. Or If I write a join query to select all the related data then How the Row Mapper will merge the results. What should be the correct approach.

public class GTNContractRowMapper implements RowMapper<GTNContract>{

@Override
public GTNContract mapRow(ResultSet rs, int arg1) throws SQLException {
    GTNContract contract = new GTNContract();
    contract.setContractSeq(rs.getString("CONTRACT_SEQ"));
    contract.setContractNumber(rs.getString("GTN_CONTRACT_ID"));
    contract.setContractTitle(rs.getString("CONTRACT_ID"));
    contract.setCarrierName(rs.getString("CARRIER_NAME"));
    contract.setCarrierSCAC(rs.getString("CARRIER_SCAC"));
    contract.setModeName(rs.getString("MODE_NM"));
    contract.setEffectiveDate(rs.getString("TERM_BEGIN"));
    contract.setExpirationDate(rs.getString("TERM_END"));


    return contract;
}

}

Answer 1

Actually I need information for your Processor and Writer as well. Base on that information, I can give appropriate solution.

But with Reader information you gave, I would like to go with the 2 below approaches

A. No Custom Reader 1. Partitioner will handle at Contract level. 2. Reader will read LANE information base on each contract. 3. Processor will get RATE information base on each contract and lane

So at Processor level, we have each Contract/Lane/Rate.

I'm waiting for more information from your processor and writer to provide accurate approach.

B. Custom Reader The custom one, you can build your own logic with a query which joins 3 tables with GROUP BY clause. And from Java, you loop on the result set and build your Contract objects.

Thanks, Nghia