MyBatis 动态SQL

MyBatis 动态SQL

　　1) 动态 SQL是MyBatis强大特性之一。极大的简化我们拼装SQL的操作
　　2) 动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似
　　3) MyBatis 采用功能强大的基于 OGNL 的表达式来简化操作
　　　　if
　　　　choose (when, otherwise)
　　　　trim (where, set)
　　　　foreach
　　4) OGNL（ Object Graph Navigation Language ）对象图导航语言，这是一种强大的
　　　　表达式语言，通过它可以非常方便的来操作对象属性。 类似于我们的EL，SpEL等
　　　　访问对象属性：  person.name
　　　　调用方法：      person.getName()
　　　　调用静态属性/方法： @java.lang.Math@PI 
          　　　　　　　　　　   @java.util.UUID@randomUUID()
　　　　调用构造方法：  new com.atguigu.bean.Person(‘admin’).name
　　　　运算符：       +,-*,/,%
　　　　逻辑运算符：   in,not in,>,>=,<,<=,==,!=
　　　　　　注意：xml中特殊符号如”,>,<等这些都需要使用转义字符

1、动态SQL---> if

　　1) If 用于完成简单的判断。多条件查询时，是否需要该条件，不要的条件不能放入sql语句中。
　　2) <where>用于解决SQL语句中where关键字以及条件中第一个and或者or的问题 (注意是第一个！注意and或or在sql语句中的位置)

    <!-- 
        <if test=""></if>：通过test表达式，拼接SQL，对于文本框不仅需要判断是否为null，同时还要判断是否为空字符串''
        <where>：添加where关键字，同时去掉多余的and
     -->

    
    <select id="getEmpListByMoreTJ1" resultType="Emp">
        select eid,ename,age,sex,did from emp 
        <where>
            <if test="eid != null">
                and eid = #{eid}
            </if>
            <if test="ename != null and ename != ''">
                and ename = #{ename}
            </if>
            <if test="age != null">
                and age = #{age}
            </if>
            <if test="sex == 1 or sex == 0">
                and sex = #{sex}
            </if>
        </where>
    </select>

2、通过<trim>,截取和拼接标签处理。 Trim 可以在条件判断完的SQL语句前后 添加或者去掉指定的字符
　　　　　　prefix: 添加前缀
　　　　　　prefixOverrides: 去掉前缀
　　　　　　suffix: 添加后缀
　　　　　　suffixOverrides: 去掉后缀（多个中间用 | 连接）

    <!-- 
        <if test=""></if>：通过test表达式，拼接SQL
        <where>：添加where关键字，同时去掉多余的and
        <trim prefix="" suffix="" prefixOverrides="" suffixOverrides="">：截取并拼接
        prefix：在操作的SQL语句前加入某些内容
        suffix：在操作的SQL语句后加入某些内容
        prefixOverrides：把操作的SQL语句前的某些内容去掉
        suffixOverrides：把操作的SQL语句后的某些内容去掉
     -->
    
    <!-- List<Emp> getEmpListByMoreTJ();多条件查询：若页面中没有设置此条件，SQL语句中一定不能有该条件 -->
    <select id="getEmpListByMoreTJ" resultType="Emp">
        <include refid="empColumns"></include>
        <trim prefix="where" suffixOverrides="and|or">
            <if test="eid != null">
                eid = #{eid} and
            </if>
            <if test="ename != null and ename != ''">
                ename = #{ename} and 
            </if>
            <if test="age != null">
                age = #{age} or 
            </if>
            <if test="sex == 1 or sex == 0">
                sex = #{sex} 
            </if>
        </trim>
    </select>

3、set 主要是用于解决修改操作中SQL语句中可能多出逗号的问题（但一般用trim就可以实现）

    <update id="updateEmpByConditionSet">
        update  tbl_employee  
        <set>
            <if test="lastName!=null &amp;&amp; lastName!=&quot;&quot;">
                 last_name = #{lastName},
            </if>
            <if test="email!=null and email.trim()!=''">
                 email = #{email} ,
            </if>
            <if test="&quot;m&quot;.equals(gender) or &quot;f&quot;.equals(gender)">
                gender = #{gender} 
            </if>
        </set>
         where id =#{id}
    </update>

