MyBatis_动态SQL

最新推荐文章于 2023-07-17 10:39:18 发布
最新推荐文章于 2023-07-17 10:39:18 发布
阅读量162 收藏
点赞数
分类专栏： MyBatis 文章标签： MyBatis
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_44509920/article/details/107584392
版权

文章目录

7.1. 遇到的问题

客户端查询的信息不确定，因此查询条件不确定，需要动态判断

  • 示例：查询条件的不确定
    在这里插入图片描述
    以前的写法：
  1. 编写mapper接口（dao中的方法）
  2. 在mapper.xml编写SQL片段（statement）
  3. 测试
  • mapper接口
//多条件查询
public List<User> selectByNameAndBirthday(User user);
  • mapper.xml
<!--多条件查询-->
<select id="selectByNameAndBirthday" resultType="user">
    select <include refid="baseColumn"></include>
    from `user`
    where username like #{username}
    AND birthday = #{birthday}
</select>
  • 测试
 /**
     * 多条件查询
     */
@Test
public void selectByNameOrPhone() throws ParseException {
    //使用工具类获取sqlSession
    SqlSession sqlSession = MyBatisUtils.getSession();

    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    //模拟接收到的前台参数
    String username = "张";
    Date birthday = new SimpleDateFormat("yyyy-MM-dd")
        .parse("1998-12-25");

    User user = new User();
    user.setUsername("%"+username+"%");
    //user.setBirthday(birthday);

    //根据条件查询
    List<User> users = mapper.selectByNameAndBirthday(user);
    for (User u : users) {
        System.out.println(u);
    }
    //关闭会话
    MyBatisUtils.closeSession();
}
  • 问题：
    当没有输入全部查询条件时，反而查询不到结果，可以从日志的SQL语句中找到原因
==>  Preparing: 
select uid,username,birthday,phone,sex,address from `user` where username like ? AND birthday = ?
[DEBUG] 2020-07-19 23:01:26,937 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:137)
==> Parameters: %张%(String), null

没有传入参数时，参数值为null，对应SQL如下：

select uid,username,birthday,phone,sex,address from `user` where username like " %张%" AND birthday = null

AND birthday = null 肯定查询不到结果，这时就需要动态判断参数是否有值，动态拼接SQL，下面我们就来学习学习MyBatis提供的动态SQL功能。

7.2. 动态SQL

动态 SQL 是 MyBatis 的强大功能之一，可以完成各种复杂的SQL，动态SQL 相关元素如下：

  • if
  • where
  • choose(when otherwise)
  • foreach
  • set（了解）
  • trim（了解）
7.2.1. if

if：对传入参数的属性值进行判断，结果为true则拼接SQL片段，否则不拼接

修改前面根据用户名和手机号查询的SQL片段

  • mapper.xml
<!--动态SQL - if -->
<select id="selectByNameAndBirthday2" resultType="user">
    select <include refid="baseColumn"></include>
    from `user`
    where
    <if test="username != null and username != ''">
        username like #{username}
    </if>
    <if test="birthday != null">
        AND birthday = #{birthday}
    </if>

</select>

这里使用if判断，如果用户输入了username、birthday，则该属性值！=null，拼接对应的查询SQL，否则不拼接

  • 注意

if判断中，条件使用 and 、or拼接，不能使用 & | AND OR

非字符串类型不要进行空串比较，pro != ‘’，因为会先将pro强制转为字符串

  • mapper接口
public List<User> selectByNameAndBirthday2(User user);
  • 测试
/**
     * 动态sql - if
     */
@Test
public void selectByNameOrPhone2() throws ParseException {
    //使用工具类获取sqlSession
    SqlSession sqlSession = MyBatisUtils.getSession();

    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    //模拟接收到的前台参数
    String username = "张";
    Date birthday = new SimpleDateFormat("yyyy-MM-dd")
        .parse("1998-12-25");

    User user = new User();
    //user.setUsername("%"+username+"%");
    user.setBirthday(birthday);

    //根据条件查询
    List<User> users = mapper.selectByNameAndBirthday2(user);
    for (User u : users) {
        System.out.println(u);
    }
    //关闭会话
    MyBatisUtils.closeSession();
}

