Mybatis05-一对多和多对一处理

多对一和一对多

多对一

多对一的理解:

  • 多个学生对应一个老师

  • 如果对于学生这边,就是一个多对一的现象,即从学生这边关联一个老师!

结果映射(resultMap):

  • association

    • 一个复杂类型的关联;许多结果将包装成这种类型
    • 嵌套结果映射 —— 关联可以是 resultMap 元素,或是对其它结果映射的引用
  • collection

    • 一个复杂类型的集合
    • 嵌套结果映射 —— 集合可以是 resultMap 元素,或是对其它结果映射的引用

以下使用两种方式实现以下sql语句:

select s.id ,s.name ,t.name from student s,teacher t where s.tid=t.id
1、按照查询嵌套处理

类似子查询

  1. 数据库设计

在这里插入图片描述

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. 实体类STudent和Teacher
public class Teacher {
    private int id;
    private String name;

    public 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;
    }

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

    public String getName() {
        return name;
    }

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

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

    public Student(){

    }

    public int getId() {
        return id;
    }

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

    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 +
                '}';
    }
}
  1. StudentMapper接口和TeacherMapper
package com.study.dao;
import com.study.pojo.Student;
import java.util.List;

public interface StudentMapper {

    //查询所有的学生信息,以及对应的老师的信息
    public List<Student> getStudent();
}
public interface TeacherMapper {

}
  1. StudentMapper.xml映射文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace绑定一个对应的Dao/Mapper接口的全限定名-->
<mapper namespace="com.study.dao.StudentMapper">
    <resultMap id="StudentTeacher" type="Student">
        <!--主键可使用<id>-->
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <!--复杂的属性,需要单独处理——引用类型:<association>  集合:<collection>-->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>


    <select id="getStudent" resultMap="StudentTeacher">
        select * from student
    </select>

    <select id="getTeacher" resultType="Teacher">
        select * from teacher where id=#{id}
    </select>
</mapper>
  1. 核心配置文件
<mappers>
    <mapper class="com.study.dao.TeacherMapper"/>
    <mapper class="com.study.dao.StudentMapper"/>
</mappers>
  1. 测试
@Test
public void testStudent(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    List<Student> studentList = mapper.getStudent();//动态代理产生一个实现Mapper接口的对象并赋值个,将该对象赋值给接口的引用
    for (Student student : studentList) {
        System.out.println(student);
    }
    sqlSession.close();
}
2、按结果嵌套处理
  1. StudentMapper接口

    public List<Student> getStudent2();
    
  2. StudentMapper.xml

    <!--===========按照结果嵌套查询==============-->
    
    <!--
            按查询结果嵌套处理
            思路:
            1. 直接查询出结果,进行结果集的映射
        -->
    <select id="getStudent2" resultMap="StudentTeacher2">
        select s.id sid,s.name sname,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="name" column="tname"/>
        </association>
    </resultMap>
    
    
  3. 测试

    @Test
    public void testStudent(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = mapper.getStudent2();//动态代理产生一个实现Mapper接口的对象并赋值个,将该对象赋值给接口的引用
        for (Student student : studentList) {
            System.out.println(student);
        }
        sqlSession.close();
    }
    
    

小结

  • 按照查询进行嵌套处理就像SQL中的子查询
  • 按照结果进行嵌套处理就像SQL中的联表查询
