MyBatis12 结果集映射

1、准备工作

• 数据库脚本

-- 老师表
create table `teacher`(
id int(10) not null,
name varchar(30) DEFAULT null,
primary key (id)
) engine=INNODB default charset=utf8;
-- 插入数据
insert into teacher values (1,'Jack');
-- 学生表
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;
-- 插入数据
insert into student values (1,'张三',1);
insert into student values (2,'李四',1);
insert into student values (3,'王五',1);
insert into student values (4,'赵六',1);
insert into student values (5,'田七',1);
insert into student values (6,'刀八',1);

• 新建实体类

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

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

• 核心配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!-- 引入外部配置文件，在xml中所有的标签都规定其顺序。properties需要写在xml前面，不然会报错 -->
    <properties resource="mysql-config.properties"/>
    <!-- 设置：配置缓存，懒加载，驼峰，日志实现等功能 -->
    <settings>
        <!-- 开启驼峰映射 -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!-- 配置日志 SLF4J | LOG4J | LOG4J2 | JDK_LOGGING | COMMONS_LOGGING | STDOUT_LOGGING | NO_LOGGING -->
        <setting name="logImpl" value="LOG4J"/>
    </settings>
    <!-- 可以给实体类起别名 -->
    <typeAliases>
        <!-- 给单个类起别名 -->
<!--        <typeAlias type="com.study.pojo.User" alias="User"/>-->
        <!-- 扫描一个包，它的默认别名就是包下的类的类名，首字母小写,如果实体类上用了注解@Alias("hello")，会优先用注解的 -->
        <package name="com.study.pojo"/>
    </typeAliases>
    <!-- 环境配置,可以配置多个环境,只能有一个生效 default指定哪个环境生效 -->
    <environments default="development">
        <environment id="uat">
            <!-- 配置事务管理器，默认都用JDBC -->
            <transactionManager type="MANAGED"/>
            <!-- 数据源 类型有 UNPOOLED|POOLED|JNDI,默认都用POOLED,有池的连接 -->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <!-- 映射器 -->
    <mappers>
<!--        <mapper resource="userMapper.xml"/>-->
        <mapper resource="userMapper.xml"/>
        <mapper resource="TeacherMapper.xml"/>
        <mapper resource="StudentMapper.xml"/>
    </mappers>
</configuration>

2、复杂类型的映射（多对一）

• 接口

public interface StudentMapper {
    List<Student> selectStudentList();
}

• 方式一：按照查询嵌套类型

<mapper namespace="com.study.dao.StudentMapper">
    <resultMap id="studentMap" type="com.study.pojo.Student">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <!-- 属性是teacher 字段是外键 类型是Teacher ,select 嵌套一个查询-->
        <association property="teacher" column="tid" javaType="com.study.pojo.Teacher" select="selectTeacherById"/>
    </resultMap>

    <select id="selectStudentList" resultMap="studentMap">
        select * from student
    </select>
    <select id="selectTeacherById" resultType="com.study.pojo.Teacher">
        select * from teacher where id = #{id}
    </select>
</mapper>

• 方式二：按照结果嵌套类型（一般用这个）

<!-- namespace：绑定一个对应的mapper接口-->
<mapper namespace="com.study.dao.StudentMapper">
    <resultMap id="studentMap" type="com.study.pojo.Student">
        <id property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <id property="id" column="id"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>

    <select id="selectStudentList" resultMap="studentMap">
        select
        s.id as sid,
        s.name as sname,
        t.name as tname,
        t.id
        from student s,teacher t where s.tid = t.id
    </select>

3、复杂类型的映射（一对多）

• 实体类修改

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

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

• 接口

public interface TeacherMapper {
    Teacher selectTeachers(int id);
}

• 方式一：按照结果集嵌套查询

<!-- namespace：绑定一个对应的mapper接口-->
<mapper namespace="com.study.dao.TeacherMapper">

    <resultMap id="baseResultMap" type="Teacher">
        <id property="id" column="id"/>
        <result property="name" column="tname"/>
        <!-- 多个要用collection，注意这里的ofset -->
        <collection property="students" ofType="Student">
            <id property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>

    <select id="selectTeachers" parameterType="int" resultMap="baseResultMap">
        select s.id as sid,s.name as sname,s.tid,t.id,t.name as tname from teacher t,student s where s.tid = t.id and t.id = #{id}
    </select>

</mapper>

• 方式二：按照查询嵌套查询

<mapper namespace="com.study.dao.TeacherMapper">

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

    <select id="selectTeacherById" parameterType="int" resultMap="baseResultMap">
        select * from teacher t where t.id = #{id}
    </select>

    <select id="selectStudentByTid" resultType="Student" parameterType="int">
        select * from student s where s.tid = #{id}
    </select>

</mapper>

注意：javaType 用于映射实体类中属性的类型；ofType 用于映射集合中的泛型类型