MyBatis——动态SQL

MyBatis动态SQL

MyBatis 的一个强大的特性之一就是它的动态 SQL 能力。也就是可以根据不同的条件拼接SQL语句。

以下是动态SQL在XML中支持的几种元素

if

if元素通常用于where语句中，通过判断参数值来决定是否使用某个查询条件

假设现在有一个需求：实现一个用户管理高级查询功能，根据用户输入的条件去检索用户信息

创建实体类User和映射器接口

package org.example.entity;

import lombok.Data;

@Data
public class User {
    private Integer userId;
    private String userName;
    private String password;
}

package org.example.mapper;

import com.github.pagehelper.Page;
import org.apache.ibatis.annotations.Param;
import org.example.entity.User;

import java.util.List;
import java.util.Map;
import java.util.Objects;

public interface UserMapper {
    User findUser(Map<String,String> data);
}

不使用动态SQL时，实现上述需求的映射文件如下：

<mapper namespace="com.mybatis.mapper.UserMapper">
    <select id="findUserByPhoneOrEmail"    
            resultType="com.mybatis.mapper.User">
        select * 
        from t_user 
        where user_name = #{userName} and password = #{password}
    </select>
</mapper>

调用该方法时，传入的Map类型的实际参数中，必须存在userName和password这两个key值

不使用动态SQL时，只有同时输入phone和email两个条件时，才能查出正确结果，此时需要if元素来解决这个问题

<select id="findUserByPhoneOrEmail" resultType="User">
     select * 
  from user where 1 = 1
  <if test="userName != null and !''.equals(userName)">
        and user_name = #{userName}
  </if>
  <if test="password != null and !''.equals(password)">
        and password = #{password}
  </if>
</select>

如果不想让where条件中出现“1=1”这种表达式时，就需要使用where元素 where元素的作用：如果该元素中有内容，就在生成SQL语句时加上where条件，如果该元素的内容以AND或者OR开头，就去除这两个单词

    <resultMap id="userMap" type="User">
        <result property="userId" column="user_id"/>
        <result property="userName" column="user_name"/>
        <result property="password" column="password"/>
    </resultMap>
    <select id="findUser" resultMap="userMap">
        
        动态sql
        select * from t_user 
        <where>
            <if test="userName != null and !''.equals(userName)">
                and user_name = #{userName}
            </if>
            <if test="password != null and !''.equals(password)">
                and password = #{password}
            </if>
        </where>
                                 
    </select>

使用if元素实现动态列更新，即更新用户信息时，只更新有变化的字段

   <update id="updateUser">
        update t_user set
        <if test="userName != null and !''.equals(userName)">
            user_name = #{userName},
        </if>
        <if test="password != null and !''.equals(password)">
            password = #{password},
        </if>
        user_id = #{userId}
        where user_id = #{userId}
    </update>

实现动态列更新时，可以使用set元素 set元素作用：当该元素有内容时，生成SQL语句时就加上set语句；当set元素的内容以逗号结尾时，去掉逗号

    <update id="updateUser">
        update t_user
        <set>
            <if test="userName != null and !''.equals(userName)">
                user_name = #{userName},
            </if>
            <if test="password != null and !''.equals(password)">
                password = #{password},
            </if>
        </set>
        where user_id = #{userId}
    </update>

choose (when, otherwise)

choose元素中包含when和otherwise两个子元素一个choose元素中至少有一个when子元素一个choose元素中可以包含0个或1个otherwise子元素使用choose元素可以实现类似if…else…的逻辑

    <select id="findUserByUserIdOrUserName" resultMap="userMap">
        select * from t_user where 1=1
        <choose>
            <when test="userId != null and !''.equals(userId)">
                and user_id = #{userId}
            </when>
            <when test="userName != null and !''.equals(userName)">
                and user_name = #{userName}
            </when>
        </choose>
    </select>

trim (where, set)

