Mybatis 获取自增主键_喵先森爱吃鱼_mybatis主键自增

未知 02-07 4781

一、MySQL 获取自增主键

若数据库支持自动生成主键（比如 MySQL 和 SQL Server），则可以设置 useGeneratedKeys=“true”，表明使用自增主键获取主键值策略，然后再利用 keyProperty 属性指定对应的主键属性，也就是 Mybatis 获取到主键值后，将这个值封装给 JavaBean 的哪个属性。

<insert id="addEmp" databaseId="mysql" parameterType="employee" useGeneratedKeys="true" keyProperty="id"> insert into tbl_employee (id, last_name, email, gender) values (#{id}, #{lastName}, #{email}, #{gender}); </insert>

获取自增主键值：

@Test public void test04() throws IOException { SqlSessionFactory sqlSessionFactory = getSqlSessionFactory(); SqlSession sqlSession = sqlSessionFactory.openSession(); try{ EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class); Employee employee = new Employee(); employee.setLastName("wangwu"); employee.setEmail("wangwu@123.com"); employee.setGender("1"); boolean addEmp = mapper.addEmp(employee); System.out.println(addEmp); // 直接通过 getId() 方法来获取自增主键值 System.out.println(employee.getId()); sqlSession.commit(); } finally { sqlSession.close(); } } [main] [com.example.mapper.EmployeeMapper.addEmp]-[DEBUG] ==> Preparing: insert into tbl_employee (id, last_name, email, gender) values (?, ?, ?, ?); [main] [com.example.mapper.EmployeeMapper.addEmp]-[DEBUG] ==> Parameters: null, wangwu(String), wangwu@123.com(String), 1(String) [main] [com.example.mapper.EmployeeMapper.addEmp]-[DEBUG] <== Updates: 1 true 9

其中“9”为获取到的自增主键值。

二、Oracle 获取主键

Oracle 不支持自增，它使用序列来模拟自增，每次插入的数据的主键是从序列中拿到的值。

2.1 准备环境

创建表 tbl_employee：

create table tbl_employee ( id varchar2(20) PRIMARY key, last_name varchar2(255), gender varchar2(20), email varchar2(255) ) INSERT INTO tbl_employee (id, last_name, gender, email) VALUES (1, 'tom', '0', 'tom@123.com'); INSERT INTO tbl_employee (id, last_name, gender, email) VALUES (2, 'jerry', '0', 'jerry@123.com'); INSERT INTO tbl_employee (id, last_name, gender, email) VALUES (3, 'jack', '0', 'jack@123.com'); INSERT INTO tbl_employee (id, last_name, gender, email) VALUES (4, 'rose', '1', 'rose@123.com');

创建序列：

create sequence employee_seq --序列名为employee_seq start with 1 --以该值开始自增或自减 increment by 1 --每次自增1 nomaxvalue -- 最大值；设置NOMAXVALUE表示无最大值 minvalue 1--最小值；设置NOMINVALUE表示无最小值 NOCYCLE --CYCLE or NOCYCLE 设置到最大值后是否循环； cache 20 --指定可以缓存 20 个值在内存里；如果设置不缓存序列，则写NOCACHE order --order or noorder 设置是否按照请求的顺序产生序列

创建触发器：

create trigger employee_trigger --触发器名employee_trigger before insert on TBL_EMPLOYEE for each row when (new.id is null) --只有在id为空时，启动该触发器生成id号 begin select employee_seq.nextval into:new.id from dual; end; 2.2 使用属性order="BEFORE"获取主键值 <insert id="addEmp" databaseId="oracle">  <selectKey keyProperty="id" order="BEFORE" resultType="Integer"> select EMPLOYEE_SEQ.nextval from dual </selectKey>  insert into tbl_employee (id, last_name, email, gender) values (#{id}, #{lastName}, #{email}, #{gender}) </insert>

测试方法：

控制台结果：

[main] [com.example.mapper.EmployeeMapper.addEmp!selectKey]-[DEBUG] ==> Preparing: select EMPLOYEE_SEQ.nextval from dual [main] [com.example.mapper.EmployeeMapper.addEmp!selectKey]-[DEBUG] ==> Parameters: [main] [com.example.mapper.EmployeeMapper.addEmp!selectKey]-[DEBUG] <== Total: 1 [main] [com.example.mapper.EmployeeMapper.addEmp]-[DEBUG] ==> Preparing: insert into tbl_employee (id, last_name, email, gender) values (?, ?, ?, ?) [main] [com.example.mapper.EmployeeMapper.addEmp]-[DEBUG] ==> Parameters: 6(Integer), wangwu(String), wangwu@123.com(String), 1(String) [main] [com.example.mapper.EmployeeMapper.addEmp]-[DEBUG] <== Updates: 1 true 5

数据库：

注意：由于我在建表时向表中插入了四条记录，为了避免主键值冲突，需要执行select EMPLOYEE_SEQ.nextval from dual;语句，将序列的 nextval 增加至“4”。

2.3 使用属性order="AFTER"获取主键值

EmployeeMapper.xml

<insert id="addEmp" databaseId="oracle">  <selectKey keyProperty="id" order="AFTER" resultType="Integer"> select EMPLOYEE_SEQ.currval from dual </selectKey>  insert into tbl_employee (id, last_name, email, gender) values (employee_seq.nextval, #{lastName}, #{email}, #{gender}) </insert>

控制台结果：

main] [com.example.mapper.EmployeeMapper.addEmp]-[DEBUG] ==> Preparing: insert into tbl_employee (id, last_name, email, gender) values (employee_seq.nextval, ?, ?, ?) [main] [com.example.mapper.EmployeeMapper.addEmp]-[DEBUG] ==> Parameters: Mark(String), Mark@123.com(String), 1(String) [main] [com.example.mapper.EmployeeMapper.addEmp]-[DEBUG] <== Updates: 1 [main] [com.example.mapper.EmployeeMapper.addEmp!selectKey]-[DEBUG] ==> Preparing: select EMPLOYEE_SEQ.currval from dual [main] [com.example.mapper.EmployeeMapper.addEmp!selectKey]-[DEBUG] ==> Parameters: [main] [com.example.mapper.EmployeeMapper.addEmp!selectKey]-[DEBUG] <== Total: 1 true 7

数据库：

2.4 总结

BEFORE 运行顺序：先执行 selectKey 查询 id 的 SQL，查出 id 值封装给 JavaBean 的 id 属性。再运行插入的 SQL，就可以取出 id 属性的值。 AFTER 运行顺序：先运行插入的 SQL（从序列中取出新值作为 id），再运行 selectKey 查询 id 的 SQL，查出 id 值封装给 JavaBean 的 id 属性。不推荐使用 after 方式。