Mybatis-多对一处理

多对一

对于学生而言，多个学生，关联对应一个老师【多对一】

对于老师而言，一个老师，关联对应多个学生【一对多】

SQL

create table teacher(
id int auto_increment primary key,
name char(20) not null,
unique(id)
)engine=innodb default charset=utf8;

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

CREATE TABLE student (
    id int auto_increment PRIMARY KEY,
    name CHAR(30) NOT NULL,
    tid INT NOT NULL,
    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);

测试环境搭建(mybatis-05)

1.导入lombok

2.新建实体类Teacher，Student

3.建立Mapper接口

4.建立Mapper.xml文件

5.在核心配置文件中绑定注册我们的Mapper接口或者文件！【方式很多】

6.测试查询是否能够成功！

按照查询嵌套处理

package com.kwok.pojo;

import lombok.Data;

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

    // 学生需要关联一个老师
    private Teacher teacher;
}
//查询所有的学生信息，以及对应的老师的信息
    public List<Student> getStudent();
<?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.kwok.dao.StudentMapper">

    <select id="getStudent" 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="getTeacherById"/>
    </resultMap>
    
    <select id="getTeacherById" resultType="Teacher">
        select * from teacher where id = #{id}
    </select>
</mapper>
	@Test
    public void getStudent(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = mapper.getStudent();
        for (Student student : studentList) {
            System.out.println(student);
        }
        sqlSession.close();
    }

按照结果嵌套查询

package com.kwok.pojo;

import lombok.Data;

@Data
public class Student {
    private int id;
    private String name; // 这个字段名称改的与数据库中字段不一样

    // 学生需要关联一个老师
    private Teacher teacher;
}
/*按照结果嵌套处理*/
    public List<Student> getStudent2();
<!--按照结果嵌套处理-->
    <select id="getStudent2" resultMap="StudentTeacher2">
        select s.id sid,s.name sname,t.id tid,t.name tname from student s,teacher t where s.tid = t.id
    </select>

    <resultMap id="StudentTeacher2" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="id" column="tid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>
@Test
    public void getStudent2(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = mapper.getStudent2();
        for (Student student : studentList) {
            System.out.println(student);
        }
        sqlSession.close();
    }

回顾Mysql多对一查询方式：

• 子查询
• 联表查询