Best practice using recordsets

[Erikxxx]

Hi all,

I'm wondering what is best practice for dealing with data retrieved via JDBC as Recordsets without involving third part products such as Hibernate etc. I've been told to NOT use RecordSets throughout in my applications since they are taking up resources and are expensive. I'm wondering which collection type is best to convert RecordSets into. The apps I'm building are webbased using JSPs as presentation layer, beans and servlets.

Many thanks
Erik

 

[sedj]

>>> I've been told to NOT use RecordSets throughout in my applications

ResultSets are not actually inefficent - because there is often no other way of retrieving bulk data from a a database.
Of course, you could use a stored procedure if your db supports it and return a nested table, but in many cases, this is very similar to a ResultSet implementation, and to be honest, I doubt very much quicker.

Who has told you not to use ResultSets ? I would ask them what their opinion is on what else you should use !

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[timw]

The key to using ResultSets is to ensure that the SQL you use in the first place is crafted to return only as much info as you actually need. And careful / appropriate use of indexes on the database tables.

I too would be interested to know what the alternative could be. The only one I come across is the Cache product from InterSystems, which uses in-memory data as an Object Database.

http://www.InterSystems.co.uk

Tim
---------------------------
"Your morbid fear of losing,
destroys the lives you're using." - Ozzy

 

[Erikxxx]

Hi again and thanks for your replies.

I'll try to clarify my previous posting a little bit. Using the Recordsets to get the data from the database I guess is OK and the best way of doing it. I'm more concerned in HOW to pass this data around within the application (Java beans, servlets etc). If I have one class taking care of all recordsets from the database and then having methods such as

getALLCustomers()
getCustomer(int id)

I don't know what collection type to use to return the data to other classes within the system.

Thanks
Erik

 

[sedj]

There are many ways to do it ... here are two :

1) Hacky way - stuff all the data into an ArrayList object, with the values separated by some kind of delimeter such as a pipe or comma

2) Object-orientated way - for each set of data returned, create an object (like 'Customer' object) - when you rip the data from the db, create a Customer object, and have an array or array list of these objects.

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[Erikxxx]

Hi Sedj,

How would I proceed if I return a query given some data from one table and data from another table (where Im using join to two or more tables)? Do I need to build a seperate Object with corresponding attributes that the query output? I guess in the end there must be ALOT of various combination of queries that I need to create objects for. How do you manage this?

Thanks
Erik

 

[sedj]

>>>>> How would I proceed if I return a query given some data from one table and data from another table (where Im using join to two or more tables)?

I would not bother with mapping objects to tables - just create an object for the data that will be returned in the result set (if you are going to go down this road).

>>>> I guess in the end there must be ALOT of various combination of queries that I need to create objects for.

Yes, you will (if you are going to go down this road).


>>>> How do you manage this?

Ha ! Luckily no longer do I have to - I haven't written a front-end JSP for almost 18 months now

In the past we've tried several methods - from using a hacky version in a JSP (ie just iterating the result set, outputting data as you go) to using full-blown frameworks like Struts and oracle-objects. I never really got on with either, and found web development work boring and mundane - which is why I moved away from it !

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk