Spring JDBC - How to return auto-generated keys using PreparedStatementCreator and PreparedStatementCallback?

1. Introduction

There are cases when you rely on Database server to auto generate values for some columns of the table. E.g. auto increment primary key, creation_date or any other column while inserting records. There is a way with which you can retrieve those auto-generated keys when you execute the insert statement. Let's see how you can do this using Spring JDBC but first we will see what PreparedStatementCreator and PreparedStatementCallback interfaces are.

2. What is PreparedStatementCreator?

There are cases when you want to create PreparedStatement yourself. One use case is to return auto generated keys. In that case, Spring JDBC provides you an option to do so by providing implementation for PreparedStatementCreator. Let's create an implementation of PreparedStatementCreator which sets those options.

public class ReturnGeneratedKeysPreparedStatementCreator implements PreparedStatementCreator, SqlProvider {
  private final String sql;
  private String[] generatedColumnNames;

  public ReturnGeneratedKeysPreparedStatementCreator(String sql) {
    this(sql, Collections.emptyList());
  }

  public ReturnGeneratedKeysPreparedStatementCreator(String sql, List<String> generatedColumnNames) {
    this.sql = sql;
    this.generatedColumnNames = Objects.nonNull(generatedColumnNames)
    ? generatedColumnNames.toArray(new String[generatedColumnNames.size()])
    : new String[0];
  }

  @Override
  public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
    return generatedColumnNames.length > 0 ? con.prepareStatement(this.sql, this.generatedColumnNames)
    : con.prepareStatement(this.sql, Statement.RETURN_GENERATED_KEYS);
  }

  @Override
  public String getSql() {
    return this.sql;
  }

}

There are two options which you can use to retrieve generated keys. >

• Get all the generated keys.
• Pass the column you want to retrieve.

3. What is PreparedStatementCallback?

In normal usage, you might never need to implement this interface but will provide implementation only if you need to execute some other code e.g. retrieval of auto-generated keys. Let's see how you can do this with implementing this interface.

class GeneratedKeysPreparedStatementCallback implements PreparedStatementCallback<Integer> {

  @Override
  public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException {
    int updated = ps.executeUpdate();
    if (updated > 0) {
      try (ResultSet rs = ps.getGeneratedKeys()) {
        if (rs.next())
          return rs.getInt("id");
      }
   
      throw new DataRetrievalFailureException("There was no key auto generated by the database");
    }
    throw new DataRetrievalFailureException("Nothing was updated");
  }
}

Usage

Integer key = jdbcTemplate.execute(new ReturnGeneratedKeysPreparedStatementCreator(
    "insert into product(name, category, description) values('Acer Laptop', 'laptop', 'Predator series')"),
    new GeneratedKeysPreparedStatementCallback());
log.info(() -> String.format("Product saved in database with key: %d", key));

You can see the full example code on Github.

---

Tags: Spring Framework, Spring JDBC, auto-generated keys in JDBC, PreparedStatementCallback example, PreparedStatementCreator example

← Back home