MyBatis的对象关系映射(多表链接查询)

冷不防 2023-03-13 12:26 167阅读 0赞

在实际开发中,一个业务可能涉及到多个数据表的查询,那么多表查询就涉及连接查询(等值连接), 等值连接 表与表之间有一个外键关键

但是程序中最终获取的表封装的对象, 对象与对象之间是没有外键关系的,对象和对象之间只有依赖关系

对象之间关系主要是四种

1.一对一 关系 one2one to->Two->2  
(1)一个人对应身份证id,一个QQ号对应一个QQ空间  
2.一对多 关系 one2many  
(1)一个部门对应多个员工  
3.多对一 关系 many2one  
(1)多个员工对应一个部门  
4.多对多 关系 many2many  
(1)多个学生对应多个老师,多个学生对应多个课程

什么关系应该从哪个对象作为中心点来看  
一对多, 以one方作为中心点  
多对一，以many方作为中心点来看

MyBatis框架支持多表查询封装对象之间关系

**<collection>** 一对多查询  
**<association>** 多对一和一对一查询  
上述两个标签都在手动映射标签 **<ResultMap>** 中

## Mybatis支持的两种查询策略 ##

MyBatis 支持两种策略

1，等值连接策略  
2，N+策略

多对一关系 ：以员工为中心，多个员工对应一个部门（全局角度考虑）

需求：查询指定id某一个员工的信息（包含部门信息）

### 等值连接策略(等值连接查询) ###

直接将多表进行联合查询直接把数据全部查询出来

select e.*,d.d_name from employee e left join department d 
    on e.dept_id = d.d_id where e.e_id = 1

### N+1 策略 ###

1)先发送N（实际业务需求对应的SQL语句）查询出基本信息  
2)再额外发送一条SQL语句查询对应的其他表的信息  
先查询出员工的基本信息： N 业务上N只有一条就是 1

select * from employee where e_id = 3;

再根据员工的部门id查询出部门的信息 : +1

select * from department where d_id = 1;

--------------------

#### 一、多对一查询 ####

以员工为中心查询部门（从全局角度）多个员工对应一个部门：多对一关系  
Pojo映射关系

###### Employee 类 ######

public class Employee {
        
    	private Integer eId;
    	private String eName;
    	//以员工为中心 : 
               多个员工对应一个部门，多对一关系，many2one
    	//员工与部门的对象关系
    	private Department dept;
    	
      	public Integer geteId() {
        
    		return eId;
    	}
    	public void seteId(Integer eId) {
        
    		this.eId = eId;
    	}
    	public String geteName() {
        
    		return eName;
    	}
    	public void seteName(String eName) {
        
    		this.eName = eName;
    	}
    	
    	public Employee(Integer eId, String eName) {
        
    		super();
    		this.eId = eId;
    		this.eName = eName;
    	}
    	public Employee() {
        
    		super();
    	}
    	@Override
    	public String toString() {
        
    		return "Employee [eId=" + eId + ", eName=" + eName + ", dept=" + dept + "]";
    	}
    	
    	
    }

###### Department 类 ######

public class Department {
        
    	private Integer dId;
    	private String dName;
    	public Integer getdId() {
        
    		return dId;
    	}
    	public void setdId(Integer dId) {
        
    		this.dId = dId;
    	}
    	public String getdName() {
        
    		return dName;
    	}
    	public void setdName(String dName) {
        
    		this.dName = dName;
    	}
    	@Override
    	public String toString() {
        
    		return "Department [dId=" + dId + ", dName=" + dName + "]";
    	}
    	public Department(Integer dId, String dName) {
        
    		super();
    		this.dId = dId;
    		this.dName = dName;
    	}
    	public Department() {
        
    		super();
    	}
    	
    	
    }

##### 1.N+1策略 #####

N+1 ： N 就是当前需要查询结果对应发送的SQL语句的条数  
\+1 关联查询数据需要额外多发一条SQL语句才能查询出对应的结果

##### Mapper接口与映射文件 #####

package com.ywq.mybatis.mapper;
    
    import com.ywq.mybatis.pojo.Employee;
    
    public interface Many2OneMapper {
        
    	
    	/**
    	 * 查询出指定员工的所有信息（包括部门信息）
    	 * @param eId 员工id
    	 * @return 员工对象
    	 */
    	Employee selectByPrimaryKey(Integer eId);
    }

<?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.ywq.mybatis.mapper.Many2OneMapper">
      	
     	
     	
    	  <select id="selectByPrimaryKey" resultMap="emp_map">
    	  select * from employee where e_id = #{
        eId}
    	  </select>
    	  
    	  
    	  <resultMap type="com.ywq.mybatis.pojo.Employee" id="emp_map">
    	  		
    	  		<id column="e_id" property="eId"/>
    	  		
    	  		<result column="e_name" property="eName"/>
    	  		
    	  		
    	  		<association property="dept" column="dept_id" select="selectDetpByPrarimKey"/>
    	  </resultMap>
    	  
    	  
    	   <select id="selectDetpByPrarimKey" parameterType="Integer" resultType="com.ywq.mybatis.pojo.Department">
    	   select * from department where d_id = #{
        dId}
    	   </select>
    	  
    </mapper>

##### 2.等值连接策略 #####

