aspose file tools*
The moose likes JDBC and the fly likes Closing Statements Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Closing Statements" Watch "Closing Statements" New topic
Author

Closing Statements

Anonymous
Ranch Hand

Joined: Nov 22, 2008
Posts: 18944
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

Joined: Jun 13, 2000
Posts: 3340
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).]


I Hope This Helps
Carl Trusiak, SCJP2, SCWCD
Anonymous
Ranch Hand

Joined: Nov 22, 2008
Posts: 18944
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

Joined: Jun 13, 2000
Posts: 3340
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.
 
Consider Paul's rocket mass heater.
 
subject: Closing Statements
 
Similar Threads
Database access, potential threading problem?
Synchronization Problem whil accessing JDBC Code
Reuse an object?
Statement Closed Error - please help
Testing Database Connection