MyBatis复杂查询环境

wc，还是没明白

数据库环境创建

创建的 teacher 和 student 是一对多的关系。要查询的就是所有学生以及对应的老师的名字，显然要组合表。

感觉方法一比较简单，进行原始的 sql 查询，得到的结果根据 resultMap 进行映射即可。映射原则：

对于普通类型直接使用：<result property="name" column="name" />；(property是 pojo 类中的属性，column 是该属性对应数据库字段)

而如果是集合，使用<collection property="students" ofType="student">；

如果是单个对象，使用<association property="teacher" javaType="teacher">

CREATE TABLE teacher(
	id INT(10) NOT NULL,
	NAME VARCHAR(30) DEFAULT NULL,
	PRIMARY KEY (id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO teacher(id, NAME) 
VALUES (1, '秦始皇');

CREATE TABLE student(
	id INT(10),
	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;

INSERT INTO student(id, NAME, tid) VALUES (1, '小明', '1');
INSERT INTO student(id, NAME, tid) VALUES (2, '小红', '1');
INSERT INTO student(id, NAME, tid) VALUES (3, '小张', '1');
INSERT INTO student(id, NAME, tid) VALUES (4, '小李', '1');
INSERT INTO student(id, NAME, tid) VALUES (5, '小王', '1');

查询方法 (多对一)

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.klaus.dao.StudentMapper">

<!--===================      方法一       =======================-->
    <select id="getStudent2" resultMap="StudentTeacher2">
        select s.id sid, s.name name, t.name tname, t.id tid
        from student s, teacher t
        where s.tid = t.id
    </select>

    <resultMap id="StudentTeacher2" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="name"/>
        <association property="teacher" javaType="teacher">
            <result property="name" column="tname"/>
            <result property="id" column="tid"/>
        </association>
    </resultMap>

<!--==================        方法二       =========================-->
    <resultMap id="StudentTeacher" type="Student">
        <result property="id" column="id" />
        <result property="name" column="name" />
        <!-- 复杂的属性 Teacher 要单独处理
             对象：association
             集合：collection
        -->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>

    <select id="getStudent" resultMap="StudentTeacher">
        select *
        from student;
    </select>

    <select id="getTeacher" resultType="teacher">
        select * from teacher where id = #{tid}
    </select>

</mapper>

查询方法(一对多)

一个老师里面有 Student 的 List 集合，是一对多的关系。

要查询指定老师的所有学生有如下两种方法。

public class Student {
    private int id;
    private String name;
    private int tid;
}

public class Teacher {
    private int id;
    private String name;
    private List<Student> students;
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.klaus.dao.TeacherMapper">

    <!--================      方法一       ====================-->
        <select id="getTeacherAndStu2" resultMap="teacherWithStudent2">
        select * from teacher where id = #{id}
    </select>

    <resultMap id="teacherWithStudent2" type="teacher">
        <collection property="students" javaType="ArrayList" ofType="student"
                    select="getStuById" column="id" />
    </resultMap>

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

    <!--================      方法二       ====================-->

    <select id="getTeacherAndStu" resultMap="teacherWithStudent">
        select s.id sid, s.name sname, t.name tname, t.id tid
        from teacher t, student s
        where t.id = s.tid and t.id = #{id}
    </select>

    <resultMap id="teacherWithStudent" type="teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <collection property="students" ofType="student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>
    
</mapper>