MyBatis学习笔记——动态Sql语句

Emp.java

public class Emp {
    private Integer id;
    private String eName;
    private Integer age;
    private String sex;
    private Dept dept;
}

if

<if>：根据条件，动态拼接sql语句。
test：不需要添加 #{} 或 ${}，默认在传入的参数中搜索。

若 test 中判断的是字符串，用单引号时，Java 会将其解析为字符类型，因此要使用''.toString()将其转换为字符串类型，如'男'.toString()

EmpMapper.java

/**
 * 根据emp对象的id，name,age,sex查找员工
 * @param emp
 * @return List<Emp>
 */
List<Emp> getEmpByElements(Emp emp);

EmpMapper.xml

<!-- List<Emp> getEmpByElements(Emp emp); -->
<!--
    if标签：
        根据条件，动态拼接sql语句
        test属性：不需要添加#{}或${}，默认在传入的参数中搜索
-->
<select id="getEmpByElements" resultType="Emp">
	select id,eName,age,sex from employee
    where 1=1
      <!--判断传入的参数中id是否为空，不为空时，根据id查询-->
      <if test="id != null">
          and id = #{id}
      </if>
      <if test="eName != null and eName !='' ">
          and eName = #{eName}
      </if>
      <if test="age != null">
          and age= #{age}
      </if>
      <if test="sex == '男'.toString() or sex == '女'.toString()">
          and sex = #{sex}
      </if>
</select>

where

<where>：添加类似上面 where 1=1 的条件，并去除 sql 语句拼接之后语句前面多余的逻辑连接词，如and、or等。

EmpMapper.java

/**
 * 根据emp对象的id，name,age,sex查找员工
 * @param emp
 * @return List<Emp>
 */
List<Emp> getEmpByElements(Emp emp);

EmpMapper.xml

<!--
    where标签：
        添加类似上面 where 1=1 的条件，并去除sql语句拼接之后语句前面多余的逻辑连接词，如and、or等
-->
<select id="getEmpByElements222" resultType="Emp">
    select id,eName,age,sex from employee
    <where>
        <if test="id != null">
            and id = #{id}
        </if>
        <if test="eName != null and eName !='' ">
            and eName = #{eName}
        </if>
        <if test="age != null">
            and age= #{age}
        </if>
        <if test="sex == '男'.toString() or sex == '女'.toString()">
            and sex = #{sex}
        </if>
    </where>
</select>

trim

<trim>：
prefix：在第一条 sql 语句前添加某些内容。
suffix：在最后一条 sql 语句后添加某些内容。
prefixOverrides：删除第一条 sql 语句前的某些内容。
suffixOverrides：删除最后一条 sql 语句后的某些内容。

prefixOverrides 与 suffixOverrides 中要删除的内容有多个时，可以用竖线|连接，如删除最后一条语句后的 and 或 or，则suffixOverrides="and|or"

EmpMapper.java

/**
 * 根据emp对象的id，name,age,sex查找员工
 * @param emp
 * @return List<Emp>
 */
List<Emp> getEmpByElements(Emp emp);

EmpMapper.xml

<select id="getEmpByElements" resultType="Emp">
    select id,eName,age,sex from employee
    <!--在sql语句前添加where，去除语句后多余的and或or-->
    <trim prefix="where" suffixOverrides="and|or">
        <if test="id != null">
            id = #{id} and
        </if>
        <if test="eName != null and eName !='' ">
            eName = #{eName} and
        </if>
        <if test="age != null">
            age= #{age} or
        </if>
        <if test="sex == '男'.toString() or sex == '女'.toString()">
            sex = #{sex}
        </if>
    </trim>
</select>

set

<set>：去除修改操作时，set 语句后多余的逗号。
使用 trim 标签可以达到同样的效果。

EmpMapper.java

/**
 * 根据id修改Emp
 * @param emp
 * @return Integer
 */
Integer updateEmp(Emp emp);

EmpMapper.xml

 <!--
 	set标签：
        去除修改时，set语句后多余的逗号
    使用 trim标签 可以达到同样的效果
-->
<!--Integer updateEmp(Emp emp);-->
<update id="updateEmp">
    update employee
    <set>
        <if test="eName != null and eName !=''">
            eName=#{eName},
        </if>
        <if test="age != null">
            age=#{age},
        </if>
        <if test="sex == '男'.toString() or sex == '女'.toString()">
            sex=#{sex}
        </if>
    </set>
    where
        id=#{id}
</update>

使用 trim 标签实现 set

<!--Integer updateEmp(Emp emp);-->
<update id="updateEmp111">
    update employee
    set
        <trim suffixOverrides=",">
            <if test="eName != null and eName !=''">
                eName=#{eName},
            </if>
            <if test="age != null">
                age=#{age},
            </if>
            <if test="sex == '男'.toString() or sex == '女'.toString()">
                sex=#{sex}
            </if>
        </trim>
    where
          id=#{id}
</update>

choose when otherwise

<choose>：父标签。
<when>：test 成立时执行语句。
<otherwise>：所有的 when 都不成立时，执行 otherwise 语句。

EmpMapper.java

/**
 * 根据emp对象的id，eName，age，sex中的一个，查询员工信息
 * @param emp
 * @return
 */
List<Emp> getEmpByOneElement(Emp emp);

EmpMapper.xml

