MyBatis 多对一关联和一对多关联

用户9184480

发布于 2024-12-17 12:59:56

11700

代码可运行

运行总次数：0

代码可运行

关联.多对一关联查询

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">  <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></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());
    }
  }

常见错误：java.lang.IllegalArgumentException: argument type mismatch,在Emp类中设置Dept 类型的dept属性，可能设置成了int类型，导致引用的时候，无法和Dept类关联起来!!!

无效的列类型: 1111 错误,可能是传递参数的时候，出现了问题，比如说嵌套查询empno=#{id},但是在接口中，却没有定义该查询语句所对应的参数，则必然会出问题哦！！！对于使用映射文件来操作数据库，操作系统的架构方式来说，参数条件等强关联，大小写，格式等要严格遵守规范！！！！

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2022-03-16，如有侵权请联系 cloudcommunity@tencent.com 删除

测试