mybatis(八)复杂查询

mybatis(八)复杂查询

8.1多对一

8.1.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

老师表:

CREATE TABLE `teacher` (
  `id` int(10) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

插入相应的数据

8.1.2 测试环境

• 导入相应的包

• 编写 teacher和student 实体类 使用lombok

• 编写student和teacher Mapper 接口

• 编写相对应的接口的xml配置文件

• 编写获取MyBaitsUtils的工具类

• 把 studentMapper.xml和teacher Mapper,xml 添加到mybatis-config.xml中

• 测试

  public class demo {
      SqlSession sqlSession = MybatisUtils06.getSqlSession();
      TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
      StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
  
      @Test
      public void test() {
          Teacher teacher = teacherMapper.getTeacher(1);
          System.out.println(teacher);
      }
  }
  

可以正确得到 id为1 的老师信息 即可

8.1.3 查询所有学生的信息以及对应老师的信息

方式一: 按照查询嵌套

其中原生的sql 语句是:

select  *  from   student s,teacher t where s.tid=t.id;

编写接口

/**
    * 查询所有的学生 以及  对应老师的信息
    * @param
    * @return java.util.List<com.qlx.pojo06.Student>
    * @author 小小的梦想丶
    * @date 2020/09/23 23:41:35
    */
    List<Student> getStudent();

编写StudentMapper.xml

<select id="getStudent" resultType="com.qlx.pojo06.Student">
    select * from mybatis.student s
</select>

得出的结果却是 teacher 为null

解决办法:

<select id="getStudent" resultMap="StudentTeacher">
    select * from mybatis.student s
</select>
<resultMap id="StudentTeacher" type="Student">
    <association property="teacher"  column="tid" javaType="com.qlx.pojo06.Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
    select * from mybatis.teacher t
</select>

结果:

方式二:按照结果嵌套处理(推荐)

编写接口

/**
 * 按照结果嵌套查询
* 查询所有的学生 以及  对应老师的信息
* @param
* @return java.util.List<com.qlx.pojo06.Student>
* @author 小小的梦想丶
* @date 2020/09/23 23:41:35
*/
List<Student> getStudent2();

编写StudentMapper.xml

<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>

测试

@Test
public void testGetStudent2() {
    List<Student> student2 = studentMapper.getStudent2();
    student2.forEach(System.out::println);
}

结果:

​

8.2 一对多

比如 一个老师拥有多个学生 对老师而言 就是一对多的关系

8.2.1测试环境

• 编写接口

  List<Teacher> getTeachers();
  

• 编写xml

  <select id="getTeachers" resultType="com.qlx.pojo07.Teacher">
      select *
      from mybatis.teacher t
  </select>
  

• 测试

  public class demo {
      SqlSession sqlSession = MybatisUtils07.getSqlSession();
      StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
      TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
  
      @Test
      public void testGetTeachers() {
          List<Teacher> teachers = teacherMapper.getTeachers();
          teachers.forEach(System.out::println);
      }
  }	
  

• 结果

  Opening JDBC Connection
  Created connection 1997859171.
  ==>  Preparing: select * from mybatis.teacher t
  ==> Parameters: 
  <==    Columns: id, name
  <==        Row: 1, 秦老师
  <==      Total: 1
  Teacher(id=1, name=秦老师, students=null)
  

  问题所在就是 查询出来的 students 为null

8.2.2 方式一 按照结果嵌套查询

编写接口

Teacher getTeacher2(@Param("tid") int id);

编写xml文件

<resultMap id="TeacherStudents" type="com.qlx.pojo07.Teacher">
    <result property="id" column="tid"/>
    <result property="name" column="tname"/>
    <collection property="students" ofType="com.qlx.pojo07.Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <result property="tid" column="tid"/>
    </collection>
</resultMap>
<select id="getTeacher2" resultType="com.qlx.pojo07.Teacher" resultMap="TeacherStudents">
    select s.id sid, s.name sname,t.id tid,t.name tname from mybatis.teacher t,mybatis.student s where s.tid=t.id
    and
    t.id=#{tid}
</select>

测试:

public class demo {
    SqlSession sqlSession = MybatisUtils07.getSqlSession();
    StudentMapper07 studentMapper07 = sqlSession.getMapper(StudentMapper07.class);
    TeacherMapper07 teacherMapper07 = sqlSession.getMapper(TeacherMapper07.class);
    @Test
    public void testGetTeacher2() {
        Teacher teacher2 = teacherMapper07.getTeacher2(1);
        System.out.println(teacher2);
    }

}

结果:

8.2.3方式二按照查询嵌套

接口

/**
 * 按照查询嵌套  出 id对应的老师信息以及对应的所有学生信息
 *
 * @param id
 * @return com.qlx.pojo07.Teacher
 * @author 小小的梦想丶
 * @date 2020/09/24 19:50:13
 */
Teacher07 getTeacher(@Param("tid") int id);

xml配置

<resultMap id="TeacherStudent1" type="com.qlx.pojo07.Teacher07">
       <!--column="id"   就是  查询学生时候传递的值--->
    <collection property="student07s" javaType="ArrayList" ofType="com.qlx.pojo07.Student07" select="getStudent"
                column="id"/>
 
</resultMap>
<select id="getTeacher" resultType="com.qlx.pojo07.Teacher07" resultMap="TeacherStudent1">
    select * from mybatis.teacher t where id=#{tid}
</select>
<select id="getStudent" resultType="com.qlx.pojo07.Student07">
    select * from mybatis.student s where tid=#{tid}
</select>

测试:

@Test
public void testGetTeacher() {
    Teacher07 teacher07 = teacherMapper07.getTeacher(1);
    System.out.println(teacher07);
}

结果: