【Spring集成MyBatis】动态sql

文章目录

1. 什么是动态sql
2. 动态sql之`<if>`
3. 动态sql之`<where>`
4. 动态sql之`<foreach>`
5. sql片段抽取

此篇的代码基于【Spring集成MyBatis】MyBatis的Dao层实现（基于配置，非注解开发）续写

1. 什么是动态sql

MyBatis映射文件中的SQL都比较简单，有时当业务逻辑复杂时，SQL是动态变化的，此时在前面学的简单SQL就无法满足需求了。
具体地说，假如我的数据库中有id,username,password三个字段，假如我想查询username=xxx的所有用户信息时候，查询语句是：
select * from user where username=xxx，
当我想查询username=xxx且password=xxx的所有用户信息时，查询语句是：
select * from user where username=xxx and password=xxx
这两者的共通之处就是，username和password其实都是User里面的的一个属性，假如我的输入以User为条件，而不是以string为条件，让SQL语句自动去判断我要根据什么查询（有username根据username查询，有password根据password查询，有username和password时根据二者共同查询）
如果我写的是select * from user where id=#{id} and username=#{username} and password=#{password}，此时sql语句等效为图中红字部分：
在这里插入图片描述

但是这不是我们理想的效果，我们想的是，当password=""时，语句自动变为：
select * from user where id=#{id} and username=#{username}
这时候，就无法查询出结果，如果我们想要查询出结果，就需要使用动态sql去实现

2. 动态sql之`<if>`

如果有if语法，我们就可以很好地拼接SQL语句，假如id != 0，就写 id=#{id}，假如username=""，就写username=#{username}，依此类推

<?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.example.demo.dao.UserMapper">

    <select id="findByCondition" parameterType="user" resultType="user">
        select * from user where 1=1
        <if test="id!=0">
            and id=#{id}
        </if>
        <if test="username!=null">
            and username=#{username}
        </if>
        <if test="password!=password">
            and password=#{password}
        </if>
    </select>

</mapper>

<if>标签里的test写的就是判断，再次运行测试代码，仍能够查询出结果：

package com.example.demo.service;

import com.example.demo.dao.UserMapper;
import com.example.demo.domain.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class ServiceDemo {

    public static void main(String[] args) throws IOException {
        InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();

        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        // 模拟条件user
        User condition = new User();
        condition.setId(0);
        condition.setUsername("aaa");

        List<User> userList = userMapper.findByCondition(condition);

        System.out.println(userList);

    }
}

结果：
在这里插入图片描述

3. 动态sql之`<where>`

上面为了保证在无条件的时候不报错、也能够查询，我们还多写了一个where 1=1
为了使得代码更优雅，即有需要写的where时写上where，无需where时不写，还可以修改如下：

<?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.example.demo.dao.UserMapper">

    <select id="findByCondition" parameterType="user" resultType="user">
        select * from user where 1=1
        <where>
            <if test="id!=0">
                and id=#{id}
            </if>
            <if test="username!=null">
                and username=#{username}
            </if>
            <if test="password!=password">
                and password=#{password}
            </if>
        </where>
    </select>

</mapper>

4. 动态sql之`<foreach>`

假如我们想查询id=1或者id=2或者id=3…的：
select * from user where id=1 or id=2 or id=3
select * from user where id in(1, 2, 3)
这时候我们可能就需要用到循环
在XML文件中：

<select id="findByIds" parameterType="list" resultType="user">
    select * from user
    <where>
        <foreach collection="list" open="id in(" close=")" item="id" separator=",">
            #{id}
        </foreach>
    </where>
</select>

其中，foreach中的：
collection指定遍历的类型，如果是列表就写list，如果是数组就写array
open写的是开始的内容
close写的是结束的内容
item里边是循环中当前的元素
seperator写的是分隔符
在接口里需要添加上该方法：

public List<User> findByIds(List<Integer> ids);

在测试类中：

List<Integer> ids = new ArrayList<Integer>();
ids.add(0);
ids.add(1);
List<User> userList = userMapper.findByIds(ids);

这样结果为id=0和id=1的数据：
在这里插入图片描述

5. sql片段抽取

Sql中可以将重复的sql提取出来，使用时通过include引用即可，最终达到Sql重用的目的
例如，我们原本有以下文件：

<?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.example.demo.dao.UserMapper">

    <select id="findByCondition" parameterType="user" resultType="user">
        select * from user
        <where>
            <if test="id!=0">
                and id=#{id}
            </if>
            <if test="username!=null">
                and username=#{username}
            </if>
            <if test="password!=password">
                and password=#{password}
            </if>
        </where>
    </select>

    <select id="findByIds" parameterType="list" resultType="user">
        select * from user
        <where>
            <foreach collection="list" open="id in(" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </where>
    </select>

</mapper>

其中，两个语句都包含select * from user
那么，我们就使用以下语法抽取sql片段简化编写（sql标签）：

<sql id="selectUser">select * from user</sql>

然后使用inclue标签进行引用，refid中写对应sql片段的id：

<include refid="selectUser"></include>

所以以上代码可改写为：

<?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.example.demo.dao.UserMapper">

    <sql id="selectUser">select * from user</sql>

    <select id="findByCondition" parameterType="user" resultType="user">
        <include refid="selectUser"></include>
        <where>
            <if test="id!=0">
                and id=#{id}
            </if>
            <if test="username!=null">
                and username=#{username}
            </if>
            <if test="password!=password">
                and password=#{password}
            </if>
        </where>
    </select>

    <select id="findByIds" parameterType="list" resultType="user">
        <include refid="selectUser"></include>
        <where>
            <foreach collection="list" open="id in(" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </where>
    </select>

</mapper>