Mybatis多表查询

川长思鸟来 2022-12-31 05:21 135阅读 0赞

### Mybatis多表查询 ###

*  实体类
 *   *  员工表
     *  部门表
 *  一、Xml方式
 *   *  1、多表连接查询，只需要查一次
     *   *  一对一关系
         *  一对多关系
     *  2、多表子查询，需要查询两次
     *   *  一对一关系
         *  一对多关系
 *  二、注解方式
 *   *  一对一关系
     *  一对多关系

# 实体类 #

## 员工表 ##

package com.Company.domain;
    
    import java.util.Date;
    
    public class Emp { 
    	private int empno;
    	private String ename;
    	private String job;
    	private double mgr;
    	private Date hiredate;
    	private double sal;
    	private double comm;
    	private Dept dp;
    	
    	public int getEmpno() { 
    		return empno;
    	}
    	public void setEmpno(int empno) { 
    		this.empno = empno;
    	}
    
    	public String getEname() { 
    		return ename;
    	}
    	public void setEname(String ename) { 
    		this.ename = ename;
    	}
    	public String getJob() { 
    		return job;
    	}
    	public void setJob(String job) { 
    		this.job = job;
    	}
    	public double getMgr() { 
    		return mgr;
    	}
    	public void setMgr(double mgr) { 
    		this.mgr = mgr;
    	}
    	public Date getHiredate() { 
    		return hiredate;
    	}
    	public void setHiredate(Date hiredate) { 
    		this.hiredate = hiredate;
    	}
    	public double getSal() { 
    		return sal;
    	}
    	public void setSal(double sal) { 
    		this.sal = sal;
    	}
    	public double getComm() { 
    		return comm;
    	}
    	public void setComm(double comm) { 
    		this.comm = comm;
    	}
    	public Dept getDp() { 
    		return dp;
    	}
    	public void setDp(Dept dp) { 
    		this.dp = dp;
    	}
    	public Emp(int empno, String ename, String job, double mgr, Date hiredate, double sal, double comm, Dept dp) { 
    		super();
    		this.empno = empno;
    		this.ename = ename;
    		this.job = job;
    		this.mgr = mgr;
    		this.hiredate = hiredate;
    		this.sal = sal;
    		this.comm = comm;
    		this.dp = dp;
    	}
    	public Emp() { 
    		super();
    	}
    	@Override
    	public String toString() { 
    		return "Emp [empno=" + empno + ", ename=" + ename + ", job=" + job + ", mgr=" + mgr + ", hiredate=" + hiredate
    				+ ", sal=" + sal + ", comm=" + comm + ", dp=" + dp + "]";
    	}
    
    }

## 部门表 ##

package com.Company.domain;
    
    import java.util.List;
    
    public class Dept { 
    	private int deptno;
    	private String dname;
    	private String loc;
    	private List<Emp> empList;
    	
    	public List<Emp> getEmpList() { 
    		return empList;
    	}
    	public void setEmpList(List<Emp> empList) { 
    		this.empList = empList;
    	}
    
    	public int getDeptno() { 
    		return deptno;
    	}
    
    	public void setDeptno(int deptno) { 
    		this.deptno = deptno;
    	}
    
    	public String getDname() { 
    		return dname;
    	}
    
    	public void setDname(String dname) { 
    		this.dname = dname;
    	}
    
    	public String getLoc() { 
    		return loc;
    	}
    
    	public void setLoc(String loc) { 
    		this.loc = loc;
    	}
    
    	@Override
    	public String toString() { 
    		return "Dept [deptno=" + deptno + ", dname=" + dname + ", loc=" + loc + ", empList=" + empList + "]";
    	}
    
    	public Dept() { 
    		super();
    	}
    
    	public Dept(int deptno, String dname, String loc) { 
    		super();
    		this.deptno = deptno;
    		this.dname = dname;
    		this.loc = loc;
    	}
    }

# 一、Xml方式 #

## 1、多表连接查询，只需要查一次 ##

### 一对一关系 ###

EmpMapper.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.Company.mapper.EmpMapper">
    	<resultMap type="com.Company.domain.Emp" id="empsMap">
    		<id column="empno" property="empno" />
    		<result column="ename" property="ename" />
    		<result column="job" property="job" />
    		<result column="mgr" property="mgr" />
    		<result column="hiredate" property="hiredate" />
    		<result column="sal" property="sal" />
    		<result column="comm" property="comm" />
         
    	 <association property="dp" javaType="com.Company.domain.Dept" column="deptno"> 
    			<id column="deptno" property="deptno" /> 
    			<result column="dname" property="dname" /> 
    			<result column="loc" property="loc" /> 
    	</association>
    	</resultMap>
    	
    	<select id="findAllEmpsAndDept" resultMap="empsMap"> 
    	     select * from emp,dept where emp.deptno = dept.deptno 
    	</select> 
    </mapper>

### 一对多关系 ###

