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

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store