mybatis如何实现saveOrUpdate
作者:分享心得 时间:2021-07-18 10:33:11
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` 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>
来源:https://blog.csdn.net/m0_37539286/article/details/126594466
标签:mybatis,saveOrUpdate
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Java 如何调用long的最大值和最小值
2021-07-24 02:27:18
![](https://img.aspxhome.com/file/2023/0/60590_0s.jpg)
Java实现读取resources目录下的文件路径的九种方式
2023-11-23 11:47:02
Java使用Freemarker页面静态化生成的实现
2022-07-24 08:48:42
![](https://img.aspxhome.com/file/2023/9/63069_0s.png)
本地jvm执行flink程序带web ui的操作
2022-09-03 20:49:00
解决jmap命令打印JVM堆信息异常的问题
2023-11-05 09:07:02
![](https://img.aspxhome.com/file/2023/4/58884_0s.jpg)
OpenCV画任意圆弧曲线
2023-06-22 19:28:44
![](https://img.aspxhome.com/file/2023/5/94045_0s.jpg)
SSM Mapper文件查询出返回数据查不到个别字段的问题
2023-10-17 01:02:08
![](https://img.aspxhome.com/file/2023/3/58603_0s.png)
flutter material widget组件之信息展示组件使用详解
2023-06-22 08:45:35
flyway实现java 自动升级SQL脚本的问题及解决方法
2021-10-14 00:23:19
![](https://img.aspxhome.com/file/2023/9/60399_0s.jpg)
引入mybatis-plus报 Invalid bound statement错误问题的解决方法
2021-06-01 14:28:00
![](https://img.aspxhome.com/file/2023/3/66253_0s.jpg)
Java8中的 Lambda表达式教程
2023-10-13 01:32:29
解析Java的设计模式编程之解释器模式的运用
2022-01-04 04:02:18
![](https://img.aspxhome.com/file/2023/5/63755_0s.jpg)
SpringCloud搭建netflix-eureka微服务集群的过程详解
2023-09-02 18:11:52
![](https://img.aspxhome.com/file/2023/3/58293_0s.jpg)
浅谈JAVA设计模式之享元模式
2021-09-22 10:12:06
![](https://img.aspxhome.com/file/2023/2/66062_0s.jpg)
Android中获取资源 id 及资源 id 的动态获取
2023-06-30 04:38:06
Java多线程之同步锁-lock详解
2023-12-16 14:40:08
![](https://img.aspxhome.com/file/2023/0/62230_0s.png)
Spring整合Quartz实现定时任务调度的方法
2023-07-07 00:55:55
基于Java接口回调详解
2023-11-09 00:03:11
![](https://img.aspxhome.com/file/2023/4/59314_0s.png)
Spring Boot深入排查 java.lang.ArrayStoreException异常
2023-07-11 16:31:27
Java数据结构之顺序表的实现
2023-06-22 00:47:26
![](https://img.aspxhome.com/file/2023/8/57418_0s.jpg)