动态 SQL 语句-更复杂的查询业务需求
2023年11月16日大约 11 分钟约 2229 字
官方文档
文档地址
https://mybatis.org/mybatis-3/zh/dynamic-sql.html
为什么需要动态 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 的，保持原来的值]
案例演示
新建 Module dynamic-sql
新建 Module 后，先创建需要的包，再将需要的文件/资源拷贝过来(这里我们拷贝 Monster.java、resources/jdbc.properties 和 mybatis-config.xml)
创建 MonsterMapper.java MonsterMapper.xml 和 MonsterMapperTest.java
if 标签应用实例
需求：请查询 age 大于 10 的所有妖怪，如果程序员输入的 age 不大于 0，则输出所有的妖怪。
修改 MonsterMapper.java
package com.lzw.mapper;
import com.lzw.entity.Monster;
import org.apache.ibatis.annotations.Param;
import java.util.List;
/**
* @author LiAng
*/
public interface MonsterMapper {
//根据age查询结果
public List<Monster> findMonsterByAge(@Param(value = "age")Integer age);
}修改 MonsterMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lzw.mapper.MonsterMapper">
<!--
1. 配置方法public List<Monster> findMonsterByAge(@Param(value="age") Integer age);
2. 请查询age 大于 10 的所有妖怪，如果程序员输入的age 不大于 0, 则输出所有的妖怪
3. 按照以前方式来配置->问题? 如果使用原来的#{age} 在test表达式是取不出入参值
4. 解决方案是使用@Param
-->
<select id="findMonsterByAge" resultType="Monster" parameterType="Integer">
SELECT * FROM `monster` WHERE 1 = 1
<if test="age >= 0">
AND age > #{age}
</if>
</select>
</mapper>修改 MonsterMapperTest.java
package com.lzw.mapper;
import com.lzw.entity.Monster;
import com.lzw.util.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Before;
import org.junit.Test;
import java.util.List;
/**
* @author LiAng
*/
public class MonsterMapperTest {
private SqlSession sqlSession;
private MonsterMapper monsterMapper;
//初始化
@Before
public void init(){
sqlSession = MyBatisUtils.getSqlSession();
monsterMapper = sqlSession.getMapper(MonsterMapper.class);
System.out.println("monsterMapper = " + monsterMapper.getClass());
}
@Test
public void findMonsterByAge(){
List<Monster> monsters = monsterMapper.findMonsterByAge(-1);
for (Monster monster : monsters) {
System.out.println("monster-" + monster);
}
if(sqlSession != null){
sqlSession.close();
}
System.out.println("ok~~");
}
}
where 标签应用实例
需求：查询 id 大于 2 的，并且名字是 "牛魔王" 的所有妖怪。注意，如果名字为空， 或者输入的 id 小于 0，则不拼接 sql 语句。(如果名字为空，就不带名字条件，如果输入的 id 小于 0，就不带 id 的条件)
修改 MonsterMapper.java
//根据 id 和名字来查询结果
public List<Monster> findMonsterByIdAndName(Monster monster);修改 MonsterMapper.xml
<!--
1、配置public List<Monster> findMonsterByIdAndName(Monster monster);
2、查询id 大于 20的，并且名字是 "牛魔王" 的所有妖怪, 注意，如果名字为空，
或者输入的id 小于 0, 则不拼接 sql语句(老师梳理：如果名字为空 , 就不带名字条件,
如果输入的id 小于 0, 就不带id的条件)
3、where + if : WHERE `id` > #{id} AND `name` = #{name}
4. 如果我们入参是对象，test表达式中, 直接使用对象的属性名即可
5. where标签，会在组织动态sql时，加上where
6. mybatis底层自动的去掉多余的AND
-->
<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
SELECT * FROM `monster`
<where>
<if test="id >= 0">
AND `id` > #{id}
</if>
<if test="name != null and name != ''">
AND `name` = #{name}
</if>
</where>
</select>修改 MonsterMapperTest.java
@Test
public void findMonsterByIdAndName(){
Monster monster = new Monster();
monster.setId(2);
monster.setName("牛魔王");
List<Monster> monsters = monsterMapper.findMonsterByIdAndName(monster);
for (Monster m : monsters) {
System.out.println("monster-" + m);
}
if(sqlSession != null){
sqlSession.close();
}
System.out.println("操作成功~~");
}
choose-when-otherwise 应用实例
需求：如果给的 name 不为空，就按名字查询妖怪。
如果指定的 id>0，就按 id 来查询妖怪。
如果前面两个条件都不满足，就默认查询 salary > 9000的。
要求使用 choose/when/otherwise 标签实现，传入参数要求使用 Map。
修改 MonsterMapper.java
//测试choose标签的使用
public List<Monster> findMonsterByIdOrName_choose(Map<String,Object> map);修改 MonsterMapper.xml
<!--
配置 public List<Monster> findMonsterByIdOrName_choose(Map<String,Object> map);
需求：如果给的 name 不为空，就按名字查询妖怪。如果指定的 id>0，就按 id 来查询妖怪。
如果前面两个条件都不满足，就默认查询 salary > 9000的
要求使用 choose/when/otherwise 标签实现, 传入参数要求使用 Map
使用 mybatis提供的 choose-when-otherwise
-->
<select id="findMonsterByIdOrName_choose" parameterType="map" resultType="Monster">
SELECT * FROM `monster`
<choose>
<when test="name != null and name != ''">
WHERE `name` = #{name}
</when>
<when test="id > 0">
WHERE `id` = #{id}
</when>
<otherwise>
WHERE `salary` > 9000
</otherwise>
</choose>
</select>修改 MonsterMapperTest.java
@Test
public void findMonsterByIdOrName_choose(){
HashMap<String, Object> map = new HashMap<>();
map.put("name", "牛魔王-1");
map.put("id", 5);
List<Monster> monsters = monsterMapper.findMonsterByIdOrName_choose(map);
for (Monster monster : monsters) {
System.out.println("monster-" + monster);
}
if(sqlSession != null){
sqlSession.close();
}
System.out.println("操作成功~~");
}
forEach 标签应用实例
需求：查询 monster_id 为 1, 3, 5 的妖怪。
修改 MonsterMapper.java
//测试 foreach 的标签使用
public List<Monster> findMonsterById_forEach(Map<String, Object> map);修改 MonsterMapper.xml
<!--
配置 public List<Monster> findMonsterById_forEach(Map<String, Object> map);
查询 monster_id 为 1, 3, 5 的妖怪。
入参 map 中会如何传入id值k-v ids-[1,3,5]
-->
<select id="findMonsterById_forEach" parameterType="map" resultType="Monster">
SELECT * FROM monster
<!--
1.where标签
2.再写入相应的处理代码，比如判断 ids 是否为空
3.如果ids不为空，则使用foreach标签进行遍历
4.collection="ids" 对应入参map的key - ids
5.item="id" 在遍历ids集合时，每次取出的值，对应的变量id
6.open="(" 对应的就是 sql语句中的 (1,3,5) 的第一个 (
7.separator="," 遍历出来的多个值的分隔符号
8.close="(" 对应的就是 sql语句中的 (1,3,5) 的最后一个 (
9.#{id} 对应的就是 item="id"
-->
<if test="ids != null and ids != ''">
<where>
id IN
<foreach collection="ids" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</where>
</if>
</select>修改 MonsterMapperTest.java
@Test
public void findMonsterById_forEach(){
HashMap<String, Object> map = new HashMap<>();
map.put("ids", Arrays.asList(1,3,5));
List<Monster> monsters = monsterMapper.findMonsterById_forEach(map);
for (Monster monster : monsters) {
System.out.println("monster-" + monster);
}
if(sqlSession != null){
sqlSession.close();
}
System.out.println("操作成功~~");
}
trim 标签应用实例[使用较少]
trim 可以替换一些关键字。
要求：按名字查询妖怪，如果 sql 语句有 and | or 就替换成 where。
修改 MonsterMapper.java
//trim 标签的使用
public List<Monster> findMonsterByName_Trim(Map<String, Object> map);修改 MonsterMapper.xml
<!--
配置 public List<Monster> findMonsterByName_Trim(Map<String, Object> map);
按名字查询妖怪，如果 sql 语句有 and | or 就替换成 where
如果要实现这个功能，使用where标签就可以[加入where 同时会去掉多余的 and]
<trim prefix="where" prefixOverrides="and|or|lzw">
如果发现子句的开头为 "and"或"or"或"lzw"，就去除
-->
<select id="findMonsterByName_Trim" parameterType="map" resultType="Monster">
select * from `monster`
<trim prefix="where" prefixOverrides="and|or|lzw">
<if test="name != null and name != ''">
lzw `name` = #{name}
</if>
<if test="age != null and age != ''">
and `age` >= #{age}
</if>
</trim>
</select>修改 MonsterMapperTest.java
@Test
public void findMonsterByName_Trim(){
HashMap<String, Object> map = new HashMap<>();
map.put("name", "牛魔王-1");
map.put("age" , 800);
List<Monster> monsters = monsterMapper.findMonsterByName_Trim(map);
for (Monster monster : monsters) {
System.out.println("monster-" + monster);
}
if(sqlSession != null){
sqlSession.close();
}
System.out.println("操作成功~~");
}
set 标签应用实例[重点]
需求：请对指定 id 的妖怪进行修改，如果没有设置新的属性，则保持原来的值。
修改 MonsterMapper.java
//测试 Set 标签
public void updateMonster_set(Map<String, Object> map);修改 MonsterMapper.xml
<!--
配置 public void updateMonster_set(Map<String, Object> map);
需求：请对指定 id 的妖怪进行修改，如果没有设置新的属性，则保持原来的值。
入参需要根据sql语句来配合 map[age-10，email-‘xx@qq.com’]
set标签会处理多余的 ','
-->
<select id="updateMonster_set" parameterType="map">
update `monster`
<set>
<if test="age != null and age != ''">
age = #{age},
</if>
<if test="email != null and email != ''">
email = #{email},
</if>
<if test="name != null and name != ''">
`name` = #{name},
</if>
<if test="birthday != null and birthday != ''">
`birthday` = #{birthday},
</if>
<if test="salary != null and salary != ''">
`salary` = #{salary},
</if>
<if test="gender != null and gender != ''">
`gender` = #{gender},
</if>
</set>
where id =#{id}
</select>修改 MonsterMapperTest.java
@Test
public void updateMonster_set(){
HashMap<String, Object> map = new HashMap<>();
map.put("name", "牛魔王2-1");
//map.put("age" , 800);
map.put("id", 3);
monsterMapper.updateMonster_set(map);
if(sqlSession != null){
sqlSession.commit();
sqlSession.close();
}
System.out.println("操作成功~~");
}