<!--
    choose when otherwise标签：
        choose：父标签
        when：test成立时执行语句
        otherwise：所有的when都不成立时，执行otherwise语句
-->
<!--List<Emp> getEmpByOneElement(Emp emp);-->
<select id="getEmpByOneElement" resultType="Emp">
    select id,eName,age,sex from employee
    where
        <choose>
            <when test="id != null">
                id = #{id}
            </when>
            <when test="eName != null and eName !=''">
                eName = #{eName}
            </when>
            <when test="age != null">
                age= #{age}
            </when>
            <otherwise>
                sex = #{sex}
            </otherwise>
        </choose>
</select>

例题：向数据库中插入数据，0,1 分别转换为 男，女

<!--向数据库中插入数据，0,1 分别转换为 男，女-->
<!--Integer insertEmp(Emp emp);-->
<insert id="insertEmp">
    insert into employee (eName, age, sex)
    values (
        #{eName},
        #{age},
        <choose>
            <when test="sex == 0">'男'</when>
            <when test="sex == 1">'女'</when>
            <otherwise>'未知'</otherwise>
        </choose>
    )
</insert>

foreach

<foreach>：遍历集合或数组中的元素
collection：要遍历的集合或数组
item：集合中每个元素的别名
open：整个遍历的开始内容
close：整个遍历的结束内容
separator：每次遍历之间的分隔符
index：若遍历的是 List，则代表元素的下标，若遍历的是 Map，则代表键

注意：若传入的参数是 List 或数组，则 MyBatis 默认将其保存为 Map，键分别为 list 和 array，不能直接根据参数名获取参数，可以使用@Param注解，自定义键

以删除为例：
EmpMapper.java

/**
 * 批量删除IdList中包含的全部员工
 * @param idList
 * @return
 */
Integer deleteByIdList(@Param("list") List<String> idList);

EmpMapper.xml

<!--  Integer deleteByIdList(List<String> idList);  -->
<!--第一种：delete from employee where id in (...)-->
<delete id="deleteByIdList">
    delete from employee
    where id in
        <foreach collection="list" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
</delete>

<!--第二种：delete from employee where id = ? or id = ?-->
<delete id="deleteByIdList222">
    delete from employee
    where
    <foreach collection="list" item="id" separator="or">
       id = #{id}
    </foreach>
</delete>

批量操作

• 批量添加：
  insert into employee (eName,age,sex,departmentId) values (?,?,?,?),(?,?,?,?),...;
• 批量修改：
  修改为相同内容，如修改状态等
  update employee set eName,age,sex,departmentId where id in (?,?,?...);
update employee set eName,age,sex,departmentId where id=? or id=? or id=?;
  修改为不同内容：需要在数据库连接配置文件中url上添加 allowMultiQueries=true
update employee set eName=?, age=?, sex=?, departmentId=? where id=?;
• 批量删除：
  delete from employee where id in (?,?,?...);
delete from employee where id=? or id=? or id=?;
• 批量查询：
  select * from employee where id in (?,?,?...);
select * from employee where id=? or id=? or id=?;

EmpMapper.java

/**
 * 批量添加员工
 * @param emps
 * @return Integer
 */
Integer bathInsert(@Param("empList")List<Emp> emps);

/**
 * 批量修改员工信息
 * @param emps
 * @return Integer
 */
Integer batchUpdate(@Param("empList")List<Emp> emps);

/**
 * 批量查询指定的id的员工
 * @param idList
 * @return List<Emp>
 */
List<Emp> batchSelect(String[] idList);

EmpMapper.xml

<!--批量插入：Integer bathInsert(List<Emp> emps);-->
<insert id="bathInsert">
    insert into employee (eName,age,sex,departmentId)
    values
        <foreach collection="empList" item="emp" separator=",">
            (#{emp.eName}, #{emp.age}, #{emp.sex}, 'JS')
        </foreach>
</insert>

<!--批量修改：Integer batchUpdate(@Param("empList")List<Emp> emps);-->
<update id="batchUpdate">
    <foreach collection="empList" item="emp">
        update employee
        set eName=#{emp.eName}, age=#{emp.age}, sex=#{emp.sex}
        where id=#{emp.id};
    </foreach>
</update>

<!--批量查询：List<Emp> batchSelect(String[] idList);-->
<select id="batchSelect" resultType="Emp">
    select id,eName,age,sex from employee
    where id in
        <foreach collection="array" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
</select>

sql

<sql>：设置一段公共的 sql 语句，标签内的 sql 语句可以被本映射文件内的所有其他 sql 语句使用。
<include>：包含标签。
refid：要引入的 sql 标签的 id。

EmpMapper.xml

<!--
    sql标签：
        设置一段公共的sql语句，标签内的sql语句可以被本映射文件内的所有其他sql语句使用
    include标签：
        refid：要引入的sql标签的id
-->
<sql id="commonSql">
    select id,eName,age,sex from employee
</sql>

<select id="getEmpByElements222" resultType="Emp">
    <include refid="commonSql"/>
        <!--select id,eName,age,sex from employee-->
    <where>
        <if test="id != null">
            and id = #{id}
        </if>
        <if test="eName != null and eName !='' ">
            and eName = #{eName}
        </if>
        <if test="age != null">
            and age= #{age}
        </if>
        <if test="sex == '男'.toString() or sex == '女'.toString()">
            and sex = #{sex}
        </if>
    </where>
</select>