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

MyBatis必学之动态SQL

苏生Susheng

2200人浏览 · 2024-04-16 10:00:21

苏生Susheng · 2024-04-16 10:00:21 发布

动态SQL

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

作用

生成SQL语句具有灵活性：动态SQL允许根据不同的条件来生成不同的SQL语句，从而实现动态查询和更新操作。这样我们可以根据项目框架需求灵活地构造所需的SQL语句，避免了不需要硬编码的情况出现。
使SQL语句清晰易懂（可读性）：使用动态SQL可以使SQL语句更加清晰易读。通过使用内置携带的函数与标签，根据条件组织SQL语句的不同部分，从而避免大量的SQL嵌套与重复。它使SQL语句更加直观，更有利于开发人员理解及维护。
具有较强的扩展性：动态SQL提供了一种扩展SQL语句的方式，允许我们根据需求加、修改或删除查询条件。这种扩展性使得我们能够轻松地改变和优化SQL语句，而不需要修改大量的固定SQL代码。这样大大减少了开发使用的时间，提高了开发效率及后期的维护效率。
性能优化：通过使用动态SQL，我们可以根据不同的条件动态生成SQL语句，从而避免不必要的查询或更新操作。例如，我们可以根据用户的选择动态生成查询条件，只查询真正需要的数据，减少了不必要的数据库操作，提高了系统性能。

MyBatis的9种动态SQL标签

元素	作用	备注
if	判断语句	单条件分支判断
choose、when、otherwise>	相当于Java中的Switch case语句	多条件分支判断
trim	辅助元素	用于去除或补齐SQL语句片段的开头和结尾的空格，可以用于拼接包含可选条件的SQL语句。
where	辅助元素	用于拼接WHERE子句，自动处理WHERE关键字和添加适当的AND或OR连接条件。
set	辅助元素	用于拼接UPDATE语句的SET子句，自动处理SET关键字和添加适当的逗号分隔更新字段。
foreach	循环语句	在in语句等列举条件中较常用
bind	辅助元素	拼接参数
sql	定义一个通用功能	常用include结合使用

动态SQL的常用函数

函数	函数说明
trim()	去除字符串的开头或结尾指定字符（默认为空格）。例子：<trim prefixOverrides="AND
concat()	拼接多个字符串。例子：${param1} + ’ ’ + ${param2}
substring()	截取字符串的一部分。例子：SUBSTRING(column_name, start_index, length)
lower()	将字符串转换为小写。例子：LOWER(column_name)
upper()	将字符串转换为大写。例子：UPPER(column_name)
replace()	替换字符串中的某个字符或字符串。例子：REPLACE(column_name, old_string, new_string)
length()	获取字符串的长度。例子：LENGTH(column_name)
now()	获取当前时间。例子：NOW()
date_format()	格式化日期。例子：DATE_FORMAT(column_name, pattern)

动态SQL应用场景

应用场景	说明
条件查询	当需要根据不同的条件进行查询时，可以使用动态SQL来根据条件动态拼接WHERE子句。比如在一个用户管理系统中，可以根据用户的姓名、性别、年龄等条件来进行灵活的查询。
动态排序	当需要根据不同的字段进行排序时，可以使用动态SQL来动态生成ORDER BY子句。比如在一个商品列表中，用户可以选择按照价格、销量等字段进行排序。
动态更新	当需要根据不同的条件来进行更新操作时，可以使用动态SQL来根据条件动态生成UPDATE语句。比如在一个订单系统中，可以根据订单状态、支付状态等条件来更新订单信息。
动态插入	当需要根据不同的条件来进行插入操作时，可以使用动态SQL来根据条件动态生成INSERT语句。比如在一个用户注册系统中，可以根据用户的选择来插入不同的用户信息。
复杂逻辑处理	当需要根据复杂的业务逻辑来生成SQL语句时，可以使用动态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()

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

javaBean对象，例如上面的person，则 OGNL 表达式格式必须为 #{属性名}
map 对象，OGNL 表达式格式为 #{key}
简单类型对象（基本类型、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");

${ }

${} 的实际作用：直接把值设置进去。(可能会出现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";

动态SQL使用示例

1.if元素

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

在这里插入图片描述

代码实现

mapper

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

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>

注意！！！

<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>

3.set元素

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

代码实现

mapper

    int updateUserById(User user);

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>

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>

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);

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>

5.1.2List入参

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

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

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>

5.1.3Map中有list入参

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

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

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>

测试

    @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();
    }

5.2 批量添加

mapper

int insertUserBatch(List<User> userList);

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>

测试

    @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);
    }

6.choose(when、otherwise)

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

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

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

代码实现

mapper

int updateUserByFlag(String flag);

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>

7.sql，include元素

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

代码示例

公共部分SQL

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

分页查询列表

    <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>

查询详情

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

8.bind元素

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

语法

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},"%")

oracle写法

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

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

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

Mybatis分页查询

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

重写Mybatis的分页

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

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

xml

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

测试

    @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);
    }

使用分页插件编写分页SQL

1.依赖

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.1.2</version>
</dependency>

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

注意放的顺序

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

在这里插入图片描述

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);
    }

开放原子开发者工作坊

开放原子开发者工作坊旨在鼓励更多人参与开源活动，与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动，如meetup、训练营等，主打技术交流，干货满满，真诚地邀请各位开发者共同参与！

更多推荐

第二届开放原子大赛首批创新成果集结武汉，诚邀广大开发者共鉴开源技术盛宴

开放原子开发者工作坊

诚邀报名 | 开源基础设施能力建设分论坛：打造开源生态的“心脏”

开放原子开发者工作坊

诚邀报名 | 编程语言分论坛：AI时代的技术革新与开源实践

开放原子开发者工作坊

所有评论(0)

查看更多评论

苏生Susheng

@weixin_37833693

已为社区贡献21条内容

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

苏生Susheng

目录

动态SQL

作用

MyBatis的9种动态SQL标签

动态SQL的常用函数

动态SQL应用场景

动态SQL的执行原理

OGNL：表达式注入

OGNL三要素/三个参数

表达式（Expression）

Root对象（Root Object）：

上下文环境（Context）：

OGNL 表达式获取属性举例

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

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

#{ }

${ }

动态SQL使用示例

1.if元素

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

代码实现

注意！！！

2.where元素

上个案例的mapper.xml实现

3.set元素

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

代码实现

4.trim元素

上个案例的mapper.xml实现

5.foreach元素

5.1.批量查询

5.1.1数组入参

5.1.2List入参

5.1.3Map中有list入参

5.2 批量添加

6.choose(when、otherwise)

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

代码实现

7.sql，include元素

代码示例

公共部分SQL

8.bind元素

语法

案例背景

mysql写法：

oracle写法

Mybatis分页查询

重写Mybatis的分页

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

使用分页插件编写分页SQL

1.依赖

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

3.使用PageHelper进行分页

所有评论(0)

苏生Susheng