JDBCTemplate close prepared statement after executing query

Question

I would like to close my connection after fulfilling a DB query. The thing is I am using prepared statement since they are pre-compiled so more secure. I want to close the DB connection so it can be reused later for another query.

The documentation says the following:

/**
 * Create a statement in this connection. Allows implementations to use
 * PreparedStatements. The JdbcTemplate will close the created statement.
 * @param con the connection used to create statement
 * @return a prepared statement
 * @throws SQLException there is no need to catch SQLExceptions
 * that may be thrown in the implementation of this method.
 * The JdbcTemplate class will handle them.
 */
PreparedStatement createPreparedStatement(Connection con) throws SQLException;

I got into a situation where no connection was available in the pool.

I have the following but it throws an exception saying that no operations is allowed after statement closed.

private IRespondent InsertRespondentToken(IRespondent respondent) {
try{
    final String insertRespondent = "insert into respondents_token (SynchroID,TerminalID,QuestionnaireID,ProjectID,Token) values (?,?,?,?,?)";

    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(
            (Connection con) -> {
                try{
                    PreparedStatement pst = con.prepareStatement(insertRespondent, new String[] {"ID"});
                    pst.setInt(1, respondent.getSynchroId());
                    pst.setInt(2, respondent.getTerminalId());

                    pst.setInt(3, respondent.getQuestionnaireId());

                    pst.setInt(4,respondent.getProjectId());

                    respondent.setToken(GenerateUniqueId.getIdentifier());

                    pst.setString(5,respondent.getToken());

                    return pst;
                }
                catch (SQLException e)
                {
                    log.error("Prepared statement failed! Read the stack!",e);
                }
                finally {
                    con.close(); // IS thsi right. Or there is another way of doing it.
                }
                return null;
            }
            ,keyHolder);
    }catch(NullPointerException ex){
        log.error("Error during end element parsing.", ex);
        if (respondent.getId() != -1)
            deleteRespondent(respondent.getId());
        return null;
    }

  return respondent;
 }

Does this answer your question? [Does Spring's JdbcTemplate close the connection after query timeout?](https://stackoverflow.com/questions/20419785/does-springs-jdbctemplate-close-the-connection-after-query-timeout) — Ryuzaki L, Nov 19 '19 at 23:36
`JdbcTemplate` takes care orf all that. Remove the `try/catch` from your code, because now when an exception happens things might go wrong. If you run into these situations, you are propably obtaining a connection somewhere yourself instead of using the `JdbcTemplate` properly. — M. Deinum, Nov 20 '19 at 07:01
Could I add a QueryTimeout at least. To solve the problem which involves that no connection is available from the template. — Nesan Mano, Nov 20 '19 at 15:35

score 0 · Answer 1 · answered Nov 20 '19 at 07:29

You're closing the connection, and by extension the prepared statement you created. So when JdbcTemplate proceeds to execute the statement, the connection (and statement) is already closed. You need to remove the try-catch-finally around your code which closes the connection (and swallows any exception).

See also the example in the Spring documentation, section Retrieving Auto-generated Keys. Preparing the statement and populating its parameters is all you need to do.

Another way of looking at this, is with a basic 'rule' of resource-management: you only close what you create (unless otherwise documented). In this case you did not create the connection, so you are not responsible for closing it.

JDBCTemplate close prepared statement after executing query

1 Answers1