CODEWITHSUNDEEP
javaprepared-statementspring-jdbc
andy007
andy007

Reputation: 917

Spring-Jdbc Template and Prepared statement

Do I need to close Prepared Statement and Connection (jt.getDataSource().getConnection()) when using Spring-Jdbc Template? Or they will be closed by Template mechanizm?

public void updateRow() throws SQLException {

        final int i = 100;
        final int y = 2;

        PreparedStatementCreator creator = new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement updateSales = con.prepareStatement(
                "update ignor set ignored_id=? where id=?");
                updateSales.setInt(1, i);
                updateSales.setInt(2, y);
                return updateSales;
            }
        };

        PreparedStatement updateIgnor = creator.createPreparedStatement(jt.getDataSource().getConnection());
        int k = updateIgnor.executeUpdate();
        System.out.println("rows updated = " + k);

    }

Upvotes: 2

Views: 18544

Answers (2)

Thomas Risberg
Thomas Risberg

Reputation: 976

If you want to use the PreparedStatementCreator, instead of calling the JdbcTemplatemethods that take an SQL statement as a parameter, you should use the JdbcTemplate method that takes your PreparedStatementCreator as a parameter.

In your example remove: 

PreparedStatement updateIgnor = creator.createPreparedStatement(jt.getDataSource().getConnection());
int k = updateIgnor.executeUpdate();

and replace it with:

int k = jt.update(creator);

That way the JdbcTemplate will handle the statement and connection resources plus any transaction management and close the resources as needed.

Upvotes: -2

Lokeswaraswamy Gubba
Lokeswaraswamy Gubba

Reputation: 56

By default, the JDBCTemplate does its own PreparedStatement internally, if you just use the .update(String sql, Object ... args) form. Spring, and your database, will manage the compiled query for you, so you don't have to worry about opening, closing, resource protection, etc.

Upvotes: 4

Related Questions