多表查询之一对一、一对多、多对多_四张表连接查询-CSDN博客

本文链接：https://blog.csdn.net/qq_52350532/article/details/134525940

项目的前期准备

jdbc.properties

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis
jdbc.username=root
jdbc.password=root

applicationContext-mybatis.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="jdbc.properties"/>
    <typeAliases>
        <package name="com.yan.pojo"/>
    </typeAliases>
    <environments default="dev">
        <environment id="dev">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <package name="com/yan/mapper"/>
    </mappers>
</configuration>

实体类的建立

@AllArgsConstructor
@NoArgsConstructor
@Data
public class Emp implements Serializable {
    private Integer empno;
    private String ename;
    private String job;
    private Integer mgr;
    private Date hiredate;
    private Double sal;
    private Double comm;
    private Integer deptno;

}

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Dept implements Serializable {

    private Integer deptno;
    private String dname;
    private String loc;
}

一、一对一

根据编号查询员工信息及所在的部门信息

1.1 实体类的改变

@AllArgsConstructor
@NoArgsConstructor
@Data
public class Emp implements Serializable {
    private Integer empno;
    private String ename;
    private String job;
    private Integer mgr;
    private Date hiredate;
    private Double sal;
    private Double comm;
    private Integer deptno;
    // 组合一个Dept对象作为自己的属性
    private Dept dept;

}

1.2 EmpMapper

public interface EmpMapper {

    /**
     * 根据员工编号查询emp 对象 和 dept 对象信息
     * @param empno
     * @return
     */
    public Emp findEmpJoinDeptByEmpno(Integer empno);
}

1.3 EmpMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yan.mapper.EmpMapper">

    <!--封装的结果映射集-->
    <resultMap id="empJoinDept" type="com.yan.pojo.Emp">
        <!--column 是指数据库表中的字段名
            property 是指实体类中的属性名
            id 是主键独有的
        -->
        <id property="empno" column="empno" />
        <result column="ename" property="ename"/>
        <result property="job" column="job"></result>
        <result property="sal" column="sal"></result>
        <result property="hiredate" column="hiredate"></result>
        <result property="mgr" column="mgr"></result>
        <result property="comm" column="comm"></result>
        <result property="deptno" column="deptno"></result>

        <!--封装一对一的查询
            javaType 主要是指实体类中对应的类型
        -->
        <association property="dept" javaType="com.yan.pojo.Dept">
            <id column="deptno" property="deptno" />
            <result column="dname" property="dname"/>
            <result column="loc" property="loc"/>
        </association>
    </resultMap>

    <select id="findEmpJoinDeptByEmpno" resultMap="empJoinDept">
        SELECT * FROM emp e LEFT JOIN dept d on e.DEPTNO = d.deptno WHERE e.EMPNO = #{empno}
    </select>
</mapper>

