How To Obtain Auto-Generated Keys

This application is an example of retrieving the database auto-generated primary keys.

Key points:

• JPA style, retrieve the auto-generated keys via getId()
• JDBC style, retrieve the auto-generated keys via JdbcTemplate
• JDBC style, retrieve the auto-generated keys via SimpleJdbcInsert

Author.java

@Data
@Entity
public class Author implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private int age;
    private String name;
    private String genre;
}

AuthorRepository.java

@Repository
public interface AuthorRepository extends JpaRepository<Author, Long> {
}

AuthorDao.java

public interface AuthorDao {
    public long insertAuthor(int age, String name, String genre);
}

JdbcTemplateDao.java

@RequiredArgsConstructor
@Repository
public class JdbcTemplateDao implements AuthorDao {

    private static final String SQL_INSERT = "INSERT INTO author (age, name, genre) VALUES (?, ?, ?);";

    private final JdbcTemplate jdbcTemplate;

    @Override
    @Transactional    
    public long insertAuthor(int age, String name, String genre) {
        KeyHolder keyHolder = new GeneratedKeyHolder();

        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(SQL_INSERT, Statement.RETURN_GENERATED_KEYS);
            
            // or
            // PreparedStatement ps = connection
            //      .prepareStatement(SQL_INSERT, new String[]{"id"});
            
            // or
            // PreparedStatement ps = connection
            //      .prepareStatement(SQL_INSERT, new int[] {1});
            
            ps.setInt(1, age);
            ps.setString(2, name);
            ps.setString(3, genre);
            return ps;
        }, keyHolder);

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

SimpleJdbcInsertDao.java

@Repository
public class SimpleJdbcInsertDao implements AuthorDao {

    private final SimpleJdbcInsert simpleJdbcInsert;

    public SimpleJdbcInsertDao(DataSource dataSource) {
        this.simpleJdbcInsert = new SimpleJdbcInsert(dataSource)
                .withTableName("author")
                .usingGeneratedKeyColumns("id");
    }

    @Override
    @Transactional
    public long insertAuthor(int age, String name, String genre) {
        Map<String, String> map = new HashMap<>();
        map.put("age", String.valueOf(age));
        map.put("name", name);
        map.put("genre", genre);

        return simpleJdbcInsert.executeAndReturnKey(map).longValue();
    }
}

BookStoreService.java

@RequiredArgsConstructor
@Service
public class BookstoreService {

    private final JdbcTemplateDao jdbcTemplateDao;
    private final SimpleJdbcInsertDao simpleJdbcInsertDao;
    private final AuthorRepository authorRepository;

    public void insertAuthorGetAutoGeneratedKeyViaJdbcTemplate() {
        long pk = jdbcTemplateDao.insertAuthor(23, "Mark Janel", "Anthology");
        System.out.println("Auto generated key: " + pk);
    }

    public void insertAuthorGetAutoGeneratedKeyViaSimpleJdbcInsert() {
        long pk = simpleJdbcInsertDao.insertAuthor(42, "Olivia Goy", "Horror");
        System.out.println("Auto generated key: " + pk);
    }

    public void insertAuthorGetAutoGeneratedKeyViaGetId() {
        Author author = new Author();
        author.setAge(38);
        author.setName("Alicia Tom");
        author.setGenre("Anthology");
        authorRepository.save(author);

        long pk = author.getId();
        System.out.println("Auto generated key: " + pk);
    }
}

MainApp.java

application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/bookstoredb?createDatabaseIfNotExist=true
spring.datasource.username=root
spring.datasource.password=root

spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect

spring.jpa.open-in-view=false

Output —

Auto generated key: 1
Auto generated key: 2
Hibernate: insert into author (age, genre, name) values (?, ?, ?)
Auto generated key: 3