Closing a ResultSet object

[Joshua]

I have a database utility class with a number of static methods that
perform SQL actions. Amongst the paramaters passed to a static method
is a connection object. The database utility static method creates a
statement from the connection object that is passed in, performs an
SQL query on that statement, and returns a ResultSet. Originally this
utility method was calling the close() method on the statement object
for tidyness. Then I realised the ResultSet object was consequently
closed and therefore could not be used outside of the utility method.

The first question I have is if I call the close() method of the
ResultSet object will it close the statement object created in the
utility method?

Java docs suggests so -
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSet.html#close()
..

If this is the case then I will call ResultSet.close() in the method
calling the utility method after it has done it's job with the
returned data.

The second question I have is I'm wondering how others have best
designed a database utility class, and if I can maybe improve my
design.

 

[Niels Dybdahl]

The first question I have is if I call the close() method of the

ResultSet object will it close the statement object created in the
utility method?

Java docs suggests so -
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSet.html#close()

It is opposite. Closing a Statement will automatically close the ResultSet.

Niels Dybdahl

 

[Sudsy]

Joshua wrote:

If this is the case then I will call ResultSet.close() in the method
calling the utility method after it has done it's job with the
returned data.

Which doesn't close the statement, wasting resources.

The second question I have is I'm wondering how others have best
designed a database utility class, and if I can maybe improve my
design.

You might want to look into the use of "value objects". You populate
them with the contents of your ResultSet but then close both the
ResultSet and Statement (ideally in your finally block) and just
return the value object(s).

 

[Peter Kirk]

I have a database utility class with a number of static methods that
perform SQL actions. Amongst the paramaters passed to a static method
is a connection object. The database utility static method creates a
statement from the connection object that is passed in, performs an
SQL query on that statement, and returns a ResultSet. Originally this
utility method was calling the close() method on the statement object
for tidyness. Then I realised the ResultSet object was consequently
closed and therefore could not be used outside of the utility method.

Yes. closing the Statement should close the ResultSet.

The first question I have is if I call the close() method of the
ResultSet object will it close the statement object created in the
utility method?

Java docs suggests so -
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSet.html#close()

I think it is the other way around. If you close a Statement, then the
ResultSets it created will also be closed. Although I guess for utmost
robustness you should close the ResultSets yourself.

If this is the case then I will call ResultSet.close() in the method
calling the utility method after it has done it's job with the
returned data.

But since it doesn't work that way you might have problems - your Statement
will not be closed until the Connection is closed. And again, I think it
would be best to ensure that the Statement is closed, rather then relying on
closing the Connection to close the Statement.

The second question I have is I'm wondering how others have best
designed a database utility class, and if I can maybe improve my
design.

Well, I would make a "data" class which you instantiate, populate from the
ResultSet, and return from your utility method.

 

[Jared MacDonald]

The first question I have is if I call the close() method of the

ResultSet object will it close the statement object created in the
utility method?

No. Think about the resources as if they are boxes that contain each
other. A connection contains some statements. A statement contains a
result set. A result set contains some data. Close any of those, and
you can't get at the contents within. But closing an item does not
prevent you from getting at its container.

The second question I have is I'm wondering how others have best
designed a database utility class, and if I can maybe improve my
design.

I'd suggest as a start that it have the following methods:

close(Connection conn);
close(Statement stmt);
close(ResultSet rs);

These methods should all be null-safe and not throw any exceptions --
i.e., make them appropriate for finally { ... } blocks.

Beyond a typical database utility class that contains static methods,
however, you can come up with a more object-based design which
prevents any resource leakage. Think about an abstract class that
provides the common structure of any database operation, and that
takes care of the usual plumbing and cleanup.

 

[Joshua]

"Joshua" <[email protected]> skrev i en meddelelse



Well, I would make a "data" class which you instantiate, populate from the
ResultSet, and return from your utility method.

Good suggestion. In fact I implemented something to this effect. The
utility method now returns a vector of hashmaps to represent the
result set. Each hashmap represents a row - containing column names
as key and data as values. Vector acts as a container for rows.

The vector is then stored in a databean which then provides a JSP
access to this information via appropriate get methods.

I have now redesigned the utility method so that each time it is
called it creates a connection object, statement object, performs a
query, processes the resultset, closes the statement object, closes
the connection object. I'm hoping this should keep things tidy.

Out of curiosity, just how many connection objects can there be
instantiated?

 

[Joshua]

No. Think about the resources as if they are boxes that contain each
other. A connection contains some statements. A statement contains a
result set. A result set contains some data. Close any of those, and
you can't get at the contents within. But closing an item does not
prevent you from getting at its container.

Thanks for the clarification. There was some ambiguity in the
documentation as it explains for the ResultSet.close() method -

"Releases this ResultSet object's database and JDBC resources
immediately instead of waiting for this to happen when it is
automatically closed."

I initially interpreted this as also closing the statement object that
generated it. Furthermore the getStatement() and getConnection()
methods could be used on the ResultSet object to then call the close()
method on each in turn.

I'd suggest as a start that it have the following methods:

close(Connection conn);
close(Statement stmt);
close(ResultSet rs);

These methods should all be null-safe and not throw any exceptions --
i.e., make them appropriate for finally { ... } blocks.

Beyond a typical database utility class that contains static methods,
however, you can come up with a more object-based design which
prevents any resource leakage. Think about an abstract class that
provides the common structure of any database operation, and that
takes care of the usual plumbing and cleanup.

Good suggestion! In fact this architecture was already designed this
way. The utility class I speak of was to perform SQL only, an SQL
engine if you like. The way my general objects connect to a database
is not through the SQL engine itself but through methods inherited
from an abstract superclass. And now since the connection and
statement objects are created and closed in each utility method of the
SQL engine, there are no wasted resources. Everything is kept tidy.

 

[Sudsy]

Joshua wrote:

I have now redesigned the utility method so that each time it is
called it creates a connection object, statement object, performs a
query, processes the resultset, closes the statement object, closes
the connection object. I'm hoping this should keep things tidy.

Out of curiosity, just how many connection objects can there be
instantiated?

Actually, I never suggested closing the connection each time. It's
"expensive" to obtain a connection so I usually try to keep one
around or, better yet, use a ConnectionPoolDataSource.

 

[Peter Kirk]

Good suggestion. In fact I implemented something to this effect. The
utility method now returns a vector of hashmaps to represent the
result set. Each hashmap represents a row - containing column names
as key and data as values. Vector acts as a container for rows.

So you are making some sort of general database table representation?
I've never done that before - only had a specific class mapping to a table
(or tables). For example, the utility method might return a Person object,
with name, address, ...

The vector is then stored in a databean which then provides a JSP
access to this information via appropriate get methods.

I have now redesigned the utility method so that each time it is
called it creates a connection object, statement object, performs a
query, processes the resultset, closes the statement object, closes
the connection object. I'm hoping this should keep things tidy.

Yes. As another poster pointed out, obtaining connections can be expensive
(if it actually matters for your application only you can know), so it is a
good idea to have some sort of connection pool in use. In this case you
should still call close, which returns the connection to the pool instead of
really performing a close.

Out of curiosity, just how many connection objects can there be
instantiated?

Do you mean how many connections you can have open at a time? It depends on
your database - I think it will probably have a configuration parameter for
setting the number of simultaneous connections allowed.

BTW, if you get tired of all that handling of JDBC stuff (connections,
statements, resultsets) you could look at object/relational database mapping
tools (for example "hibernate"), which help translate between your classes
and tables/columns in a database.

Peter