Close

Spring - Using KeyHolder to retrieve database auto-generated keys

[Last Updated: Aug 16, 2017]

This example shows how to retrieve auto generated primary key by the database (via an insert statement). Following method of JdbcTemplate takes KeyHolder argument which will contain the generated key on the successful insert execution.

public int update(final PreparedStatementCreator psc, final KeyHolder generatedKeyHolder)
			throws DataAccessException

Example using KeyHolder

@Component
public class CustomerBean {
  @Autowired
  private JdbcTemplate jdbcTemplate;

  public void createCustomers() {
      String sql = "insert into CUSTOMER (NAME, PHONE, ADDRESS) values (?, ?, ?)";
      KeyHolder keyHolder = new GeneratedKeyHolder();

      jdbcTemplate.update(
              connection -> {
                  PreparedStatement ps = connection.prepareStatement(sql, new String[]{"ID"});
                  ps.setString(1, "Jake");
                  ps.setString(2, "234-333-627");
                  ps.setString(3, "345 Move Dr, Shine Hill");
                  return ps;
              }, keyHolder);

      Number key = keyHolder.getKey();
      System.out.println("Newly persisted customer generated id: " + key.longValue());
      System.out.println("-- loading customer by id --");
      System.out.println(loadCustomerById(key.longValue()));

  }

  public Customer loadCustomerById(long id) {
      List<Customer> customers = jdbcTemplate.query("select * from CUSTOMER where id =?",
              new Object[]{id}, (resultSet, i) -> {
                  return Customer.create(
                          resultSet.getLong("ID"),
                          resultSet.getString("NAME"),
                          resultSet.getString("PHONE"),
                          resultSet.getString("ADDRESS"));
              });

      if (customers.size() == 1) {
          return customers.get(0);
      }
      return null;
  }
}
public class Customer {
  private long id;
  private String name;
  private String address;
  private String phone;
    .............
}

src/main/resources/createCustomerTable.sql

CREATE TABLE CUSTOMER(
ID BIGINT  PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(255),
PHONE VARCHAR(15),
ADDRESS VARCHAR(255)
);

Configuration and main class

@Configuration
@ComponentScan
public class AppConfig {

  @Bean
  public DataSource h2DataSource() {
      return new EmbeddedDatabaseBuilder()
              .setType(EmbeddedDatabaseType.H2)
              .addScript("createCustomerTable.sql")
              .build();
  }

  @Bean
  public JdbcTemplate jdbcTemplate() {
      return new JdbcTemplate(h2DataSource());
  }

  public static void main(String[] args) {
      AnnotationConfigApplicationContext context =
              new AnnotationConfigApplicationContext(AppConfig.class);

      CustomerBean customerBean = context.getBean(CustomerBean.class);
      customerBean.createCustomers();
  }
}

Output

Newly persisted customer generated id: 1
-- loading customer by id --
Customer{id=1, name='Jake', address='234-333-627', phone='345 Move Dr, Shine Hill'}

Example Project

Dependencies and Technologies Used:

  • spring-context 4.3.6.RELEASE: Spring Context.
  • spring-jdbc 4.3.6.RELEASE: Spring JDBC.
  • h2 1.4.196: H2 Database Engine.
  • JDK 1.8
  • Maven 3.3.9

KeyHolder Example Select All Download
  • key-holder-example
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • CustomerBean.java
          • resources

    See Also