mybatis插入返回自增主键

昨天下班到家以后收到一朋友问我一个问题：mysql中设置的是自增主键，然后在Mybatis执行insert语句后如何返回自增主键值，我说明天我到公司后写一篇博客给你

Mybatis官网是这样说的：

First, if your database supports auto-generated key fields (e.g. MySQL and SQL Server), then you can simply set useGeneratedKeys="true" and set the keyProperty to the target property and you're done. For example, if the Authortable above had used an auto-generated column type for the id, the statement would be modified as follows:

就是说需要useGenerateKeys=“true”和keyProperty=“XXId”，但是需要下面这些前置条件

1、升级Mybatis版本到3.3.1。

2、在Dao中不能使用@param注解。

一、单条insert返回自增主键，方案一

好了废话不多说，直接上代码

user.xml

<insert id="insert" parameterType="com.test.User">
    INSERT INTO User
     <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="id != null" >taskScheduleId,</if>
        <if test="userName != null" >clearSettleFileId,</if>
        <if test="age!= null" >inputDate,</if>
        <if test="address != null" >fileDate,</if>
    </trim>
    <trim prefix="VALUES (" suffix=")" suffixOverrides="," >
        <if test="id != null" >#{id,jdbcType=INTEGER},</if>
        <if test="userName != null" >#{userName,jdbcType=VARCHAR},</if>
        <if test="age != null" >#{age,jdbcType=VARCHAR},</if>
        <if test="address != null" >#{address,jdbcType=VARCHAR},</if>       
    </trim>
    <selectKey resultType="java.lang.Integer" keyProperty="id">
        SELECT LAST_INSERT_ID() AS id    
    </selectKey>
</insert>

UserServiceImpl.java

User vo = new User();
vo.setUserName("zhangsan");
vo.setAge("20");
vo.setAddress("a");
dao.insert("insert",vo);

System.out.println(vo.getId());

二、单条insert返回自增主键，方案二

就是xml但变动，java类不需要变动

<insert id="insert" parameterType="com.test.User" keyProperty="taskScheduleId" useGeneratedKeys="true">
    INSERT INTO User
     <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="id != null" >taskScheduleId,</if>
        <if test="userName != null" >clearSettleFileId,</if>
        <if test="age!= null" >inputDate,</if>
        <if test="address != null" >fileDate,</if>
    </trim>
    <trim prefix="VALUES (" suffix=")" suffixOverrides="," >
        <if test="id != null" >#{id,jdbcType=INTEGER},</if>
        <if test="userName != null" >#{userName,jdbcType=VARCHAR},</if>
        <if test="age != null" >#{age,jdbcType=VARCHAR},</if>
        <if test="address != null" >#{address,jdbcType=VARCHAR},</if>       
    </trim>
</insert>

二、批量插入insert返回自增主键

user.xml

<insert id="insertBatch" parameterType="list" keyProperty="id" useGeneratedKeys="true">
    INSERT INTO User(
        userName,
        age,
        address)VALUES
        <foreach collection="list" item="item" separator="," index="index">
            <trim prefix="(" suffix=")" suffixOverrides=",">
            #{item.userName,jdbcType=VARCHAR},
            #{item.age,jdbcType=VARCHAR},
            #{item.address,jdbcType=VARCHAR}
            </trim>
        </foreach>
</insert>