4、choose 主要是用于分支判断，类似于java中的switch case,只会满足所有分支中的一个

    <!-- 
        <choose>：选择某一个when或otherwise拼接SQL
            <when test=""></when>：通过test表达式拼接SQL
            .
            .
            .
            <otherwise></otherwise>：当when都不符合条件，就会选择otherwise拼接SQL
        </choose>
     -->
    
    <!-- List<Emp> getEmpListByChoose(Emp emp); -->
    <select id="getEmpListByChoose" resultType="Emp">
        select eid,ename,age,sex from emp 
        where 
        <choose>
            <when test="eid != null">
                eid = #{eid}
            </when>
            <when test="ename != null and ename != ''">
                ename = #{ename}
            </when>
            <when test="age != null">
                age = #{age}
            </when>
            <otherwise>
                sex = #{sex}
            </otherwise>
        </choose>
    </select>
    
    <!-- void insertEmp(Emp emp); -->
    <insert id="insertEmp">
        insert into emp(eid,ename,age,sex) values(
            null,
            #{ename},
            #{age},
            <choose>
                <when test="sex == 0">'女'</when>
                <when test="sex == 1">'男'</when>
                <otherwise>'不详'</otherwise>
            </choose>
        )
    </insert>

5、foreach 主要用于循环迭代

<!-- 
        <foreach collection="" item="" close="" open="" separator="" index=""></foreach>
        对一个数组或集合进行遍历
        collection：指定要遍历的集合或数组
        item：设置别名
        close：设置循环体的结束内容
        open：设置循环体的开始内容
        separator：设置每一次循环之间的分隔符
        index：若遍历的是list，index代表下标；若遍历的是map，index代表键
     -->
    
    <!-- void deleteMoreByList(List<Integer> eids); -->
    <!-- 
        delete from emp where eid in ();  //delete from emp where eid in (${value})
        delete from emp where eid = 1 or eid = 2 or eid = 3 
     -->
    <delete id="deleteMoreByList">
        delete from emp where eid in 
        <foreach collection="list" item="eid" separator="," open="(" close=")"> <!--注意一定是list，数组为array-->
            #{eid}
        </foreach>
    </delete>

        List<Integer> eids = new ArrayList<>();
        eids.add(7);
        eids.add(8);
        eids.add(10);
        mapper.deleteMoreByList(eids);

同时也可以采用自定义参数键名

　　 void deleteMoreByList(@Param("eids")List<Integer> eids); //此时，就可以指定collection='eids' 

 

对于批量操作

    <!-- 
        delete：
            delete from emp where eid in ();
            delete from emp where eid = 1 or eid = 2 or eid = 3 
        select：
            select * from emp where eid in ();
            select * from emp where eid = 1 or eid = 2 or eid = 3 
        update：
            把每条数据修改为相同内容
            update emp set ... where eid in ();
            update emp set ... where eid = 1 or eid = 2 or eid = 3
            把每条数据修改为对应内容，注意：必须在连接地址（url）后添加参数?allowMultiQueries=true
　　　　　　　　　　jdbc.url=jdbc:mysql://localhost:3306/ssm?allowMultiQueries=true
　　　　　　　update emp set ... where eid = 1;
            update emp set ... where eid = 2;
            update emp set ... where eid = 3
        insert
            insert into emp values(),(),()
     -->

     <!-- void insertMoreByArray(Emp[] emps); -->
     <insert id="insertMoreByArray">
         insert into emp values
         <foreach collection="emps" item="emp" separator=",">
             (null,#{emp.ename},#{emp.age},#{emp.sex},1)
         </foreach>
     </insert>
     
     <!-- void updateMoreByArray(@Param("emps")Emp[] emps); -->
     <update id="updateMoreByArray">
         <foreach collection="emps" item="emp">
             update emp set ename = #{emp.ename}, age = #{emp.age}, sex = #{emp.sex} where eid = #{emp.eid};
         </foreach>
     </update>

6、sql片段 

　　1) sql 标签是用于抽取可重用的sql片段，将相同的，使用频繁的SQL片段抽取出来，单独定义，方便多次引用.
　　2) 抽取SQL:

    <!-- 
        <sql id=""></sql>：设置一段SQL片段，即公共SQL，可以被当前映射文件中所有的SQL语句所访问
        <include refid="empColumns"></include>：访问某个SQL片段
     -->
    <sql id="empColumns">select eid,ename,age,sex,did from emp </sql>

　　3) 引用SQL:

　　<select id="getEmpListByMoreTJ" resultType="Emp">
        <include refid="empColumns"></include>
        <trim prefix="where" suffixOverrides="and|or">
            <if test="eid != null">
                eid = #{eid} and
            </if>
            <if test="ename != null and ename != ''">
                ename = #{ename} and 
            </if>
            <if test="age != null">
                age = #{age} or 
            </if>
            <if test="sex == 1 or sex == 0">
                sex = #{sex} 
            </if>
        </trim>
    </select>