原生一对多查询

查询一个dept部门的部门信息和该部门下所有员工信息（这是一对多，一个部门下有多个员工）

Emp

package domain;

public class Emp {

    //自有属性
    private Integer empno;
    private String ename;
    private Float sal;
    //关联属性  deptno-->外键-->对象
    private Dept dept;

    public Emp() {}
    public Emp(Integer empno, String ename, Float sal, Dept dept) {
        this.empno = empno;
        this.ename = ename;
        this.sal = sal;
        this.dept = dept;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "empno=" + empno +
                ", ename='" + ename + '\'' +
                ", sal=" + sal +
                ", 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 Float getSal() {
        return sal;
    }
    public void setSal(Float sal) {
        this.sal = sal;
    }
    public Dept getDept() {
        return dept;
    }
    public void setDept(Dept dept) {
        this.dept = dept;
    }

}

Dept

package domain;

import java.util.List;

public class Dept {

    //自有属性
    private Integer deptno;
    private String dname;
    private String loc;
    //为了根据deptno部门编号查询 部门中所有的人更加方便
    //也存储一个关联属性     一个部门中有好多员工  List<Emp>
    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;
    }

    @Override
    public String toString() {
        return "Dept{" +
                "deptno=" + deptno +
                ", dname='" + dname + '\'' +
                ", loc='" + loc + '\'' +
                ", 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;
    }

}

DeptDao

public class DeptDao {

    private SqlSession sqlSession = MyUtil.getSqlSession(true);

    //设计一个方法 根据给定的deptno部门编号 查询部门信息+这个部门中的所有员工信息
    public Dept selectOne(Integer deptno){
        return sqlSession.selectOne("selectOne",deptno);
    }

    //设计一个方法 查询所有部门的信息 + 每一个部门中的所有员工信息
    public List<Dept> selectAll(){
        return sqlSession.selectList("selectAll");
    }
}

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="dao.DeptDao">

    <!-- 方式一 利用两条语句 分别查询 也出现N+1问题 设置延迟加载的方式解决 -->
    <!--<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" javaType="list" ofType="domain.Emp" select="selectEmpForDept" column="deptno"></collection>-->
    <!--</resultMap>-->
    <!--<select id="selectEmpForDept" resultType="domain.Emp">-->
        <!--select empno,ename,sal,deptno from emp where deptno = #{deptno}-->
    <!--</select>-->

    <!-- 方式二 利用联合查询 一次性将所有记录都查到 直接赋值就可以啦 -->
    <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" javaType="list" ofType="domain.Emp">
            <id property="empno" column="empno"></id>
            <result property="ename" column="ename"></result>
            <result property="sal" column="sal"></result>
        </collection>
    </resultMap>

    <!--========================================================-->
    <select id="selectOne" resultMap="selectDept">
        <!-- 方式一 -->
        <!--select deptno,dname,loc from dept where deptno = #{deptno}-->

        <!-- 方式二 -->
        select e.empno,e.ename,e.sal,d.deptno,d.dname,d.loc from emp e right outer join dept d on e.deptno = d.deptno where d.deptno = #{deptno}
    </select>

    <select id="selectAll" resultMap="selectDept">
        <!-- 方式一 -->
        <!--select deptno,dname,loc from dept-->

        <!-- 方式二 -->
        select e.empno,e.ename,e.sal,d.deptno,d.dname,d.loc from emp e right outer join dept d on e.deptno = d.deptno
    </select>
</mapper>

EmpDao

package dao;

import domain.Emp;
import org.apache.ibatis.session.SqlSession;
import util.MyUtil;

import java.util.List;

public class EmpDao {

    //一个sqlSession对象
    private SqlSession sqlSession = MyUtil.getSqlSession(true);


    //设计一个方法 提供一个empno员工编号 查询员工的信息+部门信息
    public Emp selectOne(Integer empno){
        return sqlSession.selectOne("selectOne",empno);
    }

    //设计一个方法 提供一个查询所有员工信息+部门信息
    public List<Emp> selectAll(){
        return sqlSession.selectList("selectAll");
    }
}

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="dao.EmpDao">

    <!--自定义规则 方式一 两条语句查询-->
    <!--<resultMap id="selectEmp" type="domain.Emp">-->
        <!--<id property="empno" column="empno"></id>-->
        <!--<result property="ename" column="ename"></result>-->
        <!--<result property="sal" column="sal"></result>-->
        <!--<association property="dept" javaType="domain.Dept" select="selectDeptForEmp" column="deptno"></association>-->
    <!--</resultMap>-->
    <!--<select id="selectDeptForEmp" resultType="domain.Dept">-->
        <!--select deptno,dname,loc from dept where deptno = #{deptno}-->
    <!--</select>-->

    <!--自定义规则 方式二 一条语句查询 联合-->
    <resultMap id="selectEmp" type="domain.Emp">
        <id property="empno" column="empno"></id>
        <result property="ename" column="ename"></result>
        <result property="sal" column="sal"></result>
        <association property="dept" javaType="domain.Dept">
            <id property="deptno" column="deptno"></id>
            <result property="dname" column="dname"></result>
            <result property="loc" column="loc"></result>
        </association>
    </resultMap>

    <!--=======================================================-->
    <select id="selectOne" resultMap="selectEmp">
        <!--方式一-->
--         select empno,ename,sal,deptno from emp where empno = #{empno}
        <!--方式二-->
        select e.empno,e.ename,e.sal,d.deptno,d.dname,d.loc from emp e left outer join dept d on e.deptno = d.deptno where e.empno = #{empno}
    </select>
    <select id="selectAll" resultMap="selectEmp">
        <!--方式一-->
--         select empno,ename,sal,deptno from emp
        <!--方式二-->
        select e.empno,e.ename,e.sal,d.deptno,d.dname,d.loc from emp e left outer join dept d on e.deptno = d.deptno
    </select>
</mapper>

configuration.xml

<?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>
    <settings>
        <setting name="lazyLoadingEnabled" value="true"/>
        <setting name="aggressiveLazyLoading" value="false"/>
    </settings>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/testmybatis?useSSL=false"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!--<mapper resource="mapper/EmpMapper.xml"></mapper>-->
        <mapper resource="mapper/DeptMapper.xml"></mapper>
    </mappers>
</configuration>