Mybatis (批量操作)

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这三种

属性	描述
item	循环体中的具体对象。支持属性的点路径访问，如item.age,item.info.details。具体说明：在list和数组中是其中的对象，在map中是value。该参数为必选。
collection	要做foreach的对象，作为入参时，List<?>对象默认用list代替作为键，数组对象有array代替作为键，Map对象没有默认的键。当然在作为入参时可以使用@Param("keyName")来设置键，设置keyName后，list,array将会失效。 除了入参这种情况外，还有一种作为参数对象的某个字段的时候。举个例子：如果User有属性List ids。入参是User对象，那么这个collection = "ids"如果User有属性Ids ids;其中Ids是个对象，Ids有个属性List id;入参是User对象，那么collection = "ids.id"上面只是举例，具体collection等于什么，就看你想对那个元素做循环。该参数为必选。
separator	元素之间的分隔符，例如在in()的时候，separator=","会自动在元素中间用“,“隔开，避免手动输入逗号导致sql错误，如in(1,2,)这样。该参数可选。
open	foreach代码的开始符号，一般是(和close=")"合用。常用在in(),values()时。该参数可选。
close	foreach代码的关闭符号，一般是)和open="("合用。常用在in(),values()时。该参数可选。
index	在list和数组中,index是元素的序号，在map中，index是元素的key，该参数可选。

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>

No.	文字表記	0進表記	16進表記	文字	Comment
001	"	"	"	"""	quotation mark = APL quote
002	&	&	&	"&"	ampersand
003	<	<	<	"<"	less-than sign
004	>	>	>	">"	greater-than sign
005				" "	no-break space = non-breaking space