JdbcTemplate、NamedParameterJdbcTemplate 执行数据插入后返回主键写法

一、简单需求

某业务操作，在执行数据保存后，需要查询具体的的数据信息，供二次确认。 从后台开发角度而言，转换成：插入1条数据库表记录后，如何返回当前插入数据的主键。

二、准备工作

下面我们以JdbcTemplate、NamedParameterJdbcTemplate 为例来进行说明，为简单起见，我们以内存数据库H2
来进行演示。

首先，新建springboot工程，引入jdbc相关模块，在配置文件中引入数据源相关配置。

三、JdbcTemplate 实现

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
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.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.transaction.annotation.Transactional;

import com.fly.template.bean.StudentVO;

import lombok.extern.slf4j.Slf4j;

@Slf4j
@Transactional
@SpringBootTest(webEnvironment = WebEnvironment.NONE)
public class JdbcTemplateTest2
{
    @Autowired
    JdbcTemplate jdbcTemplate;
    
    @BeforeEach
    public void init()
    {
        // execute可以执行所有SQL语句，因为没有返回值，一般用于执行DDL语句
        jdbcTemplate.execute("drop table if exists student");
        jdbcTemplate.execute("create table student(id bigint NOT NULL AUTO_INCREMENT, stu_name varchar(50), primary key(id))");
        jdbcTemplate.execute("insert into student(stu_name) values('Jack')");
        jdbcTemplate.execute("insert into student(stu_name) values('Phil')");
        jdbcTemplate.execute("insert into student(stu_name) values('Jenny')");
        
        // batchUpdate
        jdbcTemplate.batchUpdate("insert into student(stu_name) values(?)", Arrays.asList(new Object[][] {{"Tom"}, {"Jerry"}}));
        log.info("::: init success!!");
        log.info(">>>>> before: {}", jdbcTemplate.queryForObject("select count(*) from student", Long.class));
    }
    
    @AfterEach
    public void after()
    {
        log.info(">>>>> after : {}", jdbcTemplate.queryForObject("select count(*) from student", Long.class));
    }
    
    /**
     * 查询主键
     */
    @Test
    public void testQueryPk()
    {
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator()
        {
            @Override
            public PreparedStatement createPreparedStatement(Connection conn)
                throws SQLException
            {
                // 预处理,注意参数PreparedStatement.RETURN_GENERATED_KEYS
                PreparedStatement ps = conn.prepareStatement("insert into student(stu_name) values(?)", PreparedStatement.RETURN_GENERATED_KEYS);
                ps.setString(1, "Chery");
                return ps;
            }
        }, keyHolder);
        log.info("pk: {}", keyHolder.getKey().longValue());
        
        // lambda写法
        jdbcTemplate.update((conn) -> {
            PreparedStatement ps = conn.prepareStatement("insert into student(stu_name) values(?)", PreparedStatement.RETURN_GENERATED_KEYS);
            ps.setString(1, "Chery");
            return ps;
        }, keyHolder);
        log.info("pk: {}", keyHolder.getKey().longValue());
    }
    
    /**
     * 查询非自增主键
     */
    @Test
    public void testQueryPk2()
    {
        jdbcTemplate.execute("drop table if exists student2");
        jdbcTemplate.execute("create table student2(id bigint NOT NULL, stu_name varchar(50), primary key(id))");
        
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update((conn) -> {
            PreparedStatement ps = conn.prepareStatement("insert into student2(id, stu_name) values(?,?)", PreparedStatement.RETURN_GENERATED_KEYS);
            ps.setInt(1, 11);
            ps.setString(2, "Chery");
            return ps;
        }, keyHolder);
        log.info("pk: {}", keyHolder.getKey().longValue());
    }
}
    

四、NamedParameterJdbcTemplate 实现

import java.util.Arrays;
import java.util.Date;

import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
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.JdbcOperations;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.transaction.annotation.Transactional;

import com.fly.template.bean.StudentVO;

import lombok.extern.slf4j.Slf4j;

@Slf4j
@Transactional
@SpringBootTest(webEnvironment = WebEnvironment.NONE)
public class NamedJdbcTemplateTest
{
    @Autowired
    NamedParameterJdbcTemplate namedJdbcTemplate;
    
    @BeforeEach
    public void init()
    {
        /****** JdbcOperations实际就是JdbcTemplate ******/
        JdbcOperations jdbcOperations = namedJdbcTemplate.getJdbcOperations();
        
        jdbcOperations.execute("drop table if exists student");
        jdbcOperations.execute("create table student(id int not null AUTO_INCREMENT, stu_name varchar(50), create_date datetime, primary key(id))");
        
        // batchUpdate
        jdbcOperations.batchUpdate("insert into student(stu_name, create_date) values(?, CURRENT_TIMESTAMP())", Arrays.asList(new Object[][] {{"Jack"}, {"Phil"}, {"Jenny"}}));
        
        jdbcOperations.execute("delete from student where id > 100");
        log.info("before::: init success!!");
        log.info(">>>>> before: {}", namedJdbcTemplate.getJdbcTemplate().queryForObject("select count(*) from student", Long.class));
    }
    
    @AfterEach
    public void after()
    {
        log.info(">>>>> after : {}", namedJdbcTemplate.getJdbcTemplate().queryForObject("select count(*) from student", Long.class));
    }
    

    @Test
    public void testQueryPk()
    {
        // 查询主键
        KeyHolder keyHolder = new GeneratedKeyHolder();
        namedJdbcTemplate.update("insert into student(stu_name, create_date) values(:stuName, :time)", new MapSqlParameterSource("stuName", "jackson").addValue("time", new Date()), keyHolder);
        log.info("pk: {}", keyHolder.getKey().longValue());
        
        namedJdbcTemplate.update("insert into student(stu_name, create_date) values(:stuName, :time)", new BeanPropertySqlParameterSource(new StudentVO().setStuName("jackson").setTime(new Date())), keyHolder);
        log.info("pk: {}", keyHolder.getKey().longValue());
    }
}

五、结论

从上面的实现过程可以看出，NamedParameterJdbcTemplate的实现方式更加简洁，言简意赅 ，建议采用！

六、源码放送

https://gitcode.com/00fly/springboot-demo/

---

有任何问题和建议，都可以向我提问讨论,大家一起进步，谢谢!

-over-