动态sql

动态sql

在写sql语句时如果使用语句拼接，根据不同的参数组织不同的语句，经常出现少或多一个and，缺少空格，最后出现逗号等问题，mybatis通过动态sql来解决这些问题。

mybatis中包含有if、choose、when、otherwise、trim、where、set、foreach等标签

动态sql中的表达式使用的是OGNL表达式

if标签

使用if标签来判断是否符合该条件<if test="">，if标签结合test属性来使用

<!-- 使用动态sql  测试if标签 -->
<select id="getEmployeeByConditon" resultMap="emp">
    select * from employee where id = #{id}
        <if test="name != null and name!=''">
            and name like #{name}
        </if>
        <if test="salary != null and salary != 0">
            and name = #{salary}
        </if>
</select>

where标签

可能遇到的情况

有时通过if条件判断之后导致后续一个条件都没有，此时SQL就变成了

select * from employee where

此时将不需要使用where条件

也有可能会出现有一个条件符合，但是会多出来一个and，此时SQL就变成了

select * from employee where and id = 1

很明显，这个多出来的and会导致报错

出现这种情况有两种方式解决

• 可以使用1=1条件
• 可以使用where标签，where会将where标签中拼接的sql中开头多出来的and或者or去掉
• 可以使用trim

<!-- 使用动态sql  测试if和where标签 -->
<select id="getEmployeeByCondition" resultMap="emp">
    select * from employee
    <where>
        <if test="id != null and id != 0">
            and id = #{id}
        </if>
        <if test="name != null and name!=''">
            and name like #{name}
        </if>
        <if test="salary != null and salary != 0">
            and salary = #{salary}
        </if>
    </where>
</select>

trim标签

由于where只会去除开头的and或or，有时使用where元素与预期不符，此时可以使用trim标签

trim标签有几个子元素prefix/prefixOverrides/suffixOverrides

• prefix 前缀，trim标签体中是整个字符串拼接后的结果，prefix给拼串后的整个字符串加一个前缀
• prefixOverrides 前缀覆盖，去掉整个字符串中前面多余的字符
• suffix 后缀，在整个字符串后加一个后缀
• suffixOverrides 后缀覆盖，去掉整个字符串中后面多余的字符

<select id="getEmployeeByCondition" resultMap="emp">
    select * from employee
    <trim prefix="where" suffixOverrides="and | or">
        <if test="id != null and id != 0">
            id = #{id} and
        </if>
        <if test="name != null and name!=''">
            name like #{name} and
        </if>
        <if test="salary != null and salary != 0">
            salary = #{salary} and
        </if>
    </trim>
</select>

where标签可以使用trim来表示 <trim prefix="where" suffixOverrides="and | or">

set标签可以用trim来表示 <trim prefix="SET" suffixOverrides=",">

choose标签

choose与when和otherwise组合使用，根据when标签中的test条件是否成立来进行分支选择，相当于switch-case，只会进入一个分支，如果有一个成立，则choose结束，如果when都不满足，则拼接otherwise标签中的sql

<select id="getEmployeeByConditionChoose" resultMap="emp">
    select * from employee
    <trim prefix="where" suffixOverrides="and | or" prefixOverrides="and | or">
        <!-- 有id时用id查，有name时用name查，否则查询所有 -->
        <choose>
            <when test="id != null and id != 0">
                id = #{id} and
            </when>
            <when test="name != null and name!=''">
                name like #{name} and
            </when>
            <otherwise>

            </otherwise>
        </choose>
    </trim>
</select>

set标签

可能遇到的情况

对于update语句，也可能出现经过if判断之后一个条件都不符合的情况，此时sql语句就会变成

update employee set where id = 1

很明显此时的set就不应该存在了

也可能会出现这种情况，最后一条if判断不成立，导致最后多出来一个,，此时sql语句就变成了

update employee set name='张三', where id = 1

这个sql语句也不会执行成功

set标签用于动态更新，只更新需要更新的列，忽略其他不更新的列，set元素会在字符串开头添加set，并且会删除掉额外的逗号

<update id="updateEmployeeBySet">
    update employee
    <set>
        <if test="name != null and name != ''">
            name = #{name},
        </if>
        <if test="salary != null and salary != 0">
            salary = #{salary},
        </if>
    </set>
    where id = #{id}
</update>

也可以使用trim来实现

<update id="updateEmployeeBySet">
    update employee
    <trim prifix="set" suffixOverrides=",">
        <if test="name != null and name != ''">
            name = #{name},
        </if>
        <if test="salary != null and salary != 0">
            salary = #{salary},
        </if>
    </trim>
    where id = #{id}
</update>

foreach标签

使用foreach标签来进行对集合的遍历

<select id="selectByIds" resultType="User">
    select * from user
    <where>
        id in
          <!-- collection:指定遍历的集合  list类型参数会特殊封装到map中，key为list；array类型参数封装到map，key为array
                          item: 将当前遍历出的元素赋值给指定的变量，元素别名
                         separator: 每个元素之间的分隔符
                         open: 遍历所有结果拼接一个开始的字符
                         close: 遍历所有结果拼接一个结束的字符
                       index: 索引。遍历list的时候，index是索引，item是当前值；遍历map的时候，index是map的key，item是map的值
                -->
        <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </where>
</select>

foreach还可以实现批量插入

<insert id="insertList">
   insert into student(name, phone, sex)
   values
   <foreach collection="list" item="student" separator=",">
     (
     #{student.name}, #{student.phone},
     #{student.sex}
     )
   </foreach>
 </insert>

模糊查询

有时可能需要进行模糊查询，需要拼接%，可以使用concat

<select id="selectByName" resultType="User">
    select * from user
  <where>
      <if test="name != null">
        name like concat(#{name},'%')
    </if>
  </where>
</select>

内置参数

mybatis包含两个内置参数

• _parameter：代表整个参数。单个参数时，_parameter就是这个参数;多个参数时，参数会被封装为一个map，_parameter代表这个map
• _databaseId:如果配置了databaseIdProvider标签，_databaseId就是代表当前数据库的别名

<select id="selectUserByCondition" resultType="User">
    select * from user
    <if test="_parameter != null">
        <where>
            <if test="id != null and id != 0">
                and id = #{_parameter.id}
            </if>
            <if test="name != null">
                and name = #{_parameter.name}
            </if>
            <if test="account != null and account != 0">
                and account = #{_parameter.account}
            </if>
        </where>
    </if>
</select>

bind绑定

bind可以将OGNL表达式的值绑定到一个变量中，并将其绑定到当前上下文，方便后来引用这个变量的值

<select id="selectUserByCondition" resultType="User">
    select * from user
    <where>
        <if test="id != null and id != 0">
            and id = #{id}
        </if>
        <if test="name != null">
        <bind name="_name" value="'%'+name+'%'"/>
            and name like #{_name}
        </if>
        <if test="account != null and account != 0">
            and account = #{account}
        </if>
    </where>
</select>

sql标签

sql标签用于抽取可重用的sql片段，使用include标签来引用sql标签

• 经常将查询的列名和插入的列名抽取出来
• include来引用抽取的sql
• include还可以自定义一些property，sql标签内部可以使用自定义的属性，使用${属性名}来取自定义的属性

<sql id="insertColumn">
    `name`,account
</sql>

<insert id="addUser">
  insert into `user` (
    <include refid="insertColumn">
    </include>
  )
  values (#{name},#{account})
</insert>