SSM框架之MyBatis多表联查（详细使用案例）

目录

一、多表联查

1.准备阶段

2.多对一查询

1、join联查

2.嵌套查询（子查询）

---

一、多表联查

1.准备阶段

数据库表2个

CREATE TABLE `emp` (
  `emp_id` int NOT NULL AUTO_INCREMENT,
  `emp_name` varchar(20) DEFAULT NULL,
  `emp_age` int DEFAULT NULL,
  `part_id` int DEFAULT NULL,
  PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES ('1', '张三', '22', '2');
INSERT INTO `emp` VALUES ('2', '李四', '32', '1');
INSERT INTO `emp` VALUES ('3', '王五', '21', '2');
INSERT INTO `emp` VALUES ('4', '二夫人', '24', '3');
INSERT INTO `emp` VALUES ('5', '李夫人', '32', '1');

CREATE TABLE `part` (
  `part_id` int NOT NULL AUTO_INCREMENT,
  `part_name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`part_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of part
-- ----------------------------
INSERT INTO `part` VALUES ('1', '财务部');
INSERT INTO `part` VALUES ('2', '人力部');
INSERT INTO `part` VALUES ('3', '后勤部');

建maven项目，导入相关依赖等 我前面一片文章详细介绍过 此处就不再冗述

此处以查询员工号为5的员工的所有信息为例

2.多对一查询

1、join联查

创建相关的实体类

package com.exy.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @program: Mybatis08
 * @description:
 * @author: jdy
 * @create: 2021-12-08 19:03
 **/

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Emp {
    private int empid;
    private String empname;
    private Integer empage;
    private int partid;
    private Part part;
}

package com.exy.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * @program: Mybatis08
 * @description:
 * @author: jdy
 * @create: 2021-12-08 19:03
 **/

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Part {
    private int partid;
    private String partname;
}

创建dao接口并添加以id查询的方法

List<Emp> selectById(int id);

 写出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="com.exy.dao.EmpDao">
    
    <resultMap id="test01" type="com.exy.entity.Emp">
        <id property="empid" column="emp_id"/>
        <result property="empname" column="emp_name"/>
        <result property="empage" column="emp_age"/>
        <result property="empid" column="emp_id"/>
        <result property="partid" column="part_id"/>

        <association property="part" javaType="com.exy.entity.Part" autoMapping="true">
            <id property="partid" column="part_id"/>
            <result property="partname" column="part_name"/>
        </association>
    </resultMap>

    <select id="selectById" resultMap="test01" parameterType="int">
        select * from emp e join part p on e.part_id=p.part_id where e.emp_id=5
    </select>

</mapper>

 添加测试类进行测试

import com.exy.dao.EmpDao;
import com.exy.entity.Emp;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;

import java.io.Reader;
import java.util.List;

/**
 * @program: Mybatis08
 * @description:
 * @author: jdy
 * @create: 2021-12-08 19:15
 **/

public class ETest {
    private SqlSession session;
    @Before
    public void before() throws Exception{
        Reader reader = Resources.getResourceAsReader("mybatis.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        session=sessionFactory.openSession();
    }
    @Test
    public void test01(){
        EmpDao empDao = session.getMapper(EmpDao.class);
        List<Emp> emps = empDao.selectById(5);
        System.out.println(emps);
    }
}

 结果如图

2.嵌套查询（子查询）

嵌套查询也就是先查出一个表中的相关数据 再用得出的数据查询下一个表 从而的出数据

分析得出 我们想查出员工为5的部门的话需要 本表的部门号 再用部门号查询另一个表

先根据part查询部门

<?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.exy.dao.PartDao">
    <resultMap id="test01" type="com.exy.entity.Part">
        <id property="partid" column="part_id"/>
        <result property="partname" column="part_name"/>
    </resultMap>
    
    <select id="selectById" resultMap="test01">
        select * from part where part_id=#{partid}
    </select>
    
</mapper>

 zdEmpMapper中添加代码

 注意我图中标记的 是引用上一个的查询的结果中的列

添加测试代码

@Test
    public void test03(){
        PartDao partDao = session.getMapper(PartDao.class);
        List<Part> emps = partDao.selectById(1);
        System.out.println(emps);
    }

 运行结果如图所示

 发出了两条sql

注：