Mybatis动态ＳＱＬ
2024-02-28 14:45:00
１．动态SQL基本标签
•if
•choose (when, otherwise)
•trim (where, set)
•foreach
２．IF 具体用法
<select id="XX"
resultType="Blog">
SELECT * FROM BLOG
WHERE state = ‘ACTIVE’
<if test="title != null">
AND title like #{title}
</if>
</select>
多个IF注意第二个ｉｆ语句里的表达式
<select id="XX"
resultType="Blog">
SELECT * FROM BLOG WHERE state = ‘ACTIVE’
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</select>
３．类似 if ,else用法例子
<select id="XX"
resultType="Blog">
SELECT * FROM BLOG WHERE state = ‘ACTIVE’
<choose>
<when test="title != null">
AND title like #{title}
</when>
<when test="author != null and author.name != null">
AND author_name like #{author.name}
</when>
<otherwise>
AND featured = 1
</otherwise>
</choose>
4．Where用法例子
<select id="XX"
resultType="Blog">
SELECT * FROM BLOG
WHERE
<if test="state != null">
state = #{state}
</if>
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</select>
<select id="XX"
resultType="Blog">
SELECT * FROM BLOG
<where>
<if test="state != null">
state = #{state}
</if>
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</where>
</select>
6．trim用法
<trim prefix="WHERE" prefixOverrides="AND |OR ">
...
</trim>
７．set用法注意set是sql语句中update table set.....不是将一个参数set到一个属性中
<update id="updateAuthorIfNecessary">
update Author
<set>
<if test="username != null">username=#{username},</if>
<if test="password != null">password=#{password},</if>
<if test="email != null">email=#{email},</if>
<if test="bio != null">bio=#{bio}</if>
</set>
where id=#{id}
</update>
8.foreach用法
<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
WHERE ID in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</select>
最新文章
- MATLAB地图工具箱学习总结（三）地图工具箱的基本知识
- Swift (if while)
- CMD魔法堂：CMD进入指定目录
- Linux Kernel中断子系统来龙去脉浅析【转】
- 实现gabor filter的滤波
- asp.net mvc下ckeditor使用
- mysql中自己定义函数编程
- PySide 简易教程<二>-------工欲善其事，必先利其器
- Oracle错误代码案例总结及解决方案
- SEO定义目的，优化的好处
- DB数据导出工具分享
- 【转载】从头编写 asp.net core 2.0 web api 基础框架 (1)
- Git访问远程出现错误
- Geekers团队成立日志
- saltstack自动化运维系列①之saltstack服务安装及简单使用
- python-跨域问题
- rhel7.0解决：This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
- dspmq dspmqver command not found(dspmq命令找不到,dspmqver主安装目录设置不正确
- Python2.7-Queue
- fiddler——一款莱斯的抓包工具