如何在Spring JDBC 中获取自动生成的 ID

r囧r小猫 2023-09-26 10:10 55阅读 0赞

在此页面上，我们将学习如何在Spring JDBC中获取自动生成的ID。在Spring JDBC 中，我们可以使用`JdbcTemplate`方法和`SimpleJdbcInsert`方法来执行 SQL 查询，并将自动生成的密钥作为`KeyHolder`返回。

### KeyHolder ###

`KeyHolder`是用于检索自动生成的密钥的接口。`KeyHolder`由 JDBC 插入语句返回。通常，键作为`List`包含每行键的键的`Map`键返回。  
`KeyHolder`具有以下方法。  
**getKey（）** ：从第一张地图中检索第一个项目。  
**getKeyAs(Class<T> keyType)** **：** 从给定键类型的第一个映射中检索第一个项目。  
**getKeyList()** ：返回对包含密钥的列表的引用。  
**getKeys()** ：检索第一个密钥映射。

### 使用 Jdbc 模板更新 ###

从春季文档中查找方法声明。`JdbcTemplate.update`

int update(PreparedStatementCreator psc, KeyHolder generatedKeyHolder) throws DataAccessException

**1.** 该方法使用`PreparedStatementCreator`发出更新语句。  
**2.**生成的密钥将被放入给定的`KeyHolder`.  
**3.** 该方法返回受影响的行数。  
  
**示例** ：

String sql = "insert into person (name, age) values (?, ?)";
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(connection -> {
    			PreparedStatement pst = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
    			pst.setString(1, p.getName());
    			pst.setInt(2, p.getAge());
    			return pst;
    		}, keyHolder);
    int id = keyHolder.getKey().intValue();

### 使用简单Jdbc插入 ###

`SimpleJdbcInsert`为工作台提供简单的插入功能。我们需要提供的只是表的名称和包含列名和列值的`Map`。查找执行`SimpleJdbcInsert`查询并返回自动生成的密钥的方法。

Number executeAndReturnKey(Map<String,?> args)
    Number executeAndReturnKey(SqlParameterSource parameterSource)
    KeyHolder executeAndReturnKeyHolder(Map<String,?> args)
    KeyHolder executeAndReturnKeyHolder(SqlParameterSource parameterSource)

**示例**：  
在这里，我们使用`JdbcTemplate.update`和`KeyHolder`检索自动生成的密钥。

Map<String, Object> params = new HashMap<String, Object>();
    params.put("name", p.getName());
    params.put("age", p.getAge());
    
    KeyHolder keyHolder = simpleJdbcInsert
    	.withTableName("person")
    	.usingColumns("name", "age")
    	.usingGeneratedKeyColumns("id")
    	.withoutTableColumnMetaDataAccess()	    
    	.executeAndReturnKeyHolder(params);
    int id = keyHolder.getKey().intValue();

### 完整示例 ###

**表：person**

CREATE TABLE `person` (
    	`id` INT(5) NOT NULL AUTO_INCREMENT,
    	`name` VARCHAR(100) NOT NULL,
    	`age` INT(3) NOT NULL,
    	PRIMARY KEY (`id`)
    )

**PersonDAO.java**

package com.concretepage;
    import java.sql.PreparedStatement;
    import java.sql.Statement;
    import java.util.HashMap;
    import java.util.Map;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
    import org.springframework.jdbc.support.GeneratedKeyHolder;
    import org.springframework.jdbc.support.KeyHolder;
    import org.springframework.stereotype.Repository;
    
    @Repository
    public class PersonDAO {
      @Autowired
      private JdbcTemplate jdbcTemplate;
      
      private SimpleJdbcInsert simpleJdbcInsert;
      
      @Autowired
      private void setSimpleJdbcInsert(JdbcTemplate jdbcTemplate) {
    	simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate);
      }
      
      public void addPersonUsingJdbcTemplate(Person p) {
    	String sql = "insert into person (name, age) values (?, ?)";
    	KeyHolder keyHolder = new GeneratedKeyHolder();
    	jdbcTemplate.update(connection -> {
    	            PreparedStatement pst = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
    	            pst.setString(1, p.getName());
    	            pst.setInt(2, p.getAge());
    	            return pst;
    	        }, keyHolder);
    	int id = keyHolder.getKey().intValue();
    	p.setId(id);
    	System.out.println(id);
      }
      
      public void addPersonUsingSimpleJdbcInsert(Person p) {
    	Map<String, Object> params = new HashMap<String, Object>();
    	params.put("name", p.getName());
    	params.put("age", p.getAge());
    	
    	KeyHolder keyHolder = simpleJdbcInsert
    	    .withTableName("person")
    	    .usingColumns("name", "age")
    	    .usingGeneratedKeyColumns("id")
    	    .withoutTableColumnMetaDataAccess()	    
    	    .executeAndReturnKeyHolder(params);
    	int id = keyHolder.getKey().intValue();
    	p.setId(id);
    	System.out.println(id);
      }  
    }

**application.properties**

spring.datasource.url=jdbc:mysql://localhost:3306/concretepage
    spring.datasource.username=root
    spring.datasource.password=cp
    spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

**Person.java**

package com.concretepage;
    public class Person {
        private int id;
    	private String name;
    	private int age;
    	public Person(String name, int age) {
    	  this.name = name;
    	  this.age = age;
    	}
    	public int getId() {
    	  return id;
    	}
    	public void setId(int id) {
    	  this.id = id;
    	}
    	public String getName() {
    	  return name;
    	}
    	public int getAge() {
    	  return age;
    	}
    	@Override
    	public String toString() {
    	  return id + " - " + name + " - " + age;
    	}
    }

**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/maven-v4_0_0.xsd">
        <modelVersion>4.0.0</modelVersion>
        <groupId>com.concretepage</groupId>
        <artifactId>soap-ws</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <packaging>jar</packaging>
        <name>spring-demo</name>
        <description>Spring Demo Application</description>
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.7.1</version>
            <relativePath />
        </parent>
        <properties>
            <java.version>11</java.version>
        </properties>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.30</version>
            </dependency>
        </dependencies>
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
        </build>
    </project>

**MyApplication.java**

package com.concretepage;
    
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    import org.springframework.context.ApplicationContext;
    
    @SpringBootApplication
    public class MyApplication {
      public static void main(String[] args) {
    	ApplicationContext ctx = SpringApplication.run(MyApplication.class, args);
    	PersonDAO personDAO = ctx.getBean(PersonDAO.class);
    	
    	Person p1 = new Person("Mohan", 25);
    	personDAO.addPersonUsingJdbcTemplate(p1);
    	System.out.println(p1);
    	Person p2 = new Person("Shiva", 30);
    	personDAO.addPersonUsingSimpleJdbcInsert(p2);
    	System.out.println(p2);
      }
    }

#### 引用 ####

[Interface KeyHolder][]  
[Class JdbcTemplate][]  
[Class SimpleJdbcInsert][]

#### 下载源代码 ####

[how-to-get-auto-generated-id-in-spring-jdbc.zip][]

[Interface KeyHolder]: https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/support/KeyHolder.html
[Class JdbcTemplate]: https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/core/JdbcTemplate.html
[Class SimpleJdbcInsert]: https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/core/simple/SimpleJdbcInsert.html
[how-to-get-auto-generated-id-in-spring-jdbc.zip]: https://www.concretepage.com/spring/download/how-to-get-auto-generated-id-in-spring-jdbc.zip