Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Closing Statements

 
Anonymous
Ranch Hand
Posts: 18944
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello-
What are the implications of not closing a Statement, but rather closing the ResultSet that comes from the Statement? The reason is my class that handles the database connection and querying just returns a result set. For example, if I have the following:
String query = "Select * from test_table";
ResultSet rs = DB.DBQuery(query);
. . . .
rs.close();
In class DB (try catch blocks left off...):
public ResultSet DBQuery(String query) {
Statement st;
// get connection...
ResultSet rs = st.execute(query);
return rs;
}
Notice the statment st is not closed in the DB class. However, it only has method scope. Will this cause a problem? I am using JDBC:ODBC bridge with Access.
Is a better design to not return a ResultSet, but rather put the ResultSet data into a HashTable or something similar and pass back the HashTable, that way the statement (and thus the resultset) can be closed in the database class?
Thanks for the help
Brian
 
Carl Trusiak
Sheriff
Posts: 3341
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There is a danger that the underlaying database may not close the associated cursor or recordset if all resourses in the calling program are not explicatly released. This is a notable problem with an Oracle database. The end result result is that you eventually get an SQL error something like 'Exceeded the maximum number of open cursors'. The best thing is to get into the habit of doing all the SQL in a routine like
ResultSet rs = null;
Statement stmt = null;
try
{
//your code or query
}
catch(SQLException se)
{
//handle any exceptions that are possible
}
finally
{
try
{
if(rs != null)
rs.close();
if(stmt != null)
stmt.close();
}
catch(SQLException sqle)
{}
rs = null;
stmt = null;
}
....
This does everything possible to release all the resourses both in your java code and in the underlaying database.
Hope This Helps
[This message has been edited by Carl Trusiak (edited September 20, 2000).]
 
Anonymous
Ranch Hand
Posts: 18944
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Would the best thing to do if all database actions are handled in a separate class, including getting the ResultSet, be to somehow put the ResultSet information into a Hashtable or something and return it to the calling class?
 
Carl Trusiak
Sheriff
Posts: 3341
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is a design question that you have to answer, among the options open to you are:
Return the ResultSet and let the calling class handle the details. Draw back if ever the data moves from a db or you change to RwoSet, all other classes have to change as well.
Create a class that represents the ResultSet and in the class that does the SQL read the info from the resultset into this class and return an array, Vector or some other Collection type of these classes. Can increase the overhead on your system if you have large rows and allot of rows to process.
Place each row into a hastable with colmun name as the key and return an array or Vector of the hashtable. Handling of the rows later means the other classes have to be aware of the database structure and a change to the underlaying tables means a lot of other changes. (such as changing the type on one row etc)
Any of these (and probably many others) can suffice in your system, you need to assess the best means. IMHO I create a class that mimics the rows and return this.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic