Mybatis-复杂查询
1. 测试环境搭建
- 导入lombok
- 新建实体类Teacher, Student
- 建立Mapper接口
- 建立Mapper.xml文件
- 在核心配置中绑定注册Mapper接口或者文件
- 测试查询是否能够成功
2. 复杂查询
实体类:
// 学生
package com.kangzhu.pojo;
import lombok.Data;
@Data
public class Student {
private int id;
private String name;
// 学生需要关联一个老师
private Teacher teacher;
}
// 老师
package com.kangzhu.pojo;
import lombok.Data;
@Data
public class Teacher {
private int id;
private String name;
}
note:student表中有tid字段,关联到teacher表中的id字段;
现在想要查询所有学生:(使用sql语句)
select s.id, s.name, t.name from student s, teacher t where s.tid = t.id
使用Mybatis(按照之前的方法):
-
编写接口
public interface StudentMapper { List<Student> getStudents(); }
-
编写Mapper.xml
<select id="getStudents" resultType="Student"> select * from student </select>
-
测试
@Test void test() { SqlSession sqlSession = MybatisUtils.getSqlSession(); StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); for (Student student : mapper.getStudents()) { System.out.println(student.getTeacher()); } sqlSession.close(); }
测试发现我们找不到对应的Teacher;
1. 按照查询嵌套处理:(子查询)
通过这种方式可以得到整个teacher对象;
<select id="getStudents" resultMap="StudentTeacher">
select * from student
</select>
<resultMap id="StudentTeacher" type="Student">
<result property="id" column="id"/>
<result property="name" column="name"/>
<!--复杂的属性,我们需要单独处理:
对象:association
集合:collection
-->
<!--javaType,属性的类-->
<!--select,嵌套查询-->
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * from teacher where id = #{tid}
</select>
2. 按照结果嵌套处理:(联表查询)
这种方式得到的是此学生关联的teacher的某个属性;
这个例子中teacher的id是查不到的;
<select id="getStudents" resultMap="StudentTeacher">
select s.id sid, s.name sname, t.name tname
from student s, teacher t
where s.tid = t.id
</select>
<resultMap id="StudentTeacher" type="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<association property="teacher" javaType="Teacher">
<result property="name" column="tname"/>
</association>
</resultMap>