# 5. 动态 SQL

# 5.0 OGNL 表达式

访问对象属性： person.name

调用方法：person.getName()

调用静态属性：@java.lang.Math@PI

调用静态方法：@java.util.UUID@randomUUID()

调用构造方法：new com.heon.bean.Employee("name").name

算术运算符：+ - * / %

逻辑运算符：in，not in，>，>=，>，>=，==，!=

访问集合伪属性：

类型	伪属性	伪属性对应的 java 方法
List、Set、Map	size、iSEmpty	List/Set/Map.size()，List/Set/Map.isEmpty()
List、Set	iterator	List.iterator()、Set.iterator()
Map	keys、values	Map.keySet()、Map.values()
Iterator	next、hasNext	Iterator.next()、Iterator.hasNext()

# 5.1 if

List<Employee> getEmpsByCondition(Employee employee) ;

<!--查询员工：携带哪个字段就按这个字段来查询-->
<select id="getEmpsByCondition" resultType="com.hedon.bean.Employee">
    SELECT * FROM tbl_employee
    WHERE 1=1        <!-- 1=1 是为了连接后面，不然如果只剩一个 where 的话语法是错误的 -->
    <!--text: 判断表达式(OGNL)-->
    <if test="id!=null">
        AND id = #{id}
    </if>
    <if test="lastName!=null and lastName.trim()!=''">
        AND last_name like #{lastName}
    </if>
    <if test="email!=null and email.trim()!=''">
        AND email = #{email}
    </if>
    <if test="gender==0 or gender==1">
        AND gender = #{gender}
    </if>
</select>

测试：带了 id 和 email

EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Employee employee = new Employee();
employee.setId(1);
employee.setEmail("1111");
List<Employee> empsByCondition = mapper.getEmpsByCondition(employee);
System.out.println(empsByCondition);

测试：只带 lastName

EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Employee employee = new Employee();
employee.setLastName("hedon");
List<Employee> empsByCondition = mapper.getEmpsByCondition(employee);
System.out.println(empsByCondition);

由上可以看到我们实现了 携带哪个字段就按这个字段来查询 的功能。

# 5.2 where

上面我们在拼接条件的时候，为了防止 where 后面没有字段而导致 SQL 语法错误我们就加上了 where 1=1。这是可以解决问题的，但是似乎不太优雅。MyBatis 提供了一个新的标签 where 可以帮助我们解决这个问题。

<!--查询员工：携带哪个字段就按这个字段来查询-->
<select id="getEmpsByCondition" resultType="com.hedon.bean.Employee">
    SELECT * FROM tbl_employee
    <!--where 会自动帮我们把前面多余的 and 和 or 去掉-->
    <where>
        <if test="id!=null">
            AND id = #{id}
        </if>
        <if test="lastName!=null and lastName.trim()!=''">
            AND last_name like #{lastName}
        </if>
        <if test="email!=null and email.trim()!=''">
            AND email = #{email}
        </if>
        <if test="gender==0 or gender==1">
            AND gender = #{gender}
        </if>
    </where>
</select>

# 5.3 trim

上面的 where 有一个问题，它只能去掉 前面的 AND。如果 AND 是写在后面的话，如：

<!--查询员工：携带哪个字段就按这个字段来查询-->
<select id="getEmpsByCondition" resultType="com.hedon.bean.Employee">
    SELECT * FROM tbl_employee
    <!--where 会自动帮我们把前面多余的 and 和 or 去掉-->
    <where>
        <if test="id!=null">
            id = #{id} AND
        </if>
        <if test="lastName!=null and lastName.trim()!=''">
            last_name like #{lastName} AND
        </if>
        <if test="email!=null and email.trim()!=''">
            email = #{email} AND
        </if>
        <if test="gender==0 or gender==1">
            gender = #{gender}
        </if>
    </where>
</select>

那么当 gender 属性为 null 的话，SQL 语句最后就会多出一个 AND 而报错。

如果要解决这个问题，一个思路就是老老实实把 AND 写在前面，另外一个思路就是可以使用 trim 标签，它有 4 个属性：

prefix：前缀。给 trim 标签拼串后的整个字符串加一个前缀。
prefixOverrides：前缀覆盖。去掉整个字符串前面多余的字符，如 AND，OR
suffix：后缀。给 trim 标签拼串后的整个字符串加一个后置。
suffixOverrides：后缀覆盖。去掉整个字符串前面多余的字符，如 AND，OR

这样不管是在前面加 AND 还是在后面加 AND，我们都有办法应对了。

<!--查询员工：携带哪个字段就按这个字段来查询-->
<select id="getEmpsByCondition" resultType="com.hedon.bean.Employee">
    SELECT * FROM tbl_employee
    <trim prefix="where" prefixOverrides="AND" suffixOverrides="AND">
        <if test="id!=null">
            id = #{id} AND
        </if>
        <if test="lastName!=null and lastName.trim()!=''">
            last_name like #{lastName} AND
        </if>
        <if test="email!=null and email.trim()!=''">
            email = #{email} AND
        </if>
        <if test="gender==0 or gender==1">
            gender = #{gender}
        </if>
    </trim>
</select>

# 5.4 choose(when/otherwise)

前面的三个标签都是把所有的条件都拼接起来，如果你只带了 id，就那按 id 查询，如果你 id 和 lastName 一起带了，那就按这两个条件来查询。下面的这个 choose 标签，其实是类似于我们的 switch-case 语句，只选择一个属性进行查询。

