Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > Statement and Resultset

Reply
Thread Tools

Statement and Resultset

 
 
Andrea
Guest
Posts: n/a
 
      02-21-2007
Hi all,
I've a java method (included in a java class) like this above:

public static ResultSet eseguiSelect(Connection conn,String query) throws
SQLException
{
LogTrace log = new LogTrace();
log.trace("query : "+query);
Statement stmt = null;
ResultSet rs=null;
try
{
stmt=conn.createStatement();
rs=stmt.executeQuery(query);
}
catch(SQLException sqle)
{
log.trace("SQLException : "+sqle.getMessage());
log.trace("SQLState : "+sqle.getSQLState());
log.trace("VendorError : "+sqle.getErrorCode());
try
{
rs.close();
stmt.close();
}
catch(Exception exception)
{ }
throw sqle;
}
return rs;
}

In this execution I don't close "stmt" and "rs" 'cause "rs" must be returned
to caller of method; but this can be dangerous? i.e. this statement and
resultset unclosed could remain active with consequent memory occupation?
Thanks in advance
JFM


 
Reply With Quote
 
 
 
 
Jeff
Guest
Posts: n/a
 
      02-21-2007
On Feb 21, 2:54 pm, "Andrea" <(E-Mail Removed)> wrote:
> Hi all,
> I've a java method (included in a java class) like this above:
>
> public static ResultSet eseguiSelect(Connection conn,String query) throws
> SQLException
> {
> LogTrace log = new LogTrace();
> log.trace("query : "+query);
> Statement stmt = null;
> ResultSet rs=null;
> try
> {
> stmt=conn.createStatement();
> rs=stmt.executeQuery(query);
> }
> catch(SQLException sqle)
> {
> log.trace("SQLException : "+sqle.getMessage());
> log.trace("SQLState : "+sqle.getSQLState());
> log.trace("VendorError : "+sqle.getErrorCode());
> try
> {
> rs.close();
> stmt.close();
> }
> catch(Exception exception)
> { }
> throw sqle;
> }
> return rs;
> }
>
> In this execution I don't close "stmt" and "rs" 'cause "rs" must be returned
> to caller of method; but this can be dangerous? i.e. this statement and
> resultset unclosed could remain active with consequent memory occupation?
> Thanks in advance
> JFM



My understanding of how Java garbage collection is supposed to work is
that once the last reference to an object is abandoned, its memory is
flagged to be reclaimed. So, by rights, there should be no problem. In
this case, I suspect that the database connection object is the one
that has the most system wide effects, but those are often left active
throughout program execution... (yes, I will check that sticky . key).

 
Reply With Quote
 
 
 
 
Lew
Guest
Posts: n/a
 
      02-21-2007
"Andrea" <(E-Mail Removed)> wrote:
>> Hi all,
>> I've a java method (included in a java class) like this above:
>>
>> public static ResultSet eseguiSelect(Connection conn,String query) throws
>> SQLException
>> {
>> LogTrace log = new LogTrace();
>> log.trace("query : "+query);
>> Statement stmt = null;
>> ResultSet rs=null;
>> try
>> {
>> stmt=conn.createStatement();
>> rs=stmt.executeQuery(query);
>> }
>> catch(SQLException sqle)
>> {
>> log.trace("SQLException : "+sqle.getMessage());
>> log.trace("SQLState : "+sqle.getSQLState());
>> log.trace("VendorError : "+sqle.getErrorCode());
>> try
>> {
>> rs.close();
>> stmt.close();
>> }
>> catch(Exception exception)
>> { }


You should log this error, not ignore it.

>> throw sqle;
>> }
>> return rs;
>> }
>>
>> In this execution I don't close "stmt" and "rs" 'cause "rs" must be returned
>> to caller of method; but this can be dangerous? i.e. this statement and
>> resultset unclosed could remain active with consequent memory occupation?


Jeff wrote:
> My understanding of how Java garbage collection is supposed to work is
> that once the last reference to an object is abandoned, its memory is
> flagged to be reclaimed. So, by rights, there should be no problem. In
> this case, I suspect that the database connection object is the one
> that has the most system wide effects, but those are often left active
> throughout program execution... (yes, I will check that sticky . key).


Not a good idea to leave database resources open for long times.

The issue here is not garbage collection but the release of database resources.

Andrea, look into the "try { ... } finally { .... }" idiom.

Personally, I transfer my results into value object lists (or sets, or ...)
and close the result set before passing the results up. This frees database
resources (or returns them to the pool) right away.

Side note: Consider using PreparedStatement instead of Statement.

The bottom line is that you must release external resources after you have
used them. If you are not sure if they are released, you haven't written your
code correctly. You must release external resources after you have used them.

There are a number of ways to ensure the guarantee of resource release, most
of which use the finally block. Use your favorite idiom, but remember, you
must release your external resources.

- Lew
 
Reply With Quote
 
Wojtek
Guest
Posts: n/a
 
      02-23-2007
Lew wrote :
> Side note: Consider using PreparedStatement instead of Statement.


Hmm, I use a Statement for a single call to the database, but a
PreparedStatement within a loop.

Why would I want to incur the overhead of a PreparedStatment for a
single call?

--
Test Sig


 
Reply With Quote
 
Chris Uppal
Guest
Posts: n/a
 
      02-23-2007
Wojtek wrote:

> Why would I want to incur the overhead of a PreparedStatment for a
> single call?


To avoid the tempation to assemble SQL queries by concatenating unsafe strings.

(Just a general point, nothing to do with the original question.)

-- chris


 
Reply With Quote
 
Chris Uppal
Guest
Posts: n/a
 
      02-23-2007
Andrea wrote:

