Mybatis初学-07

多对一数据查询

<!--    select s.id, s.name, s.tid, t.name as tname from student as s, Teacher as t where s.tid = t.id;-->
<!--    方式一，按查询嵌套查询-->
    <resultMap id="StudentTeacher1" type="Student">
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>

    <select id="getStudent" resultMap="StudentTeacher1">
        select * from student
    </select>

    <select id="getTeacher" resultType="Teacher">
        select * from teacher where id = #{tid}
    </select>

<!--    方式二：按结果嵌套查询-->

    <select id="getStudent2" resultMap="StudentTeacher2">
        select s.id sid, s.name sname, s.tid tid, t.name tname
        from student s, teacher t
        where s.tid = t.id
        order by sid asc
    </select>

    <resultMap id="StudentTeacher2" type="Student">
        <id property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" column="tid" javaType="Teacher">
            <result property="id" column="tid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>

一堆多数据查询

 <select id="getTeacherById" resultMap="TeacherStudent">
        select  t.id tid, t.name tname, s.id sid, s.name sname
        from Student s, Teacher t
        where t.id = s.tid
        and t.id = #{id}
    </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>

<!--    ===================-->

    <resultMap id="TeacherStudent1" type="Teacher">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <collection property="students" javaType="ArrayList" ofType="Student" select="getStudent" column="id"/>
    </resultMap>

    <select id="getTeacherById1" resultMap="TeacherStudent1">
        select * from teacher t where id = #{id}
    </select>

    <select id="getStudent" resultType="Student">
        select * from student where tid = #{tid}
    </select>