【Java框架】Mybatis教程（三）——动态SQL

动态SQL

• MyBatis中的动态SQL是一种在SQL语句中根据条件动态生成不同部分的技术。它允许我们根据不同的条件来构建不同的SQL语句，从而实现更灵活的查询和更新操作。
• 在MyBatis中，我们可以使用动态SQL来处理各种情况，例如根据不同的条件拼接WHERE子句、根据条件选择不同的列、根据条件判断是否包含某个元素等等。
• MyBatis提供了一些标签和函数来实现动态SQL，其中最常用的是if、choose、when、otherwise和foreach标签。通过使用这些标签，我们可以根据条件来动态生成SQL语句的不同部分。

作用

1. 生成SQL语句具有灵活性：动态SQL允许根据不同的条件来生成不同的SQL语句，从而实现动态查询和更新操作。这样我们可以根据项目框架需求灵活地构造所需的SQL语句，避免了不需要硬编码的情况出现。

2. 使SQL语句清晰易懂（可读性）：使用动态SQL可以使SQL语句更加清晰易读。通过使用内置携带的函数与标签，根据条件组织SQL语句的不同部分，从而避免大量的SQL嵌套与重复。它使SQL语句更加直观，更有利于开发人员理解及维护。

3. 具有较强的扩展性：动态SQL提供了一种扩展SQL语句的方式，允许我们根据需求加、修改或删除查询条件。这种扩展性使得我们能够轻松地改变和优化SQL语句，而不需要修改大量的固定SQL代码。这样大大减少了开发使用的时间，提高了开发效率及后期的维护效率。

4. 性能优化：通过使用动态SQL，我们可以根据不同的条件动态生成SQL语句，从而避免不必要的查询或更新操作。例如，我们可以根据用户的选择动态生成查询条件， 只查询真正需要的数据，减少了不必要的数据库操作，提高了系统性能。

MyBatis的9种动态SQL标签

动态SQL的常用函数

动态SQL应用场景

动态SQL的执行原理

使用OGNL从SQL参数对象中计算表达式的值，根据表达式的值动态拼接SQL，以此来完成动态SQL的功能。

OGNL：表达式注入

• OGNL是Object Graphic Navigation Language(对象图导航语言)的缩写，一个开源项目。Struts框架使用OGNL作为默认的表达式语言。
• #{ }： 是指上下文(环境)对象

OGNL三要素/三个参数

表达式（Expression）

• 表达式是整个OGNL的核心，所有的OGNL操作都是针对表达式的解析后进行的。
• 表达式会规定此次OGNL操作到底要干什么。因此，表达式其实是一个带有语法含义的字符串，这个字符串将规定操作的类型和操作的内容。
• OGNL支持大量的表达式语法，不仅支持“链式”描述对象访问路径，还支持在表达式中进行简单的计算，甚至还能够支持复杂的Lambda表达式等。我们可以在接下来的章节中看到各种各样不同的OGNL表达式。

Root对象（Root Object）：

• OGNL的Root对象可以理解为OGNL的操作对象。
• 当OGNL表达式规定了“干什么”以后，我们还需要指定对谁干。OGNL的Root对象实际上是一个Java对象，是所有OGNL操作的实际载体。
• 这就意味着，如果我们有一个OGNL的表达式，那么我们实际上需要针对Root对象去进行OGNL表达式的计算并返回结果。

上下文环境（Context）：

• 有了表达式和Root对象，我们已经可以使用OGNL的基本功能。
• 例如，根据表达式针对OGNL中的Root对象进行“取值”或者“写值”操作。不过，事实上，在OGNL的内部，所有的操作都会在一个特定的数据环境中运行，这个数据环境就是OGNL的上下文环境（Context）。说得再明白一些，就是这个上下文环境（Context）将规定OGNL的操作在哪里干。
• OGNL的上下文环境是一个Map结构，称之为OgnlContext。之前我们所提到的Root对象（Root Object），事实上也会被添加到上下文环境中去，并且将被作为一个特殊的变量进行处理。

OGNL 表达式获取属性举例

对象person:{id:10,age:18,name:小明};   若上下文(环境)的对象是person，通过#{ } 可以直接获取到对象的属性值  
#{id} 相当于 perosn.getId()
#{age} 相当于 person.getAge()
#{name} 相当于 person.getName()
1
2
3
4

关于OGNL 的上下文对象类型【获取属性值】

1. javaBean对象，例如上面的person，则 OGNL 表达式格式必须为 #{属性名}
2. map 对象，OGNL 表达式格式为 #{key}
3. 简单类型对象（基本类型、String类型），OGNL 表达式格式为 #{随便写}，不过一般写得见名知意，增加代码的阅读性

