Spring Boot GeneratedKeyHolder

last modified July 28, 2023

In this article we show how to get auto-generated Ids from JDBC inserts. GeneratedKeyHolder is used to hold auto-generated keys potentionally returend from JDBC insert statements.

Spring is a popular Java application framework for creating enterprise applications. Spring Boot is an evolution of Spring framework which helps create stand-alone, production-grade Spring based applications with minimal effort.

GeneratedKeyHolder

GeneratedKeyHolder is a standard implementation of the KeyHolder interface, which is used for holding auto-generated keys. The auto-generated keys are potentially returned by JDBC insert statements.

Spring Boot GeneratedKeyHolder example

In the following example we use the GeneratedKeyHolder to retrieve the Id of the created user. In a typical scenario, the applicaiton returns the object that was created back to the user.

build.gradle
...
src
├───main
│   ├───java
│   │   └───com
│   │       └───zetcode
│   │           │   Application.java
│   │           │   MyRunner.java
│   │           ├───mapper
│   │           │       UserMapper.java
│   │           ├───model
│   │           │       MaritalStatus.java
│   │           │       User.java
│   │           └───service
│   │                   IUserService.java
│   │                   UserService.java
│   └───resources
│           application.properties
│           schema-h2.sql
└───test
    └───java

This is the project structure.

plugins {
    id 'org.springframework.boot' version '3.1.1'
    id 'io.spring.dependency-management' version '1.1.0'
    id 'java'
}

group = 'com.example'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '17'

repositories {
    mavenCentral()
}

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-jdbc'
    runtimeOnly 'com.h2database:h2'
}

This is the build.gradle file. The GeneratedKeyHolder resides in the spring-boot-starter-jdbc dependency.

spring.main.banner-mode=off
spring.sql.init.platform=h2
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:testdb

In the application.properties we turn off the banner and set the database driver.

CREATE TABLE users(id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255), marital_status SMALLINT);

This is the SQL to create the users table.

package com.zetcode.model;

import java.util.Objects;
import java.util.StringJoiner;

public class User {

    private Long id;
    private String name;
    private MaritalStatus maritalStatus;

    public User() {
    }

    public User(Long id, String name, MaritalStatus maritalStatus) {
        this.id = id;
        this.name = name;
        this.maritalStatus = maritalStatus;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public MaritalStatus getMaritalStatus() {
        return maritalStatus;
    }

    public void setMaritalStatus(MaritalStatus maritalStatus) {
        this.maritalStatus = maritalStatus;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        User user = (User) o;
        return Objects.equals(id, user.id) &&
                Objects.equals(name, user.name) &&
                maritalStatus == user.maritalStatus;
    }

    @Override
    public int hashCode() {
        return Objects.hash(id, name, maritalStatus);
    }

    @Override
    public String toString() {
        return new StringJoiner(", ", User.class.getSimpleName() + "[", "]")
                .add("id=" + id)
                .add("name='" + name + "'")
                .add("maritalStatus=" + maritalStatus)
                .toString();
    }
}

This is the User model.

package com.zetcode.model;

public enum MaritalStatus {
    SINGLE, MARRIED, DIVORCED, NOT_SPECIFIED
}

We have the MaritalStatus enumeration.

package com.zetcode.mapper;

import com.zetcode.model.MaritalStatus;
import com.zetcode.model.User;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;

public class UserMapper implements RowMapper<User> {

    @Override
    public User mapRow(ResultSet rs, int rowNum) throws SQLException {

        return new User(rs.getLong("id"), rs.getString("name"),
                MaritalStatus.values()[rs.getInt("marital_status")]);
    }
}

The UserMapper maps a row from the result set to the User model class.

package com.zetcode.service;

import com.zetcode.model.MaritalStatus;
import com.zetcode.model.User;
import java.util.List;

public interface IUserService {

    List<User> findAll();
    User findById(Long id);
    User create(String name, MaritalStatus status);
}

We have three contract methods in the IUserService.

package com.zetcode.service;

import com.zetcode.mapper.UserMapper;
import com.zetcode.model.MaritalStatus;
import com.zetcode.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreatorFactory;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.stereotype.Service;

import java.sql.Types;
import java.util.List;
import java.util.Objects;

@Service
public class UserService implements IUserService {

    @Autowired
    private JdbcTemplate jtm;

    @Override
    public List<User> findAll() {

        String sql = "SELECT * FROM users";

        return jtm.query(sql, new UserMapper());
    }

    @Override
    public User findById(Long id) {

        String sql = "SELECT * FROM users WHERE id = ?";

        return jtm.queryForObject(sql, new UserMapper(), id);
    }

