MyBatis的集合查询

来源：互联网发布：爱动体感运动机知乎编辑：程序博客网时间：2024/05/16 03:29

集合（collection）元素用来处理“一对多”的关系，集合元素和联合非常相似，还是上一篇中的数据张三丰有武当七侠，王重阳有全真七子
来看第一种子查询方式：

<resultMap id="teacherResult" type="com.zengyg.myibatis.dto.TeacherInfo">    <id property="teacherId" column="teacher_id" />    <result property="name" column="name" />    <result property="sex" column="sex" />    <collection property="students" column="teacher_id" javaType="ArrayList"  ofType="com.zengyg.myibatis.dto.StudentInfo"        select="querystudentByTeacherId" >    </collection></resultMap><select id="querystudentByTeacherId" parameterType="int" resultType="com.zengyg.myibatis.dto.StudentInfo">    select student_id as studentId ,name ,sex    from student    where teacher_id = #{teacher_id}</select><select id="queryById" parameterType="int" resultMap="teacherResult">    select teacher_id ,name ,sex    from teacher    where teacher_id = #{teacherId}</select>

通过子查询querystudentByTeacherId查询学生的列表，这里新增了个“ofType”属性，这个属性用来区分JavaBean（或字段）属性类型和集合包含的类型来说是很重要的，这几javaType=”ArrayList” 表示JavaBean属性类型，ofType=”com.zengyg.myibatis.dto.StudentInfo” 表示ArrayList里面是StudentInfo，column也可以传入多个值，方式同联合，同样子查询也存在N+1的问题，在查询结果会集合时应该慎用
另外一种外链接的方式：

<resultMap id="teacherResultUseJoin" type="com.zengyg.myibatis.dto.TeacherInfo">    <id property="teacherId" column="teacher_id" />    <result property="name" column="name" />    <result property="sex" column="sex" />    <collection property="students" javaType="ArrayList"  ofType="com.zengyg.myibatis.dto.StudentInfo" >        <id property="studentId" column="student_id" />        <id property="sex" column="sex_s" />        <id property="name" column="name_s" />        <id property="teacherId" column="teacher_id" />    </collection></resultMap><select id="queryTeacherByIdUseJoin" parameterType="int"    resultMap="teacherResultUseJoin">    select t.teacher_id ,t.name ,t.sex,student_id,s.name as name_s,s.sex as sex_s    from teacher t left join student s on t.teacher_id = s.teacher_id    where t.teacher_id = #{teacherId}</select>

比较简单，不做过多解释
以上完整代码地址：https://git.oschina.net/zengyg/J2EEAPIStudy.git

0 0