MyBatis（四）——动态SQL

最新推荐文章于 2024-06-02 14:01:58 发布

老臣、

最新推荐文章于 2024-06-02 14:01:58 发布

阅读量4.7k

点赞数 3

分类专栏： Java MyBatis Spring技术栈

本文链接：https://blog.csdn.net/vae1314chuanchen/article/details/79510870

版权

Java 同时被 3 个专栏收录

35 篇文章 0 订阅

订阅专栏

Spring技术栈

19 篇文章 3 订阅

订阅专栏

MyBatis

9 篇文章 0 订阅

订阅专栏

概述

　　MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其他类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句有多么痛苦。拼接的时候要确保不能忘了必要的空格，还要注意省掉列名列表最后的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。
　　
　　通常使用动态 SQL 不可能是独立的一部分,MyBatis 当然使用一种强大的动态 SQL 语言来改进这种情形,这种语言可以被用在任意的 SQL 映射语句中。动态 SQL是可以极大的简化我们拼装SQL的操作。

if
choose (when, otherwise)
trim (where, set)
foreach

MyBatis中文版文档：

http://www.mybatis.org/mybatis-3/zh/dynamic-sql.html

if

　　if就是简单的条件判断，利用if语句我们可以实现某些简单的条件选择。
场景一：
　　查询员工：要求携带了哪个字段，查询条件就带上这个字段的值。如：传入了id就按id查寻，传入了lastName就加上lastName，用id加lastName作为查询条件。
　　示例代码：
　　

     <select id="getEmpsByConditionIf" parameterType="com.mybatis.bean.Employee" resultType="com.mybatis.bean.Employee">
        select * from tbl_employee
        WHERE
            <!-- test：判断表达式（OGNL）
                 从参数中取值进行判断
             -->
            <if test="id!=null">
                id=#{id}
            </if>
            <if test="lastName!=null and lastName!=''">
                and last_name like #{lastName}
            </if>
            <if test="email!=null and email.trim()!=''">
                and email=#{email}
            </if>
            <if test="gender==0 or gender==1">
                and gender=#{gender}
            </if>
     </select>

    EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
    Employee employee = new Employee(1, "tom", null, null,null);
    List<Employee> emps = mapper.getEmpsByConditionIf(employee);
    for (Employee emp:emps) {
        System.out.println(emp);
    }

　　上述代码查询的是id为1，切员工名为tom的员工，可以看到数据库中有这条信息，输出如下：

Employee [id=1, lastName=tom, email=912324@qq.com, gender=0]

　　接下来看一个异常：
　　如果我不带id，只查询员工名是tom的员工能不能查出来呢？
　　

    Employee employee = new Employee(null, "tom", null, null,null);
    List<Employee> emps = mapper.getEmpsByConditionIf(employee);

这里写图片描述
　　
　　可以看到报了SQL异常：
　　

select * from tbl_employee  WHERE and last_name like ?

　　其原因就是在拼接SQL的时候多拼接了一个and。
解决方法：
　　1）我们在拼接SQL之前添加WHERE 1=1，如下所示：
　　

     <select id="getEmpsByConditionIf" parameterType="com.mybatis.bean.Employee" resultType="com.mybatis.bean.Employee">
        select * from tbl_employee
        WHERE 1=1
            <if test="id!=null">
                id=#{id}
            </if>
            <if test="lastName!=null and lastName!=''">
                and last_name like #{lastName}
            </if>
            <if test="email!=null and email.trim()!=''">
                and email=#{email}
            </if>
            <if test="gender==0 or gender==1">
                and gender=#{gender}
            </if>
     </select>

　　可以看到控制台打印了一条记录，说明查询成功。看下拼接后的SQL：
　　

Preparing: select * from tbl_employee WHERE 1=1 and last_name like ?

　　2）见后面的where标签。

choose

