手册   游戏   WEB   字典   单词   在线工具  

[Mybatis] MyBatis 之动态 SQL(六)

MyBatis 的强大特性之一便是它的动态 SQL, 本章介绍动态 SQL

查看本章, 请先阅读 [Mybatis] MyBatis 对表执行 CRUD 操作 (三) https://www.cnblogs.com/h--d/p/10264650.html .

本例表结构

  1. CREATE TABLE `employee` (
  2.    `id` int(11) NOT NULL AUTO_INCREMENT,
  3.    `last_name` varchar(255) DEFAULT NULL,
  4.    `gender` char(1) DEFAULT NULL,
  5.    `email` varchar(255) DEFAULT NULL
  6.    PRIMARY KEY (`id`)
  7.  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  8. if

示例

1,EmployeeMapper 新增 Sql 如下:

  1. <!-- if: 判断 -->
  2.  <select id="testConditionIf"
  3.      resultType="com.hd.test.pojo.Employee">
  4.      select id, last_name lastName, gender from employee
  5.      <!--
  6.          test: 判断表达式 (OGNL) OGNL 参照 PPT 或者官方文档.
  7.          c:if test 从参数中取值进行判断 遇见特殊符号应该去写转义字符: &&:
  8.       -->
  9.      where 1 = 1
  10.      <if test="id != null">
  11.          AND id = #{id}
  12.      </if>
  13.      <!-- 表达式中, 字符串使用''单引号引起来 -->
  14.      <if test="lastName != null and lastName.trim() !=''">
  15.          AND last_name = #{lastName}
  16.      </if>
  17.      <!-- ognl 会进行字符串与数字的转换判断  "0"==0 -->
  18.      <if test="gender==0 or gender==1">
  19.          AND gender = #{gender}
  20.      </if>
  21.      <!-- 转义字符: && == &&    "" == ""-->
  22.      <if test="email != null && lastName!=""">
  23.          AND email = #{email}
  24.      </if>
  25.  </select>

2,EmployeeMapper 接口中, 新增方法

1 public List<Employee> testConditionIf(Employee employee);

3, 测试方法

  1. @Test
  2.  public void test001() throws IOException {
  3.      InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
  4.      SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
  5.      SqlSession session = sessionFactory.openSession();
  6.      try {
  7.          EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
  8.          List<Employee> list = mapper.testConditionIf(new Employee(null, "0", null));
  9.          System.out.println(list.size());
  10.          for (Employee employee : list) {
  11.              System.out.println(list);
  12.          }
  13.      } catch (Exception e) {
  14.          e.printStackTrace();
  15.      } finally {
  16.          session.close();
  17.      }
  18.  }

4, 执行结果

choose

有时我们不想应用到所有的条件语句, 而只想从中择其一项. 针对这种情况, MyBatis 提供了 choose 元素, 它有点像 Java 中的 switch 语句.

示例

1,EmployeeMapper 新增 Sql 如下:

  1. <!-- choose -->
  2.  <select id="testConditionChoose" resultType="com.hd.test.pojo.Employee">
  3.      select id, last_name lastName, gender from employee
  4.      where 1 = 1
  5.      <choose>
  6.          <when test="id != null">
  7.              AND id = #{id}
  8.          </when>
  9.          <when test="lastName != null">
  10.              AND last_name = #{lastName}
  11.          </when>
  12.          <when test="gender != null">
  13.              AND gender = #{gender}
  14.          </when>
  15.          <when test="email != null">
  16.              AND email = #{email}
  17.          </when>
  18.          <otherwise>
  19.              AND 1 = 1
  20.          </otherwise>
  21.      </choose>
  22.  </select>

2,EmployeeMapper 接口中, 新增方法

1 public List<Employee> testConditionChoose(Employee employee);

3, 测试方法

  1. @Test
  2.  public void test002() throws IOException {
  3.      InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
  4.      SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
  5.      SqlSession session = sessionFactory.openSession();
  6.      try {
  7.          EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
  8.          List<Employee> list = mapper.testConditionChoose(new Employee(1, null, null, null));
  9.          System.out.println(list.size());
  10.          for (Employee employee : list) {
  11.              System.out.println(list);
  12.          }
  13.      } catch (Exception e) {
  14.          e.printStackTrace();
  15.      } finally {
  16.          session.close();
  17.      }
  18.  }

4, 执行结果

where

  1. <!-- if + where -->
  2.  <select id="testConditionIfWhere"
  3.      resultType="com.hd.test.pojo.Employee">
  4.      select id, last_name lastName, gender from employee
  5.      <where>
  6.          <if test="id != null">
  7.              AND id = #{id}
  8.          </if>
  9.          <if test="lastName != null and lastName.trim() !=''">
  10.              AND last_name = #{lastName}
  11.          </if>
  12.          <if test="gender==0 or gender==1">
  13.              AND gender = #{gender}
  14.          </if>
  15.          <if test="email != null && lastName!=""">
  16.              AND email = #{email}
  17.          </if>
  18.      </where>
  19.  </select>
  1. @Test
  2.  public void test003() throws IOException {
  3.      InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
  4.      SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
  5.      SqlSession session = sessionFactory.openSession();
  6.      try {
  7.          EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
  8.          List<Employee> list = mapper.testConditionIfWhere(new Employee("小红", "1", null));
  9.          System.out.println(list.size());
  10.          for (Employee employee : list) {
  11.              System.out.println(list);
  12.          }
  13.      } catch (Exception e) {
  14.          e.printStackTrace();
  15.      } finally {
  16.          session.close();
  17.      }
  18.  }
  1. <select id="testConditionIfTrim" resultType="com.hd.test.pojo.Employee">
  2.      select id, last_name lastName, gender from employee
  3.      <!-- 后面多出的 and 或者 or where 标签不能解决
  4.          prefix="": 前缀: trim 标签体中是整个字符串拼串 后的结果. prefix 给拼串后的整个字符串加一个前缀
  5.          prefixOverrides="": 前缀覆盖: 去掉整个字符串前面多余的字符
  6.          suffix="": 后缀 suffix 给拼串后的整个字符串加一个后缀
  7.          suffixOverrides="" 后缀覆盖: 去掉整个字符串后面多余的字符 -->
  8.      <trim prefix="where" prefixOverrides="AND">
  9.          <if test="id != null">
  10.              AND id = #{id}
  11.          </if>
  12.          <if test="lastName != null">
  13.              AND last_name = #{lastName}
  14.          </if>
  15.          <if test="gender != null">
  16.              AND gender = #{gender}
  17.          </if>
  18.          <if test="email != null">
  19.              AND email = #{email}
  20.          </if>
  21.      </trim>
  22.  </select>
  1. @Test
  2.  public void test004() throws IOException {
  3.      InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
  4.      SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
  5.      SqlSession session = sessionFactory.openSession();
  6.      try {
  7.          EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
  8.          List<Employee> list = mapper.testConditionIfTrim(new Employee(1, null, null, null));
  9.          System.out.println(list.size());
  10.          for (Employee employee : list) {
  11.              System.out.println(list);
  12.          }
  13.      } catch (Exception e) {
  14.          e.printStackTrace();
  15.      } finally {
  16.          session.close();
  17.      }
  18.  }
  1. <!-- foreach -->
  2.  <select id="testConditionForeach" resultType="com.hd.test.pojo.Employee">
  3.      select id, last_name lastName, gender from employee
  4.      <!--
  5.           collection: 指定要遍历的集合:
  6.               list 类型的参数会特殊处理封装在 map 中, map 的 key 就叫 list
  7.           item: 将当前遍历出的元素赋值给指定的变量
  8.           separator: 每个元素之间的分隔符
  9.           open: 遍历出所有结果拼接一个开始的字符
  10.           close: 遍历出所有结果拼接一个结束的字符
  11.           index: 索引. 遍历 list 的时候是 index 就是索引, item 就是当前值
  12.                         遍历 map 的时候 index 表示的就是 map 的 key,item 就是 map 的值
  13.           #{变量名} 就能取出变量的值也就是当前遍历出的元素
  14.         -->
  15.      <foreach collection="ids" item="id" separator="," open="where id in (" close=")">
  16.          #{id}
  17.      </foreach>
  18.  </select>
  1. @Test
  2.  public void test005() throws IOException {
  3.      InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
  4.      SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
  5.      SqlSession session = sessionFactory.openSession();
  6.      try {
  7.          EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
  8.          List<Employee> list = mapper.testConditionForeach(Arrays.asList(1, 2, 3, 4));
  9.          System.out.println(list.size());
  10.          for (Employee employee : list) {
  11.              System.out.println(list);
  12.          }
  13.      } catch (Exception e) {
  14.          e.printStackTrace();
  15.      } finally {
  16.          session.close();
  17.      }
  18.  }
  1. <!-- set -->
  2.  <update id="testConditionSet">
  3.      update employee
  4.      <set>
  5.          <if test="lastName != null">
  6.              last_name = #{lastName},
  7.          </if>
  8.          <if test="gender != null">
  9.              gender = #{gender},
  10.          </if>
  11.          <if test="email != null">
  12.              email = #{email},
  13.          </if>
  14.      </set>
  15.      where id = #{id}
  16.  </update>
  1. @Test
  2.  public void test006() throws IOException {
  3.      InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
  4.      SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
  5.      SqlSession session = sessionFactory.openSession();
  6.      try {
  7.          EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
  8.          boolean f = mapper.testConditionSet(new Employee(1, "小白", "1", null));
  9.          System.out.println(f);
  10.          session.commit();
  11.      } catch (Exception e) {
  12.          e.printStackTrace();
  13.      } finally {
  14.          session.close();
  15.      }
  16.  }
  1. <select id="testConditionBind" resultType="Blog">
  2.      <bind name="pattern" value="'%' + keyword + '%'" />
  3.      SELECT * FROM employee  WHERE last_name LIKE #{pattern}
  4.  </select>
  5. sql
  1. <sql id="selectSql">select id, last_name lastName, gender from employee</sql>
  2.  <select id="testConditionInclude" resultType="com.hd.test.pojo.Employee">
  3.      <include refid="selectSql"></include>
  4.      where id = 1
  5.  </select>

来源: http://www.bubuko.com/infodetail-2935901.html


与本文相关文章

  1. mybatis 动态 SQL 操作之插入学习笔记
  2. Mybatis 入门之动态 sql
  3. MyBatis 构造动态 SQL 语句
  4. Mybatis 动态 Sql 语句《常用》
  5. 9,SpringBoot+Mybatis 整合 ------ 动态 sql
  6. mybatis 之旅第五篇 - 动态 SQL
  7. mybatis动态sql
  8. Mybatis 学习系列 (三) 动态 SQL
暂无,快来抢沙发吧！
更多





黑ICP备17005318号-1