复杂查询
声明:本文章属于学习笔记,根据狂神说的Mybatis编写
Mybatis在线文档:https://mybatis.org/mybatis-3/zh/index.html
一丶复杂查询的环境搭建
首先我们要明白一个问题:什么是一对多,什么是多对一。
我们可以看这样的一张图:
这个图中代表多个学生和一个老师:
对于学生这边而言,多个学生关联这个一个老师(关联)
对于老师这边而言,一个老师带着多个学生(集合)
数据库的搭建:
CREATE TABLE `teacher`(
`id` INT(10) NOT NULL,
`name` VARBINARY(30) DEFAULT NULL,
PRIMARY KEY(`id`)
)
INSERT INTO teacher(`id`,`name`) VALUES(1,'kdy');
CREATE TABLE `student`(
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
`tid` INT(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fktid` (`tid`),
CONSTRAINT `fktid` FOREIGN KEY(`tid`) REFERENCES `teacher` (`id`)
)
运行结果:
在这里我们首先是没有向这两张表中添加任何数据的。
创建实现类以及相应的接口:
@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 TeacherMapper {
@Select("select * from teacher where id=#{tid}")
Teacher getTeacherbyid(@Param("tid") int id);
}
public interface StudentMapper {
}
之后进行Mapper.xml文件的配置:
mybatis-config.xml文件通过类绑定接口:
<mappers>
<mapper class="com.kdy.dao.StudentMapper"/>
<mapper class="com.kdy.dao.TeacherMapper"/>
</mappers>
测试类进行测试:
@Test
public void tset1() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacherbyid = mapper.getTeacherbyid(1);
System.out.println(teacherbyid);
sqlSession.close();
}
运行结果:
我们可以看见这样环境就搭建成功了,这也是我们学习多对一和一对多的前提环境搭建。
二丶多对一的处理
首先我们看这样一条sql语句:
SELECT student.`id`, student.`name` ,teacher.`name` FROM student,teacher WHERE student.`tid`=teacher.`id`
运行结果:
我们可以看见,运行结果是这样的。
但是我们如果用Mybatis集成该怎么实现呢?
创建接口:
public interface StudentMapper {
@Select("SELECT student.`id`, student.`name` ,teacher.`name` FROM student,teacher WHERE student.`tid`=teacher.`id`")
List<Student> getstu();
}
测试类测试:
@Test
public void test2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> getstu = mapper.getstu();
for (Student student : getstu) {
System.out.println(student);
}
sqlSession.close();
}
运行结果:
我们可以看见我们额运行结果是这样的,teacher为null!主要的原因为teacher为一个对象,而id,name都为字段名称。那么我们应该怎么处理这种情况呢?其实这就要用到了我们的Mapper.xml文件了。
我们要清楚:这条语句是如何执行的:
1丶首先要执行查询student表
2丶查询teacher表
3丶通过student.tid
=teacher.id
建立链接
所以我们在编写Mapper,xml文件的时候,我们应该这样去写:
<mapper namespace="com.kdy.dao.StudentMapper ">
<select id="getstu" resultType="Student" >
select * from student
</select>
<select id="getTeacherbyid" resultType="Teacher">
select * from student where id=#{id}
</select>
</mapper>
但是我们这么将两个语句建立链接呢?
这就用到了resultmap结果集映射了!
(子查询)
<mapper namespace="com.kdy.dao.StudentMapper">
<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 property="teacher" column="tid" javaType="Teacher" select="getTeacherbyid"/>
</resultMap>
<select id="getTeacherbyid" resultType="Teacher">
select * from teacher where id=#{id}
</select>
</mapper>
因为是对象和字段照应,所以我们要用:
<association property="teacher" column="tid" javaType="Teacher" select="getTeacherbyid"/>
javaType=“Teacher” select="getTeacherbyid"也就对象着 <select id="getTeacherbyid" resultType="Teacher">
。
三丶一对多处理
首先我们还是要进行环境的搭建:
实体类Teacher和Student:
@Data
public class Teacher {
private int id;
private String name;
//一个老师带着多个学生
List<Student> students;
}
@Data
public class Student {
private int id;
private String name;
private int tid;
}
创建接口:
mybatis-config.xml文件绑定接口:
<mappers>
<mapper class="com.kdy.dao.StudentMapper"/>
<mapper class="com.kdy.dao.TeacherMapper"/>
</mappers>
我们把环境搭建好之后,就要进行测试环境搭建是否成功:
TeacherMapper.xml:
<mapper namespace="com.kdy.dao.TeacherMapper">
<select id="getTeacher" resultType="Teacher">
select * from mybatis.teacher
</select>
</mapper>
测试类测试:
@Test
public void test(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
List<Teacher> teacher = mapper.getTeacher();
for (Teacher teachers: teacher) {
System.out.println(teachers);
}
sqlSession.close();
}
运行结果:
我们可以看见环境已经搭建成功了。
我们还是同样的问题,怎么解决这个students为null的问题?
那么我们还要借助Mapper.xml(嵌套查询):
<!--按结果嵌套查询-->
<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"/>
<!--复杂的属性,我们需要单独处理 对象: 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>
测试类测试:
public void test1(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher(1);
System.out.println(teacher);
sqlSession.close();
}
运行结果:
可以看见我们这个是成功的。