　　choose元素的作用就相当于JAVA中的switch语句，通常与when和otherwise搭配。
场景二：
　　查询员工：如果带了id就用id查，如果带了lastName就用lastName查;如果带了id和lastName，就只会进入其中一个。
　　示例代码：
　　

     <select id="getEmpsByConditionChoose" resultType="com.mybatis.bean.Employee">
        select * from tbl_employee
        <where>
            <choose>
                <when test="id!=null">
                    id=#{id}
                </when>
                <when test="lastName!=null">
                    last_name like #{lastName}
                </when>
                <when test="email!=null">
                    email = #{email}
                </when>
                <otherwise>
                    gender = 0
                </otherwise>
            </choose>
        </where>
     </select>

　　when元素表示当when中的条件满足的时候就拼接其中的sql，跟JAVA中的switch效果差不多的是按照条件的顺序，当when中有条件满足的时候，就会跳出choose，即所有的when和otherwise条件中，只有一个会输出，当所有的我很条件都不满足的时候就输出otherwise中的内容。所以上述语句的意思非常简单，当id!=null的时候就拼接语句id=#{id}，不再往下判断条件，当id为空且content!=null的时候就拼接语句last_name like #{lastName}，当所有条件都不满足的时候就拼接otherwise中的内容。

    EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
    Employee employee = new Employee(1, "tom", null, null,null);
    List<Employee> emps = mapper.getEmpsByConditionChoose(employee);

　　执行测试后拼接的SQL为：
　　

Preparing: select * from tbl_employee WHERE id=?

where

　　where语句的作用主要是简化SQL语句中where中的条件判断。
　　
　　使用where语句可以解决上面if中的SQL语句拼接异常的问题。
　　

     <select id="getEmpsByConditionIf" parameterType="com.mybatis.bean.Employee" resultType="com.mybatis.bean.Employee">
        select * from tbl_employee
        <!-- where -->
        <where>
            <if test="id!=null">
                id=#{id}
            </if>
            <if test="lastName!=null and lastName!=''">
                and last_name like #{lastName}
            </if>
            <if test="email!=null and email.trim()!=''">
                and email=#{email}
            </if>
            <if test="gender==0 or gender==1">
                and gender=#{gender}
            </if>
        </where>
     </select>

　　where元素的作用是会在写入where元素的地方拼接一个where，另外一个好处是你不需要考虑where元素里面的条件输出是什么样子的，MyBatis会智能的帮你处理，如果所有的条件都不满足那么MyBatis就会查出所有的记录，如果输出后是and 开头的，MyBatis会把第一个and忽略，当然如果是or开头的，MyBatis也会把它忽略；此外，在where元素中你不需要考虑空格的问题，MyBatis会智能的帮你加上。
　　所以在上述例子中，如果id=null，而lastName!= null，那么输出的整个语句会是select * from tbl_employee WHERE last_name like ? ，而不是select * from tbl_employee WHERE and last_name like ? ，因为MyBatis会智能的把首个and 或 or 给忽略。
　　注意： MyBatis只会智能的把首个and 或 or 给忽略。如果把and写在后面将会失效。
　　例如，有些人喜欢这样写代码：
　　

   <select id="getEmpsByConditionIf" parameterType="com.mybatis.bean.Employee" resultType="com.mybatis.bean.Employee">
        select * from tbl_employee
        <where>
            <if test="id!=null">
                id=#{id} and
            </if>
            <if test="lastName!=null and lastName!=''">
                last_name like #{lastName} and
            </if>
            <if test="email!=null and email.trim()!=''">
                email=#{email} and
            </if>
            <if test="gender==0 or gender==1">
                gender=#{gender}
            </if>
        </where>
    </select>

　　在只传入id的时候会报错，
　　

==> Preparing: select * from tbl_employee WHERE id=? and

　　所以应该知道，MyBatis只会智能的把首个and 或 or 给忽略，在写代码的时候，尽量把and提到前面来写。

set

　　set元素主要是用在更新操作的时候，它的主要功能和where元素其实是差不多的，主要是在包含的语句前拼装一个set，然后如果包含的语句是以逗号结束的话将会把该逗号忽略，如果set包含的内容为空的话则会出错。
　　有了set元素我们就可以动态的更新那些修改了的字段。下面是一段示例代码：
　　

     <update id="updateEmp">
        <!-- Set标签的使用 -->
        update tbl_employee
        <set>
            <if test="lastName!=null">
                last_name=#{lastName},
            </if>
            <if test="email!=null">
                email=#{email},
            </if>
            <if test="gender!=null">
                gender=#{gender}
            </if>
        </set>
        where id=#{id}

