mybatis一对多mapper文件如何配置

mybatis想来大家不陌生吧，但是不知道大家对mybatis中的一对多了解吗？

老生常谈的话题，学生-老师关系：一个老师对应多个学生。首先建表t_student、t_teacher表（没有用外键，之前一直以为一对多、一对一必须要外键呢，然而并不需要，哈哈哈）

表对应的实体类关系t_student对应Student.class、t_teacher对应Teacher.class

package org.myself.pojo;

import java.io.Serializable;

/**
 * Created by zhao_xinhu
 * On 2018/8/30
 * 学生实体类
 */
public class Student implements Serializable{

    private String studentId;

    private String studentName;

    private Byte studentAge;

    private Boolean studentSex;

    public String getStudentId() {
        return studentId;
    }

    public void setStudentId(String studentId) {
        this.studentId = studentId;
    }

    public String getStudentName() {
        return studentName;
    }

    public void setStudentName(String studentName) {
        this.studentName = studentName;
    }

    public Byte getStudentAge() {
        return studentAge;
    }

    public void setStudentAge(Byte studentAge) {
        this.studentAge = studentAge;
    }

    public Boolean getStudentSex() {
        return studentSex;
    }

    public void setStudentSex(Boolean studentSex) {
        this.studentSex = studentSex;
    }

    public Student() {
    }

    public Student(String studentId, String studentName, Byte studentAge, Boolean studentSex) {
        this.studentId = studentId;
        this.studentName = studentName;
        this.studentAge = studentAge;
        this.studentSex = studentSex;
    }
}

package org.myself.pojo;

import java.io.Serializable;

/**
 * Created by zhao_xinhu
 * On 2018/8/30
 * 对应老师表
 */
public class Teacher implements Serializable {

    private String teacherId;

    private String teacherName;

    private Boolean teacherSex;

    private Byte teacherAge;

    public String getTeacherId() {
        return teacherId;
    }

    public void setTeacherId(String teacherId) {
        this.teacherId = teacherId;
    }

    public String getTeacherName() {
        return teacherName;
    }

    public void setTeacherName(String teacherName) {
        this.teacherName = teacherName;
    }

    public Boolean getTeacherSex() {
        return teacherSex;
    }

    public void setTeacherSex(Boolean teacherSex) {
        this.teacherSex = teacherSex;
    }

    public Byte getTeacherAge() {
        return teacherAge;
    }

    public void setTeacherAge(Byte teacherAge) {
        this.teacherAge = teacherAge;
    }

    public Teacher() {
    }

    public Teacher(String teacherId, String teacherName, Boolean teacherSex, Byte teacherAge) {
        this.teacherId = teacherId;
        this.teacherName = teacherName;
        this.teacherSex = teacherSex;
        this.teacherAge = teacherAge;
    }
}

有人发现我的实体类中没有对应的关系（我习惯表单独对应实体类），如果需要老师对应学生的我一般单独封装成下面这样（纯属自己习惯）:

package org.myself.object;

import org.myself.pojo.Student;
import org.myself.pojo.Teacher;

import java.io.Serializable;
import java.util.List;

/**
 * Created by zhao_xinhu
 * On 2018/8/30
 * 老师（包含老师对应的多个学生）信息表
 */
public class TeacherStudentObj extends Teacher implements Serializable {

    private List<Student> students;

    public List<Student> getStudents() {
        return students;
    }

    public void setStudents(List<Student> students) {
        this.students = students;
    }
}

只要是需要一对多关系的，就用单独封装的实体类，如果不需要一对多的，就用原始的pojo类。

下面是dao接口（随便写的两个测试方法）

package org.myself.dao;

import org.apache.ibatis.annotations.Param;
import org.myself.object.TeacherStudentObj;

/**
 * Created by zhao_xinhu
 * On 2018/8/30
 */
public interface StudentDao {
    //1）一对多
    TeacherStudentObj getTeacherStudentObjByTeacherId(@Param(value = "teacherId") String teacherId);
    //2）一对多
    TeacherStudentObj getTeacherStudentById(@Param(value = "teacherId") String teacherId);
}

mapper才是重点

1. 一对多不用select的方式：

