MyBatis的动态Sql查询(七)-天翼云

年轻的时候，遇见了一个人，便以为余生再没有江湖，后来，才懂，她才是江湖的起源。

上一章简单介绍了MyBatis的XxxMapper.xml 映射器的详解（六）,如果没有看过,请观看上一章。

一. 动态Sql 查询

在数据库的持久化操作的过程中，最复杂最常用的就是查询 select 语句，主要是后面跟着各种各样的条件判断语句。而MyBatis很好的提供了这一点。体现了Mybatis的灵活性，高度可配置性和可维护性。
具有的sql动态元素有:

if 判断单分支的语句
choose (when otherwise) 多条件分支
trim 去除前缀和后缀的第一个符号
where 辅助元素，代替 sql中的 1=1
set 更新元素
foreach 循环判断
bind 绑定元素

下面老蝴蝶会讲解这些元素的使用。

二. if元素

二.一以前不带参数的查询

老蝴蝶以前常写的查询全部:

接口:

User getById(int id);
List<User> findAllF1();

sql 语句配置:

<!-- 定义map形式 -->
  
  <resultMap type="user" id="userResultMap">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <result property="sex" column="sex"/>
    <result property="age" column="age"/>
    <result property="description" column="description"/>
  </resultMap>
  
  <select id="getById"  parameterType="int" resultType="user">
    <!-- 设置别名 -->
    select * from user where id=#{id}
  </select>
  
  <select id="findAllF1" resultMap="userResultMap">
    select * from user t
  </select>

测试方法:

@Test
  public void findAllF1Test(){
    SqlSession sqlSession=SqlSessionFactoryUtils.getSession();
    UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
    List<User> allList=userMapper.findAllF1();
    allList.forEach(n ->System.out.println(n));
  }

MyBatis的动态Sql查询(七)

二.二带参数的简单查询

上面的都是没有传入条件的，下面传入条件。按照名称进行查询，其中，名称可填可不填。即，如果名称有值，则按照名称进行查询，如果没有传入名称值，则查询全部。

接口:

List<User> findAllF2(User user);

sql 元素: 用mybatis提供的 if 元素进行单分支判断。

