Mybatis多表查询(一对一、一对多、多对多)_mybatis 1对1连表查询-CSDN博客

本文链接：https://blog.csdn.net/qq_43753862/article/details/109855136

一、一对一表关系查询

表person

表cardid 表idcard

根据表构建对应的domain实体对象

Person.class

package domain;

public class Person {
    private Integer pid;
    private String pname;
    private Idcard idcard;

    public Person() {
    }

    public Person(Integer pid, String pname, Idcard idcard) {
        this.pid = pid;
        this.pname = pname;
        this.idcard = idcard;
    }

    public Integer getPid() {
        return pid;
    }

    public void setPid(Integer pid) {
        this.pid = pid;
    }

    public String getPname() {
        return pname;
    }

    public void setPname(String pname) {
        this.pname = pname;
    }

    public Idcard getIdcard() {
        return idcard;
    }

    public void setIdcard(Idcard idcard) {
        this.idcard = idcard;
    }

    @Override
    public String toString() {
        return "Person{" +
                "pid=" + pid +
                ", pname='" + pname + '\'' +
                ", idcard=" + idcard +
                '}';
    }
}

Idcard.class

package domain;

public class Idcard {
    private String cardid;
    private String address;

    public Idcard() {
    }

    public Idcard(String cardid, String address) {
        this.cardid = cardid;
        this.address = address;
    }

    public String getCardid() {
        return cardid;
    }

