Mybatis 复杂查询多对一 和 一对多
多对一的处理:(尽量使用方法一)
@Data public class Student { private int id; private String name; //学生需要关联一个老师 private Teacher teacher; } @Data public class Teacher { private int id; private String name; }
public interface StudentMapper { List<Student> getStudent(); List<Student> getStudent2(); }
<!--方法一 根据结构查询 联表 --> <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">
#property 属性名 column字段名 起了别名写别名
#简单字段可以直接这些写 <result property="id" column="sid"/> <result property="name" column="sname"/>
#复杂字段 返回是对象使用association <association property="teacher" javaType="Teacher"> <result property="name" column="tname"/> </association> </resultMap>
<!-- 方法二 子查询 嵌套--> <select id="getStudent" resultMap="StudentTeacher"> select * from student ; </select> <resultMap id="StudentTeacher" type="Student"> <result property="id" column="id"/> <result property="name" column="name"/> <!-- 复杂的属性,我们需要单独处理 对象:association 集合:collection--> <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/> </resultMap> <select id="getTeacher" resultType="Teacher"> select * from teacher where id = #{tid}; </select>
一对多的处理:(尽量使用方法一)
@Data public class Student { private int id; private String name; private int tid; } @Data public class Teacher { private int id; private String name; private List<Student> students; }
public interface TeacherMapper { //获取指定老师下的学生及老师信息 Teacher getTeacher(@Param("tid")int id); Teacher getTeacher2(@Param("tid") int id); }
<!-- 方法一 根据结构查询 --> <select id="getTeacher" resultMap="TeacherStudent"> 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="TeacherStudent" type="Teacher"> <result property="id" column="tid"/> <result property="name" column="tname"/> <!-- 集合中的泛型信息,我们使用ofType获取 --> <collection property="students" ofType="Student"> <result property="id" column="sid"/> <result property="name" column="sname"/> <result property="tid" column="tid"/> </collection> </resultMap>
<!-- 方法二 子查询 --> <select id="getTeacher2" resultMap="TeacherStudent2"> select * from mybatis.teacher where id=#{tid}; </select> <resultMap id="TeacherStudent2" type="Teacher"> <collection property="students" javaType="Arraylist" ofType="Student" select="getStudentByTeacherId" column="id"/> </resultMap> <select id="getStudentByTeacherId" resultType="Student"> select * from mybatis.student where tid=#{tid} </select>