4.动态SQL(if,choose,where,set,trim,foreach遍历)的使用+$和#的区别

动态sql

一、动态sql

常见动态标签：

• if
• choose (when, otherwise)
• trim (where, set)
• foreach

1.if条件判断

if标签的test属性判断成立，就会将标签对之间的sql语句拼接到主sql语句上

//如果根据传入参数(emp_name和phone)筛选数据   
<select id="queryByNameAndTel" resultType="employee">
         select emp_name, phone, address, salary
         from employee
         where 1=1
         <if test="empName != null">
             AND emp_name = #{empName}
         </if>
         <if test="phone != null">
               and phone = #{phone}
         </if> 
     </select>

2、choose、when、otherwise

相当与java条件语句Switch语句

//根据多条件筛选语句  
<select id="queryByNameAndTel" resultType="employee">
         select emp_name, phone, address, salary
         from employee
         where 1=1
         <choose>
             <when test="empName != null">
                 and emp_name = #{empName}
             </when>
             <when test="phone != null">
                 and phone = #{phone}
             </when>
             <otherwise>
                 and dept_id = 1
             </otherwise>
         </choose>
     </select>

3、where标签

若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。

     <select id="queryByNameAndTel" resultType="employee">
         select emp_name, phone, address, salary
         from employee
         <where>
             <if test="empName != null">
                 and emp_name = #{empName}
             </if>
             <if test="phone != null">
                 and phone = #{phone}
             </if>
         </where>
     </select>

4、set标签

用于动态更新语句的类似解决方案叫做 set,

动态更新需要更新的字段,忽略不更新的字段

 <update id="updateByPrimaryKeySelective">
         update employee
         <set>
             <if test="empName != null">
                 emp_name = #{empName},
             </if>
             <if test="phone != null">
                 phone = #{phone},
             </if>
             <if test="address != null">
                 address = #{address},
             </if>
             <if test="salary != null">
                 salary = #{salary}
             </if>
         </set>
         where id = #{id}
     </update>

5、trim标签

可以使用属性给sql语句添加前缀和后缀或删除前缀和后缀

包含属性:

属性	说明
prefix(添加前缀)	添加前面的关键字（在标签开始位置，添加属性中的内容）
suffix(添加后缀)	添加后面的关键字（在标签结束位置，添加属性中的内容）
prefixoverrides(删除前缀)	去掉第一个关键字（所有子标签中第一子标签中的前缀关键字）
suffixoverrides(删除后缀)	去掉最后一个关键字（所有子标签中最后一个子标签后缀的关键字）

1)替代where标签效果

prefix添加前缀where,代替where标签;prefixOverrides删除子语句判断条件的and | or

  <select id="queryByNameAndTel" resultType="employee">
         select emp_name, phone, address, salary
         from employee
         <trim prefix="where" prefixOverrides="AND|OR">
             <if test="empName != null">
                 and emp_name = #{empName}
             </if>
             <if test="phone != null">
                 and phone = #{phone}
             </if>
         </trim>
     </select>

2) 生成set标签效果

prefix添加前缀set替代set标签,suffixOverrides删除子语句尾部的,

<update id="updateByPrimaryKeySelective">
        update employee
        <trim prefix="set" suffixOverrides=",">
            <if test="empName != null">
                emp_name = #{empName},
            </if>
            <if test="phone != null">
                phone = #{phone},
            </if>
            <if test="address != null">
                address = #{address},
            </if>
            <if test="salary != null">
                salary = #{salary}
            </if>
        </trim>
        where id = #{id}
    </update>

6、foreach迭代遍历

应用于—批量插入或批量删除

1)属性

属性	说明
collection	集合的名字 默认为collection或者list ，可以通过@Param(“listName”)
item	循环出的每个对象 在访问对象属性时，需要加前缀employee.id
open	前缀
close	后缀
separator	以值进行分隔
index	索引(当前迭代的序号)

<insert id="addBath">
        insert into dept values
        <foreach collection="deptList" item="dept" open="(" separator="),(" close=")">
          <!--属性值前加入前缀item值-->
            #{dept.deptId},#{dept.deptName},#{dept.remark}
        </foreach>
         <!--           或者这样写也可以
        <foreach collection="deptList" item="dept" separator=",">
         ( #{dept.deptId},#{dept.deptName},#{dept.remark})
       </foreach>
     -->
    </insert>

7.SQL标签-提取重用的SQL代码片段

通过sql标签封装常使用的字段,通过include标签refid引用其id

 <sql id="base_column_sql">
        account_id ,account_no,account_name,balance
  </sql>
 <select id="queryByNo" resultMap="base-result-account">
        select
        <include refid="base_column_sql"/>
        from account
        where account_no = #{accountNo}
    </select>

8、bind标签

select id, dept_name as deptName, remark from dept where dept_name like #{dn}

9.MyBatis中${}和#{}的区别:

都是用于SQL语句中的占位符?

1. #参数值直接替换到sql语句,并对参数进行类型转换和字符转义处理;可以有效防止SQL语句注入问题;
2. $不会进行类型转换和字符转义处理,可能到值sql注入问题

模糊查询为例:
<select id="getBookByNname" resultType="com.example.demo.entity.BookInfo">
    select * from book_info where book_name like '${bookName}';
</select>

<select id="getBookByName" resultType="com.example.demo.entity.BookInfo">
    select * from book_info where book_name like concat('%',#{bookName},'%');
</select>

book_name like ‘${bookName}’;

select * from book_info where book_name like concat('%',#{bookName},'%'); ~~~