Resultset metadata as header for CSV file in Spring batch

Question

I am writing a code to generate CSV file from result set of a SQL stored procedure. I made almost everything generic by using Spring Batch framework itself but except header.I want resultset's metadata as the header. I don't want to extend StoredProcedureItemReader and do it. Is there any class/item in Spring Batch framework to write a header like I asked above or is there any property in StoredProcedureItemReader or in FlatFileItemWriter to do this one. I want it generic because I want to use the same code for multiple Stored procedures.

Answer 1

spring Batch provides FlatFileHeaderCallback for creating file header. You need to implement the writeHeader(Writer writer) method.

In WriteHeader method you need to write Something like this.

1 Inject JdbcTemplate in that class

then

List<ColumList> list = jdbcTemplate.query("Select * From table where 1=2",new YourMapper()) 

In Mapper- from ResultSet Object you can get ResultSetMetaData*. Now using ResultSetMetaData you can get list of all ColumnNames. then return this list from mapper

Answer 2

implement FlatFileHeaderCallback like this

public class TestClass implements FlatFileHeaderCallback  {

    @Autowired
    private JdbcTemplate jdbcTemplate; //IF you have jdbcTemplate bean or use data source run the SQL


    @Override
    public void writeHeader(Writer writer) throws IOException {


        List<String> list = jdbcTemplate.query("Select * From table where 1=2",new YourMapper()) ;
        list.toString();
        writer.write(list.toString());      
    }

}

Mapper will be something like this

public class YourDBMapper implements ResultSetExtractor<List<String>> {

    List<String> list = new List<>();
    @Override
    public List<String> extractData(ResultSet rs){      

        ResultSetMetaData meta = rs.getMetaData();    

        for (int i = 1; i <=  meta.getColumnCount(); i++) 
            list.add(meta.getColumnName(i));        

        return list;
    }

}

Hope this helps

Answer 3

Actually I had something like below in my code to get metadata

    @Scope("step")
    public class CustomColumnMapRowMapper extends ColumnMapRowMapper{
    private static boolean isMetadataSet = false;
    @Autowired
    private StepExecution stepExecution;
    @Autowired
    private String delimiter;

    private String metadata="";
    @Override
    public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
        System.out.println("Inside custom row mapper "+this.getClass().getName());
        ResultSetMetaData rsmd = rs.getMetaData();

        int columnCount = rsmd.getColumnCount();
        Map<String, Object> mapOfColValues = createColumnMap(columnCount);
        for (int i = 1; i <= columnCount; i++) {
            String key = getColumnKey(JdbcUtils.lookupColumnName(rsmd, i));
            if(!this.isMetadataSet){
                if(i==columnCount){
                    metadata=metadata.concat(key);
                    break;
                }
                metadata=metadata.concat(key).concat(delimiter);
            }
            Object obj = getColumnValue(rs, i);
            mapOfColValues.put(key, obj);
        }
        if(!this.isMetadataSet){
            this.stepExecution.getJobExecution().getExecutionContext().put("metadata", this.metadata);
            this.isMetadataSet = true;
            System.out.println("Metadata retrieved is "+this.stepExecution.getJobExecution().getExecutionContext().get("metadata"));
        }

        //System.out.println("Metadata is "+this.metadata);
        return mapOfColValues;
    }

It is working but the problem here is header is called before the above rowmapper. Please see below for the CustomFlatFileHeader

public class CustomFlatFileHeader implements FlatFileHeaderCallback{

    @Autowired
    private StepExecution stepExecution;

    @Override
    public void writeHeader(Writer writer) throws IOException {
        System.out.println("Metdata in col header");
        writer.write(this.stepExecution.getJobExecution().getExecutionContext().get("metadata").toString());
    }

    public StepExecution getStepExecution() {
        return stepExecution;
    }

    public void setStepExecution(StepExecution stepExecution) {
        System.out.println("Inside step exec setting ");
        this.stepExecution = stepExecution;
    }
}

Please see to my job configuration

<batch:job id="ReportJob">
    <batch:step id="step1">
        <batch:tasklet transaction-manager="transactionManager">
            <batch:chunk reader="databaseItemReader" writer="flatFileItemWriter"
                processor="itemProcessor" commit-interval="100" />
        </batch:tasklet>
    </batch:step>
    <batch:listeners>
        <batch:listener ref="jobListener" />
    </batch:listeners>
</batch:job>