mybatis---多表查询

多表查询

1. 多对一

1.1 联合查询

员工与部门的关系

根据员工的id 获取员工信息及其所在部门的信息

1.1.1 实体类的创建

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Department {
    private Integer id;
    private String name;
}

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Employee {
    private Integer id;
    private String name;
    private String sex;
    private Integer money;
    private Integer departmentId;

    /**
     * 员工与部门 多对一，创建对象
     */
    private Department department;
}

1.2. EmployeeMapper

/**
 * 根据id获取员工的信息及其所在部门信息
 * @param id 员工id
 * @return Employee
 */
Employee getEmployeeDataById(int id);

1.3. 映射文件

<select id="getEmployeeDataById" resultMap="EmployeeResultMap">
    SELECT e.*, d.* FROM `employee` e, `department` d
    WHERE e.e_department_id = d.d_id AND e.e_id = #{id}
</select>
<resultMap id="EmployeeResultMap" type="employee">
    <id column="e_id" property="id"/>
    <result column="e_name" property="name"/>
    <result column="e_sex" property="sex"/>
    <result column="e_money" property="money"/>
    <result column="e_department_id" property="departmentId"/>
    <association property="department" javaType="Department">
        <id column="d_id" property="id"/>
        <result column="d_name" property="name"/>
    </association>
</resultMap>

对Employee中复杂属性Department类对象的映射的处理

 <association property="department" javaType="Department">
        <id column="d_id" property="id"/>
        <result column="d_name" property="name"/>
 </association>

属性的含义：

• property: 在 Employee 类对象中的变量名称

• javaType: Department类对象的数据类型

1.2 分页查询

1 .2.1 Mapper

1. 2.1.1 EmployeeMapper

/**
 * 根据id获取员工的信息及其所在部门信息
 * @param id 员工id
 * @return Employee
 */
Employee getEmployeeDataById(int id);

1.2.1.2 DepartmentMapper

/**
 * 根据id获取部门信息
 * @param id 部门id
 * @return Department
 */
Department getDepartmentById(int id);

1.2.2 映射文件

1.2. 2.1 EmployeeMapper.xml

<!--   多对一 分页查询-->
    <select id="getEmployeeDataById" resultMap="EmployeeResultMap">
        SELECT * FROM `employee` WHERE e_id = #{id}
    </select>
    <resultMap id="EmployeeResultMap" type="employee">
        <id column="e_id" property="id"/>
        <result column="e_name" property="name"/>
        <result column="e_sex" property="sex"/>
        <result column="e_money" property="money"/>
        <result column="e_department_id" property="departmentId"/>
        <association property="department" javaType="department" column="e_department_id"
                     select="com.wy.mapper.DepartmentMapper.getDepartmentById"/>
    </resultMap>

由于是分页查询，所以当前结果集下并没有 department表的数据，因此需要再去调用department表的查询方法

<association property="department" javaType="department" column="e_department_id"
                     select="com.wy.mapper.DepartmentMapper.getDepartmentById"/>

参数分析：

• column ： 当前结果的哪一列作为调用查询所需要的参数
• select： 调用的查询方法的定位

1.2.2.2 DepartmentMapper.xml

<select id="getDepartmentById" resultMap="DepartmentResultMap">
    SELECT * FROM department WHERE d_id = #{id}
</select>

<resultMap id="DepartmentResultMap" type="department">
    <id column="d_id" property="id"/>
    <result column="d_name" property="name"/>
</resultMap>

2. 一对多

2.1（联合查询）

部门对员工的关系

查询某部门中所有员工及其信息

2.1.1. 实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Employee {
    private Integer id;
    private String name;
    private String sex;
    private Integer money;
    private Integer departmentId;
}

public class Department {
    private Integer id;
    private String name;

    /**
     * 部门对类的关系 是一对多 创建Employee集合
     */
    private List<Employee> employeeList;
}

2.1.2. mapper

/**
 * 查询某个部门下的所有员工信息
 * @param id 部门id
 * @return Department
 */
Department getDepartmentIncludeEmployeeById(int id);

2.1.3. 映射文件

<!-- 一对多的联合查询-->
    <select id="getDepartmentIncludeEmployeeById" resultMap="DepartmentIncludeEmployeeResultMap">
        SELECT e.*, d.* FROM `employee` e, `department` d WHERE e.e_department_id = d.d_id AND d.d_id = #{id}
    </select>

    <resultMap id="DepartmentIncludeEmployeeResultMap" type="department">
        <id column="d_id" property="id"/>
        <result column="d_name" property="name"/>
        <collection property="employeeList" ofType="employee">
            <id column="e_id" property="id"/>
            <result column="e_name" property="name"/>
            <result column="e_sex" property="sex"/>
            <result column="e_money" property="money"/>
            <result column="e_department_id" property="departmentId"/>
        </collection>
    </resultMap>

2.2 分页查询

2.2.1. DepartmentMapper.xml

<!--    一对多的分页查询-->
    <select id="getDepartmentIncludeEmployeeById" resultMap="DepartmentIncludeEmployeeResultMap2">
        SELECT * FROM department WHERE d_id = #{id}
    </select>

    <resultMap id="DepartmentIncludeEmployeeResultMap2" type="department">
        <id column="d_id" property="id"/>
        <result column="d_name" property="name"/>
        <collection property="employeeList" ofType="employee" column="d_id" 		select="com.wy.mapper.EmployeeMapper.getEmployeeDataByDepartmentId">
        </collection>
    </resultMap>

2.2.2. Employee表的处理

/**
 * 根据部门id查询员工信息
 * @param id 部门id
 * @return Employee
 */