> In this execution I don't close "stmt" and "rs" 'cause "rs" must be
> returned to caller of method; but this can be dangerous? i.e. this
> statement and resultset unclosed could remain active with consequent
> memory occupation? Thanks in advance


I can't find any information on what's guaranteed and what's not (not even in
the JDBC spec).

From a cautious point of view (which I would normally take) it seems that
relying on automatic cleanup is not a good idea, if it is not guaranteed to
work. And so, from that point of view, it seems that you design is flawed.

On the other hand, finalisation and related techniques are there to be used for
this kind of thing, so there's a good chance that it will work perfectly in
practise (I suppose that depends on the driver vendor too).

Can't help much, I'm afraid. I just wanted to say that there doesn't seem to
be a well-defined answer.

-- chris


 
Reply With Quote
 
=?ISO-8859-15?Q?Arne_Vajh=F8j?=
Guest
Posts: n/a
 
      02-24-2007
Wojtek wrote:
> Lew wrote :
>> Side note: Consider using PreparedStatement instead of Statement.

>
> Hmm, I use a Statement for a single call to the database, but a
> PreparedStatement within a loop.
>
> Why would I want to incur the overhead of a PreparedStatment for a
> single call?


1) Easy handling of quotes.

2) Easy handling of date formats.

3) The special case of #1 = protection against SQL injection.

Arne
 
Reply With Quote
 
Lew
Guest
Posts: n/a
 
      02-24-2007
> Wojtek wrote:
>
>> Why would I want to incur the overhead of a PreparedStatment for a
>> single call?

>

Chris Uppal wrote:
> To avoid the tempation to assemble SQL queries by concatenating unsafe strings.
> (Just a general point, nothing to do with the original question.)


That is relevant to Wojtek's question. It is the first reason, or more
generally, the precise control that a PreparedStatement provides. The
protection against SQL injection security hacks.

Also, the Statement gets compiled anyway, or the db engine won't be able to
run it.

So how does one avoid the overhead of a PreparedStatement when Statement
incurs it anyway?

Or are you referring to the fact that it takes two lines of code instead of one?

- Lew
 
Reply With Quote
 
Wojtek
Guest
Posts: n/a
 
      02-24-2007
Lew wrote :
>> Wojtek wrote:
>>
>>> Why would I want to incur the overhead of a PreparedStatment for a
>>> single call?

>>

> Chris Uppal wrote:
>> To avoid the tempation to assemble SQL queries by concatenating unsafe
>> strings.
>> (Just a general point, nothing to do with the original question.)

>
> That is relevant to Wojtek's question. It is the first reason, or more
> generally, the precise control that a PreparedStatement provides. The
> protection against SQL injection security hacks.


I do not understand what you mean by precise control?

> Also, the Statement gets compiled anyway, or the db engine won't be able to
> run it.
>
> So how does one avoid the overhead of a PreparedStatement when Statement
> incurs it anyway?
>
> Or are you referring to the fact that it takes two lines of code instead of
> one?


I always thought that a Statement was passed to the DB engine, where it
is compiled then run.

A PreparedStatement is passed to the DB engine where it is compiled.
The compiled code is held until the connection is closed, thus using up
a resource. Moreover it is a more complex resource, as a
PreparedStatement must be able to replace the ?'s with passed
parameters.

As to the SQL injections, I build up SQL statements using a custom
class (SQLBuffer) which escapes imbeded quotes in String parameters.

--
Wojtek


 
Reply With Quote
 
Lew
Guest
Posts: n/a
 
      02-24-2007
Wojtek wrote:
> I do not understand what you mean by precise control?


The types of the set parameters that get passed to the ? parameters can be
checked at compile time, for example.

Lew wrote:
>> Also, the Statement gets compiled anyway, or the db engine won't be
>> able to run it.


Wojtek wrote:
> I always thought that a Statement was passed to the DB engine, where it
> is compiled then run.


Correct.

> A PreparedStatement is passed to the DB engine where it is compiled.


Same overhead so far.

> The compiled code is held until the connection is closed, thus using up a
> resource.


As to what the db engine holds or how long, I do not know any place that Java
makes promises about that. PreparedStatement is an interface. The only sure
thing is that the compilation is separate from the execution, not how long the
compiled version hangs around. For all we know, the actual driver may hang on
to Statements' compiled forms for just as long. In fact, I would not be
surprised if the Statement in a driver were implemented by the same code as
the PreparedStatement.

The PreparedStatement, like the Statement from which it inherits, releases its
resources on close().

> Moreover it is a more complex resource, as a PreparedStatement
> must be able to replace the ?'s with passed parameters.


That is true. The price of power. Notice that you thus get compile-time
checking of the types of arguments to the ? parameters, a pretty strong benefit.

> As to the SQL injections, I build up SQL statements using a custom class
> (SQLBuffer) which escapes imbeded quotes in String parameters.


Good. You really don't have to go through as much protection with
PreparedStatements, so perhaps that helps offset the complexity. Plus that
pruning step adds overhead, so the overhead argument by itself might tilt in
favor of PreparedStatement because of this.

There really isn't any overhead of PreparedStatement compared to Statement
that I know of, other than the additional complexity of use - but that
complexity can be offset in other parts of the program.

- Lew
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mutiple ResultSet objects with same Statement object teser3@hotmail.com Java 3 09-29-2007 12:41 AM
Newbie question - MVC model and Accessing Database and resultset bean? Evrim Java 1 08-08-2007 09:58 PM
ResultSet and fetchSize() Cruella DeVille Java 10 05-15-2006 01:59 PM
Which of switch statement and if-else statement takes less time to execute? swaroophr@gmail.com C Programming 21 08-02-2005 09:24 AM
Trouble with Connector/J and ResultSet() lord0 Java 4 02-11-2004 10:04 PM



Advertisments