Mybatis学习笔记(八)——动态SQL
动态 SQL 只有几个基本元素,与 JSTL 或 XML 文本处理器相似,十分简单明了,大量的判断都可以在 MyBatis 的映射 XML 文件里配置,以达到许多需要大量代码才能实现的功能。
动态 SQL 大大减少了编写代码的工作量,更体现了 MyBatis 的灵活性、高度可配置性和可维护性。
MyBatis 也可以在注解中配置 SQL,但是由于注解功能受限,且对于复杂的 SQL 语句来说可读性差,所以使用较少。本教程不对它们进行介绍。
MyBatis 的动态 SQL 包括以下几种元素,如下表所示。
1.MyBatis if标签:条件判断
<if test="判断条件"> SQL语句 </if>
当判断条件为 true 时,才会执行所包含的 SQL 语句。
最常见的场景是在 if 语句中包含 where 子句,例如。
<select id="selectAllWebsite" resultMap="myResult"> select id,name,url from website <if test="name != null"> where name like #{name} </if> </select>
以上代表表示根据网站名称去查找相应的网站信息,但是网站名称是一个可填可不填的条件,不填写的时候不作为查询条件。
可多个 if 语句同时使用。以下语句表示为可以按照网站名称(name)或者网址(url)进行模糊查询。如果您不输入名称或网址,则返回所有的网站记录。但是,如果你传递了任意一个参数,它就会返回与给定参数相匹配的记录:
<select id="selectAllWebsite" resultMap="myResult"> select id,name,url from website where 1=1 <if test="name != null"> AND name like #{name} </if> <if test="url!= null"> AND url like #{url} </if> </select>
2.MyBatis choose、when和otherwise标签
<choose> <when test="判断条件1"> SQL语句1 </when > <when test="判断条件2"> SQL语句2 </when > <when test="判断条件3"> SQL语句3 </when > <otherwise> SQL语句4 </otherwise> </choose>
choose 标签按顺序判断其内部 when 标签中的判断条件是否成立,如果有一个成立,则执行相应的 SQL 语句,choose 执行结束;如果都不成立,则执行 otherwise 中的 SQL 语句。这类似于 Java 的 switch 语句,choose 为 switch,when 为 case,otherwise 则为 default。
示例
- 当网站名称不为空时,只用网站名称作为条件进行模糊查询;
- 当网站名称为空,而网址不为空时,则用网址作为条件进行模糊查询;
- 当网站名称和网址都为空时,则要求网站年龄不为空。
<mapper namespace="net.biancheng.mapper.WebsiteMapper"> <select id="selectWebsite" parameterType="net.biancheng.po.Website" resultType="net.biancheng.po.Website"> SELECT id,name,url,age,country FROM website WHERE 1=1 <choose> <when test="name != null and name !=''"> AND name LIKE CONCAT('%',#{name},'%') </when> <when test="url != null and url !=''"> AND url LIKE CONCAT('%',#{url},'%') </when> <otherwise> AND age is not null </otherwise> </choose> </select> </mapper>
3.MyBatis where标签
<where> <if test="判断条件"> AND/OR ... </if> </where>
if 语句中判断条件为 true 时,where 关键字才会加入到组装的 SQL 里面,否则就不加入。where 会检索语句,它会将 where 后的第一个 SQL 条件语句的 AND 或者 OR 关键词去掉。
示例
<select id="selectWebsite" resultType="net.biancheng.po.Website"> select id,name,url from website <where> <if test="name != null"> AND name like #{name} </if> <if test="url!= null"> AND url like #{url} </if> </where> </select>
4.MyBatis set标签
示例
+----+----------------+----------------------------+-----+---------+---------------------+
| id | name | url | age | country | createtime |
+----+----------------+----------------------------+-----+---------+---------------------+
| 1 | 编程帮 | https://www.biancheng.net/ | 10 | CN | 2021-02-23 10:20:40 |
| 2 | C语言中文网 | http://c.biancheng.net/ | 12 | CN | 2021-03-08 11:23:27 |
| 3 | 百度 | https://www.baidu.com/ | 18 | CN | 2021-03-08 11:23:53 |
| 4 | 淘宝 | https://www.taobao.com/ | 17 | CN | 2021-03-10 10:33:54 |
| 5 | Google | https://www.google.com/ | 23 | US | 2021-03-10 10:34:34 |
| 6 | GitHub | https://github.com/ | 13 | US | 2021-03-10 10:34:34 |
| 7 | Stack Overflow | https://stackoverflow.com/ | 16 | US | 2021-03-10 10:34:34 |
| 8 | Yandex | http://www.yandex.ru/ | 11 | RU | 2021-03-10 10:34:34 |
+----+----------------+----------------------------+-----+---------+---------------------+
WebsiteMapper.xml 中代码如下。
<select id="selectWebsite" parameterType="net.biancheng.po.Website" resultType="net.biancheng.po.Website"> SELECT id,name,url,age,country FROM website WHERE age in <foreach item="age" index="index" collection="list" open="(" separator="," close=")"> #{age} </foreach> </select>
WebsiteMapper 类中相应方法如下。
public List<Website> selectWebsite(List<Integer> ageList);
测试代码如下。
public class Test { public static void main(String[] args) throws IOException { // 读取配置文件mybatis-config.xml InputStream config = Resources.getResourceAsStream("mybatis-config.xml"); // 根据配置文件构建 SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(config); // 通过SqlSessionFactory创建SqlSession SqlSession ss = ssf.openSession(); List<Integer> ageList = new ArrayList<Integer>(); ageList.add(10); ageList.add(12); List<Website> siteList = ss.selectList("net.biancheng.mapper.WebsiteMapper.selectWebsite", ageList); for (Website ws : siteList) { System.out.println(ws); } } }
拓展
在使用 foreach 标签时,应提前预估一下 collection 对象的长度。因为大量数据的 in 语句会影响性能,且还有一些数据库会限制执行的 SQL 语句长度。
6.MyBatis bind标签
<select id="selectWebsite" resultType="net.biancheng.po.Website"> <bind name="pattern" value="'%'+_parameter+'%'" /> SELECT id,name,url,age,country FROM website WHERE name like #{pattern} </select>
bind 元素属性如下。
- value:对应传入实体类的某个字段,可以进行字符串拼接等特殊处理。
- name:给对应参数取的别名。
以上代码中的“_parameter”代表传递进来的参数,它和通配符连接后,赋给了 pattern,然后就可以在 select 语句中使用这个变量进行模糊查询,不管是 MySQL 数据库还是 Oracle 数据库都可以使用这样的语句,提高了可移植性。
大部分情况下需要传递多个参数,下面为传递多个参数时 bind 的用法示例。
示例
public List<Website> selectWebsite(Website site);
SQL 映射文件代码如下。
<select id="selectWebsite" resultType="net.biancheng.po.Website"> <bind name="pattern_name" value="'%'+name+'%'" /> <bind name="pattern_url" value="'%'+url+'%'" /> SELECT id,name,url,age,country FROM website WHERE name like #{pattern_name} AND url like #{pattern_url} </select>
测试代码如下。
public class Test { public static void main(String[] args) throws IOException { // 读取配置文件mybatis-config.xml InputStream config = Resources.getResourceAsStream("mybatis-config.xml"); // 根据配置文件构建 SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(config); // 通过SqlSessionFactory创建SqlSession SqlSession ss = ssf.openSession(); Website site = new Website(); site.setname("编程"); site.setUrl("http"); List<Website> siteList = ss.selectList("net.biancheng.mapper.WebsiteMapper.selectWebsite", site); for (Website ws : siteList) { System.out.println(ws); } } }
7.MyBatis trim标签
<trim prefix="前缀" suffix="后缀" prefixOverrides="忽略前缀字符" suffixOverrides="忽略后缀字符">
SQL语句
</trim>
trim 中属性说明如下。
示例
<select id="selectWebsite" resultType="net.biancheng.po.Website"> SELECT id,name,url,age,country FROM website <trim prefix="where" prefixOverrides="and"> <if test="name != null and name !=''"> AND name LIKE CONCAT ('%',#{name},'%') </if> <if test="url!= null"> AND url like concat ('%',#{url},'%') </if> </trim> </select>
WebsiteMapper 类中方法如下。
public List<Website> selectWebsite(Website website);
测试类代码如下。
public class Test { public static void main(String[] args) throws IOException { // 读取配置文件mybatis-config.xml InputStream config = Resources.getResourceAsStream("mybatis-config.xml"); // 根据配置文件构建 SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(config); // 通过SqlSessionFactory创建SqlSession SqlSession ss = ssf.openSession(); Website site = new Website(); site.setname("编程"); site.setUrl("http"); List<Website> siteList = ss.selectList("net.biancheng.mapper.WebsiteMapper.selectWebsite", site); for (Website ws : siteList) { System.out.println(ws); } } }