How to close PreparedStatement

Question

I have the following code on which I want to close the preparedStatement object as it is raised as a bug from sonar.

  public myfunction() throws SQLException {
            
           PreparedStatementCreator preparedStatementCreator = new PreparedStatementCreator() {
                String query = "";//let us assume a query
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement preparedStatement= connection.prepareStatement(query);
                    preparedStatement.setString();
                    preparedStatement.setString();
                }
            };
    int rows;
    try
    {
    rowNumbers = jdbcTemplate.update(preparedStatementCreator);
    }
    catch(....)
    {
    }
    catch(....)
    {
    }
    catch(....)
    {
    }
    }

How can I close the preparedStatement object? Most of examples I saw they mostly use try/finally or try with resources and then create object and use it try and close in finally. However, here the object is getting created in separate function and it is returned from there and then it is used. So creation and usage are happening at two different places. So I want to know both ways of handling this

Before Java 8
With Java 8 try with resources

I consider this a bad design choiche, anyway the caller is responsible to close the associated resources - how could this be done by the calling method? — fantaghirocco, Dec 10 '20 at 13:58
oops… I see *jdbcTemplate*. I your're using it in the correct way there's no need to free resources (I guess you're using Spring and JdbcTemplate takes care of it) — fantaghirocco, Dec 10 '20 at 14:00
here is a related question: https://stackoverflow.com/q/23961553/217324 my understanding is the case where the statement doesn't get closed also results in the parameters not working and the whole thing blowing up. so in practice it is not an issue. but sonar doesn't see that of course. — Nathan Hughes, Dec 10 '20 at 14:03
this is some old code base i am handling.The link you shared, i have gone through it but did not find it. Since createPreparedStatement might have been called by jdbc at some point in its execution so it should be only responsible to close i think.It does not seem possible to close in application code that i had as i dont have access to the preparedStatement object.?In the link you shared in final solution they assume we have the preparedStatement object i thinlk. — rahul sharma, Dec 10 '20 at 14:33

score 1 · Accepted Answer · answered Dec 10 '20 at 14:24

1

You don't need to close the statement in this case, as Spring's JdbcTemplate will do that for you. In other words, this is a false positive of Sonar.

As documented in the javadoc of PreparedStatementCreator.createPreparedStatement(Connection con) (emphasis mine):

Create a statement in this connection. Allows implementations to use PreparedStatements. The JdbcTemplate will close the created statement.

answered Dec 10 '20 at 14:24

Mark Rotteveel

100,966
191
140
197

yeah i just read about that.but incase jdbc would not have been doing it, what could be the best way to handle this?Or any better way to use PreparedStatementCreator ? – rahul sharma Dec 10 '20 at 14:26
@rahulsharma It is impossible to guess or advice for hypothetical situations that are not presented in a question. The situation presented in your question is clear-cut: there is no need to close as Springs `JdbcTemple` (**not** JDBC!), closes it for you. In general, the advice is to use try-with-resource, but this situation is an exception to that advice. – Mark Rotteveel Dec 10 '20 at 15:47
ok.thanks a lot.since i am trying it first time so i was keen to ask.got clarifications. thanks a ton – rahul sharma Dec 10 '20 at 17:24

How to close PreparedStatement

1 Answers1