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

Question

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

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;
}

score 0 · Accepted Answer · edited Jun 13 '20 at 09:33

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.

it worked, thank you. My root element Settlement is appearing twice. I have a writer.setRootTagName ='Settlement' and XmlRootAnnotation on my Settlement model class. — Malume Ed, May 03 '20 at 12:16
Perfect. The writer.setRootTagName is there to set the root Element that holds all the Settlement Items. By default if you don't set it, it will be set to "root". I would do: writer.setRootTagName("**Settlements**") — youness.bout, May 03 '20 at 14:50
so there is no way I can remove 1 annotation as I have two root element tags. If I remove the XMLRoot on the model class Jaxb produces a compilation error. — Malume Ed, May 03 '20 at 15:52
If you do so, your XML document will be invalid. But there is a workaround by Overriding the behavior of StaxEventItemWriter. Check this [https://stackoverflow.com/a/49324643/2831242] . Hope this is what you are looking for. — youness.bout, May 03 '20 at 16:16
Can you please advise, I have to basically produce multiple xml files for each file_id per currency ( ie. usd,zar ect) these transactions are all in 1 DB table. Do I create a composite writer for each currency and on my Item Processor I filter for each different currency that I read from the DB. or Can I use multiple steps for each currency per file_id ? I have been struggling to find a Springbatch solution around this. — Malume Ed, May 16 '20 at 12:53
Can you please elaborate more. Does it mean that you have for each file_id multiple currencies, and you to write for each couple (file_id, currency) to a different file? — youness.bout, May 17 '20 at 00:11
Hi Youness, yes 100% correct. Sorry for the confusion and the filename resource will be different for each file and currency. For example I can recieve file_id=1 currency=USD needs to be 1 file 'USD20051701 with 01 the file sequence'. I can also get two files file_id 1 & 2 for Currency ='ZAR' and those need to be two files 'ZAR20051701' & 'ZAR20051702' 01 & 02 file sequences — Malume Ed, May 17 '20 at 06:33
I was thinking of using Composite ItemProcessor and Composite ItemWriter, seperate the result set from the reader per file per currency and create writer for the different currencies and Override the Resource method to use a custom filename resource for each currency file. I am just stuck as to how to execute this — Malume Ed, May 17 '20 at 06:52

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

1 Answers1