MyBatis——级联查询与Mapper.xml

一、一对多（学生与班级）

（1）Student

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    private long id;
    private String studentName;
    private Classes classes;
}

（2）Classes

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Classes {
    private long id;
    private String className;
    private List<Student> students;
}

（3）通过学生id查找学生信息

StudentRepository接口

public interface StudentRepository {
    Student findById(long id);

}

（4）StudentRepository.xml

<?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.lin.repository.StudentRepository">
    <!-- 设置映射 -->
    <resultMap id="StudentMap" type="com.lin.entity.Student">
        <id column="id" property="id"></id>
        <result column="studentName" property="studentName"></result>
        <association property="classes" javaType="com.lin.entity.Classes">
            <id column="id" property="id"></id>
            <result column="className" property="className"></result>
        </association>
    </resultMap>
    <select id="findById"  parameterType="java.lang.Long" resultMap="StudentMap">
        select s.id,s.studentName,c.className from student s,classes c where s.id=#{id} and s.classID=c.id
    </select>
</mapper>

（5）通过班级id查找学生信息

ClassesRepository接口

public interface ClassesRepository {
    Classes findById(long id);
}

ClassesRepository.xml

<?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.lin.repository.ClassesRepository">
    <!-- 设置映射 -->
    <resultMap id="classesMap" type="com.lin.entity.Classes">
        <id column="id" property="id"></id>
        <result column="className" property="className"></result>
        <collection property="students" ofType="com.lin.entity.Student">
            <id column="sid" property="id"></id>
            <result column="studentName" property="studentName"></result>
        </collection>
    </resultMap>
    <select id="findById" resultMap="classesMap" parameterType="java.lang.Long">
        select c.id,s.id as sid,s.studentName,c.className from classes c,student s where c.id=s.classID and c.id= #{id}
    </select>
</mapper>

二、多对多（学生与课程）

建立一个数据表存放两张表的关系

（1）Student

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    private long id;
    private String studentName;
    private List<Course> courses;
}

（2）Course

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Course {
    private long id;
    private String courseName;
    private List<Student> students;
}

（3）StudentRepository接口

public interface StudentRepository {
    Student findById(long id);

}

（4）StudentRepository.xml

<?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.lin.repository.StudentRepository">

    <resultMap id="studentMap" type="com.lin.entity.Student">
        <id column="sid" property="id"></id>
        <result column="studentName" property="studentName"></result>
        <collection property="courses" ofType="com.lin.entity.Course">
            <id column="cid" property="id"></id>
            <result column="courseName" property="courseName"></result>
        </collection>
    </resultMap>
    <select id="findById" parameterType="java.lang.Long" resultMap="studentMap">
        select s.id as sid,s.studentName,c.id as cid,c.courseName from student s,course c,student_course sc where s.id=#{id} and sc.c_id=c.id and sc.s_id=s.id
    </select>
</mapper>

三、 xml 文件小结

• column为SQL语句中字段名称
• property为实例类中字段名称
• 使用<collection>表示集合
  • ofType泛型的类型
• 使用<association>表示对象
  • javaType对象类型