一对多
  1. 实体类

    package com.study.pojo;
    
    public class Student {
        private int id;
        public String getName() {
            return name;
        }
        public void setName(String name) {
            this.name = name;
        }
    
        private String name;
    
        public int getTid() {
            return tid;
        }
    
        public void setTid(int tid) {
            this.tid = tid;
        }
    
        private int tid;
    
        public Student(){
    
        }
    
        public int getId() {
            return id;
        }
    
        public void setId(int id) {
            this.id = id;
        }
    
        @Override
        public String toString() {
            return "Student{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", tid=" + tid +
                    '}';
        }
    }
    
    package com.study.pojo;
    
    import java.util.List;
    
    public class Teacher {
        private int id;
        private String name;
        private List<Student> students;//一个老师拥有多个学生
    
    
        public List<Student> getStudents() {
            return students;
        }
    
        public void setStudents(List<Student> students) {
            this.students = students;
        }
    
        public 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;
        }
    
        @Override
        public String toString() {
            return "Teacher{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", students=" + students +
                    '}';
        }
    }
    
  2. TeacherMapper接口

    List<Teacher> getTeacher();
    
  3. TeacherMapper.xml

    <select id="getTeacher" resultType="Teacher">
        select * from teacher;
    </select>
    
  4. 核心配置文件中注册Mapper

  5. 测试环境正常

    @Test
    public void test(){
    
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        List<Teacher> teacherList = mapper.getTeacher();
        for (Teacher teacher : teacherList) {
            System.out.println(teacher);
        }
        sqlSession.close();
    }
    
    1、按结果嵌套查询
    1. TeacherMapper接口`

      Teacher getTeacherById(@Param("tid")int id);
      
    2. TeaccherMapper.xml——按结果嵌套查询

      <!--==================按结果嵌套查询================-->
      <select id="getTeacherById" resultMap="TeacherStudent">
          select t.name tname,t.id tid,s.id sid,s.name sname
          from student s,teacher t
          where s.tid=t.id and t.id=#{tid}
      </select>
      
      <resultMap id="TeacherStudent" type="Teacher">
          <result property="id" column="tid"/>
          <result property="name" column="tname"/>
          <!--复杂的属性,需要单独处理——
                  引用类型:<association>  javaType:指定的属性的类型
                  集合:<collection>   集合中的泛型信息,我们使用ofType获取
                -->
          <!--Teacher实体类中有一个名为students的引用类型的List集合,将List中Student对象的各属性与sql语句返回的字段进行映射-->
          <collection property="students" ofType="Student">
              <result property="id" column="sid"/>
              <result property="name" column="sname"/>
              <result property="tid" column="tid"/>
          </collection>
      </resultMap>
      
    3. 测试

      @Test
      public void getTeacherById(){
      
          SqlSession sqlSession = MybatisUtils.getSqlSession();
          TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
          Teacher teacher = mapper.getTeacherById(1);
          System.out.println(teacher);
          sqlSession.close();
      }
      
      输出:
      Opening JDBC Connection
      Created connection 1278254413.
      ==>  Preparing: 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=?
      ==> Parameters: 1(Integer)
      <==    Columns: sid, sname, tname, tid
      <==        Row: 1, 小明, 秦老师, 1
      <==        Row: 2, 小红, 秦老师, 1
      <==        Row: 3, 小张, 秦老师, 1
      <==        Row: 4, 小李, 秦老师, 1
      <==        Row: 5, 小王, 秦老师, 1
      <==      Total: 5
      Teacher{id=1, name='秦老师', students=[Student{id=1, name='小明', tid=1}, Student{id=2, name='小红', tid=1}, Student{id=3, name='小张', tid=1}, Student{id=4, name='小李', tid=1}, Student{id=5, name='小王', tid=1}]}
      Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4c309d4d]
      Returned connection 1278254413 to pool.
      
    2、按照查询嵌套处理
    1. TeacherMapper

      Teacher getTeacherById2(@Param("tid")int id);
      
    2. TeacherMapper.xml

      <!--===========按照查询嵌套处理================-->
      <select id="getTeacherById2" resultMap="TeacherStudent2">
          select * from mybatis.teacher where id=#{tid}
      </select>
      
      <resultMap id="TeacherStudent2" type="Teacher">
          <collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id"/><!--这里的column="id"是teacher表中的id-->
      </resultMap>
      
      <select id="getStudentByTeacherId" resultType="Student">
          <!--这里的id是上文中的id-->
          select * from student where tid=#{id}
      </select>
      
      
    3. 测试

          @Test
          public void getTeacherById2() {
      
              SqlSession sqlSession = MybatisUtils.getSqlSession();
              TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
              Teacher teacher = mapper.getTeacherById2(1);
              System.out.println(teacher);
              sqlSession.close();
          }
      
      
      输出:
      Opening JDBC Connection
      Created connection 931675031.
      ==>  Preparing: select * from mybatis.teacher where id=?
      ==> Parameters: 1(Integer)
      <==    Columns: id, name
      <==        Row: 1, 秦老师
      ====>  Preparing: select * from student where tid=?
      ====> Parameters: 1(Integer)
      <====    Columns: id, name, tid
      <====        Row: 1, 小明, 1
      <====        Row: 2, 小红, 1
      <====        Row: 3, 小张, 1
      <====        Row: 4, 小李, 1
      <====        Row: 5, 小王, 1
      <====      Total: 5
      <==      Total: 1
      Teacher{id=0, name='秦老师', students=[Student{id=1, name='小明', tid=1}, Student{id=2, name='小红', tid=1}, Student{id=3, name='小张', tid=1}, Student{id=4, name='小李', tid=1}, Student{id=5, name='小王', tid=1}]}
      Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@37883b97]
      Returned connection 931675031 to pool.
      

小结

  • 关联:association【多对一】

  • 集合:collection 【一对多】

  • association是用于一对一和多对一,而collection是用于一对多的关系

  • javaType & ofType

    • JavaType是用来指定pojo中属性的类型
    • ofType指定的是映射到list集合属性中pojo的类型。

注意说明:

1、保证SQL的可读性,尽量通俗易懂

2、根据实际要求,尽量编写性能更高的SQL语句

3、注意属性名和字段不一致的问题

4、注意一对多和多对一 中:字段和属性对应的问题

5、尽量使用Log4j,通过日志来查看自己的错误

面试高频:

  • Mysql引擎

  • InnoDB底层原理

  • 索引

d=4, name=‘小李’, tid=1}, Student{id=5, name=‘小王’, tid=1}]}
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@37883b97]
Returned connection 931675031 to pool.
```

小结

  • 关联:association【多对一】

  • 集合:collection 【一对多】

  • association是用于一对一和多对一,而collection是用于一对多的关系

  • javaType & ofType

    • JavaType是用来指定pojo中属性的类型
    • ofType指定的是映射到list集合属性中pojo的类型。

注意说明:

1、保证SQL的可读性,尽量通俗易懂

2、根据实际要求,尽量编写性能更高的SQL语句

3、注意属性名和字段不一致的问题

4、注意一对多和多对一 中:字段和属性对应的问题

5、尽量使用Log4j,通过日志来查看自己的错误

面试高频:

  • Mysql引擎

  • InnoDB底层原理

  • 索引

  • 索引优化

  • 34
    点赞
  • 22
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值