mybatis基础系列（三）——动态sql

时间：2023-03-08 20:38:25

本文是Mybatis基础系列的第三篇文章，点击下面链接可以查看前面的文章：

mybatis基础系列（二）——基础语法、别名、输入映射、输出映射

mybatis基础系列（一）——mybatis入门

动态sql

MyBatis 的强大特性之一便是它的动态 SQL。摆脱了JDBC中根据不同条件拼接 SQL 语句的痛苦。动态 SQL可以帮我们解决复杂需求。mybatis 动态SQL，通过 if, choose, when, otherwise, trim, where, set, foreach等标签组合成非常灵活的SQL语句。

根据员工编号或者员工姓名查询员工信息，输入的员工编号可能为空。 

<select id="queryByEnameOrEempno" parameterType="com.itpsc.request.EmpRequest" resultType="com.itpsc.vo.EmpVo">

    SELECT * FROM t_emp WHERE

    <if test="emp.ename!=null">

      ename=#{emp.ename}

    </if>

    <if test="emp.empno!=null">

      and empno=#{emp.empno}

    </if>

  </select>

执行结果： 

编号为空

==>  Preparing: SELECT * FROM t_emp WHERE ename=?

==> Parameters: itpsc(String)

<==    Columns: empno, ename, job, mgr, hiredate, sal, comm, deptno

<==        Row: 7100, itpsc, developer, 7902, 1980-01-10, null, 1000.00, 20

<==      Total: 1

姓名为空

==>  Preparing: SELECT * FROM t_emp WHERE and empno=?

==> Parameters: 7100(Integer)

从执行结果可以看出，上面的if语句只能是在员工姓名不能为空的情况下执行，如果员工姓名为空sql语句就出错了，If+where语句可以解决这个问题。

if+where语句与trim 语句

<select id="queryByEnameOrEempno" parameterType="com.itpsc.request.EmpRequest" resultType="com.itpsc.vo.EmpVo">

    SELECT * FROM t_emp

    <where>

      <if test="emp.ename!=null">

        and ename=#{emp.ename}

      </if>

      <if test="emp.empno!=null">

        and empno=#{emp.empno}

      </if>

    </where>

  </select>

执行结果：

姓名为空

==>  Preparing: SELECT * FROM t_emp WHERE empno=?

==> Parameters: 7100(Integer)

<==    Columns: empno, ename, job, mgr, hiredate, sal, comm, deptno

<==        Row: 7100, itpsc, developer, 7902, 1980-01-10, null, 1000.00, 20

<==      Total: 1

编号为空

==>  Preparing: SELECT * FROM t_emp WHERE ename=?

==> Parameters: itpsc(String)

<==    Columns: empno, ename, job, mgr, hiredate, sal, comm, deptno

<==        Row: 7100, itpsc, developer, 7902, 1980-01-10, null, 1000.00, 20

<==      Total: 1

<where>标签中包含的标签中有返回值的话，它就插入一个where关键字，并且where 标签会自动将其后第一个条件的and或者是or给忽略掉。

trim语句可以实现<where>标签类似的功能：

（1）trim标签可以在包含的内容前加上前缀，也可以在其后加上后缀，对应的属性是prefix和suffix；

（2）trim标签可以把包含内容的首部某些内容忽略，也可以把尾部的某些内容忽略，对应的属性是prefixOverrides和suffixOverrides。 

<select id="queryByEnameOrEempno" parameterType="com.itpsc.request.EmpRequest" resultType="com.itpsc.vo.EmpVo">

    SELECT * FROM t_emp

    <trim prefix="where" prefixOverrides="and | or">

      <if test="emp.ename!=null">

        and ename=#{emp.ename}

      </if>

      <if test="emp.empno!=null">

        and empno=#{emp.empno}

      </if>

    </trim>

  </select>

执行结果： 

empno为空

==>  Preparing: SELECT * FROM t_emp where ename=?

==> Parameters: itpsc2(String)

<==    Columns: empno, ename, job, mgr, hiredate, sal, comm, deptno

<==        Row: 7101, itpsc2, developer, 7902, 1980-01-10, 2000.00, 1000.00, 20

<==      Total: 1

ename为空

==>  Preparing: SELECT * FROM t_emp where empno=?

==> Parameters: 7100(Integer)

<==    Columns: empno, ename, job, mgr, hiredate, sal, comm, deptno

<==        Row: 7100, itpsc1, mannager, 7902, 1980-01-10, null, 1000.00, 20

<==      Total: 1

empno、ename都不为空

==>  Preparing: SELECT * FROM t_emp where ename=? and empno=?

==> Parameters: itpsc2(String), 7100(Integer)

<==      Total: 0

if+set语句与trim语句

条件判断用<where>标签，同理更新操作用<set>标签。 

<select id="updateEnameOrJob" parameterType="com.itpsc.request.EmpRequest">

    UPDATE t_emp

    <set>

      <if test="emp.ename!=null">

        ename=#{emp.ename}

      </if>

      <if test="emp.job!=null">

        job=#{emp.job}

      </if>

    </set>

    WHERE empno=#{emp.empno}

  </select>

运行结果： 

job为空

==>  Preparing: UPDATE t_emp SET ename=? WHERE empno=?

