Spring - Retrieving auto-generated keys using SimpleJdbcInsert

[Updated: Sep 15, 2017, Created: Sep 15, 2017]

This example shows how to use SimpleJdbcInsert to retrieve auto generated primary key.

Example

Using SimpleJdbcInsert

@Repository
public class PersonDao implements Dao<Person> {
  @Autowired
  private DataSource dataSource;
  private JdbcTemplate jdbcTemplate;
  private SimpleJdbcInsert jdbcInsert;

  @PostConstruct
  private void postConstruct() {
      jdbcTemplate = new JdbcTemplate(dataSource);
      jdbcInsert = new SimpleJdbcInsert(dataSource).withTableName("PERSON")
                                                   .usingGeneratedKeyColumns("ID");
  }

  @Override
  public long save(Person person) {
      SqlParameterSource parameters = new BeanPropertySqlParameterSource(person);
      Number number = jdbcInsert.executeAndReturnKey(parameters);
      if (number != null) {
          return number.longValue();
      }
      throw new RuntimeException("Cannot retrieve primary key");
  }

  @Override
  public Person load(long id) {
      List<Person> persons = jdbcTemplate.query("select * from Person where id =?",
              new Object[]{id}, (resultSet, i) -> {
                  return toPerson(resultSet);
              });

      if (persons.size() == 1) {
          return persons.get(0);
      }
      return null;
  }
    .............
  @Override
  public List<Person> loadAll() {
      return jdbcTemplate.query("select * from Person", (resultSet, i) -> {
          return toPerson(resultSet);
      });
  }

  private Person toPerson(ResultSet resultSet) throws SQLException {
      Person person = new Person();
      person.setId(resultSet.getLong("ID"));
      person.setFirstName(resultSet.getString("FIRST_NAME"));
      person.setLastName(resultSet.getString("LAST_NAME"));
      person.setAddress(resultSet.getString("ADDRESS"));
      return person;
  }
}
public class Person {
  private long id;
  private String firstName;
  private String lastName;
  private String address;
    .............
}

src/main/resources/createPersonTable.sql

CREATE TABLE PERSON(
ID BIGINT  PRIMARY KEY AUTO_INCREMENT,
FIRST_NAME VARCHAR(255),
LAST_NAME VARCHAR(255),
ADDRESS VARCHAR(255)
);

A client bean

@Component
public class PersonClient {

  @Autowired
  Dao<Person> personDao;

  public void process() {
      Person person = Person.create("Dana", "Whitley", "464 Gorsuch Drive");
      System.out.println("saving: " + person);
      long generatedId = personDao.save(person);
      System.out.println("generated id: " + generatedId);
      Person loadedPerson = personDao.load(generatedId);
      System.out.println("Loaded by id: "+loadedPerson);
  }
}

Java Config

@Configuration
@ComponentScan
public class AppConfig {

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

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

Output

saving: Person{id=0, firstName='Dana', lastName='Whitley', address='464 Gorsuch Drive'}
generated id: 1
Loaded by id: Person{id=1, firstName='Dana', lastName='Whitley', address='464 Gorsuch Drive'}

Example Project

Dependencies and Technologies Used :

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

Retrieving Generated Key with SimpleJdbcInsert Example Select All Download
  • simple-jdbc-insert-with-return-key
    • src
      • main
        • java
          • com
            • logicbig
              • example
        • resources

See Also