第8章 动态SQL

学习目标

● 了解常用的动态SQL元素及其作用

● 掌握动态SQL中主要元素的使用

8.1 动态SQL中的元素

MyBatis 3 OGNL的表达式消除了之前版本中需要了解的大多数元素，使用不到原来一半的元素就能完成所需工作

8.2 <if>元素

1.

2.mapper

<select id="findCustomerByNameAndJobs" parameterType="com.itheima.po.Customer"

        resultType="com.itheima.po.Customer">

    select * from t_customer where 1=1

    <if test="username != null and username !=''">

        and username like concat('%',#{username},'%')

    </if>

    <if test="jobs != null and jobs != '' ">

        and jobs = #{jobs}

    </if>

</select>

test属性多用于条件判断语句中，用于判断真假，大部分的场景中都是进行非空判断，有时候也需要判断字符串、数字和枚举等

#{xx}写成{#xx}

3.测试类

sqlSession.selectList()不传customer对象就只能查询所有

8.3 <choose>、<when>、<otherwise>元素

<select id="findCustomerByNameOrJobs" parameterType="com.itheima.po.Customer"

        resultType="com.itheima.po.Customer">

    select * from t_customer where 1=1

    <choose>

        <when test="username != null and username!= ''">

            and username like concat('%',#{username},'%')

        </when>

        <when test="jobs != null and jobs != ''">

            and jobs = #{jobs}

        </when>

        <otherwise>

            and phone is not null

        </otherwise>

    </choose>

</select>

8.4 <where>、<trim>元素

where 1=1 防止sql语法错误 且 避免了where后面第一个词是and或者or之类的关键词

使用<where>元素对“where 1=1”条件进行了替换

<where>

<if test="username != null and username !=''">

    and username like concat('%',#{username},'%')

</if>

<if test="jobs != null and jobs != '' ">

    and jobs = #{jobs}

</if>

</where>

<trim prefix="where" prefixOverrides="and">

<if test="username != null and username !=''">

    and username like concat('%',#{username},'%')

</if>

<if test="jobs != null and jobs != '' ">

    and jobs = #{jobs}

</if>

</trim>

<trim>元素的作用是去除一些特殊的字符串，它的prefix属性代表的是语句的前缀（这里使用where来连接后面的SQL片段），而prefixOverrides属性代表的是需要去除的那些特殊字符串（这里定义了要去除SQL中的and），上面的写法和使用<where>元素基本是等效的。

8.5 <set>元素

让程序只更新需要更新的字段

<set>元素主要用于更新操作，其主要作用是在动态包含的SQL语句前输出一个SET关键字，并将SQL语句中最后一个多余的逗号去除。

<update id="updateCustomer" parameterType="com.itheima.po.Customer">

        update t_customer

        <set>

            <if test="username != null and username !=''">

            username=#{username}

            </if>

            <if test="jobs != null and jobs !=''">

                jobs=#{jobs}

            </if>

            <if test="phone != null and phone !=''">

                phone=#{phone}

            </if>

        </set>

        where id = #{id}

    </update>

### Cause: org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='username', mode=IN, javaType=class java.lang.String, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting null for parameter #3 with JdbcType OTHER . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: Parameter index out of range (3 > number of parameters, which is 2).

原因：mapper文件中的注释没消干净 

方法：消除--

在映射文件中使用<set>和<if>元素组合进行update语句动态SQL组装时，如果<set>元素内包含的内容都为空，则会出现SQL语法错误。所以在使用<set>元素进行字段信息更新时，要确保传入的更新字段不能都为空。

8.6 <foreach>元素

<!--    foreach-->

    <select id="findCustomerByIds" parameterType="List" resultType="com.itheima.po.Customer">

        select * from t_customer where id in

        <foreach collection="list" index="index" item="id"

        open="(" separator="," close=")">

            #{id}

        </foreach>

    </select>

· item：配置的是循环中当前的元素。

· index：配置的是当前元素在集合的位置下标。

· collection：配置的list是传递过来的参数类型（首字母小写），它可以是一个array、list（或collection）、Map集合的键、POJO包装类中数组或集合类型的属性名等。

· open和close：配置的是以什么符号将这些集合元素包装起来。

· separator：配置的是各个元素的间隔符。

当使用可迭代对象或者数组时，index是当前迭代的次数，item的值是本次迭代获取的元素。当使用字典（或者Map.Entry对象的集合）时，index是键，item是值。

@Test

public void findCustomerByIdsTest(){

    SqlSession sqlSession = MybatisUtils.getSession();

    ArrayList<Integer> ids = new ArrayList<>();

    ids.add(1);

    ids.add(2);

    List<Customer> customers =sqlSession.selectList("com.itheima.mapper.CustomerMapper.findCustomerByIds", ids);

    for (Customer customer1 : customers){

        System.out.println(customer1);

    }

    sqlSession.close();

}

collection属性是必须指定的，而且在不同情况下，该属性的值是不一样的。

（1）传入的是单参数且参数类型是一个数组或者List，collection属性值分别为array和list（或collection）。

（2）传入的参数是多个，collection属性值就为Map的键

（3）传入的参数是POJO包装类，collection属性值就为该包装类中需要进行遍历的数组或集合的属性名

所以在设置collection属性值的时候，必须按照实际情况配置，否则程序就会出现异常。

8.7 <bind>元素

在进行模糊查询编写SQL语句的时候，如果使用“${}”进行字符串拼接，则无法防止SQL注入问题；如果使用concat函数进行拼接，则只针对MySQL数据库有效；如果使用的是Oracle数据库，则要使用连接符号“||”。

MyBatis的<bind>元素可以通过OGNL表达式来创建一个上下文变量，其使用方式如下：

<select id="findCustomerByName1" parameterType="com.itheima.po.Customer"

        resultType="com.itheima.po.Customer">

    <bind name = "pattern_username" value="'%'+_parameter.getUsername()+'%"/>

    select * from t_customer

    where

    username like #{pattern_username}

</select>

使用<bind>元素定义了一个name为pattern_username的变量，<bind>元素中value的属性值就是拼接的查询字符串，其中_parameter.getUsername()表示传递进来的参数（也可以直接写成对应的参数变量名，如username）

 

【思考题】

1．请简述MyBatis框架动态SQL中的主要元素及其作用。

select id parameterType resultType 

选择标签 id是唯一标志 传入参数类型 返回参数类型×

2．请简述MyBatis框架动态SQL中<foreach>元素collection属性的注意事项。

如果是单个参数，返回array或者list

如果是多个参数，返回map

如果是指定类，返回指定类的集合