How to expose data from ResultSet to business layer

[michael warren]

I'm working on an application that loads data from a database table row by row, does some work on the data then moves on to the next row. There could be a lot of rows so don't want to copy the whole table into an array or RowSet or similar.

The current solution is to have a DAO that has a method that returns a VO containing data for one row, this method is called repeatedly to get data for the next row until it returns null, when a releaseResources method on the DAO must be called. This works, but it feels wrong to me - releasing of resources should always be the responsibility of the object that uses the resources in my opinion.

This is what the current implementation looks like (very simplified)

   
public class SomeDAO 
{
private ResultsSet results = null;
 
/**
 * Will return nextVO from DB, or null when no more rows are left.
 * IMPORTANT - must call realseResources when finished
 */
public SomeVO getSomeData()
{
SomeVo vo = null;
if (this.results == null)
{
buildData();// gets connection to database and runs query to get the ResultsSet results
}
if (this.results.next())
{
vo = new SomeVO();
vo.setSomeValue(results.getColumnAsString(SOME_COLUMN_NAME));
}
return vo;
}

public void releaseResources()
{
// closes resultset etc.
}
}

Whilst I don't like it, I am stuggling to think of a better solution. We could possibly have a method that returns batches of data (copying data into an array and closing the resultset for each batch), I would prefer this although I can see problems :
1.It would be less efficient, requiring a new query for each batch - whether this would be a noticeable perfomance problem would need to be investigated.
2.It would mean the business layer knowing about the key on the database used to determine the start and end of each batch, which again feels wrong.
3.In our case everything is happening in one transaction, but in general it could result in what should be in one transaction being split into these batches.

I've searched this forum, and the JDBC forum and found some topics that touch on this subject - but they always seem to end up advising to copy everthing from the resultset into memory, which is what I'm trying to avoid.

Am I missing something obvious, any ideas on a better design ?

thanks Mike

[Peer Reynders]

Hi mike,
Welcome to the JavaRanch. Please remove the period (".") from your display name. Thank You for Your cooperation. If You require further details please refer to the JavaRanch Naming Policy.
[ October 20, 2006: Message edited by: Peer Reynders ]

[michael warren]

sorry abuot that - have changed my name to michael, there was already a mike warren - I think a previous incarnation of me with an old email address, hence the mike. to differentiate.
[ October 20, 2006: Message edited by: michael warren ]

[Ilja Preuss]

Your problem sounds like a good match for the Adapter pattern to me: you DAO method could return an Iterator that wraps the ResultSet - on a call to next() it would fetch the next row from the ResultSet and create a VO/BO from it.

How does that sound to you?

[Stan James]

Would it help to reverse who's driving? Describe the whole process in one place close to the database:

public void driver( String query, Handler handler )
{
   connect
   execute query
   for each row of resultset
      handler.processOneRow( row )
   close connection
}

This gives you one place to write robust exception handling and connection management. You can reuse it with any kind of handler. I think Spring includes something like this if you want code somebody else has debugged.

[Peer Reynders]

I like both of Ilja's and Stan's suggestions.
(I wrote the following before I saw their responses. So here it is for what its worth.)

