一、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">
<!--
keyProperty:查出的主键值封装给 JavaBean 的哪个属性
order="BEFORE":当前 SQL 在插入 SQL 之前运行
AFTER:当前 SQL 在插入 SQL 之后运行
resultType:查出的数据的返回值类型
-->
<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>
测试方法:
@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);
System.out.println(employee.getId());
sqlSession.commit();
} finally {
sqlSession.close();
}
}
控制台结果:
[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">
<!--
keyProperty:查出的主键值封装给 JavaBean 的哪个属性
order="BEFORE":当前 SQL 在插入 SQL 之前运行
AFTER:当前 SQL 在插入 SQL 之后运行
resultType:查出的数据的返回值类型
-->
<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 方式。