mybaties
<insert id="insertAndReturnID" parameterType="Privilege">
<!-- parameterType="Privilege": 是传入参数的对象,后面返回的privilegeID就是它的属性 -->
<!-- 插入数据,并返回privilegeID,该privilegeID的值给对象的属性privilegeID,并返回Privilege对象-->
<selectKey resultType="INTEGER" order="BEFORE" keyProperty="privilegeID">
SELECT Privilege_PrivilegeID_seq.Nextval as ID from DUAL
</selectKey>
insert into Privilege
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="privilegeID != null">
PrivilegeID,
</if>
<if test="type != null">
Type,
</if>
</trim>
VALUES
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="privilegeID != null">
#{privilegeID},
</if>
<if test="type != null">
#{type},
</if>
</trim>
</insert>
接口
/**
* 插入数据,并返回自动增长的id
*/
public int insertAndReturnID(Privilege privilege);
@RequestMapping(value = "/basic/test.do")
public void test(){
Privilege privilege= new Privilege();
privilege.setType("test");
int insertAndReturnID = privilegeService.insertAndReturnID(privilege);
System.out.println("test: "+privilege.getPrivilegeID());
}
自增长id的实现
-----------------------------Privilege
--创建自增id,名称为:表名_字段名_seq
create sequence Privilege_PrivilegeID_seq
-- 最小值
minvalue 10
-- 不设置最大值
nomaxvalue
-- 每次加1
increment by 1
-- 从1开始计数
start with 10
-- 一直累加,不循环
nocycle
-- 不建缓冲区
nocache;
--为insert操作创建触发器,SysRole_ins_trg
create or replace trigger Privilege_ins_trg
before insert on Privilege
for each row
begin
select Privilege_PrivilegeID_seq.nextval into :new.PrivilegeID from dual;
end;
/
日志和打印信息
数据库的PrivilegeID
为什么不是47,而是48?
原因是:插入代码的前面:SELECT Privilege_PrivilegeID_seq.Nextval as ID from DUAL
调用了一次序列;
在数据库中,数据插入前有一次调用序列:
select Privilege_PrivilegeID_seq.nextval into :new.PrivilegeID from dual;
所以数据库中PrivilegeID是48
修改如下就可以解决
将Privilege_PrivilegeID_seq.nextval改为如下
Privilege_PrivilegeID_seq.nextval-1
--修改后的代码如下
--为insert操作创建触发器,SysRole_ins_trg
create or replace trigger Privilege_ins_trg
before insert on Privilege
for each row
begin
select Privilege_PrivilegeID_seq.nextval-1 into :new.PrivilegeID from dual;
end;
/