【MyBatis】使用常见的“动态SQL”

【MyBatis】使用常见的“动态SQL”

一. 前置知识

XML配置：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- XML映射文件的namespace属性为Mapper接口全限定名一致 -->
<mapper namespace="Mapper接口 Copy Reference">

</mapper>

XML映射文件中sql语句的id与Mapper接口中的方法名一致，并保持返回类型一致

配置日志输入

在Mybatis当中我们可以借助日志，查看到sql语句的执行、执行传递的参数以及执行结果。具体操作如下：

1. 打开application.properties文件

2. 开启mybatis的日志，并指定输出到控制台

#指定mybatis输出日志的位置, 输出控制台
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

开启日志之后，我们再次运行单元测试，可以看到在控制台中，输出了SQL语句信息

@Param注解

@Param是MyBatis所提供的作为Dao层的注解，作用是用于传递参数，从而可以与SQL中的的字段名相对应。\

List<PreEndBlogListVO> selectBlogListByTypeId(@Param("typeId") Integer id);

上述@Param作用其实就是将id重命名为typeId。因此，我们可以在xml中这样使用：

  <select id="selectBlogListByTypeId" resultMap="preEndBlogListMap">
       select id,title,description from blog where type_id=#{typeId,jdbcType=INTEGER}
  </select>

参数占位符

在Mybatis中提供的参数占位符有两种：${…} 、#{…}

• #{…}

  • 执行SQL时，会将#{…}替换为?，生成预编译SQL，会自动设置参数值
  • 使用时机：参数传递，都使用#{…}

• ${…}

  • 拼接SQL。直接将参数拼接在SQL语句中，存在SQL注入问题
  • 使用时机：如果对表名、列表进行动态设置时使用

注意事项：在项目开发中，建议使用#{…}，生成预编译SQL，防止SQL注入安全。

预编译SQL:

性能更高：预编译SQL，编译一次之后会将编译后的SQL语句缓存起来，后面再次执行这条语句时，不会再次编译。（只是输入的参数不同）

更安全(防止SQL注入)：将敏感字进行转义，保障SQL的安全性。

字符串拼接函数：concat(‘%’ , ‘关键字’ , ‘%’)

生成的SQL都是预编译的SQL语句（性能高、安全），防止SQL注入

二.动态SQL标签

1.if标签

​ 通过test属性中的表达式去判断内容是否有效，有效则将if中的sql片段拼接执行。

例如：在菜品分页查询表，根据菜品名称，菜品表中的套餐分类，售卖状态条件查询 菜品名称、菜品分类，售价，售卖状态，图片等信息。

接受的参数从DTO来，返回到VO用于传递给前端。

/**
     * 菜品分页查询
     * @param dishPageQueryDTO
     * @return
     */
    Page<DishVO> pageQuery(DishPageQueryDTO dishPageQueryDTO);

<select id="pageQuery" resultType="com.sky.vo.DishVO">
        select d.* , c.name as categoryName from dish d left outer join category c on d.category_id = c.id
        <where>
            <if test="name != null">
                and d.name like concat('%',#{name},'%')
            </if>
            <if test="categoryId != null">
                and d.category_id = #{categoryId}
            </if>
            <if test="status != null">
                and d.status = #{status}
            </if>
        </where>
        order by d.create_time desc
</select>

2.where标签

where标签特性：只有if标签有内容的情况下才会拼接where字句；若子句的开通为"and"或"or"，where标签会将它替换去除。需要注意的是：where标签只会智能的去除首个满足条件语句的前缀，所以建议在使用where标签时，每个语句都最好写上 and 前缀或者 or 前缀。

3.set标签

<set>：动态的在SQL语句中插入set关键字，并会删掉额外的逗号。（用于update语句中）

<!--更新操作-->
    <update id="update">
        update emp
        <!-- 使用set标签，代替update语句中的set关键字 -->
        <set>
            <if test="username != null">
                username=#{username},
            </if>
            <if test="name != null">
                name=#{name},
            <if test="deptId != null">
                dept_id=#{deptId},
            </if>
            <if test="updateTime != null">
                update_time=#{updateTime}
            </if>
        </set>
        where id=#{id}
    </update>

4.foreach标签

SQL语句：

delete from emp where id in (1,2,3);

Mapper接口：

@Mapper
public interface EmpMapper {
    //批量删除
    public void deleteByIds(List<Integer> ids);
}

XML映射文件：

• 使用<foreach>遍历deleteByIds方法中传递的参数ids集合

<foreach collection="集合名称" item="集合遍历出来的元素/项" separator="每一次遍历使用的分隔符" 
         open="遍历开始前拼接的片段" close="遍历结束后拼接的片段">
</foreach>

    <!--删除操作-->
    <delete id="deleteByIds">
        delete from emp where id in
        <!--item="id" 等于这里的 #{id}-->
        <foreach collection="ids" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>

执行得SQL语句：delete from emp where id in (?,?,?)

5.include标签

我们可以对重复的代码片段进行抽取，将其通过<sql>标签封装到一个SQL片段，然后再通过<include>标签进行引用。

• <sql>：定义可重用的SQL片段
• <include>：通过属性refid，指定包含的SQL片段

reference id :参考id

SQL片段： 抽取重复的代码

<sql id="commonSelect">
 	select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time from emp
</sql>

然后通过<include> 标签在原来抽取的地方进行引用。操作如下：

<select id="list" resultType="com.itheima.pojo.Emp">
    <include refid="commonSelect"/>
    <where>
        <if test="name != null">
            name like concat('%',#{name},'%')
        </if>
        <if test="gender != null">
            and gender = #{gender}
        </if>
        <if test="begin != null and end != null">
            and entrydate between #{begin} and #{end}
        </if>
    </where>
    order by update_time desc
</select>