阅读 2904

MyBatis系列(九)- MyBatis的动态SQL| 8月更文挑战


相关文章

MyBatis系列汇总:MyBatis系列


前言

  • 动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架,你应该能理解根据不同条件拼接 SQL 语句有多痛苦,例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。利用动态 SQL,可以彻底摆脱这种痛苦。

  • 上面这句话是MaBatis官网说的!这篇文章很重要!在工作中必不可少!

  • 首先我们先建立一些测试的库。下面的示例都是建立在此表进行测试的!

    • image-20210730140037206.png
  • 实体类

    • @Data
      @AllArgsConstructor
      @NoArgsConstructor
      public class Blog {
          private Integer id;
          private String title;
          private String autor;
          private Date creat_time;
          private Integer reads;
      }
      复制代码
  • 使用动态标签,可以在xml中也就是sql中写一些基本的逻辑。十分方便好用!

  • 造点数据,方便测试。

    • image-20210730161726094.png

一、if 、where 标签

  • xml

    •     <select id="getBlogInfo" resultType="Blog" parameterType="map">
              select
                 *
              from
                 myblog
              where
                 1 = 1
              <if test="title!='' and title!=null">
                  and title like concat(concat('%',#{title}),'%')
              </if>
          </select>
      复制代码
  • mapper

    • List<Blog> getBlogInfo(Map<String,Object> map);
      复制代码
    • 在实际工作开发中,我们传入的值一般使用map来。这样会更加方便和易扩展。

    • 而返回的result,我们一般使用实体类来实现,因为Swgger,前后端联调无敌方便!!强烈推荐!

    • 如果有小伙伴想了解Swgger的话,欢迎留言,博主会根据需要的程度来决定是否单独开一篇来详解Swgger!!!

  • Junit Test

    •     @Test
          public void getMyBlog(){
              SqlSession session = MybatisUtils.getSession();
              MyBlogMapper mapper = session.getMapper(MyBlogMapper.class);
              Map<String,Object> map = new HashMap<>();
              map.put("title","Mybatis");
              List<Blog> myBlogMappers = mapper.getBlogInfo(map);
              for (Blog myBlogMapper : myBlogMappers) {
                  System.out.println(myBlogMapper);
              }
              session.close();
          }
      复制代码
  • 执行结果:

    • image-20210730162207384.png
    • 完美查出。
  • 可能有人会问,为什么要加这个标签呢?

    • 首先,if 标签可以让我们的sql更加灵活,这个 title 的值传的话就代表带条件查询,不传的话,即是无效代码,查询所有!
    • 然后,如果在service中实现这种效果也是可以的,但是十分繁琐。所以在实际工作中,if 标签是用的最多的!
    • 最后,同学们注意到上面sql中 1=1,为啥要写这个,在实际开发中,我们一般都是软删除(逻辑删除),所以一般都会有个删除标识来确定这条数据是否存在!加这个纯粹是为了模拟真实开发代码!
  • 那么where标签什么时候用呢?

    • xml

      •     <select id="getBlogInfo1" resultType="Blog" parameterType="map">
                select
                *
                from
                myblog
                where
                <if test="title!='' and title!=null">
                    title like concat(concat('%',#{title}),'%')
                </if>
                <if test="id!='' and id!=null">
                    and id = #{id}
                </if>
            </select>
        
        复制代码
      • 如果我们的语句是这样子的,那么如果 title 为空,sql语句是不是相当于where后面直接加上了and?

      • 演示:

        •     @Test
              public void getMyBlog1(){
                  SqlSession session = MybatisUtils.getSession();
                  MyBlogMapper mapper = session.getMapper(MyBlogMapper.class);
                  Map<String,Object> map = new HashMap<>();
                  map.put("id","2");
                  List<Blog> myBlogMappers = mapper.getBlogInfo1(map);
                  for (Blog myBlogMapper : myBlogMappers) {
                      System.out.println(myBlogMapper);
                  }
                  session.close();
              }
          
          复制代码
        • 报错信息如下

        • image-20210730163358590.png

        • org.apache.ibatis.exceptions.PersistenceException: 
          ### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and id = '2'' at line 8
          ### The error may exist in com/dy/dynamic/mapper/MyBlogMapper.xml
          ### The error may involve com.dy.dynamic.mapper.MyBlogMapper.getBlogInfo1-Inline
          ### The error occurred while setting parameters
          ### SQL: select         *         from         myblog         where                                 and id = ?
          ### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and id = '2'' at line 8
          
          复制代码
  • 使用where标签

    •     <select id="getBlogInfo1" resultType="Blog" parameterType="map">
              select
              *
              from
              myblog
              <where>
              <if test="title!='' and title!=null">
                  title like concat(concat('%',#{title}),'%')
              </if>
              <if test="id!='' and id!=null">
                  and id = #{id}
              </if>
              </where>
          </select>
      复制代码
    • 执行看结果

    • image-20210730163517626.png

    • 完美解决!

  • 如果没有匹配的条件会怎么样?最终这条 SQL 会变成这样:

    • SELECT * FROM BLOG
      WHERE
      复制代码
  • 这会导致查询失败。如果匹配的只是第二个条件又会怎样?这条 SQL 会是这样:

    • SELECT * FROM BLOG
      WHERE AND id = 2
      复制代码
    • 这个查询也会失败。这个问题不能简单地用条件元素来解决。

  • where标签的作用显而易见了!

    • 当条件有and时,它可以判断该条件是否是第一个条件,是的话自动去除。
    • where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
  • 如果 where 元素与你期望的不太一样,你也可以通过自定义 trim 元素来定制 where 元素的功能。比如,和 where 元素等价的自定义 trim 元素为:

    • <trim prefix="WHERE" prefixOverrides="AND |OR ">
        ...
      </trim>
      复制代码
    • 这个trim 下面再讲。

二、choose、when、otherwise 标签

  • 有时候,我们不想使用所有的条件,而只是想从多个条件中选择一个使用。针对这种情况,MyBatis 提供了 choose 元素,它有点像 Java 中的 switch 语句。

  • if 讲完了,记不记得还有一种判断条件的东东?

    • 没错就是 switch case (JAVA中)
    • 在动态sql中就是 choose
    • 下面来玩一玩这玩意
  • xml

    •     <select id="getBlogInfoWhoose" resultType="Blog" parameterType="map">
              select
                  *
              from
                  myblog
             <where>
                <choose>
                    <when test="title!='' and title!=null">
                        title like concat(concat('%',#{title}),'%')
                    </when>
                    <when test="id!='' and id!=null">
                        and id = #{id}
                    </when>
                    <otherwise>
                        AND `reads` > 10000
                    </otherwise>
                </choose>
             </where>
          </select>
      复制代码
  • mapper

    • List<Blog> getBlogInfoWhoose(Map<String,Object> map);
      复制代码
  • Junit Test

    •     @Test
          public void getMyBlog1(){
              SqlSession session = MybatisUtils.getSession();
              MyBlogMapper mapper = session.getMapper(MyBlogMapper.class);
              Map<String,Object> map = new HashMap<>();
              map.put("title","Spring");
              List<Blog> myBlogMappers = mapper.getBlogInfoWhoose(map);
              for (Blog myBlogMapper : myBlogMappers) {
                  System.out.println(myBlogMapper);
              }
              session.close();
          }
      复制代码
  • 执行结果

    • image-20210730165601475.png
  • 当没有满足choose中的条件时

    • Junit Test

      • @Test
         public void getMyBlog1(){
             SqlSession session = MybatisUtils.getSession();
             MyBlogMapper mapper = session.getMapper(MyBlogMapper.class);
             Map<String,Object> map = new HashMap<>();
        //        map.put("title","Spring");
                List<Blog> myBlogMappers = mapper.getBlogInfoWhoose(map);
                for (Blog myBlogMapper : myBlogMappers) {
                    System.out.println(myBlogMapper);
                }
                session.close();
            }
        
        复制代码
    • 执行结果

      • image-20210730165825240.png
      • 这样只查出来readis(阅读量)大于10000的数据
  • 总结下这几个标签

    • 类似reads这种关键字,需要加飘号 `
    • 所有条件都不满足的时候就输出 otherwise 中的内容。
    • when元素表示当 when 中的条件满足的时候就输出其中的内容,跟 JAVA 中的 switch 效果差不多的是按照条件的顺序。

三、trim、set 标签

  • 上面说的这么多,都是在讲select(查询),那么现在来讲一下 update 中的标签 set
  • 基本的update语句我这里就不再重复写了,大家只需要关注这种动态语句即可。在实际工作中开发一般以这种居多,毕竟它比较灵活嘛!

①、set 标签

  • xml

    •     <update id="updateBlogName" parameterType="map">
              update myblog
              <set>
                  <if test="title!=null and title!=''">
                      title = #{title},
                  </if>
                  <if test="autor!=null and autor!=''">
                      autor = #{autor},
                  </if>
                  <if test="reads!=null and reads!=''">
                      `reads` = #{reads}
                  </if>
              </set>
              where
                  id = #{id}
          </update>
      复制代码
  • mapper

    • Integer updateBlogName(Map<String,Object> map);
      复制代码
  • Junit Test

    •     @Test
          public  void updateBlogName(){
              SqlSession session = MybatisUtils.getSession();
              MyBlogMapper mapper = session.getMapper(MyBlogMapper.class);
              Map<String,Object> map = new HashMap<>();
              map.put("id","3");
              map.put("title","富婆让我陪她逛街");
              map.put("autor","大大大大鱼");
              map.put("reads","100000");
              Integer num = mapper.updateBlogName(map);
              System.out.println("一共更新了:"+num+"条数据");
              session.commit();//更新不要忘记提交事务哦
              session.close();
          }
      复制代码
  • 执行结果

    • image-20210731162506916.png
    • image-20210731162711474.png
  • 大家有没有发现,我们在sql中是将set语句后面的逗号写死的?

    • image-20210731163403210.png

    • 那么,如果我们只传了titleautor呢?

    • 理论上是不是语句变成这样?

      • update myblog set
            title = #{title},
        	autor = #{autor},
        where id = #{id}
        复制代码
    • 我们执行一下试试

      • image-20210731163729668.png
      • image-20210731163748574.png
    • 更新成功,原来set标签和where标签一样,这么智能呀!

    • set标签可以自动帮我识别语句结尾的逗号并进行一定的处理!

    • 这样我们写的sql语句是不是更加具有灵活性呢?十分方便好用!

②、trim 标签

  • 通过上面的例子我们知道了,whereset 标签可以去除逗号 、and 、or 这种连接符。

  • trim 标签也是可以做到的!

  • xml

    •     <insert id="insertBlogName" parameterType="map">
              insert into myblog
              (
              <if test="title!=null and title!=''">
                  title,
              </if>
              <if test="autor!=null and autor!=''">
                  autor,
              </if>
              <if test="reads!=null and reads!=''">
                  `reads`,
              </if>
              <if test="creat_time!=null">
                  `creat_time`
              </if>
              )
              values(
              <if test="title!=null and title!=''">
                  #{title},
              </if>
              <if test="autor!=null and autor!=''">
                  #{autor},
              </if>
              <if test="reads!=null and reads!=''">
                  #{reads},
              </if>
              <if test="creat_time!=null">
                  #{creat_time}
              </if>
              )
          </insert>
      复制代码
  • mapper

    • Integer insertBlogName(Map<String,Object> map);
      复制代码
  • Junit Test

    •     @Test
          public void insetBlogInfo(){
              SqlSession session = MybatisUtils.getSession();
              MyBlogMapper mapper = session.getMapper(MyBlogMapper.class);
              Map<String,Object> map = new HashMap<>();
              map.put("title","如何榜上富婆?");
              map.put("autor","大鱼");
              map.put("reads","1000");
              map.put("creat_time",new Date());
              Integer num = mapper.insertBlogName(map);
              System.out.println("一共新增了:"+num+"条数据");
              session.commit();
              session.close();
          }
      复制代码
  • 执行结果

    • image-20210731194154983.png
    • image-20210731194211831.png
  • 记得session.commit(); 提交事务哦~

  • 这个sql如果我们不是传所有,是不是也会出现上面的问题?就是多个逗号,导致sql执行出错?

    • image-20210731194402341.png

image-20210731194402341.png

  • 我们可以使用trim标签来完成自动去除逗号等连接符

  • xml改造

    •     <insert id="insertBlogName1" parameterType="map">
              insert into myblog
              <trim prefix="(" suffix=")" suffixOverrides=",">
                  <if test="title!=null and title!=''">
                      title,
                  </if>
                  <if test="autor!=null and autor!=''">
                      autor,
                  </if>
                  <if test="reads!=null and reads!=''">
                      `reads`,
                  </if>
                  <if test="creat_time!=null">
                      `creat_time`
                  </if>
              </trim>
              <trim prefix="values(" suffix=")" suffixOverrides=",">
                  <if test="title!=null and title!=''">
                      #{title},
                  </if>
                  <if test="autor!=null and autor!=''">
                      #{autor},
                  </if>
                  <if test="reads!=null and reads!=''">
                      #{reads},
                  </if>
                  <if test="creat_time!=null">
                      #{creat_time}
                  </if>
              </trim>
          </insert>
      复制代码
    • 执行结果

      • image-20210731194922870.png
      • image-20210731194938661.png
    • 完美解决问题!

    • 重点解析

      • prefix:开头所需加的
      • suffix:结束所需加的
      • suffixOverrides:每行语句结束时需要加的东西
      • prefixOverrides:每行语句开始时需要加的东西
    • 注意点

      • 当数据库中设置了时间为datetime时,我们if标签中不要判断不为空

      • 只需要判断不为null即可

      • image-20210731195553294.png

四、foreach 标签

  • 在sql中写for循环是种什么感受?

  • 来玩一个需求:查询id为1、2、4、5的博客数据。

  • 学到这里了,千万不要这样用and这种的写法。

    • select * from myblog where id = 1 or id = 2 or id = 4 or id = 5
      复制代码

    ①、foreach简单用法

  • xml

    •    <select id="getBlogInfos" parameterType="map" resultType="Blog">
              select * from myblog
              <where>
                  <foreach collection="ids" item="id" open="and ("
                           close=")" separator="or">
                      id=#{id}
                  </foreach>
              </where>
          </select>
      复制代码
  • mapper

    • List<Blog> getBlogInfos(Map<String,Object> map);
      复制代码
  • Junit Test

    •     @Test
          public void getBlogInfos(){
              SqlSession session = MybatisUtils.getSession();
              MyBlogMapper mapper = session.getMapper(MyBlogMapper.class);
              Map<String,Object> map = new HashMap<>();
              List<String> ids = new ArrayList<>();
              ids.add("1");
              ids.add("2");
              ids.add("4");
              ids.add("5");
              map.put("ids",ids);
              List<Blog> list = mapper.getBlogInfos(map);
              for (Blog blog : list) {
                  System.out.println(blog);
              }
              session.close();
          }
      
      复制代码
  • 执行结果

    • image-20210731221756853.png

    • foreach标签

      • collection : 这个是我们在标签内所需遍历的集合,即放入map中的key
      • item : 遍历出来的值我们所赋予的key
      • open : 开始所加的参数
      • close : 结束所加的参数
      • separator : 每个值中间所加的参数

    ②、where in 配合foreach用法

    • xml

      •    <select id="getBlogInfos1" parameterType="map" resultType="Blog">
                select * from myblog
                <where>
                    id in
                    <foreach collection="ids" item="id" open="("
                             close=")" separator=",">
                        #{id}
                    </foreach>
                </where>
            </select>
        复制代码
    • 其他地方不需要变

    • 这个最终效果是

      • select * from myblog WHERE id in ( ? , ? , ? , ? ) 
        复制代码
      • image-20210731222613047.png

    • 只列举这两个例子吧,具体怎么用还是得看具体的需求。

    • sql的长度是有限制的,这个ids里面的值太多会无法查询。

    • sql最大长度是:默认的SQL拼接长度最大值是2000个参数。

五、sql、include 标签

  • 上面那么多标签玩下来,大家会发现一个问题,就是同一个业务中,可能会有很多重复的内容,比如每个查询都有titleautor等相同内容。

  • 那么我们有没有类似于java中工具类的方法呢?

  • 这时候就需要用到我们的sql、include 标签啦~

  • xml

    •     <sql id="if-key-info">
              <if test="title!=null and title!=''">
                  title = #{title}
              </if>
              <if test="autor!=null and autor!=''">
                  and autor = #{autor}
              </if>
              <if test="reads!=null and reads!=''">
                  and `reads` = #{reads}
              </if>
          </sql>
      
          <select id="getBlogInfo" resultType="Blog" parameterType="map">
              select
                 *
              from
                 myblog
              <where>
                  <include refid="if-key-info"></include>
              </where>
          </select>
      
      复制代码
    • 执行结果

      • image-20210731223327323.png
    • 有了这个,我们可以把相同的的放在一起,是不是方便了很多呢?

    • sql id= "" :这里面的命名是随意的,只要在当前mapper.xml是唯一的即可

    • include : 引用上面的命名即可!


路漫漫其修远兮,吾必将上下求索~

如果你认为i博主写的不错!写作不易,请点赞、关注、评论给博主一个鼓励吧~hahah

文章分类
后端
文章标签