MyBatis动态增改,多字段模糊查询
示例:
insert
insert into bargain_products
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null and id != ''">id,</if>
<if test="userid != null and userid != ''">user_id,</if>
<if test="productName != null and productName != ''">product_name,</if>
<if test="type != null and type != ''">type,</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id != null and id != ''">#{id},</if>
<if test="userid != null and userid != ''">#{userid},</if>
<if test="productName != null and productName != ''">#{productName},</if>
<if test="type != null and type != ''">#{type},</if>
</trim>
update
update bargain_products
<trim prefix="set" suffixOverrides=",">
<if test="productName != null and productName != ''">product_name = #{productName},</if>
<if test="type != null and type != ''">type = #{type},</if>
<if test="texture != null and texture != ''">texture = #{texture},</if>
</trim>
where id = #{id}
select
SELECT
bp.id,
bp.user_id userid,
bp.product_name productName,
bp.type,
DATE_FORMAT(bp.create_time,'%Y-%m-%d') createTime
FROM
bargain_products bp
LEFT JOIN sys_user sus ON bp.user_id = sus.userid
<where>
<if test="secondLevel != null and secondLevel != ''">
sus.second_level = #{secondLevel}
</if>
<if test="fuzzyQuery != null and fuzzyQuery != '' ">
and CONCAT(bp.product_name, bp.type) LIKE "%"#{fuzzyQuery}"%"
</if>
</where>
ORDER BY bp.create_time DESC
多字段模糊查询中如果有一个字段为null,则返回的也是NULL,那么这一条记录可能就会被错过,怎么处理呢,我这边使用的是IFNULL进行判断
<if test="fuzzyQuery != null and fuzzyQuery != '' ">
and CONCAT(IFNULL(bp.product_name,''), IFNULL( bp.type,'')) LIKE "%"#{fuzzyQuery}"%"
</if>
delete
要么就直接删
DELETE
FROM
bargain_products
WHERE
id = 'BP-6ec77c53464f43afbe55b23d4a4e7b7d'
如果需要加别名的格式
DELETE bp FROM bargain_products bp WHERE bp.id ='BP-6ec77c5346'
本文来自博客园,作者:好事的猫,转载请注明原文链接:https://www.cnblogs.com/BeiJiuGuRen/p/15828593.html
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· 上周热点回顾(2.17-2.23)
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 如何使用 Uni-app 实现视频聊天(源码,支持安卓、iOS)