Java - retrieving large amounts of data from a DB using iBatis

Question

I need to extract data from a DB2 table, run some processing on each returned row and output to a flat file. I'm using iBatis but found that using the queryForList I started getting out of memory errors, I'll be looking at 100k+ rows of data increasing.

I've looked at using queryWithRowHandler instead but the iBatis RowHandler interface doesn't throw an exception from its handleRow function so if it gets an error I can't properly report it back and stop iterating the rest of the data. It looks like I can throw a RuntimeException but that doesn't strike me as a neat way of doing things.

I'd like to be able to stop processing while throwing a meaningful Exception indicating whether the error occurred on the data manipulation, the file access or whatever.

Has anyone had experience with this approach or have an alternative solution using iBatis. I know I could look to do this without iBatis, just using JDBC, but as iBatis is used for all other DB access in my app I'd like to avail of this architecture if possible.

Answer 1

1) Create your own RowHandler interface with checked Exceptions in signature:

public interface MySpecialRowHandler {
    public void handleRow(Object row) 
        throws DataException, FileException, WhateverException;
}

2) Inherit (or even better, delegate ) from SqlMapDaoTemplate to add a new method that will manage your own handler with the same Exceptions in signature:

public class MySpecialTemplate extends SqlMapDaoTemplate {
    ...
    public void queryWithRowHandler(String id, 
        final MySpecialRowHandler myRowHandler
    ) throws DataException, FileException, WhateverException {
        // "holder" will hold the exception thrown by your special rowHandler
        // both "holder" and "myRowHandler" need to be declared as "final"
        final Set<Exception> holder = new HashSet<Exception>();
        this.queryWithRowHandler(id,new RowHandler() {
            public void handleRow(Object row) {
                try {
                    // your own row handler is executed in IBatis row handler
                    myRowHandler.handleRow(row);
                } catch (Exception e) {
                    holder.add(e);
                }
            }
        });
        // if an exception was thrown, rethrow it.
        if (!holder.isEmpty()) {
            Exception e = holder.iterator().next();
            if (e instanceof DataException)     throw (DataException)e;
            if (e instanceof FileException)     throw (FileException)e;
            if (e instanceof WhateverException) throw (WhateverException)e;
            // You'll need this, in case none of the above works
            throw (RuntimeException)e;
        }
    }
}

3) Your business code will look like this:

// create your rowHandler
public class Db2RowHandler implements MySpecialRowHandler {
    void handleRow(Object row) throws DataException, FileException, WhateverException {
        // what you would have done in ibatis RowHandler, with your own exceptions
    }
}
// use it.
MySpecialTemplate template = new MySpecialTemplate(daoManager);
try {
    template.queryWithRowHandler("selectAllDb2", new Db2RowHandler());
} catch (DataException e) {
    // ...
} catch (FileException e) {
    ...