Mybaits（12）MyBatis级联-3
一、另一种级联
MyBatis还提供了另一种级联方式，它是基于sql表连接的基础上，进行再设计的，之前我们在一对一级联时简单介绍过。现在我们看如何使用。
1.定义sql，修改IEmployeeDao.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.xhbjava.dao.IEmployeeDao"> <resultMap id="employee" type="com.xhbjava.domain.Employee"> <id column="id" property="id" /> <result column="real_name" property="realName" /> <result column="sex" property="sex" typeHandler="com.xhbjava.typeHandler.SexTypeHandler" /> <result column="birthday" property="birthday" /> <result column="mobile" property="mobile" /> <result column="email" property="email" /> <result column="position" property="position" /> <association property="workcard" javaType="com.xhbjava.domain.WorkCard" column="id"> <id column="wc_id" property="id" /> <result column="id" property="empId" /> <result column="wc_real_name" property="realName" /> <result column="wc_department" property="department" /> <result column="wc_mobile" property="mobile" /> <result column="wc_position" property="position" /> <result column="wc_note" property="note" /> </association> <collection property="employeeTaskList" ofType="com.xhbjava.domain.EmployeeTask" column="id"> <id column="et_id" property="id" /> <result column="id" property="empId" /> <result column="task_name" property="taskName" /> <result column="note" property="note" /> <association property="task" javaType="com.xhbjava.domain.Task" column="et_task_id"> <id column="t_id" property="id" /> <result column="t_title" property="title" /> <result column="t_context" property="context" /> <result column="t_note" property="note" /> </association> </collection> <discriminator javaType="int" column="sex"> <case value="1" resultMap="maleHealthFormMapper" /> <case value="2" resultMap="femaleHealthFormMapper" /> </discriminator> </resultMap> <resultMap type="com.xhbjava.domain.MaleEmployee" id="maleHealthFormMapper" extends="employee"> <association property="maleHealthForm" column="id" javaType="com.xhbjava.domain.MaleHealthForm"> <id column="h_id" property="id" /> <result column="h_heart" property="heart" /> <result column="h_liver" property="liver" /> <result column="h_spleen" property="spleen" /> <result column="h_lung" property="lung" /> <result column="h_kidney" property="kidney" /> <result column="h_prostate" property="prostate" /> <result column="h_note" property="note" /> </association> </resultMap> <resultMap type="com.xhbjava.domain.FemaleEmployee" id="femaleHealthFormMapper2" extends="employee"> <association property="femaleHealthForm" column="id" javaType="com.xhbjava.domain.FemaleHealthForm"> <id column="h_id" property="id" /> <result column="h_heart" property="heart" /> <result column="h_liver" property="liver" /> <result column="h_spleen" property="spleen" /> <result column="h_lung" property="lung" /> <result column="h_kidney" property="kidney" /> <result column="h_uterus" property="uterus" /> <result column="h_note" property="note" /> </association> </resultMap> <select id="getEmployee" parameterType="long" resultMap="employee"> select emp.id, emp.real_name, emp.sex, emp.birthday, emp.mobile, emp.email, emp.position, emp.note, et.id as et_id, et.task_id as et_task_id, et.task_name as et_task_name, et.note as et_note, if (emp.sex = 1, mhf.id, fhf.id) as h_id, if (emp.sex = 1, mhf.heart, fhf.heart) as h_heart, if (emp.sex = 1, mhf.liver, fhf.liver) as h_liver, if (emp.sex = 1, mhf.spleen, fhf.spleen) as h_spleen, if (emp.sex = 1, mhf.lung, fhf.lung) as h_lung, if (emp.sex = 1, mhf.kidney, fhf.kidney) as h_kidney, if (emp.sex = 1, mhf.note, fhf.note) as h_note, mhf.prostate as h_prostate, fhf.uterus as h_uterus, wc.id wc_id, wc.real_name wc_real_name, wc.department wc_department, wc.mobile wc_mobile, wc.position wc_position, wc.note as wc_note, t.id as t_id, t.title as t_title, t.context as t_context, t.note as t_note from t_employee emp left join t_employee_task et on emp.id = et.emp_id left join t_female_health_form fhf on emp.id = fhf.emp_id left join t_male_health_form mhf on emp.id = mhf.emp_id left join t_work_card wc on emp.id = wc.emp_id left join t_task t on et.task_id = t.id where emp.id = #{id} </select> </mapper>
2.执行测试类，日志输出
3.总结
通过日志我们可以看出完全消除N+1问题，但是这样做也有弊端。首先，SQL会比较复杂，不好维护。其次，需要配置的比之复杂。所以对于这种方式我们可以针对业务逻辑比较简单的sql进行使用。
二、多对多级联
1.概述
在实际生活工作中有多对多级联，我们在程序处理中一般会将多对多级联进行拆分，我们拆分成两个一对多级联就可以。
2.业务场景分析
我们在使用一个系统时涉及到权限管理，我们一个用户可以拥有多个角色，而一个角色又可以对应多个用户，对于这种多对多的情况我们拆分成 两个一对多的关系，一个角色对应多个用户和一个用户对应多个角色。
3.数据库表设计
4.测试
（1）编写pojo实体类
package com.xhbjava.domain; import java.util.List; import com.xhbjava.enumeration.SexEnum; /** * 用户类 * @author Mr.wang *@date 2020年2月28日 */ public class User { private Long id; private String userName; private String realName; private SexEnum sex; private String moble; private String email; private String note; //对角色进行一对多关联 private List<Role> roleList; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getUserName() { return userName; } public void setUserName(String userName) { this.userName = userName; } public String getRealName() { return realName; } public void setRealName(String realName) { this.realName = realName; } public SexEnum getSex() { return sex; } public void setSex(SexEnum sex) { this.sex = sex; } public String getMoble() { return moble; } public void setMoble(String moble) { this.moble = moble; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getNote() { return note; } public void setNote(String note) { this.note = note; } public List<Role> getRoleList() { return roleList; } public void setRoleList(List<Role> roleList) { this.roleList = roleList; } }
package com.xhbjava.domain; import java.util.List; /** * 角色类 * @author Mr.wang *@date 2020年2月28日 */ public class Role { private Long id; private String roleName; private String note; //关联用户信息，一对多 private List<User> userList; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getRoleName() { return roleName; } public void setRoleName(String roleName) { this.roleName = roleName; } public String getNote() { return note; } public void setNote(String note) { this.note = note; } public List<User> getUserList() { return userList; } public void setUserList(List<User> userList) { this.userList = userList; } }
（2）持久层dao接口编写
package com.xhbjava.dao; import java.util.List; import com.xhbjava.domain.Role; /** * 角色接口 * * @author Mr.wang * @date 2020年2月28日 */ public interface IRoleDao { /** * 根据id查询角色 * * @param id * @return */ public Role getRole(Long id); /** * 根据用户id查询角色 * * @param userId * @return */ public List<Role> findRoleByUserId(Long userId); }
package com.xhbjava.dao; import java.util.List; import com.xhbjava.domain.User; /** * 用户接口 * * @author Mr.wang * @date 2020年2月28日 */ public interface IUserDao { /** * 根据id获取用户 * * @param id * @return */ public User getUser(Long id); /** * 根据角色id获取用户 * * @param roleId * @return */ public List<User> findUserByRoleId(Long roleId); }
（3）持久层映射文件编写
<?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.xhbjava.dao.IUserDao"> <resultMap type="com.xhbjava.domain.User" id="userMapper"> <id column="id" property="id" /> <result column="user_name" property="userName" /> <result column="real_name" property="realName" /> <result column="sex" property="sex" typeHandler="com.xhbjava.typeHandler.SexTypeHandler" /> <result column="mobile" property="moble" /> <result column="email" property="email" /> <result column="position" property="position" /> <result column="note" property="note" /> <collection property="roleList" column="id" fetchType="lazy" select="com.xhbjava.dao.IRoleDao.findRoleByUserId" /> </resultMap> <select id="getUser" parameterType="long" resultMap="userMapper"> select id, user_name, real_name, sex, moble, email, note from t_user where id =#{id} </select> <select id="findUserByRoleId" parameterType="long" resultMap="userMapper"> select u.id, u.user_name, u.real_name, u.sex, u.moble, u.email, u.note from t_user u , t_user_role ur where u.id = ur.user_id and ur.role_id =#{roleId} </select> </mapper>
<?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.xhbjava.dao.IRoleDao"> <resultMap type="com.xhbjava.domain.Role" id="roleMapper"> <id column="id" property="id" /> <result column="role_name" property="roleName" /> <result column="note" property="note" /> <collection property="userList" column="id" fetchType="lazy" select="com.xhbjava.dao.IUserDao.findUserByRoleId" /> </resultMap> <select id="getRole" parameterType="long" resultMap="roleMapper"> select id, role_name, note from t_role where id = #{id} </select> <select id="findRoleByUserId" parameterType="long" resultMap="roleMapper"> select r.id, r.role_name, r.note from t_role r, t_user_role ur where r.id = ur.role_id and ur.user_id = #{userId} </select> </mapper>
（4）编写测试类进行测试
@Test public void testManyToMany() { // 5.创建Dao的代理对象 roleDao = session.getMapper(IRoleDao.class); Role role = roleDao.getRole(1L); role.getUserList(); userDao = session.getMapper(IUserDao.class); userDao.getUser(1l); }
一共有3条sql被执行，调用getUserList获取用户信息所以有第二条sql执行，我们在映射文件中进行了懒加载设置，这样不需要的数据就不会立即加载出来。
