MyBatis.4关联

时间：2020-12-09 14:34:46
关联.多对一关联查询
package org.mybatis.example.dao;

import java.util.Date;
//雇员类
public class Emp {
	private Integer empno;
	private String ename;
	private String job;
	private Integer mgr;
	private Date hiredate;
	private Integer sal;
	private Integer comm;
	private Dept dept;
	public Emp() {
		// TODO Auto-generated constructor stub
	}
	public Integer getEmpno() {
		return empno;
	}
	public void setEmpno(Integer 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 Integer getMgr() {
		return mgr;
	}
	public void setMgr(Integer mgr) {
		this.mgr = mgr;
	}
	public Date getHiredate() {
		return hiredate;
	}
	public void setHiredate(Date hiredate) {
		this.hiredate = hiredate;
	}
	public Integer getSal() {
		return sal;
	}
	public void setSal(Integer sal) {
		this.sal = sal;
	}
	public Integer getComm() {
		return comm;
	}
	public void setComm(Integer comm) {
		this.comm = comm;
	}
	public Dept getDept() {
		return dept;
	}
	public void setDept(Dept dept) {
		this.dept = dept;
	}

}

//EmpMapper.java接口

package org.mybatis.example.dao;
import java.util.List;

public interface EmpMapper {
	public List<Emp>selectManytoOne();
}

关联关系，体现的是两个类之间的一种强依赖关系。比如在员工类中，有一个属性是部门类的对象；先看第一种 嵌套查询：

通过执行另外一个SQL映射语句来返回语气的复杂类型。

//整体mybatis配置文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<properties resource="db.properties"/>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
            <property name="driver" value="${driver}"/>
			<property name="url" value="${url}"/>
			<property name="username" value="${username}"/>
			<property name="password" value="${password}"/>
            </dataSource>
        </environment>
        </environments>
    <mappers>
        <mapper resource="org/mybatis/example/dao/DeptMapper.xml"/>
        <mapper resource="org/mybatis/example/dao/EmpMapper.xml"/>
    </mappers>
</configuration>

//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="org.mybatis.example.dao.EmpMapper">
	<resultMap type="org.mybatis.example.dao.Emp" id="getEmpresultMap">
		<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="dept" column="deptno"
			javaType="org.mybatis.example.dao.Dept">
			<id column="deptno" property="deptno"/>
			<result column="dname" property="dname"/>
			<result column="loc" property="loc"/>
		</association>
	</resultMap>
	<select id="selectManytoOne" resultMap="getEmpresultMap">
		select
		e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,
		e.deptno,d.dname,d.loc
		from emp e left join dept d on e.deptno=d.deptno
	</select>
</mapper>

//测试类 

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.mybatis.example.dao.Emp;
import org.mybatis.example.dao.EmpMapper;
import org.mybatis.example.dao.SqlSessionFactoryUtil;

public class Test21 {
	public static void main(String[] args) {
		SqlSession session=SqlSessionFactoryUtil.getSqlSession();
		EmpMapper empmapper=session.getMapper(EmpMapper.class);
		List<Emp>empList=empmapper.selectManytoOne();
		for(Emp emp:empList){
			System.out.println(emp.getEname()+"的部门是："+emp.getDept().getDname());
		}
	}
}

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="org.mybatis.example.dao.EmpMapper">
	<resultMap id="empResult" type="org.mybatis.example.dao.Emp">
		<association property="dept" column="deptno"
			javaType="org.mybatis.example.dao.Dept" select="selectDept"/>
	</resultMap>
	<select id="selectEmp" parameterType="int" resultMap="empResult">
		select * from emp where empno=#{id}
	</select>
	<select id="selectDept" parameterType="int"
		resultType="org.mybatis.example.dao.Dept">
		select * from dept where deptno=#{id}
	</select>
	<select id="selectManytoOne" resultMap="getEmpresultMap">
        select
        e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,
        e.deptno,d.dname,d.loc
        from emp e left join dept d on e.deptno=d.deptno
    </select>
</mapper>

EmpMapper.java接口

增加方法：public List<Emp>selectEmp(int id);

测试类

public static void main(String[] args) {
		SqlSession session=SqlSessionFactoryUtil.getSqlSession();
		EmpMapper empmapper=session.getMapper(EmpMapper.class);
		List<Emp>empList=empmapper.selectEmp(2);
		System.out.println(empList.size());
		for(Emp emp:empList){
			System.out.println(emp.getEname()+"的部门是："+emp.getDept().getDname());
		}
	}

但是这种方法，在查询的时候只能孤立的查询某个员工id比较复杂，可以使用如下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="org.mybatis.example.dao.EmpMapper">
	<resultMap type="org.mybatis.example.dao.Emp" id="getEmpresultMap">
		<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="dept" column="deptno"
			javaType="org.mybatis.example.dao.Dept" resultMap="deptresultmap">
		</association>
	</resultMap>
	<resultMap type="org.mybatis.example.dao.Dept" id="deptresultmap">	
	<id column="deptno" property="deptno"/>
	<result column="dname" property="dname"/>
	<result column="loc" property="loc"/>


	</result>
	<select id="selectManytoOne" resultMap="getEmpresultMap">
		select
		e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,
		e.deptno,d.dname,d.loc
		from emp e left join dept d on e.deptno=d.deptno
	</select>
</mapper>

//一对多关联集合查询

重写下Dept类

增加多的一方的集合属性，private List<Emp>emps;并且添加相应的getter/setter方法;

重新配置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="org.mybatis.example.dao.DeptMapper">
	<resultMap type="org.mybatis.example.dao.Dept" id="deptresultMap">
		<id column="deptno" property="deptno"/>
		<result column="dname" property="dname"/>
		<result column="loc" property="loc"/>
		<collection property="emps" ofType="org.mybatis.example.dao.Emp"
			resultMap="empresultmap">
		</collection>
	</resultMap>
	<resultMap type="org.mybatis.example.dao.Emp" id="empresultmap">
			<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"/>
	</resultMap>
	<select id="selectOnetoMany" resultMap="deptresultMap">
		select
        e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,
        e.deptno,d.dname,d.loc
        from emp e left join dept d on e.deptno=d.deptno
	</select>
</mapper>

测试类代码

public static void main(String[] args) {
		SqlSession session=SqlSessionFactoryUtil.getSqlSession();
		DeptMapper deptmapper=session.getMapper(DeptMapper.class);
		List<Dept>deptList=deptmapper.selectOnetoMany();
		for(Dept dept:deptList){
			System.out.println("部门名称："+dept.getDname());
			System.out.println("所属员工的个数："+dept.getEmps().size());
		}
	}

相关文章