测试：输入birthday 结果：异常，对应SQL

SELECT uid,username,birthday,phone,sex,address FROM `user` WHERE AND birthday = ?

测试：不输入参数 结果 ： 异常，对应SQL

SELECT uid,username,birthday,phone,sex,address FROM `user` WHERE

​问题

  • 有where条件时，加上where关键字，并自动判断要不要加and
  • 希望没有条件时，不要where关键字
7.2.2. where

使用where元素解决上述问题

<!--动态SQL - if where -->
<select id="selectByNameAndBirthday2" resultType="user">
    select <include refid="baseColumn"></include>
    from `user`
    <where>
        <if test="username != null and username != ''">
            username like #{username}
        </if>
        <if test="birthday != null">
            AND birthday = #{birthday}
        </if>
    </where>
</select>
7.2.3. choose

choose相当于java中的switch

  • 需求
    • 根据性别查询，注意问题，用户输入的是“男”“女”,数据库中国存储的是1和2表示男女
  • mapper接口
//动态sql - choose
public List<User> dynamicSQLChoose(String sex);
  • mapper.xml
<select id="dynamicSQLChoose" resultType="user">
    select
    <include refid="baseColumn"></include>
    from `user`
    <where>
        <choose>
            <when test='sex != null and sex == "男"'>
                and sex = '1'
            </when>
            <when test='sex != null and sex == "女"'>
                and sex = '2'
            </when>
            <otherwise>
                and sex is not null
            </otherwise>
        </choose>
    </where>
</select>
  • 这里必须注意：当比较的值只有一个字符时，mybatis会把值当成char处理，转成数值，汉字是无法直接转成数值的，因此这里要用双引号，表示是字符串
<when test='sex != null and sex == "女"'>
  • 测试
/**
  * 动态SQL choose
  */
@Test
public void dynamicSQLChoose(){
    SqlSession sqlSession = MyBatisUtils.getSession();

    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    List<User> list = mapper.dynamicSQLChoose("女");
    for (User user : list) {
        System.out.println(user);
    }
     //关闭会话
    MyBatisUtils.closeSession();
}
7.2.4. foreach

类似于java中for循环，主要用于in语句的条件查询

需求：根据多个ID进行查询

  • UserMapper.xml
<!--
SELECT uid,username,birthday,sex,address
FROM `user`
WHERE uid IN (1,10,12,16)
collection: 遍历的集合类型 arrar -> 数组 list -> list  collection -> set
item ; 变量名，可以自定义，表示遍历的每一项的值
open: 开始内容
separator: 分隔符
close: 结束内容
-->

<select id="dynamicSQLForeach" resultType="user">
    select uid,username,birthday,sex,address
      from `user`
      <if test="collection != null">
        <where>
             <foreach collection="collection" item="item" open="uid IN (" separator="," close=")">
                  #{item}
             </foreach>
        </where>
      </if>
</select>
  • mapper接口
public List<User> dynamicSQLForeach(Set<Integer> ids);
  • 测试
@Test
public void dynamicSQLForeach(){
    //获取连接
    SqlSession sqlSession = MyBatisUtils.getSession();
    //获取mapper代理
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    //查询条件
    //数组类型
    //int[] ids = {1,10,12,16};
    
    //list集合类型
    //ArrayList<Integer> list = new ArrayList<>();
    //list.add(1);
    //list.add(10);
    //list.add(12);
    //list.add(16);
    
    //set集合类型
    HashSet<Integer> set = new HashSet<>();
    set.add(1);
    set.add(10);
    set.add(12);
    //调用接口方法
    //List<User> users = mapper.dynamicSQLForeach(ids);
    //List<User> users = mapper.dynamicSQLForeach(list);

    List<User> users = mapper.dynamicSQLForeach(set);
    
    //List<User> users = mapper.dynamicSQLForeach(null);
    //遍历查询结果
    for (User u : users) {
        System.out.println(u);
    }
    
    //关闭会话
    MyBatisUtils.closeSession();
}
7.2.5. set (了解)

