在这一篇文章中我们讲解Mybatis的一对多查询
在一对多的查询过程中有两种查询方式,
一种是单次查询,也就是一次查询出所有的结果,这种查询的优点就是速度比较快,但是SQL的可复用性就降低;
另一种是嵌套查询,这种查询就是根据主查询的结果进行多次查询,这种查询速度比较低。
下面我们先对第一种进行讲解,
首先创建javaBean
package com.mybatis.in;
public class Student {
private String sId;
private String name;
public String getsId() {
return sId;
}
public void setsId(String sId) {
this.sId = sId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
package com.mybatis.in;
import java.util.List;
public class Course {
private String courseId;
private String courseName;
private String courseType;
private ClassRoom classRoom;
private List<Student> students;
public String getCourseId() {
return courseId;
}
public void setCourseId(String courseId) {
this.courseId = courseId;
}
public String getCourseName() {
return courseName;
}
public void setCourseName(String courseName) {
this.courseName = courseName;
}
public String getCourseType() {
return courseType;
}
public void setCourseType(String courseType) {
this.courseType = courseType;
}
public void setClassRoom(ClassRoom classRoom) {
this.classRoom = classRoom;
}
public ClassRoom getClassRoom() {
return classRoom;
}
public void setStudents(List<Student> students) {
this.students = students;
}
public List<Student> getStudents() {
return students;
}
}
下面进行配置
<resultMap id="courseClassRoom" type="Course">
<id property="courseId" column="courseId"/>
<result property="courseType" column="courseType"/>
<result property="courseName" column="courseName"/>
<association property="classRoom" resultMap="classRoom"></association>
<collection property="students" ofType="Student" javaType="java.util.ArrayList">
<id property="sId" column="studentId"/>
<result property="name" column="studentName"/>
</collection>
</resultMap>
<select id="getCoursesClassRoom" resultMap="courseClassRoom">
<![CDATA[select c.courseId courseId,c.courseType courseType,c.courseName courseName,
room.roomId roomId,room.roomName roomName,room.area area,student.id sId, student.name studentName
from course c
inner join classroom room
on c.roomId=room.roomId
left join student
on student.courseId = c.courseId]]>
</select>
这样相信大家都看的明白,就不过多赘述。
下面看一下嵌套查询
这个只需要改一下配置文件即可
<!-- one to one and one to many nested query -->
<resultMap id="courseStudentNested" type="Course">
<id property="courseId" column="courseId"/>
<result property="courseType" column="courseType"/>
<result property="courseName" column="courseName"/>
<association property="classRoom" resultMap="classRoom"></association>
<collection property="students" ofType="Student" javaType="java.util.ArrayList"
column="courseId" select="getStudents">
</collection>
</resultMap>
<resultMap type="Student" id="student">
<id property="sId" column="studentId"/>
<result property="name" column="studentName"/>
</resultMap>
<select id="getStudents" parameterType="String" resultMap="student">
<![CDATA[Select student.id sId, student.name studentName from student where courseId=#{id}]]>
</select>
<select id="getCoursesStudentNested" resultMap="courseStudentNested">
<![CDATA[select c.courseId courseId,c.courseType courseType,c.courseName courseName,
room.roomId roomId,room.roomName roomName,room.area area
from course c
inner join classroom room
on c.roomId=room.roomId]]>
</select>
然后在接口中添加一个方法
package com.mybatis.in;
import java.util.List;
public interface CourseMapper {
public List<Course> getCourses();
public List<Course> getCoursesClassRoom();
public List<Course> getCourseStudents();
public List<Course> getCoursesStudentNested();
public List<Student> getStudents(String courseId);
}
这样就可以进行嵌套查询了,这里有一个问题,如果一个对象里面有一个集合,而集合中包含的对象是他自己的时候该怎么样来查询呢??仅仅是吧 connection的select属性改成他自身吗??那么我们下一篇来揭开答案