How do I get the auto-generated key in JdbcTemplate?

Spring JDBC simplifies database operations by providing an abstraction layer over traditional JDBC. In this blog post, we’ll walk through a practical example of using Spring JDBC to insert a record into a database and obtain the generated key for the newly inserted record.

First, let’s look at the complete code snippet:

package org.kodejava.spring.jdbc;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.math.BigInteger;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.Month;

public class InsertDemoObtainsKey {
    public static void main(String[] args) {
        String insert = "INSERT INTO record (title, release_date, artist_id, label_id, created) VALUES (?, ?, ?, ?, ?)";

        JdbcTemplate template = new JdbcTemplate(new DriverManagerDataSource("jdbc:mysql://localhost/musicdb", "root", ""));
        KeyHolder keyHolder = new GeneratedKeyHolder();

        template.update(con -> {
            PreparedStatement ps = con.prepareStatement(insert, PreparedStatement.RETURN_GENERATED_KEYS);
            ps.setString(1, "Let It Be");
            ps.setDate(2, Date.valueOf(LocalDate.of(1970, Month.MAY, 8)));
            ps.setLong(3, 1L);
            ps.setLong(4, 1L);
            ps.setTimestamp(5, Timestamp.valueOf(LocalDateTime.now()));
            return ps;
        }, keyHolder);

        BigInteger key = (BigInteger) keyHolder.getKeys();
        System.out.println("key = " + key);
    }
}

This code demonstrates how to insert a record into a database table and retrieve the generated key using Spring’s JdbcTemplate.

DataSource Configuration

JdbcTemplate template = new JdbcTemplate(new DriverManagerDataSource("jdbc:mysql://localhost/musicdb", "root", ""));

Here, we create a JdbcTemplate instance, providing it with a DriverManagerDataSource. This data source points to our MySQL database named musicdb, with the username root and an empty password.

SQL Insert Statement

String insert = "INSERT INTO record (title, release_date, artist_id, label_id, created) VALUES (?, ?, ?, ?, ?)";

We define our SQL insert statement with placeholders for the values to be inserted. The table record is assumed to have columns title, release_date, artist_id, label_id, and created.

KeyHolder Initialization

KeyHolder keyHolder = new GeneratedKeyHolder();

A KeyHolder is used to hold the generated keys. In this case, we’re using a GeneratedKeyHolder.

Executing the Update

template.update(con -> {
PreparedStatement ps = con.prepareStatement(insert, PreparedStatement.RETURN_GENERATED_KEYS);
    ps.setString(1, "Let It Be");
    ps.setDate(2, Date.valueOf(LocalDate.of(1970, Month.MAY, 8)));
    ps.setLong(3, 1L);
    ps.setLong(4, 1L);
    ps.setTimestamp(5, Timestamp.valueOf(LocalDateTime.now()));
    return ps;
}, keyHolder);

We use the JdbcTemplate‘s update method, which takes two parameters: a PreparedStatementCreator and a KeyHolder. The PreparedStatementCreator is implemented using a lambda expression that sets the values for the placeholders in our SQL insert statement.

Retrieving the Generated Key

BigInteger key = (BigInteger) keyHolder.getKeys();
System.out.println("key = " + key);

After the update operation, we retrieve the generated key from the KeyHolder. The key is then printed to the console.

Summary

Using Spring JDBC simplifies the process of interacting with the database. In this example, we demonstrated how to insert a record into a MySQL database and retrieve the generated key using JdbcTemplate. This approach ensures clean and manageable code while leveraging the power of Spring’s abstraction over traditional JDBC.

Feel free to experiment with this code, adjusting the database connection details and SQL statements to fit your requirements. Happy coding!

Maven Dependencies

<dependencies>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>6.1.10</version>
    </dependency>
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <version>8.4.0</version>
    </dependency>
</dependencies>

Author
Recent Posts

Wayan

Founder at Kode Java

A Java programmer who lives on the island of Bali, Indonesia. Programming in Spring Framework, Hibernate / JPA, MySQL. To support me working on this project, buy me a cup of coffee, every little bit helps. Thank you.