This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes Need a disconnected resultset Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Need a disconnected resultset" Watch "Need a disconnected resultset" New topic
Author

Need a disconnected resultset

Marshall B Thompson
Ranch Hand

Joined: Apr 11, 2002
Posts: 42
Using java and oracle. I want to build a java data layer that allows business layer to specify name of stored procedure to invoke. The data layer should return something like a disconnected resultset to the business layer. Problem: OracleCachedRowSet is very limited - it must be given a select against a single table. Seems to eliminate OracleCachedRowSet for generic usage. I can't find a read only disconnected resultset that I can return from data layer into business layer. Did I miss such a class somewhere? I can roll my own Data Transfer Object (DTO) for each resultset, and return that from data layer to business layer. That will be a lot of DTO classes for a large system with many stored procedures. I could return a hashmap or xml to the business layer, but that seems too heavy. Suggestions are welcome.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60774
    
  65

Passing database objects to the business layer is always inappropriate. Any of the other techniques you mentioned could be appropriate depending upon the structure of your application. The "it's a large system" claim is no excuse for shoddy architecture.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Marshall B Thompson
Ranch Hand

Joined: Apr 11, 2002
Posts: 42
Debating between DTO/Hashmap/XML ...etc. I just need my data (column name/value pairs) accessible in the business layer. XML works, but loading up a DOM every time and parsing seems overkill. I don't want to code a bunch of DTO's if I can help it. Hashmap is not efficient for resultset with just a few columns. Seems like the best choice is to make a generic DTO maybe. It would maybe have methods getValue("columnnameasastring") and setValue("columnname", "value") wrapping an internal data structure optimized for the number of columns that were returned. I feel like I'm probably reinventing something that already exists though. ....Looking thru the current java data structures and collections again.
Marshall B Thompson
Ranch Hand

Joined: Apr 11, 2002
Posts: 42
Specifically what is needed for holding a single row of a resultset is a data structure:
- indexed by string (a column name)
- holding an object value
- with an initial and unchanging size set to the exact number of columns
- duplicate indexes are not allowed
- order is unimportant
HashMap comes close, but initialCapacity must be a power of two, so for example: If I returned 17 columns, a hashmap internall has an array with 32 slots - this in inefficient.
Looks like I should roll my own structure, but I would think this has been done many times over. Anyone?
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30085
    
149

Why don't you want to create your own DTO? It's not hard to do as most IDEs will generate getters and setters. You also have the advantage of clearer data structures and compile time checking.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60774
    
  65

If I returned
17 columns, a hashmap internall has an array with 32 slots - this in inefficient.

Are you so sure? This is an attitude known as "premature optimization". The Java collection classes are written to be as efficient as possible and you should feel free to use them unless you can prove that they are causing a measurable inefficiency in your program.
Dirk Schreckmann
Sheriff

Joined: Dec 10, 2001
Posts: 7023
Actually, a HashMap data structure would possibly be terribly inefficient if it did not have considerably more total slots than it had filled slots. Don't forget about those collisions.
A few null references in an array probably don't take enough memory to talk about, and I'd be surprised if it degraded your system's performance.
[ May 01, 2004: Message edited by: Dirk Schreckmann ]

[How To Ask Good Questions] [JavaRanch FAQ Wiki] [JavaRanch Radio]
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60774
    
  65

Exactly. Second-guessing performance at this level is unlikely to accurately predict actual application performance.
Marshall B Thompson
Ranch Hand

Joined: Apr 11, 2002
Posts: 42
I don't want to physically create a ton of DTO objects, and maintain them as my SQL changes if I don't have to.
Points on premature optimization are indeed valid. I do not feel I can create a better HashMap.
I am questioning the proper data structure to use out of my data layer. I have a prototype working that I'd like to float here for comment. I need another day or so to complete and do some initial unit testing.
I don't see a way to upload a zip file, so is it acceptable to copy in code in the reply text. I'll have 3 or 4 classes.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30085
    
149

Marshall,
You are correct that you cannot upload a zip file. You can include the classes in the reply text (although you may want to remove any unessential code to make it easier to follow.) Also, make sure you use the UBB code tags so the code stays nicely formatted.
Dirk Schreckmann
Sheriff

Joined: Dec 10, 2001
Posts: 7023
I don't want to physically create a ton of DTO objects, and maintain them as my SQL changes if I don't have to.
...
I am questioning the proper data structure to use out of my data layer. I have a prototype working that I'd like to float here for comment. I need another day or so to complete and do some initial unit testing.

Again, to me it sounds like a list of maps is just what the doctor ordered.
A few times, I've used the following code, which I believe is (a possibly modified version of an example) from a post by Peter den Haan, to convert a result set to a list of maps. (I couldn't locate the original post after a few searches.)

Depending on just what you're up to, if you're constantly changing your desires and understanding of the proper persistence and database schema for the application, you might want to save some work and simply mock the entire persistence concern until the business logic evolution stabilizes. I've been very happy with this strategy on the past few projects I've worked on.
I don't see a way to upload a zip file, so is it acceptable to copy in code in the reply text. I'll have 3 or 4 classes.
You're right, we don't support file uploads in the forums. You can always post code examples. When posting code, please be sure to surround the code with the [code] and [/code] UBB Tags. This will help to preserve the formatting of the code, thus making it easier to read and understand.
[ May 03, 2004: Message edited by: Dirk Schreckmann ]
Marshall B Thompson
Ranch Hand

Joined: Apr 11, 2002
Posts: 42
Thanks Dirk, now that's sort of what I was looking for. I had just finished prototyping the code below. All comments are welcome.
Marshall B Thompson
Ranch Hand

Joined: Apr 11, 2002
Posts: 42
I know it's a lot of code, but I had hoped to get some constructive criticism on my RowDTO class. You can basically ignore everything else in the sample. Just know that I turn a ResultSet into an ArrayList of RowDTO objects - one RowDTO for each row.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30085
    
149

I would use an ArrayList to store the internal representation of each RowDTO rather than an Array. You can still set the initial capacity of an ArrayList.
Actually, I would really use a meaningful name for each field. But I understand that you don't want to have a lot of classes.
Marshall B Thompson
Ranch Hand

Joined: Apr 11, 2002
Posts: 42
Jeanne: Thanks for looking this over. Why the ArrayList instead of Array?
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30085
    
149

Marshall,
Mostly personal preference. The main advantages are that you don't have to worry about index out of bounds exceptions and the ArrayList can grow dynamically. You also benefit from the API methods that are available.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Need a disconnected resultset
 
Similar Threads
Passing Serilizable JPA Entity from EJB to Web Tier
Implementing Patterns
Isolating business layer from presentation
Struts and App Design question
can you pass ResultSet as argument