mybatis加强（映射器、动态sql操作、级联得配置、缓存）

最新推荐文章于 2023-03-13 12:27:17 发布

原创最新推荐文章于 2023-03-13 12:27:17 发布 · 407 阅读

0 ·

CC 4.0 BY-SA版权

mybatis 专栏收录该内容

4 篇文章

订阅专栏

本文详细介绍了MyBatis的高级使用技巧，包括映射器接口的配置与调用、动态SQL操作实现批量删除和修改、多对一和一对多的级联配置，以及如何利用缓存提升性能。通过示例代码展示了EmployeeMapper的实现，包括查询、删除、添加等操作，同时探讨了多对一和一对多关系的处理策略。

一.映射Mapper

接口方法映射到对应的SQL
Mapper.xml的命名空间名称就是Maper接口的全限定名
Mapper接口上也可以写SQL(不建议这么做)

1.employeeMapper.xml

...

<select id="findAll" resultType="employee">
  ...
</select>

2.EmployeeMapper

package cn.itsource._02_mapper.mapper;
public interface EmployeeMapper {

void save(Employee employee);

// @Select(“select * from employee”)
List findAll();
}

3.调用 Mapper的方法

SqlSession session = MyBatisUtil.openSession();
EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);

mapper.findAll().forEach(e -> System.out.println(e));

二.高级查询

准备一个Query对象(封装所有条件)
模糊查询 concat("%",#{name},"%")
遇到特殊符号 1.转义 &lt; 2.CDATA段 <![CDATA[...]]>
使用where标签(第一个and变成where)
if中有多个条件使用 and/or 进行关联
如果出现相就的代码,可以单独抽取sql标签,引用include即可

select * from employee and name like concat("%",#{name},"%") and age >= #{minAge} <![CDATA[ and age<=#{maxAge} ]]>

三批量删除,添加,动态修改

3.1 批量删除

  首先知道sql delete from 表名 where id in (?,?,..)

    collection="":代表你要循环的是什么? array/list
   如果传过来的是数据,写array(集合就写list)
   item:循环的每一个数据
  open:拼接字符串以什么开始
  close:拼接字符串以什么结尾
  separator:拼接的时候每个值使用,隔开
  index:遍历的下标

3.1.1 传数组的方法

delete from employee where id in #{v}

3.1.2 传集合的方式

delete from employee where id in #{v}

3.1 批量添加

 <insert id="batchSave" parameterType="list">
        insert into employee (name,age,sex) values
        <foreach collection="list" item="emp" separator=",">
            (#{emp.name},#{emp.age},#{emp.sex})
        </foreach>
    </insert>

3.2 动态修改

<update id="update" parameterType="cn.itsource._02_mapper.domain.Employee">
    UPDATE employee
    <set>
        <if test="name!=null and name!=''">
            name = #{name},
        </if>
        <if test="age!=null">
            age=#{age},
        </if>
    </set>
    WHERE id=#{id}
</update>

四多对一

准备(两张表employee,department,两个domain,两个mapper.xml)
查询(嵌套结果[一条sql],嵌套查询[n+1条sql])

4.1 准备domain

Employee

public class Employee {

    private Long id;
    private String name;
    private Integer age;
    private Boolean sex;

    private Department dept;
    //... getter,setter与toString
}



  Department

public class Department {
    private Long id;
    private String name;
    //... getter,setter与toString
}

4.2 嵌套结果

查询的sql要关连多张表(一定要取别名,不然有些名称会产生冲突)
当我们使用了association 后默认的映射失败,需要自己手动完成映射

<select id="findAll" resultMap="employeeMap">
    select e.id eid,e.name ename,e.age,e.sex,d.id did,d.name dname
      from employee e join department d on d.id = e.dept_id
</select>

<resultMap id="employeeMap" type="employee">
    <!-- 手动完成基本映射 -->
    <id property="id" column="eid" />
    <result property="name" column="ename" />
    <result property="sex" column="sex" />
    <result property="age" column="age" />
    <!-- property:属性名,javaType:属性类型 -->
    <association property="dept" javaType="department">
        <id property="id" column="did" />
        <result property="name" column="dname" />
    </association>
</resultMap>

4.2 嵌套查询

    会产生n+1条sql
    需要去找到对应的那条sql并且执行
    保证MyBatis能找到这两个xml

    employeeMapper.xml

<select id="findAll" resultMap="employeeMap">
    SELECT * FROM employee
</select>
<!--嵌套查询方案-->
<resultMap id="employeeMap" type="employee">
    <id property="id" column="id" />
    <result property="name" column="name" />
    <result property="sex" column="sex" />
    <result property="age" column="age" />
<!--
    查询相应的部门
        column:数据库对应的列 select:查询对应的sql的路径
 -->
    <association property="dept" javaType="department"
             column="dept_id" select="cn.itsource._04_many2one.DepartmentMapper.findById" />
</resultMap>

    departmentMapper.xml

<mapper namespace="cn.itsource._04_many2one.DepartmentMapper">
    <select id="findById" parameterType="long" resultType="department">
        select * from department where id = #{id}
    </select>
</mapper>

五一对多

5.1 准备domain

    Employee

public class Employee {

    private Long id;
    private String name;
    private Integer age;
    private Boolean sex;
     //... getter,setter与toString
}

    Department

public class Department {
    private Long id;
    private String name;

    private List<Employee> employees = new ArrayList<>();

5.2 级连保存

准备两个Mapper
保存部门后需要马上拿到它的id
保存员工传的是Map{List<Employee>,deptId}

departmentMapper.xml

insert into department (name) values (#{name})

employeeMapper.xml

insert into employee (name,age,sex,dept_id) values (#{emp.name},#{emp.age},#{emp.sex},#{deptId})

5.3 嵌套结果

查询的sql要关连多张表(一定要取别名,不然有些名称会产生冲突)
当我们使用了collection 后默认的映射失败,需要自己手动完成映射

select d.id did,d.name dname,e.id eid,e.name ename,e.age,e.sex from department d left join employee e on e.dept_id = d.id

5.4 嵌套查询

依赖需要找到对应的SQL

departmentMapper.xml

select * from department

employeeMapper.xml

//...

<select id="findByDeptId" resultType="employee" parameterType="long">
    select * from employee where dept_id = #{deptId}
</select>

六.缓存

自带一级级联
二级缓存需要加上标签 <cache />
二级缓存的对象必需是序列化对象 .. implements Serializable

public class Employee implements Serializable { }

xml里面得配置
<mapper namespace="cn.itsource._05_cache.EmployeeMapper">
<!--开启二级缓存(二级缓存的对象一定要序列化)-->
<cache />

<select id="findOne" parameterType="long" resultType="cn.itsource._05_cache.Employee">
    SELECT * FROM employee WHERE  id= #{id}
</select>