当前位置：首页 > news >正文

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

news 2024/12/25 18:36:06

文章目录

一、简单需求
二、准备工作
三、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
{@AutowiredJdbcTemplate jdbcTemplate;@BeforeEachpublic 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')");// batchUpdatejdbcTemplate.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));}@AfterEachpublic void after(){log.info(">>>>> after : {}", jdbcTemplate.queryForObject("select count(*) from student", Long.class));}/*** 查询主键*/@Testpublic void testQueryPk(){KeyHolder keyHolder = new GeneratedKeyHolder();jdbcTemplate.update(new PreparedStatementCreator(){@Overridepublic PreparedStatement createPreparedStatement(Connection conn)throws SQLException{// 预处理,注意参数PreparedStatement.RETURN_GENERATED_KEYSPreparedStatement 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());}/*** 查询非自增主键*/@Testpublic 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
{@AutowiredNamedParameterJdbcTemplate namedJdbcTemplate;@BeforeEachpublic 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))");// batchUpdatejdbcOperations.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));}@AfterEachpublic void after(){log.info(">>>>> after : {}", namedJdbcTemplate.getJdbcTemplate().queryForObject("select count(*) from student", Long.class));}@Testpublic 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());}
}