Java - retrieving large amounts of data from DB using iBatis
I need to extract data from a DB2 table, do some processing on each row returned, and output to a flat file. I am using iBatis but found that with queryForList I started to go out of memory, I will be looking at 100k + rows of data.
I have considered using queryWithRowHandler , but the iBatis RowHandler interface does not throw an exception from its handleRow function , so if this gets an error, I cannot properly report it and stop iterating over the rest of the data. It looks like I can throw a RuntimeException, but that doesn't affect me as a neat way of doing things.
I would like to stop processing by throwing a meaningful exception indicating whether an error occurred while manipulating data, accessing a file, or whatever.
Does anyone have experience with this approach or have an alternative solution using iBatis. I know I could do this without iBatis, just using JDBC, but since iBatis is used for all other DB access in my application, I would like to take advantage of this architecture if possible.
source to share
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) {
...
source to share