mysql/mybatis 批量新增/删除/修改

怼烎@ 2022-10-06 00:46 313阅读 0赞

**1.mybatis相关：**  
新增：–返回值为增加的的行数

int insertLableBatch(@Param("list")List<UserGroupFilterLabel> list);

<insert id="insertLableBatch"  parameterType="com.sf.cps.mana.model.UserGroupFilterLabel">
      insert into ti_user_group_filter_label
      (emp_group_id,label_type,label_code,label_name,label_symbol,label_content,label_valid,create_name,create_emp,create_time)
      values
      <foreach collection="list" item="re" separator=",">
        (
        #{ re.empGroupId,jdbcType=INTEGER},
        #{ re.labelType,jdbcType=VARCHAR},
        #{ re.labelCode,jdbcType=VARCHAR},
        #{ re.labelName,jdbcType=VARCHAR},
        #{ re.labelSymbol,jdbcType=VARCHAR},
        #{ re.labelContent,jdbcType=VARCHAR},
        #{ re.labelValid,jdbcType=INTEGER},
        #{ re.createName,jdbcType=VARCHAR},
        #{ re.createEmp,jdbcType=VARCHAR},
        now()
        )
      </foreach>
    </insert>

修改：使用 分号；拼接语句需要在链接配置上加上 allowMultiQueries=true& 不然会报sql赋值错误；

int updateLableBatch(@Param("list")List<UserGroupFilterLabel> list);

<update id="updateLableBatch" parameterType="com.sf.cps.mana.model.UserGroupFilterLabel">
      <foreach collection="list" separator=";" item="item">
        update ti_user_group_filter_label
        <set>
          <if test="item.empGroupId != null">
            emp_group_id = #{ item.empGroupId,jdbcType=INTEGER},
          </if>
          <if test="item.labelType != null">
            label_type = #{ item.labelType,jdbcType=VARCHAR},
          </if>
          <if test="item.labelCode != null">
            label_code = #{ item.labelCode,jdbcType=VARCHAR},
          </if>
          <if test="item.labelName != null">
            label_name = #{ item.labelName,jdbcType=VARCHAR},
          </if>
          <if test="item.labelSymbol != null">
            label_symbol = #{ item.labelSymbol,jdbcType=VARCHAR},
          </if>
          <if test="item.labelContent != null">
            label_content = #{ item.labelContent,jdbcType=VARCHAR},
          </if>
          <if test="item.labelValid != null">
            label_valid = #{ item.labelValid,jdbcType=INTEGER},
          </if>
          <if test="item.modifyEmp != null">
            modify_emp = #{ item.modifyEmp,jdbcType=VARCHAR},
          </if>
          <if test="item.modifyName != null">
            modify_name = #{ item.modifyName,jdbcType=VARCHAR},
          </if>
          modify_time = now()
        </set>
        where id = #{ item.id,jdbcType=INTEGER}
      </foreach>
    </update>

修改/删除(改状态式的删除)：–返回值为增加的的行数  
int delLableBatch(@Param(“modifyEmp”)String modifyEmp,@Param(“modifyName”)String modifyName, @Param(“list”)List list);

<update id="delLableBatch">
      update ti_user_group_filter_label
      <set>
        <if test="modifyEmp != null">
          modify_emp = #{ modifyEmp,jdbcType=VARCHAR},
        </if>
        <if test="modifyName != null">
          modify_name = #{ modifyName,jdbcType=VARCHAR},
        </if>
        modify_time = now(),
        label_valid = 0
      </set>
      where emp_group_id in
      <foreach collection="list" item="item" open="(" close=")" separator=",">
        #{ item,jdbcType=INTEGER}
      </foreach>
    </update>

**2.批处理相关：注意使用批处理需要在链接信息上添加 rewriteBatchedStatements=true 参数**

package com.sf.sfim.sync.util;
     
    import com.sf.sfim.sync.model.TabContact;
    import com.sf.sfim.sync.source.DynamicDataSourceRegister;
    import org.apache.commons.lang3.StringUtils;
    import org.springframework.stereotype.Component;
     
    import java.sql.Connection;
    import java.sql.Date;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    import java.util.List;
     
    @Component
    public class BatchedStatements { 
     
        /** * 批处理方法 * @param dbName 对应数据源的name * @param tabContacts * @return */
        @SuppressWarnings("all")
        public int updateContactBatche(String dbName, List<TabContact> tabContacts) { 
     
            Connection conn = null;
            PreparedStatement pst = null;
     
            try { 
                //数据源获取，此处使用动态数据源
                Class.forName("com.mysql.jdbc.Driver");
                if (StringUtils.isNotEmpty(dbName)) { 
                    conn = DynamicDataSourceRegister.slaveDataSources.get("dbName").getConnection();
                } else { 
                    conn = DynamicDataSourceRegister.defaultDataSource.getConnection();
                }
     
                //拼接逻辑
                String sql = "update tab_contact set col_create_time = ? where col_account = ?";
                pst = conn.prepareStatement(sql);
                for (int loop = 0; loop < tabContacts.size(); loop++) { 
                    pst.setDate(1, new Date(System.currentTimeMillis()));
                    pst.setString(2, tabContacts.get(loop).getColAccount());
                    pst.addBatch();
                }
                //执行和返回值的处理
                int[] result = pst.executeBatch();
                int sum = 0;
                for (int i : result) { 
                    sum = sum + i;
                }
                return sum;
            } catch (ClassNotFoundException e) { 
                e.printStackTrace();
            } catch (SQLException e) { 
                e.printStackTrace();
            } finally { 
                if (pst != null) { 
                    try { 
                        pst.close();
                    } catch (SQLException e) { 
                        e.printStackTrace();
                    }
                }
                if (conn != null) { 
                    try { 
                        conn.close();
                    } catch (SQLException e) { 
                        e.printStackTrace();
                    }
                }
            }
            return 0;
        }
    }

