mybatis关联查询复习

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的数据,一表有一表没有的不显示),两表都有的字段会显示两个列
三表关联查询