    public void setCardid(String cardid) {
        this.cardid = cardid;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "Idcard{" +
                "cardid='" + cardid + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

1.基于Mapper方式

PersonDao接口

package dao;

import domain.Person;

public interface PersonDao {
    public Person selectOne(Integer pid);
}

（1）嵌套查询

<?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="dao.PersonDao">
    <resultMap id="selectIdcard" type="domain.Person">
        <id property="pid" column="pid"></id>
        <result property="pname" column="pname"></result>
        <association property="idcard" column="cardid" select="selectBycardid" javaType="domain.Idcard"></association>
    </resultMap>
    <select id="selectBycardid" resultType="domain.Idcard">
        select * from idcard where cardid=#{cardid};
    </select>
    <select id="selectOne" resultMap="selectIdcard">
        select * from person where pid=#{pid}
    </select>
</mapper>

(2)联合查询

<?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="dao.PersonDao">
    <resultMap id="selectIdcard" type="domain.Person">
        <id property="pid" column="pid"></id>
        <result property="pname" column="pname"></result>
        <association property="idcard" javaType="domain.Idcard">
            <id property="cardid" column="cardid"></id>
            <result property="address" column="address"></result>
        </association>
    </resultMap>
    <select id="selectOne" resultMap="selectIdcard">
        select a.pid,a.pname,a.cardid,b.address from person a inner join idcard b on a.cardid=b.cardid where pid=#{pid};
    </select>
</mapper>

2.基于Annotation方式

PersonDao接口

package dao;

import domain.Idcard;
import domain.Person;
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 org.apache.ibatis.mapping.FetchType;

public interface PersonDao {
    @Results(
            id = "selectIdcard",
            value = {
                    @Result(property = "pid",column = "pid",id=true),
                    @Result(property = "pname",column = "pname"),
                    @Result(property = "idcard",javaType = Idcard.class,column = "cardid",one=@One(select = "selectcardid",fetchType = FetchType.LAZY))
            }
    )
    @Select("select * from person where pid=#{pid}")
    public Person selectOne(Integer pid);
    @Select("select * from idcard where cardid=#{cardid}")
    public Idcard selectcardid(String cardid);
}

注意：基于注解方式查询，只有嵌套查询方式。

二、一对多表关系查询

表dept 表dept
表emp 表emp
根据表构建对应的domain实体对象

Dept.class

package domain;

import java.util.List;

public class Dept {
    private Integer deptno;
    private String dname;
    private String loc;
    private List<Emp> empList;

    public Dept() {
    }

    public Dept(Integer deptno, String dname, String loc, List<Emp> empList) {
        this.deptno = deptno;
        this.dname = dname;
        this.loc = loc;
        this.empList = empList;
    }

    public Integer getDeptno() {
        return deptno;
    }

    public void setDeptno(Integer 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;
    }

    public List<Emp> getEmpList() {
        return empList;
    }

    public void setEmpList(List<Emp> empList) {
        this.empList = empList;
    }

    @Override
    public String toString() {
        return "Dept{" +
                "deptno=" + deptno +
                ", dname='" + dname + '\'' +
                ", loc='" + loc + '\'' +
                ", empList=" + empList +
                '}';
    }
}

Emp.class

package domain;

import java.sql.Date;

public class Emp {
    private Integer empno;
    private String ename;
    private String job;
    private Integer mgr;
    private Date hiredate;
    private Float sal;
    private Float comm;
    private Dept dept;

    public Emp() {
    }

    public Emp(Integer empno, String ename, String job, Integer mgr, Date hiredate, Float sal, Float comm, Dept dept) {
        this.empno = empno;
        this.ename = ename;
        this.job = job;
        this.mgr = mgr;
        this.hiredate = hiredate;
        this.sal = sal;
        this.comm = comm;
        this.dept = dept;
    }

    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 Float getSal() {
        return sal;
    }

    public void setSal(Float sal) {
        this.sal = sal;
    }

    public Float getComm() {
        return comm;
    }

    public void setComm(Float comm) {
        this.comm = comm;
    }

    public Dept getDept() {
        return dept;
    }

    public void setDept(Dept dept) {
        this.dept = dept;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "empno=" + empno +
                ", ename='" + ename + '\'' +
                ", job='" + job + '\'' +
                ", mgr=" + mgr +
                ", hiredate=" + hiredate +
                ", sal=" + sal +
                ", comm=" + comm +
                ", dept=" + dept +
                '}';
    }
}

1.基于Mapper方式

DeptDao接口

package dao;

import domain.Dept;

public interface DeptDao {
    public Dept selectOne(Integer deptno);
}

(1)嵌套查询

<?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="dao.DeptDao">
    <resultMap id="selectDept" type="domain.Dept">
        <id property="deptno" column="deptno"></id>
        <result property="dname" column="dname"></result>
        <result property="loc" column="loc"></result>
        <collection property="empList" column="deptno" javaType="list" ofType="domain.Emp" select="selectEmp"></collection>
    </resultMap>
    <select id="selectEmp" resultType="domain.Emp">
        select * from emp where deptno=#{deptno}
    </select>
    <select id="selectOne" resultMap="selectDept">
        select * from dept where deptno=#{deptno}
    </select>
</mapper>

(2)联合查询

<?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="dao.DeptDao">
    <resultMap id="selectOne" type="domain.Dept">
        <id property="deptno" column="deptno"></id>
        <result property="dname" column="dname"></result>
        <result property="loc" column="loc"></result>
        <collection property="empList" javaType="list" ofType="domain.Emp">
            <id property="empno" column="empno"></id>
            <result property="ename" column="ename"></result>
            <result property="job" column="job"></result>
            <result property="mgr" column="mgr"></result>
            <result property="hiredate" column="hiredate"></result>
            <result property="sal" column="sal"></result>
            <result property="comm" column="comm"></result>
        </collection>
    </resultMap>
    <select id="selectOne" resultMap="selectOne">
        select a.deptno,a.dname,a.loc,b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.comm from dept a inner join emp b on a.deptno=b.deptno where a.deptno=#{deptno}
    </select>
</mapper>

2.基于Annotation方式

DeptDao接口

package dao;

import domain.Dept;
import domain.Emp;
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 org.apache.ibatis.mapping.FetchType;

import java.util.List;

public interface DeptDao {
    @Results(
            id = "selectDept",
            value = {
                    @Result(property ="deptno",column = "deptno"),
                    @Result(property = "dname",column = "dname"),
                    @Result(property = "loc",column = "loc"),
                    @Result(property = "empList",column = "deptno",javaType = List.class,many = @Many(select = "selectEmp",fetchType = FetchType.LAZY))
            }
    )
    @Select("select * from dept where deptno=#{deptno}")
    public Dept selectOne(Integer deptno);
    @Select("select * from emp where deptno=#{deptno}")
    public Emp selectEmp(Integer deptno);
}

三、多对多表关系查询

多对多表关系查询与一对多查询类似，不过多赘述。