<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--
动态 SQL
MyBatis 的强大特性之一便是它的动态 SQL 能力。如果你有使用 JDBC 或其他类似框架的经验,你就能体会到根据不同条件拼接 SQL 字符串有多么痛苦。拼接的时候要确保不能忘了必要的空格,还要注意省掉列名列表最后的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。
通常使用动态 SQL 不可能是独立的一部分,MyBatis 当然使用一种强大的动态 SQL 语言来改进这种情形,这种语言可以被用在任意映射的 SQL 语句中。
动态 SQL 元素和使用 JSTL 或其他相似的基于 XML 的文本处理器相似。在 MyBatis 之前的版本中,有很多的元素需要来了解。MyBatis 3 大大提升了它们,现在用不到原先一半的元素就能工作了。MyBatis 采用功能强大的基于 OGNL 的表达式来消除其他元素。
if
choose (when, otherwise)
trim (where, set)
foreach
-->
<mapper namespace="com.test.employees">
<!-- EMPLOYEES -->
<!-- if 动态 SQL 通常要做的事情是有条件地包含 where 子句的一部分 可以有多条if-->
<select id="EMPLOYEES_SELECT_IF" parameterType="map" resultType="map">
select * from EMPLOYEES
where DEPARTMENT_ID = #{DEPARTMENT_ID,jdbcType=VARCHAR}
<if test="EMPLOYEE_ID != null">
and EMPLOYEE_ID like #{EMPLOYEE_ID,jdbcType=VARCHAR}
</if>
</select>
<!-- choose, when, otherwise 有些时候,我们不想用到所有的条件语句,而只想从中择其一二。针对这种情况,MyBatis 提供了 choose 元素,它有点像 Java 中的 switch 语句。 -->
<select id="EMPLOYEES_SELECT_FIRST" parameterType="map" resultType="map">
select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,TO_CHAR(HIRE_DATE,'yyyy-MM-dd hh24:mi')HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID
from EMPLOYEES
where DEPARTMENT_ID = #{DEPARTMENT_ID,jdbcType=VARCHAR}
<choose>
<when test="EMPLOYEE_ID != null">
and EMPLOYEE_ID like #{EMPLOYEE_ID,jdbcType=VARCHAR}
</when>
<otherwise>
and MANAGER_ID = #{MANAGER_ID,jdbcType=VARCHAR}
</otherwise>
</choose>
</select>
<!-- trim, where, set 去除条件为空还会保留where and or导致sql语句失败的方法 , where 元素知道只有在一个以上的if条件有值的情况下才去插入“WHERE”子句。而且,若最后的内容是“AND”或“OR”开头的,where 元素也知道如何将他们去除。-->
<select id="EMPLOYEES_SELECT_SECOND" parameterType="map" resultType="map">
select * from EMPLOYEES
<where>
<if test="DEPARTMENT_ID != null">
DEPARTMENT_ID = #{DEPARTMENT_ID,jdbcType=VARCHAR}
</if>
<if test="EMPLOYEE_ID != null">
and EMPLOYEE_ID like #{EMPLOYEE_ID,jdbcType=VARCHAR}
</if>
</where>
</select>
<!-- trim 和上述方法功能相同 //prefixOverrides 属性会忽略通过管道分隔的文本序列(注意此例中的空格也是必要的)。它带来的结果就是所有在 prefixOverrides 属性中指定的内容将被移除,并且插入 prefix 属性中指定的内容。-->
<select id="EMPLOYEES_SELECT_TRIM" parameterType="map" resultType="map">
select * from EMPLOYEES
<trim prefix="WHERE" prefixOverrides="AND |OR ">
<if test="DEPARTMENT_ID != null">
DEPARTMENT_ID = #{DEPARTMENT_ID,jdbcType=VARCHAR}
</if>
<if test="EMPLOYEE_ID != null">
and EMPLOYEE_ID like #{EMPLOYEE_ID,jdbcType=VARCHAR}
</if>
</trim>
</select>
<!-- set 类似的用于动态更新语句的解决方案叫做 set。set 元素可以被用于动态包含需要更新的列,而舍去其他的。-->
<update id="EMPLOYEES_UPDATE_SET" parameterType="map">
update EMPLOYEES
<set>
<if test="FIRST_NAME != null">FIRST_NAME=#{FIRST_NAME,jdbcType=VARCHAR},</if>
<if test="LAST_NAME != null">LAST_NAME=#{LAST_NAME,jdbcType=VARCHAR},</if>
<if test="EMAIL != null">EMAIL=#{EMAIL,jdbcType=VARCHAR},</if>
<if test="PHONE_NUMBER != null">PHONE_NUMBER=#{PHONE_NUMBER,jdbcType=VARCHAR},</if>
<if test="HIRE_DATE != null">HIRE_DATE=to_date(#{HIRE_DATE,jdbcType=TIMESTAMP},'yyyy-MM-dd hh24:mi:ss'),</if>
<if test="JOB_ID != null">JOB_ID=#{JOB_ID,jdbcType=VARCHAR},</if>
<if test="SALARY != null">SALARY=#{SALARY,jdbcType=VARCHAR},</if>
<if test="COMMISSION_PCT != null">COMMISSION_PCT=#{COMMISSION_PCT,jdbcType=VARCHAR},</if>
<if test="MANAGER_ID != null">MANAGER_ID=#{MANAGER_ID,jdbcType=VARCHAR},</if>
<if test="DEPARTMENT_ID != null">DEPARTMENT_ID=#{DEPARTMENT_ID,jdbcType=VARCHAR}</if>
</set>
<where>
<if test="EMPLOYEE_ID != null">
EMPLOYEE_ID = #{EMPLOYEE_ID,jdbcType=VARCHAR}
</if>
</where>
</update>
<!-- trim 和上述方法功能相同 , 这里,set 元素会动态前置 SET 关键字,同时也会消除无关的逗号,因为用了条件语句之后很可能就会在生成的赋值语句的后面留下这些逗号。 -->
<update id="EMPLOYEES_UPDATE_SET" parameterType="map">
update EMPLOYEES
<trim prefix="SET" suffixOverrides=",">
<if test="FIRST_NAME != null">FIRST_NAME=#{FIRST_NAME,jdbcType=VARCHAR},</if>
<if test="LAST_NAME != null">LAST_NAME=#{LAST_NAME,jdbcType=VARCHAR},</if>
<if test="EMAIL != null">EMAIL=#{EMAIL,jdbcType=VARCHAR},</if>
<if test="PHONE_NUMBER != null">PHONE_NUMBER=#{PHONE_NUMBER,jdbcType=VARCHAR},</if>
<if test="HIRE_DATE != null">HIRE_DATE=to_date(#{HIRE_DATE,jdbcType=TIMESTAMP},'yyyy-MM-dd hh24:mi:ss'),</if>
<if test="JOB_ID != null">JOB_ID=#{JOB_ID,jdbcType=VARCHAR},</if>
<if test="SALARY != null">SALARY=#{SALARY,jdbcType=VARCHAR},</if>
<if test="COMMISSION_PCT != null">COMMISSION_PCT=#{COMMISSION_PCT,jdbcType=VARCHAR},</if>
<if test="MANAGER_ID != null">MANAGER_ID=#{MANAGER_ID,jdbcType=VARCHAR},</if>
<if test="DEPARTMENT_ID != null">DEPARTMENT_ID=#{DEPARTMENT_ID,jdbcType=VARCHAR}</if>
</trim>
<where>
<if test="EMPLOYEE_ID != null">
EMPLOYEE_ID = #{EMPLOYEE_ID,jdbcType=VARCHAR}
</if>
</where>
</update>
<!-- foreach-----mybatis批量操作
foreach的主要用在构建in条件中,它可以在SQL语句中进行迭代一个集合。
foreach 元素是非常强大的,它允许你指定一个集合,声明可以用在元素体内的集合项和索引变量。它也允许你指定开闭匹配的字符串以及在迭代之间放置分隔符。这个元素是很智能的,因此它不会偶然地附加多余的分隔符。
oreach元素的属性主要有item,index,collection,open,separator,close。
item表示集合中每一个元素进行迭代时的别名;
index指定一个名字,用于表示在迭代过程中,每次迭代到的位置;
open表示该语句以什么开始;
separator表示在每次进行迭代之间以什么符号作为分隔符;
close表示以什么结束;
collection,在使用foreach的时候最关键的也是最容易出错的就是collection属性,该属性是必须指定的,但是在不同情况下,该属性的值是不一样的,主要有一下3种情况:
1、 如果传入的是单参数且参数类型是一个List的时候,collection属性值为list。
2、如果传入的是单参数且参数类型是一个array数组的时候,collection的属性值为array
3、如果传入的参数是多个的时候,我们就需要把它们封装成一个Map了,
当然单参数也可以封装成map,实际上如果你在传入参数的时候,在MyBatis里面也是会把它封装成一个Map的,map的key就是参数名,
所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key。
-->
<select id="EMPLOYEES_SELECT_FOREACH" parameterType="java.util.List">
select * from EMPLOYEES EMPLOYEE_ID in
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
<!-- 批量删除 -->
<delete id="EMPLOYEES_DELETE_FOREACH" parameterType="java.util.List">
delete from EMPLOYEES where EMPLOYEE_ID in
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
#{EMPLOYEE_ID,jdbcType=VARCHAR}
</foreach>
</delete>
<!-- 批量更新 注意:oracle中 形如 update *** set *** where ** in(....) 这种语句 in所在的集合有条数限制 为1000条-->
<update id="EMPLOYEES_UPDATE_FOREACH" parameterType="java.util.List">
update EMPLOYEES set FIRST_NAME = "AAA" where EMPLOYEE_ID in
<foreach collection="list" item="item" index="index" open="(" separator="," close=")" >
#{EMPLOYEE_ID,jdbcType=VARCHAR}
</foreach>
</update>
<!-- 批量插入 mysql和oracle不一样 -->
<!-- mysql -->
<insert id="EMPLOYEES_INSERT_FOREACH_MYSQL" parameterType="java.util.List">
insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
values
<foreach collection="list" item="item" index="index" separator="," >
(#{item.EMPLOYEE_ID},#{item.FIRST_NAME},#{item.LAST_NAME},#{item.EMAIL},#{item.PHONE_NUMBER},#{item.HIRE_DATE},#{item.JOB_ID},#{item.SALARY},#{item.COMMISSION_PCT},#{item.MANAGER_ID},#{item.DEPARTMENT_ID})
</foreach>
</insert>
<!-- oracle -->
<insert id="EMPLOYEES_INSERT_FOREACH_ORACLE" parameterType="java.util.List">
insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
values
<foreach collection="list" item="item" index="index" separator="union all" >
(#{item.EMPLOYEE_ID},#{item.FIRST_NAME},#{item.LAST_NAME},#{item.EMAIL},#{item.PHONE_NUMBER},#{item.HIRE_DATE},#{item.JOB_ID},#{item.SALARY},#{item.COMMISSION_PCT},#{item.MANAGER_ID},#{item.DEPARTMENT_ID})
</foreach>
</insert>
</mapper>
mybatis--动态SQL
最新推荐文章于 2022-04-06 10:14:27 发布