<select id="getEmpsByCondition" resultType="com.hedon.bean.Employee">
    SELECT * FROM tbl_employee
    <where>
        <choose>
            <when test="id!=null">
                id = #{id}
            </when>
            <when test="lastName!=null and lastName.trim()!=''">
                last_name = #{lastName}
            </when>
            <when test="email!=null and email.trim()!=''">
                email = #{email}
            </when>
            <otherwise>
                gender = 1
            </otherwise>
        </choose>
    </where>
</select>

测试：只带 lastName

测试：同时带 id 和 lastName

测试：都不带

# 5.5 set

前面都是在封装查询条件，而 set 标签是用来封装我们的修改条件的。

void updateEmp(Employee employee);

<update id="updateEmp" parameterType="com.hedon.bean.Employee">

    UPDATE tbl_employee
    <set>
        <if test="lastName!=null and lastName.trim()!=''">
            last_name = #{lastName},
        </if>
        <if test="email!=null and email.trim()!=''">
            email = #{email},
        </if>
        <if test="gender==0 or gender ==1">
            gender = #{gender}
        </if>
    </set>
    WHERE id = #{id}
</update>

测试：

employee.setId(1);
employee.setLastName("lalalal");
employeeMapper.updateEmp(employee);

也可以使用 trim 来代替，也可以配置自动去掉拼接后多出的逗号：

<update id="updateEmp" parameterType="com.hedon.bean.Employee">
    UPDATE tbl_employee
    <trim prefix="set" suffixOverrides=",">
        <if test="lastName!=null and lastName.trim()!=''">
            last_name = #{lastName},
        </if>
        <if test="email!=null and email.trim()!=''">
            email = #{email},
        </if>
        <if test="gender==0 or gender ==1">
            gender = #{gender}
        </if>
    </trim>
    WHERE id = #{id}
</update>

# 5.6 foreach

我们可以用 foreach 标签来遍历传进来的集合参数：

collection：指定要遍历的集合，可以有 3 种值，collection，list，map

list 类型的参数会特殊处理封装在 map 中，map 的 key 就叫 list

item：指当前遍历出的元素赋值给特定的变量
separator：每个元素之间的分隔符
open：遍历出所有结果后拼接一个开始的字符，如果集合为空的话是不会拼的
close：遍历出所有结果后拼接一个结束的字符，如果集合为空的话是不会拼的
index：索引。
- 遍历 list 的时候 index 就是索引，item 就是当前值
- 遍历 map 的时候 index 表示的是 map 的key，item 表示的是值
#{变量名}：取出变量的值

# 5.6.1 批量查询

<select id="getEmpsByIds" resultType="com.hedon.bean.Employee">
    SELECT * FROM tbl_employee
    <foreach collection="list" item="item" separator="," open="WHERE id IN (" close=")" index="index">
        #{item}
    </foreach>
</select>

# 5.6.2 批量插入一

<insert id="insertEmps">
    INSERT INTO tbl_employee(last_name,email,gender,d_id)
    VALUES
    <foreach collection="list" item="emp" separator=",">
        (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.department.id})
    </foreach>
</insert>

上述可以实现批量插入 Employee，但是如果传进来的 list 是空的话，就会报错。

# 5.6.3 批量插入二

<insert id="insertEmps">
    <foreach collection="list" item="emp" separator=";">
        INSERT INTO tbl_employee(last_name,email,gender,d_id)
        VALUES (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.department.id})
    </foreach>
</insert>

这种方式需要开启 MySQL 的多语句执行：allowMultiQueries=true

<dataSource type="POOLED">
    <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
    <property name="url" value="jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true"/>
    <property name="username" value="root"/>
    <property name="password" value="root"/>
</dataSource>

# 5.7 bind

bind 可以将一个 OGNL 表达式的值绑定到一个变量中，方便后面使用。

List<Employee> getEmpsByLastName(String lastName);

<select id="getEmpsByLastName" resultType="com.hedon.bean.Employee">
    <bind name="_lastName" value="'%'+lastName+'%'"/>
    SELECT * FROM tbl_employee WHERE last_name LIKE #{_lastName}
</select>

List<Employee> emps = mapper.getEmpsByLastName("h");

比如上面，我们希望进行模糊查询，并且不希望在传参的时候加上 %%，但是如果我们想用

<select id="getEmpsByLastName" resultType="com.hedon.bean.Employee">
    SELECT * FROM tbl_employee WHERE last_name LIKE '%#{_lastName}%'
</select>

来完成目的是不成功的，#{} 是不支持拼接的。

虽然我们可以用 ${} 来实现：

<select id="getEmpsByLastName" resultType="com.hedon.bean.Employee">
    SELECT * FROM tbl_employee WHERE last_name LIKE '%${_lastName}%'
</select>

但是这样就可能会有 SQL 注入攻击的风险。

所以我们就可以用 bind 来进行绑定，用 _lastName 来替代 %lastName%，从而实现模糊查询。

# 5.8 sql

sql 标签用来抽取可重用的字段，方便后面引用，也支持动态判断。

<sql id="basicColumn">
	id,name,gender,address
</sql>

<seletc id="getUsersInfo">
	select
  <include refid="basicColumn"/>
  from tbl_employee
</seletc>

← 4. MyBatis 映射文件 6. 两个内置参数 →