set用于update的动态SQL中。

  1. 使用set标签可以将动态的配置 SET 关键字，并剔除追加到条件末尾的任何不相关的逗号。
  2. 使用 if+set 标签修改后，如果某项为 null 则不进行更新，而是保持数据库原值。

  • 遇到的问题

    用户更新信息的时候，可能只填入部分信息，因此编写update语句时需要判断字段是否更新，按照之前只写if判断，动态拼接SQL过程如下：

  • UserMapper.xml

<!--动态sql - update只用if判断的写法-->
<update id="dynamicUpdateUser" >
    UPDATE
    `user`
    SET 
    <if test="username != null and username != ''">
        username = #{username},
    </if>
    <if test="phone != null and phone != ''">
        phone=#{phone},
    </if>
    <if test="sex != null and sex != ''">
        sex = #{sex}
    </if>
    WHERE uid = #{uid}
</update>
  • 测试
/**
  * 测试动态SQL - UPDATE
  */
@Test
public void dynamicSQLUpdate(){
    //获取会话连接
    SqlSession sqlSession = MyBatisUtils.getSession();

    //模拟用户输入的参数
    User user = new User();
    user.setUid(12);
    user.setUsername("蹦迪小王子");
    user.setPhone("13266666666");
    //模拟不更新性别
    // user.setSex("1");
    //完成更新
    sqlSession.update("test.dynamicUpdateUser",user);

    //关闭事务并提交
    MyBatisUtils.commitAndclose();
}
  • 发现问题
  • 当在 update 语句中使用if标签时，如果后面的if没有执行，导致动态拼接的SQL逗号多余错误。
  • 当任何参数都没输入时，动态拼接的SQL多余一个set关键字

当不传入sex参数时，对应的SQL：

  UPDATE `user` SET username = ?,phone = ?, where uid = ?

当任何参数都没输入时,对应的SQL：

  UPDATE `user` where uid = ?
  • 解决方案：
    • 使用 元素
  • 修改UserMapper.xml
<!--动态sql - update只用if判断的写法-->
<update id="dynamicUpdateUser" >
    UPDATE
    `user`
    <set>
        <if test="username != null and username != ''">
            username = #{username},
        </if>
        <if test="phone != null and phone != ''">
            phone=#{phone},
        </if>
        <if test="sex != null and sex != ''">
            sex = #{sex}
        </if>
    </set>
    WHERE uid = #{uid}
</update>
  • 解决了最后一个条件不输入时多余逗号的问题，但是没有解决所有参数都不输入时的SQL语法问题，需要自己在代码中判断，后续使用mybatis的高级功能（通用mapper）这些问题都已经处理了。
7.2.6. trim（了解）

trim 是更灵活用来去处多余关键字的标签，它可以用来实现 where 和 set 的效果。

trim 元素的主要功能是可以在自己包含的内容前加上某些前缀，也可以在其后加上某些后缀，与之对应的属性是 prefix 和 suffix；

也可以把包含内容的首部或尾部的某些内容覆盖，对应的属性是 prefixOverrides 和 suffixOverrides；

因此也可以非常简单的利用 trim 来代替 where 元素的功能。

  • trim 代替 where

    示例：使用动态SQL多条件查询：根据输入的用户名/手机号/地址进行查询

    使用where的写法

    • UserMapper.xml
