如何在使用 JdbcTemplate时获取自动递增的 ID

本文将教您在使用JdbcTemplate或NamedParameterJdbcTemplate时如何获取自动递增的 ID。

表 （MYSQL）

假设以下数据表可用。

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';

如您所见，id字段是一个自动递增的列。

JDBC模板

spring-jdbc提供GeneratedKeyHolder对象以在数据插入后获取自动递增的 ID 值。

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);
    }
}

输出日志如下，一切正常。

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

命名参数Jdbc模板

NamedParameterJdbcTemplate的用法与JdbcTemplate没有太大区别。但它支持在SQL中使用命名参数而不是?，通过此功能，我们可以直接使用对象或Map作为参数。在实际开发中，更建议使用它。​​​​​​​

NamedParameterJdbcTemplate还提供了更丰富的方法，可以参考文档来了解更多信息。

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);
    }
}

输出日志如下，一切正常。

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

总结

通过GeneratedKeyHolder对象获取自动递增的 ID。

如果使用JdbcTemplate，则需要预编译SQL并自行设置参数，这样比较麻烦。更建议使用NamedParameterJdbcTemplate 。​​​​​​​

GeneratedKeyHolder还有其他一些方法，如果您想了解更多信息，可以参考其文档。

最后，看看我们插入的2个数据。

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)

​​​​​​​