How to get the auto-incremented ID when using JdbcTemplate

This article will teach you how to get auto-incrementing IDs when using JdbcTemplate or NamedParameterJdbcTemplate.

Table (MYSQL)

Suppose the following data table is available.

CREATE TABLE `user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `balance` decimal(10,2) DEFAULT NULL,
  `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `enabled` tinyint unsigned NOT NULL,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `update_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='user';

As you can see, the id field is an auto-incrementing column.

JdbcTemplate

spring-jdbc provides the GeneratedKeyHolder object to get the auto-incremented ID value after data insertion.

package io.springcloud.test;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.time.LocalDateTime;

import javax.sql.DataSource;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.test.context.SpringBootTest.WebEnvironment;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.test.annotation.Rollback;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.transaction.annotation.Transactional;

import io.springboot.demo.DemoApplication;
import lombok.extern.slf4j.Slf4j;

@RunWith(SpringRunner.class)
@SpringBootTest(classes = DemoApplication.class, webEnvironment = WebEnvironment.RANDOM_PORT)
@Slf4j
public class DemoApplicationTest {

    @Autowired
    DataSource dataSource;

    @Test
    @Transactional
    @Rollback(false)
    public void test() {

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        // Create GeneratedKeyHolder object
        GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();

        String sql = "INSERT INTO `user`(`balance`, `create_at`, `enabled`, `name`, `update_at`) VALUES(?, ?, ?, ?, ?);";

        // To insert data, you need to pre-compile the SQL and set up the data yourself.
        int rowsAffected = jdbcTemplate.update(conn -> {

            // Pre-compiling SQL
            PreparedStatement preparedStatement = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

            // Set parameters
            preparedStatement.setBigDecimal(1, new BigDecimal("15.88"));
            preparedStatement.setObject(2, LocalDateTime.now());
            preparedStatement.setBoolean(3, Boolean.TRUE);
            preparedStatement.setString(4, "JdbcTemplate");
            preparedStatement.setObject(5, LocalDateTime.now());

            return preparedStatement;

        }, generatedKeyHolder);


        // Get auto-incremented ID
        Integer id = generatedKeyHolder.getKey().intValue();

        log.info("rowsAffected = {}, id={}", rowsAffected, id);
    }
}

The output log is as follows, everything is OK.

2022-06-06 17:03:11.240  INFO 8964 --- [           main] io.springcloud.test.DemoApplicationTest  : rowsAffected = 1, id=11

NamedParameterJdbcTemplate

The usage of NamedParameterJdbcTemplate is not much different from JdbcTemplate. But it supports using named parameters in SQL instead of ?, by this feature we can directly use objects or Map as parameters. Very friendly. In actual development, it is more recommended to use it.

The NamedParameterJdbcTemplate also provides more rich methods, you can refer to the documentation to learn more.

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.test.context.SpringBootTest.WebEnvironment;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.test.annotation.Rollback;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.transaction.annotation.Transactional;

import io.springboot.demo.DemoApplication;
import lombok.extern.slf4j.Slf4j;

@RunWith(SpringRunner.class)
@SpringBootTest(classes = DemoApplication.class, webEnvironment = WebEnvironment.RANDOM_PORT)
@Slf4j
public class DemoApplicationTest {

    @Autowired
    DataSource dataSource;

    @Test
    @Transactional
    @Rollback(false)
    public void test() {

        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

        // The GeneratedKeyHolder object is used to get the auto-incrementing ID.
        GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();

        // SQL placeholders can use named parameters instead of "?".
        String sql = "INSERT INTO `user`(`balance`, `create_at`, `enabled`, `name`, `update_at`) VALUES(:balance, :create_at, :enabled, :name, :update_at);";

        // params
        Map<String, Object> params = new HashMap<>();
        params.put("balance", new BigDecimal("25.66"));
        params.put("create_at", LocalDateTime.now());
        params.put("enabled", Boolean.FALSE);
        params.put("name", "NamedParameterJdbcTemplate");
        params.put("update_at", LocalDateTime.now());

        int rowsAffected = namedParameterJdbcTemplate.update(sql, new MapSqlParameterSource(params), generatedKeyHolder);

        Integer id = generatedKeyHolder.getKey().intValue();

        log.info("rowsAffected = {}, id={}", rowsAffected, id);
    }
}

The output log is as follows, everything is OK.

2022-06-06 17:10:19.167  INFO 12408 --- [           main] io.springcloud.test.DemoApplicationTest  : rowsAffected = 1, id=12

Summary

Get the auto-incremented ID through the GeneratedKeyHolder object.

If you use JdbcTemplate, then you need to pre-compile the SQL and set the parameters yourself, which is more troublesome. It is more recommended to use NamedParameterJdbcTemplate.

GeneratedKeyHolder has some other methods, if you want to know more, you can refer to its documentation.

Finally, take a look at the 2 data we inserted.

mysql> select * from `user`;
+----+---------+---------------------+---------+----------------------------+---------------------+
| id | balance | create_at           | enabled | name                       | update_at           |
+----+---------+---------------------+---------+----------------------------+---------------------+
| 11 |   15.88 | 2022-06-06 17:03:11 |       1 | JdbcTemplate               | 2022-06-06 17:03:11 |
| 12 |   25.66 | 2022-06-06 17:10:19 |       0 | NamedParameterJdbcTemplate | 2022-06-06 17:10:19 |
+----+---------+---------------------+---------+----------------------------+---------------------+
2 rows in set (0.00 sec)

Reference https://www.springcloud.io/post/2022-06/jdbctemplate-id/