动态SQL汇总

1 -批量新增

int batchInsert(@Param("list") List<T> list);

<insert id="batchInsert" parameterType="map">
		insert into product
		values
<foreach collection="list" item="item" separator=",">
    (
  	#{item.entCode,jdbcType=VARCHAR}, 
     #{item.categoryCode,jdbcType=VARCHAR}, 
     #{item.bizCode,jdbcType=VARCHAR},
    	#{item.name,jdbcType=VARCHAR},
     #{item.unit,jdbcType=VARCHAR},
   	 #{item.isDeleted,jdbcType=BOOLEAN}, 
     #{item.createdAt,jdbcType=TIMESTAMP},
    	#{item.createdBy,jdbcType=VARCHAR}, 
     #{item.updatedAt,jdbcType=TIMESTAMP}, 
     #{item.updatedBy,jdbcType=VARCHAR}
    )
</foreach>
</insert>

2- 批量删除

int deleteBatchByCodes(@Param("codes") Collection<String> codeCollection)

<update id="deleteBatchByCodes">
    update product
    set updated_at = #{updatedAt,jdbcType=TIMESTAMP},
    updated_by = #{userCode,jdbcType=VARCHAR},
    is_deleted = true,
    where code in
    <foreach close=")" collection="codes " index="index" item="item" open="(" separator=",">
        #{item,jdbcType=VARCHAR}
    </foreach>
</update>

3 批量修改

int updateBatch(@Param("list")List<Product> list);

code这里是每刻的铸件

如果你们是ID，则替换为ID

<update id="updateBatch" parameterType="java.util.List">
    update product
  
    <trim prefix="set" suffixOverrides=",">
  
        <trim prefix="name = case" suffix="end,">
            <foreach collection="list" index="index" item="item">
                <if test="item.name != null">
                    when code = #{item.code,jdbcType=VARCHAR} then  																#{item.name,jdbcType=VARCHAR}
                </if>
            </foreach>
        </trim>
              
                  
   
        <trim prefix="updated_by = case" suffix="end,">
            <foreach collection="list" index="index" item="item">
                <if test="item.updatedBy != null">
                    when code = #{item.code,jdbcType=VARCHAR} then #{item.updatedBy,jdbcType=VARCHAR}
                </if>
            </foreach>
        </trim>
                  
                  
    </trim>
                  
    where code in
    <foreach close=")" collection="list" item="item" open="(" separator=", ">
        #{item.code,jdbcType=VARCHAR}
    </foreach>
</update>

4 批量查询

List<ProductCategory> queryProductCategoryList(@Param("entCode") String entCode, @Param("codes") List<String> codes);

<select id="queryProductCategoryList" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List"/>
    from product_category
    where ent_code = #{entCode, javaType = String, jdbcType=VARCHAR}
    and is_deleted = 0
    <if test="codes.size()>0">
        and code in
        <foreach close=")" collection="codes" item="code" open="(" separator=",">
            #{code}
        </foreach>
    </if>
</select>

5 列不为空，再更新（增量inline更新）

<update id="updateByPrimaryKeySelective" parameterType="com.maycur.product.entity.ProductCategory">
    update product_category
    <set>
        <if test="entCode != null">
            ent_code = #{entCode,jdbcType=VARCHAR},
        </if>
        <if test="parentCode != null">
            parent_code = #{parentCode,jdbcType=VARCHAR},
        </if>
        <if test="bizCode != null">
            biz_code = #{bizCode,jdbcType=VARCHAR},
        </if>
        <if test="name != null">
            `name` = #{name,jdbcType=VARCHAR},
        </if>
        <if test="isLeaf != null">
            is_leaf = #{isLeaf,jdbcType=BOOLEAN},
        </if>
        <if test="type != null">
            `type` = #{type,jdbcType=VARCHAR},
        </if>
        <if test="isDeleted != null">
            is_deleted = #{isDeleted,jdbcType=BOOLEAN},
        </if>
        <if test="createdAt != null">
            created_at = #{createdAt,jdbcType=TIMESTAMP},
        </if>
        <if test="createdBy != null">
            created_by = #{createdBy,jdbcType=VARCHAR},
        </if>
        <if test="updatedAt != null">
            updated_at = #{updatedAt,jdbcType=TIMESTAMP},
        </if>
        <if test="updatedBy != null">
            updated_by = #{updatedBy,jdbcType=VARCHAR},
        </if>
    </set>
    where code = #{code,jdbcType=VARCHAR}
</update>

6 列不为空，再插入

<insert id="insertSelective" parameterType="com.maycur.product.entity.ProductCategory">
    insert into product_category
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="code != null">
            code,
        </if>
        <if test="entCode != null">
            ent_code,
        </if>
        <if test="parentCode != null">
            parent_code,
        </if>
        <if test="bizCode != null">
            biz_code,
        </if>
        <if test="name != null">
            `name`,
        </if>
        <if test="isLeaf != null">
            is_leaf,
        </if>
        <if test="type != null">
            `type`,
        </if>
        <if test="isDeleted != null">
            is_deleted,
        </if>
        <if test="createdAt != null">
            created_at,
        </if>
        <if test="createdBy != null">
            created_by,
        </if>
        <if test="updatedAt != null">
            updated_at,
        </if>
        <if test="updatedBy != null">
            updated_by,
        </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
        <if test="code != null">
            #{code,jdbcType=VARCHAR},
        </if>
        <if test="entCode != null">
            #{entCode,jdbcType=VARCHAR},
        </if>
        <if test="parentCode != null">
            #{parentCode,jdbcType=VARCHAR},
        </if>
        <if test="bizCode != null">
            #{bizCode,jdbcType=VARCHAR},
        </if>
        <if test="name != null">
            #{name,jdbcType=VARCHAR},
        </if>
        <if test="isLeaf != null">
            #{isLeaf,jdbcType=BOOLEAN},
        </if>
        <if test="type != null">
            #{type,jdbcType=VARCHAR},
        </if>
        <if test="isDeleted != null">
            #{isDeleted,jdbcType=BOOLEAN},
        </if>
        <if test="createdAt != null">
            #{createdAt,jdbcType=TIMESTAMP},
        </if>
        <if test="createdBy != null">
            #{createdBy,jdbcType=VARCHAR},
        </if>
        <if test="updatedAt != null">
            #{updatedAt,jdbcType=TIMESTAMP},
        </if>
        <if test="updatedBy != null">
            #{updatedBy,jdbcType=VARCHAR},
        </if>
    </trim>
</insert>

7 查询新增insert后的主键

<insert id="insertUser" parameterType="cn.itcast.mybatis.po.User"> 
<selectKey keyProperty="id" order="AFTER" resultType="int">
select LAST_INSERT_ID() </selectKey>


INSERT INTO USER(username,birthday,sex,address) VALUES(#{username},#{birthday},#{sex},#{address}) </insert>