Mybatis多对一，一对多查询模式

1.多对一的处理

现数据库中存在teacher表和student表，其中student表中的tid为外键，连接teacher表中的id

实体类：

Student.java：

@Data
public class Student {
    private int id;
    private String name;
    private int age;
    private Teacher teacher;
}
1
2
3
4
5
6
7

Teacher.java：

@Data
public class Teacher {
    private int id;
    private String name;
    private String major;
}
1
2
3
4
5
6

按照查询嵌套处理

StudentMapper.xml：

具体的思路是，使用resultMap进行联合查询操作，封装到一个结果集中：

resultMap中使用association对对象类型进行处理🐙

DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="top.imustctf.dao.StudentMapper">
    <select id="getStuList" resultMap="StudentTeacher">
        select * from student
    select>
    <resultMap id="StudentTeacher" type="Student">
        <result column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="age" property="age"/>
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    resultMap>

    <select id="getTeacher" resultType="Teacher">
        select * from teacher where id = #{id}
    select>
mapper>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

测试类：

public class StudentMapperTest {
    @Test
    public void test() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> stuList = mapper.getStuList();
        for (Student student : stuList) {
            System.out.println(student);
        }
        sqlSession.close();
    }
}
--------------------------
Student(id=1, name=王伟, age=18, teacher=Teacher{id=1, name='鱼皮', major='语文'})
Student(id=2, name=李华, age=15, teacher=Teacher{id=1, name='鱼皮', major='语文'})
Student(id=3, name=皮皮, age=11, teacher=Teacher{id=1, name='鱼皮', major='语文'})
Student(id=4, name=树立, age=16, teacher=Teacher{id=2, name='大河', major='数学'})
Student(id=5, name=自立, age=17, teacher=Teacher{id=2, name='大河', major='数学'})
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

按照结果嵌套处理

StudentMapper.xml：

使用复杂的SQL语句直接进行连表查询：

<select id="getStuListDou" resultMap="StudentTeacherDou">
    select s.id sid , s.name sname , t.name tname
    from student s , teacher t
    where s.tid = t.id
select>

<resultMap id="StudentTeacherDou" type="Student">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <association property="teacher" javaType="Teacher">
        <result property="name" column="tname"/>
    association>
resultMap>
1
2
3
4
5
6
7
8
9
10
11
12
13

测试类：

@Test
public void test() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    List<Student> stuListDou = mapper.getStuListDou();
    for (Student student : stuListDou) {
        System.out.println(student);
    }
    sqlSession.close();
}
---------------------------------------
Student(id=1, name=王伟, age=0, teacher=Teacher{id=1, name='鱼皮', major='鱼皮'})
Student(id=2, name=李华, age=0, teacher=Teacher{id=2, name='鱼皮', major='鱼皮'})
Student(id=3, name=皮皮, age=0, teacher=Teacher{id=3, name='鱼皮', major='鱼皮'})
Student(id=4, name=树立, age=0, teacher=Teacher{id=4, name='大河', major='大河'})
Student(id=5, name=自立, age=0, teacher=Teacher{id=5, name='大河', major='大河'})
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

---

2.一对多的处理

实体类：

Student.java：

@Data
public class Student {
    private int id;
    private String name;
    private int age;
    private int tid;
}
1
2
3
4
5
6
7

Teacher.java：

@Data
public class Teacher {
    private int id;
    private String name;
    private String major;
    private List<Student> students;
}
1
2
3
4
5
6
7

按照结果嵌套处理

开发接口类：

public interface TeacherMapper {
    Teacher getTeacherById (@Param("tid") int id);
}
1
2
3

TeacherMapper.xml：

DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="top.imustctf.dao.TeacherMapper">

    <select id="getTeacherById" resultMap="TeacherStudent">
        select s.id sid , s.name sname , t.name tname , t.id tid
        from student s , teacher t
        where s.tid = t.id and t.id = #{tid}
    select>

    <resultMap id="TeacherStudent" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <collection property="students" ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        collection>
    resultMap>
mapper>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

测试类开发：

public class TeacherMapperTest {
    @Test
    public void testA() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = teacherMapper.getTeacherById(1);
        System.out.println(teacher);
        sqlSession.close();
    }
}
1
2
3
4
5
6
7
8
9
10

输出结果：

Teacher(id=1, name=鱼皮, major=null, students=[Student(id=1, name=王伟, age=0, tid=1), Student(id=2, name=李华, age=0, tid=1), Student(id=3, name=皮皮, age=0, tid=1)])
1