MyBatis-复杂查询
隔离期间开始学ssm,记录一下知识点,方便回顾
1.一对多的处理
pojo-Student类
@Data
public class Student {
private int id;
private String name;
private int tid;
}
pojo-Teacher类
@Data
public class Teacher {
private int id;
private String name;
private List< Student> students;
}
TeacherMapper.xml
< mapper namespace= "com.cinco.dao.TeacherMapper" >
< ! -- 按结果嵌套查询-- >
< select id= "getTeacher1" resultMap= "TeacherStudent1" >
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 = #{ tid}
< / select>
< resultMap id= "TeacherStudent1" type= "Teacher" >
< result property= "id" column= "tid" / >
< result property= "name" column= "tname" / >
< ! -- 复杂的属性,对象用association,集合用collection
javaType 指定属性的类型
ofType 指定集合中的泛型信息
-- >
< collection property= "students" javaType= "ArrayList" 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 teacher where id = #{ tid}
< / select>
< resultMap id= "TeacherStudent2" type= "Teacher" >
< result property= "id" column= "id" / >
< result property= "name" column= "name" / >
< collection property= "students" javaType= "ArrayList" ofType= "Student" select= "getStudentByTid" column= "id" / >
< / resultMap>
< select id= "getStudentByTid" resultType= "Student" >
select * from student where tid = #{ tid}
< / select>
< / mapper>
2.多对一的处理
pojo-Student类
@Data
public class Student {
private int id;
private String name;
private Teacher teacher;
}
pojo-Teacher类
@Data
public class Teacher {
private int id;
private String name;
}
StudentMapper.xml
< mapper namespace= "com.cinco.dao.StudentMapper" >
< ! -- 相当与sql子查询 -- >
< select id= "getStudent1" resultMap= "StudentTeacher1" >
select * from student
< / select>
< resultMap id= "StudentTeacher1" type= "Student" >
< result property= "id" column= "id" / >
< result property= "name" column= "name" / >
< association property= "teacher" column= "tid" javaType= "Teacher" select= "getTeacher" / >
< / resultMap>
< select id= "getTeacher" resultType= "Teacher" >
select * from teacher where id = #{ id}
< / select>
< ! -- 相当与sql联表查询 -- >
< 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>