MyBatis多对一的两种查询处理方法

MyBatis一对多的两种处理方法

简单的来说就是一个学生对多个老师：
然后说一下需求：
需求：获取所有学生及对应老师的信息
这是我的数据库关系：
这是我的sql语句

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

表建完了下面给大家看下我的pojo类：

package com.zsp.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;


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

package com.zsp.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

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

接下去我们说方法把：
第一种方法：按照查询嵌套处理，类似于子查询

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zsp.dao.StudentMapper">
<!--       思路:-->
<!--                1．查询所有的学生信息-->
<!--                2．根据查询出来的学生的tid，寻找对应的老师-&#45;&#45;子查询-->
    <select id="getStudents" resultMap="StudentTeacher">
        select * from student;
    </select>
    <resultMap id="StudentTeacher" type="student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <!--复杂的属性，我们需要单独处理对象:association集合: collection -->
        <association property="teacher" column="tid" javaType="teacher" select="getTeacher">
        </association>
    </resultMap>
    <select id="getTeacher" resultType="teacher">
        select * from teacher where id =#{id};
    </select>



  
</mapper>

第二种方法： 按结果嵌套查询，这个有点类似于我们数据库里的连接查询

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zsp.dao.StudentMapper">




    <!--    按结果嵌套处理-->
    <select id="getStudents2" resultMap="StudentTeacher2">
        select s.id sid,s.name sname, t.name tname ,t.id tid from student s ,teacher t where s.tid=t.id;
    </select>
    <resultMap id="StudentTeacher2" type="student">
<!--        和查询语句里的属性一一对应-->
    <result property="id" column="sid"></result>
    <result property="name" column="sname"></result>
    <association property="teacher" javaType="teacher">
<!--        老师表里的name和查询语句里的tname对应-->
        <result property="name" column="tname"></result>
        <result property="id" column="tid"></result>
    </association>
    </resultMap>
</mapper>

测试类

import com.zsp.dao.StudentMapper;
import com.zsp.dao.TeacherMapper;

import com.zsp.pojo.Student;
import com.zsp.pojo.Teacher;
import com.zsp.utils.MybatiesUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class TestDemo {
    @Test
    public void testGetTeacherById(){
        SqlSession session = MybatiesUtils.getSession();
        TeacherMapper mapper = session.getMapper(TeacherMapper.class);
        Teacher teacherById = mapper.getTeacherById(1);
        System.out.println(teacherById);
        session.close();
    }
    @Test
    public void testgetStudents(){
        SqlSession session = MybatiesUtils.getSession();
        StudentMapper mapper = session.getMapper(StudentMapper.class);
        List<Student> students = mapper.getStudents();
        for (Student student : students) {
            System.out.println(student);
        }
    }
    @Test
    public void testgetStudents2(){
        SqlSession session = MybatiesUtils.getSession();
        StudentMapper mapper = session.getMapper(StudentMapper.class);
        List<Student> students = mapper.getStudents2();
        for (Student student : students) {
            System.out.println(student);
        }
    }

}