　　如果set包含的内容为空的话则会出错。还是SQL语句拼接的错误。
　　

### The error occurred while setting parameters
### SQL: update tbl_employee        where id=?

    EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
    Employee employee = new Employee(1, "Tom", null, "1",null);
    boolean flag = mapper.updateEmp(employee);
    System.out.println(flag);
    openSession.commit();

trim

　　trim元素可以自定义字符串的截取规则。trim元素的主要功能是可以在自己包含的内容前加上某些前缀，也可以在其后加上某些后缀，与之对应的属性是prefix和suffix；可以把包含内容的首部某些内容覆盖，即忽略，也可以把尾部的某些内容覆盖，对应的属性是prefixOverrides和suffixOverrides。
　　
　　prefix=”“：前缀：trim标签体中是整个字符串拼串后的结果。prefix给拼串后的整个字符串加一个前缀。
　　prefixOverrides=”“：前缀覆盖：去掉整个字符串前面多余的字符。
　　suffix=”“：后缀：suffix给拼串后的整个字符串加一个后缀。
　　suffixOverrides=”“：后缀覆盖：去掉整个字符串后面多余的字符。

　　正因为trim有这样的功能，所以我们也可以非常简单的利用trim来代替where元素的功能，用来解决拼接的SQL语句后面多出的and或者or，where标签不能解决的问题。
　　示例代码如下：
　　

     <select id="getEmpsByConditionTrim" resultType="com.mybatis.bean.Employee">
        select * from tbl_employee
        <!-- 后面多出的and或者or where标签不能解决
        prefix="":前缀：trim标签体中是整个字符串拼串 后的结果。
                prefix给拼串后的整个字符串加一个前缀
        prefixOverrides="":
                前缀覆盖： 去掉整个字符串前面多余的字符
        suffix="":后缀
                suffix给拼串后的整个字符串加一个后缀
        suffixOverrides=""
                后缀覆盖：去掉整个字符串后面多余的字符

        -->
        <!-- 自定义字符串的截取规则 -->
        <trim prefix="where" suffixOverrides="and">
            <if test="id!=null">
                id=#{id} and
            </if>
            <if test="lastName!=null and lastName!=' '">
                last_name like #{lastName} and
            </if>
            <if test="email!=null and email.trim()!= ' '">
                email=#{email} and
            </if>
            <!-- ognl会进行字符串与数字的转换判断  "0"==0 -->
            <if test="gender==0 or gender==1">
                gender=#{gender}
            </if>
         </trim>
     </select>

     <update id="updateEmp">
        update tbl_employee
        <trim prefix="set" suffixOverrides=",">
            <if test="lastName!=null">
                last_name=#{lastName},
            </if>
            <if test="email!=null">
                email=#{email},
            </if>
            <if test="gender!=null">
                gender=#{gender}
            </if>
        </trim>
        where id=#{id}
     </update>

foreach

