SELECT ... FOR UPDATE with Spring JDBC

SELECT ... FOR UPDATE is required to lock specific row in database in order to prevent other query thread to read or make changes to the same row. The lock is released once you update the data or commit it.

As a Spring lover, it's hard to find references about implementing the technique in Spring JDBC template. But finally I managed to use it.

public MyDao {
    private PreparedStatementCreatorFactory queryForUpdateFactory;

    public MyDao() {
        queryForUpdateFactory = new PreparedStatementCreatorFactory(
            "SELECT name FROM people WHERE id = ? FOR UPDATE",
            Arrays.asList(new SqlParameter(Types.INTEGER)));

        // These lines are important!
        queryForUpdateFactory.setUpdatableResults(true);
        queryForUpdateFactory.setResultSetType(
            ResultSet.TYPE_SCROLL_SENSITIVE);
    }


    public void testSelectForUpdate() {
        Object id = "someID";

        PreparedStatementCreator psc = 
            queryForUpdateFactory.newPreparedStatementCreator(
            new Object[] { id });

        RowCallbackHandler rch = new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet rs) throws SQLException {
                String name = rs.getString("name");

                // Do some manipulation
                if (name != "Jack") {
                    name = "Jack Sparrow";
                }

                // Update and release the lock
                rs.updateString("name", name);
            }

        };

        jdbcTemplate.query(psc, rch);
    }
}

I hope it's useful!