MyBatis的多表联查(一对多)

创建数据库表:department(部门表),employee(员工表)同时设定部门和员工表的关系

CREATE TABLE department(
 d_id INT NOT NULL AUTO_INCREMENT,
 d_name VARCHAR(100),
 PRIMARY
 KEY(d_id)
)

CREATE TABLE employee(
 e_id INT NOT NULL AUTO_INCREMENT,
 e_name VARCHAR(30),
 e_gender VARCHAR(6),
 e_age INT,
 e_depart_id INT,
 PRIMARY KEY(e_id),
 FOREIGN KEY(e_depart_id) REFERENCES department(d_id)
)

1、创建实体类Department和employee

public class Department {
    private int did ;
    private String dname ;
    private List<Employee> employeeList; //通过部门找员工 一对多

    @Override
    public String toString() {
        return "Department{" +
                "did=" + did +
                ", dname='" + dname + '\'' +
                ", employee=" + employeeList +
                '}';
    }

    public int getDid() {
        return did;
    }

    public void setDid(int did) {
        this.did = did;
    }

    public String getDname() {
        return dname;
    }

    public void setDname(String dname) {
        this.dname = dname;
    }

    public List<Employee> getEmployeeList() {
        return employeeList;
    }

    public void setEmployeeList(List<Employee> employeeList) {
        this.employeeList = employeeList;
    }
}

public class Employee {
    private int eid;
    private String ename ;
    private String egender ;
    private int eage ;

    @Override
    public String toString() {
        return "Employee{" +
                "eid=" + eid +
                ", ename='" + ename + '\'' +
                ", egender='" + egender + '\'' +
                ", eage=" + eage +
                '}';
    }

    public int getEid() {
        return eid;
    }

    public void setEid(int eid) {
        this.eid = eid;
    }

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public String getEgender() {
        return egender;
    }

    public void setEgender(String egender) {
        this.egender = egender;
    }

    public int getEage() {
        return eage;
    }

    public void setEage(int eage) {
        this.eage = eage;
    }
}

2、编写持久层接口方法

(DepartmentDao接口)

public interface DepartmentDao {
    //根据部门名称,查询部门信息(员工)
    Department findByDname(String name);
}

(EmployeeDao接口) 

public interface EmployeeDao {
    List<Employee>selectByDepartmentId(int departId);
}

 3、编写对应的映射文件DepartmentDao.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="com.offcn.dao.DepartmentDao">
<!--    一对多,方式一:嵌入式-->

<!--    <resultMap id="departmentResult" type="com.offcn.entity.Department">-->
<!--        <id column="d_id" property="did"/>-->
<!--        <result column="d_name" property="dname"/>-->
<!--        <collection property="employeeList" ofType="com.offcn.entity.Employee">-->
<!--            <id column="e_id" property="eid"/>-->
<!--            <result column="e_name" property="ename"/>-->
<!--            <result column="e_gender" property="egender"/>-->
<!--            <result column="e_age" property="eage"/>-->
<!--        </collection>-->
<!--    </resultMap>-->
<!--    <select id="findByDname" resultMap="departmentResult">-->
<!--        select * from department d , employee e-->
<!--        where-->
<!--        e.e_depart_id=d.d_id and d_name=#{name}-->
<!--    </select>-->

<!--    一对多,方式二:嵌套式查询-->

    <resultMap id="departmentResult" type="com.offcn.entity.Department">
        <id column="d_id" property="did"/>
        <result column="d_name" property="dname"/>
        <collection property="employeeList"
            select="com.offcn.dao.EmployeeDao.selectByDepartmentId" column="d_id">
        </collection>
    </resultMap>
    
    <select id="findByDname" resultMap="departmentResult">
            select * from department where d_name=#{name}
    </select>
    
</mapper>

—嵌套式———EmployeeDao.xml映射文件

<mapper namespace="com.offcn.dao.EmployeeDao">
    <resultMap id="empResult" type="com.offcn.entity.Employee">
        <id column="e_id" property="eid"/>
        <result column="e_name" property="ename"/>
        <result column="e_gender" property="egender"/>
        <result column="e_age" property="eage"/>
    </resultMap>
    
    <select id="selectByDepartmentId" resultMap="empResult">
        select * from employee where e_depart_id=#{departId}
    </select>
</mapper>

4、编写对应的测试类

    @Test
    public void method() throws Exception{
        InputStream stream = Resources.getResourceAsStream("mybatisConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        IdcardDao mapper = sqlSession.getMapper(IdcardDao.class);
        Idcard idcard = mapper.selectByCardno("110112199012127821");
        System.out.println(idcard);
    }