Mybatis--动态SQL

foreach标签
批量添加数据场景

<select id="insertBatchAddTaskRun">
    insert into tb_ucpaas_task_run_${taskId} ( task_id, nbr, `status`, create_time )
    values
    <foreach collection="lstNbr" item="item" index="index" separator="," close=";">
      (
      #{taskId}, #{item}, #{status}, now()
      )
    </foreach>
  </select>

Dao：

void insertBatchAddTaskRun(@Param("taskId") Integer taskId, @Param("lstNbr") List<String> lstNbr, @Param("status") String status);

SQL原型：

INSERT INTO user(name)
VALUES("张三"),
("张三");

insert into tb_task_nbr_var
        <trim prefix="(" suffix=")" suffixOverrides=",">
            appId,
            taskId,
            nbr,
            var1,
            var2,
            var3,
            var4,
            var5,
            var6,
            var7,
            var8,
            var9,
            var10,
            create_time,
            sid,
        </trim>
        <foreach collection="lstTbTaskNbrVars" item="item" open="VALUES" separator="," close=";">
            <trim prefix="(" suffix=")" suffixOverrides=",">
                #{item.appid,jdbcType=VARCHAR},
                #{item.taskid,jdbcType=INTEGER},
                #{item.nbr,jdbcType=VARCHAR},
                #{item.var1,jdbcType=CHAR},
                #{item.var2,jdbcType=CHAR},
                #{item.var3,jdbcType=CHAR},
                #{item.var4,jdbcType=CHAR},
                #{item.var5,jdbcType=CHAR},
                #{item.var6,jdbcType=CHAR},
                #{item.var7,jdbcType=CHAR},
                #{item.var8,jdbcType=CHAR},
                #{item.var9,jdbcType=CHAR},
                #{item.var10,jdbcType=CHAR},
                #{item.createTime,jdbcType=DATE},
                #{item.sid,jdbcType=VARCHAR},
            </trim>
        </foreach>

Dao：

/**
     * 批量添加
     * @param lstTbTaskNbrVars
     * @return
     */
    Integer batchInsertSelect(@Param("lstTbTaskNbrVars") List<TbTaskNbrVar> lstTbTaskNbrVars);

<select id="selectTaskNbrVarCountBySidAndNbrs" resultType="java.lang.Integer">
        SELECT
        COUNT(*)
        FROM tb_task_nbr_var
        WHERE 1=1
        AND sid = #{sid}
        AND nbr IN
        <foreach item="nbr" collection="nbrs"
                 open="(" separator="," close=")">
            #{nbr}
        </foreach>
    </select>

Dao

/**
     * 根据sid和号码查询记录数
     * @param sid
     * @param lstNbrs
     * @return
     */
    Integer selectTaskNbrVarCountBySidAndNbrs(@Param("sid") String sid, @Param("nbrs") List<String> lstNbrs);

批量更新

<update id="batchUpdateTaskNbrVarBySidAndNbr" parameterType="java.util.List">
        <foreach collection="list" item="item" separator=";">
            UPDATE tb_task_nbr_var
            SET appId=#{item.appid},taskId=#{item.taskid}
            WHERE 1=1
            AND sid=#{item.sid}
            AND nbr=#{item.nbr}
        </foreach>
    </update>

Dao：

Integer batchUpdateTaskNbrVarBySidAndNbr(List<TbTaskNbrVar> lstTbTaskNbrVarList);

在if标签中使用枚举及外部静态方法，测试案例如下

  <select id="testEnum" resultType="com.ltq.model.User">
    SELECT *
    FROM user
    WHERE 1=1
    <if test="@java.util.Objects@equals(id,@com.test.mapper.UserIdEnum@USER_01.getId)">
        AND id=#{id}
    </if>
  </select>

枚举类

public enum UserIdEnum {
    /**
     * 测试枚举
     */
    USER_01(1,"测试");

    private Integer id;

    private String desc;

    UserIdEnum(Integer id, String desc) {
        this.id = id;
        this.desc = desc;
    }

    public Integer getId() {
        return id;
    }

    public String getDesc() {
        return desc;
    }
}

枚举只能定义在外部，在类中定义的话启动会报错。
参考https://blog.csdn.net/u010002184/article/details/86490787