多对一

举个例子：多个学生，对应一个老师

一、复杂查询环境搭建

# 创建老师表并插入数据
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'),
('2', '小红', '1'),
('3', '小张', '1'),
('4', '小李', '1'),
('5', '小王', '1');

二、配置环境

//实体类，学生
public class Student {
    private int id;
    private String name;
    private Teacher teacher;

    public Student() {}

    public Student(int id, String name, Teacher teacher) {
        this.id = id;
        this.name = name;
        this.teacher = teacher;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Teacher getTeacher() {
        return teacher;
    }

    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", teacher=" + teacher +
                '}';
    }
}

//实体类，老师
public class Teacher {
    private int id;
    private String name;

    public Teacher() {}

    public Teacher(int id, String name) {
        this.id = id;
        this.name = name;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return "Teacher{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}

//StudentMapper接口
public interface StudentMapper {
    //获取全部的学生
    List<Student> getAllStudents();
}

//TeacherMapper接口
public interface TeacherMapper {
}

<?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.comprehensive.dao.StudentMapper">

    <!-- 获取全部的学生 -->
    <select id="getAllStudents" resultType="com.comprehensive.pojo.Student">
        select * from mybatis.student
    </select>

</mapper>

public class Test_ManyToOne {
    @Test
    //查询全部学生
    public void test() {
        SqlSession sqlSession = MyBatis_Util.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        for(Student student : mapper.getAllStudents()) System.out.println(student);
        sqlSession.close();
    }
}

发现问题：从结果上来看，Student类中的private Teacher teacher;属性未被赋值

解决问题：多对一实现

三、多对一实现

按照查询嵌套处理（子查询）

• 需求分析

  select s.id as s_id, s.name as s_name, s.tid as s_tid
  from mybatis.student s
  where s.tid=(select id from mybatis.teacher)
  

  //StudentMapper接口
  public interface StudentMapper {
    //多对一：
    //方式一：按照查询嵌套处理
    List<Student> getAllStudentsWithTeachers();
  }
  

  ```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">
  

  select s.id as s_id, s.name as s_name, s.tid as s_tid from mybatis.student s select * from mybatis.teacher where id=#{s_tid}

```java
//测试类
public class Test_ManyToOne {
    @Test
    //方式一：按照查询嵌套处理
    public void test1() {
        SqlSession sqlSession = MyBatis_Util.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> students = mapper.getAllStudentsWithTeachers();
        for (Student stu : students) {
            System.out.println(stu);
        }
        sqlSession.close();
    }
}

按照结果嵌套处理（联表查询）

• 需求分析（SQL实现）

  select s.id as s_id, s.name as s_name, s.tid as s_tid, t.id as t_id, t.name as t_name
  from mybatis.student s, mybatis.teacher t
  where s.tid=t.id;
  

  //StudentMapper接口
  public interface StudentMapper {
    //多对一：
    //方式二：按照结果嵌套处理
    List<Student> getAllStudentsWithTeachers2();
  }
  

  ```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">
  

  select s.id as s_id, s.name as s_name, s.tid as s_tid, t.id as t_id, t.name as t_name from mybatis.student s, mybatis.teacher t where s.tid=t.id

```java
//测试类
public class Test_ManyToOne {
    @Test
    //方式二：按照结果嵌套处理
    public void test2() {
        SqlSession sqlSession = MyBatis_Util.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> students = mapper.getAllStudentsWithTeachers2();
        for(Student stu : students) {
            System.out.println(stu);
        }
        sqlSession.close();
    }
}