MyBatis:collection

Posted on

和association一样，collection用于处理复杂关系。不同的是association用于映射单个对象，而collection用于映射集合对象。

场景

创建student表：

1
2
3
4
5
6
7
8
CREATE TABLE `student` (
     `id` INT(10) NOT NULL,
     `name` VARCHAR(30) DEFAULT NULL,
     `tid` INT(10) DEFAULT NULL,
     PRIMARY KEY (`id`),
     KEY `fktid` (`tid`),
     CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
     ) ENGINE=INNODB DEFAULT CHARSET=utf8

创建teacher表：

1
2
3
4
5
CREATE TABLE `teacher` (
     `id` INT(10) NOT NULL,
     `name` VARCHAR(30) DEFAULT NULL,
     PRIMARY KEY (`id`)
     )ENGINE=INNODB DEFAULT CHARSET=utf8

要求

根据teacher的id查出老师手下的所有学生

MySQL

1
2
3
select t.name tname, s.id sid, s.name sname
from teacher t, student s
where t.id=s.tid and t.id=#{tid}

MyBatis实现

实体类(pojo)

Student.java:

1
2
3
4
5
6
public class Student {
    private int id;
    private String name;
    private int tid;
    <!-- getter,setter,toString -->
}

Teacher.java:

1
2
3
4
5
6
public class Teacher {
    private int id;
    private String name;
    private List<Student> students;
    <!-- getter,setter,toString -->
}

与association的要求不同，此处的要求是根据老师的id属性去查询学生，因此构建实体类时也需要变化。

接口类

用了@Param(“tid”)后，xml中的输入也要注意用tid，xml中会进一步解释。

1
2
3
4
public interface TeacherMapper {
    Teacher getTeacher(@Param("tid") int id);
    Teacher getTeacher2(@Param("tid") int id);
}

TeacherMapper.xml

子查询

先根据输入的id查询老师，然后从根据tid查询出学生

1
2
3
4
5
6
7
8
9
10
11
12
<select id="getTeacher" resultMap="TeacherStudent" >
<!-- id=#{tid}意为接受参数 -->
    select * from teacher where id=#{tid}
</select>

<resultMap id="TeacherStudent" type="Teacher">
    <collection property="students" column="id" javaType="ArrayList" ofType="Student" select="getStudentByTeacherID"/>
</resultMap>

<select id="getStudentByTeacherID" resultType="Student">
    select * from student where tid=#{id}
</select>

此方法需要注意的是collection标签中的column对应的是Teacher类中的id属性（注意resultMap的type是Teacher），javaType应该写ArrayList，因为student属性是一个集合，ofType用于获取集合中的泛型。

结果集嵌套查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<select id="getTeacher2" resultMap="TeacherStudent2">
    select t.name tname, s.id sid, s.name sname
    from teacher t, student s
    <!-- id=#{tid}意为接受参数 -->
    where t.id=s.tid and t.id=#{tid}
</select>

<resultMap id="TeacherStudent2" type="Teacher">
    <result property="name" column="tname"/>
    <result property="id" column="tid"/>
    <!-- 集合中泛型的信息，用ofType获取 -->
    <collection property="students" ofType="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <result property="tid" column="tid"/>
    </collection>
</resultMap>

同association，先查询出结果，然后再用resultMap进行说明。