    @Override
    public User create(String name, MaritalStatus status) {

        String sql = "INSERT INTO users(name, marital_status) VALUES (?, ?)";

        var decParams = List.of(new SqlParameter(Types.VARCHAR, "name"),
                new SqlParameter(Types.INTEGER, "marital_status"));

        var pscf = new PreparedStatementCreatorFactory(sql, decParams) {
            {
                setReturnGeneratedKeys(true);
                setGeneratedKeysColumnNames("id");
            }
        };

        var psc = pscf.newPreparedStatementCreator(List.of(name, status.ordinal()));

        var keyHolder = new GeneratedKeyHolder();
        jtm.update(psc, keyHolder);

        var uid = Objects.requireNonNull(keyHolder.getKey()).longValue();

        return findById(uid);
    }
}

We have the implementations of the three contract methods.

@Override
public List<User> findAll() {

    String sql = "SELECT * FROM users";

    return jtm.query(sql, new UserMapper());
}

The findAll method finds all users.

@Override
public User findById(Long id) {

    String sql = "SELECT * FROM users WHERE id = ?";

    return jtm.queryForObject(sql, new UserMapper(), id);
}

The findById method finds a user specified by its Id.

String sql = "INSERT INTO users(name, marital_status) VALUES (?, ?)";

This is the SQL to insert a new user to the users table.

var decParams = List.of(new SqlParameter(Types.VARCHAR, "name"),
    new SqlParameter(Types.INTEGER, "marital_status"));

We declare the two SqlParameters for the PreparedStatementCreatorFactory.

var pscf = new PreparedStatementCreatorFactory(sql, decParams) {
    {
        setReturnGeneratedKeys(true);
        setGeneratedKeysColumnNames("id");
    }
};

PreparedStatementCreatorFactory is a helper class that creates multiple PreparedStatementCreator objects with different parameters based on an SQL statement and a single set of parameter declarations.

var psc = pscf.newPreparedStatementCreator(List.of(name, status.ordinal()));

The newPreparedStatementCreator returns a new PreparedStatementCreator for the given parameters.

var keyHolder = new GeneratedKeyHolder();
jtm.update(psc, keyHolder);

A GeneratedKeyHolder is created and passed with the PreparedStatementCreator to the JdbcTemplate's update method.

var uid = Objects.requireNonNull(keyHolder.getKey()).longValue();

We retrieve the auto-generated key value.

return findById(uid);

The retrieved key value is used to find the newly generated user object that is returned back to the caller.

package com.zetcode;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

The Application sets up the Spring Boot application.

package com.zetcode;

import com.zetcode.model.MaritalStatus;
import com.zetcode.service.IUserService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;

@Component
public class MyRunner implements CommandLineRunner {

    private static final Logger logger = LoggerFactory.getLogger(Application.class);

    private final IUserService userService;
    
    @Autowired
    public MyRunner(IUserService userService) {
        this.userService = userService;
    }

    @Override
    public void run(String... args) throws Exception {

        var u1 = userService.create("John Doe", MaritalStatus.SINGLE);
        logger.info("{} created", u1);

        var u2 = userService.create("Bill Dennis", MaritalStatus.MARRIED);
        logger.info("{} created", u2);

        var u3 = userService.create("Lucia Smith", MaritalStatus.SINGLE);
        logger.info("{} created", u3);

        var u4 = userService.create("Robert Brown", MaritalStatus.NOT_SPECIFIED);
        logger.info("{} created", u4);

        var users = userService.findAll();
        logger.info("{}", users);
    }
}

In the MyRunner class, we create four users and later retrieve them all.

$ ./gradlew bootRun

We start the application.

... Started Application in 1.304 seconds (JVM running for 1.74)
... User[id=1, name='John Doe', maritalStatus=SINGLE] created
... User[id=2, name='Bill Dennis', maritalStatus=MARRIED] created
... User[id=3, name='Lucia Smith', maritalStatus=SINGLE] created
... User[id=4, name='Robert Brown', maritalStatus=NOT_SPECIFIED] created
... [User[id=1, name='John Doe', maritalStatus=SINGLE], User[id=2, name='Bill Dennis' ...

The created and retrieved users are shown in the log.

In this article we have worked with the Spring Boot GeneratedKeyHolder.

Author

My name is Jan Bodnar and I am a passionate programmer with many years of programming experience. I have been writing programming articles since 2007. So far, I have written over 1400 articles and 8 e-books. I have over eight years of experience in teaching programming.

List all Spring Boot tutorials.