==> Parameters: hello(String), 7100(Integer)

ename 为空

==>  Preparing: UPDATE t_emp SET job=? WHERE empno=?

==> Parameters: itpsc(String), 7100(Integer)

<set>标签中包含的标签中有返回值的话，它就插入一个set关键字。job和ename都不为空则sql错误，那又如何写呢，trim标签可以帮我们能实现。 

<select id="updateEnameOrJob" parameterType="com.itpsc.request.EmpRequest">

    UPDATE t_emp

    <trim prefix="set" suffixOverrides=",">

      <if test="emp.ename!=null">

        ename=#{emp.ename},

      </if>

      <if test="emp.job!=null">

        job=#{emp.job},

      </if>

    </trim>

    WHERE empno=#{emp.empno}

  </select>

运行结果： 

==>  Preparing: UPDATE t_emp set ename=?, job=? WHERE empno=?

==> Parameters: itpsc(String), mannager(String), 7100(Integer)

choose(when,otherwise) 语句

上的例子中，job和ename都不为空则是一种条件，job为空且ename不为空是一种条件，ename为空且job不为空是一种条件。实际上，我们要只要满足任意一个条件，就可以执行。choose(when,otherwise) 语句可以帮我们解决，类似jstl。 

<select id="updateEnameOrJob" parameterType="com.itpsc.request.EmpRequest">

    UPDATE t_emp

    <set>

      <choose>

        <when test="emp.ename!=null and emp.job!=null">

          ename=#{emp.ename},job=#{emp.job}

        </when>

        <otherwise>

          <if test="emp.ename!=null">

            ename=#{emp.ename}

          </if>

          <if test="emp.job!=null">

            job=#{emp.job}

          </if>

        </otherwise>

      </choose>

    </set>

    WHERE empno=#{emp.empno}

  </select>

执行结果： 

ename为空且job不为空

==>  Preparing: UPDATE t_emp SET job=? WHERE empno=?

==> Parameters: mannager(String), 7100(Integer)

job为空且ename不为空

==>  Preparing: UPDATE t_emp SET ename=? WHERE empno=?

==> Parameters: itpsc2(String), 7100(Integer)

job和ename都不为空

==>  Preparing: UPDATE t_emp SET ename=?,job=? WHERE empno=?

==> Parameters: itpsc2(String), mannager(String), 7100(Integer)

foreach 语句

需求：根据员工编号列表查询员工信息。7369，7499，7521。

Sql的写法是：select * from t_emp where 1=1 and empno=7369 or empno=7499 or empno=7521

mybatis的foreach语句可以帮我们实现类似功能。 

<select id="queryByIds"

parameterType="com.itpsc.request.IdRequest" resultType="com.itpsc.vo.EmpVo">

    SELECT * FROM t_emp

    <where>

      <foreach collection="ids" item="id" open="and (" close=")" separator="or">

        empno=#{id}

      </foreach>

    </where>

  </select>

collection:指定输入对象中的集合属性

item:每次遍历生成的对象

open:开始遍历时的拼接字符串

close:结束时拼接的字符串

separator:遍历对象之间需要拼接的字符串

运行结果: 

==>  Preparing: SELECT * FROM t_emp WHERE ( empno=? or empno=? or empno=? )

==> Parameters: 7369(Integer), 7499(Integer), 7521(Integer)

<==    Columns: empno, ename, job, mgr, hiredate, sal, comm, deptno

<==        Row: 7369, SMITH, CLERK, 7902, 1980-12-17, 800.00, null, 20

<==        Row: 7499, ALLEN, SALESMAN, 7698, 1981-02-20, 1600.00, 300.00, 30

<==        Row: 7521, WARD, SALESMAN, 7698, 1981-02-22, 1250.00, 500.00, 30

<==      Total: 3

sql片段

将上面动态sql代码块抽取出来，组成一个sql片段，其它的statement中就可以引用该sql片段。提供代码复用性，方便团队成员之间进行开发。 

<select id="queryCount2" parameterType="com.itpsc.request.EmpRequest" resultType="int">

    SELECT count(*) FROM t_emp

    <where>

      <if test="emp!=null">

        <if test="emp.deptno!=null">

          and emp.deptno=#{emp.deptno}

        </if>

        <if test="emp.ename!=null">

          and emp.ename=#{emp.ename}

        </if>

        <if test="emp.job!=null">

          and emp.job=#{emp.job}

        </if>

      </if>

    </where>

  </select>

上面where语句中代码，我们可以通过<sql>标签封装成一个sql片段，然后在其它statement中通过<include>引用。 

<sql id="querySql">

    <if test="emp!=null">

      <if test="emp.deptno!=null">

        and emp.deptno=#{emp.deptno}

      </if>

      <if test="emp.ename!=null">

        and emp.ename=#{emp.ename}

      </if>

      <if test="emp.job!=null">

        and emp.job=#{emp.job}

      </if>

    </if>

  </sql>

  <select id="queryCount2" parameterType="com.itpsc.request.EmpRequest" resultType="int">

    SELECT count(*) FROM t_emp

    <where>

      <include refid="querySql"></include>

    </where>

  </select>

本文到此，下篇 mybatis基础系列（四）——关联查询、延迟加载、一级缓存与二级缓存。

相关文章