Spring - Retrieving auto-generated keys using SimpleJdbcInsert

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;
    }
    .............
    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