1.4 测试

 @Test
    public void findEmpJoinDept(){

        try {
            SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
            SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(Resources.getResourceAsStream("applicationContext-mybatis.xml"));
            SqlSession sqlSession = sqlSessionFactory.openSession();
            EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
            Emp emp = mapper.findEmpJoinDeptByEmpno(7369);
            System.out.println(emp);
            sqlSession.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

测试结果

Emp(empno=7369, ename=SMITH, job=CLERK, mgr=7902, hiredate=Wed Dec 17 00:00:00 CST 1980, sal=800.0, comm=null, deptno=20, dept=Dept(deptno=20, dname=RESEARCH, loc=DALLAS))

二、一对多

2.1 Dept

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Dept implements Serializable {

    private Integer deptno;
    private String dname;
    private String loc;

    //list 集合中存入 emp 对象
    private List<Emp> empList;
}

2.2 DeptMapper

public interface DeptMapper {

//    public List<Dept> findAllDept();

    public Dept deptJoinEmpByDeptno(Integer deptno);
}

2.3 DeptMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yan.mapper.DeptMapper">

<!--    <select id="findAllDept" resultType="com.yan.pojo.Dept">
        select * from dept
    </select>-->

    <resultMap id="deptJoinEmoMap" type="com.yan.pojo.Dept">
        <id column="deptno" property="deptno"/>
        <result column="dname" property="dname" />
        <result column="loc" property="loc" />

        <!--处理一对多关系的标签-->
        <collection property="empList" ofType="com.yan.pojo.Emp">
            <id property="empno" column="empno" />
            <result column="ename" property="ename"/>
            <result property="job" column="job"></result>
            <result property="sal" column="sal"></result>
            <result property="hiredate" column="hiredate"></result>
            <result property="mgr" column="mgr"></result>
            <result property="comm" column="comm"></result>
            <result property="deptno" column="deptno"></result>
        </collection>
    </resultMap>

    <!--查询部门中所有的员工信息-->
    <select id="deptJoinEmpByDeptno" resultMap="deptJoinEmoMap">
        select * from
        dept d
        left join
        emp e
        on
        d.deptno = e.deptno
        where
        d.deptno = #{deptno}
    </select>
</mapper>

2.4 测试

 @Test
    public void deptJoinEmp(){

        try {
            SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
            SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(Resources.getResourceAsStream("applicationContext-mybatis.xml"));
            SqlSession sqlSession = sqlSessionFactory.openSession();
            DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
            Dept dept = mapper.deptJoinEmpByDeptno(20);
            System.out.println(dept);

            List<Emp> empList = dept.getEmpList();
            for (Emp emp : empList) {
                System.out.println(emp);
            }

            sqlSession.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

Dept(deptno=20, dname=RESEARCH, loc=DALLAS, empList=[Emp(empno=7369, ename=SMITH, job=CLERK, mgr=7902, hiredate=Wed Dec 17 00:00:00 CST 1980, sal=800.0, comm=null, deptno=20), Emp(empno=7566, ename=JONES, job=MANAGER, mgr=7839, hiredate=Thu Apr 02 00:00:00 CST 1981, sal=2975.0, comm=null, deptno=20), Emp(empno=7788, ename=SCOTT, job=ANALYST, mgr=7566, hiredate=Sun Apr 19 00:00:00 CDT 1987, sal=3000.0, comm=null, deptno=20), Emp(empno=7876, ename=ADAMS, job=CLERK, mgr=7788, hiredate=Sat May 23 00:00:00 CDT 1987, sal=1100.0, comm=null, deptno=20), Emp(empno=7902, ename=FORD, job=ANALYST, mgr=7566, hiredate=Thu Dec 03 00:00:00 CST 1981, sal=3000.0, comm=null, deptno=20)])

Emp(empno=7369, ename=SMITH, job=CLERK, mgr=7902, hiredate=Wed Dec 17 00:00:00 CST 1980, sal=800.0, comm=null, deptno=20)
Emp(empno=7566, ename=JONES, job=MANAGER, mgr=7839, hiredate=Thu Apr 02 00:00:00 CST 1981, sal=2975.0, comm=null, deptno=20)
Emp(empno=7788, ename=SCOTT, job=ANALYST, mgr=7566, hiredate=Sun Apr 19 00:00:00 CDT 1987, sal=3000.0, comm=null, deptno=20)
Emp(empno=7876, ename=ADAMS, job=CLERK, mgr=7788, hiredate=Sat May 23 00:00:00 CDT 1987, sal=1100.0, comm=null, deptno=20)
Emp(empno=7902, ename=FORD, job=ANALYST, mgr=7566, hiredate=Thu Dec 03 00:00:00 CST 1981, sal=3000.0, comm=null, deptno=20)

三、多对多

3.1 实体类


@Data
@AllArgsConstructor
@NoArgsConstructor
public class Projects implements Serializable {

    private Integer pid;
    private String pname;
    private String money;

    // 组合一个对象
    private List<ProjectRecord> projectRecords;
}

@Data
@AllArgsConstructor
@NoArgsConstructor
public class ProjectRecord implements Serializable {

    private Integer empno;
    private Integer pid;

    // 组合一个emp对象作为属性
    private Emp emp;
}

3.2 ProjectsMapper

public interface ProjectsMapper {

    /**
     * 更具项目编号查询项目信息，以及参与该项目的员工信息
     * @param pid
     * @return
     */
    public Projects findProjectsJoinEmpByPid(Integer pid);
}

3.3 ProjectsMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yan.mapper.ProjectsMapper">

    <resultMap id="findProjectsJoinEmpByPidMap" type="com.yan.pojo.Projects">
        <id column="pid" property="pid"></id>
        <result column="pname" property="pname"></result>
        <result column="money" property="money"></result>

        <!--一对多 集合 属性 -->
        <collection property="projectRecords" ofType="com.yan.pojo.ProjectRecord">
            <id column="empno" property="empno"></id>
            <id column="pid" property="pid"></id>
            <association property="emp" javaType="com.yan.pojo.Emp">
                <id property="empno" column="empno" />
                <result column="ename" property="ename"/>
                <result property="job" column="job"></result>
                <result property="sal" column="sal"></result>
                <result property="hiredate" column="hiredate"></result>
                <result property="mgr" column="mgr"></result>
                <result property="comm" column="comm"></result>
                <result property="deptno" column="deptno"></result>
            </association>
        </collection>
    </resultMap>

    <select id="findProjectsJoinEmpByPid" resultMap="findProjectsJoinEmpByPidMap">
        SELECT * FROM
        projects p
        LEFT JOIN
        projectrecord pr
        on
        p.pid = pr.pid
        LEFT JOIN
        emp e
        on
        pr.empno = e.empno
        where p.pid = #{pid}
    </select>
</mapper>

3.4 测试

 @Test
    public void findProjectsJoinEmpByPid(){

        try {
            SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
            SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(Resources.getResourceAsStream("applicationContext-mybatis.xml"));
            SqlSession sqlSession = sqlSessionFactory.openSession();

            ProjectsMapper mapper = sqlSession.getMapper(ProjectsMapper.class);
            Projects projectsJoinEmpByPid = mapper.findProjectsJoinEmpByPid(2);
            System.out.println(projectsJoinEmpByPid);
            List<ProjectRecord> projectRecords = projectsJoinEmpByPid.getProjectRecords();
            for (ProjectRecord projectRecord : projectRecords) {

                System.out.println(projectRecord);

                Emp emp = projectRecord.getEmp();
                System.out.println(emp);
            }

            sqlSession.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

Projects(pid=2, pname=学生选课系统, money=100000, projectRecords=[ProjectRecord(empno=7369, pid=2, emp=Emp(empno=7369, ename=SMITH, job=CLERK, mgr=7902, hiredate=Wed Dec 17 00:00:00 CST 1980, sal=800.0, comm=null, deptno=20)), ProjectRecord(empno=7499, pid=2, emp=Emp(empno=7499, ename=ALLEN, job=SALESMAN, mgr=7698, hiredate=Fri Feb 20 00:00:00 CST 1981, sal=1600.0, comm=300.0, deptno=30)), ProjectRecord(empno=7521, pid=2, emp=Emp(empno=7521, ename=WARD, job=SALESMAN, mgr=7698, hiredate=Sun Feb 22 00:00:00 CST 1981, sal=1250.0, comm=500.0, deptno=30))])

ProjectRecord(empno=7369, pid=2, emp=Emp(empno=7369, ename=SMITH, job=CLERK, mgr=7902, hiredate=Wed Dec 17 00:00:00 CST 1980, sal=800.0, comm=null, deptno=20))
Emp(empno=7369, ename=SMITH, job=CLERK, mgr=7902, hiredate=Wed Dec 17 00:00:00 CST 1980, sal=800.0, comm=null, deptno=20)
ProjectRecord(empno=7499, pid=2, emp=Emp(empno=7499, ename=ALLEN, job=SALESMAN, mgr=7698, hiredate=Fri Feb 20 00:00:00 CST 1981, sal=1600.0, comm=300.0, deptno=30))
Emp(empno=7499, ename=ALLEN, job=SALESMAN, mgr=7698, hiredate=Fri Feb 20 00:00:00 CST 1981, sal=1600.0, comm=300.0, deptno=30)
ProjectRecord(empno=7521, pid=2, emp=Emp(empno=7521, ename=WARD, job=SALESMAN, mgr=7698, hiredate=Sun Feb 22 00:00:00 CST 1981, sal=1250.0, comm=500.0, deptno=30))
Emp(empno=7521, ename=WARD, job=SALESMAN, mgr=7698, hiredate=Sun Feb 22 00:00:00 CST 1981, sal=1250.0, comm=500.0, deptno=30)