##### 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.ywq.mybatis.mapper.Many2OneMapper">
      	
     	
     		
     
    	  <select id="selectByPrimaryKey" resultMap="emp_map">
    	  	select * from employee e
    	  	left join department d
    	  	on e.dept_id = d.d_id where e.e_id = #{
        eId};
    	  </select>
    	  
    	  
    	  <resultMap type="com.ywq.mybatis.pojo.Employee" id="emp_map">
    	  	
    	  	<id column="e_id" property="eId"/>
    	  	<result column="e_name" property="eName"/>
    	  	
    	  	
    	  	<association property="dept" javaType="com.ywq.mybatis.pojo.Department">
    	  	<id column="d_id" property="dId"/>
    	  	<result column="d_name" property="dName"/>
    	  	</association>
    	  </resultMap> 
    	  
    	
    	  
    </mapper>

#### 二、一对多查询 ####

以部门为中心查询部门的所有信息（包括员工），一个部门对应多个员工  
Pojo映射关系

###### Employee 类 ######

package com.ywq.mybatis.pojo;
    
    public class Employee {
        
    	private Integer eId;
    	private String eName;
    	private Department dept;
    	public Integer geteId() {
        
    		return eId;
    	}
    	public void seteId(Integer eId) {
        
    		this.eId = eId;
    	}
    	public String geteName() {
        
    		return eName;
    	}
    	public void seteName(String eName) {
        
    		this.eName = eName;
    	}
    	
    	public Employee(Integer eId, String eName) {
        
    		super();
    		this.eId = eId;
    		this.eName = eName;
    	}
    	public Employee() {
        
    		super();
    	}
    	@Override
    	public String toString() {
        
    		return "Employee [eId=" + eId + ", eName=" + eName + "]";
    	}
    	
    	
    	
    }

###### Department 类 ######

package com.ywq.mybatis.pojo;
    
    import java.util.List;
    
    public class Department {
        
    	private Integer dId;
    	private String dName;
    	
    	/*
    	 * 以部门为中心，部门维护关系，一个部门对应多个员工
    	 * 
    	 * 此处应该使用一个集合维护部门员工管理
    	 * 
    	 * */
    	
    	private List<Employee> employees;
    	
    	public Integer getdId() {
        
    		return dId;
    	}
    	public void setdId(Integer dId) {
        
    		this.dId = dId;
    	}
    	public String getdName() {
        
    		return dName;
    	}
    	public void setdName(String dName) {
        
    		this.dName = dName;
    	}
    	
    	public Department(Integer dId, String dName) {
        
    		super();
    		this.dId = dId;
    		this.dName = dName;
    	}
    	public Department() {
        
    		super();
    	}
    	@Override
    	public String toString() {
        
    		return "Department [dId=" + dId + ", dName=" + dName + ", employees=" + employees + "]";
    	}
    	
    	
    	
    }

##### 1.N+1策略 #####

N+1 ： N 就是当前需要查询结果对应发送的SQL语句的条数  
\+1 关联查询数据需要额外多发一条SQL语句才能查询出对应的结果

##### Mapper接口与映射文件 #####

package com.ywq.mybatis.mapper;
    
    import com.ywq.mybatis.pojo.Department;
    
    public interface One2ManyMapper {
        
    	
    	/**
    	 * 查询出指定部门的所有信息（包括此部门对应的所有员工信息）
    	 * @param dId 部门id
    	 * @return 部门对象
    	 */
    	Department selectByPrimaryKey(Integer dId);
    }

<?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.ywq.mybatis.mapper.One2ManyMapper">
      	
     	
     	
     	
    	
    	<select id="selectByPrimaryKey" resultMap="dept_map">
    		select * from department where d_id = #{
        dId}
    	</select>
    	  
    	  
    	  <resultMap type="com.ywq.mybatis.pojo.Department" id="dept_map">
    	  	<id column="d_id" property="dId"/>
    	  	<result column="d_name" property="dName"/>
    	  	
    	  	 <collection property="employees" column="d_id" select="selectEmployeesByDeptId"/>
    	  	  
    	  </resultMap>
    	  
    	  
    	    <select id="selectEmployeesByDeptId" resultType="com.ywq.mybatis.pojo.Employee">
    	   	select * from employee where dept_id = #{
        d_id}
    	   </select> 
    </mapper>

**运行结果**  
![在这里插入图片描述][20200513184352340.png]

##### 2.等值连接策略 #####

##### 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.ywq.mybatis.mapper.One2ManyMapper">
      	
     	
    	
    	<select id="selectByPrimaryKey" resultMap="dept_map">
    		select * from employee e left join department d
    		ON e.dept_id = d.d_id where d.d_id = #{
        dId}
    	</select>
    	  
    	  
    	  <resultMap type="com.ywq.mybatis.pojo.Department" id="dept_map">
    	  	<id column="d_id" property="dId"/>
    	  	<result column="d_name" property="dName"/>
    	  	
    	  	  
    	  	   <collection property="employees" ofType="com.ywq.mybatis.pojo.Employee">
    	  	   	
    	  	   	<id column="e_id" property="eId"/>
    	  	   	
    	  	   	<result column="e_name" property="eName"/>
    	  	   </collection>
    	  	   
    	  </resultMap>
    	  
    
    </mapper>

**运行结果**  
只会发送一条SQL语句  
![在这里插入图片描述][20200513184323378.png]

[20200513184352340.png]: /images/20230312/0cf7738e783c4f54b7aaeea056af0ef3.png
[20200513184323378.png]: /images/20230312/43a2286e00534835b57d90948cf9d4b9.png