How to get the auto-incremented ID when using JdbcTemplate
Tags: spring, springboot
Published: 2022-09-16 00:52:53

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.

```sql
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.

```java
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.

```txt
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](https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/core/namedparam/NamedParameterJdbcTemplate.html) to learn more.

```java
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.

```txt
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](https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/support/GeneratedKeyHolder.html).

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

```sql
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/](https://www.springcloud.io/post/2022-06/jdbctemplate-id/)