ZetCode

Spring Boot GeneratedKeyHolder

last modified July 6, 2020

Spring Boot GeneratedKeyHolder tutorial shows 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. 

pom.xml
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.

pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
         http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.zetcode</groupId>
    <artifactId>SpringBootGeneratedKeyHolder</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>13</maven.compiler.source>
        <maven.compiler.target>13</maven.compiler.target>
    </properties>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.4.RELEASE</version>
    </parent>

    <dependencies>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

This is the pom.xml file. The GeneratedKeyHolder resides in the spring-boot-starter-jdbc dependency.

resources/application.properties
spring.main.banner-mode=off
spring.datasource.platform=h2
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:testdb;MODE=PostgreSQL

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

resources/schema-h2.sql
CREATE TABLE users(id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255), marital_status SMALLINT);

This is the SQL to create the users table.

com/zetcode/model/User.java
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.

com/zetcode/model/MaritalStatus.java
package com.zetcode.model;

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

We have the MaritalStatus enumeration.

com/zetcode/mapper/UserMapper.java
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.

com/zetcode/service/UserService.java
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.

com/zetcode/service/UserService.java
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 Object[]{id},
                new UserMapper());
    }

    @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 Object[]{id},
            new UserMapper());
}

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.

com/zetcode/Application.java
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.

com/zetcode/MyRunner.java
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);

    @Autowired
    private IUserService 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. 

$ mvn spring-boot:run

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 tutorial, we have worked with the Spring Boot GeneratedKeyHolder. List Spring Boot tutorials.

List all Spring Boot tutorials.