Mybatis_3_动态SQL

1.动态SQL简介

1）简化了拼装SQL的操作

2）动态SQL元素和使用JSTL或其他类似于XML的文本处理器相似

3）mybatis采用强大的基于OGNL的表达式来简化操作

OGNL:对象图导航语言

 

2. if

<!--多条件查询：若页面中没有设置此条件，SQL语句中一定不能有该条件-->
    <!--输入框需要判断是否为null或空串-->
    <!--单选框、多选框只需要判断是否为null-->
    <!--在最后需要加一个 1 = 1以免最后一个if判断不通过导致sql语句多一个and-->
    <select id="getUsersByMultipalConditions" resultType="com.pojo.User">
        select id,username,upassword,email from t_user
        where
        <if test="id != null">
            id=#{id} and
        </if>
        <if test="username != null and username != ''">
            username=#{username} and
        </if>
         <if test="upassword != null and upassword != ''">
          upassword=#{upassword} and
         </if>
        <if test='email == "zhaofeixiang@cdut.edu.cn" '>
         email=#{email} and
        </if>
        1 = 1
    </select>

3.where

<!--<where>:添加where关键字，同时去掉where后边紧跟的第一个多余的and-->
    <select id="getUsersByMultipalConditions" resultType="com.pojo.User">
        select id,username,upassword,email from t_user
        <where>
        <if test="id != null">
          id=#{id}
        </if>
        <if test="username != null and username != ''">
            and   username=#{username}
        </if>
         <if test="upassword != null and upassword != ''">
             and  upassword=#{upassword}
         </if>
        <if test='email == "zhaofeixiang@cdut.edu.cn" '>
            and email=#{email}
        </if>

        </where>
    </select>

 

4. trim

<!--<trim prefix="" suffix="" prefixOverrides="" suffixOverrides="">: 截取并拼接字符串-->
    <!--prefix:在sql语句之前填充某些内容-->
    <!--suffix:在sql语句之后填充某些内容-->
    <!--prefixOverrides:把sql语句头部的某些内容去掉-->
    <!--suffixOverrides:把sql语句尾部的某些内容去掉，多个待删除内容用/隔开，例如"and/or"-->
    <select id="getUsersByMultipalConditions" resultType="com.pojo.User">
        select id,username,upassword,email from t_user
        <trim prefix="where"  prefixOverrides="and" suffixOverrides="and">
        <if test="id != null">
         and id=#{id}
        </if>
        <if test="username != null and username != ''">
            and   username=#{username}
        </if>
         <if test="upassword != null and upassword != ''">
             and  upassword=#{upassword}
         </if>
        <if test='email == "zhaofeixiang@cdut.edu.cn" '>
            and email=#{email}
        </if>
            and
        </trim>
    </select>

 

5.chose

 <!--<chose>:选择某一个when或者otherwise中的字符串拼接进sql-->
    <select id="getUsersByMultipalConditions" resultType="com.pojo.User">
        select id,username,upassword,email from t_user
        where
        <choose>
            <when test="id != null">
                id=#{id}
            </when>
            <when test="username != null and username != ''">
                username=#{username}
            </when>
            <when test="upassword != null and upassword != ''">
                upassword=#{upassword}
            </when>
            <otherwise>
                email=#{email}
            </otherwise>
        </choose>
    </select>

 

<!--index:当传入的是数组、集合时index相当于下标，当传入的是map时index为键-->

6.Foreach

 <!--<foreach>: 遍历数组、集合中的元素并将其拼接到sql-->
    <!--collection: 集合名-->
　　　　<!-- index:当传入的是数组、集合时index相当于下标，当传入的是map时index为键-->
    <!--item:遍历所用的临时变量，相当于i-->
    <!--separator:每个变量之间的分隔符-->
    <!--open:在遍历之前添加的字符串-->
    <!--close:在遍历之后添加的字符串-->
    <delete id="batchDelete">
        delete from t_user where id
        <foreach collection="list" item="id" separator="," open="in (" close=")">
            #{id}
        </foreach>
    </delete>

使用foreach进行批量插入：

<!--使用foreach进行批量插入-->
    <!--需要在db.properties中给url加上 &allowMultiQueries=true-->
    <insert id="batchAdd" >
        <foreach collection="users" item="user" separator=";" close=";">
            insert into t_user values(null,#{user.username},#{user.upassword},#{user.email})
        </foreach>
    </insert>

7.sql

<!--<sql>:设置一段公共的sql片段，可以被当前映射文件中所有sql语句访问-->
    <sql id="myCommonSQL">select id,username,upassword,email</sql>
    <select id="getAllUser" resultType="com.pojo.User">
        <include refid="myCommonSQL"></include>
        from t_user
    </select>