mybatis多对一查询

• 模拟测试：多个学生对应一个老师
  1、 MySQL测试表【Teachers】、【Students】
  2、 测试实体类【Teachers】、【Students】
  3、 dao层【TeachersMapper】、【StudentsMapper】
  4、 XML映射文件【teachersMapper.xml】、【studentsMapper.xml】
  5、 核心配置文件=>【mybatis-config.xml】绑定dao接口、注册XML映射文件
  6、 输出测试

实体类创建

import lombok.Data;

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

StudentMapper方法接口

public interface StudentMapper {
    //查询所有学生信息以及对应的老师信息(方法一)
    List<Student> getStudent();
    //查询所有学生信息以及对应的老师信息(方法二)
    List<Student> getStudent2();
}

StudentMapper.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.StudentMapper">
    <!--1.查询所有学生信息
        2.根据查询出来的学生tid，寻找对应的老师
    -->
    <!--按照查询进行嵌套处理多对一-->
    <resultMap id="ST" type="student">
        <result property="id" column="id" />
        <result property="name" column="name" />
        <!--复杂属性单独处理,对象使用association，集合使用collection-->
        <association property="teacher" column="tid" javaType="teacher" select="getTeacher" />
    </resultMap>
    <select id="getStudent" resultMap="ST">
        select * from student
    </select>
    <select id="getTeacher" resultType="teacher">
        select * from teacher where id=#{id}
    </select>
    <!--/////////////////////////////////////////////////////-->
    <!--按照结果进行嵌套处理多对一-->
    <resultMap id="ST2" type="student">
        <result property="id" column="sid"/>
        <result property="name" column="sname" />
        <association property="teacher" javaType="teacher">
            <result property="name" column="tname" />
            <result property="id" column="teacherid" />
        </association>
    </resultMap>
    <select id="getStudent2" resultMap="ST2">
        select s.id sid,s.name sname,t.name tname,t.id teacherid from student s,teacher t where s.tid=t.id
    </select>
</mapper>

测试方法

    @Test
    public void Test02(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> student = mapper.getStudent();
        for (Student student1 : student) {
            System.out.println(student1.toString());
        }
    }
    @Test
    public void Test03(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> student = mapper.getStudent2();
        for (Student student1 : student) {
            System.out.println(student1.toString());
        }
    }

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 521081105.
==>  Preparing: select * from student
==> Parameters: 
<==    Columns: id, name, tid
<==        Row: 1, 小明, 1
====>  Preparing: select * from teacher where id=?
====> Parameters: 1(Integer)
<====    Columns: id, name
<====        Row: 1, 秦老师
<====      Total: 1
<==        Row: 2, 小红, 1
<==        Row: 3, 小张, 1
<==        Row: 4, 小李, 1
<==        Row: 5, 小王, 1
<==      Total: 5
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=秦老师))
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@1f0f1111]
Returned connection 521081105 to pool.

进程已结束,退出代码0

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 521081105.
==>  Preparing: select s.id sid,s.name sname,t.name tname,t.id teacherid from student s,teacher t where s.tid=t.id
==> Parameters: 
<==    Columns: sid, sname, tname, teacherid
<==        Row: 1, 小明, 秦老师, 1
<==        Row: 2, 小红, 秦老师, 1
<==        Row: 3, 小张, 秦老师, 1
<==        Row: 4, 小李, 秦老师, 1
<==        Row: 5, 小王, 秦老师, 1
<==      Total: 5
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=秦老师))
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@1f0f1111]
Returned connection 521081105 to pool.

进程已结束,退出代码0