　　动态 SQL 的另外一个常用的必要操作是需要对一个集合进行遍历，通常是在构建 IN 条件语句的时候，它可以在SQL语句中进行迭代一个集合。 foreach元素的属性主要有item，index，collection，open，separator，close。
　　item表示集合中每一个元素进行迭代时的别名。
　　index指定一个名字，用于表示在迭代过程中，每次迭代到的位置。
　　open表示该语句以什么开始。
　　separator表示在每次进行迭代之间以什么符号作为分隔符。
　　close表示以什么结束。
　　
　　在使用foreach的时候最关键的也是最容易出错的就是collection属性，该属性是必须指定的，但是在不同情况下，该属性的值是不一样的，主要有一下3种情况：
　　如果传入的是单参数且参数类型是一个List的时候，collection属性值为list。
　　如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array。
　　如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可以封装成map，实际上如果你在传入参数的时候，在MyBatis里面也是会把它封装成一个Map的，map的key就是参数名，所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key。
场景三：
　　查询给定的id集合里面所有的员工。
　　示例代码如下：
　　

     List<Employee> getEmpsByConditionForeach(@Param("ids") List<Integer> ids);

     <select id="getEmpsByConditionForeach" resultType="com.mybatis.bean.Employee">
        select * from tbl_employee
        <!--
            collection：指定要遍历的集合：
                list类型的参数会特殊处理封装在map中，map的key就叫list
            item：将当前遍历出的元素赋值给指定的变量
            separator:每个元素之间的分隔符
            open：遍历出所有结果拼接一个开始的字符
            close:遍历出所有结果拼接一个结束的字符
            index:索引。遍历list的时候是index就是索引，item就是当前值
                          遍历map的时候index表示的就是map的key，item就是map的值

            #{变量名}就能取出变量的值也就是当前遍历出的元素
          -->
        <foreach collection="ids" item="item_id" separator=","
            open="where id in(" close=")">
            #{item_id}
        </foreach>
     </select>

    EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
    List<Employee> list = mapper.getEmpsByConditionForeach(Arrays.asList(1,3,4));
    for (Employee emp : list) {
        System.out.println(emp);
    }

　　执行测试后，拼装的SQL语句为：

Preparing: select * from tbl_employee WHERE id in ( ? , ? , ? )

批量保存

MySQL数据库批量保存

一、MySQL下批量保存：可以foreach遍历，mysql支持values(),(),()语法

   <insert id="addEmps">
        insert into tbl_employee(
            last_name,email,gender,d_id
        )
        values
        <foreach collection="emps" item="emp" separator=",">
            (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dId})
        </foreach>
    </insert>

    EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
    List<Employee> emps = new ArrayList<>();
    emps.add(new Employee(null, "smith0x1", "smith0x1@atguigu.com", "1","1"));
    emps.add(new Employee(null, "allen0x1", "allen0x1@atguigu.com", "0","1"));
    mapper.addEmps(emps);
    penSession.commit();

　　
　　执行测试后，拼装的SQL语句为：

==>  Preparing: insert into tbl_employee( last_name,email,gender,d_id ) values (?,?,?,?) , (?,?,?,?)

二、用分号分隔多个sql语句，来执行批量保存

　　这种方式需要数据库连接属性allowMultiQueries=true；同时这种分号分隔多个sql语句的方式可以用于其他的批量操作（删除，修改）。
　　

jdbc.jdbcUrl=jdbc:mysql://localhost:3306/account?allowMultiQueries=true

    <insert id="addEmps">
        <foreach collection="emps" item="emp" separator=";">
            insert into tbl_employee(last_name,email,gender,d_id)
            values(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dId})
        </foreach>
    </insert>

Oracle数据库批量保存

　　由于Oracle不支持values(),(),()语法，所以需要用以下Oracle支持的批量操作方式。

一、多个insert放在begin - end里面

   begin
        insert into employees(employee_id,last_name,email)
        values(employees_seq.nextval,'test_001','test_001@163.com');
        insert into employees(employee_id,last_name,email)
        values(employees_seq.nextval,'test_002','test_002@163.com');
   end;

