Statement and Resultset

[Andrea]

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

 

[Jeff]

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).

 

[Lew]

You should log this error, not ignore it.

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

 

[Wojtek]

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?

 

[Chris Uppal]

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

 

[Chris Uppal]

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

 

[=?ISO-8859-15?Q?Arne_Vajh=F8j?=]

Lew wrote :

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

 

[Lew]

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

 

[Wojtek]

Lew wrote :

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.

 

[Lew]

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.

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

 

[Chris Uppal]

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

If the JDBC implementation provides PreparedStatement pooling, then the
overhead may even be negative.

(I don't know how many implementation /do/ provide PreparedStatement pooling --
I onlly heard of it for the first time yesterday.)

-- chris

 

[Chris Uppal]

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

That is an approach that I'd like to see stamped-out. I don't know how
competent you may be -- I'm willing to believe that you've got it exactly
right -- but I don't trust /most/ programmers to get it right. In this case I
don't trust /myself/ to get it right, and I'm at the careful end of the
spectrum as a programmer.

You have to know /exactly/ how the real DBMS's parser will work, including its
responses to malformed input, to non-ASCII characters in the input, and
whatever bugs it may have. You also have to take account of what bugs it may
develop in later releases. If you think it's worthwhile then you have to make
your code work with all possible databases (I wouldn't think it's worth it
myself); but if you don't then you should include some way to ensure that using
it with a "wrong" DB will cause runtime errors (exceptions, say) rather than
opening an invisible security hole.

Of course, having such a module is /far/ better than concatenating unsafe
strings without thought. It's good, but I just don't think it's good
/enough/ -- and it is actually quite a bit more effort than using prepared
statements.

-- chris

 

[Wojtek]

Lew wrote :

Same overhead so far.


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.

No, it would have to be different. A Statement contains all, whereas a
PreparedStatement has place holders for the information, which would
require index pointers to where the information is to be placed.

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


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.

Not at DB compile time, but when the ? parameters are filled. You need
to use the correct parameter type for the numbered index.

Which also creates a potential for mis-placing which parameter goes
where. I use a LOT of comments between where the PreparedStatement is
built up, to where the parameters are filled in.

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.

Well, the class does more than that. I have abstracted the tables and
their columns, which I also pass into SQLBuffer. It then extracts the
actual table name and column name from the passed Table and Column
classes (I *like* the compiler to catch errors for me )

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.

Ah, trade-offs, trade-offs

 

[Wojtek]

Chris Uppal wrote :

That is an approach that I'd like to see stamped-out. I don't know how
competent you may be -- I'm willing to believe that you've got it exactly
right -- but I don't trust /most/ programmers to get it right. In this case
I don't trust /myself/ to get it right, and I'm at the careful end of the
spectrum as a programmer.

I fully understand that paranoia I abhor "magic numbers" and
values. I use lots of constants and let the compiler catch as many
mistakes as I can make it catch.

You have to know /exactly/ how the real DBMS's parser will work, including
its responses to malformed input, to non-ASCII characters in the input, and
whatever bugs it may have.

Malformed input by the user is checked way above the SQL layer. All
user input is conditioned (leading/training spaces, double spaces,
numeric values, dates, etc) then checked for validity before the
business logic is even evaluated.

You also have to take account of what bugs it may
develop in later releases. If you think it's worthwhile then you have to
make your code work with all possible databases (I wouldn't think it's worth
it myself); but if you don't then you should include some way to ensure that
using it with a "wrong" DB will cause runtime errors (exceptions, say) rather
than opening an invisible security hole.

The actual code for a particular DB is held in a class specific to that
DB (for each use case). The command class accesses the DB method
through an abstract class, which the DB class extends. The only
interaction is through a data class wich is passed around.

Of course, having such a module is /far/ better than concatenating unsafe
strings without thought. It's good, but I just don't think it's good
/enough/ -- and it is actually quite a bit more effort than using prepared
statements.

And I have learned something. I was only ever concerned with text
delimiters, ie the single quote used in most DB's, but I had never
considered problems with non-ASCII characters. Need to do some research
here.

I will need to re-think on how I use Statement vs PreparedStatement,
though at this point in the application we are talking about many
thousands of places. It would mean major re-factoring and subsequent
regression testing.

 

[Lew]

