MyBatis学习（7）—— 多表查询

最新推荐文章于 2024-08-30 18:40:43 发布

Yacht13

最新推荐文章于 2024-08-30 18:40:43 发布

阅读量693

点赞数

分类专栏： MyBatis 文章标签： java MyBatis

本文链接：https://blog.csdn.net/Archer__13/article/details/124211732

版权

MyBatis 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

一. 创建表

员工表：

CREATE TABLE `studymybatis`.`emp` (
  `eid` INT NOT NULL AUTO_INCREMENT,
  `emp_name` VARCHAR(20),
  `age` INT,
  `sex` CHAR(1),
  `email` VARCHAR(20),
  `did` VARCHAR(20),
  PRIMARY KEY (`eid`));

INSERT INTO `studymybatis`.`emp` (`emp_name`, `age`, `sex`, `email`, `did`) VALUES ('张三', '21', '男', 'Zhang@163.com', 'D1');
INSERT INTO `studymybatis`.`emp` (`emp_name`, `age`, `sex`, `email`, `did`) VALUES ('李四', '23', '男', 'Lisi@qq.com', 'D2');
INSERT INTO `studymybatis`.`emp` (`emp_name`, `age`, `sex`, `email`, `did`) VALUES ('王二', '22', '男', 'Wang@163.com', 'D2');
INSERT INTO `studymybatis`.`emp` (`emp_name`, `age`, `sex`, `email`, `did`) VALUES ('小红', '20', '女', 'Hong@163.com', 'D1');
INSERT INTO `studymybatis`.`emp` (`emp_name`, `age`, `sex`, `email`, `did`) VALUES ('王梅', '22', '女', 'Mei@qq.com', 'D3');

部门表：

CREATE TABLE `dept` (
  `did` varchar(20) NOT NULL,
  `dept_name` varchar(20),
  PRIMARY KEY (`did`)); 

INSERT INTO `studymybatis`.`dept` (`did`,`dept_name`) VALUES ('D1', '设计部门');
INSERT INTO `studymybatis`.`dept` (`did`,`dept_name`) VALUES ('D2', '开发部门');
INSERT INTO `studymybatis`.`dept` (`did`,`dept_name`) VALUES ('D3', '测试部门');
INSERT INTO `studymybatis`.`dept` (`did`,`dept_name`) VALUES ('D4', '销售部门');

二. 创建JavaBean

package pojo;

public class Emp {
    private Integer eid;

    private String empName;

    private Integer age;

    private Character sex;

    private String email;

    private Dept dept;

    public Emp() {
    }

    public Emp(Integer eid, String empName, Integer age, Character sex, String email) {
        this.eid = eid;
        this.empName = empName;
        this.age = age;
        this.sex = sex;
        this.email = email;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "eid=" + eid +
                ", empName='" + empName + '\'' +
                ", age=" + age +
                ", sex=" + sex +
                ", email='" + email + '\'' +
                ", dept=" + dept +
                '}';
    }

    public Integer getEid() {
        return eid;
    }

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

    public String getEmpName() {
        return empName;
    }

