mybatis批量插入时判断是否存在 如果存在就更新或忽略

mybatis批量插入时判断是否存在 如果存在就更新

 建立了一个唯一的约束 payroll_number，来判断是否重复 

 <insert id="insertStaffSalaryList" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
    insert into staff_salary
    (user_id, group_id, payroll_number, approval_date, user_name, id_card, group_name, join_date, leave_date, online_days, work_months,
    tb_sumpremium, xb_sumpremium, xb_sumnetpremium, zb_sumpremium, zb_sumnetpremium, incentive_coefficient, basic_subsidy,
    is_probation, harvest_allowance, basic_salary, talking_time_reward, personal_task, sumpremium, complex_coefficient,
    late_Leave_early, take_leave, sick_leave, absenteeism, full_attendance, total_attendance, recommend_will,
    business_violation, lmdi_performance, coulmn_id, coulmn1, coulmn2, coulmn3, coulmn4, coulmn5, coulmn6, coulmn7,
    coulmn8, coulmn9, oth_reward, performance, kpi, oth_bonus, total_wages, created_name, created_date, created_id, profit_insurance_coefficient)
    values
    <foreach collection="list" item="item" index="index" separator=",">
      (
        #{item.userId,jdbcType=INTEGER}, #{item.groupId,jdbcType=INTEGER}, #{item.payrollNumber,jdbcType=VARCHAR},
        #{item.approvalDate,jdbcType=TIMESTAMP}, #{item.userName,jdbcType=VARCHAR},
        #{item.idCard,jdbcType=VARCHAR}, #{item.groupName,jdbcType=VARCHAR}, #{item.joinDate,jdbcType=TIMESTAMP},
        #{item.leaveDate,jdbcType=TIMESTAMP},  #{item.onlineDays,jdbcType=DECIMAL}, #{item.workMonths,jdbcType=INTEGER}, #{item.tbSumpremium,jdbcType=DECIMAL},
        #{item.xbSumpremium,jdbcType=DECIMAL}, #{item.xbSumnetpremium,jdbcType=DECIMAL}, #{item.zbSumpremium,jdbcType=DECIMAL},
        #{item.zbSumnetpremium,jdbcType=DECIMAL}, #{item.incentiveCoefficient,jdbcType=DECIMAL}, #{item.basicSubsidy,jdbcType=INTEGER},
        #{item.isProbation,jdbcType=VARCHAR}, #{item.harvestAllowance,jdbcType=INTEGER}, #{item.basicSalary,jdbcType=DECIMAL},
        #{item.talkingTimeReward,jdbcType=INTEGER}, #{item.personalTask,jdbcType=INTEGER}, #{item.sumpremium,jdbcType=INTEGER},
        #{item.complexCoefficient,jdbcType=INTEGER}, #{item.lateLeaveEarly,jdbcType=INTEGER}, #{item.takeLeave,jdbcType=INTEGER},
        #{item.sickLeave,jdbcType=INTEGER}, #{item.absenteeism,jdbcType=INTEGER}, #{item.fullAttendance,jdbcType=INTEGER},
        #{item.totalAttendance,jdbcType=INTEGER}, #{item.recommendWill,jdbcType=INTEGER},#{item.businessViolation,jdbcType=INTEGER},
        #{item.lmdiPerformance,jdbcType=INTEGER}, #{item.coulmnId,jdbcType=INTEGER}, #{item.coulmn1,jdbcType=VARCHAR},
        #{item.coulmn2,jdbcType=VARCHAR}, #{item.coulmn3,jdbcType=VARCHAR}, #{item.coulmn4,jdbcType=VARCHAR},
        #{item.coulmn5,jdbcType=VARCHAR}, #{item.coulmn6,jdbcType=VARCHAR}, #{item.coulmn7,jdbcType=VARCHAR},
        #{item.coulmn8,jdbcType=VARCHAR}, #{item.coulmn9,jdbcType=VARCHAR}, #{item.othReward,jdbcType=DECIMAL},
        #{item.performance,jdbcType=DECIMAL}, #{item.kpi,jdbcType=DECIMAL}, #{item.othBonus,jdbcType=DECIMAL}, #{item.totalWages,jdbcType=DECIMAL},
        #{item.createdName,jdbcType=VARCHAR}, #{item.createdDate,jdbcType=TIMESTAMP}, #{item.createdId,jdbcType=INTEGER}, #{item.profitInsuranceCoefficient,jdbcType=DECIMAL}
      )
    </foreach>
    on duplicate key update
    approval_date = values(approval_date), user_name = values(user_name), work_months = values(work_months), tb_sumpremium = values(tb_sumpremium) ,
     xb_sumpremium = values(xb_sumpremium), xb_sumnetpremium = values(xb_sumnetpremium), zb_sumpremium = values(zb_sumpremium), zb_sumnetpremium = values(zb_sumnetpremium) ,
    incentive_coefficient = values(incentive_coefficient), basic_subsidy = values(basic_subsidy), is_probation = values(is_probation),  harvest_allowance = values(harvest_allowance) ,
      basic_salary = values(basic_salary),  talking_time_reward = values(talking_time_reward) , personal_task = values(personal_task) , sumpremium = values(sumpremium),
      complex_coefficient = values(complex_coefficient), late_Leave_early = values(late_Leave_early), take_leave = values(take_leave), sick_leave = values(sick_leave),
       absenteeism = values(absenteeism), full_attendance = values(full_attendance), total_attendance = values(total_attendance), recommend_will = values(recommend_will),
       business_violation = values(business_violation), lmdi_performance = values(lmdi_performance), coulmn_id = values(coulmn_id), coulmn1 = values(coulmn1),
        coulmn2  = values(coulmn2), coulmn3 = values(coulmn3), coulmn4 = values(coulmn4), coulmn5 = values(coulmn5), coulmn6 = values(coulmn6),
        coulmn7  = values(coulmn7), coulmn8  = values(coulmn8), coulmn9  = values(coulmn9), oth_reward  = values(oth_reward),
        performance  = values(performance), kpi = values(kpi), oth_bonus  = values(oth_bonus), total_wages  = values(total_wages),
        created_name = values(created_name), created_date = values(created_date), created_id = values(created_id),
        profit_insurance_coefficient = values(profit_insurance_coefficient)
  </insert>

