MyBatis动态sql

1）动态sql有什么作用？
在网页当中我们经常会看到勾选或去掉某个参数后，结果动态变化，省去了人工写复杂sql语句，如果达到这样的效果，则需要用mybatis提供的动态sql，以下简单介绍了几个常用多条件查询元素
条件查询：if，where...if
条件更新：update... set/trim
多条件：where...choose...when...otherwise
范围查询：in...foreach

• if条件

<!-- if条件 -->
    <select id = "listID1" resultType = "Product">
        select * from Product_
        <if test = "id = 1">//满足if语句则加入条件
            where id = 1
        </if> 
    </select>

     Map<String, Object> params = new HashMap<>();   
    params.put("id", 1); params.put("name", "product"); params.put("price","88"); List<Product> p1 = session.selectList("whenTest",params);
    for(Product p : p1) { 
          System.out.println(p.getId() + p.getName()); 
        }

• where条件

<!-- where条件 -->
    <select id = "whereTest" resultType = "Product">
        select * from Product_
        <where>
            <if test = "id = 1"> and id = 1</if>
            <if test = "name = 'product b'"> and name = "product b"</if>
            
        </where>
    </select>

• 更新语句: update元素，trim元素可以用来替代set元素

<!-- update -->
    <update id = "updateTest" parameterType = "Product">
        update Product_
        <!-- <set>
            <if test = "name != null"> name = #{name},</if>
            <if test = "price != null"> price = #{price}</if>
        </set> -->
        <trim prefix = "SET" suffixOverrides = ",">
            <if test = "name != null"> name = #{name},</if>
            <if test = "price != null"> price = #{price}</if>
        </trim>
        where id = #{id}
    </update>

• where ...choose... when ...otherwise多条件语句：

<!-- when otherwise -->
    <select id = "whenTest" resultType = "Product">
        select * from Product_
        <where>
            <choose>
                <when test = "name != null">
                    and name like CONCAT("%",#{name},"%")
                </when>
                <when test = "price != null and price != 0">
                    and price > #{price}
                </when>
                <otherwise>
                    and id > 1
                </otherwise>
            </choose>
            
        </where>
    </select>   

• 范围查询in，foreach元素

<!-- in 用法 -->
<select id = "inTest" parameterType = "Product">
    select * from Product_ where id in 
    <foreach item = "item" index = "index" collection = "list" open = "(" separator = "," close = ")" >
      #{item}
        
    </foreach>
    
</select>   

• bind替换：当模糊查询时，可以用 where name like #{likename}，对模糊条件建立一个变量引用

<bind name = "likeName" value = "'%' + name + '%">