之前的Blog我们使用了各种SQL语句，然而大多数都是静态的SQL，也就是不会依据业务逻辑来进行拼接的单一逻辑的SQL，其实有很多场景我们也需要使用到动态SQL。

为什么使用MyBatis动态SQL

什么是动态SQL呢？动态SQL指的是根据不同的查询条件 , 生成不同的SQL语句。

为什么使用动态SQL

首先我们需要明确为什么有些场合下需要使用动态SQL而不是静态SQL。例如当我们写一些判断SQL或者循环的SQL时：

SET @username='tml';
SET @age=30;
select * from person where
    (@username is null or username = @username ) AND
    (@age is null or age = @age)

SQL过于复杂，甚至还使用了IS NULL和OR，导致语句完全无法使用索引，而且整个SQL看起来十分臃肿，我认为SQL语句只要执行单纯的细粒度的CRUD，所以如果判断条件都动态的上移，那么整个SQL会简单很多。所以JDBC会将判断逻辑放到代码里动态拼接SQL

var sql = new StringBuilder();
sql.Append("SELECT * FROM person WHERE 1=1 ");
if (userId != null) 
{
    sql.AppendLine("AND username= @username");
}
if (menuId != null)
{
    sql.AppendLine("AND age= @age");
}

MyBatis动态SQL的优势

如果使用过 JDBC ，就能体会到根据不同条件拼接 SQL 语句的痛苦。正因为有了痛苦才有了需求，我们来看一个复杂的例子：

拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号，有时还需要注意单引号和双引号的混合使用。而MyBatis 的强大特性之一便是它的动态 SQL，有了MyBatis的动态SQL，我们就更进一步不用担心这些耗费时间的细节了，代码传递给Mapper参数，Mapper依据逻辑写出动态SQL，运行时解析为确定的SQL语句，相当于Mapper作为一个翻译把我们的逻辑传达给了SQL。常用的一些符号如下：

-------------------------------
  - if  //判断语句
  - choose (when, otherwise)    //判断语句
  - trim (where, set) //判断语句、赋值语句，并且可消除语句中其它预定义字符。
  - foreach   //循环语句
  -------------------------------

MyBatis动态SQL操作

操作还是使用我们之前的Person表：

在personMapper中的返回结果统一用上篇Blog中的PersonBankAccountBySelectMap

<resultMap id="PersonBankAccountBySelectMap" type="com.example.MyBatis.dao.model.Person">
        <!-- id为主键 -->
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <result column="age" property="age"/>
        <result column="phone" property="phone"/>
        <!-- column写错会因为查到数据匹配不上，给默认值, type写错会报错，找不到属性-->
        <result column="email" property="email"/>
        <!-- column是数据库表的列名 , property是对应实体类的属性名 -->
        <result column="hobby" property="interests"/>
        <!--column是一对多的外键 , 写的是一的主键的列名-->
        <collection column="id" property="bankAccounts" javaType="ArrayList" ofType="com.example.MyBatis.dao.model.BankAccount" select="getBankAccountByPersonId"/>
    </resultMap>
    <select id="getBankAccountByPersonId" resultMap="BankAccountResultMap">
        select * from bank_account where person_id = #{id}
    </select>
    <resultMap id="BankAccountResultMap" type="com.example.MyBatis.dao.model.BankAccount">
        <!-- id为主键 -->
        <id column="id" property="id"/>
        <result column="bank_name" property="bankName"/>
        <result column="account_name" property="accountName"/>
        <result column="person_id" property="personId"/>
    </resultMap>

条件分支动态SQL-if

我们来看下if的动态SQL如何编写。

1 PersonDao增加方法

首先我们在PersonDao中增加相应的方法：

List<Person> selectPersonListIf(Map<String,Object> map);

2 personMapper增加动态SQL

然后我们在personMapper中增加动态SQL

<select id="selectPersonListIf" parameterType="map" resultMap="PersonBankAccountBySelectMap">
        select * from person where
        <if test="username != null">
            username = #{username}
        </if>
        <if test="age != null">
            and age = #{age}
        </if>
    </select>

3 测试实现

最后我们测试实现：

@Test
    public void testSelectPersonListIf(){
        //1.获取SqlSession对象
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        //2.执行SQL
        PersonDao personDao = sqlSession.getMapper(PersonDao.class);
        Map<String,Object> map = new HashMap<>();
        map.put("username","tml");
        map.put("age","30");
        List<Person> personList = personDao.selectPersonListIf(map);
        for (Person person : personList) {
            System.out.println(person);
        }
        //关闭sqlSession
        sqlSession.close();
    }

打印结果如下：

这样写我们可以看到，如果 username等于 null，那么查询语句为 select * from person where age=#{age},但是如果title为空呢？那么查询语句为 select * from person where and username=#{username}，这是错误的 SQL 语句，会报错，而where可以解决这个问题。

