mybatis学习 多对一 一对多解决方案

十、多对一

• 多个学生对应一个老师
• 对于学生而言，**关联 ** 多个学生关联一个老师【多对一】
• 对于老师而言，集合 一个老师，有多个学生 【一对多】

SQL：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pva5TaMy-1608274462897)(image-20201218102736811.png)]

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

测试环境搭建

1、导入lombok

<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.16</version>
    <scope>provided</scope>
</dependency>

2、新建实体类Teacher Student

@Data
public class Student {

    private int id;
    private String name;

    //学生需要关联一个老师
    private Teacher teacher;

}

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

3、建立Mapper接口

package com.zhang.dao;

public interface StudentMapper {
}

public interface TeacherMapper {
    @Select("select * from teacher where id = #{tid}")
    Teacher getTeacher(@Param("tid") int id);
}

4、建立Mapper.xml文件

<?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.zhang.dao.TeacherMapper">



</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.zhang.dao.StudentMapper">



</mapper>

5、在核心配置文件中绑定注册Mapper.xml文件

<mappers>
    <mapper class="com.zhang.dao.TeacherMapper"/>
    <mapper class="com.zhang.dao.StudentMapper"/>
</mappers>

6、测试

@Test
public void Mytest(){

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


    sqlSession.close();
}

按照查询嵌套处理

<mapper namespace="com.zhang.dao.StudentMapper">

    <resultMap id="studentMap" type="student">
        <!--复杂的属性  需要单独处理
        对象使用：association
        集合使用：collection

        -->
    <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>

    </resultMap>

    <select id="getStudentList" resultMap="studentMap">
        select * from student;
    </select>

    <select id="getTeacher" resultType="teacher">
        select * from  teacher where id = #{id}
    </select>
</mapper>

按照结果嵌套处理

<resultMap id="studentMap2" type="student">
    <!--起了别名后column对应的为别名-->
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
   <association property="teacher" javaType="Teacher">
       <result property="name" column="tname"/>
   </association>
</resultMap>

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

11、一对多

一个老师拥有多个学生

1、环境搭建

实体类修改

@Data
public class Teacher {
    private int id;
    private String name;
    //一个老师拥有多名学生
    private List<Student> student;
}

@Data
public class Student {

    private int id;
    private String name;

    private int tid;

}

<resultMap id="teacherMap" type="Teacher">
    <result property="id" column="tid"/>
    <result property="name" column="tname"/>
    <!--复杂的属性  对象：association  集合：collection

    javaType=""指定属性的类型
    集合中的泛型信息，使用ofType获取
    -->
    <collection property="students" ofType="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <result property="tid" column="tid"/>
    </collection>
</resultMap>
<select id="getTeacher2" resultMap="teacherMap">
    select s.id sid ,s.name sname,t.name tname,t.id tid
    from student s ,teacher t
    where s.tid = t.id and t.id = #{tid};
</select>

总结：

1、关联—–association 【多对一】

2、集合—–collection 【一对多】

3、javaType & ofType

1. javaType 用来指定实体类中属性的类型
2. ofType 用来指定映射到List 或者集合中的POJO类型，泛型中的约束类型

注意点：

1. SQL的可读性
2. 注意一对多和多对一中，属性名和字段的问题
3. 若问题不好排查，建议使用日志