    public void setEmpName(String empName) {
        this.empName = empName;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Character getSex() {
        return sex;
    }

    public void setSex(Character sex) {
        this.sex = sex;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Dept getDept() {
        return dept;
    }

    public void setDept(Dept dept) {
        this.dept = dept;
    }
}

package pojo;

import java.util.List;

public class Dept {
    private String did;

    private String deptName;

    private List<Emp> emps;

    public Dept() {
    }

    public Dept(String did, String deptName) {
        this.did = did;
        this.deptName = deptName;
    }

    @Override
    public String toString() {
        return "Dept{" +
                "did='" + did + '\'' +
                ", deptName='" + deptName + '\'' +
                ", emps=" + emps +
                '}';
    }
    
    public String getDid() {
        return did;
    }

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

    public String getDeptName() {
        return deptName;
    }

    public void setDeptName(String deptName) {
        this.deptName = deptName;
    }

    public List<Emp> getEmps() {
        return emps;
    }

    public void setEmps(List<Emp> emps) {
        this.emps = emps;
    }
}

三. 多对一

①解决方式一：使用resultMap自定义一个映射关系

package mapper;

import org.apache.ibatis.annotations.Param;
import pojo.Emp;

public interface EmpMapper {
    //查询员工以及员工对应的部门信息
    Emp queryEmpAndDept(@Param("eid") String eid);
}

import mapper.EmpMapper;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import pojo.Emp;
import utils.SqlSessionUtils;

public class MapperTest {
    @Test
    public void testQueryEmpAndDept(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
        Emp emp = mapper.queryEmpAndDept("2");
        System.out.println(emp);
    }
}

<?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="mapper.EmpMapper">  <!--为mapper接口的全类名-->

    <!-- 处理多对一映射 -->
    <resultMap id="ManyToOne" type="Emp">
        <id property="eid" column="eid"></id>
        <result property="empName" column="emp_name"></result>
        <result property="age" column="age"></result>
        <result property="sex" column="sex"></result>
        <result property="email" column="email"></result>
        <result property="dept.did" column="did"></result>
        <result property="dept.deptName" column="dept_name"></result>
    </resultMap>
    
    <select id="queryEmpAndDept" resultMap="ManyToOne">
        select * from emp left join dept on emp.did=dept.did where emp.eid=#{eid}
    </select>

</mapper>

②解决方式二：使用resultMap和association实现

<?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="mapper.EmpMapper">  <!--为mapper接口的全类名-->

    <!-- 处理多对一映射 -->
    <resultMap id="ManyToOne" type="Emp">
        <id property="eid" column="eid"></id>
        <result property="empName" column="emp_name"></result>
        <result property="age" column="age"></result>
        <result property="sex" column="sex"></result>
        <result property="email" column="email"></result>
        <association property="dept" javaType="Dept">  <!-- association：处理多对一的映射关系 -->
            <id property="did" column="did"></id>
            <result property="deptName" column="dept_name"></result>
        </association>
    </resultMap>
    
    <select id="queryEmpAndDept" resultMap="ManyToOne">
        select * from emp left join dept on emp.did=dept.did where emp.eid=#{eid}
    </select>

</mapper>

③解决方式三：分步查询

package mapper;

import org.apache.ibatis.annotations.Param;
import pojo.Emp;

public interface EmpMapper {
    //通过分布查询来获得员工以及员工对应的部门信息
    //①根据员工id查询员工信息
    Emp queryEmp(@Param("eid") Integer eid);
    //②根据员工信息中的部门id查询对应的部门信息（该方法在DeptMapper接口中）
}

package mapper;

import org.apache.ibatis.annotations.Param;
import pojo.Dept;

public interface DeptMapper {
    //①根据部门id查询部门信息
    Dept queryDept(@Param("did") String did);
}

<?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="mapper.EmpMapper">  <!--为mapper接口的全类名-->

    <resultMap id="empMap" type="Emp">
        <id property="eid" column="eid"></id>
        <result property="empName" column="emp_name"></result>
        <result property="age" column="age"></result>
        <result property="sex" column="sex"></result>
        <result property="email" column="email"></result>
        <association property="dept" select="mapper.DeptMapper.queryDept" column="did">
        </association> <!-- select：设置分步查询的sql的唯一标识（namespace.SQLId或者mapper接口的全类名.方法名） -->
    </resultMap>        <!-- column：设置分步查询的条件 -->

    <select id="queryEmp" resultMap="empMap">
        select * from emp where eid=#{eid}
    </select>

</mapper>

<?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="mapper.DeptMapper">  <!--为mapper接口的全类名-->

    <resultMap id="deptMap" type="Dept">
        <id property="did" column="did"></id>
        <result property="deptName" column="dept_name"></result>
    </resultMap>

    <select id="queryDept" resultMap="deptMap">
        select * from dept where did=#{did}
    </select>

</mapper>

import mapper.EmpMapper;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import pojo.Emp;
import utils.SqlSessionUtils;

public class MapperTest {
    @Test
    public void testQueryEmpAndDept(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
        Emp emp = mapper.queryEmp(2);
        System.out.println(emp);
    }
}

四. 一对多

①解决方式一：使用resultMap和collection实现

package mapper;

import org.apache.ibatis.annotations.Param;
import pojo.Dept;

public interface DeptMapper {
    //根据部门id查询部门信息和对应的所有员工信息
    Dept queryDeptAndEmp(@Param("did") String did);
}

<?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="mapper.DeptMapper">  <!--为mapper接口的全类名-->

    <resultMap id="OneToMany" type="Dept">
        <id property="did" column="did"></id>
        <result property="deptName" column="dept_name"></result>
        <collection property="emps" ofType="Emp">  <!--collection：处理一对多的映射关系，ofType：表示该属性所对应的集合中存储数据的类型-->
            <id property="eid" column="eid"></id>
            <result property="empName" column="emp_name"></result>
            <result property="age" column="age"></result>
            <result property="sex" column="sex"></result>
            <result property="email" column="email"></result>
        </collection>
    </resultMap>

    <select id="queryDeptAndEmp" resultMap="OneToMany">
        select * from dept left join emp on dept.did=emp.did where dept.did=#{did}
    </select>

</mapper>

import mapper.DeptMapper;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import pojo.Dept;
import utils.SqlSessionUtils;

public class MapperTest {
    @Test
    public void testQueryDeptAndEmp(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
        Dept dept = mapper.queryDeptAndEmp("D1");
        System.out.println(dept);
    }
}

①解决方式一：分步查询

package mapper;

import org.apache.ibatis.annotations.Param;
import pojo.Dept;

public interface DeptMapper {
    //通过分布查询来获得部门以及部门对应的员工信息
    //①根据部门id查询部门信息
    Dept queryDept(@Param("did") String did);
    //②根据部门信息中的部门id查询对应的用户信息（该方法在EmpMapper接口中）
}

package mapper;

import org.apache.ibatis.annotations.Param;
import pojo.Emp;

import java.util.List;

public interface EmpMapper {
    //根据部门id查询对应的用户信息
    List<Emp> queryEmp(@Param("did") String did);
}

<?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="mapper.DeptMapper">  <!--为mapper接口的全类名-->

    <resultMap id="DeptMap" type="Dept">
        <id property="did" column="did"></id>
        <result property="deptName" column="dept_name"></result>
        <collection property="emps" select="mapper.EmpMapper.queryEmp" column="did"></collection>
    </resultMap>

    <select id="queryDept" resultMap="DeptMap">
        select * from dept where dept.did=#{did}
    </select>

</mapper>

<?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="mapper.EmpMapper">  <!--为mapper接口的全类名-->

    <resultMap id="empMap" type="Emp">
        <id property="eid" column="eid"></id>
        <result property="empName" column="emp_name"></result>
        <result property="age" column="age"></result>
        <result property="sex" column="sex"></result>
        <result property="email" column="email"></result>
    </resultMap>

    <select id="queryEmp" resultMap="empMap">
        select * from emp where did=#{did}
    </select>

</mapper>

import mapper.DeptMapper;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import pojo.Dept;
import utils.SqlSessionUtils;

public class MapperTest {
    @Test
    public void testQueryDept(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
        Dept dept = mapper.queryDept("D1");
        System.out.println(dept);
    }
}

Yacht13

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MyBatis学习（7）—— 多表查询

一. 创建表员工表：CREATE TABLE `studymybatis`.`emp` ( `eid` INT NOT NULL AUTO_INCREMENT, `emp_name` VARCHAR(20), `age` INT, `sex` CHAR(1), `email` VARCHAR(20), `did` VARCHAR(20), PRIMARY KEY (`eid`));INSERT INTO `studymybatis`.`emp` (`emp_nam.
复制链接

扫一扫

专栏目录