11. 复杂查询环境搭建

空~2022年8月30日

mybatis

大约 4 分钟

11. 复杂查询环境搭建

多对一处理

多个学生对应一个老师
对于学生而言,多个学生管理一个老师(多对一)[关联]
对于老师而言, 一个老师有很多学生(一对多)[集合]

测试环境搭建:

新建实体类

@Data
public class Student {
    private int id;
    private String name;
    private Teacher teacher;
}

@Data
public class Teacher {
    private int id;
    private String name;
}

新建 Mapper 接口

public interface StudentMapper {
    /**
* 获取全部学生
*
* @return 学生
*/
    List<Student> getStudent();
}

public interface TeacherMapper {
    /**
* 通过id查询老师
*
* @param id 老师id
* @return 老师
*/
    Teacher getTeacherById(int id);
}

新建 Mapper.xml 文件

按照查询嵌套处理

<?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="dao.StudentMapper">
    <select id="getStudent" resultMap="student">
        select id, name, tid
        from student
    </select>
    <resultMap id="student" type="student">
        <!--association
        – 一个复杂类型的关联；许多结果将包装成这种类型
        嵌套结果映射 – 关联可以是 resultMap 元素，或是对其它结果映射的引用
        javaType
        一个 Java 类的全限定名，或一个类型别名
        如果你映射到一个 JavaBean，MyBatis 通常可以推断类型。
        然而，如果你映射到的是 HashMap，那么你应该明确地指定 javaType 来保证行为与期望的相一致。
        -->
        <association property="teacher" column="tid" javaType="teacher" select="getTeacherById"/>
    </resultMap>
    <select id="getTeacherById" resultType="teacher">
        select id, name
        from teacher
        where id = #{id};
    </select>
</mapper>

按照结果嵌套处理

<?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="dao.StudentMapper">
    <select id="getStudent" resultMap="student">
        select s.id sid, s.name sname, t.id tid, t.name tname
        from student s,
        teacher t
        where t.id = s.tid;
    </select>
    <resultMap id="student" type="student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="teacher">
            <result property="id" column="tid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>
</mapper>

Mpper.xml 文件的结构最好应该和接口的文件结构相同

复杂查询环境

在核心配置文件中绑定 Mapper.xml 文件或接口
```
<mappers>
    <package name="dao"/>
</mappers>
```

测试

@Test
public void getStudent() {
    SqlSession session = MybatisUtils.getSession();
    StudentMapper mapper = session.getMapper(StudentMapper.class);
    List<Student> studentList = mapper.getStudent();
    for (Student student : studentList) {
        System.out.println(student);
    }
    session.close();
}

一对多处理

一个老师对应多个学生

按照结果嵌套处理

<?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="dao.TeacherMapper">
    <select id="getTeacherById" resultMap="teacher">
        select t.id tid, t.name tname, s.id sid, s.name sname
        from teacher t,
        student s
        where t.id = #{id}
        and t.id = s.tid;
    </select>
    <resultMap id="teacher" type="teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <!--collection –
        一个复杂类型的集合
        嵌套结果映射 – 集合可以是 resultMap 元素，或是对其它结果映射的引用
        ofType
        集合中的泛型信息
        -->
        <collection property="studentList" ofType="student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
        </collection>
    </resultMap>
</mapper>

环境搭建

实体类

@Data
public class Teacher {
    private int id;
    private String name;
    private List<Student> studentList;
}

@Data
public class Student {
    private int id;
    private String name;
    private Teacher teacher;
}

接口

public interface StudentMapper {
    /**
   * 获取全部学生
   *
   * @return 学生
   */
    List<Student> getStudent();
}

public interface TeacherMapper {
    /**
   * 通过id查询老师
   *
   * @param id 老师id
   * @return 老师
   */
    Teacher getTeacherById(int id);
}

Mapper.xml

结果查询

<?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="dao.TeacherMapper">
    <select id="getTeacherById" resultMap="teacher">
        select t.id tid, t.name tname, s.id sid, s.name sname
        from teacher t,
        student s
        where t.id = #{id}
        and t.id = s.tid;
    </select>
    <resultMap id="teacher" type="teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <!--collection –
        一个复杂类型的集合
        嵌套结果映射 – 集合可以是 resultMap 元素，或是对其它结果映射的引用
        ofType
        集合中的泛型信息
        -->
        <collection property="studentList" ofType="student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
        </collection>
    </resultMap>
</mapper>

嵌套查询

<?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="dao.TeacherMapper">
    <select id="getTeacherById" resultMap="teacher">
        select id, name
        from teacher
        where id = #{id};
    </select>
    <resultMap id="teacher" type="teacher">
        <result property="id" column="id"/>
        <collection property="studentList" column="id" ofType="student" select="studentByTeacher"/>
    </resultMap>
    <select id="studentByTeacher" resultType="student">
        select *
        from student
        where tid = #{tid};
    </select>
</mapper>

测试类

@Test
public void getTeacherById() {
    SqlSession session = MybatisUtils.getSession();
    TeacherMapper mapper = session.getMapper(TeacherMapper.class);
    Teacher teacherById = mapper.getTeacherById(1);
    System.out.println(teacherById);
    for (Student student : teacherById.getStudentList()) {
        System.out.println(student);
    }
    session.close();
}

总结

association: 多对一
collection: 一对多
javaType & ofType
1. javaType 指定实体类中属性的类型
2. ofType 指定 List 集合中的约束类型(泛型类型)或映射到 Lsit 集合中的 pojo 类型

11. 复杂查询环境搭建

# 11. 复杂查询环境搭建

# 多对一处理

# 一对多处理

# 总结

11. 复杂查询环境搭建

多对一处理

一对多处理

总结