<select id="dynamicSQLForWhere" resultType="user">
        SELECT
            uid,
            username,
            birthday,
            phone,
            sex,
            address
        FROM `user`
        <where>
            <if test="username != null and username != ''">
                username LIKE #{username}
            </if>
            <if test="phone != null and phone != ''">
                AND phone = #{phone}
            </if>
           <if test="address != null and address != ''">
               AND address LIKE #{address}
           </if>
        </where>
    </select>
  • 测试
@Test
  public void dynamicSQLForWhere(){
  //获取会话连接
  SqlSession sqlSession = MyBatisUtils.getSession();
  
  //模拟用户输入的参数
  User user = new User();
  user.setUsername("%蹦迪%");
  user.setPhone("13266666666");
  user.setAddress("%江苏%");
  //完成更新
  sqlSession.selectList("test.dynamicSQLForWhere",user);
  
  //关闭事务并提交
  MyBatisUtils.commitAndclose();
  }

使用trim替代where的写法

<select id="dynamicSQLForTrim" resultType="user">
      SELECT
      uid,
      username,
      birthday,
      phone,
      sex,
      address
      FROM `user`
      <trim prefix="WHERE" prefixOverrides="AND | OR">
          <if test="username != null and username != ''">
              username LIKE #{username}
          </if>
          <if test="phone != null and phone != ''">
              AND phone = #{phone}
          </if>
          <if test="address != null and address != ''">
              AND address LIKE #{address}
          </if>
      </trim>
  </select>

  • trim 替代set
    如果set后面出现逗号，则将其覆盖

    • 动态更新使用的写法
<!--动态sql - update只用if判断的写法-->
<update id="dynamicUpdateUser" >
    UPDATE
    `user`
    <set>
        <if test="username != null and username != ''">
            username = #{username},
        </if>
        <if test="phone != null and phone != ''">
            phone=#{phone},
        </if>
        <if test="sex != null and sex != ''">
            sex = #{sex}
        </if>
    </set>
    WHERE uid = #{uid}
