映射Mapper高级查询批量添加,删除，动态修改多对一(查询) - 难点一对多(级联添加,查询 ) -难点缓存

一.映射Mapper

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

1.employeeMapper.xml

<!--这个命名空间的名称就是咱们Mapper接口的全限定名--> <mapper namespace="wltyx.nyybw._02_mapper.mapper.EmployeeMapper"> <!--这个id的名称必需和映射的EmployeeMapper的方法名一致--> <insert id="save" parameterType="employee"> ... </insert> <select id="findAll" resultType="employee"> ... </select> </mapper>

2.EmployeeMapper

package wltyx.nyybw._02_mapper.mapper; public interface EmployeeMapper { void save(Employee employee); // @Select("select * from employee") List<Employee> 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.转义 < 2.CDATA段 <![CDATA[...]]>使用where标签(第一个and变成where)if中有多个条件使用 and/or 进行关联如果出现相就的代码,可以单独抽取sql标签,引用include即可 <select id="findByQuery" parameterType="employeeQuery" resultType="employee"> select * from employee <include refid="whereSql" /> </select> <!--准备代码片断--> <sql id="whereSql"> <where> <if test="name!=null and name!=''"> and name like concat("%",#{name},"%") </if> <if test="minAge!=null"> and age >= #{minAge} </if> <if test="maxAge!=null"> <![CDATA[ and age<=#{maxAge} ]]> </if> </where> </sql>

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

3.1 批量删除

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

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

3.1.1 传数组的方法

<delete id="batchDelete" parameterType="long[]"> delete from employee where id in <foreach collection="array" item="v" open="(" close=")" separator=","> #{v} </foreach> </delete>

3.1.2 传集合的方式

<delete id="batchDelete" parameterType="list"> delete from employee where id in <foreach collection="list" item="v" open="(" close=")" separator=","> #{v} </foreach> </delete>

3.1 批量添加

首先知道sql `insert into 表名 (p1,p2,…) values (#{p1},#{p2}),(#{p1},#{p2}),…

<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="wltyx.nyybw._02_mapper.domain.Employee"> UPDATE employee <trim prefix="SET" suffixOverrides=","> <if test="name!=null and name!=''"> name = #{name}, </if> <if test="age!=null"> age=#{age}, </if> <if test="sex!=null"> sex = #{sex} </if> </trim> WHERE id = #{id} </update>

更好的方案

<update id="update" parameterType="wltyx.nyybw._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="wltyx.nyybw._04_many2one.DepartmentMapper.findById" /> </resultMap>

departmentMapper.xml

<mapper namespace="wltyx.nyybw._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,deptId}

departmentMapper.xml

<!--保存后需要拿到id--> <insert id="save" parameterType="department" useGeneratedKeys="true" keyProperty="id" keyColumn="id"> insert into department (name) values (#{name}) </insert>

employeeMapper.xml

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

5.3 嵌套结果

查询的sql要关连多张表(一定要取别名,不然有些名称会产生冲突)当我们使用了collection 后默认的映射失败,需要自己手动完成映射 <resultMap id="departmentMap" type="department"> <id property="id" column="did" /> <result property="name" column="dname" /> <collection property="employees" ofType="employee"> <id property="id" column="eid" /> <result property="name" column="ename" /> <result property="age" column="age" /> <result property="sex" column="sex" /> </collection> </resultMap> <select id="findAll" resultMap="departmentMap"> 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 </select>

5.4 嵌套查询

依赖需要找到对应的SQL

departmentMapper.xml <resultMap id="departmentMap" type="department"> <id property="id" column="id" /> <result property="name" column="name" /> <collection property="employees" ofType="employee" column="id" select="wltyx.nyybw._05_one2many.EmployeeMapper.findByDeptId"> </collection> </resultMap> <select id="findAll" resultMap="departmentMap"> select * from department </select> employeeMapper.xml <!--这个命名空间的名称就是咱们Mapper接口的全限定名--> <mapper namespace="wltyx.nyybw._05_one2many.EmployeeMapper"> //... <select id="findByDeptId" resultType="employee" parameterType="long"> select * from employee where dept_id = #{deptId} </select> </mapper>

六.缓存

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