多表操作

一对多/多对一查询方式:

  1. 子查询
  2. 联表查询

要求:通过学生查询老师和学生的信息。

1. 准备工作

  1. 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' );
    
    image-20210911182542746
    Image

1.1. 准备工作

  1. pojo

    package org.gs.pojo;
    
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    
    /**
     * @author admin
     * @date 2021/9/11 4:34 下午
     */
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public class Student {
        private int id;
        private String name;
        private Teacher teacher;    // 多对一的查询加上
    }
    
    package org.gs.pojo;
    
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    
    /**
     * @author admin
     * @date 2021/9/11 4:33 下午
     */
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public class Teacher {
        private int id;
        private String name;
          private List<Student> students;    // 一对多的查询加上
    }
    

2. 多对一查询

  1. 子查询(按照查询嵌套处理)

    思路:

    1. 查询所有的学生信息
    2. 根据查询出来的学生id,寻找对应的老师
    <resultMap id="StudentAndTeacher" type="Student">
      <!--能自动映射的可以不配置-->
      <!--javaType指定子查询映射的pojo对象-->
      <!--property需要封装的对象变量名-->
      <!--column子查询调用的前面查询的参数的字段名-->
      <!--下面的select字段只能指定当前mapper的某个select块-->
      <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>
    
    <select id="getStudents" resultMap="StudentAndTeacher">
      select *
      from student
    </select>
    
    <select id="getTeacher" resultType="Teacher">
      select *
      from teacher
      where id = #{id}
    </select>
    
  2. 联表查询(按照结果嵌套处理)

    思路:一次性查出所有需要的结果,然后一一映射。

    <resultMap id="StudentAndTeacher2" 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>
    
    <select id="getStudents2" resultMap="StudentAndTeacher2">
      select s.id sid, s.name sname, t.id tid, t.name tname
      from student s,
      teacher t
      where s.tid = t.id
    </select>
    

3. 一对多查询

  1. 子查询(按照查询嵌套处理)

    <resultMap id="TeacherAndStudents" type="Teacher">
      <result property="id" column="id"/>
      <collection property="students" column="id" select="getStudentsByTeacherId" ofType="Student"/>
    </resultMap>
    
    <select id="getTeahcer" resultMap="TeacherAndStudents">
      select *
      from teacher
      where id = #{id}
    </select>
    
    <select id="getStudentsByTeacherId" resultType="Student">
      select *
      from student
      where tid = #{id}
    </select>
    
  2. 联表查询(按照结果嵌套处理)

    <resultMap id="TeacherAndStudents2" type="Teacher">
      <result property="id" column="tid"/>
      <result property="name" column="tname"/>
      <collection property="students" javaType="list" ofType="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
      </collection>
    
    </resultMap>
    
    <select id="getTeahcer2" resultMap="TeacherAndStudents2">
      select s.id sid, s.name sname, t.id tid, t.name tname
      from student s,
      teacher t
      where t.id = #{id}
      and t.id = s.tid
    </select>
    </mapper>
    
Copyright © rootwhois.cn 2021-2022 all right reserved,powered by GitbookFile Modify: 2022-05-18 16:24:00

results matching ""

    No results matching ""