Dynamic SQL(动态SQL)

应用场景

• 有时候需要根据实际传入的参数来动态的拼接SQL语句

应用

在EmployeeMapper接口中添加一个方法：

public List<Employee> getEmployeeByConditionIf(Employee employee);
1

按需查询：

<select id="getEmployeeByConditionIf" resultType="com.neuedu.entity.Employee">
            select *from tbl_employee 
            where 
             <!--
                test:判断表达式(OGNL)
                OGNL参照PPT或者官方文档。
                    c:if test
                从参数中取值进行判断
                遇见特殊符号，应该去写转义字符(&)：参考W3CSchool>>HTML>>ISO8859

              -->
              <if test="id != null">
                id = #{id}
              </if>
              <if test="userName != null &amp;&amp; userName !=''">
                and user_name = #{userName} 
              </if>
              <if test="email != null and email.trim() != &quot;&quot;">
                and email = #{email}
              </if>
              <!-- ognl会进行字符串和数字的转换判断；"0"==0,"1"==1 -->
              <if test="gender == 0 or gender == 1">
                and gender = #{gender} 
              </if>
        </select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

• mybatis可以使用where标签来将所有的查询条件包括在内。mybatis就会将where标签中拼装的sql， 多出来的and或者or去掉，但是where标签只会去掉第一个多出来的and或者or

• 使用trim标签

  <select id="getEmployeeByConditionIf" resultType="com.neuedu.entity.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="userName != null &amp;&amp; userName !=''">
              user_name = #{userName} and  
            </if>
            <if test="email != null and email.trim() != &quot;&quot;">
              email = #{email} and 
            </if>
            <!-- ognl会进行字符串和数字的转换判断；"0"==0,"1"==1 -->
            <if test="gender==0 or gender==1">
             gender = #{gender}
            </if>     
          </trim>
      </select>
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29

• choose标签
  只会加载一个when标签

  <select id="getEmployeeByConditionChoose" resultType="com.neuedu.entity.Employee">
          select *from tbl_employee
          <where>
              <choose>
                  <when test="id != null">
                      id = #{id}
                  </when>
                  <when test="userName != null">
                      user_name like #{userName}
                  </when>
                  <when test="email != null">
                     email = #{email}
                  </when>
                  <otherwise>
                  <!-- 查询全部-->
                      1=1
                  </otherwise>
              </choose>
          </where>
      </select>
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20

• update中的set标签【set标签可以将字段后面的逗号去掉】

   <update id="updateEmp">
      update tbl_employee 
      <set>
          <if test="userName != null">
              user_name = #{userName},
          </if>
          <if test="email != null">
              email = #{email},
          </if>
          <if test="gender != null">
              gender = #{gender},
          </if>
      </set>
      where id = #{id}
  </update>
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15

• trim标签代替set标签

   <update id="updateEmp">
          update tbl_employee 
          <trim prefix="set" suffixOverrides=",">
              <if test="userName != null">
                  user_name = #{userName},
              </if>
              <if test="email != null">
                  email = #{email},
              </if>
              <if test="gender != null">
                  gender = #{gender},
              </if>
          </trim>
          where id = #{id}
      </update>
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15

• foreach:遍历元素
  批量查询
  public List getEmpsByConditionForeach(@Param(“ids”) List ids);

   <select id="getEmpsByConditionForeach" resultType="com.neuedu.entity.Employee">
          select * from  tbl_employee where id in
          <!-- 
              collection:指定要遍历的集合
              item:将当前遍历出的元素赋值给指定的变量
              separator:每个元素之间的分隔符
              open:遍历出所有记过拼接一个开始的字符
              close:遍历出所有结果拼接一个结束的字符
           -->
          <foreach collection="ids" open="(" close=")" separator="," item="id">
              #{id}
          </foreach>
      </select>
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13

  批量插入数据

  <insert id="addEmps">
          INSERT INTO tbl_employee(user_name,gender,email,d_id) VALUES
          <foreach collection="emps" item="emp" separator=",">
              (#{emp.userName},#{emp.gender},#{emp.email},#{emp.depart.id})
          </foreach>
      </insert>
  1
  2
  3
  4
  5
  6