MyBatis系列(八)- MyBatis结果集嵌套映射| 8月更文挑战

2,635 阅读2分钟

相关文章

MyBatis系列汇总:MyBatis系列


前言

  • 先看官网关于结果集的说明

    • image-20210720142547793.png
  • 我们一般使用最多的就是result,但在实际情况中,可能会遇到复杂类型的关联!这个时候就需要用到 associationcollection

  • 下面是前置条件

  • student表

    • CREATE TABLE `student` (
        `id` int(10) NOT NULL,
        `name` varchar(20) 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
      
  • teacher表

    • CREATE TABLE `teacher` (
        `id` int(10) NOT NULL,
        `name` varchar(20) DEFAULT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8
      
  • 加点测试数据

    • image-20210720150033094.png
    • image-20210720150200345.png
  • 实体类

    • @Data
      public class Student {
          private Integer id;
          private String name;
          //需要关联一个老师类
          private Teacher teacher;
      }
      
    • @Data
      public class Teacher {
          private int id;
          private String name;
      }
      
  • 其他的跟前面文章保持一致即可!多种方式任君选择~

一、多对一

  • 首先我们正常查询一下学生试试看结果:

    • public interface StudentMapper {
              List<Student> getStudent();
      }
      
    •     <select id="getStudent" resultType="Student">
              select * from student
          </select>
      
    •     @Test
          public void getStudent(){
              SqlSession session = MybatisUtils.getSession();
              StudentMapper mapper = session.getMapper(StudentMapper.class);
              List<Student> students = mapper.getStudent();
              students.forEach(student -> {
                  System.out.println(student);
              });
              session.close();
          }
      
  • 执行结果:

    • image-20210730102419710.png
  • 结果显而易见,在查询中,如果有这种特殊的嵌套类,正常返还是null。下面我们就要使用嵌套映射来解决解决这个问题。

①、查询嵌套(子查询)

  • 注意点:resultMap和resultType只能有一个。不能同时存在。

  • 修改mapper.xml

    •     <select id="getStudent" resultMap="Student">
              select * from student
          </select>
      
          <resultMap id="Student" type="com.dy.pojo.Student">
              <result property="id" column="id"></result>
              <result property="name" column="name"></result>
              <!--对象使用assiociation-->
              <!--集合用collection-->
              <association property="teacher" column="tid"
                           javaType="com.dy.pojo.Teacher"
                           select="getTeacher"></association>
          </resultMap>
      
          <select id="getTeacher" resultType="com.dy.pojo.Teacher">
              select * from teacher where id = #{tid};
          </select>
      
      
  • 执行结果:

    • image-20210730103403595.png
    • image-20210730103654594.png
  • Nice!完美解决问题!

②、结果嵌套(联表查询)

  • mapper.xml:

    •     <select id="getStudent2" resultMap="Student">        select s.id sid,s.name sname,t.name tname        from student s,teacher t where s.tid=t.id;    </select>    <resultMap id="Student" type="com.dy.pojo.Student">        <result property="id" column="sid"></result>        <result property="name" column="sname"></result>        <association property="teacher" javaType="com.dy.pojo.Teacher">            <result property="name" column="tname"></result>        </association>    </resultMap>
      
  • 执行结果:

    • image-20210730105013436.png

二、一对多

  • 基本前提:

    • @Data@Alias("Teacher2")public class Teacher2 {    private int id;    private String name;    private List<Student1> studentList;}
      
    • @Datapublic class Student1 {    private Integer id;    private String name;    private int tid;}
      

    ①、结果嵌套(联表查询)

    • mapper.xml

      •     <select id="getTeacher" resultMap="Teacher2">        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 = #{id};    </select>    <resultMap id="Teacher2" type="com.dy.pojo2.Teacher2">        <result property="id" column="tid"></result>        <result property="name" column="tname"></result>        <!--集合中的泛型信息,我们用oftype获取-->        <collection property="studentList" ofType="com.dy.pojo2.Student1">            <result property="id" column="sid"></result>            <result property="name" column="sname"></result>        </collection>    </resultMap>
        
    • 执行结果:

      • image-20210730112430923.png

②、查询嵌套(子查询)

  • mapper.xml

    •     <select id="getTeacher2" resultMap="Teacher2">        select * from teacher where id = #{id}    </select>    <resultMap id="Teacher2" type="com.dy.pojo2.Teacher2">        <collection property="studentList" column="id" javaType="ArrayList"                    ofType="com.dy.pojo2.Student1"                    select="getStudentByTeacherId"></collection>    </resultMap>    <select id="getStudentByTeacherId" resultType="com.dy.pojo2.Student1">        select * from student where tid = #{id}    </select>
      
  • 执行结果:

    • image-20210730112812602.png
  • 完美!

三、总结

  • 关联 - association 多对一

  • 集合 - collection 一对多

  • javaType & ofType

    • JavaType用来指定实体中属性类型
    • ofType映射到list中的类型,泛型中的约束类型
  • 注意点:

    • 保证sql可读性,尽量保证通俗易懂
    • 如果问题不好排查错误,使用日志
    • resultMap 和 resultType 要区分清楚!

路漫漫其修远兮,吾必将上下求索~

如果你认为i博主写的不错!写作不易,请点赞、关注、评论给博主一个鼓励吧~hahah