oracle表自增id序列及触发器

对USER_T表的user_id字段增加自增序列

创建自增序列:

CREATE sequence USER_ID_SEQUENCE
minvalue 1 --最小值
maxvalue 9999999999 --不设置最大值
START WITH 4100000001 --从4100000001开始计数
INCREMENT BY 1 --每次加1
NOCYCLE --一直累加，不循环
NOCACHE; --不建缓冲区

 

创建触发器:

CREATE OR REPLACE TRIGGER USER_ID_TRIGGER BEFORE INSERT ON USER_T FOR EACH ROW WHEN(NEW.USER_ID IS NULL)
BEGIN 
SELECT USER_ID_SEQUENCE.NEXTVAL INTO:NEW.USER_ID FROM dual;
END;

 

mybatis插入USER_T时，自动返回user_id的处理：

<insert id="insertUser" useGeneratedKeys="false" parameterType="cn.com.entity.User">
    <selectKey resultType="Long" order="BEFORE" keyProperty="userId">
        SELECT USER_ID_SEQUENCE.NEXTVAL as userId from DUAL
    </selectKey>
    insert into USER_T
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="userId != null">
            user_id,
        </if>
        <if test="userName != null">
            user_name,
        </if>
            update_time
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
        <if test="userId != null">
            #{userId,jdbcType=BIGINT},
        </if>
        <if test="userName != null">
            #{userName,jdbcType=VARCHAR},
        </if>
            sysdate
    </trim>
</insert>