Design Question

Jeremy Hooie

Ranch Hand

Posts: 38

posted 15 years ago

I'm not sure if I'm asking this in the right forum, but I'm trying to design a class structure for database access. I want to create a class which holds the most frequently used statements. Right now, I have a class which executes the statements and returns the result set.

Then, in my calling class, I instantiate the PreparedSQL class.

My question is, is this a good design? What are the pros and cons of doing something else, say returning a Vector instead of the recordset? If anyone has any tips (or knows of any good books), I would be most appreciative. Sorry, it put smilies for in jdbcdbc! [This message has been edited by Jeremy Hooie (edited November 09, 2001).]

The problem here lies in the fact that when you close your connection, it implicitly closes all statements and resultsets associated with the connection. So return formList will have no data after you close the connection. Another design performance error is that everytime you query the database you need to create a new connection. Usually this is a very expensive(timewise) task. I would create a member variable connection, create it once, and use it throughout the life of the prepareSQL object. Some ideas if you need the design that you have set up is to return a Vector or ArrayList(ArrayList being the preferred data structure). if I get some time to give you a sample, I'll post it Jamie

[This message has been edited by Jamie Robertson (edited November 09, 2001).]

This is only an idea, I haven't tried to compile, and I don't know what your specs need. Jamie

Jeremy Hooie

Ranch Hand

Posts: 38

posted 15 years ago

Thanks, Jamie. I had not thought about the extra overhead caused by creating connections every time . But what, if any, is the benefit of returning an ArrayList instead of using a member variable connection and returning the result set? Jeremy

assuming you are using this method repeatedly for every instance of PrepareSQL. If you keep creating ResultSets then returning them, you are leaving open MANY ResultSets and Statements which can cause resource problems(like too many open cursors). How are you going to close the ResultSets and Statements from the calling class? You only have access to the ResultSet you have been returned, so how do you close the associated statement? It will eventually be released by closeConnection() but until then, it will keep eating up resources. One technique to get around this is to declare the ResultSet and Statement as member variables: