[Java2023] Day9.9-MyBatis通过xml配置SQL-基础完结

• 一般情况下, 简单的SQL用注解, 复杂的SQL用xml映射. 灵活使用切换
• 插件. MyBatisX, 用于跳转等功能,在Mappper接口的方法中Alt+回车可生成xml

1. 注解方式

2. 使用xml的方式

代码

• 1.单元测试类

@Test
public void testCheck()
{
    List<Emp> emp = empMapper.checkXml("张", (short)1, LocalDate.of(2000, 1, 1), LocalDate.of(2030, 1, 1));
    System.out.println(emp);
}

• 2.映射接口

public List<Emp> checkXml(String name, Short gender, LocalDate begin, LocalDate end);

• 3.xml文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.emp01.mapper.EmpMapper">
    <select id="checkXml" resultType="com.emp01.pojo.Emp">
        select * from emp  where name like concat('%', #{name}, '%') and gender = #{gender} and entrydate between #{begin} and #{end} order by update_time desc
    </select>
</mapper>

动态SQL标签:

<if></if>
<where></where>
<foreach></foreach>
<sql></sql>
<include></include>

1.改造查询语句: 当name,gender等搜索条件无值时, 传入 name = null 会导致查询不到信息

• 1.1使用标签前

<select id="checkXml" resultType="com.emp01.pojo.Emp">
      select *
      from emp
      where name like concat('%', #{name}, '%')
        and gender = #{gender}
        and entrydate between #{begin} and #{end}
      order by update_time desc
</select>

• 1.2使用标签后

<mapper namespace="com.emp01.mapper.EmpMapper">
    <select id="checkXml" resultType="com.emp01.pojo.Emp">
        select *
        from emp
        <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>
</mapper>

2.改造更新语句: 当更新传入的image,gender,job,dept_id没传值时,会导致数据被更新为null

• 2.1 改造前

// 更新员工
@Test
public void testUpdate()
{
    // 构造员工对象
    Emp emp = new Emp();
    emp.setId(6);
    emp.setUsername("Tom1");
    emp.setName("Tom1");
    // emp.setImage("1.jpg");
    // emp.setGender((short)1);
    // emp.setJob((short)1);
    // emp.setDeptId(1);
    empMapper.update(emp);
}

// Mapper中
// 更新 (注意大小写. 正确:dept_id= #{deptId}, 错误:dept_id= #{dept_id},)
@Update("UPDATE `emp` SET `username`= #{username}, `password`= #{password}, `name`= #{name}, `gender`= #{gender}, `image`= #{image}, `job`= #{job}, `entrydate`= #{entrydate}, `dept_id`= #{deptId} WHERE `id`= #{id}")
public void update(Emp emp);

• 2.2 改造后 (注意字段名小写,变量名驼峰)

<update id="updateNew" >
    update emp
    <set>
        <if test="username != null"> username = #{username},</if>
        <if test="name != null"> name = #{name},</if>
        <if test="gender != null"> gender = #{gender},</if>
        <if test="image != null"> image = #{image},</if>
        <if test="job != null"> job = #{job},</if>
        <if test="entrydate != null"> entrydate = #{entrydate},</if>
        <if test="deptId != null"> dept_id = #{deptId},</if>
        <if test="updateTime != null"> update_time = #{update_time},</if>
    </set>
    where id = #{id}
</update>

• 3.1 使用foreach标签的删除方法

// Mapper接口中: 批量删除员工
public void deleteByIds(List<Integer> ids);

//单元测试类:
@Test
public void testDel()
{
    List<Integer> ids = Arrays.asList(7,8,9);
    empMapper.deleteByIds(ids);
}

<!--批量删除员工信息-->
<!--
    collection:遍历的集合
    item: 遍历出来的元素
    separator: 分隔符
    open: 遍历开始前拼接的SQL片段
    close: 同上
   -->
<delete id="deleteByIds">
    delete from emp where id in
    <foreach collection="ids" item="id"  separator="," open="(" close=")">
        #{id}
    </foreach>
</delete>

• 4.sql片段的封装和引用