javaspring-batchspring-jdbcrowmapper

Springbatch reading data from two DB tables and write to XML file


I am new to spring batch and I wanted to get some guidance please around a issue I am experiencing. I have two db tables Settlement_Header ( which has the header info of the file) and Settlement_Detail( which is a list of all the transactions for that header file.)

The goal is to create a file that has 1 header and multiple details.

Current Output - The header appears on each record and I want it to appear once. I tried to include a condition in the Item Processor but it did not resolve my issue.

Current Output

Desired Output

Desired Output

This is my Itemreader

@Bean(destroyMethod="")
public JdbcCursorItemReader<Settlement> settlementreader(){
    JdbcCursorItemReader<Settlement> ItemReader = new JdbcCursorItemReader<>();
    ItemReader.setDataSource(dataSource);
    ItemReader.setSql("SELECT DISTINCT A.PROCESS_DATE,A.FILE_NAME,A.SERVICE,A.SUB_SERVICE,A.SENDER,A.RUN_MODE,A.CURRENCY,A.PROCESS_WINDOW_NO,B.RECORD_SEQ_NO,"
            + "B.AGENT_FROM,B.AGENT_TO,B.SETTLE_VOLUME,B.AMOUNT_CURRENCY,B.SETTLE_AMOUNT "
            + "FROM SETTLEMENT_HEADER A,SETTLEMENT_DETAIL B WHERE A.FILE_ID=B.FILE_ID");
    ItemReader.setRowMapper(new SettlementRowMapper());
    return ItemReader;
}

Writer

@Bean (destroyMethod="")
   public StaxEventItemWriter<Settlement> settlementwriter() throws Exception {
       StaxEventItemWriter<Settlement> ItemWriter = new StaxEventItemWriter<>();
       ItemWriter.setResource(new FileSystemResource(".../Settlement.xml")); 
       ItemWriter.setRootTagName("Settlement");
       ItemWriter.setMarshaller(marshaller);
       ItemWriter.afterPropertiesSet();
       return ItemWriter;
   }

Row Mapper

public class SettlementRowMapper implements RowMapper {

@Override
public Settlement mapRow(ResultSet rs, int rowNum) throws SQLException {

    Settlement Settlement = new Settlement ();
    List<SettlementHeader> settlementheader= new ArrayList<SettlementHeader>();
    List<SettlementDetail> settlementdetail = new ArrayList<SettlementDetail>();
    SettlementHeader header = new SettlementHeader();
    SettlementDetail detail = new SettlementDetail();
    SttlAmt sttlAmt = new SttlAmt();


    header.setPrcDte(rs.getDate("PROCESS_DATE").toLocalDate());
    header.setFilename(rs.getString("FILE_NAME"));
    header.setService(rs.getString("SERVICE"));
    header.setSubServ(rs.getString("SUB_SERVICE"));
    header.setSender(rs.getInt("SENDER"));
    header.setRunMode(rs.getString("RUN_MODE"));
    header.setCurrency(rs.getString("CURRENCY"));
    header.setPrcWndwNum(rs.getInt("PROCESS_WINDOW_NO"));
    detail.setSeqNumb(rs.getInt("RECORD_SEQ_NO"));
    detail.setAgntFrm(rs.getInt("AGENT_FROM"));
    detail.setAgntTo(rs.getInt("AGENT_TO"));
    detail.setSttlVol(rs.getInt("SETTLE_VOLUME"));
    sttlAmt.setCcy(rs.getString("AMOUNT_CURRENCY"));
    sttlAmt.setValue(rs.getDouble("SETTLE_AMOUNT"));
    detail.setSttlAmt(sttlAmt); 
    settlementheader.add(header);
    settlementdetail.add(detail);
    Settlement.setSettlementDetails(settlementdetail);
    Settlement.setSettlementHeader(settlementheader);
    return Settlement;
}

Solution

  • The behavior you are having is absolutely normal, since for each Item you read you have a Settlement object that contains one Header and one Detail.

    Your problem is a common one in the batch processing realm and with Spring Batch it is called "Driving Query Based ItemReaders", you can find more about that in here.

