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

缺乏、安全感 2023-09-26 10:35 48阅读 0赞

本文将教您在使用`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`还提供了更丰富的方法，可以参考[文档][Link 1]来了解更多信息。

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`还有其他一些方法，如果您想了解更多信息，可以参考其[文档][Link 2]。

最后，看看我们插入的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)

[Link 1]: https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/core/namedparam/NamedParameterJdbcTemplate.html
[Link 2]: https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/support/GeneratedKeyHolder.html