aspose file tools*
The moose likes OO, Patterns, UML and Refactoring and the fly likes How to expose data  from ResultSet to business layer Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Engineering » OO, Patterns, UML and Refactoring
Bookmark "How to expose data  from ResultSet to business layer " Watch "How to expose data  from ResultSet to business layer " New topic
Author

How to expose data from ResultSet to business layer

michael warren
Ranch Hand

Joined: Oct 20, 2006
Posts: 50
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)



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
Bartender

Joined: Aug 19, 2005
Posts: 2922
    
    5
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
Ranch Hand

Joined: Oct 20, 2006
Posts: 50
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
author
Sheriff

Joined: Jul 11, 2001
Posts: 14112
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?


The soul is dyed the color of its thoughts. Think only on those things that are in line with your principles and can bear the light of day. The content of your character is your choice. Day by day, what you do is who you become. Your integrity is your destiny - it is the light that guides your way. - Heraclitus
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
Would it help to reverse who's driving? Describe the whole process in one place close to the database:

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.


A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
Peer Reynders
Bartender

Joined: Aug 19, 2005
Posts: 2922
    
    5
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
    Ranch Hand

    Joined: Oct 20, 2006
    Posts: 50
    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
    Ranch Hand

    Joined: Oct 20, 2006
    Posts: 50
    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
    Sheriff

    Joined: Jan 07, 1999
    Posts: 6920
    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:



    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:



    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


    Read about me at frankcarver.me ~ Raspberry Alpha Omega ~ Frank's Punchbarrel Blog
    Frank Carver
    Sheriff

    Joined: Jan 07, 1999
    Posts: 6920
    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
    author
    Sheriff

    Joined: Jul 11, 2001
    Posts: 14112
    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
    (instanceof Sidekick)
    Ranch Hand

    Joined: Jan 29, 2003
    Posts: 8791
    I was enamored of SAX HTML parsers at one moment, and made my resultset handler feel like a sax parser ... here's the interface

    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
    Bartender

    Joined: Aug 19, 2005
    Posts: 2922
        
        5
    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
    (instanceof Sidekick)
    Ranch Hand

    Joined: Jan 29, 2003
    Posts: 8791
    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
    (instanceof Sidekick)
    Ranch Hand

    Joined: Jan 29, 2003
    Posts: 8791
    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?

    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
    Bartender

    Joined: Aug 19, 2005
    Posts: 2922
        
        5
    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
    Sheriff

    Joined: Jan 07, 1999
    Posts: 6920
    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
    author
    Sheriff

    Joined: Jul 11, 2001
    Posts: 14112
    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?



    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
    (instanceof Sidekick)
    Ranch Hand

    Joined: Jan 29, 2003
    Posts: 8791
    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
    Bartender

    Joined: Aug 19, 2005
    Posts: 2922
        
        5
    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
    Greenhorn

    Joined: Oct 22, 2006
    Posts: 8
    Great discussion.
     
    With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
     
    subject: How to expose data from ResultSet to business layer