mybatis查询多对一、一对多

现在数据库mybatis中存在两个表：student与teacher，结构与数据分别为：

student：

teacher：

 

 其中：表student的stid字段为外键，约束参考表teacher中的tid字段。

 

两个javabean：student、teacher：

student：

@Data
public class Student {
    private int sid;
    private String sname;
    private Teacher teacher;
}

teacher：

@Data
public class Teacher {
    private int tid;
    private String tname;
}

 

 studentMapper接口：

public interface StudentMapper {
    List<Student> getStudents();
    List<Student> getStudents1();
}

 

如何查询学生所有信息加上所关联的是哪个老师？

sql查询语句我们可以这样写：

select sid,sname,tname from mybatis.student,mybatis.teacher where stid = tid;

 

那么使用mybatis该怎么查询呢？

这里提供了两种方式：

子查询方式：

<select id="getStudents" resultMap="StudentTeacher">
    select * from mybatis.student
</select>
<resultMap id="StudentTeacher" type="pojo.Student">
    <result property="sid" column="sid"/>
    <result property="sname" column="sname"/>
    <association property="teacher" column="stid" javaType="pojo.Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="pojo.Teacher">
    select * from mybatis.teacher where tid = #{stid}
</select>

嵌套查询方式：

 <select id="getStudents1" resultMap="StudentTeacher1">
    select sid,sname,tname from mybatis.student,mybatis.teacher where stid = tid
</select>
<resultMap id="StudentTeacher1" type="pojo.Student">
    <result property="sid" column="sid"/>
    <result property="sname" column="sname"/>
    <association property="teacher" javaType="pojo.Teacher">
        <result property="tname" column="tname"/>
    </association>
</resultMap>

嵌套方式比较容易掌握。以上就是多对一的操作。

 

下面来看看一对多的操作：

javabean：

@Data
public class Student {
    private int sid;
    private String sname;
    private int stid;
}

@Data
public class Teacher {
    private int tid;
    private String tname;
    private List<Student> students;
}

TeacherMapper接口：

public interface TeacherMapper {
    //根据tid获取指定老师及该老师下所有的学生
    Teacher getTeacherById(@Param("tid") int tid);
    Teacher getTeacherById1(@Param("tid") int tid);
}

TeacherMapper.xml文件：

嵌套查询处理：

<select id="getTeacherById1" resultMap="TeacherStudent2">
        select * from teacher where tid = #{tid}
</select>
<resultMap id="TeacherStudent2" type="pojo.Teacher">
    <collection property="students" column="tid" ofType="pojo.Student"  javaType="java.util.ArrayList"  select="getStudentsByTid" />
</resultMap>
<select id="getStudentsByTid" resultType="pojo.Student">
    select * from student where stid = #{tid}
</select>

按照结果嵌套处理：

<select id="getTeacherById" resultMap="TeacherStudent">
        select tid,tname,sid,sname from student,teacher where (stid=tid) and (tid=#{tid})
</select>
<resultMap id="TeacherStudent" type="pojo.Teacher">
    <result property="tid" column="tid"/>
    <result property="tname" column="tname"/>
    <collection property="students" ofType="pojo.Student">
        <result property="sid" column="sid"/>
        <result property="sname" column="sname"/>
    </collection>
</resultMap>