Re: [Springframework-developer] JdbcTemplate.doWithResultSetFromStaticQuery cleanups

[Colin S. <col...@ex...>]

This is wrong. The best mechanism I've seen to handle this is something like

ResultSet rs;
Statement s;
Connection conn;
try {
  ...
  rs.close();
  rs = null;
  s.close();
  s = null;
  conn.close();
  conn = null;
}
catch (whatever) {
  throw whatever...
}
finally {
  DataSourceUtils.closeIfNotNull(rs, s, conn);
}


where closeIfNotNull is a simple util which does oa close on the 
resultset, statement, and connection, respectively, but only if the item 
is not null, and silently swallows SQLExceptions.

In this fashion, you catch and propogate all SQLExceptions on normal 
usage of the resultset, statement, or connection, including the closing 
of those items, but even if there is an exception, the items are 
actually closed...

Regards,

Colin


Justinus Menzel wrote:

> Hi,
>
> first let me tell you that Spring is great, you guys rock!
>
> Now to the question: if you look into 
> JdbcTemplate.doWithResultSetFromStaticQuery():
> ...
>        try {
>            con = DataSourceUtils.getConnection(this.dataSource);
>            s = con.createStatement();
>            rs = s.executeQuery(sql);
>
>            if (logger.isInfoEnabled())
>                logger.info("Executing static SQL query '" + sql + "' 
> using a java.sql.Statement");
>
>            rse.extractData(rs);
>
>            SQLWarning warning = s.getWarnings();
>            rs.close();
>            s.close();
>
>            throwExceptionOnWarningIfNotIgnoringWarnings(warning);
>        }
>        catch (SQLException ex) {
>            throw 
> getExceptionTranslater().translate("JdbcTemplate.query(sql)", sql, ex);
>        }
>        finally {
>            DataSourceUtils.closeConnectionIfNecessary(con, 
> this.dataSource);
>        }
> ...
>
> it seems like rs.close() and s.close() will be executed only if there 
> was no exception thrown from
> rse.extractData(). s.close will only be executed if  rs = 
> s.executeQuery(sql); did OK.
> So my question is: why don't both close() go into the finally block?
> The reason why I'm asking this is that we had a few problems with 
> running out of cursors
> in our application server and we also had a few
> examples of queries not working correctly with certain input parameters.
>
> any comments are much appreciated
> thanks
>
> Justinus