MyBatis（四）关于多表联查 关联关系

在MyBatis中，进行多表联查时关联关系主要有这几种：一对多，多对一，多对多，还有一种自关联这几种用的比较多

1.一对多：有两种方式

（1）用一条sql语句进行查询    （以查询部门和员工为案例）

　首先创建实体类

package entity;

import java.util.List;

/**
 * Created by mycom on 2018/2/26.
 */
public class Dept {//部门
    private Integer deptNo;

    private String deptName;

    private List<Emp> emps;

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

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

    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;
    }



}

然后创建部门接口以及对应的xml文件

package dao;

import entity.Dept;
import entity.Emp;

import java.util.List;

/**
 * Created by mycom on 2018/2/26.
 */
public interface IDeptDao {
    //根据部门编号获得部门名称  员工名称  单条sql
    public Dept getEmpByDeptNo(int deptNo);
}



<?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.IDeptDao">
    <resultMap id="deptMaper" type="Dept">
        <id column="d" property="deptNo"></id>
        <result column="deptName" property="deptName"></result>
        <collection property="emps" ofType="Emp">
            <id column="empNo" property="empNo"></id>
            <result column="empName" property="empName"></result>
        </collection>
    </resultMap>

    <!--查询部门和员工  单条sql语句方案-->
    <select id="getEmpByDeptNo" resultMap="deptMaper">
        SELECT dept.deptNo as d ,deptName,empName FROM dept,emp
        WHERE dept.`deptNo`=emp.`deptNo`
        AND dept.`deptNo`=#{deptNo}
    </select>

</mapper>

编写测试类
//查询部门和员工  单条sql
@Test
public void oneToMany(){
    SqlSession sqlSession = MyBatisUtil.getSession();
    IDeptDao mapper = sqlSession.getMapper(IDeptDao.class);
    Dept dept = mapper.getEmpByDeptNo(1);
    System.out.println("部门名称"+dept.getDeptName());
    for (Emp emp:dept.getEmps()) {
        System.out.println(emp.getEmpName());
    }
    sqlSession.close();
}

（2）多条sql语句查询

   
接口中的方法

public interface IDeptDao {

 //根据部门编号获得部门名称  员工名称  多条sql
    public Dept getEmpByDeptNoMultiSQL(int deptNo);
}

<?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.IDeptDao">
    <!--多条sql-->
    <resultMap id="deptMaperMulti" type="Dept">
        <id column="d" property="deptNo"></id>
        <result column="deptName" property="deptName"></result>
        <collection property="emps" ofType="Emp" select="selectEmpByDeotNo" column="deptNo">
        </collection>
    </resultMap>
    <select id="selectEmpByDeotNo" resultType="Emp">
        SELECT * from Emp
        WHERE  deptNo=#{deptNo}
    </select>
    <!--查询部门和员工  多条sql语句方案-->
    <select id="getEmpByDeptNoMultiSQL" resultMap="deptMaperMulti">
        select * from dept
        where deptNo=#{deptNo}
    </select>
</mapper>

 

测试类

    //查询部门和员工  多条sql
    @Test
    public void oneToManyMulti(){
        SqlSession sqlSession = MyBatisUtil.getSession();
        IDeptDao mapper = sqlSession.getMapper(IDeptDao.class);
        Dept dept = mapper.getEmpByDeptNoMultiSQL(1);
        System.out.println("部门名称"+dept.getDeptName());
        for (Emp emp:dept.getEmps()) {
            System.out.println(emp.getEmpName());
        }
        sqlSession.close();
    }

2.多对一（同样有两种方法  单条sql和多条sq）同样使用部门和员工的案例  站在多的一方

首先是接口

package dao;

import entity.Emp;

/**
 * Created by mycom on 2018/2/26.
 */
public interface IEmpDao {
    //根据员工编号查询 单条sql
    public Emp getDeptByEmpNo(int empNo);
    //根据员工编号查询 多条sql
    public Emp getDeptByEmpNoMulti(int empNo);
}

以及接口对应的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="dao.IEmpDao">
    <resultMap id="empMaper" type="Emp">
        <id column="empNo" property="empNo"></id>
        <result property="empName" column="empName"></result>
        <association property="dept" javaType="dept">
            <id column="deptNo" property="deptNo"></id>
            <result property="deptName" column="deptName"></result>
        </association>
    </resultMap>
    <resultMap id="empMaperMulti" type="Emp">
        <id column="empNo" property="empNo"></id>
        <result property="empName" column="empName"></result>
        <association property="dept" javaType="dept" select="selectDeptByEmpNo" column="deptNo">
        </association>
    </resultMap>
    <!--查询部门和员工 单条sql语句方案-->
    <select id="getDeptByEmpNo" resultMap="empMaper">
        SELECT * FROM dept,emp
        WHERE dept.`deptNo`=emp.`deptNo`
        AND empNo=#{empNo}
    </select>


    <select id="selectDeptByEmpNo" resultType="Dept">
        SELECT * FROM dept WHERE deptNo=#{deptNo}
    </select>
    <!--查询部门和员工 多条sql语句方案-->
    <select id="getDeptByEmpNoMulti" resultMap="empMaperMulti">
        SELECT * FROM emp
        WHERE empNo=#{empNo}
    </select>


