mybatis获取刚插入数据的ID
1.很多时候,sql的语句的住建都是逐渐递增的,mybatis给我们提供了获取ID的方法
2.代码,
方法一
(只对MySQL数据库可用)
1.useGeneratedKeys="true" keyProperty="id"
<insert id="insert" parameterType="com.yd.pojo.DemoUser" useGeneratedKeys="true" keyProperty="id" >
insert into demo_user (id, name, age,
address, love)
values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER},
#{address,jdbcType=VARCHAR}, #{love,jdbcType=VARCHAR})
</insert>
方法二
注意:SelectKey的属性order属性。像Mysql一类支持自动增长类型的数据库中,order需要设置为after;像Oracle/PG这样取序列的情况,需要设置为before,否则会报错。
MySQL的
select LAST_INSERT_ID()
<insert id="insertSelective" parameterType="com.yd.pojo.DemoUser" >
<selectKey keyProperty="id" resultType="java.lang.Integer" order="AFTER">
select LAST_INSERT_ID()
</selectKey>
insert into demo_user
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="id != null" >
id,
</if>
<if test="name != null" >
name,
</if>
<if test="age != null" >
age,
</if>
<if test="address != null" >
address,
</if>
<if test="love != null" >
love,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="id != null" >
#{id,jdbcType=INTEGER},
</if>
<if test="name != null" >
#{name,jdbcType=VARCHAR},
</if>
<if test="age != null" >
#{age,jdbcType=INTEGER},
</if>
<if test="address != null" >
#{address,jdbcType=VARCHAR},
</if>
<if test="love != null" >
#{love,jdbcType=VARCHAR},
</if>
</trim>
</insert>
oracle的
要先获取序列
<insert id="insert2" parameterType="com.ls.oracle.po.Person">
<selectKey keyProperty="pid" resultType="java.lang.Integer" order="BEFORE">
SELECT seqpersonid.nextval from dual
</selectKey>
insert into person (pid, name, age,
birthday, create_time)
values (#{pid}, #{name}, #{age},
#{birthday}, #{createTime})
</insert>
或者
注意:SelectKey的属性order为AFTER,但查询 改为: SELECT seqpersonid.currval FROM dual
和seqpersonid.nextval
<insert id="insert2" parameterType="com.ls.oracle.po.Person">
<selectKey keyProperty="pid" resultType="java.lang.Integer" order="AFTER">
SELECT seqpersonid.currval FROM dual
</selectKey>
insert into person (pid, name, age,
birthday, create_time)
values (seqpersonid.nextval, #{name}, #{age},
#{birthday}, #{createTime})
</insert>
3.运行结果