八.MyBatis关联查询

1.一对多单条SQL

例如部门和员工，实体类如下

所属部门

//所属部门类
public class Dept {
    public Integer deptNo;
    public String deptName;
    public List<Emp> emps=new ArrayList<Emp>();

    public Integer getDeptNo() {
        return deptNo;
    }

    public void setDeptNo(Integer deptNo) {
        this.deptNo = deptNo;
    }

    public String getDeptName() {
        return deptName;
    }

    public void setDeptName(String deptName) {
        this.deptName = deptName;
    }

    public List<Emp> getEmps() {
        return emps;
    }

    public void setEmps(List<Emp> emps) {
        this.emps = emps;
    }
}

员工

//员工类
public class Emp {
    public Integer empNo;
    public String empName;
    public Integer deptNo;


    public Integer getEmpNo() {
        return empNo;
    }

    public void setEmpNo(Integer empNo) {
        this.empNo = empNo;
    }

    public String getEmpName() {
        return empName;
    }

    public void setEmpName(String empName) {
        this.empName = empName;
    }

    public Integer getDeptNo() {
        return deptNo;
    }

    public void setDeptNo(Integer deptNo) {
        this.deptNo = deptNo;
    }
}

先创建一个接口，定义如下方法

//一对多单条SQL
public Dept getDeptById(int id);

在配置文件中写入以下内容

<!--单条SQL-->
<resultMap id="deptMapper" type="Dept">
   <result column="deptName" property="deptName"></result>
    <collection property="emps" ofType="Emp">
        <result column="empName" property="empName"></result>
    </collection>
</resultMap>
<select id="getDeptById" resultMap="deptMapper">
    SELECT deptName,empName from dept,emp
    where dept.deptNo=emp.deptNo and
    dept.deptNo=#{deptNo}
</select>

MyBatis中使用collection标签来解决一对多的关联查询，property属性指定集合名称，ofType属性指定集合中元素的对象类型。

2.一对多多条SQL

与单条SQL类似，需要改动的地方，在collection节点上添加两个属性，代码如下

<!--多条SQL-->
<resultMap id="empMapper" type="Dept">
    <result column="deptName" property="deptName"></result>
    <collection property="emps" ofType="Emp" select="selectMultSQL" column="deptNo">
    </collection>
</resultMap>
<select id="getDeptByIdMultSQL" resultMap="empMapper">
    SELECT * from dept
    where deptNo=#{deptNo}
</select>
<select id="selectMultSQL" resultType="Emp">
    SELECT * from emp
    where deptNo=#{deptNo}
</select>

select属性指向一个新的select节点，column属性指向数据表中的列名。

3.多对一单条SQL

在员工实体类（Emp）中添加所属部门类（Dept）的对象

创建接口方法

//多对一 单条SQL
public Emp getEmpById(int empNo);

配置xml文件

<!--单条SQL-->
<resultMap id="EmpMapper" type="emp">
   <result column="empName" property="empName"></result>
    <association property="dept" javaType="Dept">
        <result column="deptName" property="deptName"></result>
    </association>
</resultMap>
<select id="getEmpById" resultMap="EmpMapper">
    SELECT deptName,empName from dept,emp
    where dept.deptNo=emp.deptNo and
    empNo=#{deptNo}
</select>

MyBatis中使用association标签来解决多对一的关联查询，property属性指定对象名称，javaType属性指定对象类型。

4.多对一多条SQL

   与一对多的实现方法相同

<!--多条SQL-->
<resultMap id="empMapper" type="Emp">
    <result column="empName" property="empName"></result>
    <association property="dept" javaType="Dept" select="selectMultSQL" column="deptNo">
    </association>
</resultMap>
<select id="getEmpByIdMultSQL" resultMap="empMapper">
    SELECT * from emp
    where empNo=#{empNo}
</select>
<select id="selectMultSQL" resultType="Dept">
    SELECT * from dept
    where deptNo=#{deptNo}
</select>

5.多对多

//教师类
public class Teacher {
    private Integer tid;
    private String tname;
    //植入学生集合
    private List<Student> stus=new ArrayList<Student>();

    public Integer getTid() {
        return tid;
    }

    public void setTid(Integer tid) {
        this.tid = tid;
    }

    public String getTname() {
        return tname;
    }

    public void setTname(String tname) {
        this.tname = tname;
    }

    public List<Student> getStus() {
        return stus;
    }

    public void setStus(List<Student> stus) {
        this.stus = stus;
    }
}

//学生类
public class Student {
    private Integer sid;
    private String sname;

    public Integer getSid() {
        return sid;
    }

    public void setSid(Integer sid) {
        this.sid = sid;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }
}

数据库结构

 

  创建接口编写方法

　　

//根据老师Id查询该老师所教的学员
public Teacher findAll(int tid);

配置xml

<!--多对多-->
 <resultMap id="teacherMapper" type="Teacher">
     <id column="tid" property="tid"></id>
     <result column="tname" property="tname"></result>
     <collection property="stus" ofType="Student">
         <id column="sid" property="sid"></id>
         <result column="sname" property="sname"></result>
     </collection>
 </resultMap>

 <select id="findAll" resultMap="teacherMapper">
     SELECT * FROM student,teacher,Teacher_Student
     WHERE Student.`sid`=Teacher_Student.`sid`
     AND Teacher.`tid`=Teacher_Student.`tid`
     AND Teacher_Student.`tid`=#{tid}
 </select>

6.自连接

 实体类

public class Category {
    private Integer cid;
    private String cname;
    private Set<Category> cates;

    @Override
    public String toString() {
        return "Category{" +
                "cid=" + cid +
                ", cname='" + cname + '\'' +
                ", cates=" + cates +
                '}';
    }

    public Integer getCid() {
        return cid;
    }

    public void setCid(Integer cid) {
        this.cid = cid;
    }

    public String getCname() {
        return cname;
    }

    public void setCname(String cname) {
        this.cname = cname;
    }

    public Set<Category> getCates() {
        return cates;
    }

    public void setCates(Set<Category> cates) {
        this.cates = cates;
    }
}

数据库结构
　　

 

配置xml文件

<!--自连接-->
 <resultMap id="categoryMapper" type="Category">
     <id column="tid" property="tid"></id>
     <result column="tname" property="tname"></result>
     <collection property="cates" ofType="Category" select="findAll" column="cid">
     </collection>
 </resultMap>

 <select id="findAll" resultMap="categoryMapper">
     SELECT * from category where pid=#{pid}
 </select>




谢谢观看！