Employee getEmployeeDataByDepartmentId(int id);

    <select id="getEmployeeDataByDepartmentId" resultMap="EmployeeResultMap">
        SELECT * FROM `employee` WHERE e_department_id = #{id}
    </select>


    <resultMap id="EmployeeResultMap" type="employee">
        <id column="e_id" property="id"/>
        <result column="e_name" property="name"/>
        <result column="e_sex" property="sex"/>
        <result column="e_money" property="money"/>
        <result column="e_department_id" property="departmentId"/>
    </resultMap>

3. 一对一

3.1 联合查询

居民与身份证的关系

根据居民id获取居民信息

3.1.1. 实体类准备

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Person {
    private Integer id;
    private String name;

    private Card card;
}

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Card {
    private Integer id;
    private String name;
    private String sex;
    private String number;
    private Integer personId;
}

3.1.2. PersonMapper

/**
 * 根据id获取居民信息
 * @param id 居民id
 * @return Person
 */
Person getPersonDataById(int id);

3.1.3. 注册文件

<!-- 联合查询-->
    <select id="getPersonDataById" resultMap="PersonDataResultMap">
        SELECT p.*, c.* FROM person p, card c WHERE c.c_person_pid = p.p_id AND p.p_id = #{id}
    </select>
    <resultMap id="PersonDataResultMap" type="person">
        <id column="p_id" property="id"/>
        <result column="p_name" property="name"/>
        <association property="card" javaType="card">
            <id column="c_id" property="id"/>
            <result column="c_name" property="name"/>
            <result column="c_sex" property="sex"/>
            <result column="c_number" property="number"/>
            <result column="c_person_pid" property="personId"/>
        </association>
    </resultMap>

3.2 分页查询

3.2.1. 对person表的查询

<select id="getPersonDataById" resultMap="PersonDataResultMap2">
    SELECT * FROM person WHERE p_id = #{id}
</select>
  <resultMap id="PersonDataResultMap2" type="person">
        <id column="p_id" property="id"/>
        <result column="p_name" property="name"/>
        <association property="card" javaType="card" column="p_id" select="com.wy.mapper.CardMapper.getCardDateByPId">
        </association>
    </resultMap>

3.2.2. 对card表的查询

/**
 * 根据居民id获取身份证信息
 * @param id 居民id
 * @return Card
 */
Card getCardDateByPId(int id);

<select id="getCardDateByPId" resultMap="CardDateResultMap">
    SELECT * FROM `card` WHERE c_person_pid = #{id}
</select>
<resultMap id="CardDateResultMap" type="Card">
    <id column="c_id" property="id"/>
    <result column="c_name" property="name"/>
    <result column="c_sex" property="sex"/>
    <result column="c_number" property="number"/>
    <result column="c_person_pid" property="personId"/>
</resultMap>

4 多对多

4.1. 联合查询

老师和学生的关系： 一个学生有多个老师，一个老师有多个学生

需要构建中间表

4.1.1 实体类的准备

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    private Integer id;
    private String name;

    /**
     * student 到中间表 是 1对多的关系
     */
    private List<StudentTeacher> studentTeacherList;
}

@Data
@AllArgsConstructor
@NoArgsConstructor
public class StudentTeacher {
    private Integer sId;
    private Integer tId;

    /**
     * 中间表到student, teacher 都是多对一
     */
    private Student student;
    private Teacher teacher;

}

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
    private Integer id;
    private String name;

    /**
     * teacher 到中间表 是 1对多的关系
     */
//    private List<StudentTeacher> studentTeacherList;
}

4.1.2. StudentMapper

    /**
     * 根据学生查询学生信息以及该学生的老师信息
     * @param id
     * @return
     */
    Student getDataById(int id);

4.1.3. 注册文件

<select id="getDataById" resultMap="DataResultMap">
    SELECT s.*, t.*
    FROM student s
             JOIN `student_teacher` st ON st.st_tid = s.s_id
             JOIN teacher t ON st.st_sid = t.t_id
    WHERE s.s_id = 1

</select>


<resultMap id="DataResultMap" type="student">
    <id column="s_id" property="id"/>
    <result column="s_name" property="name"/>
    <collection property="studentTeacherList" ofType="studentTeacher">
        <association property="teacher" javaType="teacher">
            <id column="t_id" property="id"/>
            <result column="t_name" property="name"/>
        </association>
    </collection>
</resultMap>

4.2分页查询

4.2.1. 对student表的查询

<select id="getDataById" resultMap="DataResultMap2">
    SELECT * FROM student WHERE s_id = #{id}
</select>

<resultMap id="DataResultMap2" type="student">
    <id column="s_id" property="id"/>
    <result column="s_name" property="name"/>
    <collection property="studentTeacherList" ofType="studentTeacher" column="s_id" select="com.wy.mapper.StudentTeacherMapper.getDataById"></collection>
</resultMap>

4.2.2. 对中间表的查询

/**
 * @param id
 * @return
 */
StudentTeacher getDataById(int id);

<select id="getDataById" resultMap="getDataByIdResultMap">
    SELECT * FROM `student_teacher` WHERE st_sid = #{id}
</select>

<resultMap id="getDataByIdResultMap" type="studentTeacher">
    <association property="teacher" javaType="teacher" column="st_tid" select="com.wy.mapper.TeacherMapper.getData">

    </association>
</resultMap>

4.2.3. 对teacher表的查询

/**
 * 
 * @param id
 * @return
 */
Teacher getData(int id);

<select id="getData" resultMap="getDataResultMap">
    SELECT * FROM teacher WHERE t_id = #{id}
</select>

<resultMap id="getDataResultMap" type="teacher">
    <id column="t_id" property="id"/>
    <result column="t_name" property="name"/>
</resultMap>