　　XML中的示例代码如下：

    <insert id="addEmps" databaseId="oracle">
        <foreach collection="emps" item="emp" open="begin" close="end;">
            insert into employees(employee_id,last_name,email)
            values(employees_seq.nextval,#{emp.lastName},#{emp.email});
        </foreach> 
    </insert>

二、利用中间表

    insert into employees(employee_id,last_name,email)
    select employees_seq.nextval,lastName,email from(
           select 'test_a_01' lastName,'test_a_e01' email from dual
           union
           select 'test_a_02' lastName,'test_a_e02' email from dual
           union
           select 'test_a_03' lastName,'test_a_e03' email from dual
     )

　　XML中的示例代码如下：
　　

    <insert id="addEmps" databaseId="oracle">
        insert into employees(
              employee_id,last_name,email
        )
        <foreach collection="emps" item="emp" separator="union"
                 open="select employees_seq.nextval,lastName,email from("
                 close=")">
            select #{emp.lastName} lastName,#{emp.email} email from dual
        </foreach>
    </insert>

bind

　　bind 元素可以从 OGNL 表达式中创建一个变量并将其绑定到上下文，方便后来引用这个变量的值。
　　示例代码如下：
　　

    <select id="getEmpByLikeName" resultType="com.mybatis.bean.Employee">
        <bind name="_lastName" value="'%'+lastName+'%'"/>
        SELECT
        <include refid="emp"/>
        FROM tbl_employee
        WHERE last_name LIKE #{_lastName}
    </select>

　　利用bind创建一个_lastName变量，这个变量的值为传进来的lastName拼接两个%，做模糊查询的时候就可以只传lastName的值。

注：模糊查询的几种方式

一、如上所示，利用bind元素。
二、使用sql中的字符串拼接函数

    <select id="getEmpByLikeName" resultType="com.mybatis.bean.Employee">
        SELECT
        <include refid="emp"/>
        FROM tbl_employee
        WHERE last_name
        LIKE CONCAT('%',#{lastName},'%')
    </select>

三、用${…}代替#{…}

    <select id="getEmpByLikeName" resultType="com.mybatis.bean.Employee">
        SELECT
        <include refid="emp"/>
        FROM tbl_employee
        WHERE last_name
        LIKE '%${lastName}%'
    </select>

　　使用${}不能有效防止SQL注入，所以这种方式不推荐使用。
　　
　　

sql

　　sql元素可以被用来定义可重用的 SQL 代码段，可以包含在其他语句中。sql元素经常将要查询的列名，或者插入用的列名抽取出来方便引用。
　　示例代码如下：
　　

    <sql id="emp">
      id,last_name,email,gender
    </sql>

　　这个 SQL 片段可以被包含在其他语句中，使用include元素来引用已经抽取的sql。
　　例如：
　　

    <select id="getEmpById" resultMap="MyEmpDis" parameterType="Integer">
        select
        <include refid="emp"/>
        from tbl_employee
        where id = #{id}
    </select>

　　以上代码相当于：
　　

    <select id="getEmpById" resultMap="MyEmpDis" parameterType="Integer">
        select id,last_name,email,gender
        from tbl_employee
        where id = #{id}
    </select>

　　include还可以自定义一些property，sql标签内部就能使用自定义的属性。
　　注意： include-property：取值的正确方式${prop}，不能使用这种方式#{}
　　
　　示例代码如下：
　　

<sql id="userColumns"> ${alias}.id,${alias}.username,${alias}.password </sql>

<select id="selectUsers" resultType="map">
  select
    <include refid="userColumns"><property name="alias" value="t1"/></include>,
    <include refid="userColumns"><property name="alias" value="t2"/></include>
  from some_table t1
    cross join some_table t2
</select>

两个内置参数

　　不只是方法传递过来的参数可以被用来判断，取值。MyBatis默认还有两个内置参数：
　　_parameter:代表整个参数
　　　　单个参数：_parameter就是这个参数
　　　　多个参数：参数会被封装为一个map；_parameter就是代表这个map
　　_databaseId:如果配置了databaseIdProvider标签。_databaseId就是代表当前数据库的别名。
　　示例代码如下：
　　

    <select id="getEmpsTestInnerParameter" resultType="com.mybatis.bean.Employee">
        <if test="_databaseId=='mysql'">
            select * from tbl_employee
            <if test="_parameter!=null">
                where last_name like #{lastName}
            </if>
        </if>
        <if test="_databaseId=='oracle'">
            select * from employees
            <if test="_parameter!=null">
                where last_name like #{_parameter.lastName}
            </if>
        </if>
    </select>

老臣、

关注

3
点赞
踩
14

收藏

觉得还不错? 一键收藏
2
评论
MyBatis（四）——动态SQL

概述　　MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其他类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句有多么痛苦。拼接的时候要确保不能忘了必要的空格，还要注意省掉列名列表最后的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。　　　　通常使用动态 SQL 不可能是独立的一部分,MyBatis 当然使用一种强大的动态 SQL 语言来改进这...
复制链接

扫一扫