</mapper>

最后编写测试类进行测试

//多对一  单条sql
    @Test
    public void manyToOne(){
        SqlSession sqlSession = MyBatisUtil.getSession();
        IEmpDao mapper = sqlSession.getMapper(IEmpDao.class);
        Emp emp = mapper.getDeptByEmpNo(1);
        System.out.println("员工名称："+emp.getEmpName());
        System.out.println("员工所属部门："+emp.getDept().getDeptName());
        sqlSession.close();
    }
    //多对一  多条sql
    @Test
    public void manyToOneMulti(){
        SqlSession sqlSession = MyBatisUtil.getSession();
        IEmpDao mapper = sqlSession.getMapper(IEmpDao.class);
        Emp emp = mapper.getDeptByEmpNoMulti(1);
        System.out.println("员工名称："+emp.getEmpName());
        System.out.println("员工所属部门："+emp.getDept().getDeptName());
        sqlSession.close();
    }

3.多对多（比如一名学生可以由多个老师教，一个老师可以教多个学生）

先编写学生类和老师类

package entity;

/**
 * Created by mycom on 2018/2/26.
 */
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;
    }
}



//老师类

package entity;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by mycom on 2018/2/26.
 */
public class Teacher {
    private Integer tid;
    private String tname;

    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;
    }

    //植入学生集合
    private List<Student> stus=new ArrayList<Student>();

}

接口中方法和xml文件

package dao;

import entity.Student;
import entity.Teacher;

import java.util.List;

/**
 * Created by mycom on 2018/2/26.
 */
public interface ITeacherDao {
    //根据老师编号查询学生集合
    public Teacher findAllStudentsByTid(int tid);
}

<?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.ITeacherDao">
    <resultMap id="teacherMaper" 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="findAllStudentsByTid" resultMap="teacherMaper">
        SELECT * FROM student AS s,teacher AS t ,Teacher_Student AS ts
        WHERE  s.`sid`=ts.`sid`
        AND t.`tid`=ts.`tid`
        AND t.`tid`=#{tid}
    </select>


</mapper>

测试类

//多对多
    @Test
    public void manyToMany(){
        SqlSession sqlSession = MyBatisUtil.getSession();
        ITeacherDao mapper = sqlSession.getMapper(ITeacherDao.class);
        Teacher teacher = mapper.findAllStudentsByTid(1);
        System.out.println("名老师姓："+teacher.getTname());
        for (Student student:teacher.getStus()) {
            System.out.println(student.getSname());
        }
        sqlSession.close();
    }

4.自关联（自己即充当一的一方，又可以充当多的一方）

比如无限极分类

首先看数据库中category表

pid为0的是一级分类

pid为1的是二级分类

pid为3的是三级分类

那么category既充当了一又充当了多

现在开始编写实体类Category

package entity;

import java.util.List;

/**
 * Created by mycom on 2018/2/26.
 */
public class Category {
    private Integer cid;
    private String cname;
    private List<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 List<Category> getCates() {
        return cates;
    }

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

接口

package dao;

import entity.Category;

import java.util.List;

/**
 * Created by mycom on 2018/2/26.
 */
public interface ICategoryDao {
    //根据pid查询分类
    public List<Category> getChildrenByPid(int pid);
}

<?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.ICategoryDao">
    <resultMap id="categoryMapper" type="Category">
        <id column="cid" property="cid"></id>
        <result column="cname" property="cname"></result>
        <!--直到下一级没有东西了就停止，否则就继续线下一层找-->
        <collection property="cates" ofType="Category" select="getChildrenByPid" column="cid"></collection>
    </resultMap>
    <select id="getChildrenByPid" resultMap="categoryMapper">
        select * from category where pid=#{pid}
    </select>
</mapper>

测试类

//自关联
    @Test
    public void selfSelect(){
        SqlSession sqlSession = MyBatisUtil.getSession();
        ICategoryDao mapper = sqlSession.getMapper(ICategoryDao.class);
        List<Category> list = mapper.getChildrenByPid(1);
        for (Category category:list) {
            System.out.println(category);
        }
        sqlSession.close();
    }