Statement and Resultset

Discussion in 'Java' started by Andrea, Feb 21, 2007.

  1. Andrea

    Andrea Guest

    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
     
    Andrea, Feb 21, 2007
    #1
    1. Advertising

  2. Andrea

    Jeff Guest

    On Feb 21, 2:54 pm, "Andrea" <> 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).
     
    Jeff, Feb 21, 2007
    #2
    1. Advertising

  3. Andrea

    Lew Guest

    "Andrea" <> 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
     
    Lew, Feb 21, 2007
    #3
  4. Andrea

    Wojtek Guest

    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
     
    Wojtek, Feb 23, 2007
    #4
  5. Andrea

    Chris Uppal Guest

    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
     
    Chris Uppal, Feb 23, 2007
    #5
  6. Andrea

    Chris Uppal Guest

    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
     
    Chris Uppal, Feb 23, 2007
    #6
  7. 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
     
    =?ISO-8859-15?Q?Arne_Vajh=F8j?=, Feb 24, 2007
    #7
  8. Andrea

    Lew Guest

    > 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
     
    Lew, Feb 24, 2007
    #8
  9. Andrea

    Wojtek Guest

    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 :)
     
    Wojtek, Feb 24, 2007
    #9
  10. Andrea

    Lew Guest

    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
     
    Lew, Feb 24, 2007
    #10
  11. Andrea

    Chris Uppal Guest

    Lew wrote:

    > 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, Feb 24, 2007
    #11
  12. Andrea

    Chris Uppal Guest

    Wojtek wrote:

    > 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
     
    Chris Uppal, Feb 24, 2007
    #12
  13. Andrea

    Wojtek Guest

    Lew wrote :
    > 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.


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


    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.

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


    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 :)
     
    Wojtek, Feb 24, 2007
    #13
  14. Andrea

    Wojtek Guest

    Chris Uppal wrote :
    > Wojtek wrote:
    >
    >> 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.


    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.

    --
    Wojtek :)
     
    Wojtek, Feb 24, 2007
    #14
  15. Andrea

    Lew Guest

    Lew wrote:
    >> In fact, I would not be surprised if the
    >> Statement in a driver were implemented by the same code as the
    >> PreparedStatement.


    Wojtek wrote:
    > 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.

    Lew wrote:
    >> Notice that you thus get
    >> compile-time checking of the types of arguments to the ? parameters, a
    >> pretty strong benefit.


    Wojtek wrote:
    > 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.

    Wojtek wrote:
    > 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
     
    Lew, Feb 24, 2007
    #15
  16. Andrea

    Wojtek Guest

    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.

    --
    Wojtek :)
     
    Wojtek, Feb 25, 2007
    #16
  17. Andrea

    Lew Guest

    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.

    >

    Wojtek 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
     
    Lew, Feb 25, 2007
    #17
  18. Andrea

    Guest

    On Feb 25, 7:35 am, Lew <> wrote:

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


    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.
     
    , Feb 25, 2007
    #18
  19. Lew wrote:
    > 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
     
    =?UTF-8?B?QXJuZSBWYWpow7hq?=, Feb 26, 2007
    #19
  20. Andrea

    Lew Guest

    Arne Vajhøj wrote:
    > 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
     
    Lew, Feb 26, 2007
    #20
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. lord0
    Replies:
    4
    Views:
    705
    lord0
    Feb 11, 2004
  2. Cruella DeVille

    ResultSet and fetchSize()

    Cruella DeVille, May 11, 2006, in forum: Java
    Replies:
    10
    Views:
    26,609
    dayjah
    May 15, 2006
  3. Mariano

    ResultSet and getArray()

    Mariano, Mar 22, 2007, in forum: Java
    Replies:
    6
    Views:
    6,501
    Andrew Thompson
    Mar 25, 2007
  4. Evrim
    Replies:
    1
    Views:
    441
  5. Replies:
    3
    Views:
    352
    =?ISO-8859-1?Q?Arne_Vajh=F8j?=
    Sep 29, 2007
Loading...

Share This Page