mybatis如何实现saveOrUpdate

目录

• 1. selectKey标签查询
• 2. 主键自增或者累加的，不使用selectKey
• 3. 主键为varchar的使用ON DUPLICATE KEY UPDATE
• 总结

1. selectKey标签查询

DDL

CREATE TABLE `luck_reward_info` (
 `id` int NOT NULL AUTO_INCREMENT COMMENT 'id',
 `activity_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '活动id',
 `reward_name` varcha编程客栈r(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '奖品名',
 `reward_level` int DEFAULT NULL COMMENT '奖品等级',
 `num` int DEFAULT NULL COMMENT '奖品数量',
 `dis_number` int DEFAULT '0' COMMENT '已发奖品数量',
 `create_time` datetime DEFAULT NULL COMMENT '创建时间',
 `update_tulHdQzjime` datetime DEFAULT NULL COMMENT '修改时间',
 `field` varchar(255) DEFAULT NULL COMMENT '备注',
 PRIMARY KEY (`id`,`activity_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='奖品信息表';

RewardInfoMapper接口

@Mapper
public interface RewardInfoMapper{
 int saveOrUpdate(RewardInfo rewardInfo);
}

使用selectKey

<insert id="saveOrUpdate" >
 <selectKey keyProperty="count" resultType="int" order="BEFORE">
  select count(*) from luck_reward_info where id = #{id}
 </selectKey>
 <if test="count > 0">
   UPDATE luck_reward_info
    <set>
      <if test='activityId != null and activityId != "" '>activity_id=#{activityId},</if>
      <if test='rewardName != null and rewardName != "" '>reward_name=#{rewardName},</if>
      <if test='rewardLevel != null '>reward_level=#{rewardLevel},</if>
      <if test='num != null '>num=#{num},</if>
      <if test='disNumber != null '>dis_number=#{disNumber},</if>
      update_time=now(),
      <if test='field != null and field != "" '>field=#{field},</if>
    </set>
    WHERE id=#{id}
 </if>
 <if test="count==0">
  INSERT INTO luck_reward_info(
      id,activity_id,reward_name,reward_level,number,create_time,field
      )
      VALUES (
      (select id from (select (ifnull(max(id), 0)) + 1 as id from luck_reward_info)t), #{activityId},#{rewardName},#{re编程客栈wardLevel},#{number},now(),#{field}
      )
 </if>
</insert>

报错：

org.myBATis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.executor.ExecutorException: No setter found for the keyProperty ‘count’ in com.cmbchina.ssm.entity.RewardInfo.

原因：RewardInfo内必须有count属性，来接收count值，否则报错

优化：接口使用Map，实体类不需要再新增count字段了

//RewardInfoMapper.Java
int saveOrUpdate(Map<String, Object> map);

2. 主键自增或者累加的，不使用selectKey

根据id是否为空，空新增，不为空修改

<insert id="saveOrUpdate" keyProperty="id" useGeneratedKeys="true">
    <if test="id == null">
      INSERT INTO luck_reward_info(
        id,activity_id,reward_name,reward_level,num,create_time,field
      )
      VALUES (
        (select id from (select (ifnull(max(id), 0)) + 1 as id from luck_reward_info)t), #{activityId},#{rewardName},#{rewardLevel},#{num},n开发者_Go学习ow(),#{field}
      )
    </if>
    <if test="id != null">
      UPDATE luck_reward_info
      <set>
        <if test='activityId != null and activityId != "" '>activity_id=#{activityId},</if>
        <if test='rewardName != null and rewardName != "" '>reward_name=#{rewardName},</if>
        <if test='rewardLevel != null '>reward_level=#{rewardLevel},</if>
        <if test='num!= null '>num=#{num},</if>
        <if test='disNumber != null '>dis_number=#{disNumber},</if>
        update_time=now(),
        <if test='field != null and field != "" '>field=#{field},</if>
      </set>
      WHERE id=#{id}
    </if>
  </insert>

3. 主键为varchar的使用ON DUPLICATE KEY UPDATE

DDL

CREATE TABLE `luck_activity_info` (
 `id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'id',
 `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '标题',
 `start_time` datetime DEFAULT NULL COMMENT '开始时间',
 `end_time` datetimandroide DEFAULT NULL COMMENT '结束时间',
 `sap_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '操作员工编号',
 `create_time` datetime DEFAULT NULL COMMENT '创建时间',
 `update_time` datetime DEFAULT NULL COMMENT '修改时间',
 PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_cijavascript ROW_FORMAT=DYNAMIC COMMENT='抽奖活动表';

条数 新增返回1，修改返回2

<insert id="saveOrUpdate">
    INSERT INTO luck_activity_info(
      id,title,start_time,end_time,sap_id,create_time
    )
    VALUES (
      #{id},#{title},#{startTime},#{endTime},#{sapId},now()
    )
    ON DUPLICATE KEY UPDATE
      <if test='title != null and title != "" '>title=#{title},</if>
      <if test='startTime != null '>start_time=#{startTime},</if>
      <if test='endTime != null '>end_time=#{endTime},</if>
      <if test='sapId != null and sapId != "" '>sap_id=#{sapId},</if>
      update_time=now()
  </insert>

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。