前言
mybatis中查询时一对多和多对一代码的一些总结。
多对一的两种思路:
<!--
思路1:
按照结果嵌套处理
-->
<select id = "getStudent2" resultMap="StudentTeacher2">
select s.id sid , s.name sname , t.name tname
from student s,teacher t
where s.tid = t.id;
</select>
<resultMap id="StudentTeacher2" type="com.kuang.pojo.Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<!--多对一用association,表示一种关系-->
<association property="teacher" javaType="com.kuang.pojo.Teacher">
<result property="name" column="tname"/>
</association>
</resultMap>
<!--
思路2:
1.查询所有学生信息
2.根据查询出来的学生的tid,寻找对应的老师!子查询
-->
<select id="getStudent" resultMap="StudentTeacher">
select * from student;
</select>
<resultMap id = "StudentTeacher" type="com.kuang.pojo.Student">
<result property="id" column="id"/>
<result property="name" column="name"/>
<association property="teacher" column="tid" javaType="com.kuang.pojo.Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="com.kuang.pojo.Teacher">
select * from teacher where id = #{id}
</select>
一对多的两种思路:
<mapper namespace="com.kuang.dao.TeacherMapper">
<select id = "getTeacher" resultMap="TeacherStudent">
select s.id sid,s.name sname,t.name tname,t.id tid
from student s,teacher t
where s.tid = t.id and t.id = #{tid}
</select>
<resultMap id = "TeacherStudent" type="com.kuang.pojo.Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<!--把学生放在一个集合中-->
<collection property="students" ofType="com.kuang.pojo.Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
<!-- 子查询-->
<select id= "getTeacher2" resultMap="TeacherStudent2">
select * from mybatis.teacher where id = #{tid}
</select>
<resultMap id="TeacherStudent2" type="com.kuang.pojo.Teacher">
<collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id"/>
</resultMap>
<select id="getStudentByTeacherId" resultType="com.kuang.pojo.Student">
select * from mybatis.student where tid = #{tid}
</select>