mybatis批量插入时判断是否存在 如果存在就忽略  

INSERT INTO staff_salary(user_id, approval_date, user_name, id_card) 
SELECT '491', '2010-01-10', '高某' , '6404251111112227'
FROM DUAL
WHERE NOT EXISTS(SELECT id FROM staff_salary WHERE approval_date = '2020-01-10' AND id_card ='6404251111112227' )

 先试了试单个数据的增加判断 关键词

insert into 表名 (列名) 

      （注意这里没有value） select 插入的值  

             from dual

                 where not exists(查询的数据用来判断唯一的条件)

 

      放上mybatis代码   注意 separator="union all"

     

<insert id="insertStaffSalaryList" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
    insert into staff_salary (user_id, approval_date, user_name, id_card, group_name, join_date, leave_date, online_days,
    tb_sumpremium, xb_sumpremium, xb_sumnetpremium, zb_sumpremium, zb_sumnetpremium, incentive_coefficient, basic_subsidy,
    is_probation, harvest_allowance, basic_salary, talking_time_reward, personal_task, sumpremium, complex_coefficient,
    late_Leave_early, take_leave, sick_leave, absenteeism, full_attendance, total_attendance, recommend_will,
    business_violation, lmdi_performance, coulmn_id, coulmn1, coulmn2, coulmn3, coulmn4, coulmn5, coulmn6, coulmn7,
    coulmn8, coulmn9, oth_reward, performance, kpi, oth_bonus, total_wages, created_name, created_date, created_id)
    <foreach collection="list" item="item" index="index" separator="union all">
      ( select
        #{item.userId,jdbcType=INTEGER}, #{item.approvalDate,jdbcType=TIMESTAMP}, #{item.userName,jdbcType=VARCHAR},
      #{item.idCard,jdbcType=VARCHAR}, #{item.groupName,jdbcType=VARCHAR}, #{item.joinDate,jdbcType=TIMESTAMP},
      #{item.leaveDate,jdbcType=TIMESTAMP},  #{item.onlineDays,jdbcType=INTEGER} ,#{item.tbSumpremium,jdbcType=DECIMAL},
      #{item.xbSumpremium,jdbcType=DECIMAL}, #{item.xbSumnetpremium,jdbcType=DECIMAL}, #{item.zbSumpremium,jdbcType=DECIMAL},
      #{item.zbSumnetpremium,jdbcType=DECIMAL}, #{item.incentiveCoefficient,jdbcType=DECIMAL}, #{item.basicSubsidy,jdbcType=INTEGER},
      #{item.isProbation,jdbcType=VARCHAR}, #{item.harvestAllowance,jdbcType=INTEGER}, #{item.basicSalary,jdbcType=DECIMAL},
      #{item.talkingTimeReward,jdbcType=INTEGER}, #{item.personalTask,jdbcType=INTEGER}, #{item.sumpremium,jdbcType=INTEGER},
      #{item.complexCoefficient,jdbcType=INTEGER}, #{item.lateLeaveEarly,jdbcType=INTEGER}, #{item.takeLeave,jdbcType=INTEGER},
      #{item.sickLeave,jdbcType=INTEGER}, #{item.absenteeism,jdbcType=INTEGER}, #{item.fullAttendance,jdbcType=INTEGER},
      #{item.totalAttendance,jdbcType=INTEGER}, #{item.recommendWill,jdbcType=INTEGER},#{item.businessViolation,jdbcType=INTEGER},
      #{item.lmdiPerformance,jdbcType=INTEGER}, #{item.coulmnId,jdbcType=INTEGER}, #{item.coulmn1,jdbcType=VARCHAR},
      #{item.coulmn2,jdbcType=VARCHAR}, #{item.coulmn3,jdbcType=VARCHAR}, #{item.coulmn4,jdbcType=VARCHAR},
      #{item.coulmn5,jdbcType=VARCHAR}, #{item.coulmn6,jdbcType=VARCHAR}, #{item.coulmn7,jdbcType=VARCHAR},
      #{item.coulmn8,jdbcType=VARCHAR}, #{item.coulmn9,jdbcType=VARCHAR}, #{item.othReward,jdbcType=DECIMAL},
      #{item.performance,jdbcType=DECIMAL}, #{item.kpi,jdbcType=DECIMAL}, #{item.othBonus,jdbcType=DECIMAL}, #{item.totalWages,jdbcType=DECIMAL},
      #{item.createdName,jdbcType=VARCHAR}, #{item.createdDate,jdbcType=TIMESTAMP}, #{item.createdId,jdbcType=INTEGER}
       from DUAL where not exists (select id from staff_salary where approval_date = #{item.approvalDate,jdbcType=TIMESTAMP}
          and id_card = #{item.idCard,jdbcType=VARCHAR}  )
       )
    </foreach>

        如果帮助到你了，帮忙点个赞吧~