在MyBatis中，OGNL 表达式 #{} 和 EL 表达式 ${} 的异同

#{ }

#{} 的实际作用：现在转成?的占位符，然后再把值设置进去

<!-- 映射文件 ---> 
<select id="login" resultType="Client">
	select id, username, password from client where username = #{username} and password = #{password} ;
 </select>
 
 <!-- #{} 的实际作用：现在转成?的占位符，然后再把值设置进去【假设外界传入的值username="小明"，password="1"】 -->
select id, username, password from client where username = ? and password = ?;
PreparedStatement.setString(1, "小明");
PreparedStatement.setString(2,"1");
1
2
3
4
5
6
7
8
9

${ }

${} 的实际作用：直接把值设置进去。(可能会出现sql注入安全问题，比较不安全)

<!-- 映射文件 --->
<select id="login" resultType="Client">
	select id, username, password from client where username = ${username} and password = ${password} ;
 </select>
 
 <!-- ${} 的实际作用：直接把值设置进去【假设外界传入的值username="小明"，password="1"】 -->
select id, username, password from client where username = "小明" and password = "1";
1
2
3
4
5
6
7

动态SQL使用示例

1.if元素

需求：获取用户列表，但是可能会根据用户名称、用户角色查询

代码实现

mapper

    List<User> getUserList(@Param("userName")String userName,
                           @Param("userRole")int userRole);
1
2