No, it would have to be different. A Statement contains all, whereas a
PreparedStatement has place holders for the information, which would
require index pointers to where the information is to be placed.

What I meant was substantially the same. If I were writing a DB driver, I'd
make my Statement implementor a thin layer over my PreparedStatement
implementor, so literally the real work is done by PreparedStatement in either
case.

Not at DB compile time, but when the ? parameters are filled.

That is what I was talking about.

You need to use the correct parameter type for the numbered index.

I do not see that as an exceptional burden. I have to know the data types to
fill in the Statement, too, only a Statement doesn't provide /any/ assistance
to the matchup. Yes, there are more lines of code in the PreparedStatement
usage - the payback is that extra little boost of type checking.

Which also creates a potential for mis-placing which parameter goes
where. I use a LOT of comments between where the PreparedStatement is
built up, to where the parameters are filled in.

Don't you have to be careful about which parameter goes where in the
concatenation phase of your SQL String for a Statement?

This validates Chris's paranoia about how careful programmers are.

As to the SQL injections, I build up SQL statements using a custom
class (SQLBuffer) which escapes imbeded quotes in String parameters. ....
Well, the class does more than that. I have abstracted the tables and
their columns, which I also pass into SQLBuffer. It then extracts the
actual table name and column name from the passed Table and Column
classes (I *like* the compiler to catch errors for me )

Chris Uppal spoke to why, at least going forward, using PreparedStatement can
help ease the programming burden here.

No one is arguing to eliminate surface edits. At the very least,
PreparedStatement provides extra security on data types and values for a
defense in depth against data anomalies.

Chris also pointed to how a clever DB driver might even leverage
PreparedStatement to perform better than a "regular" Statement.

However, it is really impossible to generalize an implementation
characteristic from an interface. PreparedStatement is an interface subtype of
the Statement interface. In particular, one can not make any averrals about
their relative performance.

So the only meaningful comparison between the two is in terms of how they aid
program design and construction.

The question was why one /would/ use PreparedStatements. It might not be that
you always should use them, but at least one can be aware of the advantages
they confer to offset their complexity, and that the performance burden is
likely an urban legend.

- Lew

 

[Wojtek]

Lew wrote :

So the only meaningful comparison between the two is in terms of how they aid
program design and construction.

The question was why one /would/ use PreparedStatements. It might not be that
you always should use them, but at least one can be aware of the advantages
they confer to offset their complexity, and that the performance burden is
likely an urban legend.

Well, any performance hit would probably be within the DB engine,
rather than Java.

But yes, I get the thrust of your argument.

 

[Lew]

Lew wrote :

Well, any performance hit would probably be within the DB engine, rather
than Java.

In fact, the DB side could be completely unable to tell the difference, since
the JDBC layer could hide it.

- Lew

 

[angrybaldguy]

In fact, the DB side could be completely unable to tell the difference, since
the JDBC layer could hide it.

In fact, I believe that MySQL 4 did not support prepared statements,
so the JDBC driver for MySQL faked them. It's a completely valid
approach, so long as there's no way to tell from the application that
there's no real "prepare" step.

 

[Guest]

What I meant was substantially the same. If I were writing a DB driver,
I'd make my Statement implementor a thin layer over my PreparedStatement
implementor, so literally the real work is done by PreparedStatement in
either case.

I do not think that would be a good decision.

That would add the overhead of PreparedStatement (and
it does exist with some databases) to the usage of
Statement where the programmer did not expect it.

However, it is really impossible to generalize an implementation
characteristic from an interface. PreparedStatement is an interface
subtype of the Statement interface. In particular, one can not make any
averrals about their relative performance.

So the only meaningful comparison between the two is in terms of how
they aid program design and construction.

The choice of name gives a clear indication of what JDBC driver
implementators are supposed to do with it.

And that do have performance implications.

Not necessarily a simple obvious conclusion, but none the
less a conclusion.

Arne

 

[Lew]

The choice of name gives a clear indication of what JDBC driver
implementators are supposed to do with it.

And that do have performance implications.

Not necessarily a simple obvious conclusion, but none the
less a conclusion.

I see your point, but I just do not see how a programmer can make *any*
performance decisions about code to which the only access is an interface.
Furthermore, others on this thread have offered anecdotal evidence that the
naive expectation is, in fact, violated in the real world.

Furthermore, the "clear indication" says absolutely nothing about performance,
only usage structure.

- Lew