MyBatis归纳之动态SQL

为什么要有动态SQL

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

通常使用动态 SQL 不可能是独立的一部分,MyBatis 当然使用一种强大的动态 SQL 语言来改进这种情形,这种语言可以被用在任意的 SQL 映射语句中。

动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似。在 MyBatis 之前的版本中,有很多的元素需要来了解。MyBatis 3 大大提升了它们,现在用不到原先一半的元素就可以了。MyBatis 采用功能强大的基于 OGNL 的表达式来消除其他元素。

MyBatis为我们提供了以下四种运算符：

        • if:判断

        • choose (when, otherwise):分支选择；带了break的swtich-case

        如果带了id就用id查，如果带了lastName就用lastName查;只会进入其中一个

        • trim 字符串截取(where(封装查询条件), set(封装修改条件))

        • foreach 遍历集合

2.if的示例：

（1）使用了where标签

<!-- 查询员工，要求，携带了哪个字段查询条件就带上这个字段的值 -->
<!-- public List<Employee> getEmpsByConditionIf(Employee employee); -->
<selectid="getEmpsByConditionIf"resultType="com.atguigu.mybatis.bean.Employee">
select * from tbl_employee
<!-- where （在这里我们用了MyBatis的where标签 ，因为当我们不带id而带了lastName时会在前面多出来一个and，
而where标签能也只能解决前面多出来一个and的问题,where只会去掉第一个多出来的and或者or。-->
<where>
<!-- 
        test：判断表达式（OGNL）
        OGNL参照PPT或者官方文档。
        c:if test
        从参数中取值进行判断
        遇见特殊符号(比如此处不应出现&&和"")应该去写转义字符：（"是"的转义字符,&是&的转义字符，
        这里用"" 取代""（当然也可以用两个单引号''）,用&& 取代&&(当然也可以用and)）
-->
<iftest="id!=null">
        id=#{id}
</if>
<iftest="lastName!=null&&lastName!=""">
        and last_name like #{lastName}
</if>
<iftest="email!=null and email.trim()!=""">
        and email=#{email}
</if>
<!-- ognl会进行字符串与数字的转换判断 "0"==0 -->
<iftest="gender==0 or gender==1">
        and gender=#{gender}
</if>
</where>
</select>

（2）使用了set标签

<updateid="updateEmp">
        <!-- set标签的使用在这里我们用了MyBatis的set标签 ，因为我们在大多数语句后面会多出来一个逗号以便接下来的sql语句拼接，
                而这个多出来的逗号会导致sql语句出错，用set标签可以去除掉后面多出来的一个逗号。-->
        update tbl_employee
<set>
<iftest="lastName!=null">
        last_name=#{lastName},
</if>
<iftest="email!=null">
        email=#{email},
</if>
<iftest="gender!=null">
        gender=#{gender}
</if>
</set>
        where id=#{id}
</update>

trim的示例：

（1）

<selectid="getEmpsByConditionTrim"resultType="com.atguigu.mybatis.bean.Employee">
select* from tbl_employee
<!-- 
        后面多出的and或者or where标签不能解决
        prefix="":前缀：trim标签体中是整个字符串拼串 后的结果。
        prefix给拼串后的整个字符串加一个前缀
        prefixOverrides="":
        前缀覆盖： 去掉整个字符串前面多余的字符
        suffix="":后缀
        suffix给拼串后的整个字符串加一个后缀
        suffixOverrides=""
        后缀覆盖：去掉整个字符串后面多余的字符
-->
<!-- 自定义字符串的截取规则 -->
<trimprefix="where"suffixOverrides="and">
<iftest="id!=null">
        id=#{id} and
</if>
<iftest="lastName!=null&&lastName!=""">
        last_name like #{lastName} and
</if>
<iftest="email!=null and email.trim()!=""">
        email=#{email} and
</if>
<!-- ognl会进行字符串与数字的转换判断 "0"==0 -->
<iftest="gender==0 or gender==1">
        gender=#{gender}
</if>
</trim>
</select>

（2）

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


choose的示例：
<selectid="getEmpsByConditionChoose"resultType="com.atguigu.mybatis.bean.Employee">
        select* from tbl_employee
<where>
                <!-- 如果带了id就用id查，如果带了lastName就用lastName查;只会进入其中一个 -->
<choose>
        <whentest="id!=null">
                id=#{id}
        </when>
        <whentest="lastName!=null">
                last_name like #{lastName}
        </when>
        <whentest="email!=null">
                email = #{email}
        </when>
        <otherwise>
                gender = 0
        </otherwise>
</choose>
</where>
</select>

foreach的示例：

现在我们在EmployeeMapperDynamicSQL.class接口里定义一个这样的方法

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

在其映射文件EmployeeMapperDynamicSQL.xml中

