多对一处理

一。按照查询嵌套处理

建表语句

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) 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` (`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');

项目结构如图

代码结构

pojo层Student

package com.xiaosheng.pojo;

import lombok.Data;

@Data
public class Student{
    private int id;
    private String name;
    // 学生需要关联一个老师
    private Teacher teacher;
}

pojo层Teacher

package com.xiaosheng.pojo;

import lombok.Data;

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

要查询出来每个同学对应的老师信息

类似这样

Student(id=1, name=小明, teacher=Teacher(id=1, name=秦老师))
Student(id=2, name=小红, teacher=Teacher(id=1, name=秦老师))
Student(id=3, name=小张, teacher=Teacher(id=1, name=秦老师))
Student(id=4, name=小李, teacher=Teacher(id=1, name=秦老师))
Student(id=5, name=小王, teacher=Teacher(id=1, name=秦老师))

正常纯sql语句

select * from school.student as s , school.teacher as t where s.tid=t.id

但是StudentMapper.xml中这样写

    <select id="getStudent" resultType="Student">
        select * from school.student as s , school.teacher as t where s.tid=t.id
    </select>
得到结果
Student(id=1, name=小明, teacher=null)
Student(id=2, name=小红, teacher=null)
Student(id=3, name=小张, teacher=null)
Student(id=4, name=小李, teacher=null)
Student(id=5, name=小王, teacher=null)

因为Teacher是一个对象，而不是单纯一个字段

正确方法

<!--这里采用resultMap, 而不是单纯的采用resultType-->
<select id="getStudent" resultMap="StudentTeacher">
     select * from school.student
</select>

紧接着

<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>注意这一行，也是结果集映射，property和column与上方result标签用法一样,column对应数据库中字段，javaType java类型是一个Teacher类, selectTeacher标签id写根据id查询Teacher

<resultMap id="StudentTeacher" type="Student">
    <result property="id" column="id"/>
    <result property="name" column="name"/>
    <!--复杂的属性需要单独处理, 和golang中多对一的处理有些相似
            对象：association
            集合：collection
    -->
    <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>

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

完整代码

<?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.xiaosheng.dao.StudentMapper">
    <!--
       思路：
       1. 查询所有学生的信息
       2. 根据查询出来的学生的tid，寻找对应的老师
    -->
    <select id="getStudent" resultMap="StudentTeacher">
        select * from school.student as s , school.teacher as t where s.tid=t.id
--         select * from school.student
    </select>

    <resultMap id="StudentTeacher" type="Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <!--复杂的属性需要单独处理, 和golang中多对一的处理有些相似

            对象：association
            集合：collection
        -->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>

    <select id="getTeacher" resultType="Teacher">
        select * from school.teacher where id = #{id}
    </select>
</mapper>

二。按照结果嵌套处理

正确方法

<!--这里依然得使用resultMap-->
<select id="getStudent2" resultMap="StudentTeacher2">
       select s.id sid, s.name sname, t.name tname
       from school.student as s , school.teacher as t
       where s.tid=t.id
    </select>

    <resultMap id="StudentTeacher2" type="Student">
        <result property="id" column="id"></result>
        <result property="name" column="name"></result>
<!--返回的 类型就应该是Teacher,所以javaType直接这样写 -->
        <association property="teacher" javaType="Teacher">
            <!--teacher里面有东西，把tname别名赋值，老师是一个复杂类型，老师有name属性-->
            <result property="name" column="tname"/>
        </association>
    </resultMap>

注意，这里起了别名

下面三句相当于Student类里的三个属性    
    <result property="id" column="id"></result>
    <result property="name" column="name"></result>

    <association property="teacher" javaType="Teacher">