废什么话，一句就够了！！！MyBtais动态sql和特殊符号处理（案例集锦：不定条件查询+模糊查询+批量查询+批量删除....）

MyBatis动态sql

MyBatis框架有一个强大的特性就是它的动态sql能力。我们在使用JDBC或其他相似框架时，经常会碰到串联sql字符串，但这是一件十分痛苦的事情，总是要去考虑关键字后面跟什么内容、不能忘记空格、在列表的最后要省略逗号等等。下面我用一个条件查询的案例让大家知道我们为什么需要动态sql。

案例准备

1.新建数据库表t_user

CREATE TABLE `t_user` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT'用户id',
  `name` VARCHAR(10) DEFAULT NULL COMMENT'用户姓名',
  `age` INT DEFAULT NULL COMMENT'用户年龄',
  `birthday` DATE DEFAULT NULL COMMENT'用户生日',
  PRIMARY KEY (`id`)
)

2.新建类User.java

package com.cwd.mybatis.bean;
import org.apache.ibatis.type.Alias;
import java.util.Date;

@Alias("User")
public class User {
    private Integer id;
    private String name;//姓名
    private Integer age;//年龄
    private Date birthday;//生日

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", birthday=" + birthday +
                '}';
    }
}

3.新建接口UserDao.java

package com.cwd.mybatis.dao;
import com.cwd.mybatis.bean.User;
public interface UserDao {

}

4.新建UserMapper.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">

<!--namespace:接口存放路径 类路径-->
<mapper namespace="com.cwd.mybatis.dao.UserDao">

</mapper>

为什么需要动态sql？

如上图，当我们需要按照姓名/年龄/生日这些数据进行查询时，客户端会将数据发送到处理层，处理层将数据封装到User类的对象中传给数据持久层，我们需要使用sql语句操作数据库进行查询，这时的sql语句就会出现问题，比如where后的条件，如果传入的数据为null，岂不是where后面没有数据，这时就会出错。

我们以案例来说话

接口UserDao.java中的方法为：

//为什么需要动态sql    user中封装的是查询条件
List<User> findUserList(User user);

UserMapper.xml中的配置为

<select id="findUserList" parameterType="User" resultType="User">
    select id,name,age,birthday from t_user
    where true
        <if test="name != null">
        	and name = #{name}
        </if>
        <if test="age != null">
        	and age = #{age}
        </if>
        <if test="birthday != null">
        	and birthday = #{birthday}
        </if>
</select>

我们可以想象，如果where后面没有true，那么会出现什么样的问题？where and …或者直接就是where后面什么都没有，这显然不符合sql语句的语法。并且这里的if标签本就是MyBatis中用于实现动态sql的主要元素之一，我们可以想象如果没有这个if标签，这条查询sql该如何写？这就是我们动态sql存在的意义。

动态sql的使用

MyBatis中用于实现动态sql的元素主要有：

if | where | trim | set | choose（when，otherwise） | foreach

if元素（标签）

if元素可以对传入的条件进行判断，还是上面的案例，我们依照上面案例的代码进行测试。

