Unsure how to return generated column id value using Spring jdbcTemplate and PreparedStatementSetter

Question

Before refactoring, I was inserting a row into the db using jdbcTemplate, preparedStatementCreator and a GeneratedKeyHolder to store the generated id of the inserted row.

    jdbcTemplate.update(SomePreparedStatementCreator, generatedKeyHolder)  

However, now I am trying to execute an update using my jdbcTemplate but now with a preparedStatementSetter. The only method I can see for this us jdbcTemplate.update(String sql, PreparedStatementSetter) but now I am not sure how to return the id of the generated column.

    PreparedStatementSetter pss = new PreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement preparedStatement)
                throws SQLException {
            preparedStatement.setString(1, pupil.getFirstname());
            preparedStatement.setString(2, pupil.getSurname());
            preparedStatement.setString(3, pupil.getGivenName());
            preparedStatement.setDate(4, pupil.getDob());
        }
    };      


    jdbcTemplate.update("INSERT INTO STUDENTS "+ "(FIRSTNAME, SURNAME, GIVEN_NAME, DOB) VALUES (?,?,?,?)",pss);

Assuming my approach to preparedStatements is correct, does anyone know how I can return the value of the generated id?

Answer 1

Generated keys may be returned with JdbcTemplate#update(PreparedStatementCreator,KeyHolder) method. But to achieve this you need to change your request, based on org.springframework.jdbc.core.PreparedStatementCreatorFactory.

Instead I would recommend to use for inserting SimpleJdbcInsert utility class.

If your DB fields are the very same as your entities' fields, then you can simply do as following:

Number key = jdbcInsert.executeAndReturnKey(new BeanPropertySqlParameterSource(entity));
//set generated key
if (key != null){
    entity.setId(key.longValue());
}

If it is not (as in your case), then

//define parameters
Map<String, Object> parameters = new HashMap<String, Object>();
parameters.put("FIRSTNAME", pupil.getFirstname());
parameters.put("SURNAME", pupil.getSurname());
parameters.put("GIVEN_NAME", pupil.getGivenName());
parameters.put("DOB", pupil.getDob());
//execute insert
Number key = jdbcInsert.executeAndReturnKey(new MapSqlParameterSource(parameters));
//set generated key
if (key != null){
    pupil.setId(key.longValue());
}

jdbcInsert parameter you need to define once for each entity, and it will look like the following:

SimpleJdbcInsert jdbcInsert = new SimpleJdbcInsert(getJdbcTemplate());

jdbcInsert.withTableName(entityName); //e.g. "STUDENTS"

jdbcInsert.setGeneratedKeyName(idColumn); //e.g. "ID"

jdbcInsert.setColumnNames(columnNames); //e.g. Arrays.asList("FIRSTNAME", "SURNAME", "GIVEN_NAME", "DOB")