mybatis批量插入和批量更新

在工作中遇到这一块，也是百度解决了烦恼。在此整理了一下。

/**
 * <p>
 * 数据展示内容配置明细 Mapper 接口
 * </p>
 *
 * @author dsh123
 * @since 2018-05-23
 */
public interface AdminDataContentDetailMapper extends BaseMapper<AdminDataContentDetail> {
    public void insertBatch(@Param(value="contentDetail") List<AdminDataContentDetail> contentDetail);

    void updateListByScan(@Param(value="contentDetail") List<AdminDataContentDetail> contentDetail);

}

mapper层里注释要注意一下。

mapper.xml:批量插入

    <insert id="insertBatch"  parameterType="java.util.List"  >

        INSERT INTO admin_data_content_detail(
            id,
            datasource_id,
            content_id,
            column_name,
            column_type,
            create_by,
            create_date,
            update_by,
            update_date,
            remarks,
            del_flag
        ) VALUES
        <foreach collection="contentDetail" item="content" index="index" separator=",">
                       (
            #{content.id},
            #{content.datasourceId},
            #{content.contentId},
            #{content.columnName},
            #{content.columnType},
            #{content.createBy},
            #{content.createDate},
            #{content.updateBy},
            #{content.updateDate},
            #{content.remarks},
            #{content.delFlag}
        )

        </foreach>
    </insert>

mapper.xml:批量更新

 1      <update id="updateListByScan" parameterType="java.util.List">
 2          update admin_data_content  
 3     <trim prefix="set" suffixOverrides=",">  
 4   
 5         <trim prefix="id =case" suffix="end,">  
 6             <foreach collection="contentDetail" item="content">  
 7                 <if test="content.id!=null">  
 8                      when id = #{content.id} then #{content.id}  
 9                 </if>  
10             </foreach>  
11         </trim>  
12         <trim prefix="data_source_id =case" suffix="end,">  
13             <foreach collection="contentDetail" item="content">   
14                 <if test="content.datasourceId!=null">  
15                      when id = #{content.id} then  #{content.datasourceId}
16                 </if>  
17             </foreach>  
18         </trim>     
19         <trim prefix="create_by =case" suffix="end,">  
20             <foreach collection="contentDetail" item="content">   
21                 <if test="content.createBy!=null">  
22                       when id = #{content.id} then  #{content.createBy}   
23                 </if>  
24             </foreach>  
25         </trim>  
26         <trim prefix="create_date =case" suffix="end,">  
27            <foreach collection="contentDetail" item="content">  
28                 <if test="content.createDate!=null">  
29                      when id = #{content.id}  then #{content.createDate}  
30                 </if>  
31             </foreach>  
32         </trim> 
33          <trim prefix="update_by =case" suffix="end,">  
34            <foreach collection="contentDetail" item="content">  
35                 <if test="content.updateBy!=null">  
36                     when id = #{content.id}  then #{content.updateBy}  
37                 </if>  
38             </foreach>  
39         </trim> 
40          <trim prefix="update_date =case" suffix="end,">  
41            <foreach collection="contentDetail" item="content">  
42                 <if test="content.updateDate!=null">  
43                      when id = #{content.id}  then #{content.updateDate}  
44                 </if>  
45             </foreach>  
46         </trim> 
47          <trim prefix="remarks =case" suffix="end,">  
48            <foreach collection="contentDetail" item="content">  
49                 <if test="content.remarks!=null">  
50                     when id = #{content.id} then #{content.remarks}  
51                 </if>  
52             </foreach>  
53         </trim>  
54     </trim>  
55     <where>  
56         <foreach collection="contentDetail" separator="or" item="content">  
57             id = #{content.id} 
58         </foreach>  
59     </where>  
60     </update>

批量更新的时候本意是采用插入的那种xml写法，结果报错问题没解决采用了这种写法。由于整理的时间间隔太长，记忆模糊了。汗。这也提醒我要及时总结

补充：oracle批量插入语法和mysql不同，在后面贴出

<insert id="insertBatch"  parameterType="java.util.List">
        INSERT INTO t_expert (
        id,
        name,
        intro,
        domain,
        domain_detail,
        domain_comments,
        approval_status
        )
        <foreach collection="experts" item="content" index="index" separator="union all" >
            select
                #{content.id,jdbcType=VARCHAR},
                #{content.name,jdbcType=VARCHAR},
                #{content.intro,jdbcType=VARCHAR},
                #{content.domain,jdbcType=VARCHAR},
                #{content.domainDetail,jdbcType=VARCHAR},
                #{content.domainComments,jdbcType=VARCHAR},
                #{content.approvalStatus,jdbcType=VARCHAR}
             from dual
        </foreach>
    </insert>