一、问题描述
假设有学生表、课程表、选课关系表,根据学生的学号查询学生的选课情况。
学生表:
课程表:
选课关系表:
二、解决方法
首先,需要在Student类中添加List<lesson> lessonList对象属性:
接着,在Studentmapper.xml中新建ResultMap:
<resultMap id="LessonsByStuId" type="com.neusoft.entity.Student" >
<id column="studentid" property="studentid" jdbcType="VARCHAR" />
<result column="studentname" property="studentname" jdbcType="VARCHAR" />
<result column="studentsex" property="studentsex" jdbcType="VARCHAR" />
<result column="entertime" property="entertime" jdbcType="DATE" />
<result column="pwd" property="pwd" jdbcType="VARCHAR" />
<result column="dept" property="dept" jdbcType="VARCHAR" />
<result column="class0" property="class0" jdbcType="VARCHAR" />
<collection property="lessonList" ofType="com.neusoft.entity.Lesson">
<id column="lessonid" property="lessonid" jdbcType="VARCHAR" />
<result column="name" property="name" jdbcType="VARCHAR" />
<result column="dept" property="dept" jdbcType="VARCHAR" />
<result column="grade" property="grade" jdbcType="VARCHAR" />
<result column="mark" property="mark" jdbcType="VARCHAR" />
<result column="type" property="type" jdbcType="VARCHAR" />
</collection>
</resultMap>
然后通过左连接查询即可完成查询:
<select id="selectLessonsById" resultMap="LessonsByStuId" parameterType="java.lang.String" >
select *
from student
left join courseselect on student.studentid=courseselect.studentid
left join lesson on courseselect.courseTeachid=lesson.lessonid
where student.studentid = #{studentid,jdbcType=VARCHAR}
</select>