mybatis插入Oracle主键(自增键)

矫情吗；* 2022-02-25 17:48 412阅读 0赞

在mybatis插件oracle数据库，如果是主键自增型的，就要考虑是已经有触发器处理，还是只有sequence，这样才能在保存后的类里面主键才有值。

1、只有sequence时，demo如下

<insert id="save" parameterType="com.bootdo.system.domain.UserDO">
        	<selectKey resultType="long" keyProperty="userId" order="BEFORE"> 
    	        SELECT S_SYS_USER.NEXTVAL FROM DUAL 
    	    </selectKey> 
    		insert into sys_user
    		(
    			username, 
    			name, 
    			password, 
    		)
    		values
    		(
    			#{username,jdbcType=VARCHAR}, 
    			#{name,jdbcType=VARCHAR}, 
    			#{password,jdbcType=VARCHAR}
    		)
    	</insert>

2、通过Oracle触发器

declare
    V_NUM number;
    BEGIN
      V_NUM := 0;
      ----判断序列是否存在（区分大小写）
      select count(0) into V_NUM from user_sequences where sequence_name=upper('S_SYS_USER'); 
      ----如果存在立即删除
      if V_NUM > 0 then
        execute immediate 'DROP SEQUENCE S_SYS_USER';
      end if;
    END;
    /
    
    --创建序号的规则
    create sequence S_SYS_USER
      INCREMENT BY 1   -- 每次加几个  
      START WITH 1     -- 从1开始计数  
      NOMAXVALUE       -- 不设置最大值  
      NOCYCLE ;         -- 一直累加，不循环
    /
    
    --创建触发器  DROP TRIGGER TIB_SYS_USER  --删除触发器
    create or replace trigger TIB_SYS_USER before insert on SYS_USER for each row
    declare
        integrity_error  exception;
        errno            integer;
        errmsg           char(200);
        dummy            integer;
        found            boolean;
    begin
        select S_SYS_USER.NEXTVAL INTO :new.user_id from dual;
    exception
        when integrity_error then
           raise_application_error(errno, errmsg);
    end;

<insert id="save" parameterType="com.bootdo.system.domain.UserDO">
        	<selectKey resultType="long" keyProperty="userId" order="AFTER"> 
    	        SELECT S_SYS_USER.CURRVAL FROM DUAL 
    	    </selectKey> 
    		insert into sys_user
    		(
    			username, 
    			name, 
    			password
    		)
    		values
    		(
    			#{username,jdbcType=VARCHAR}, 
    			#{name,jdbcType=VARCHAR}, 
    			#{password,jdbcType=VARCHAR}
    		)
    	</insert>

@Transactional
        @Override
        public int save(UserDO user) {
            int count = userMapper.save(user);
            //这样userid才能有值
            Long userId = user.getUserId();
            return count;
        }