6 复杂查询

多对一处理

环境搭建

创建数据库

CREATE TABLE `teacher` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO teacher(`id`, `name`) VALUES (1, '秦老师'); 

CREATE TABLE `student` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  `tid` INT(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fktid` (`tid`),
  CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8


INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小明', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小红', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '小张', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '小王', '1');

导入Lombok

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.12</version>
</dependency>

新建实体类

学生类:

package com.qin.pojo;

import lombok.Data;

@Data
public class Student {
    private int id;
    private String name;
    private Teacher teacher;
}

老师类:

package com.qin.pojo;

import lombok.Data;

@Data
public class Teacher {
    private int id;
    private String name;
}

建立Mapper接口

package com.qin.dao;

import com.qin.pojo.Student;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface StudentMapper {
    public List<Student> getStudentList();
}

在mybatis-config.xml中绑定mapper接口或文件

<mappers>
    <package name="com.qin.dao"/>
</mappers>

按照查询嵌套处理

复杂的对象需要单独处理
如果是对象使用:association
如果是集合使用:collection

<!--
    思路:
        1. 查询所有的学生信息
        2. 根据查询出来的学生的tid，寻找对应的老师！  子查询
-->
<?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.qin.dao.StudentMapper">
    <select id="getStudentList" resultMap="MyStudent">
        select * from student
    </select>
    <resultMap id="MyStudent" type="student">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <!--
            association property="属性名" colum="数据库字段名" javaType="对象类型" select="子查询名"
        -->
        <association property="tid" column="tid" javaType="teacher" select="getTeacherById"/>
    </resultMap>
    <select id="getTeacherById" resultType="teacher">
        select * from teacher where id = #{id}
    </select>
</mapper>

• 第一个select查询到了student表的id,name,tid
• 结果集映射到Student实体类id->id name->name
• 实体类的tid是一个Teacher类 查询到的tid是一个int类型的字段
• 可以根据这个int类型的字段查到teacher表
• 并根据查到的teacher对Teacher类的属性一一对应

按照结果嵌套处理

<select id="getStudentList2" resultMap="MyStudent2">
    select s.id as sid,s.name as sname,t.id as tid,t.name as tname
    from mybatis.student s
    join mybatis.teacher t
    on s.tid = t.id
</select>
<resultMap id="MyStudent2" type="student">
    <id property="id" column="sid"/>
    <result property="name" column="sname"/>
    <association property="tid" javaType="teacher">
        <id property="id" column="tid"/>
        <result property="name" column="tname"/>
    </association>
</resultMap>

• 将查到的所有结果进行命名
• 结果集里将属性和字段一一对应
• tid属性是一个teacher类,进行再对应

一对多处理

修改实体类的属性

package com.qin.pojo;

import lombok.Data;

import java.util.List;

@Data
public class Teacher {
    private int id;
    private String name;
    private List<Student> students;
}

package com.qin.pojo;

import lombok.Data;

@Data
public class Student {
    private int id;
    private String name;
    private int tid;
}

按照结果嵌套处理

package com.qin.dao;


import com.qin.pojo.Teacher;

import java.util.List;

public interface TeacherMapper {
    public List<Teacher> getTeacherList();
}

<?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.qin.dao.TeacherMapper">
    <select id="getTeacherList" resultMap="getTeacher1">
        select t.id as tid,t.name as tname,s.id as sid,s.name as sname
        from mybatis.teacher t
        join mybatis.student s
        on t.id = s.tid
    </select>
    <resultMap id="getTeacher1" type="teacher">
        <id property="id" column="tid"/>
        <result property="name" column="tname"/>
        <collection property="students" ofType="student">
            <id property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>
</mapper>

按照查询嵌套处理

<select id="getTeacherList2" resultMap="getTeacher2">
    select * from mybatis.teacher
</select>
<resultMap id="getTeacher2" type="teacher">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <collection property="students" javaType="ArrayList" ofType="student" column="id" select="getStudent"/>
</resultMap>
<select id="getStudent" resultType="student">
    select * from mybatis.student where tid=#{id}
</select>

小结

1. association多对一:多个学生对应一个老师,使查询到的学生关联一个老师
2. collection一对多:一个老师含有多个学生,将老师属性里的学生集合查询出来