mybatis一对多查询

• 模拟测试：一名老师有多名学生 => 【面向教师】
• 本质：使用<collection>标签完成一对多的输出

建立实体类

import lombok.Data;

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

import lombok.Data;
import java.util.List;

@Data
public class Teacher {
    private int id;
    private String name;
    //一个老师拥有多个学生
    private List<Student> students;
}

创建TeacherMapper方法接口

public interface TeacherMapper {
    //获取指定老师下所有学生及老师信息(方法一)
    Teacher getTeacherS2(@Param("tid") int id);
    //获取指定老师下所有学生及老师信息(方法二)
    Teacher getTeacherS(@Param("tid") int id);
}

创建TeacherMapper.xml Sql查询及结果映射

<?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.zhang.dao.TeacherMapper">
    <!--按查询嵌套一对多-->
    <resultMap id="TS2" type="teacher">
        <result column="id" property="id" />
        <result column="name" property="name" />
        <collection property="students" ofType="student" select="getStudentByTeacherId" column="id">
        </collection>
    </resultMap>
    <select id="getTeacherS2" resultMap="TS2">
        select * from mybatis.teacher where id=#{tid}
    </select>
    <select id="getStudentByTeacherId" resultType="student">
        select * from mybatis.student where tid=#{tid}
    </select>

    <!--*********************************************-->
    <!--按结果嵌套一对多-->
    <select id="getTeacher" resultType="teacher">
        select * from mybatis.teacher
    </select>
    <resultMap id="TS" type="teacher">
        <result property="id" column="tid" />
        <result property="name" column="tname" />
        <!--javaType是个指定属性的类型
        集合中的泛型要用ofType获取
        -->
        <collection property="students" ofType="student">
            <result property="id" column="sid" />
            <result property="name" column="sname" />
            <result property="tid" column="tid" />
        </collection>
    </resultMap>
    <select id="getTeacherS" resultMap="TS">
        select s.id sid,s.name sname,t.id tid ,t.name tname from mybatis.student s,mybatis.teacher t where s.tid=t.id and t.id=#{tid}
    </select>
</mapper>

创建测试方法

    @Test
    public void Test02(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacherS = mapper.getTeacherS(1);
        System.out.println(teacherS);
        sqlSession.close();
    }

    @Test
    public void Test03(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacherS = mapper.getTeacherS2(1);
        System.out.println(teacherS.toString());
        sqlSession.close();
    }

03测试结果

Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 2035070981.
==>  Preparing: select * from mybatis.teacher where id=?
==> Parameters: 1(Integer)
<==    Columns: id, name
<==        Row: 1, 秦老师
====>  Preparing: select * from mybatis.student where tid=?
====> Parameters: 1(Integer)
<====    Columns: id, name, tid
<====        Row: 1, 小明, 1
<====        Row: 2, 小红, 1
<====        Row: 3, 小张, 1
<====        Row: 4, 小李, 1
<====        Row: 5, 小王, 1
<====      Total: 5
<==      Total: 1
Teacher(id=1, name=秦老师, students=[Student(id=1, name=小明, tid=1), Student(id=2, name=小红, tid=1), Student(id=3, name=小张, tid=1), Student(id=4, name=小李, tid=1), Student(id=5, name=小王, tid=1)])
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@794cb805]
Returned connection 2035070981 to pool.

进程已结束,退出代码0

02测试结果

Logging initialized using 'class org.apache.ibatis.logging.stdout.StdOutImpl' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 2035070981.
==>  Preparing: select s.id sid,s.name sname,t.id tid ,t.name tname from mybatis.student s,mybatis.teacher t where s.tid=t.id and t.id=?
==> Parameters: 1(Integer)
<==    Columns: sid, sname, tid, tname
<==        Row: 1, 小明, 1, 秦老师
<==        Row: 2, 小红, 1, 秦老师
<==        Row: 3, 小张, 1, 秦老师
<==        Row: 4, 小李, 1, 秦老师
<==        Row: 5, 小王, 1, 秦老师
<==      Total: 5
Teacher(id=1, name=秦老师, students=[Student(id=1, name=小明, tid=1), Student(id=2, name=小红, tid=1), Student(id=3, name=小张, tid=1), Student(id=4, name=小李, tid=1), Student(id=5, name=小王, tid=1)])
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@794cb805]
Returned connection 2035070981 to pool.

进程已结束,退出代码0