MyBatis新特性

1、Mybatis插入记录后返回主键ID

<insert id="INSERT"
        parameterType="com.jd.jr.gyl.securitization.domain.po.abs.Batch"
        useGeneratedKeys="true" keyProperty="id">
    INSERT into  Batch(
    is_effect,
    reserve_field_int,
    reserve_field_varchar
    ) values(
    now(),
    1,
    #{reserveFieldInt},
    #{reserveFieldVarchar}
    )
    <selectKey resultType="java.lang.Long" order="AFTER"
               keyProperty="id">
        SELECT LAST_INSERT_ID()
    </selectKey>
</insert>

解析：

设置是否自动增长：seGeneratedKeys="true" ；
设置自动增加的主键ID：keyProperty="id"；
返回上次插入的主键ID
    <selectKey resultType="java.lang.Long" order="AFTER"
               keyProperty="id">
        SELECT LAST_INSERT_ID()
    </selectKey>
2、批量插入数据
<insert id="INSERTBATCH" parameterType="java.util.List" useGeneratedKeys="true">
    INSERT into Batch(
    reserve_field_varchar
    ) values
    <foreach collection="list" index="index" item="item" separator=",">
      (
        #{item.reserveFieldVarchar}
        )
    </foreach>
</insert>
3、单条更新
<!--更新批次-->
<update id="UPDATE" parameterType="com.jd.jr.gyl.securitization.domain.po.abs.Batch">
    UPDATE  Batch
    <set>
        <if test="remark!=null ">
            remark=#{remark},
        </if>
        modify_time=NOW()
    </set>
    WHERE id=#{id}
</update>
4、批量更新
<update id="UPDATEBATCH" parameterType="java.util.List">
    <foreach collection="list" index="index" close="" open="" item="item" separator=";">
        UPDATE  Batch
        <set>
            <if test="item.remark!=null and item.remark!=''">
                remark=#{item.remark}
            </if>
        </set>
        <where>
            id=#{item.id}
        </where>
    </foreach>
</update>
5、复合查询
<!--查询批次总条数-->
<select id="queryTotalCountsByMutliParams"
        resultType="java.lang.Integer"
        parameterType="java.util.Map">
    SELECT
    count(1)
    FROM Org_Detail org join Special_Plan s on org.id=s.plan_manager_id
    join Asset_Extract_Batch a on s.id=a.plan_id
    <where>
        <if test="extractStatus!=null and extractStatus!=''">
            <choose>
                <when test="extractStatus == 1">
                    AND a.extract_status = 1
                    <![CDATA[ AND (a.`plan_date_start` <= NOW()]]>
                </when>
                <when test="extractStatus == 4">
                    AND a.extract_status = 1
                    <![CDATA[ AND (a.`plan_date_start` > NOW()]]>
                </when>
                <when test="extractStatus != 4 and extractStatus != 1">
                    AND a.extract_status = #{extractStatus}
                </when>
            </choose>

        </if>
        <if test="extractTimeStart!=null and extractTimeStart!=''">
            AND a.extract_time &gt;=#{extractTimeStart}
        </if>
        <if test="extractTimeEnd!=null and extractTimeEnd!=''">
            AND a.extract_time &lt;=#{extractTimeEnd}
        </if>
        and a.is_effect=1
    </where>
</select>