xml

    <select id="getUserList" resultType="User">
        SELECT * FROM SMBMS_USER
        WHERE 1=1
        <if test='userName!=null and userName!=""'>
            AND USERCODE LIKE CONCAT('%',#{userName},'%')
        </if>
        <if test="userRole!=0">
            AND USERROLE = #{userRole}
        </if>
    </select>
1
2
3
4
5
6
7
8
9
10

注意！！！

• <if test=‘’></if>这里一定是单引号在外面，双引号在里面，一般正常情况下不会出现问题，但是如果遇到特殊类型时(比如比较多见的date类型)会导致类型不匹配而报错！！！
• 原理：mybatis是用OGNL表达式来解析的，在OGNL的表达式中，’1’会被解析成字符，java是强类型的，char 和 一个string 会导致不等，所以if标签中的sql不会被解析。

2.where元素

• 简化SQL语句中where条件判断
• 智能处理and和or

上个案例的mapper.xml实现

where标签会去掉第一个成立的if条件前面的and或者是or

    <select id="getUserList" resultType="User">
        SELECT * FROM SMBMS_USER
        <where>
            <if test='userName!=null and userName!=""'>
                AND USERCODE LIKE CONCAT('%',#{userName},'%')
            </if>
            <if test="userRole!=0">
                AND USERROLE = #{userRole}
            </if>
        </where>
    </select>
1
2
3
4
5
6
7
8
9
10
11

3.set元素

需求：更新数据时，若某个参数为null，则不需要更新，保持数据库原值

代码实现

mapper

    int updateUserById(User user);
1

xml

	<update id="updateUserById" parameterType="User">
        UPDATE SMBMS_USER
        <set>
            <if test='userName!=null and userName!=""'>
                userName = #{userName},
            </if>
            <if test='userPassword!=null and userPassword!=""'>
                userPassword=#{userPassword},
            </if>
            <if test='phone !=null and phone !=""'>
                phone=#{phone},
            </if>
            <if test='address!=null and address!=""'>
                address=#{address},
            </if>
            <if test='userRole!=0'>
                userRole=#{userRole},
            </if>
            <if test='birthday!=null'>
                birthday=#{birthday}
            </if>
        </set>
        WHERE ID=#{id}
    </update>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

4.trim元素

• 更灵活地去除多余关键字
• 替代where和set

上个案例的mapper.xml实现

    <update id="updateUserById" parameterType="User">
        UPDATE SMBMS_USER
        <trim prefix="set" suffixOverrides="," suffix="WHERE ID=#{id}">
            <if test='userName!=null and userName!=""'>
                userName = #{userName},
            </if>
            <if test='userPassword!=null and userPassword!=""'>
                userPassword=#{userPassword},
            </if>
            <if test='phone !=null and phone !=""'>
                phone=#{phone},
            </if>
            <if test='address!=null and address!=""'>
                address=#{address},
            </if>
            <if test='userRole!=0'>
                userRole=#{userRole},
            </if>
            <if test='birthday!=null'>
                birthday=#{birthday},
            </if>
        </trim>
    </update>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

5.foreach元素

迭代一个集合，批量查询、批量删除、批量添加等
批量查询上一章已经讲过了，可以看一下

5.1.批量查询

批量查询相当于SQL中的select xx from table where colum in (v1,v2,v3)

5.1.1数组入参

要求：foreach 标签中的collection属性值等于array
mapper

    List<User> getUserListByids(int[] ids);
1

xml

    <select id="getUserListByids" resultType="User" parameterType="int">
        SELECT * FROM SMBMS_USER WHERE ID IN
        <foreach collection="array" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>
1
2
3
4
5
6

5.1.2List入参

要求：foreach 标签中的collection属性值等于list

    List<User> getUserListByids(List<Integer> idList);
1

xml

    <select id="getUserListByids" resultType="User" parameterType="int">
        SELECT * FROM SMBMS_USER WHERE ID IN
        <foreach collection="list" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>
1
2
3
4
5
6

5.1.3Map中有list入参

要求：foreach 标签中的collection属性值等于Map中List集合的key
mapper

    List<User> getUserListByMap(Map<String,Object> paramMap);
1

xml

    <select id="getUserListByMap" parameterType="map" resultType="User">
        SELECT * FROM SMBMS_USER WHERE GENDER = #{gender}
        AND USERROLE IN
        <foreach collection="idList" item="userRole" open="(" separator="," close=")">
            #{userRole}
        </foreach>
    </select>
1
2
3
4
5
6
7

测试

    @Test
    public void getUserListByMap(){
        SqlSession session = factory.openSession();
        UserMapper userMapper = session.getMapper(UserMapper.class);
        List<Integer> roleList = new ArrayList<Integer>();
        Collections.addAll(roleList,2,4,6);
        Map<String,Object> paramMap = new HashMap<String, Object>();
        paramMap.put("idList",roleList);
        paramMap.put("gender",1);
        List<User> userList = userMapper.getUserListByMap(paramMap);
        System.out.println(userList);
        session.close();
    }
1
2
3
4
5
6
7
8
9
10
11
12
13

5.2 批量添加

mapper

int insertUserBatch(List<User> userList);
1

xml

    <insert id="insertUserBatch" parameterType="User">
        insert into smbms_user (userCode,UserName,UserPassword,birthday,userRole)
        values
        <foreach collection="list" item="user" separator=",">
            (#{user.userCode},#{user.userName},#{user.userPassword},#{user.birthday},#{user.userRole})
        </foreach>
    </insert>
1
2
3
4
5
6
7

测试

    @Test
    public void insertUserBatch(){
        SqlSession sqlSession = MybatisUtil.createSqlSession();
        userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = new ArrayList<>();
        User user1 = new User("A","aaa","123",new Date(),3);
        User user2 = new User("B","bbb","123",new Date(),3);
        User user3 = new User("C","ccc","123",new Date(),3);
        User user4 = new User("D","ddd","123",new Date(),3);
        Collections.addAll(userList,user1,user2,user3,user4);
        int line = userMapper.insertUserBatch(userList);
        System.out.println(line);
        //sqlSession.commit();
        MybatisUtil.close(sqlSession);
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

6.choose(when、otherwise)

• 相当于Java中switch语句
• 当when有条件满足的时候，就跳出choose
  

需求：根据不同标记修改不同字段

flag1修改姓名；flag2修改密码，flag==3修改手机号

代码实现

mapper

int updateUserByFlag(String flag);
1

xml
没有传完整参数，直接在sql中写死了，其实可以根据传参修改对应的值

    <update id="updateUserByFlag" parameterType="string">
        UPDATE SMBMS_USER SET address='金水区',
        <choose>
            <when test='flag=="1"'>userName='dubbo'</when>
            <when test='flag=="2"'>userPassword='123456'</when>
            <when test='flag=="3"'>phone='13888888888'</when>
            <otherwise>gender=2</otherwise>
        </choose>
        where id=60
    </update>
1
2
3
4
5
6
7
8
9
10

7.sql，include元素

在开发过程中会遇到许多相同的SQL，以查询功能为例，各功能要查询的参数都是一样，但是筛选条件不同，那么我们便可以将相同的部分抽取出来，用得时候直接引用即可，不用再写重复代码。

代码示例

公共部分SQL

    <sql id="all">
        select * from smbms_user 
    </sql>
1
2
3

分页查询列表

    <select id="getUserByUserNameAndUserRole" resultType="User">
        <include refid="all"></include>
        <where>
            <if test="userRole!=0">
                and userRole=#{userRole}
            </if>
            <if test="userName!=null and userName!=''">
                and userName like concat('%',#{userName},'%')
                <!--and userName like '%${userName}%'-->
            </if>
        </where>
        limit #{start},#{pageSize}
    </select>
1
2
3
4
5
6
7
8
9
10
11
12
13

查询详情

    <select id="getUserById" resultType="User">
        <include refid="all"></include>
        where id=#{id}
    </select>
1
2
3
4

8.bind元素

bind 标签可以使用 OGNL 表达式创建一个变量井将其绑定到上下文中

语法

<bind name = “需要绑定的变量” value = “绑定的最终值” />

• name：为上下文的变量，如SQL中存在#{userName}变量，这里name就填写userName
• value：绑定变量的最终值，如name=“userName”，那么value就是#{userName}的最终实际值，可以对传递的参数进行处理

案例背景

• 在进行模糊查询时，如果使用“${}”拼接字符串，则无法防止 SQL 注入问题。如果使用字符串拼接函数或连接符号，但不同数据库的拼接函数或连接符号不同。
• 例如 MySQL 的 concat 函数、Oracle 的连接符号“||”，这样 SQL 映射文件就需要根据不同的数据库提供不同的实现，显然比较麻烦，且不利于代码的移植。幸运的是，MyBatis 提供了 元素来解决这一问题。
• 模糊查询时，使用concat字符串拼接函数，在MySQL中支持多个参数，而Oracle中只能写两个参数

mysql写法：

select * from smbms_user where
userName like concat("%",#{userName},"%")
1
2

oracle写法

select * from smbms_user where
userName like concat("%",concat(#{userName},"%"))
1
2

因此，在Oracle迁移至MySQL时，会导致mybatis报错，我们可以使用bind标签直接兼容，如下：

select * from smbms_user where
<bind name="userName" value="'%'+userName+'%'" />
userNamelike #{userName}  <!-- userName实际是bind标签中的value->
1
2
3

Mybatis分页查询

Mybatis的分页功能很弱，它是基于内存的分页（查询所有记录再按偏移量offset和边界limit取结果），在大数据量的情况下这样的分页基本上是没用的

重写Mybatis的分页

• 自定义功能需求：Mybatis的默认分页插件可能无法满足特定的分页需求，比如定制化的分页逻辑、特殊的排序方式等。重写分页可以根据具体需求进行个性化的扩展和优化。
• 性能优化：Mybatis的默认分页插件在处理大数据量时可能存在性能瓶颈，重写分页可以通过优化算法、减少数据库查询次数等方式提升性能。
• 适应新版本：Mybatis框架本身不断更新迭代，引入了新的特性和改进，原有的分页插件可能不再兼容或不充分利用新版本的特性。重写分页可以更新适应新版本的Mybatis框架。
• 深入理解分页原理：通过重写分页，可以更深入地理解分页机制的原理和实现方式，加深对持久层框架的理解和掌握。

不使用分页插件编写的分页SQL

xml

	<select id="getUserByUserNameAndUserRole" resultType="User">
        <include refid="all"></include>
        limit #{start},#{pageSize}
    </select>
1
2
3
4

测试

    @Test
    public void getUserByUserNameAndUserRole(){
        SqlSession sqlSession = MybatisUtil.createSqlSession();
        userMapper = sqlSession.getMapper(UserMapper.class);
        int pageSize = 5;
        int currentPage = 1;
        int start = (currentPage-1)*pageSize;
        List<User> userList = userMapper.getUserByUserNameAndUserRole(0,"孙",start,pageSize);
        userList.forEach(System.out::println);
        MybatisUtil.close(sqlSession);
    }
1
2
3
4
5
6
7
8
9
10
11

使用分页插件编写分页SQL

1.依赖

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.1.2</version>
</dependency>
1
2
3
4
5

2.mybatis-config.xml中添加拦截器配置

注意放的顺序

	<plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
    </plugins>
1
2
3

3.使用PageHelper进行分页

    @Test
    public void getUserByPage(){
        SqlSession sqlSession = MybatisUtil.createSqlSession();
        userMapper = sqlSession.getMapper(UserMapper.class);

        PageHelper.offsetPage(15,3);//偏移量，页面大小
        List<User> userList = userMapper.getUserByPage(0,null);
        PageInfo<User> pageInfo = new PageInfo<>(userList);
        System.out.println(pageInfo);
        pageInfo.getList().forEach(System.out::println);

        MybatisUtil.close(sqlSession);
    }
1
2
3
4
5
6
7
8
9
10
11
12
13