一、需求
向数据库插入一条记录后,希望能立即拿到这条记录在数据库中的主键值。
二、代码演示
1、方式1 - useGeneratedKeys
-
数据库环境
CREATE DATABASE `mybatis_db`; USE `mybatis_db`; CREATE TABLE `user` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `username` VARCHAR(32) NOT NULL COMMENT '用户名称', `birthday` DATETIME DEFAULT NULL COMMENT '生日', `sex` CHAR(1) DEFAULT NULL COMMENT '性别', `address` VARCHAR(256) DEFAULT NULL COMMENT '地址', PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; -- insert.... INSERT INTO USER(id,username,birthday,sex,address) VALUES (1,'tom','2024-01-01 00:00:00','男','北京'),(2,'jerry','2023-01-01 00:00:00','男','上海');
-
pom依赖
<?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/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>org.example</groupId> <artifactId>mybatis-dao</artifactId> <version>1.0-SNAPSHOT</version> <!--指定编码及版本--> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <maven.compiler.encoding>UTF-8</maven.compiler.encoding> <java.version>1.11</java.version> <maven.compiler.source>1.11</maven.compiler.source> <maven.compiler.target>1.11</maven.compiler.target> </properties> <!--引入相关依赖--> <dependencies> <!--引入mybatis依赖--> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.3</version> </dependency> <!--引入mysql驱动--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.47</version> </dependency> <!--引入junit--> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> </dependency> </dependencies> </project>
-
SqlMapConfig.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <properties resource="jdbc.properties"></properties> <typeAliases> <!--方式一:给单个实体起别名--> <!-- <typeAlias type="domain.User" alias="user"></typeAlias>--> <!--方式二:批量起别名 别名就是类名,且不区分大小写--> <package name="domain"/> </typeAliases> <!--环境配置--> <environments default="mysql"> <!--使用mysql环境--> <environment id="mysql"> <!--使用jdbc事务管理亲--> <transactionManager type="JDBC"></transactionManager> <!-- 使用连接池--> <dataSource type="POOLED"> <property name="driver" value="${jdbc.driver}"/> <property name="url" value="${jdbc.url}"/> <property name="username" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}"/> </dataSource> </environment> </environments> <!--加载映射配置--> <mappers> <mapper resource="mapper/UserMapper.xml"></mapper> </mappers> </configuration>
-
User实体类
package domain; import java.util.Date; public class User { private Integer id; private String usernameabc; private Date birthday; private String sex; private String address; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public String getUsernameabc() { return usernameabc; } public void setUsernameabc(String usernameabc) { this.usernameabc = usernameabc; } @Override public String toString() { return "User{" + "id=" + id + ", usernameabc='" + usernameabc + '\'' + ", birthday=" + birthday + ", sex='" + sex + '\'' + ", address='" + address + '\'' + '}'; } }
-
UserMapper.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="mapper.UserMapper"> <!--id : 标签的唯一标识 type: 封装后实体类型--> <resultMap id="userResultMap" type="domain.User"> <!--手动配置映射关系--> <!--id: 用来配置主键--> <id property="id" column="id"></id> <!-- result: 表中普通字段的封装--> <result property="usernameabc" column="username"></result> <result property="birthday" column="birthday"></result> <result property="sex" column="sex"></result> <result property="address" column="address"></result> </resultMap> <!-- 返回主键:方式1 注意:只适用于主键自增的数据库,mysql 和 sqlserver 支持,oracle 不支持 --> <insert id="save1" parameterType="user" useGeneratedKeys="true" keyProperty="id"> insert into user(username, birthday, sex, address) values (#{usernameabc}, #{birthday}, #{sex}, #{address}) </insert> </mapper>
-
UserMapper.java
package mapper; import domain.User; import java.util.List; public interface UserMapper { public void save1(User user); }
-
测试类
package test; import domain.User; import mapper.UserMapper; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.Test; import java.io.InputStream; import java.util.List; public class MybatisTest { //返回主键:方式1 @Test public void testSave1() throws Exception { InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = new User(); user.setUsernameabc("张三"); user.setBirthday(new Date()); user.setSex("男"); user.setAddress("河南"); mapper.save1(user); sqlSession.commit(); System.out.println("获取返回user的id:" + user.getId()); sqlSession.close(); } }
-
测试结果
2、方式2-selectKey
-
UserMapper.java
package mapper; import domain.User; import java.util.List; public interface UserMapper { public void save1(User user); public void save2(User user); }
-
UserMapper.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="mapper.UserMapper"> <!--id : 标签的唯一标识 type: 封装后实体类型--> <resultMap id="userResultMap" type="domain.User"> <!--手动配置映射关系--> <!--id: 用来配置主键--> <id property="id" column="id"></id> <!-- result: 表中普通字段的封装--> <result property="usernameabc" column="username"></result> <result property="birthday" column="birthday"></result> <result property="sex" column="sex"></result> <result property="address" column="address"></result> </resultMap> <!-- 返回主键:方式1 注意:只适用于主键自增的数据库,mysql 和 sqlserver 支持,oracle 不支持 --> <insert id="save1" parameterType="user" useGeneratedKeys="true" keyProperty="id"> insert into user(username, birthday, sex, address) values (#{usernameabc}, #{birthday}, #{sex}, #{address}) </insert> <!--返回主键:方式2--> <!-- selectKey 适用范围广,支持所有类型数据库 keyColumn="id" 指定主键列名 keyProperty="id" 指定主键封装到实体的 id 属性中 resultType="int" 指定主键类型 order="AFTER" 设置在 sql 语句执行前(后),执行此语句 --> <insert id="save2" parameterType="user"> <selectKey keyColumn="id" keyProperty="id" resultType="int" order="AFTER"> select LAST_INSERT_ID() </selectKey> insert into user(username, birthday, sex, address) values (#{usernameabc}, #{birthday}, #{sex}, #{address}) </insert> </mapper>
-
测试类
package test; import domain.User; import mapper.UserMapper; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.Test; import java.io.InputStream; import java.util.List; public class MybatisTest { //返回主键:方式1 @Test public void testSave1() throws Exception { InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = new User(); user.setUsernameabc("张三"); user.setBirthday(new Date()); user.setSex("男"); user.setAddress("河南"); mapper.save1(user); sqlSession.commit(); System.out.println("获取返回user的id:" + user.getId()); sqlSession.close(); } //返回主键:方式2 @Test public void testSave2() throws Exception { InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = new User(); user.setUsernameabc("李四"); user.setBirthday(new Date()); user.setSex("男"); user.setAddress("河北"); mapper.save2(user); sqlSession.commit(); System.out.println("获取返回user的id:" + user.getId()); sqlSession.close(); } }
-
测试结果