trim元素可以实现where和set元素的功能 trim元素对应的where功能的实现

trim元素的属性，都在trim元素包含内容时起作用：

prefix：给内容增加该属性指定的前缀

prefixOverrides：把内容中匹配的前缀字符串去掉

suffix：给内容增加该属性指定的后缀

suffixOverrides：把内容中匹配的后缀字符串去掉

    <select id="findUser" resultMap="userMap">
        select * from t_user
        <trim prefix="where" prefixOverrides="and">
            <if test="userName != null and !''.equals(userName)">
                and user_name = #{userName}
            </if>
            <if test="password != null and !''.equals(password)">
                and password = #{password}
            </if>
        </trim>

    </select>

    <update id="updateUser">
        update t_user
        <trim prefix="set" suffixOverrides=",">
            <if test="userName != null and !''.equals(userName)">
                user_name = #{userName},
            </if>
            <if test="password != null and !''.equals(password)">
                password = #{password},
            </if>
        </trim>
        where user_id = #{userId}
    </update>

foreach

foreach可以对数组、Map或实现了Iterable接口（如：List、Set）的对象进行遍历映射接口中添加如下方法

package org.example.mapper;

import com.github.pagehelper.Page;
import org.apache.ibatis.annotations.Param;
import org.example.entity.User;

import java.util.List;
import java.util.Map;
import java.util.Objects;

public interface UserMapper {
    List<User> findUserByDy(@Param("map") Map<String, Object> data);
}

    <select id="findUserByDy" resultMap="userMap">
        select * from t_user where 1=1 and
        <foreach collection="map" item="value" index="key" separator="and">
            ${key} = #{value}
        </foreach>
    </select>

foreach各个属性的含义:

collection属性的取值有多种情况

当参数为List集合时，collection取值为list

当参数为数组时，collection取值为array

当参数为Map类型时，collection取值默认情况下为_parameter，也可以使用@Param注解指定名字，collection取值为该注解指定的名字

foreach还能实现批量插入。目前支持批量插入的数据库有DB2、SQL Server 2008及以上版本、MySQL等在映射接口中添加批量插入的方法

package org.example.mapper;

import com.github.pagehelper.Page;
import org.apache.ibatis.annotations.Param;
import org.example.entity.User;

import java.util.List;
import java.util.Map;
import java.util.Objects;

public interface UserMapper {
    int insertUserBatch(List<User> userList);
}

MySQL数据库还支持批量插入后，返回所有记录的主键的操作，MyBatis要想实现此功能，需要修改XML映射文件

    <insert id="insertUserBatch" useGeneratedKeys="true" keyProperty="userId">
        insert into t_user(user_name,password) values
        <foreach collection="list" item="user" separator=",">
            (#{user.userName},#{user.password})
        </foreach>
    </insert>

foreach实现动态更新，当参数是Map类型时，foreach元素的index属性值对应的不是索引值，而是Map中的key 实现通过指定的列名和对应的值去更新数据，在映射接口中添加如下方法

public int updateByMap(Map<String, Object> map);

这里key作为列名，对应的值作为该列的值，通过foreach将需要更新的字段拼接在SQL语句中

<update id="updateByMap">
    update user set
    <foreach collection="_parameter" item="value" 
        index="key" separator=",">
        ${key} = #{value}
    </foreach>
    where id = #{id}
</update>

上述方法在调用时，Map类型的实参中的key值要与表中字段名对应也可以将映射器接口中方法的参数添加@Param注解

public int updateByMap(@Param("map")Map<String, 
    Object> map);

映射文件修改如下

<update id="updateByMap">
    update user set
    <foreach collection="map" item="value" 
        index="key" separator=",">
        ${key} = #{value}
    </foreach>
    where id = #{map.id}
</update>

bind

bind可以使用OGNL表达式创建一个变量并将其绑定到上下文中。如：在模糊查询中可以使用

public List<User> findUserLike(
                     @Param("name")String name);