</update>

  - 使用<trim>的写法
  
  ```xml
  <update id="dynamicUpdateForTrim" >
      UPDATE
      `user`
      <trim prefix="set" suffixOverrides=",">
          <if test="username != null and username != ''">
              username = #{username},
          </if>
          <if test="phone != null and phone != ''">
              phone=#{phone},
          </if>
          <if test="sex != null and sex != ''">
              sex = #{sex}
          </if>
      </trim>
      WHERE uid = #{uid}
  </update>
- 练习

需求：查询女生中生日在1996-10-01之前的用户,按照uid降序返回

  1. 手写SQL ，保证SQL 没问题
  2. 改写SQL片段（statement）
  3. 编写mapper接口对应的方法
  4. 测试
  • UserMapper.xml
<!--多条件查询+排序-->
<select id="multiplySelect" resultType="user">
    SELECT <include refid="baseColumn"/>
    FROM `user`
    <where>
        <if test="sex != null and sex != ''">
            sex = #{sex}
        </if>
        <if test="birthday != null">
            AND birthday <![CDATA[  < ]]> #{birthday}
        </if>
    </where>
    ORDER BY ${columnName} DESC
</select>
  • UserMapper.java
//练习：多条件查询
public List<User> multiplySelect(Map<String,Object> map);
  • 测试
/**
  * 多条件查询
  */
@Test
public void multiplySelect() throws ParseException {
    //使用工具类获取sqlSession
    SqlSession sqlSession = MyBatisUtils.getSession();

    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    //模拟接收到的前台参数
    HashMap<String, Object> map = new HashMap<>();
    map.put("sex","2");
    map.put("birthday","1996-10-01");
    map.put("columnName","uid");

    //根据条件查询
    List<User> users = mapper.multiplySelect(map);
    for (User u : users) {
        System.out.println(u);
    }
    //关闭会话
    MyBatisUtils.closeSession();
}
7.3. CDATA
7.3.1. 特殊符号的问题

假如您在 XML 文档中放置了一个类似 “<” 字符，那么这个文档会产生一个错误，这是因为解析器会把它解释为新元素的开始。因此你不能这样写：

AND birthday < #{birthday}

为了避免此类错误，需要把字符 “<” 替换为实体引用，就像这样：

AND birthday &lt; #{birthday}

在 XML 中有 5 个预定义的实体引用：

实体类符号意义
&lt;<小于
&gt;>大于
&amp;&和号
&apos;’省略号
&quot;"引号

在 XML 中仅有字符 “<“和”&” 是非法的。省略号、引号和大于号是合法的

7.3.2. CDATA

CDATA 指的是不应由 XML 解析器进行解析的文本数据（Unparsed CHARACTER DATA）。

在 XML 元素中，"<" 和 “&” 是非法的。

“<” 会产生错误，因为解析器会把该字符解释为新元素的开始。

“&” 也会产生错误，因为解析器会把该字符解释为字符实体的开始。

  • CDATA 部分中的所有内容都会被解析器忽略。
  • CDATA 用法 <![CDATA[ 特殊符号 ]]>
  • 等这些标签都不要放入<![CDATA[ ]]>中，否则也不会解析，们只把有特殊字符的语句放进去即可， 尽量缩小 <![CDATA[ ]]> 的范围
 AND birthday <![CDATA[  < ]]> #{birthday}
BigMoM1573
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mybatis动态SQL之if判断(20)
BruceLeeNumberOne的博客
05-07 528
Mybatis动态SQL核心：对SQL语句进行灵活操作，通过表达式进行判断，对SQL进行灵活拼接、组装。一、if判断1.1、需求用户信息综合查询列表和用户信息查询列表总数这两个statement的定义使用动态SQL。1.2、修改UserMapper.xml中的SQL语句 &lt;!-- 用户信息综合查询 #{userCustom.sex}:取出pojo包装对象中性别值 ${userCustom...
MyBatis动态SQL
TY02072403的博客
03-05 393
官方文档 以下代码和笔记是关于mybatis动态SQL的一套，请移步观看 配置MyBatis的日志打印 在mybatis-config.xml文件中增加如下配置 <settings> <setting name="cacheEnabled" value="true" /> <setting name="useGeneratedKeys" value="true" /> <setting name="defaultExecutorType"
MaBatis使用动态SQL判断的字段找不到字段
热爱Java的小老弟
07-17 135
MyBatis的动态SQL易错点
【hadoop】关于ERROR security.UserGroupInformation
热门推荐
moxiaomomo的专栏
01-23 1万+
14/01/23 20:49:47 ERROR security.UserGroupInformation: PriviledgedActionException as:hadoop (auth:SIMPLE) cause:java.io.IOException: Failed on local exception: com.google.protobuf.InvalidProtocolBuffe
SQL.rar_MyBatis3DynamicSql_dynamic mybatis_mybatis_mybatis Dyna
09-19
而在MyBatis 3中，引入了动态SQL的功能，这正是"SQL.rar_MyBatis3DynamicSql_dynamic mybatis_mybatis_mybatis Dyna"这个压缩包所关注的主题。 动态SQL是MyBatis提供的一种强大功能，使得开发者能够在运行时根据...
MyBatis 执行动态 SQL语句详解
09-01
在 MyBatis 中，动态 SQL 是一个强大的特性，允许我们在 XML 映射文件或注解中编写条件语句，根据运行时的参数来决定 SQL 的具体执行内容。下面我们将深入探讨如何在 MyBatis 中执行动态 SQL 语句。 首先，我们要...
mybatis基本配置及例子.zip_mybatis_mybatis sql server_mybatis 实例_mybati
09-15
MyBatis的动态SQL功能强大，允许在XML映射文件中使用条件语句，如`<if>`, `<choose>`, `<when>`, `<otherwise>`, `<where>`, `<trim>`, `<foreach>`等，使得SQL语句可以根据条件动态生成，提高了代码的可读性和可...
mybatis动态sql.zip
04-28
mybatis动态sql，动态sql解析引擎，类似mybatis动态sql的功能。 mybatis动态sql，动态sql解析引擎，类似mybatis动态sql的功能。mybatis动态sql，动态sql解析引擎，类似mybatis动态sql的功能。mybatis动态sql，...
mybatis_plus 2.92
10-24
2. **条件构造器**：MP的QueryWrapper和UpdateWrapper类提供了强大的条件构造能力，可以通过链式调用来构建复杂的SQL查询条件，同时支持动态SQL，避免了硬编码SQL的尴尬。 3. **分页插件**：内建的PageHelper分页...
mybatis_mybatis_
09-29
MyBatis的动态SQL功能非常强大，可以通过if、choose、when、otherwise、where、trim、foreach等标签构建灵活的SQL语句，极大地增强了SQL的可读性和可维护性。 7. MyBatis的缓存机制： MyBatis内置了两级缓存，一...
MyBatis_3_User_Guide.zip_mybatis_mybatis api
09-24
4. **动态SQL**：MyBatis的强项之一是动态SQL，可以在XML映射文件或注解中使用if、choose、when、otherwise、where、trim、foreach等标签来构建灵活的SQL语句。 5. **参数映射**：MyBatis支持简单的Map、POJO...
mybatis动态sqlmybatis动态sqlmybatis动态sql
04-28
mybatis动态sql MyBatis是一种开源的持久层框架，它为Java程序员提供了一种简化数据库访问的方式。其中，动态SQL是MyBatis的一个重要特性，它允许用户根据不同的条件拼接SQL语句，从而实现更加灵活和可扩展的数据库...
mybatis_springboot_mybatis_
10-01
这个项目结合了 SpringBoot 的快速开发能力、MyBatis 的灵活 SQL 映射以及多数据源的支持。它可以帮助开发者更高效地构建分布式、高可用的应用系统，能够灵活地处理来自不同数据库的数据请求。同时，多数据源的设计...
Mybatis_dao.rar_mybatis dao_mybatis_dao.rar
09-21
每个方法对应一个SQL语句，Mybatis会在运行时动态生成代理对象，执行相应的SQL。 2. **Mapper XML文件**: 每个Mapper接口通常有一个对应的XML配置文件，用于编写SQL语句和结果映射。这种方式使得SQL与代码分离，更...
MyBatis_Generator_1.3.3
05-30
改变了注解提供Generator使用新的SQL生成器类从MyBatis的3.2。如果你必须在一个旧版本的MyBatis运行，设置在配置属性“useLegacyBuilder”为“true”。 问题＃11日 - Maven插件现在记录到日志行家（感谢保罗克劳斯）...
MyBatis_Review.rar
07-13
将配置文件写法，固定的session的获得方法，一些固定语法做一个总结
MyBatis执行动态SQL的方法
08-26
MyBatis执行动态SQL的方法 MyBatis是一款流行的Java持久层框架，它提供了多种执行动态SQL的方法，今天我们就来分享其中的一些。首先，我们可以使用MyBatis的XML配置文件来执行动态SQL。例如，在XXMapper.xml文件中...
MyBatis 动态 SQL 示例
04-28
附件是MyBatis 动态 SQL 示例，MyBatis 是一个持久层框架，它允许用户在 XML 文件中编写动态 SQL 语句。MyBatis 的动态 SQL 功能非常强大，它允许开发者根据运行时的条件动态地生成 SQL 语句。这使得 MyBatis 能够...
mybatis实现动态sql
最新发布
06-07
MyBatis是一个流行的Java持久层框架，它允许开发者在运行时构建动态SQL查询，从而提高了代码的灵活性和适应性。MyBatis通过结合XML映射文件和注解，实现了动态SQL的执行。以下是MyBatis动态SQL的主要实现方式： 1. ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值