复杂查询环境搭建
綁定注册可以使用包扫描,因为可以同时注册接口和xml文件
多对一处理
<association property="teacher" javaType="Teacher">
返回的是一个Teacher对象,然后对Teacher进行输出
**按照查询嵌套处理 也就是先把学生的所有信息查询出来,然后根据学生tid=老师id查找相对应的老师名字 **
这种方式就是子查询,查询了两次
<!--============================================================-->
<!--
思路:
1.查询所有的学生信息
2.根据查询出来的学生的tid,寻找对应的老师,也就是子查询
这里是按照查询嵌套处理
-->
<select id="getStudent" resultMap="StudentTeacher">
select id, name, tid, age
from mybatis.student
</select>
<!--结果集映射 将结果映射到student的实体类的中,这个实体类中有属性teacher-->
<resultMap id="StudentTeacher" type="Student">
<result column="id" property="id"/>
<result column="name" property="name"/>
<result property="tid" column="tid"/>
<!-- 复杂的属性,我们要单独进行处理 ,这里是返回的是一个对象-->
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select name
from mybatis.teacher
where id = #{tid}
</select>
测试:
@Test
public void test() {
SqlSession sqlSession = mybatisUtils.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> student = mapper.getStudent();
for (Student student1 : student) {
System.out.println(student1);
}
sqlSession.close();
}
测试结果
[com.q.dao.StudentMapper.getTeacher]-====> Preparing: select name from mybatis.teacher where id = ?
[com.q.dao.StudentMapper.getTeacher]-====> Parameters: 1(Integer)
[com.q.dao.StudentMapper.getTeacher]-<==== Total: 1
[com.q.dao.StudentMapper.getTeacher]-====> Preparing: select name from mybatis.teacher where id = ?
[com.q.dao.StudentMapper.getTeacher]-====> Parameters: 2(Integer)
[com.q.dao.StudentMapper.getTeacher]-<==== Total: 1
[com.q.dao.StudentMapper.getStudent]-<== Total: 8
Student(id=1, name=ljq1, tid=1, age=23, teacher=Teacher(student=null, id=0, name=林老师))
Student(id=2, name=ljq2, tid=1, age=23, teacher=Teacher(student=null, id=0, name=林老师))
Student(id=3, name=ljq3, tid=1, age=23, teacher=Teacher(student=null, id=0, name=林老师))
Student(id=4, name=linjinquan, tid=1, age=22, teacher=Teacher(student=null, id=0, name=林老师))
Student(id=5, name=helloWord2!, tid=2, age=34, teacher=Teacher(student=null, id=0, name=陈老师))
Student(id=6, name=ljq6, tid=1, age=23, teacher=Teacher(student=null, id=0, name=林老师))
Student(id=7, name=ljq7, tid=1, age=23, teacher=Teacher(student=null, id=0, name=林老师))
Student(id=8, name=ljq8, tid=1, age=23, teacher=Teacher(student=null, id=0, name=林老师))
按照结果查询嵌套处理
这种就是多表查询,结果就使用结果集映射
<!--
思路:
1.查询所有的学生信息
2.根据查询出来的学生的tid,寻找对应的老师
这里是按照结果嵌套处理
-->
<!-- 查询学生的id 名字 老师的名字-->
<select id="getStudent2" resultMap="StudentTeacher2">
select *
from student s,
teacher t
where s.tid = t.id
</select>
<!-- 结果集映射-->
<resultMap id="StudentTeacher2" type="Student">
<result column="id" property="id"/>
<result column="name" property="name"/>
<result column="age" property="age"/>
<association property="teacher" javaType="Teacher">
<result column="name" property="name"/>
</association>
</resultMap>
测试
@Test
public void test2() {
SqlSession sqlSession = mybatisUtils.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> student = mapper.getStudent2();
for (Student student1 : student) {
System.out.println(student1);
}
sqlSession.close();
}
一对多处理
<collection property="students" ofType="Student">
返回的是一个集合
比如一个老师拥有多个学生,对于老师而言,老师就是一对多
实体类
@Data
public class Teacher<list> {
private long id;
private String name;
//一个老师对多个学生,也就是一对多
private List<Student> students;
}
@Data
public class Student {
private long id;
private String name;
private int tid;
}
映射文件:
<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--全局核心配置文件 第一步配置核心文件-->
<mapper namespace="com.q.dao.TeacherMapper">
<select id="getTeacher1" resultMap="stu">
select t.name tname, t.id, s.tid, s.name sname
from student s,
teacher t
where s.tid = #{tid}
</select>
<resultMap id="stu" type="Teacher">
<result column="id" property="id"/>
<result column="tname" property="name"/>
<collection property="student" ofType="Student">
<result column="sname" property="name"/>
</collection>
</resultMap>
</mapper>
测试:
@Test
public void teacher01() {
SqlSession sqlSession = mybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher1 = mapper.getTeacher1(1);
System.out.println(teacher1);
sqlSession.close();
}
小结:
1、关联 这也就是多对一
2、集合 这也就是一对多的处理,一个老师有多个学生,就是集合
3、javatype用来指定实体类中的属性类型
4、oftype 用来指定映射到list或者集合中的pojo类型,泛型中的约束类型
这是很重要,关于复杂的查询要使用的标签,一对多和多对一的要牢记;
注意点:
保证sql语句的可读性,保证通俗易懂
属性名和字段的问题
使用日志排查错误