8_1_关联查询

w1156707958

已于 2022-06-01 21:04:43 修改

阅读量81

点赞数

分类专栏： MyBatis使用教程文章标签： java

于 2022-05-31 17:43:31 首次发布

本文链接：https://blog.csdn.net/w1156707958/article/details/125070214

版权

MyBatis使用教程专栏收录该内容

10 篇文章 0 订阅

订阅专栏

1_手动处理映射关系

实体类
public class Emp  implements Serializable {
    private Integer empno;
    private String name;
    private String job;
    private Integer mgr;
    private Date hiredate;
    private Double sal;
    private Double comm;
    private Integer deptno;
映射文件
<mapper namespace="com.msb.mapper.EmpMapper">
    <!--手动处理数据库查询字段和封装实体类属性之间的映射关系
    1 主键一般使用id属性
    2 当属性名和查询出的数据表字段名相同 可以不写映射关系
    -->
    <resultMap id="empMap" type="emp">
        <!--<id property="empno" column="empno"></id>-->
        <result property="name" column="ename"></result>
        <!--<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>-->
    </resultMap>
    <select id="findByEmpno" resultMap="empMap" >
        select * from emp where empno =#{empno}
    </select>
</mapper>

2_一对一关联查询

数据准备: 创建项目表和项目记录表

CREATE TABLE `projects`  (
  `pid` int(2) NOT NULL AUTO_INCREMENT,
  `pname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `money` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`pid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
INSERT INTO `projects` VALUES (1, ' ***大学OA', 500000);
INSERT INTO `projects` VALUES (2, '学生选课系统', 100000);
INSERT INTO `projects` VALUES (3, '讲师测评系统', 20000);
INSERT INTO `projects` VALUES (4, '线上问答系统 ', 20000);
CREATE TABLE `projectrecord`  (
  `empno` int(4) NOT NULL,
  `pid` int(2) NOT NULL,
  PRIMARY KEY (`empno`, `pid`) USING BTREE,
  INDEX `fk_project_pro`(`pid`) USING BTREE,
  CONSTRAINT `fk_emp_pro` FOREIGN KEY (`empno`) REFERENCES `emp` (`EMPNO`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_project_pro` FOREIGN KEY (`pid`) REFERENCES `projects` (`pid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
INSERT INTO `projectrecord` VALUES (7369, 1);
INSERT INTO `projectrecord` VALUES (7521, 1);
INSERT INTO `projectrecord` VALUES (7369, 2);
INSERT INTO `projectrecord` VALUES (7499, 2);
INSERT INTO `projectrecord` VALUES (7521, 2);
INSERT INTO `projectrecord` VALUES (7369, 3);
INSERT INTO `projectrecord` VALUES (7499, 3);
INSERT INTO `projectrecord` VALUES (7521, 3);
INSERT INTO `projectrecord` VALUES (7369, 4);
INSERT INTO `projectrecord` VALUES (7499, 4);

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

实体类添加一个部门作为属性

实体类

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

接口

public interface EmpMapper {
    /**
     * 根据员工编号查询员工的所有信息并携带所在的部门信息
     * @param empno 要查询的员工编号
     * @return Emp对象,组合了Dept对象作为属性,对部门信息进行存储
     */
    Emp findEmpJoinDeptByEmpno(int empno);
}

映射文件

<?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.msb.mapper.EmpMapper">
    <!--Emp findEmpJoinDeptByEmpno(int empno);-->
    <resultMap id="empJoinDept" type="emp">
        <!--设置emp本身的八个属性的映射关系-->
        <id property="empno" column="empno"></id>
        <result property="ename" column="ename"></result>
        <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 处理一对一
        封装一对一信息关系的标签
        property  emp类的属性名
        javaType  用哪个类的对象给属性赋值
        -->
        <association property="dept" javaType="dept">
            <id column="deptno" property="deptno"></id>
            <result column="dname" property="dname"></result>
            <result column="loc" property="loc"></result>
        </association>
    </resultMap>
    <select id="findEmpJoinDeptByEmpno" resultMap="empJoinDept" >
        select * from
        emp e
        left join dept  d
        on e.deptno =d.deptno
        where empno = #{empno}
    </select>
</mapper>

测试代码

@Test
public void testOneToOne() throws ParseException {
    EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
    Emp emp = mapper.findEmpJoinDeptByEmpno(7499);
    System.out.println(emp);
}

3_一对多关联查询

根据部门号查询部门信息及该部门的所有员工信息

实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Dept implements Serializable {
    private Integer deptno;
    private String dname;
    private String loc;
    // 组合一个Emp的List集合作为属性
    private List<Emp> empList;
}

接口

package com.msb.mapper;
import com.msb.pojo.Dept;
/**
 * @Author: Ma HaiYang
 * @Description: MircoMessage:Mark_7001
 */
public interface DeptMapper {
    /**
     * 根据部门编号查询部门信息及该部分的所有员工信息
     * @param deptno 要查询的部门编号
     * @return Dept对象,内部组合了一个Emp的List属性用于封装部门的所有员工信息
     */
    Dept findDeptJoinEmpsByDeptno(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="com.msb.mapper.DeptMapper">
    <!--Dept findDeptJoinEmpsByDeptno(int deptno);-->
    <resultMap id="deptJoinEmps" type="dept">
        <id column="deptno" property="deptno"></id>
        <result column="dname" property="dname"></result>
        <result column="loc" property="loc"></result>
        <!--处理一对多关系的标签-->
        <collection property="empList" ofType="emp" >
            <!--设置emp本身的八个属性的映射关系-->
            <id property="empno" column="empno"></id>
            <result property="ename" column="ename"></result>
            <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="findDeptJoinEmpsByDeptno" resultMap="deptJoinEmps">
        select * from dept d left join emp e on d.deptno =e.deptno where d.deptno =#{deptno}
    </select>
</mapper>

测试代码

@Test
public void testOneToMany() throws ParseException {
    DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
    Dept dept = mapper.findDeptJoinEmpsByDeptno(20);
    System.out.println(dept);
    System.out.println("---------");
    List<Emp> empList = dept.getEmpList();
    empList.forEach(System.out::println);
}

4_多对多关联查询

根据项目编号查询项目信息,以及参与到该项目之中的所有的员工信息

实体类

@NoArgsConstructor
@AllArgsConstructor
@Data
public class Project  implements Serializable {
    private Integer pid;
    private String pname;
    private Integer money;
    // 组合一个ProjectRecord对象集合作为属性
    private List<ProjectRecord> projectRecords;
}
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ProjectRecord implements Serializable {
    private Integer empno;
    private Integer pid;
    // 组合一个Emp对象作为属性
    private Emp emp;
}

接口

package com.msb.mapper;
import com.msb.pojo.Emp;
import com.msb.pojo.Project;
/**
 * @Author: Ma HaiYang
 * @Description: MircoMessage:Mark_7001
 */
public interface ProjectMapper {
    /**
     * 根据项目编号查询一个项目信息及参与该项目的所有员工信息
     * @param pid 项目编号
     * @return 所有信息封装的Project对象
     */
    Project findProjectJoinEmpsByPid(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="com.msb.mapper.ProjectMapper">
    <!--Project findProjectJoinEmpsByPid(int pid);-->
    <resultMap id="projectJoinEmps" type="project">
        <id column="pid" property="pid"></id>
        <result column="pname" property="pname"></result>
        <result column="money" property="money"></result>
        <!--一对多 集合属性 collection-->
        <collection property="projectRecords" ofType="projectRecord">
            <id column="empno" property="empno"></id>
            <id column="pid" property="pid"></id>
            <!--一对一 -->
            <association property="emp" javaType="emp">
                <id property="empno" column="empno"></id>
                <result property="ename" column="ename"></result>
                <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="findProjectJoinEmpsByPid"  resultMap="projectJoinEmps">
        select * from
        project p
        left join projectrecord pr
        on p.pid = pr.pid
        left join emp e
        on e.empno = pr.empno
        where p.pid= #{pid}
    </select>
</mapper>

测试代码

@Test
public void testManyToMany() throws ParseException {
    ProjectMapper mapper = sqlSession.getMapper(ProjectMapper.class);
    Project project = mapper.findProjectJoinEmpsByPid(2);
    System.out.println(project.getPid());
    System.out.println(project.getPname());
    System.out.println(project.getMoney());
    List<ProjectRecord> projectRecords = project.getProjectRecords();
    for (ProjectRecord projectRecord : projectRecords) {
        Emp emp = projectRecord.getEmp();
        System.out.println(emp);
    }
}

w1156707958

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
8_1_关联查询

目录1_手动处理映射关系2_一对一关联查询3_一对多关联查询4_多对多关联查询1_手动处理映射关系实体类public class Emp implements Serializable { private Integer empno; private String name; private String job; private Integer mgr; private Date hiredate; private D.
复制链接

扫一扫

专栏目录