MyBatis嵌套查寻&嵌套结果查询--复杂查询

两张表：

student，teacher

 

 

 

 

多对一 

studentMapper

public interface StudentMapper {

  public List<Student> getStudent();
  public List<Student> getStudent2();

}

StudentMapper.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.wang.dao.StudentMapper">
//嵌套查询
    <select id="getStudent" resultMap="StudentTeacher">
        select * from mybatis.student
    </select>
    <resultMap id="StudentTeacher" type="Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>


//嵌套结果查询
    <select id="getStudent2" resultMap="StudentTeacher2">
        select s.id sid,s.name sname,t.name tname
        from student s,teacher t
        where s.tid = t.id
    </select>
    <resultMap id="StudentTeacher2" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="name" column="tname"/>
        </association>
    </resultMap>


</mapper>

<==    Columns: sid, sname, tname
<==        Row: 1, 小明, 王老师
<==        Row: 2, 小红, 王老师
<==        Row: 3, 小张, 王老师
<==        Row: 4, 小李, 王老师
<==        Row: 5, 小王, 王老师
<==      Total: 5

 

一对多

TeacherMapper

public interface TeacherMapper {

    //获取指定老师下的所有学生信息
    Teacher getTeacher(@Param("tid") int id);
}

TeacherMapper.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.wang.dao.TeacherMapper">
<!--按结果嵌套查询-->
    <select id="getTeacher" resultMap="TS">
        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="TS" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
    <!--    复杂的属性，我们需要单独处理
            对象： association ; 集合：collection
            javaType="" 指定属性的类型
            集合中的泛型信息，使用ofType
    -->
        <collection property="students" ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>
</mapper>

Teacher(id=1, name=王老师, 
students=[Student(id=1, name=小明, tid=1),
Student(id=2, name=小红, tid=1), 
Student(id=3, name=小张, tid=1), 
Student(id=4, name=小李, tid=1), 
Student(id=5, name=小王, tid=1)])