@Test
    public void test1() {
        try {
            Reader reader = Resources.getResourceAsReader("MyBatisConfig.xml");
            SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
            SqlSession sqlSession = sessionFactory.openSession();
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            //处理,假设从客户端传递过来一个查询条件(姓名/年龄/生日)封装到User中
            User user = new User();
            user.setAge(20);
            List<User> list = userDao.findUserList(user);
            System.out.println(list);
            //提交事务
            sqlSession.commit();
            sqlSession.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

我们可以看到，当查询的数据不确定时，使用if元素的动态sql功能可以使我们的查询更加准确。

where标签

对于查询条件个数不确定的情况，我们还可以使用where标签。where标签会进行判断，如果它包含的标签中有返回值的话，它就插入一个where。另外，如果标签返回的内容是以AND或OR开头，它就会剔除掉AND或OR。使用方法如下：

<select id="findUserList" parameterType="User" resultType="User">
    select id,name,age,birthday from t_user
    <!--当下面if标签有返回值时,where就会出现-->
    <where>
        <!--第一个if中有返回值,第二个if返回的值就会带AND;如果第一个没有返回值,第二个就会剔除AND-->
        <if test="name != null">
        	name = #{name}
        </if>
        <if test="age != null">
        	AND age = #{age}
        </if>
        <if test="birthday != null">
        	and birthday = #{birthday}
        </if>
    </where>
</select>

trim标签

where标签也可以使用trim标签表示，当where后紧随AND或OR的时候，就去除AND或着OR。trim标签中有两个属性，prefix表示前缀，prefixOverrides表示覆盖首部指定的内容。

<select id="findUserList" parameterType="User" resultType="User">
    select id,name,age,birthday from t_user
    <trim prefix="where" prefixOverrides="and">
        <if test="name != null">
        	name = #{name}
        </if>
        <if test="age != null">
        	AND age = #{age}
        </if>
        <if test="birthday != null">
        	and birthday = #{birthday}
        </if>
    </trim>
</select>

小案例–模糊查询

当我们输入一个姓氏或者一个字时，查询与之相匹配的数据时，我们通常使用sql语句中的like关键字加%进行模糊查询。当我们在MyBatis中进行模糊查询时，我们不能使用#{}进行查询，或者说不能单独使用，对于这个问题，我们有三种解决方式。

1.使用’%${}’

<select id="findUserList" parameterType="User" resultType="User">
    select id,name,age,birthday from t_user
        <!--当下面if标签有返回值时,where就会出现-->
        <trim prefix="where" prefixOverrides="and">
            <if test="name != null">
            	name like '%${name}'
            </if>
            <if test="age != null">
            	AND age = #{age}
            </if>
            <if test="birthday != null">
            	and birthday = #{birthday}
            </if>
        </trim>
</select>

2.使用concat(’%’,’${}’)拼接字符串

<select id="findUserList" parameterType="User" resultType="User">
    select id,name,age,birthday from t_user
        <!--当下面if标签有返回值时,where就会出现-->
        <trim prefix="where" prefixOverrides="and">
            <if test="name != null">
            	name like concat('%','${name}')
            </if>
            <if test="age != null">
            	AND age = #{age}
            </if>
            <if test="birthday != null">
            	and birthday = #{birthday}
            </if>
        </trim>
</select>

3.在处理层对客户端输入的数据直接加%，一起传给sql语句当参数。

<select id="findUserList" parameterType="User" resultType="User">
    select id,name,age,birthday from t_user
    	<!--此时传入的参数为%name-->
        <trim prefix="where" prefixOverrides="and">
            <if test="name != null">
            	name like #{name}
            </if>
            <if test="age != null">
            	AND age = #{age}
            </if>
            <if test="birthday != null">
            	and birthday = #{birthday}
            </if>
        </trim>
</select>

choose标签

choose标签可以代替if标签进行判断，但是只能进行二选一的判断，一般用于条件不为空时的固定选项。

1.接口UserDao.java中的方法为：

//当查询条件为空时，查询所有的数据
List<User> findUserListNotNull(@Param("name") String name);

2.UserMapper.xml中的配置为：

<select id="findUserListNotNull" parameterType="string" resultType="User">
    select id,name,age,birthday from t_user
        <trim prefix="where" prefixOverrides="and">
            <choose>
                <when test="name!=null">
                	and name like '%${name}'
                </when>
                <otherwise>
                	true
                </otherwise>
            </choose>
        </trim>
</select>

3.测试：

	@Test
    public void test2() {
        try {
            Reader reader = Resources.getResourceAsReader("MyBatisConfig.xml");
            SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
            SqlSession sqlSession = sessionFactory.openSession();
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            //处理
            String name = "";
            List<User> list = userDao.findUserListNotNull(name);
            System.out.println(list);
            //提交事务
            sqlSession.commit();
            sqlSession.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

set标签

set标签用于去除最后一个逗号，比如当用户需要从客户端修改数据时，只需要修改一部分数据，另一部分不变时，我们使用update进行数据库修改时，就无法准确确定要修改的参数，这时就需要使用set标签来做update的动态SQL，完成确定修改哪些参数的步骤。我们以下面的修改案例为例：

1.接口UserDao.java中的方法为：

//修改User,可以修改一条数据的任意一列
void updateUser(User user);

2.UserMapper.xml中的配置为：

<!--
	修改User,当要修改的列不确定时,使用set标签可以去除最后一个逗号
-->
<update id="updateUser" parameterType="User">
    UPDATE t_user
        <set>
            <if test="name != null">
            	name  = #{name},
            </if>
            <if test="age != null">
            	age = #{age},
            </if>
            <if test="birthday != null">
            	birthday = #{birthday},
            </if>
        </set>
    where id = #{id}
</update>

3.测试：

	/**
     * 测试修改update,使用set
     */
    @Test
    public void test3() {
        try {
            Reader reader = Resources.getResourceAsReader("MyBatisConfig.xml");
            SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
            SqlSession sqlSession = sessionFactory.openSession();
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            //处理,假设从客户端传递过来要修改的参数(姓名/年龄/生日+id)封装到User中
            User user = new User();
            user.setId(5);
            user.setName("vim");
            userDao.updateUser(user);
            //提交事务
            sqlSession.commit();
            sqlSession.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

小案例–trim实现

trim标签中除了有两个属性，prefix表示前缀，prefixOverrides表示覆盖首部指定的内容。还有两个属性即suffixOverrides表示覆盖尾部指定的内容，suffix表示尾部。

UserMapper.xml中的配置为：

<update id="updateUser" parameterType="User">
    UPDATE t_user
        <trim prefix="set" suffixOverrides=",">
            <if test="name != null">
            	name  = #{name},
            </if>
            <if test="age != null">
            	age = #{age},
            </if>
            <if test="birthday != null">
            	birthday = #{birthday},
            </if>
        </trim>
    where id = #{id}
</update>

foreach标签

foreach标签主要在in(a,b)（判断某字段的值是否属于in列表中的某一项条件）中使用，它可以在 SQL 语句中进行迭代一个集合。多用于delete语句中的批量删除，foreach 标签的属性主要有 item，index，collection，open，separator，close。

各个属性的功能分别为：item 表示集合中每一个元素进行迭代时的别名；index 指定一个名字，用于表示在迭代过程中每次迭代到的位置；open 表示该语句以什么开始； separator 表示在每次进行迭代之间以什么符号作为分隔符；close 表示以什 么结束。

在使用 foreach 的时候最关键的也是最容易出错的就是 collection 属性，该属性是必须指定的，但是在不同情况下，该属性的值是不一样的。如果传入的是单参数且类型为List时，collection属性值为list，如果传入的参数类型为一个array数组时，collection属性值为array。

我们以一个批量查询和删除的案例为大家展示：

1.接口UserDao.java中的方法为：

//删除User
void deleteUser(List<Integer> list);

//迭代查询user信息
List<User> findUser(List<Integer> list);

2.UserMapper.xml中的配置为：

	<!--
        批量删除User
        对传递过来的数组参数进行迭代
    -->
    <delete id="deleteUser">
        DELETE FROM t_user WHERE id in
        <foreach collection="list" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <!--按照学号迭代查询-->
    <select id="findUser" resultType="User">
        SELECT * FROM t_user WHERE id in
        <foreach collection="list" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>

3.测试：

    /**
     * 删除User，一次删除多条记录，循环迭代参数查询
     */
    @Test
    public void test4() {
        try {
            Reader reader = Resources.getResourceAsReader("MyBatisConfig.xml");
            SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
            SqlSession sqlSession = sessionFactory.openSession();
            UserDao userDao = sqlSession.getMapper(UserDao.class);
            //处理,假设从客户端传递过来要删除的多个参数为一个集合
            List<Integer> list = new ArrayList<>();
            list.add(6);
            list.add(7);
            list.add(8);
            //查询
            System.out.println("现在id=6,7,8的数据为"+userDao.findUser(list));
            System.out.println("---------------");
            //删除
            userDao.deleteUser(list);
            //删除后
            System.out.println("---------------");
            System.out.println("删除后id=6,7,8的数据为"+userDao.findUser(list));
            //提交事务
            sqlSession.commit();
            sqlSession.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

特殊符号处理

在MyBtais中的xml文件中，存在一些特殊符号，比如：<、>、"、&、<> 等，正常书写MyBatis会报错，所以需要对这些特殊符号进行转义。具体转义如下所示：

<	&lt;
>	&gt;
"	&quot;
'	&apos;
&	&amp;

除了转义字符外，我们还可以使用<![CDATA[ 特殊符号 ]]>来包裹特殊字符。

比如下面的xml文件语句：

<!--
    xml文件中的特殊符号转义
    比如这条查询语句SELECT * FROM t_user WHERE id < #{id}
    这里使用<号就会报错
    解决方法:
    第一种:转义字符 如: < &lt;
    第二种:使用<![CDATA[ 特殊符号 ]]>进行包裹
-->
<select id="findUser" resultType="User">
	SELECT * FROM t_user WHERE id <![CDATA[ < ]]> #{id}
</select>