<select id="findAllF2" parameterType="user" resultMap="userResultMap">
    select * from user t where 
    <if test="name!=null and name!=''">
      t.name like concat('%',#{name},'%')
    </if>
  </select>

测试方法:

@Test
  public void findAllF2Test(){
    SqlSession sqlSession=SqlSessionFactoryUtils.getSession();
    UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
    User user=new User();
    user.setName("蝴蝶飞");  
    List<User> allList=userMapper.findAllF2(user);
    allList.forEach(n ->System.out.println(n));
  }

可以填写查询的值为蝴蝶飞，可以查询出结果。

MyBatis的动态Sql查询(七)

但如果此时没有传入 name值呢?

即将 user.setName(“蝴蝶飞”); 注释掉:

MyBatis的动态Sql查询(七)

是错误的。

二.三添加1=1 避免sql语句组装不正确

所以，常见的写法就是在where 后面添加一个 1=1, 避免在没有传入参数时，sql 语句组装不正确。

<select id="findAllF2" parameterType="user" resultMap="userResultMap">
    select * from user t where 1=1
    <if test="name!=null and name!=''">
      and t.name like concat('%',#{name},'%')
    </if>
  </select>

当然，也可以传入多个参数，如按照name 和age 进行查询。接口是不变的。
sql 语句也按照上面 if 的写法。后面加上1=1 就避免了sql语句出现错误

<select id="findAllF2" parameterType="user" resultMap="userResultMap">
    select * from user t where 1=1
    <if test="name!=null and name!=''">
      and t.name like concat('%',#{name},'%')
    </if>
    <if test="age!=null">
      and t.age>#{age}
    </if>
  </select>

测试方法:

@Test
  public void findAllF2Test(){
    SqlSession sqlSession=SqlSessionFactoryUtils.getSession();
    UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
    User user=new User();
    //两个条件时
    user.setName("蝴蝶飞");
    user.setAge(24);
    List<User> allList=userMapper.findAllF2(user);
    allList.forEach(n ->System.out.println(n));
  }

MyBatis的动态Sql查询(七)

传入一个name 参数，传入一个age 参数，不传参数，都是正确的，只是拼接的sql 语句不一样而已。

三. where 语句

在if 中写的 1=1 虽然避免了错误，但总是不合理的，应该直接对参数进行相应的解析。如果没有传入值，应该是 select * from user, 而不是后面再跟上 where 1=1 , 故可以用where 语句进行。 where 语句中条件正确成立后，才会加上 where 语句。

故接口为:

List<User> findAllF3(User user);

sql语句:

<select id="findAllF3" parameterType="user" resultMap="userResultMap">
    select * from user t
    <where>
      <if test="name!=null and name!=''">
        and t.name like concat('%',#{name},'%')
      </if>
      <if test="age!=null">
        and t.age>#{age}
      </if>
    </where>
  </select>

测试方法:

@Test
  public void findAllF3Test(){
    SqlSession sqlSession=SqlSessionFactoryUtils.getSession();
    UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
    User user=new User();
    //user.setName("蝴蝶飞");  //填写时， 不填写时不又其作为查询条件。
    //两个条件时
    user.setName("蝴蝶飞");
    user.setAge(24);
    List<User> allList=userMapper.findAllF3(user);
    allList.forEach(n ->System.out.println(n));
  }

传入两个参数是:

MyBatis的动态Sql查询(七)

传入一个 name 参数为:

MyBatis的动态Sql查询(七)

传入一个 age 参数为:

MyBatis的动态Sql查询(七)

不传入参数为:

MyBatis的动态Sql查询(七)

这样，就可以达到去除 1=1 的效果了。

四. trim() 去除前后缀

<select id="findAllF3" parameterType="user" resultMap="userResultMap">
    select * from user t  <!-- 去掉第一个and -->
    <trim prefix="where" prefixOverrides="and">
      <if test="name!=null and name!=''">
        and t.name like concat('%',#{name},'%')
      </if>
      <if test="age!=null">
        and t.age>#{age}
      </if>
    </trim>
  </select>

前缀 prefix 是 where , 去除前缀注解 prefixOverrides 的and , 即去除where 后的第一个and 关键字，这样就可以在每一个语句后面跟上 and 而不出错了。
除了前缀之外，还有后缀 suffix 和对应的suffixOverrides 。

常见的是前缀，去除的是 and 或者是 or.

五. choose (when,otherwise) 多分支选择

在前面的两个参数 name,age 时查询，是多条件查询， name 和sex 是没有先后顺序的。而在业务当中，常常是有先后顺序的。如 name 有值，则查询name, name 无值则去查询age, 如果age 仍然没有值，则保证 description 描述不能为空，查询的条件之间是有明显的先后顺序的。这个时候，就需要用 choose 了。

接口:

//有顺序的查询
  List<User> findAllF4(User user);

sql 语句: (先用以前的 1=1 模式)

<select id="findAllF4" parameterType="user" resultMap="userResultMap">
    select * from user t where 1=1
    <!--总的选择开头-->
    <choose>
      <!--对每一种情况进行判断-->
      <when test="name!=null and name!=''">
        and t.name like concat('%',#{name},'%')
      </when>
      <when test="age!=null">
        and t.age>#{age}
      </when>
      <!--所有条件都不符合，执行 otherwise -->
      <otherwise>
        and t.description is not null
      </otherwise>
    </choose>
  </select>

测试方法:

@Test
  public void findAllF4Test(){
    SqlSession sqlSession=SqlSessionFactoryUtils.getSession();
    UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
    User user=new User();
    //user.setName("蝴蝶飞");  //填写时， 不填写时不又其作为查询条件。
    //两个条件时  有一定的先后顺序。
    user.setName("蝴蝶飞");
    user.setAge(24);
    List<User> allList=userMapper.findAllF4(user);
    allList.forEach(n ->System.out.println(n));
  }

如果name 有值的话:

MyBatis的动态Sql查询(七)

如果 name 无值, age 有值的话:

MyBatis的动态Sql查询(七)

如果 name 和age 均没有值的话:

MyBatis的动态Sql查询(七)

这样，就达到了有先后顺序的查询了。

转换成 where 语句为:

<select id="findAllF4" parameterType="user" resultMap="userResultMap">
    select * from user t
    <where>
      <choose>
        <when test="name!=null and name!=''">
          and t.name like concat('%',#{name},'%')
        </when>
        <when test="age!=null">
          and t.age>#{age}
        </when>
        <otherwise>
          and t.description is not null
        </otherwise>
      </choose>
    </where>
  </select>

六. set 更新部分字段

关于update 语句的用法，可以看第三章，那个时候，更新都是更新全部的字段，无法进行部分字段的更新。而以前所讲的Hibernate 只能进行全部字段的更新，这也是Hibernate 效率低的原因之一。

<update id="updateUser" parameterType="user">
    update user set name=#{name},sex=#{sex},age=#{age},description=#{description}
    where id=#{id}
  </update>

而MyBatis 通过 set 语句，很好的解决了这一点。

接口:

int updateF5(User user);

sql 语句:

<update id="updateF5" parameterType="user">
    update user 
    <set>
      <if test="name!=null and name!=''">
        name=#{name},
      </if>
      <if test="age!=null">
        age=#{age},
      </if>
      <if test="description !=null">
        description=#{description}
      </if>
    </set>
    where id=#{id}
  </update>

六.一查询数据库更新

@Test
  public void findAllF51Test(){
    SqlSession sqlSession=SqlSessionFactoryUtils.getSession();
    UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
    User user=userMapper.getById(1);
    user.setName("部分更新字段123");
    user.setDescription("只修改部分字段");
    userMapper.updateF5(user);
    sqlSession.commit();
  }

MyBatis的动态Sql查询(七)

会将数据库中查询出来的值进行判断，如果有值，那么就进行更新，没有值，就不更新。如果将此时的 age字段在数据库中设置为null 的话.

MyBatis的动态Sql查询(七)

再次运行的话:

MyBatis的动态Sql查询(七)

发现，这个时候就不会更新 age 字段了。

六.二设置对象bean 更新

@Test
  public void findAllF52Test(){
    SqlSession sqlSession=SqlSessionFactoryUtils.getSession();
    UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
    User user=new User();
    user.setId(1);
    user.setName("部分更新字段");
    user.setDescription("只修改部分字段");
    userMapper.updateF5(user);
    sqlSession.commit();
  }

这个时候，运行的话，是不会更新其他的字段的。

MyBatis的动态Sql查询(七)

七. foreach 查询

在数据库中查询的话，常常会查询id 的集合，进行批量的查询。常见的形式有数组和List 集合的形式。遍历查询，用的是 foreach .
属性有:
collection 为集合， item 为循环的变量, index 为索引, open 为开始, close 为结束，separator 为分隔符。
如果是单个的数组，那么可以 collection=“array”,
如果是单个的集合，那么可以 collection=“list”
如果是多个的话，那么可以用Map 或者是@Param 注解进行传参。

七.一数组形式

接口:

//数组形式
  List<User> findAllF6(Integer[] ids);

sql语句:

<select id="findAllF6" parameterType="list" resultMap="userResultMap">
    select * from user t
    <where>
      id in 
      <foreach collection="array" item="id" index="index" open="(" separator="," close=")">
        #{id}
      </foreach>
    </where>
  </select>

测试方法:

@Test
  public void findAllF61Test(){
    SqlSession sqlSession=SqlSessionFactoryUtils.getSession();
    UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
    List<User> allList=userMapper.findAllF6(new Integer[]{1,3,4,5});
    allList.forEach(n ->System.out.println(n));
  }

MyBatis的动态Sql查询(七)

七.二集合形式

接口:

List<User> findAllF6(List<Integer> ids);

sql 语句:

<select id="findAllF6" parameterType="list" resultMap="userResultMap">
    select * from user t
    <where>
      id in 
      <foreach collection="list" item="id" index="index" open="(" separator="," close=")">
        #{id}
      </foreach>
    </where>
  </select>

测试方法:

@Test
  public void findAllF6Test(){
    SqlSession sqlSession=SqlSessionFactoryUtils.getSession();
    UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
    List<Integer> ids=Arrays.asList(1,3,4);
    List<User> allList=userMapper.findAllF6(ids);
    allList.forEach(n ->System.out.println(n));
  }

MyBatis的动态Sql查询(七)

七.三多值时用@Param 注解

接口:

List<User> findAllF6(@Param(value="ids") List<Integer> ids,
      @Param(value="sexs") List<String> sexs);

sql 语句:

<select id="findAllF6" parameterType="list" resultMap="userResultMap">
    select * from user t
    <where>
      id in 
      <foreach collection="ids" item="id" index="index" open="(" separator="," close=")">
        #{id}
      </foreach>
      and sex in 
      <foreach collection="sexs" item="sex" index="index" open="(" separator="," close=")">
        #{sex}
      </foreach>
    </where>
  </select>

测试方法:

@Test
  public void findAllF62Test(){
    SqlSession sqlSession=SqlSessionFactoryUtils.getSession();
    UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
    List<Integer> ids=Arrays.asList(1,3,4);
    List<String> sexs=Arrays.asList("男","女");
    List<User> allList=userMapper.findAllF6(ids,sexs);
    allList.forEach(n ->System.out.println(n));
  }

MyBatis的动态Sql查询(七)

foreach 的用法很重要。

八. bind 绑定

通过OGNL 表达式定义一个上下文变量，方便我们使用。如字符串拼接时， mysql 中拼接是 concat() 函数，而oracle 是 || 进行拼接，这样可以在用 like 语句时要根据不同的数据库来更新语句。而 bind 语句则解决了这种问题。

接口:

List<User> findAllF7(@Param(value="name") String name,@Param(value="description") String description);

sql 语句:

<select id="findAllF7" parameterType="list" resultMap="userResultMap">
    select * from user t
    <where>
      <!-- 先定义变量 pattern_name 和pattern_description -->
      <bind name="pattern_name" value="'%'+name+'%'"/>
      <bind name="pattern_description" value="'%'+description+'%'"/>
      
      <if test="name!=null and name!=''">
        <!-- 引入变量 pattern_name -->
        and name like #{pattern_name}
      </if>
      <if test="description!=null and description!=''">
        <!-- 引入变量 pattern_description -->
        and description like #{pattern_description}
      </if>
    </where>
  </select>

测试方法:

@Test
  public void findAllF7Test(){
    SqlSession sqlSession=SqlSessionFactoryUtils.getSession();
    UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
    List<User> allList=userMapper.findAllF7("蝴蝶","蝴蝶");
    allList.forEach(n ->System.out.println(n));
  }

MyBatis的动态Sql查询(七)

这就是 bind 的常见用法。

谢谢!!!

活动

应用商城

合作伙伴

开发者

支持与服务

了解天翼云

MyBatis的动态Sql查询(七)

MyBatis的动态Sql查询(七)

一. 动态Sql 查询

二. if元素

二.一 以前不带参数的查询

二.二 带参数的简单查询

二.三 添加1=1 避免sql语句组装不正确

三. where 语句

四. trim() 去除前后缀

五. choose (when,otherwise) 多分支选择

六. set 更新部分字段

六.一 查询数据库更新

六.二 设置对象bean 更新

七. foreach 查询

七.一 数组形式

七.二 集合形式

七.三 多值时 用@Param 注解

八. bind 绑定

相关文章

MySQL索引的类型与优化方法

SQL数据库学习：从基础到进阶的系统化教程

SQL SELECT语句的优化与性能提升策略

SQL DISTINCT关键字的使用场景与优化建议

优化Spring Boot应用的数据库访问性能

使用Hibernate实现复杂数据库查询优化策略

基于Java的美食推荐管理系统设计与实现(源码+lw+部署文档+讲解等)

Java中的高效数据查询与索引优化

MySQL经典面试题：谈一谈你对事务的理解

MySQL：CRUD进阶

作者介绍

最新文章

SQL SELECT语句的优化与性能提升策略

SQL DISTINCT关键字的使用场景与优化建议

SQL查询全揭秘：从流程到优化，EXPLAIN命令的完美应用

LINUX随笔九 nohup失效和镜像源

Mybatis 示例之 foreach （上）

cube.js elasticsearch 官方sql 扩展使用

热门文章

关于用户表根据userId分库后根据userName查询问题的思考

MyBatis-Plus 批量插入

Apache-DBUtils

ES7-SQL操作

Lucene查询语法和使用

SqlServer查询计划

热门标签

相关产品

弹性云主机

天翼云电脑（公众版）

对象存储

云硬盘

随机文章

ElasticSearch中结构化查询（term、terms、range、exists、match、bool）

Lucene查询语法和使用

MyBatis - 基础学习9 - 动态sql（sql片段，foreach）

MyBatis - 基础学习4 - xml的核心配文件（优化）

[spring学习]12、JdbcTemplate常见增删改查操作

单表的CRUD操作改造

二.一以前不带参数的查询

二.二带参数的简单查询

二.三添加1=1 避免sql语句组装不正确

六.一查询数据库更新

六.二设置对象bean 更新

七.一数组形式

七.二集合形式

七.三多值时用@Param 注解