mybatis 关联查询复习
多对1 association
<!-- resultMap结果集映射 -->
<select id="getAllStudents" resultMap="StudentAndTeacher">
select * from student
</select>
<resultMap id="StudentAndTeacher" type="Student">
<id 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>
1对多
集合:collection
javaType:指定属性的类型
集合中的泛型信息,我们使用ofType
collection标签中的column是数据库对应表的字段,
因为是根据teacher表查询的,所以这里column对应的是teacher表中的id
<select id="getTeacherById" resultMap="TeacherAndStudents">
select * from teacher where id = #{id}
</select>
<resultMap id="TeacherAndStudents" type="Teacher">
<result property="id" column="id"/>
<collection property="students" javaType="ArrayList" ofType="Student" select="getStudentsByTeacherId" column="id">
</collection>
</resultMap>
<select id="getStudentsByTeacherId" resultType="Student">
select * from student where tid = #{id}
</select>
结果嵌套
<select id="getStudentsByConditions1" resultMap="Query_nesting" parameterType="map">
select * from sheet
</select>
<resultMap id="Query_nesting" type="Sheet">
<association property="student" column="studentId" javaType="Student" select="getStudentById">
</association>
<association property="subject" column="subjectId" javaType="Subject" select="getSubjectById">
</association>
</resultMap>
<select id="getStudentById" resultType="Student">
select * from student
where id = #{id}
</select>
<!--根据id查询课程-->
<select id="getSubjectById" resultType="Subject">
select * from subject
where id = #{id}
</select>
查询嵌套
<resultMap id="Result_nesting" type="Sheet">
<result property="student.id" column="stuId"/>
<result property="student.name" column="stuName"/>
<result property="subject.name" column="subName"/>
<result property="score" column="score"/>
</resultMap>
<select id="getStudentsByConditions2" resultMap="Result_nesting" parameterType="map">
select
stu.id stuId,
stu.name stuName,
sub.name subName,
score
from student stu
right join sheet s
on stu.id = s.studentId
inner join subject sub
on s.subjectId = sub.id
</select>
左 右 连接 内连接
左连接是以左表为主,右表显示出来,没有的用null,左表没有的不显示,两表都有的就显示一个
右连接是以右表为主,左表显示出来,没有的用null,右表没有的不显示,两表都有的就显示一个
内连接 是俩表都查出来(两表都有关联id的数据,一表有一表没有的不显示),两表都有的字段会显示两个列
三表关联查询