MyBatis中复杂的结果映射

1.多对一的处理
多对一的理解：

• 多个学生对应一个老师
• 如果对于学生这边，就是一个多对一的现象，即从学生这边关联一个老师！

实体类的编写

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

@Data 
public class Student { 
	private int id; 
	private String name; 
	//多个学生可以是同一个老师，即多对一 
	private Teacher teacher; 
}

实体类对应的Mapper接口

package com.liu.mapper;

import com.liu.pojo.Student;
import java.util.List;

/**
 * @author liucong
 * @date 2020/10/4 - 9:29
 */
public interface StudentMapper {

    //查询所有的学生信息，以及对应的老师的信息！
    List<Student> getStudent();

    List<Student> getStudent2();
}

package com.liu.mapper;

import com.liu.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * @author liucong
 * @date 2020/10/4 - 9:29
 */
public interface TeacherMapper {
    @Select("select * from teacher where id=#{id}")
    Teacher getTeacher(@Param("id") int id);
}

Mapper接口对应的 mapper.xml配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.liu.mapper.StudentMapper">
<!--子查询
    思路：
        1.查询所有的学生信息
        2.根据查询出来的学生的tid，寻找对应的老师! 
    -->
    <select id="getStudent" resultMap="studentTeacher">
        select * from mybatis.student;
    </select>
    <resultMap id="studentTeacher" type="student">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <!--复杂的属性，需要单独处理-->
        <!--对象：association   集合：collection-->
        <association property="teacher" column="tid" javaType="teacher" select="getTeacher"/>
    </resultMap>

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

</mapper>

测试

public class MyTest {
	@Test
    public void test02(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> students = mapper.getStudent();
        for (Student student : students) {
            System.out.println(student);
        }
        sqlSession.close();
    }
}

除了上面这种方式，还有其他思路吗？
我们还可以按照结果进行嵌套处理；

Mapper接口对应的 mapper.xml配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.liu.mapper.StudentMapper">

    <!--按照结果嵌套处理-->
    <select id="getStudent2" resultMap="studentTeacher2">
        select s.id sid,s.name sname,t.name tname,t.id tid
        from student s,teacher t
        where s.tid=t.id;
    </select>
    <resultMap id="studentTeacher2" type="student">
        <id property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="teacher">
            <id property="id" column="tid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>
</mapper>

测试

	@Test
    public void test03(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> students = mapper.getStudent2();
        for (Student student : students) {
            System.out.println(student);
        }
        sqlSession.close();
    }

小结：

• 按照查询进行嵌套处理就像SQL中的子查询
• 按照结果进行嵌套处理就像SQL中的联表查询

2.一对多的处理

一对多的理解：

• 一个老师拥有多个学生
• 如果对于老师这边，就是一个一对多的现象，即从一个老师下面拥有一群学生（集合）！

实体类编写

package com.liu.pojo;

import lombok.Data;
/**
 * @author liucong
 * @date 2020/10/4 - 9:25
 */
@Data
public class Student {
    private int id;
    private String name;
    private int tid;
}

package com.liu.pojo;

import lombok.Data;
import java.util.List;
/**
 * @author liucong
 * @date 2020/10/4 - 9:27
 */
@Data
public class Teacher {
    private int id;
    private String name;
    //一个老师拥有多个学生
    private List<Student> students;
}

TeacherMapper接口编写

package com.liu.mapper;

import com.liu.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
import java.util.List;
/**
 * @author liucong
 * @date 2020/10/4 - 9:29
 */
public interface TeacherMapper {
    //获取指定老师的所有学生及老师信息
    Teacher getTeacher2(@Param("id") int id);

    //获取指定老师的所有学生及老师信息
    Teacher getTeacher3(@Param("id") int id);
}

编写接口对应的Mapper配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.liu.mapper.TeacherMapper">
	<select id="getTeacher2" resultMap="teacherStudent">
        select t.id tid,t.name tname,s.id sid,s.name sname
        from teacher t,student s
        where t.id=s.tid and t.id=#{id}
    </select>

    <resultMap id="teacherStudent" type="teacher">
        <id property="id" column="tid"/>
        <result property="name" column="tname"/>
        <!--javaType=""指定属性的类型-->
        <!--集合中的泛型信息，我们使用ofType-->
        <collection property="students" ofType="student">
            <id property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>
</mapper>

测试

	@Test
    public void test02(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher2(1);
        System.out.println(teacher);
        sqlSession.close();
    }

使用子查询

接口对应的Mapper配置文件

	<select id="getTeacher3" resultMap="teacherStudent2">
        select * from mybatis.teacher where id=#{id}
    </select>
    <resultMap id="teacherStudent2" type="teacher">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <collection property="students" javaType="ArrayList" ofType="student" select="getStudentByTeacherId" column="id"/>
    </resultMap>

    <select id="getStudentByTeacherId" resultType="student">
        select * from mybatis.student where tid=#{tid}
    </select>

测试

	@Test
    public void test03(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher3(1);
        System.out.println(teacher);
        sqlSession.close();
    }

小结

• 关联-association
• 集合-collection
• 所以association是用于一对一和多对一，而collection是用于一对多的关系
• JavaType和ofType都是用来指定对象类型的
  • JavaType是用来指定pojo中属性的类型
  • ofType指定的是映射到list集合属性中pojo的类型。