Retrieving Auto Generated Id With Spring JdbcTemplate

Login | Register

Retrieving Auto Generated Id With Spring JdbcTemplate

Database Spring SpringBoot

When developing database applications, one of the common requirements is to get the auto generated key back from the database after an insert statement has been executed. Different database vendors offer specific commands that can be used to retrieve auto generated keys, for example in MySQL, executing the following would obtain the last inserted key value.

SELECT LAST_INSERT_ID();

Spring’s JdbcTemplate uses a GeneratedKeyHolder class to return the last inserted generated key when executing update commands via PreparedStatements.

As an example, take the following CustomerRepostory class that implements persistence via JdbcTemplates

@Repository
public class CustomerRepository {

  private final static String INSERT_SQL = "insert into CUSTOMER (NAME) values (?)";
  private final Logger log = LoggerFactory.getLogger(this.getClass());

  @Autowired
  JdbcTemplate jdbcTemplate;

  public long createCustomer(String name) {
    KeyHolder key = new GeneratedKeyHolder();
    jdbcTemplate.update(new PreparedStatementCreator() {

      @Override
      public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
        final PreparedStatement ps = connection.prepareStatement(INSERT_SQL, 
            Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, name);
        return ps;
      }
    }, key);

    log.info("Saved customer {} with id {}.", name, key.getKey());

    return key.getKey().longValue();
  }
}

In this example, the PreparedStatement is created passing in the parameter Statement.RETURN_GENERATED_KEYS causing the key to be returned into the key variable.

To test this code, consider the following simple test case.

@RunWith(SpringRunner.class)
@SpringBootTest
public class CustomerRepositoryTests {

  @Autowired
  CustomerRepository customerRepository;

  @Test
  public void shouldBeAbleToRetrieveCustomerId() {
    long firstId = customerRepository.createCustomer("David");
    long secondId = customerRepository.createCustomer("George");
		
    assertThat(firstId, is(notNullValue()));
    assertThat(secondId, is(notNullValue()));
  }
}

When executed, the following is output into the log:

2017-07-08 15:44:41.303  INFO 992 --- [           main] c.d.sample.pk.CustomerRepository         : Saved customer David with id 1.
2017-07-08 15:44:41.306  INFO 992 --- [           main] c.d.sample.pk.CustomerRepository         : Saved customer George with id 2.

In this article, we've seen how easy it is to retrieve the auto generated key when inserting using Spring’s JdbcTemplate.

The full source code for this article can be found on GitHub

About

At Develop In Java, we've been a part of the Java community for over 10 years. If there's anything you'd like us to cover, just ask !

Current versions

  • Java - 1.8
  • Java EE - 7
  • Spring - 4.x