目录
一、复杂查询环境搭建
1、导入lombok
2、新建实体类 Teacher , Student
3、建立Mapper.xml文件
5、在核心配置文件中绑定注册我们的Mapper接口或文件
6、测试查询是否成功
二、多对一的处理
1、实体类
@Data
public class Student {
private int id;
private String name;
//学生需要关联一个老师
private Teacher teacher;
}
@Data
public class Teacher {
private int id;
private String name;
}
2、按照查询嵌套处理(子查询)
<!--
思路:
1、查询所有学生的信息
2、根据查询出的学生的tid,寻找对应的老师
-->
<!--根据查询嵌套处理(子查询)-->
<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
property:实体类中的属性名
column:数据库中对应的字段名
javaType:实体类中属性的类型
select:根据属性的类型,找到并实现相应的接口方法,获得的值返回到column中
-->
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * from teacher where id=#{id}
</select>
3、按照结果嵌套处理(联表查询)
<!--根据结果嵌套处理(联表查询)-->
<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>
二、一对多的处理
1、实体类
@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;
}
2、按照结果嵌套处理(联表查询)
<!--按结果嵌套处理-->
<select id="getTeacher" resultMap="TeacherStudent">
select s.id sid,s.name sname,t.id tid,t.name tname
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"/>
<!--复杂的属性单独处理 对象(多对一时):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>
3、按照查询嵌套处理(子查询)
<!--按查询嵌套处理-->
<select id="getTeacher2" resultMap="TeacherStudent2">
select * from 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 student where tid=#{tid}
</select>
4、测试
@Test
public void getTeacherTest(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher(1);
System.out.println(teacher);
sqlSession.commit();
sqlSession.close();
}
三、小结
1.
关联
association(多对一)
2.
集合
collection (一对多)
3.
JavaType
是用来指定实体类
中属性的类型
4.ofType
指定的是映射到
list
集合属性中实体类
的类型。