条件分支动态SQL-where

我们来看下where的动态SQL如何编写。

1 PersonDao增加方法

首先我们在PersonDao中增加相应的方法：

List<Person> selectPersonListWhere(Map<String,Object> map);

2 personMapper增加动态SQL

然后我们在personMapper中增加动态SQL

<select id="selectPersonListWhere" parameterType="map"  resultMap="PersonBankAccountBySelectMap">
        select * from person
        <where>
         <if test="username != null">
            username = #{username}
         </if>
         <if test="age != null">
            and age = #{age}
         </if>
        </where>
    </select>

3 测试实现

最后我们测试实现：

@Test
    public void testPersonListWhere(){
        //1.获取SqlSession对象
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        //2.执行SQL
        PersonDao personDao = sqlSession.getMapper(PersonDao.class);
        Map<String,Object> map = new HashMap<>();
        map.put("username","tml");
        map.put("age","30");
        List<Person> personList = personDao.selectPersonListWhere(map);
        for (Person person : personList) {
            System.out.println(person);
        }
        //关闭sqlSession
        sqlSession.close();
    }

打印结果如下：

这个where标签会知道如果它包含的标签中有返回值的话，它就插入一个where。此外，如果标签返回的内容是以AND 或OR 开头的，则它会剔除掉

条件分支动态SQL-choose

我们来看下choose的动态SQL如何编写。

1 PersonDao增加方法

首先我们在PersonDao中增加相应的方法：

List<Person> selectPersonListChoose(Map<String,Object> map);

2 personMapper增加动态SQL

然后我们在personMapper中增加动态SQL

<select id="selectPersonListChoose" parameterType="map" resultMap="PersonBankAccountBySelectMap">
        select * from person
        <where>
            <choose>
                <when test="username != null">
                    username = #{username}
                </when>
                <when test="age != null">
                    and age = #{age}
                </when>
                <otherwise>
                    and phone = #{phone}
                </otherwise>
            </choose>
        </where>
    </select>

类似于case，如果前面没有符号的条件才继续向下找条件。

3 测试实现

最后我们测试实现：

@Test
    public void testSelectPersonListChoose(){
        //1.获取SqlSession对象
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        //2.执行SQL
        PersonDao personDao = sqlSession.getMapper(PersonDao.class);
        Map<String,Object> map = new HashMap<>();
        map.put("username","tml");
        map.put("age","30");
        map.put("phone","1234555555");
        List<Person> personList = personDao.selectPersonListChoose(map);
        for (Person person : personList) {
            System.out.println(person);
        }
        //关闭sqlSession
        sqlSession.close();
    }

打印结果如下：

赋值动态SQL-set

我们来看下set的动态SQL如何编写。

1 PersonDao增加方法

首先我们在PersonDao中增加相应的方法：

int updatePersonSet(Map<String,Object> map);

2 personMapper增加动态SQL

然后我们在personMapper中增加动态SQL

<update id="updatePersonSet" parameterType="map">
        update person
        <set>
            <if test="email != null">
                email = #{email},
            </if>
            <if test="password != null">
                password = #{password}
            </if>
        </set>
        where id = #{id};
    </update>

如果我们这里password为null，那么语句为：update person set email=#{email}, where id=#{id}么？这样就报错了，答案是否定的，与where相同，如果标签返回的内容是以逗号结尾开头的，则它会剔除掉，sql语句还是会正常执行。

3 测试实现

最后我们测试实现：

@Test
    public void testUpdatePersonSet(){
        //1.获取SqlSession对象
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        //2.执行SQL
        PersonDao personDao = sqlSession.getMapper(PersonDao.class);
        Map<String,Object> map = new HashMap<>();
        map.put("email","155555@qq.com");
        map.put("password","111111");
        map.put("id",2);
        int result = personDao.updatePersonSet(map);
        System.out.println(result);
        //3.提交事务
        sqlSession.commit(); //提交事务,重点!不写的话不会提交到数据库
        //关闭sqlSession
        sqlSession.close();
    }

打印结果如下：

循环语句动态SQL-foreach

我们来看下foreach的动态SQL如何编写。

1 PersonDao增加方法

首先我们在PersonDao中增加相应的方法：

List<Person> selectPersonListForEach(Map<String,Object> map);

2 personMapper增加动态SQL

然后我们在personMapper中增加动态SQL

<select id="selectPersonListForEach" parameterType="map" resultMap="PersonBankAccountBySelectMap">
        select * from person
        <where>
            <!--
            collection:指定输入对象中的集合属性
            item:每次遍历生成的对象
            open:开始遍历时的拼接字符串
            close:结束时拼接的字符串
            separator:遍历对象之间需要拼接的字符串
            select * from person where 1=1 and (id=0 or id=1 or id=2)
          -->
            <foreach collection="ids"  item="id" open="and (" close=")" separator="or">
                id=#{id}
            </foreach>
        </where>
    </select>

