mybatis如何实现saveOrUpdate
分享心得 人气:01. 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` varchar(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_time` 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},#{rewardLevel},#{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},now(),#{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` datetime 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_ci 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>
总结
以上为个人经验,希望能给大家一个参考,也希望大家多多支持。
加载全部内容