Welcome
0%

Mybatis多表查询问题

发表于 分类于

我主要总结了一对一、一对多、多对多查询的问题。

实体类

  • 学生类
1
2
3
4
5
6
7
8
9
10
11
//学生类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    private Integer studentId;
    private Integer classId;
    private String studentName;
    private Classes classes;
}

  • 班级类
1
2
3
4
5
6
7
8
9
10
//班级类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Classes {
    Integer classId;
    String className;
    List<Student> students;
}

一对一查询

  • dao 层
1
2
3
public interface StudentDao {
    List<Student> queryStudentOnClasses();
}
  • xml 文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?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="dao.StudentDao">
    <resultMap id="student" type="pojo.Student">
        <id property="studentId" column="student_id"></id>
        <result property="studentName" column="student_name"></result>
        <!--
			association 这个标签对应student中的classes成员变量的属性。
		-->
        <association property="classes" javaType="pojo.Classes">
            <result property="classId" column="class_id"></result>
            <result property="className" column="class_name"></result>
        </association>
    </resultMap>
    <select id="queryStudentOnClasses" resultMap="student">
        select s.*, c.class_id, c.class_name from t_student s, t_class c where s.class_id = c.class_id
    </select>
</mapper>

多对多/一对多查询

  • dao层
1
2
3
public interface ClassesDao {
    List<Classes> queryClassesOnStudent();
}
  • xml文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<?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="dao.ClassesDao">
    <resultMap id="classes" type="pojo.Classes">
        <id property="classId" column="class_id"></id>
        <result property="className" column="class_name"></result>
        <!--
			collection 这个集合对应student中的classes成员变量的属性。
		-->
        <collection property="student" javaType="pojo.Student">
            <id property="studentId" column="student_id"></id>
            <result property="studentName" column="student_name"></result>
        </collection>
    </resultMap>

    <select id="queryStudentOnClasses" resultMap="student">
        select c.*, s.student_id, s.student_name from t_class c left join t_student s on c.class_id = s.class_id
    </select>
</mapper>

问题：在一对多查询中如果要使用分页查询用limit进行限制的话，会将collection的大小也算在limit内。

解决方案：将多表查询分成主查询和子查询，分别对想要的查询进行分页。