mybatis中多对一查询

实体类:(注:这里使用了lombok插件)

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

实体类对应的接口类:

public interface StudentMapper {
    /**
     * 获取所有学生的信息,包括对应的老师信息
     *
     * @return
     */
    public List<Student> getStudents();
}
public interface TeacherMapper {
    /**
     * 根据老师id来查询老师信息
     *
     * @param id
     * @return
     */
    @Select("select * from teacher where id=#{tid}")
    Teacher getTeacherById(@Param("tid") int id);
}

需求:

  1. 查询出所有的学生信息,其中他的老师信息也一起查出来。

    • 编写对应的mapper文件

      <?xml version="1.0" encoding="UTF-8" ?>
      <!DOCTYPE mapper
              PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
              "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
      <mapper namespace="com.luwanglin.dao.StudentMapper">
          <select id="getStudents" resultMap="StudentTeacher">
              select * from student
          </select>
          <resultMap id="StudentTeacher" type="Student">
              <id property="id" column="id"/>
              <result property="name" column="name"/>
              <association property="teacher" column="{demo=tid,testname=name}" javaType="Teacher" select="getTeacher"/>
          </resultMap>
          <!--
        这里传递过来的id,只有一个属性的时候,下面可以写任何值
        association中column多参数配置:
          column="{key=value,key=value}"
          其实就是键值对的形式,key是传给下个sql的取值名称,value是片段一中sql查询的
      字段名。
      
          个人测试:
          column只有一个属性时,可以在下面的getTeacher中任意名字取出值,有多个时以键值对的形式传递
        -->
          <select id="getTeacher" resultType="teacher">
              select * from teacher where id=#{demo};
          </select>
      </mapper>
      
      

      注:记得注册mapper

    • 编写测试类:

      package com.luwanglin.dao;
      
      import com.luwanglin.pojo.Student;
      import com.luwanglin.pojo.Teacher;
      import com.luwanglin.utils.MybatisUtils;
      import org.apache.ibatis.session.SqlSession;
      import org.junit.jupiter.api.AfterEach;
      import org.junit.jupiter.api.BeforeEach;
      import org.junit.jupiter.api.Test;
      
      import java.util.List;
      
      /**
       * @author luwanglin
       * @email 1769862620@qq.com
       * @Date 2020/9/15 10:00
       * @Version 1.0
       */
      public class UserMapperTest {
          SqlSession sqlSession;
      
          @Test
          void testGetTeacherById() {
              TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
              Teacher teacher = mapper.getTeacherById(1);
              System.out.println(teacher);
          }
      
          @Test
          void testGetStudents() {
              StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
              List<Student> students = mapper.getStudents();
              for (Student student : students) {
                  System.out.println(student);
              }
          }
      
      
          @BeforeEach
          public void initSqlSession() {
      //        System.out.println("这是BeforeEach!");
              //MybatisUtils是一个工具类,用来获取sqlsession
              sqlSession = MybatisUtils.getSession();
          }
      
          @AfterEach
          void closeSqlSession() {
              sqlSession.close();
          }
      }
      

      工具类:

      package com.luwanglin.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;
      
      /**
       * @author luwanglin
       * @email 1769862620@qq.com
       * @Date 2020/9/15 9:09
       * @Version 1.0
       */
      public class MybatisUtils {
          private static SqlSessionFactory sqlSessionFactory = null;
      
          static {
              try {
                  String resource = "mybatis-config.xml";
                  InputStream inputStream = Resources.getResourceAsStream(resource);
                  sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
              } catch (IOException e) {
                  e.printStackTrace();
              }
          }
      
          public static SqlSession getSession() {
              return sqlSessionFactory.openSession();
          }
      
      }
      
    • 结果如下:

      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=秦老师))
      Student(id=6, name=小哈, teacher=Teacher(id=1, name=秦老师))
      

    为了查看传递键值对的效果,这里采用了如下测试:

    <select id="getTeacher" resultType="teacher">
        select * from teacher where id=#{demo} or name=#{testname};
    /*
    这里就根据上面的键值对取出了demo对应于tid
    */
    </select>
    

    这里同样是可以查出学生的信息以及对应的老师信息。此时控制台打印的日志信息如下:

    2020-09-17 10:12:48 [com.luwanglin.dao.StudentMapper.getStudents:137]-[DEBUG] ==>  Preparing: select * from student
    2020-09-17 10:12:49 [com.luwanglin.dao.StudentMapper.getStudents:137]-[DEBUG] ==> Parameters: 
    2020-09-17 10:12:49 [com.luwanglin.dao.StudentMapper.getTeacher:137]-[DEBUG] ====>  Preparing: select * from teacher where id=? or name=?; /* 这里就根据上面的键值对取出了demo对应于tid */
    2020-09-17 10:12:49 [com.luwanglin.dao.StudentMapper.getTeacher:137]-[DEBUG] ====> Parameters: 1(Integer), 小明(String)
    2020-09-17 10:12:49 [com.luwanglin.dao.StudentMapper.getTeacher:137]-[DEBUG] <====      Total: 1
    2020-09-17 10:12:49 [com.luwanglin.dao.StudentMapper.getTeacher:137]-[DEBUG] ====>  Preparing: select * from teacher where id=? or name=?; /* 这里就根据上面的键值对取出了demo对应于tid */
    2020-09-17 10:12:49 [com.luwanglin.dao.StudentMapper.getTeacher:137]-[DEBUG] ====> Parameters: 1(Integer), 小红(String)
    2020-09-17 10:12:49 [com.luwanglin.dao.StudentMapper.getTeacher:137]-[DEBUG] <====      Total: 1
    2020-09-17 10:12:49 [com.luwanglin.dao.StudentMapper.getTeacher:137]-[DEBUG] ====>  Preparing: select * from teacher where id=? or name=?; /* 这里就根据上面的键值对取出了demo对应于tid */
    2020-09-17 10:12:49 [com.luwanglin.dao.StudentMapper.getTeacher:137]-[DEBUG] ====> Parameters: 1(Integer), 小张(String)
    2020-09-17 10:12:49 [com.luwanglin.dao.StudentMapper.getTeacher:137]-[DEBUG] <====      Total: 1
    2020-09-17 10:12:49 [com.luwanglin.dao.StudentMapper.getTeacher:137]-[DEBUG] ====>  Preparing: select * from teacher where id=? or name=?; /* 这里就根据上面的键值对取出了demo对应于tid */
    2020-09-17 10:12:49 [com.luwanglin.dao.StudentMapper.getTeacher:137]-[DEBUG] ====> Parameters: 1(Integer), 小李(String)
    2020-09-17 10:12:49 [com.luwanglin.dao.StudentMapper.getTeacher:137]-[DEBUG] <====      Total: 1
    2020-09-17 10:12:49 [com.luwanglin.dao.StudentMapper.getTeacher:137]-[DEBUG] ====>  Preparing: select * from teacher where id=? or name=?; /* 这里就根据上面的键值对取出了demo对应于tid */
    2020-09-17 10:12:49 [com.luwanglin.dao.StudentMapper.getTeacher:137]-[DEBUG] ====> Parameters: 1(Integer), 小王(String)
    2020-09-17 10:12:49 [com.luwanglin.dao.StudentMapper.getTeacher:137]-[DEBUG] <====      Total: 1
    2020-09-17 10:12:49 [com.luwanglin.dao.StudentMapper.getTeacher:137]-[DEBUG] ====>  Preparing: select * from teacher where id=? or name=?; /* 这里就根据上面的键值对取出了demo对应于tid */
    2020-09-17 10:12:49 [com.luwanglin.dao.StudentMapper.getTeacher:137]-[DEBUG] ====> Parameters: 1(Integer), 小哈(String)
    2020-09-17 10:12:49 [com.luwanglin.dao.StudentMapper.getTeacher:137]-[DEBUG] <====      Total: 1
    2020-09-17 10:12:49 [com.luwanglin.dao.StudentMapper.getStudents:137]-[DEBUG] <==      Total: 6
    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=秦老师))
    Student(id=6, name=小哈, teacher=Teacher(id=1, name=秦老师))
    

    可以从日志中看出这里的确采用的是嵌套查询,因为每一条记录都进行了一次嵌套查询。

    而且每次查询的条件是根据传递的键值对来进行取值传入的。

上一篇:SQL 查询练习题


下一篇:mybatis查询多对一