MyBatis学习笔记（十一）嵌套查询与连表查询

（十一）嵌套查询与连表查询

环境

• 数据库

  CREATE TABLE `teacher`(
  `id` INT NOT NULL,
  `name` VARCHAR(30),
  PRIMARY KEY(`id`)
  )ENGINE=INNODB DEFAULT CHARSET=utf8
  
  INSERT INTO teacher VALUES(1,"张老师");
  INSERT INTO teacher VALUES(2,"李老师");
  INSERT INTO teacher VALUES(3,"王老师")
  
  CREATE TABLE `student`(
  `id` INT,
  `name` VARCHAR(30),
  `tid` INT DEFAULT NULL,
  PRIMARY KEY (`id`),
   KEY `fktid` (`tid`),
  CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`) 
  )ENGINE=INNODB DEFAULT CHARSET=utf8
  
  INSERT INTO student VALUES(1,"小张",1);
  INSERT INTO student VALUES(2,"小王",1);
  INSERT INTO student VALUES(3,"小李",1);
  INSERT INTO student VALUES(4,"小郑",1);
  INSERT INTO student VALUES(5,"小吴",1)
  
  
  
  

• 实体类

  Student

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

  Teacher

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

• 建立Mapper

  新建StudentMapper、TeacherMapper、StuentMapper.xml、TeacherMapper.xml

• Sqlsession工具类MybatisUtils

  package top.zhang.utils;
  
  import org.apache.ibatis.io.Resources;
  import org.apache.ibatis.session.SqlSession;
  import org.apache.ibatis.session.SqlSessionFactory;
  import org.apache.ibatis.session.SqlSessionFactoryBuilder;
  
  import java.io.IOException;
  import java.io.InputStream;
  
  public class MybatisUtils {
      private static SqlSessionFactory sqlSessionFactory;
  
      static {
  
  
          try {
              String resource = "mybatis-config.xml";
              //获取factory对象
              InputStream inputStream = Resources.getResourceAsStream(resource);
  
              sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
  
          } catch (IOException e) {
  
              e.printStackTrace();
  
          }
      }
  
      public static SqlSession getSqlSession(){
  
          return sqlSessionFactory.openSession(true);
  
      }
  }
  

• 测试查询，查询成功

嵌套查询

• 背景

  想列出所有学生的信息，同时每个学生有对应的老师信息，希望能够将老师的信息一起列出来，在sql语句中，这很好实现，一个简单的连接查询即可，而在MyBatis中，需要依靠ResultMap和Association关键字来实现。

  StudentMapper.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="top.zhang.dao.StudentMapper">
      
     
  
  <select id="listStudent" resultMap="StudentTeacher">
      select * from student
  </select>
      
       <resultMap id="StudentTeacher" type="top.zhang.pojo.Student">
          <association property="teacher" column="tid" javaType="top.zhang.pojo.Teacher" select="getTeacherById" >
          </association>
      </resultMap>
      
      <select id="getTeacherById" resultType="top.zhang.pojo.Teacher">
          select * from Teacher where id = #{tid}
      </select>
  </mapper>
  

如上面的代码所示，两个select语句都比较简单，分别是查询所有学生返回学生列表以及根据id查询老师信息，关键的地方在于上面的select语句中使用了resultMap关键字，这样就可以对返回值进行一定程度的自定义，在下面的resultMap中，id和name保留，对teacher属性进行了展开，javaType表示该属性的对象类型，select表示该对象对应的查询语句，查询语句的返回值一定是一个对象，因此正好用对象填充这个属性，从而将数据库字段中的一个老师的id映射成为一个Teacher对象，最终查询结果如下。

Student(id=1, name=小张, teacher=Teacher(id=1, name=张老师))
Student(id=2, name=小王, teacher=Teacher(id=1, name=张老师))
Student(id=3, name=小李, teacher=Teacher(id=1, name=张老师))
Student(id=4, name=小郑, teacher=Teacher(id=1, name=张老师))
Student(id=5, name=小吴, teacher=Teacher(id=1, name=张老师))

连表查询

背景同上

查询代码：

  <!--连表查询-->
    <select id="listStudent2" 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="top.zhang.pojo.Student">
        <result property="id" column="sid"></result>
        <result property="name" column="sname"></result>
        <association property="teacher" javaType="top.zhang.pojo.Teacher">
            <result column="tname" property="name"></result>
            <result column="tid" property="id"></result>
        </association>
    </resultMap>

如上所示，连表查询更好理解一点，select中进行查询，然后在结果集中进行映射即可。