DeptMapper.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.Company.mapper.DeptMapper">
    	<resultMap type="com.Company.domain.Dept" id="deptsMap">
    		<id column="deptno" property="deptno" />
    		<result column="dname" property="dname" />
    		<result column="loc" property="loc" />
    		
    		
    		 <collection property="empList" ofType="com.Company.domain.Emp"> 
    		 <id column="empno" property="empno" /> 
    			<result column="ename" property="ename" /> 
    			<result column="job" property="job" />
    			<result column="mgr" property="mgr" />
    			<result column="hiredate" property="hiredate" /> 
    			<result column="sal" property="sal" /> 
    			<result column="comm" property="comm" /> 
    		</collection>
    	</resultMap>
    	
    	<select id="findAllEmpsInDept" resultMap="deptsMap">
    	 select * from dept,emp where dept.deptno =emp.deptno </select> 
    	
    	
    </mapper>

## 2、多表子查询，需要查询两次 ##

### 一对一关系 ###

EmpMapper.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.Company.mapper.EmpMapper">
    	<resultMap type="com.Company.domain.Emp" id="empsMap">
    		<id column="empno" property="empno" />
    		<result column="ename" property="ename" />
    		<result column="job" property="job" />
    		<result column="mgr" property="mgr" />
    		<result column="hiredate" property="hiredate" />
    		<result column="sal" property="sal" />
    		<result column="comm" property="comm" />
    
    		
    		<association property="dp" column="deptno"
    			javaType="com.Company.domain.Dept"
    			select="com.Company.mapper.DeptMapper.findDeptByDeptno">
    		</association>
    	</resultMap>
    	
    	<select id="findAllEmpsAndDept" resultMap="empsMap">
    		select * from emp
    	</select>
    
    	
    	<select id="findAllEmpsInDeptByDeptno" parameterType="Integer"
    		resultType="com.Company.domain.Emp">
    		select * from emp where deptno = #{ deptno}
    	</select>
    </mapper>

### 一对多关系 ###

DeptMapper.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.Company.mapper.DeptMapper">
    	<resultMap type="com.Company.domain.Dept" id="deptsMap">
    		<id column="deptno" property="deptno" />
    		<result column="dname" property="dname" />
    		<result column="loc" property="loc" />
    		
    		<collection property="empList" column="deptno"
    			ofType="com.Company.domain.Emp"
    			select="com.Company.mapper.EmpMapper.findAllEmpsInDeptByDeptno">
    		</collection>
    	</resultMap>
    	
    	
    	<select id="findAllEmpsInDept" resultMap="deptsMap">
    		select * from dept
    	</select>
    	
    	<select id="findDeptByDeptno" parameterType="Integer"
    		resultType="com.Company.domain.Dept">
    		select * from dept where deptno = #{ deptno}
    	</select>
    </mapper>

# 二、注解方式 #

只能使用多表子查询，需要查询两次

## 一对一关系 ##

**EmpMapper.java文件**

package com.Company.mapper;
    
    import java.util.List;
    import org.apache.ibatis.annotations.One;
    import org.apache.ibatis.annotations.Result;
    import org.apache.ibatis.annotations.Results;
    import org.apache.ibatis.annotations.Select;
    import com.Company.domain.Emp;
    
    public interface EmpMapper { 
    	@Select("select * from emp")
    	@Results(value = {  @Result(column = "empno", property = "empno"), 
    			@Result(column = "ename", property = "ename"),
    			@Result(column = "job", property = "job"), 
    			@Result(column = "mgr", property = "mgr"),
    			@Result(column = "sql", property = "sql"), 
    			@Result(column = "comm", property = "comm"),
    			@Result(column = "deptno", property = "dp", 
    				//使用的是DeptMapper.java文件中的findDeptByNo方法
    			one = @One(select = "com.Company.mapper.DeptMapper.findDeptByNo")) })
    	public List<Emp> findAllEmpsAndDept();
    
    // 通过部门编号查找员工
    	@Select("select * from emp where deptno=#{deptno}")
    	public List<Emp> findAllEmpsInDeptByNo(int deptno);
    }

## 一对多关系 ##

**DeptMapper.java文件**

package com.Company.mapper;
    
    import java.util.List;
    import org.apache.ibatis.annotations.Many;
    import org.apache.ibatis.annotations.Result;
    import org.apache.ibatis.annotations.Results;
    import org.apache.ibatis.annotations.Select;
    import com.Company.domain.Dept;
    
    public interface DeptMapper { 
    	@Select("select * from dept")
    	@Results(value= { 
    			@Result(column="deptno",property="deptno"),
    			@Result(column="dname",property="dname"),
    			@Result(column="loc",property="loc"),
    			@Result(column="deptno",property="empList",
    			//使用的是EmpMapper.java文件中的findAllEmpsInDeptByNo方法
    			many=@Many(select="com.Company.mapper.EmpMapper.findAllEmpsInDeptByNo"))
    	})
    	public List<Dept> findAllEmpsInDept();
    	@Select("select * from dept where deptno=#{deptno}")
    	public Dept findDeptByNo(int deptno);
    }