<!--public List<Employee> getEmpsByConditionForeach(List<Integer> ids); -->
<selectid="getEmpsByConditionForeach"resultType="com.atguigu.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的值
        #{变量名}就能取出变量的值也就是当前遍历出的元素
-->
<foreachcollection="ids"item="item_id"separator=","
open="where id in("close=")">
        #{item_id}
</foreach>
</select>

测试文件中

List<Employee> list = mapper.getEmpsByConditionForeach(Arrays.asList(1,2));

for(Employee emp : list) {

System.out.println(emp);

}

结果

足以说明foreach的作用（在sql语句中通过foreach修改in里面的参数个数）

抽取可重用的sql片段（学习下个内容用到）

<!--
        抽取可重用的sql片段。方便后面引用
        1、sql抽取：经常将要查询的列名，或者插入用的列名抽取出来方便引用
        2、include来引用已经抽取的sql：
        3、include还可以自定义一些property，sql标签内部就能使用自定义的属性
             include-property：取值的正确方式${prop},
            #{不能使用这种方式}
-->
<sqlid="insertColumn">
<iftest="_databaseId=='oracle'">
        employee_id,last_name,email
</if>
<iftest="_databaseId=='mysql'">
        last_name,email,gender,d_id
</if>
</sql>
<includerefid="insertColumn"></include>配合使用

有关于include的子标签property的使用

<includerefid="insertColumn">
        <propertyname="testColumn"value="abc"/>
</include>
<sqlid="insertColumn">
<iftest="_databaseId=='oracle'">
        employee_id,last_name,email,${testColumn}
</if>
<iftest="_databaseId=='mysql'">
        last_name,email,gender,d_id
</if>
</sql>

MySQL批量插入的两种方式

（1）sql--include--foreach--insert（四个标签，首选）

<!-- 批量保存 -->
<!--public void addEmps(@Param("emps")List<Employee> emps); -->
<!--MySQL下批量保存：可以foreach遍历 mysql支持values(),(),()语法-->
<insertid="addEmps">
        insert into tbl_employee(
<includerefid="insertColumn"></include>
        )
        values
<foreachcollection="emps"item="emp"separator=",">
        (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
</foreach>
</insert>

（2）foreach--insert

<!-- 这种方式需要数据库连接属性allowMultiQueries=true；

这种分号分隔多个sql可以用于其他的批量操作（删除，修改） -->

首先你得在数据库配置文件（一般为dbconfig.properties）中的jdbc.url这里设置为

jdbc.url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true

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

_parameter和_databaseId内置参数和bind绑定

_parameter相当于传入进来的参数整体，用于判断传入的整个参数是否为空或形式是否正确

_databaseId在mybatis的xml配置文件中配置了databaseIdProvider标签后，可以用来判断当前

使用的数据库是什么数据库，进而执行不同的命令

<!-- 两个内置参数：
不只是方法传递过来的参数可以被用来判断，取值。。。
mybatis默认还有两个内置参数：
_parameter:代表整个参数
单个参数：_parameter就是这个参数
多个参数：参数会被封装为一个map；_parameter就是代表这个map
_databaseId:如果配置了databaseIdProvider标签。
_databaseId就是代表当前数据库的别名oracle
-->
<!--public List<Employee> getEmpsTestInnerParameter(Employee employee); -->
<selectid="getEmpsTestInnerParameter"resultType="com.atguigu.mybatis.bean.Employee">
<!-- bind：可以将OGNL表达式的值绑定到一个变量中，方便后来引用这个变量的值 -->
<bindname="_lastName"value="'%'+lastName+'%'"/>
<iftest="_databaseId=='mysql'">
        select * from tbl_employee
<iftest="_parameter!=null">
        where last_name like #{lastName}
</if>
</if>
<iftest="_databaseId=='oracle'">
        select * from employees
<iftest="_parameter!=null">
        where last_name like #{_parameter.lastName}
</if>
</if>
</select>

bind标签在传入参数不太方便的情况下（如模糊查询，用户不会输入%）将原来的参数改变 

一般使用不多，因为我们可以在使用前将参数改变，如果使用bind的话，需求改变时需要专门过来更改

<!--public List<Employee> getEmpsTestInnerParameter(Employee employee); -->
<selectid="getEmpsTestInnerParameter"resultType="com.atguigu.mybatis.bean.Employee">
<!-- bind：可以将OGNL表达式的值绑定到一个变量中，方便后来引用这个变量的值 -->
<bindname="_lastName"value="'%'+lastName+'%'"/>
<iftest="_databaseId=='mysql'">
        select * from tbl_employee
<iftest="_parameter!=null">
        where last_name like #{lastName}
</if>
</if>
<iftest="_databaseId=='oracle'">
        select * from employees
<iftest="_parameter!=null">
        where last_name like #{_parameter.lastName}
</if>
</if>
</select>