1.批量插入

    <insert id="batchAddTmpD" parameterType="list">
        INSERT INTO <include refid="tb"/>
        ( <include refid="cols_exclude_id"/>)
        VALUES
        <foreach collection="list" separator="," item="i">
            (#{i.type},#{i.modifyTime},#{i.parentZipCode},#{i.zipCode},#{i.date},#{i.year},#{i.month},#{i.day},
            #{i.weekday},#{i.statusFlag},#{i.comments},#{i.remark},#{i.backlogId},#{i.formalId},#{i.isD},#{i.isChanged},#{i.originType})
        </foreach>
    </insert>

forearch的参数可以是array，list，map这三种

2.批量删除

    <delete id="batchDelTmpD" parameterType="list">
        delete from <include refid="tb" />
        WHERE ID_DTMP IN
        <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </delete>

3.批量更新

通过一条SQL语句只能将一批ID取到的行设置成相同的值,idList是数据库表项的ID链表，剩余字段为需要修改成的字段
调用代码：

Map<String,Object> map = new HashMap<>();
map.put("list", idList);
if(parms.getParentZipCode() != null){
    map.put("parentZipCode",parms.getParentZipCode());
}
if(parms.getStatusFlag() != null){
    map.put("statusFlag",parms.getStatusFlag());
}
if(parms.getComments() != null){
    map.put("comments",parms.getComments());
}
if(parms.getRemark() != null){
    map.put("remark",parms.getRemark());
}
if(parms.getFormalId() != null){
    map.put("formalId",parms.getFormalId());
}
if(parms.getBacklogId() != null){
    map.put("backlogId",parms.getBacklogId());
}

XML配置

    <update id="batchUpdateTmpD" parameterType="map">
        UPDATE <include refid="tb" />
        <set>
            <if test="parentZipCode != null">PARENT_ZIPCODE = #{parentZipCode},</if>
            <if test="statusFlag != null">STATUS_FLAG = #{statusFlag},</if>
            <if test="comments != null">COMMENTS = #{comments},</if>
            <if test="remark != null">REMARK = #{remark},</if>
            <if test="formalId != null">FORMALID = #{formalId},</if>
            <if test="backlogId != null">BACKLOGID = #{backlogId},</if>
            TS_MODIFYTIME = now()
        </set>
        WHERE ID_DTMP IN
        <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </update>

4.获取统计个数

count(*),count(1)和count(主键)都没错,count(*)扫描全表,count(1)和count(主键)只扫描index

    <select id="getTmpDCount" parameterType="map" resultType="Integer">
        SELECT count(*) FROM <include refid="tb"/> WHERE ZIPCODE = #{zipCode}
        <if test="year != null">and YEAR = #{year}</if>
        <if test="month != null">and MONTH = #{month}</if>
        <if test="day != null">and DAY = #{day}</if>
        <if test="isChanged != null">and IS_CHANGED = #{isChanged}</if>
        <if test="type != null">and TYPE = #{type}</if>
        <if test="statusFlag != null">and STATUS_FLAG = #{statusFlag}</if>
        <if test="backlogId != null">and BACKLOGID = #{backlogId}</if>
    </select>

5.根据列表批量获取

    <select id="getTmpDByIdSet" parameterType="list" resultMap="DTmpEntity">
        SELECT * FROM <include refid="tb"/> WHERE ID_DTMP IN
        <foreach item="id" collection="list" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>

6.批量获取一列并排除重复

GROUP BY函数应该的使用应该是SELECT 列表中指定的每一列也必须出现在 GROUP BY 子句中，除非这列是用于聚合函数

    <select id="getBacklogIdByIdSet" parameterType="list" resultType="String">
        SELECT BACKLOGID FROM <include refid="tb"/> WHERE ID_DTMP IN
        <foreach item="id" collection="list" open="(" separator="," close=")">
            #{id}
        </foreach>
        GROUP BY BACKLOGID
    </select>

7.获取该字段为NULL的行

IS NULL不能=null

    <select id="getUntrackIdSet" parameterType="map" resultType="Integer">
        SELECT ID_DTMP FROM <include refid="tb"/> WHERE ZIPCODE = #{zipCode}
        <if test="year != null">and YEAR = #{year}</if>
        <if test="month != null">and MONTH = #{month}</if>
        <if test="day != null">and DAY = #{day}</if>
        <if test="statusFlag != null">and STATUS_FLAG = #{statusFlag}</if>
        and BACKLOGID IS NULL
    </select>

8.分页

select * from table limit m,n
其中m是指记录开始的index，从0开始，表示第一条记录n是指从第m+1条开始，取n条。

<select id="getTmpDByDistrict" parameterType="map" resultMap="DTmpEntity">
    select  *  from <include refid="tb"/>
    <where>
        ZIPCODE = #{zipCode}
        <if test="year != null">and YEAR = #{year}</if>
        <if test="month != null">and MONTH = #{month}</if>
        <if test="day != null">and DAY = #{day}</if>
        <if test="isChanged != null">and IS_CHANGED = #{isChanged}</if>
        <if test="type != null">and TYPE = #{type}</if>
        <if test="statusFlag != null">and STATUS_FLAG = #{statusFlag}</if>
    </where>
    ORDER BY `DATE`
    <if test="(null != order)">
        <if test="(order == "asc" || order == "ASC" || order == "desc" || order == "DESC")">${order} </if>
        <if test="(order != "asc" && order != "ASC" && order != "desc" && order != "DESC")">DESC </if>
    </if>
    <if test="(null == order)">DESC </if>
    LIMIT
        <if test="(null != start) && (start > 0)">#{start},</if>
        <if test="(null == start) || (start <= 0)">0,</if>
        <if test="(null != count) && (count > 0)">#{count}</if>
        <if test="(null == count) || (count < 0)">10</if>
</select>