Spring Batch: ItemProcessor query Database?

Question

I have a scenario where I need to parse flat files and process those records into mysql database inserts (schema already exists).

I'm using the FlatFileItemReader to parse the files and a JdbcCursorItemWriter to insert in the database.

I'm also using an ItemProcessor to convert any column values or skip records that I don't want.

My problem is, some of those inserts need to have a foreign key to some other table that already has data into it.

So I was thinking to do a select to retrieve the ID and update the pojo, inside the ItemProcessor logic.

Is this the best way to do it? I can consider alternatives as I'm just beginning to write all this.

Thanks!

score 4 · Accepted Answer · answered May 28 '15 at 14:21

4

The ItemProcessor in a Spring Batch step is commonly used for enrichment of data and querying a db for something like that is common.

For the record, another option would be to use a sub select in your insert statement to get the foreign key value as the record is being inserted. This may be a bit more performant give it removes the additional db hit.

answered May 28 '15 at 14:21

Michael Minella

20,843
4
55
67

Thanks Michael. I imagined that doing something like this would be common. Didn't thought about the sub select in the insert, nice performance tip, thanks. – hell--raiser May 28 '15 at 14:40

score 0 · Answer 2 · answered May 29 '15 at 12:18

for the batch process - if you require any where you can call use below method anywhere in batch using your batch listeners

well the below piece of code which I wrote , worked for me --

In you Main class - load your application context in a static variable - APP_CONTEXT

If you are not using XML based approach - then get the dataSource by auto-wiring it and then you can use below code -

 Connection conn = null;
PreparedStatement pstmt= null;

try {


DataSource dataSource = (DataSource) Main.APP_CONTEXT
        .getBean("dataSource");

 conn = dataSource.getConnection();
 pstmt = conn.prepareStatement(" your SQL query to insert ");

pstmtMstr.executeQuery();

} catch (Exception e) {

}finally{

    if(pstmt!=null){
        pstmt.close();
    }if(conn!=null){
        conn.close();
    }


}

Spring Batch: ItemProcessor query Database?

2 Answers2