多个学生, 对应一个老师
对于学生而言, 多个学生, 关联一个老师 [多对一]
对于老师而言, 一个老师有很多学生 [一对多]

复杂查询环境搭建

创建teacher 表:

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

INSERT INTO teacher (`id`, `name`) VALUES (1, 'lxb');

创建student 表:

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 COLLATE=utf8_general_ci;

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');

新建一个Maven项目, 设置好核心配置文件, 新建teacher和student类

然后老师对应一个Mapper接口, 学生对应一个Mapper接口

多对一处理

查询所有的学生信息, 以及对应的老师的信息

查询所使用的SQL语句:

1	select s.id, s.name, t.name from student s , teacher t where s.tid = t.id;

编写接口中的方法
1
public List<Student> getStudents();

在Mapper中编写SQL语句

1
2
3

<select id="getStudents" resultType="Student">
    select * from student s , teacher t  where s.tid = t.id;
</select>

测试输出

@Test
public void test1(){
    SqlSession sqlSession = MybtisUtils.getSqlSession();
   
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    List<Student> students = mapper.getStudents();
    for (Student student : students) {
        System.out.println(student);
    }
   
    sqlSession.close();
}

可以看到无法正确输出老师的信息

按照查询嵌套处理

相当于子查询

查询所有的学生信息

1
2
3

<select id="getStudents" resultType="Student">
    select * from mybatis.student ;
</select>

根据查询出来的学生的tid, 寻找对应的老师

1
2
3

<select id="getTeacher" resultType="Teacher">
    select * from mybatis.teacher where id=#{id};
</select>

接下来的工作只是把这两个语句联系起来

使用resultMap来进行关联

<select id="getStudents" resultMap="StudentTeacher">
    select * from mybatis.student ;
</select>

<resultMap id="StudentTeacher" type="Student">
    <result column="id" property="id"/>
    <result column="name" property="name"/>
    <association column="tid" property="teacher" javaType="Teacher" 			   select="getTeacher"/>
</resultMap>

<select id="getTeacher" resultType="Teacher">
    select * from mybatis.teacher where id=#{id};
</select>

意思就是把student表中的tid属性绑定上一个Java对象, 这个对象的类型是Teacher , 并且是由getTeacher方法查询出来的

结果为：

按照结果嵌套处理

相当于联表查询

新创建一个方法:
1
List<Student> getStudents2();

在Mapper中进行绑定

<select id="getStudent2" resultMap="StudentTeacher2">
    select s.id sid, s.name sname, t.name tname
    from mybatis.student s,mybatis.teacher t
    where s.tid = t.id;
</select>

<resultMap id="StudentTeacher2" type="Student">
    <result column="id" property="sid"/>
    <result column="name" property="sname"/>
    <association column="tid" property="teacher" javaType="Teacher">
        <result column="tname" property="name"/>
    </association>
</resultMap>

这里没有将SQL语句进行嵌套, 而是直接把每个对应的属性写死

测试与输出

@Test
public void test3(){
    SqlSession sqlSession = MybtisUtils.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    List<Student> studentList = mapper.getStudents2();
    for (Student student : studentList) {
        System.out.println(student);
    }
    sqlSession.close();
}

一对多处理

比如一个老师拥有多个学生

对于老师而言就是一对多的关系

环境搭建

实体类修改, 因为主体变成老师, 所以每个学生的属性中的Teacher对象换为老师的id即可, 然后每个老师要有一个学生列表的属性

public class Student {
    private int id;
    private String name;

    // 学生需要关联一个老师
    private int tid;

public class Teacher {
    private int id;
    private String name;

    private List<Student> students;

编写接口函数
1
Teacher getTeacher(@Param("tid") int id);
功能为根据老师的id号, 查询该老师信息以及他所有的学生

Mapper映射

<resultMap id="TeacherStudent" type="Teacher">
    <result property="id" column="tid"/>
    <result property="name" column="tname"/>
    <collection property="students" ofType="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <result property="tid" column="tid"/>
    </collection>
</resultMap>

<select id="getTeacher" resultMap="TeacherStudent">
    select s.id sid, s.name sname, t.name tname, t.id tid
    from mybatis.teacher t,
    mybatis.student s
    where s.tid = t.id
    and t.id = #{tid};
</select>

要注意, 因为其中一个属性是集合, 所以要用关键字collection, 并且要找出集合中的对象类型, 用关键字ofType

@Test
public void test02() {
    SqlSession sqlSession = MybtisUtils.getSqlSession();

    TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
    Teacher teacher = mapper.getTeacher(1);
    System.out.println(teacher);

    sqlSession.close();
}

结果为