day06-动态SQL语句
动态SQL语句1.基本介绍
官方文档 mybatis – MyBatis 3 | 动态 SQL
[*]为什么需要动态SQL？
[*]动态SQL是MyBatis的强大特性之一
[*]使用 JDBC 或其他类似的框架，根据不同条件拼接SQL语句非常麻烦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号等
[*]SQL映射语句中的强大动态SQL语言可以很好地解决这个问题
[*]基本介绍
[*]在一个实际的项目中，sql语句往往是比较复杂的
[*]为了满足更加复杂的业务需求，MyBatis的设计者，提供了动态生成SQL的功能
[*]动态SQL的必要性
[*]这里有一些需求：查询Monster时，如果程序员输入的age不大于0，sql语句就不要带age条件；更新记录时，如果传入的Monster对象没有设置新的属性值，数据库表字段就保持原来的值，设置了新的值，就更新
[*]解决方案：从上面的需求中可以看出，有时我们在生成sql语句时，在同一个方法中，还要根据不同的情况生成不同的sql语句，解决的办法就是使用MyBatis提供的动态SQL机制
[*]动态SQL常用标签 -- 动态SQL提供了如下几种常用的标签，类似Java的控制语句：
[*]if[判断]
[*]where[拼接where子句]
[*]choose /when /otherwise[类似java的switch，注意是单分支]
[*]foreach[类似in]
[*]trim[替换关键字/定制元素的功能]
[*]set[在update的set中，可以保证进入set标签的属性被修改，没有进入set的就保持原来的值]
2.动态SQL-案例演示
2.1if标签
使用动态 SQL 最常见情景是，根据条件，用if标签包含 where 子句的一部分。
例子：要求查询age大于10的所有记录，如果age不大于0，则输出所有记录。
<select id="findMonsterByAge" resultType="Monster" parameterType="Integer">
SELECT * FROM `monster`
<if test="age >= 0">
WHERE `age`> #{age}
</if>
</select>当方法入参>=0时，底层的sql语句为：
https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20230227180806236.png
当入参5，并且名字为“牛魔王”的所有记录。如果名字为空，则不拼接带名字的条件，如果id<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select>id > #{id}<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select><select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select> AND `name` = #{name}<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select>如下，当id的值小于0时，会自动去除id子句和name子句前的AND
https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20230227183522393.png2.3choose/when/otherwise标签
有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。
<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select>例子：
（1）如果给的name不为空，就按名字查询妖怪
（2）如果指定的id>0，就按id来查询妖怪
（3）如果前面两个添加都不满足，就默认查询salary>100的
SELECT * FROM `monster`<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select><select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select>WHERE `name` = #{name}<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select><select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select> WHERE`id` >= #{id}<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select><select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select> WHERE`salary` > 100<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select>当入参name不符合条件时，选择id的子句
https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20230227193313279.png当入参name和id都不符合条件时，默认选择otherwise的子句
https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20230227193139283.png2.4foreach标签
动态 SQL 的另一个常见使用场景是对集合进行遍历（尤其是在构建 IN 条件语句的时候）。
foreach 元素的功能非常强大，它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。它也允许你指定开头（open）与结尾（close）的字符串以及集合项迭代之间的分隔符（separator），这个元素也不会错误地添加多余的分隔符。
提示 你可以将任何可迭代对象（如 List、Set 等）、Map 对象或者数组对象作为集合参数传递给 foreach。当使用可迭代对象或者数组时，index 是当前迭代的序号，item 的值是本次迭代获取到的元素。当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值。
例子：查询id为 10,12,14 的记录
SELECT * FROM `monster`<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select><select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select>id IN<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select><select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select> #{id}<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select><select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select>测试：
@Testpublic void findMonsterById_forEach() { Map map = new HashMap(); map.put("ids", Arrays.asList(10, 12, 14));//ids就是要遍历的集合，这里为List List monsters =<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select>monsterMapper.findMonsterById_forEach(map); for (Monster monster : monsters) { System.out.println("monster--" + monster); } if (sqlSession != null) { sqlSession.close(); }}https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20230227200903171.png2.5trim标签（使用较少）
你可以通过自定义 trim 元素来定制元素的功能。比如，和 where 元素等价的自定义 trim 元素为：
@Test
public void findMonsterById_forEach() {
Map<String, Object> map = new HashMap<>();
map.put("ids", Arrays.asList(10, 12, 14));//ids就是要遍历的集合，这里为List
List<Monster> monsters =
monsterMapper.findMonsterById_forEach(map);
for (Monster monster : monsters) {
System.out.println("monster--" + monster);
}
if (sqlSession != null) {
sqlSession.close();
}
}prefixOverrides 属性会忽略通过管道符（|）分隔的文本序列（注意此例中的空格是必要的）。上述例子会移除所有 prefixOverrides 属性中指定的内容，并且插入 prefix 属性中指定的内容。我们也可以在 prefixOverride 属性中添加其他文本序列，达到拓展where元素的功能。
此外，trim 元素还有 suffix、suffixOverrides 属性，功能和 prefix、prefixOverride 类似。
例子：按照名字或年龄查询记录，如果sql语句开头有 and | or 就替换成 where
SELECT * FROM `monster`<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select><select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select>HAHA `name` =#{name}<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select><select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select> OR `age` =#{age}<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select>https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20230227204220373.png2.6set标签（重点）
set 元素可以用于动态包含需要更新的列，忽略其它不更新的列。比如：
<select id="findMonsterByName_Trim" parameterType="map" resultType="Monster">
SELECT * FROM `monster`
<trim prefix="WHERE" prefixOverrides="and |or |HAHA">
<if test="name != null and name !=''">
HAHA `name` =#{name}
</if>
<if test="age != null and age !=''">
OR `age` =#{age}
</if>
</trim>
</select>这个例子中，set 元素会动态地在行首插入 SET 关键字，并会删掉多余的逗号（这些逗号是在使用条件语句给列赋值时引入的）。
你也可以使用trim标签来达到set标签的效果
例子：对指定id的记录进行修改，如果没有设置新的属性，则保持原来的值
UPDATE `monster`<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select>age`=#{age}, `name`=#{name}, `email`=#{email}, `birthday`=#{birthday}, `gender`=#{gender}, `salary`=#{salary}, WHERE `id`=#{id}当入参只有id和name时，SQL语句如下，只有name字段被修改了，其他的字段仍保持原来的值。
https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20230227211048101.png3.练习
有一个hero表，字段为id，nickname，skill，rank，salary，birthday
需求如下：
[*]添加hero记录
[*]查询rank大于30的所有记录，如果输入的rank不大于0，则输出所有的hero-if
[*]查询rank为 3,6,10（rank可变）的hero记录-foreach
[*]修改hero信息，如果没有设置新的属性值，则保持原来的值-set
[*]根据id查询hero，如果没有传入id，就返回所有的hero-where|choose
（1）创建表hero
<update id="updateMonster_set" parameterType="map">
UPDATE `monster`
<set>
<if test="age != null and age != ''">age`=#{age},</if>
<if test="name != null and name != ''">`name`=#{name},</if>
<if test="email != null and email != ''">`email`=#{email},</if>
<if test="birthday != null and birthday != ''">`birthday`=#{birthday},</if>
<if test="gender != null and gender != ''">`gender`=#{gender},</if>
<if test="salary != null and salary != ''">`salary`=#{salary},</if>
</set>
WHERE `id`=#{id}
</update>https://liyuelian.oss-cn-shenzhen.aliyuncs.com/imgs/image-20230227214507329.png（2）实体类Hero
-- 创建hero表
CREATE TABLE `hero`(
`id` INT NOT NULL AUTO_INCREMENT,
`nickname` VARCHAR(255) NOT NULL DEFAULT '',
`skill` VARCHAR(255) NOT NULL DEFAULT '',
`rank` INT NOT NULL,
`salary` DOUBLE NOT NULL,
`birthday` DATE NOT NULL,
PRIMARY KEY (`id`)
)CHARSET=utf8（3）接口声明
package com.li.entity;
import lombok.*;
import java.util.Date;
/**
* @author 李
* @version 1.0
*/
@NoArgsConstructor
@AllArgsConstructor
@Setter
@Getter
@ToString
public class Hero {
private Integer id;
private String nickname;
private String skill;
private Integer rank;
private double salary;
private Date birthday;
}（4）映射文件
<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select> INSERT INTO `hero` (nickname,skill,rank,salary,birthday) VALUE(#{nickname},#{skill},#{rank},#{salary},#{birthday})<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select><select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select>SELECT * FROM `hero`<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select> WHERE rank > #{rank}<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select><select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select> SELECT * FROM `hero`<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select><select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select><select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select>rank IN<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select><select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select><select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select>#{rank}<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select><select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select><select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select><select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select><select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select>UPDATE `hero`<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select> `nickname`=#{nickname},<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select>`skill`=#{skill},<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select>`rank`=#{rank},<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select>`salary`=#{salary},<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select>`birthday`=#{birthday},<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select> WHERE id = #{id}<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select><select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select>SELECT * FROM `hero`<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select><select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select><select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select> WHERE id = #{id}<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select><select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id>=0">
id > #{id}
</if>
<if test="name !=null and name !=''">
AND `name` = #{name}
</if>
</where>
</select>（5）测试略
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
页:
[1]