MyBatis and Oracle,MySql,SqlServer insert data return primary key method

MyBatis Oracle MySql SqlServer insert return primary key

MyBatis returns an auto increasing primary key when insert ing data. It can be realized through XML configuration, while different configurations of database are different. Let's take a look.

Oracle

Compared with MySQL and SQL server, it is a little more complicated for Oracle to insert the primary key and return auto increment. It requires a sequence and a Trigger. Next, see the code. Sequence: sequence number. Create an auto increasing sequence number.
Trigger: when it is used as an insert operation, it triggers to insert a Sequence obtained from the Sequence and inserted into the primary key.

• Sequence:

create sequence Seq_Test
minvalue 1
maxvalue 100000000000000
start with 1
increment by 1
nocache;Cache the serial number to prevent discontinuity caused by system downtime or other conditions. It can also be set nocache

• Trigger:

create or replace trigger tri_test
  before insert on TableName   --Table name
  for each row
declare
  nextid number;
begin
  IF :new.Id IS NULL or :new.Id=0 THEN --Id Is a column name,Primary key
    select Seq_Test.nextval --Seq_Test Serial number created above
    into nextid
    from sys.dual;
    :new.Id:=nextid;
  end if;
end tri_test;

• XML configuration

<insert id="add" parameterType="cn.crm.PO">
<selectKey resultType="java.lang.Integer"  order="BEFORE(After)" keyProperty="Id">
SELECT Seq_Test.NEXTVAL FROM DUAL
</selectKey>
insert into system_user (name, parent_id,
phone_num, password, description
)
values (#{name,jdbcType=VARCHAR},
#{parent_id,jdbcType=SMALLINT},
#{phone_num,jdbcType=VARCHAR},
#{password,jdbcType=VARCHAR},
#{description,jdbcType=VARCHAR}
)
</insert>

MySql

Mysql comes with an auto add primary key, so you only need XML configuration. There are two configuration methods.
The first:

<insert id="add" parameterType="cn.crm.PO" useGeneratedKeys="true" keyProperty="id">

insert into system_user (name, parent_id,
phone_num, password, description
)
values (#{name,jdbcType=VARCHAR},
#{parent_id,jdbcType=SMALLINT},
#{phone_num,jdbcType=VARCHAR},
#{password,jdbcType=VARCHAR},
#{description,jdbcType=VARCHAR}
)

</insert>
//After insertion, it is automatically encapsulated into PO, and you can get it directly from PO.getId()

Second:

<insert id="add" parameterType="cn.crm.PO">
    <selectKey resultType="java.lang.Short" order="AFTER" keyProperty="id">
        SELECT LAST_INSERT_ID() AS id
    </selectKey>
insert into system_user (name, parent_id,
phone_num, password, description
)
values (#{name,jdbcType=VARCHAR},
#{parent_id,jdbcType=SMALLINT},
#{phone_num,jdbcType=VARCHAR},
#{password,jdbcType=VARCHAR},
#{description,jdbcType=VARCHAR}
)

</insert>

SqlServer

It seems that Mybatis does not support SqlServer very much. The configuration mode is as follows:

<insert id="add" parameterType="cn.crm.PO" useGeneratedKeys="true" keyProperty="id">

insert into system_user (name, parent_id,
phone_num, password, description
)
values (#{name,jdbcType=VARCHAR},
#{parent_id,jdbcType=SMALLINT},
#{phone_num,jdbcType=VARCHAR},
#{password,jdbcType=VARCHAR},
#{description,jdbcType=VARCHAR}
)

</insert>
//It is configured in the same way as MySql.