<?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="org.myself.dao.StudentDao">

    <resultMap id="teacherStudent" type="org.myself.object.TeacherStudentObj">
        <id column="teacherId" property="teacherId" javaType="java.lang.String"></id>
        <result column="teacherName" property="teacherName" javaType="java.lang.String"></result>
        <result column="teacherSex" property="teacherSex" javaType="java.lang.Boolean"></result>
        <result column="teacherAge" property="teacherAge" javaType="java.lang.Byte"></result>
        <!-- 一对多collection中一定要用ofType -->
        <collection property="students" ofType="org.myself.pojo.Student">
            <id column="studentId" property="studentId" javaType="java.lang.String"></id>
            <result column="studentName" property="studentName" javaType="java.lang.String"></result>
            <result column="studentAge" property="studentAge" javaType="java.lang.Byte"></result>
            <result column="studentSex" property="studentSex" javaType="java.lang.Boolean"></result>
        </collection>
    </resultMap>
    <sql id="student">
        ${tableName}.student_id AS studentId,
        ${tableName}.student_name AS studentName,
        ${tableName}.student_age AS studentAge,
        ${tableName}.student_sex AS studentSex
    </sql>
    <sql id="teacher">
        ${tableName}.teacher_id AS teacherId,
        ${tableName}.teacher_name AS teacherName,
        ${tableName}.teacher_sex AS teacherSex,
        ${tableName}.teacher_age AS teacherAge
    </sql>
    <select id="getTeacherStudentObjByTeacherId" resultMap="teacherStudent">
        SELECT <include refid="student"><property name="tableName" value="ts"></property></include>,
                <include refid="teacher"><property name="tableName" value="tt"></property></include>
        FROM t_student ts JOIN t_teacher tt
        ON tt.teacher_id = #{teacherId} AND ts.teacher_id = tt.teacher_id
    </select>
</mapper>

上面需要注意的地方就是collection中一定要用ofType，如果是association用javaType，至于ofType和javaType的区别自行百度，还有一个地方需要注意的，column和property的指向问题，property指的是实体类中的属性名称（这个毫无疑问），但是column有人搞不清楚，反正我总是搞晕，在这再声明一遍，也使自己加深印象吧。column指的是你查询之后的映射名称（拿上你的sql去数据库中执行一遍看看你查询结果上显示的字段没错就是他，并不是你表中的字段名）

我mapper文件的sql语句：

SELECT <include refid="student"><property name="tableName" value="ts"></property></include>,
                <include refid="teacher"><property name="tableName" value="tt"></property></include>
        FROM t_student ts JOIN t_teacher tt
        ON tt.teacher_id = #{teacherId} AND ts.teacher_id = tt.teacher_id

拿上上面的sql语句，去数据库执行之后的结果：

没错就是红色标记中的名称。

最后执行测试正常：

2. 一对多用select的方式：

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="org.myself.dao.StudentDao">
    <sql id="student2">
        ${tableName}.student_id,
        ${tableName}.student_name,
        ${tableName}.student_age,
        ${tableName}.student_sex
    </sql>
    <sql id="teacher2">
        ${tableName}.teacher_id,
        ${tableName}.teacher_name,
        ${tableName}.teacher_sex,
        ${tableName}.teacher_age
    </sql>
    <resultMap id="teacherStudent2" type="org.myself.object.TeacherStudentObj">
        <id column="teacher_id" property="teacherId" javaType="java.lang.String"></id>
        <result column="teacher_name" property="teacherName" javaType="java.lang.String"></result>
        <result column="teacher_sex" property="teacherSex" javaType="java.lang.Boolean"></result>
        <result column="teacher_age" property="teacherAge" javaType="java.lang.Byte"></result>
        <collection property="students" column="teacher_id" select="getStudentByTeacherId" ofType="org.myself.pojo.Student">
        </collection>
    </resultMap>
    <select id="getStudentByTeacherId" resultType="org.myself.pojo.Student">
        SELECT <include refid="student2"><property name="tableName" value="ts"></property></include>
        FROM t_student ts
        WHERE ts.teacher_id = #{teacherId}
    </select>
    <select id="getTeacherStudentById" resultMap="teacherStudent2">
        SELECT <include refid="teacher2"><property name="tableName" value="tt"></property></include>
        FROM t_teacher tt
        WHERE tt.teacher_id = #{teacherId}
    </select>
</mapper>

大家有看到column不一样了吗？如果是这种column写法的是因为查询没有起别名，还是那句话（拿上你的sql去数据库中执行一下，看看是什么这个column填什么就对了）。有人说collection中的column字段呢？一样的，你查询出来对应的是什么就是什么，你可以换成别的试试（这样你是查不到student的，因为你换成别的字段是没有值的，所以关联查询也查不到数据）