You may be interested in the Fast Lane Reader which uses a Value List Handler.
(I'm cringing because the use of "Value" in the pattern name. See: Differences between Value Object und Transfer Object)

But basically you are left with two options if you only get a subrange of the entire query. Either:

Cache the entire result set in the DAO in the hope that all of it will be used eventually before your disposal strategy (e.g. timeout) kicks in and you have to run the whole query again.

Only query the subrange you need.

It is usually simpler to hand out the "entire" list - but there may be reasons why you can't do that.
Another question you may need to ask is whether you need to enforce the layer separation to that extent - if the business objects are running on the same machine as the DAOs there may be nothing wrong with the explict "deallocation" of resources. Don't introduce additional complexity unless you know that the DAOs and business objects are/will be on seperate machines. And if that DAO supports an SLSB that is nothing but a transaction script (and most of them are) there will be a natural beginning and ending of the interaction between the two - so it should be clear where deallocation has to happen as long as you also cover exceptional circumstances.
The introduction of a helper object acting like an iterator may make it easier to detect the circumstances when deallocation becomes necessary. It could look ahead one record and deallocate when it hands out the last record and fails to obtain the (non-existent) following record (though a seperate dispose method would still be a good idea).
[ October 20, 2006: Message edited by: Peer Reynders ]

[michael warren]

Ilja, thanks for your reply - its probably better than what we've got now, but if I've understood you correctly I think that would still leave the user of the data responsible for calling a method to free up resources.

Stan, that makes great sense to me - bit irritated that I didn't think of it I'll have a look into implementing it in our next release...

Thanks

[michael warren]

Thanks Peer - missed your reply while composing my previous reply. Had a quick look at the links, but still think I favour Stan's suggestion for what we need to do, but will bare them in mind.

Whilst reviewing the code I did wonder about the use of the term VO.

[Frank Carver]

I have encountered problems like this often enough that I now prefer to use an "inverted" approach.

Instead of requiring the consumer of the data to manage the iteration through the results, I pass in a consumer which is invoked by the data framework for each call. The data framework manages all the connection and statement stuff, and closes everything down neatly when completed, even in error/exception cases.

A simplified example might be:

DatabaseWrapper dw = new DatabaseWrapper( connection details );
String script = "SELECT ...";
dw.query(script, new ResultRowListener() {
  public Object row(ResultSet results, int rowNumber) throws SQLException {
    doSomeStuff(results);
    return null;
  }
});

In practice I usually use it by creating a subclass of the raw ResultRowListener, so I can pass in a precreated object which has context and knows what to do with a row of results:

class MoreUseful {
  private StuffHandler handler;
 
  public MoreUseful(StuffHandler handler) {
    this.handler = handler;
  }
 
  public Object row(ResultSet results, int rowNumber) throws SQLException {
    handler.doSomeStuff(results.getString(1), results.getDate(2));
    return null;
  }
}
 
...
 
  DatabaseWrapper dw = new DatabaseWrapper( connection details );
  StuffHandler = new StuffHandler(...);
 
  dw.query(script, new MoreUseful(handler));

To stop the processing before the full set has been traversed, just return a non-null object from your row handler, which will in turn be returned from the "query" method call.

Code for my implementation of this concept is available in my Stringtree utilities project at sourceforge in the package org.stringtree.db.

I hope this helps

[Frank Carver]

That'll teach me to take so long composing a reply, my answer looks a lot like Stan's but more wordy. :roll:

[Ilja Preuss]

Originally posted by michael warren:
Ilja, thanks for your reply - its probably better than what we've got now, but if I've understood you correctly I think that would still leave the user of the data responsible for calling a method to free up resources.

Well, yes - unless you rely on clients always iterating over all rows, then the iterator could free the resources when the last element has been reached.

But I actually think that the solution proposed by Stan and Frank (also known as Internal Iterator) is far better...

[Stan James]

I was enamored of SAX HTML parsers at one moment, and made my resultset handler feel like a sax parser ... here's the interface

public interface ResultSetHandler
{
    public void startDocument();
    public void endDocument();
    public void startHeading();
    public void startHeadingCell( ColumnMetaData metaData );
    public void endHeading();
    public void startRow( int rowNum ); 
    public void endRow( int rowNum );
    public void startColumn( Object value, ColumnMetaData metaData );
}

The heading methods pass metadata about the columns so the handler could set up column headings, formatters, widths, etc for an ascii-mode report.

Document and Cell were not very good names, were they. And I have "start" without "end", Amen. Alt-Shift-R it is then.

[Peer Reynders]

Originally posted by Frank Carver:
I have encountered problems like this often enough that I now prefer to use an "inverted" approach.

... which for some reason I associate with this article:
Robert C. Martin: The Dependency Inversion Principle.
I think its because the inverted approach allows you to use interfaces for both the consumer and the producer.

[Stan James]

The caller must now implement an interface defined by the database utility package, so the caller depends on the utility. But it depended on JDBC before so there's nothing that new there. The flow of data is inverted, but I don't think the dependencies.

Caller ---> JDBC

Caller ---> Utility ----> JDBC

Does that make sense?

Dependency inversion does happen someplace we can't readily see:

Caller ---> JDBC <---- Vendor Driver Implementation

[Stan James]

A few hours later ... we could introduce DIP neatly enough though if we made a handler interface implemented by many Internal Iterators. Was that what you were picturing?

                    |<--- JDBC iterator
Caller ---> Handler |<--- Tree iterator
                    |<--- Other iterator 

Feels like Visitor is just a special case here.

BTW, Ilja, I hadn't seen Internal Iterator before. Thanks for hooking up a name with this.

[Peer Reynders]

Originally posted by Stan James:
Was that what you were picturing?

I wasn't after anything that grandiose.



It was just a personal observation - it was that article that more or less introduced me explicitly to the concept of "inversion". However there it is discussed in the context of the DIP - but inversion by itself can be a powerful tool in terms of design and I don't think that that is emphasized often enough.

Now, I don't think that it is accurate to claim that the flow of the data is inverted because the data is still flowing in the same direction. What has been inverted is control. In a more "conventional" design the consumer will often pull the data from the producer. In the "inverted" design the producer will push the data into the consumer.

Internal Iterator: so what's going on here? Is awareness of this pattern being raised because of Ruby's process blocks?
[ October 22, 2006: Message edited by: Peer Reynders ]

[Frank Carver]

Stan: if we made a handler interface implemented by many Internal Iterators

This looks OK initially, but I'm having trouble imagining how (in a strongly-typed language such as Java, at least) the interface for this handler would look.

In the JDBC case, I would want the internal iterator to pass in something like a ResultSet. In the tree walk example, I'd expect a TreeNode of some sort, and so on.

Sure, you could pass in a raw Object, but that would require a cast in every case, which feels uncomfortable.

Thoughts?

Peer: Is awareness of this pattern being raised because of Ruby's process blocks?

Possibly. I know that playing with Ruby helped me crystalize what I wanted my JDBC stuff to feel like.

Oddly enough, though, I haven't been able to find an equivalent to this for database access from Ruby. Everyone seems bowled over by ActiveRecord, which seems to use a traditional "pull" approach.

[Ilja Preuss]

Originally posted by Frank Carver:
In the JDBC case, I would want the internal iterator to pass in something like a ResultSet. In the tree walk example, I'd expect a TreeNode of some sort, and so on.

Sure, you could pass in a raw Object, but that would require a cast in every case, which feels uncomfortable.

Thoughts?

Generics?

public interface Handler<T> {
  public void handle(T value);
}

Peer: Is awareness of this pattern being raised because of Ruby's process blocks?

Possibly. I know that playing with Ruby helped me crystalize what I wanted my JDBC stuff to feel like.

This Internal Iterator stuff actually is quite popular in Smalltalk - from which Ruby copied a lot of basic concepts, as far as I can tell.

[Stan James]

Peer:

What has been inverted is control.

Bingo.

"Tree" was a bad example for what I was trying to imagine. The handler interface I made would work for any model that could pump out documents with rows & columns. Maybe an XML reader or spreadsheet reader would be another implementation.

I think Spring has a scheme like this for database queries, no? Has anybody used it?

[Peer Reynders]

Originally posted by Ilja Preuss:
This Internal Iterator stuff actually is quite popular in Smalltalk - from which Ruby copied a lot of basic concepts, as far as I can tell.

I wouldn't doubt that. Buts it interesting how it sometimes requires a "newer" language to catalyze the transition of an idiom from the original language into the "main stream".
I'm looking forward to many more idioms being mined from Smalltalk, LISP, Scheme, Haskell, Dylan, Prolog, etc.

[Nav Letha]

Great discussion.