    The way I would approach your problem following this pattern is by creating a reader that only returns SettlementHeader Items, and add a processor that takes each Item (SettlementHeader) and transforms it into a Settlement Object by querying the database for the Settlement Details.

    Here is my solution:

    Step Items

    @Bean(destroyMethod = "")
    public JdbcCursorItemReader<SettlementHeader> settlementHeaderReader(DataSource dataSource) {
        JdbcCursorItemReader<SettlementHeader> ItemReader = new JdbcCursorItemReader<>();
        ItemReader.setDataSource(dataSource);
        ItemReader.setSql("SELECT DISTINCT A.FILE_ID, A.PROCESS_DATE, A.FILE_NAME, A.SERVICE, A.SUB_SERVICE, A.SENDER, A.RUN_MODE, A.CURRENCY, A.PROCESS_WINDOW_NO"
                            + "FROM SETTLEMENT_HEADER A");
        ItemReader.setRowMapper(new SettlementHeaderRowMapper());
        return ItemReader;
    }
    
    
    @Bean
    public ItemProcessor<SettlementHeader, Settlement> settlementHeaderProcessor(JdbcTemplate jdbcTemplate){
    
        return item -> {
            List<SettlementDetail> settlementDetails = jdbcTemplate.query("SELECT B.RECORD_SEQ_NO, B.AGENT_FROM,B.AGENT_TO,B.SETTLE_VOLUME,B.AMOUNT_CURRENCY,B.SETTLE_AMOUNT " +
                    "  FROM SETTLEMENT_DETAIL B WHERE ? = B.FILE_ID",
                    new Object[]{item.getFileId()},
                    new SettlementDetailRowMapper());
    
            Settlement settlement = new Settlement ();
    
            List<SettlementHeader> settlementheader= new ArrayList<SettlementHeader>();
            settlementheader.add(item);
    
            settlement.setSettlementDetails(settlementDetails);
            settlement.setSettlementHeader(settlementheader);
    
            return settlement;
        };
    }
    
    // Your writer should stay the same
    @Bean(destroyMethod = "")
    public StaxEventItemWriter<Settlement> settlementwriter() throws Exception {
        StaxEventItemWriter<Settlement> ItemWriter = new StaxEventItemWriter<>();
        ItemWriter.setResource(new FileSystemResource(".../Settlement.xml"));
        ItemWriter.setRootTagName("Settlement");
        ItemWriter.setMarshaller(marshaller);
        ItemWriter.afterPropertiesSet();
        return ItemWriter;
    }
    

    Row Mappers

    public class SettlementHeaderRowMapper implements RowMapper<SettlementHeader> {
    @Override
    public SettlementHeader mapRow(ResultSet rs, int i) throws SQLException {
        SettlementHeader header = new SettlementHeader();
    
        header.setFileId(rs.getInt("FILE_ID"));
        header.setPrcDte(rs.getDate("PROCESS_DATE").toLocalDate());
        header.setFilename(rs.getString("FILE_NAME"));
        header.setService(rs.getString("SERVICE"));
        header.setSubServ(rs.getString("SUB_SERVICE"));
        header.setSender(rs.getInt("SENDER"));
        header.setRunMode(rs.getString("RUN_MODE"));
        header.setCurrency(rs.getString("CURRENCY"));
        header.setPrcWndwNum(rs.getInt("PROCESS_WINDOW_NO"));
    
        return header;
    }
    

    }

    public class SettlementDetailRowMapper implements RowMapper<SettlementDetail> {
    @Override
    public SettlementDetail mapRow(ResultSet rs, int i) throws SQLException {
        SettlementDetail detail = new SettlementDetail();
        SttlAmt sttlAmt = new SttlAmt();
    
        detail.setSeqNumb(rs.getInt("RECORD_SEQ_NO"));
        detail.setAgntFrm(rs.getInt("AGENT_FROM"));
        detail.setAgntTo(rs.getInt("AGENT_TO"));
        detail.setSttlVol(rs.getInt("SETTLE_VOLUME"));
        sttlAmt.setCcy(rs.getString("AMOUNT_CURRENCY"));
        sttlAmt.setValue(rs.getDouble("SETTLE_AMOUNT"));
        detail.setSttlAmt(sttlAmt);
    
        return detail;
    }
    

    }

    I hope this helps you.