ResultSet Best Practices


General J2EE: ResultSet Best Practices

  1. ResultSet Best Practices (4 messages)

    I know this topic has been discussed quite enough on this forum but I really feel I need some reassurance regarding the subject.
    The banal situation is this:
    I have a table in a JSP which needs to be populated from a ResultSet.
    Instead of passing the ResultSet to the JSP and iterating over it there, I understand the best practice is to convert the ResultSet to a different object (for example, using a CachedResultSet, RowSet or a Vector of Vectors).
    The thing that bothers me with this solution is the double iteration over the data returned from the database.
    Once to convert the ResultSet to my other object and the other when populating my table in the JSP.
    What happens when we're dealing with ResultsSets containing (many?) thousands of rows?

    Any help and clarification would be greatly appreciated.
    Thanks in advance,

    Threaded Messages (4)

  2. aye[ Go to top ]

    Firstly, if you do use a jdbc class such as RowSet, it is a pretty primitive design that has JSPs directly access such classes. Normally there is an intermediate of populating model objects from the jdbc class, allowing patterns such as MVC to be employed.

    To answer your question however, yes of course there is a slight performance hit for populating these objects. But normally, this cost is negligible given:
    a) the benefit of a good design
    b) the performance saving you get from caching the data in this way

    Finally, most apps don't need to display more than a few rows at a time to a user, so for really big resultsets, these can be populated on demand rather than all at once. This is easy to do in jdbc.
  3. aye[ Go to top ]

    I have a similar requirement in one of my applications. In particular, I have a set of three principal entities (EJBs) in my application, of which two act as containers -- we'll use the following analogy to simplify things:

    • A MusicCollection can contain 0 or more...
    • Albums, which in turn can contain 0 or more...
    • Songs.

    I need to return the entire MusicCollection to my view to be displayed in a categorized manner (i.e. a header with general information about the MusicCollection, followed by several Albums -- each with it's own header -- and finally a table of Songs (with various information about each)).

    The page only needs to look at one MusicCollection at a time, so it can be passed to the view as a single variable. However, the remaining entities need to be packaged up such that I don't lose the "ownership" relationship between them.

    My thought is that I should, then, create a Collection of some sort of value object where each value object contains a reference to an Album and a collection of references to Songs -- so a Collection of value objects (Albums), which themselves contain a collection of objects (Songs).

    Am I on the right track here?
  4. I think so[ Go to top ]

    I think you're correct. Its a very common situation you're facing.

    For objects that need to implement a 'has a' relationship, I think its best to model these as private member variables with standard java collection types - normally List, available via public accessors (e.g. public List getAlbumList() )

    This sitation is somewhat improved in Java 1.5 with the introduction of generics, at last, so you can have a typed list.

    I generally avoid creating custom collections as they tend to bloat code rather than simplify.

    class MusicCollection { private List albums}
    class Album {private List songs}
    class Song {}

    The issue often arises that sometimes you don't want the lists to load as they're not needed on every page, and are causing poor performance. These performance issues should be solved on an ad hoc basis, like denormalizing a db schema. For example, you might create a MusicCollectionLite object, i.e. MusicCollection minus collections, as an alternative to the full MusicCollection.

    class MusicCollectionLite {}
  5. I think so[ Go to top ]

    Hmm... okay, well, I guess that makes sense, though it's somewhat frustrating. Especially in that it feels that in someways I'm recreating my entities as POJOs just to support the separation of layers in MVC -- although I would at least not have to reimplement the entities themselves, just create a few container classes, more or less...

    Thanks for the tips, Adrian.