mybaits多表联合查询

多表联合查询

①实体类：

@Data
@NoArgsConstructor
@AllArgsConstructor
//@Alias("Student"),注解重命名
public class Student {
    private int id;
    private String name;
    private Teacher teacher;
}

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Teacher {
    private int id;
    private String name;
    private List<Student> students;
}

②mapper配置（多对一）

<?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.lxz.dao.StudentDao">
    <insert id="insertStudent">
        insert into mybatis_example.student(id, name, tid)
        values (#{student.id}, #{student.name}, #{student.tid})
    </insert>

    <select id="getStudentList" resultMap="StudentTeacher">
        select *
        from mybatis_example.student
    </select>

    <select id="getStudentByName" resultMap="StudentTeacher">
        select *
        from mybatis_example.student where name=#{name}
    </select>
    <resultMap id="StudentTeacher" type="Student">
        <result column="id" property="id"/>
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>
    <select id="getTeacher" resultType="Teacher">
        select *
        from mybatis_example.teacher
    </select>
</mapper>

③mapper配置（一对多）

<?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.lxz.dao.TeacherDao">
    <insert id="insertTeacher">
        insert into mybatis_example.teacher(id, name)
        VALUES (#{teacher.id}, #{teacher.name})
    </insert>
    <!-- 方式一 -->
    <select id="getTeacher" resultMap="TeacherStudent">
        select t.id teacher_id, t.name teacher_name, s.id student_id, s.name student_name
        from mybatis_example.student s,
             mybatis_example.teacher t
        where s.tid = t.id
          and t.id = #{tid}
    </select>
    <resultMap id="TeacherStudent" type="Teacher">
        <result property="id" column="teacher_id"/>
        <result property="name" column="teacher_name"/>
        <collection property="students" javaType="ArrayList" ofType="Student">
            <result property="id" column="student_id"/>
            <result property="name" column="student_name"/>
        </collection>
    </resultMap>

	<!-- 方式二 -->
    <select id="getTeacherList" resultMap="list_teacher_student">
        select *
        from mybatis_example.teacher;
    </select>
    <resultMap id="list_teacher_student" type="Teacher">
        <collection property="students" column="id" javaType="ArrayList" ofType="Student" select="getStudentList"/>
    </resultMap>
    <select id="getStudentList" resultType="Student">
        select *
        from mybatis_example.student
        where tid = #{id}
    </select>
</mapper>