3.使用insert or update 方案：（有2种方案）

int saveOrUpdateBatch(@Param("list")List<Project> list);

<insert id="saveOrUpdateBatch">
        insert into project
        (code, org_code,org_name, source, create_time,version, rpt_date)
        values
        <foreach collection="list" item="re" separator=",">
        (
            #{ re.code,jdbcType=VARCHAR},
            #{ re.orgCode,jdbcType=VARCHAR},
            #{ re.orgName,jdbcType=VARCHAR},
            #{ re.source,jdbcType=VARCHAR},
            #{ re.createTime,jdbcType=BIGINT},
            #{ re.version,jdbcType=VARCHAR},
            CURRENT_DATE()
        )
        </foreach>
        ON DUPLICATE KEY UPDATE
        code = VALUES(code),
        name = VALUES(name),
        org_code = VALUES(org_code),
        org_name = VALUES(org_name),
        source = VALUES(source),
        create_time = VALUES(create_time),
        version = VALUES(version),
        rpt_date = VALUES(rpt_date)
    </insert>

<insert id="saveOrUpdate" parameterType="com.sf.sfim.task.model.Project" keyProperty="projectId" useGeneratedKeys="true">
        <selectKey keyProperty="count" order="BEFORE" resultType="java.lang.Integer">
            select count(1) as count from project where code = #{ code,jdbcType=VARCHAR}
        </selectKey>
        
        <if test="count>0">
            update project
            set name = #{ projectName,jdbcType=VARCHAR},
            org_code = #{ orgCode,jdbcType=VARCHAR},
            org_name = #{ orgName,jdbcType=VARCHAR},
            source = #{ source,jdbcType=VARCHAR},
            create_time = #{ createTime,jdbcType=BIGINT},
            version = #{ version,jdbcType=VARCHAR},
            rpt_date = #{ rptDate,jdbcType=DATE}
            where code = #{ code,jdbcType=VARCHAR}
        </if>
        
        <if test="count==0">
            insert into project (code, name, org_code,
            org_name, source, create_time,
            version, rpt_date)
            values (#{ code,jdbcType=VARCHAR}, #{ projectName,jdbcType=VARCHAR}, #{ orgCode,jdbcType=VARCHAR},
            #{ orgName,jdbcType=VARCHAR}, #{ source,jdbcType=VARCHAR}, #{ createTime,jdbcType=BIGINT},
            #{ version,jdbcType=VARCHAR}, #{ rptDate,jdbcType=DATE})
        </if>
    </insert>

**4.多insert/update处理：注意使用批处理需要在链接信息上添加 rewriteBatchedStatements=true 参数**

void overdueTaskCalculate(@Param("calculateTime") Long calculateTime);

<insert id="overdueTaskCalculate">
        
        insert into user_project_task_report (user_id,user_name,company_id,project_id,overdue)
          select tu.user_id,tu.user_name,t.company_id,t.project_id,count(tu.user_id) overdue
            from task t inner join task_user tu on t.id = tu.task_id where t.expect_end_time > 0
              and t.expect_end_time < #{ calculateTime} and t.status in (1,2) and t.del_status = 0
              and t.overdue_status = 0 and t.project_id > 0 and tu.del_status = 0 and tu.type in(1,3)
          group by tu.user_id,t.project_id
        ON DUPLICATE KEY UPDATE overdue=overdue+VALUES(overdue);
        
        insert into user_task_report (user_id,user_name,company_id,overdue)
          select tu.user_id,tu.user_name,t.company_id,count(tu.user_id) overdue
            from task t inner join task_user tu on t.id = tu.task_id where t.expect_end_time > 0
            and t.expect_end_time < #{ calculateTime} and t.status in (1,2) and t.del_status = 0
                  and t.overdue_status = 0 and tu.del_status = 0 and tu.type in(1,3)
          group by tu.user_id
        ON DUPLICATE KEY UPDATE overdue=overdue+VALUES(overdue);
        
        update task set overdue_status = 1,update_time = #{ calculateTime} where expect_end_time > 0
          and expect_end_time < #{ calculateTime} and overdue_status = 0  and status in (1,2) and del_status = 0;
    </insert>