3 测试实现

最后我们测试实现：

@Test
    public void testSelectPersonListForEach(){
        //1.获取SqlSession对象
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        //2.执行SQL
        PersonDao personDao = sqlSession.getMapper(PersonDao.class);
        Map<String,Object> map = new HashMap<>();
        List<Integer> ids = new ArrayList<>();
        ids.add(0);
        ids.add(1);
        ids.add(2);
        map.put("ids",ids);
        List<Person> personList = personDao.selectPersonListForEach(map);
        for (Person person : personList) {
            System.out.println(person);
        }
        //关闭sqlSession
        sqlSession.close();
    }

打印结果如下：

SQL语句包含查询

有时候可能某个 sql 语句我们用的特别多，为了增加代码的重用性，简化代码，我们需要将这些代码抽取出来，然后使用时直接调用

1 PersonDao增加方法

首先我们在PersonDao中增加相应的方法：

List<Person> selectPersonListIfSqlIn(Map<String,Object> map);

2 personMapper增加动态SQL

然后我们在personMapper中增加动态SQL

<select id="selectPersonListIfSqlIn" parameterType="map" resultMap="PersonBankAccountBySelectMap">
        select * from person
        <where>
            <include refid="if-username-age"/>
        </where>
    </select>
    <sql id="if-username-age">
        <if test="username != null">
             username = #{username}
        </if>
        <if test="age != null">
             and age = #{age}
        </if>
    </sql>

3 测试实现

最后我们测试实现：

@Test
    public void testSelectPersonListIfSqlIn(){
        //1.获取SqlSession对象
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        //2.执行SQL
        PersonDao personDao = sqlSession.getMapper(PersonDao.class);
        Map<String,Object> map = new HashMap<>();
        map.put("username","tml");
        map.put("age","30");
        List<Person> personList = personDao.selectPersonListIfSqlIn(map);
        for (Person person : personList) {
            System.out.println(person);
        }
        //关闭sqlSession
        sqlSession.close();
    }

打印结果如下：

可以看到与where语句完整写法结果一般无二。

总结一下

Mybatis的动态SQL就像一个中介一样，把分支判断逻辑都挡在了SQL脚本执行的门外，保证了SQL语句语法的纯净，下游的JDBC执行时是纯净的，同时动态SQL通过OGNL语言描述对程序员来说也更直观友好，不用花大量的精力在判断语句的语法合理性上，例如括号是否开闭正确，逗号以及and等拼接是否正确，诸如此类，就像一个最佳实践一样，当然我们认为到达DAO对象这层的操作本就应该十分简单，但如果无法避免复杂性，动态SQL相当于第二道门神，保证了底层SQL的单一纯净。

【MyBatis学习笔记七】MyBatis动态SQL基本操作

为什么使用MyBatis动态SQL

为什么使用动态SQL

MyBatis动态SQL的优势

MyBatis动态SQL操作

条件分支动态SQL-if

1 PersonDao增加方法

2 personMapper增加动态SQL

3 测试实现

条件分支动态SQL-where

1 PersonDao增加方法

2 personMapper增加动态SQL

3 测试实现

条件分支动态SQL-choose

1 PersonDao增加方法

2 personMapper增加动态SQL

3 测试实现

赋值动态SQL-set

1 PersonDao增加方法

2 personMapper增加动态SQL

3 测试实现

循环语句动态SQL-foreach

1 PersonDao增加方法

2 personMapper增加动态SQL

3 测试实现

SQL语句包含查询

1 PersonDao增加方法

2 personMapper增加动态SQL

3 测试实现

总结一下

热门文章

最新文章

相关课程

相关电子书

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

【MyBatis学习笔记 七】MyBatis动态SQL基本操作

为什么使用MyBatis动态SQL

为什么使用动态SQL

MyBatis动态SQL的优势

MyBatis动态SQL操作

条件分支动态SQL-if

1 PersonDao增加方法

2 personMapper增加动态SQL

3 测试实现

条件分支动态SQL-where

1 PersonDao增加方法

2 personMapper增加动态SQL

3 测试实现

条件分支动态SQL-choose

1 PersonDao增加方法

2 personMapper增加动态SQL

3 测试实现

赋值动态SQL-set

1 PersonDao增加方法

2 personMapper增加动态SQL

3 测试实现

循环语句动态SQL-foreach

1 PersonDao增加方法

2 personMapper增加动态SQL

3 测试实现

SQL语句包含查询

1 PersonDao增加方法

2 personMapper增加动态SQL

3 测试实现

总结一下

热门文章

最新文章

相关课程

相关电子书

【MyBatis学习笔记七】MyBatis动态SQL基本操作