七、多对一和一对多处理

目录：复杂查询环境搭建、多对一处理、一对多处理

1.复杂查询环境搭建

以学生和老师的关系为例
创建学生表student和老师表teacher并插入数据，可下载使用：https://share.weiyun.com/575gDFDw，也可以直接复制下方。

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

老师和学生的关系图

测试环境搭建：
①导入lombok。
②新建实体类Teacher，Student。

//老师类
package com.ping.pojo;
import lombok.Date;
@Date
public class Teacher {
  private int id;
  private String name;
}

//学生类
package com.ping.pojo;
import lombok.Date;
@Date
public class Student {
  private int id;
  private String name;
  //学生需要关联老师
  private Teacher teacher;
}

③建立Mapper接口。
TeacherMapper.java：

package com.ping.dao;
public interface TeacherMapper {
}

StudentMapper.java：

package com.ping.dao;
public interface StudentMapper {
}

④建立Mapper.xml文件。
TeacherMapper.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="com.ping.dao.TeacherMapper">
</mapper>

StudentMapper.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="com.ping.dao.TeacherMapper">
</mapper>

⑤在核心配置文件中绑定注册我们的Mapper接口或者文件。

<mappers>
  <mapper class="com.kuang.dao.TeacherMapper"/>
  <mapper class="com.kuang.dao.StudentMapper"/>
</mappers>

⑥测试查询是否能够成功。
TeacherTest.java：

import com.ping.dao.TeacherMapper;
import com.ping.pojo.Teacher;
import com.ping.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
public class TeacherTest {
  public static void main(String[] args) {
    SqlSession sqlSession = MybatisUtils.getSqlSession;
    TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
    Teacher teacher = mapper.getTeacher(1);
    System.out.println(teacher);
    sqlSession.close();
  }
}

StudentTest.java：

import com.ping.dao.StudentMapper;
import com.ping.pojo.Student;
import com.ping.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
public class StudentTest {
  public static void main(String[] args) {
    SqlSession sqlSession = MybatisUtils.getSqlSession;
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    Student student = mapper.getStudent(1);
    System.out.println(teacher);
    sqlSession.close();
  }
}

2.多对一处理

1）按照查询嵌套处理
思路：Ⅰ查询所有的学生信息
Ⅱ根据查询出来的学生的tid，寻找对应的老师。 子查询

<select id="getStudent" resultMap="StudentTeacher">
  select * from student
</select>
<resultMap id="StudentTeacher" type="Student">
  <result 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 = #{id}
</select> 

2）按照结果嵌套处理

<select id="getStudent2" resultMap="StudentTeacher2">
    select s.id sid,s.name sname,t.name tname
    from student s,teacher t
    where s.tid = t.id;
</select>
<resultMap id="StudentTeacher2" type="Student">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <association property="teacher" javaType="Teacher">
      <result property="name" column="tname"/>
    </association>
</resultMap>

Mysql多对一查询方式：子查询、联表查询

3.一对多处理

实体类：

//学生类
package com.ping.pojo;
import lombok.Date;
@Data
public class Student {
  private int id;
  private String name;
  private int tid;
}

//老师类
package com.ping.pojo;
import lombok.Date;
@Data
public class Teacher {
  private int id;
  private String name;
  //一个老师有多个学生
  private List<Student> students;
}

1）按照结果嵌套处理

<select id="getTeacher" resultMap="TeacherStudent">
  select s.id sid, s.name sname, t.name tname,t.id tid
  from student s,teacher t
  where s.tid = t.id and t.id = #{tid}
</select>
<resultMap id="TeacherStudent" type="Teacher">
  <result property="id" column="tid"/>
  <result property="name" column="tname"/>
  <!--复杂的属性，需要单独处理 对象： association 集合： collection
        javaType=指定属性的类型
        集合中的泛型信息，使用ofType获取
  -->
  <collection property="students" ofType="Student">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <result property="tid" column="tid"/>
  </collection>
</resultMap>

2）按照查询嵌套处理

<select id="getTeacher2" resultMap="TeacherStudent2">
  select * from mybatis.teacher where id = #{tid}
</select>
<resultMap id="TeacherStudent2" type="Teacher">
  <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>

总结：
Ⅰ关联：association 多对一
Ⅱ集合：collection 一对多
ⅢjavaType和ofType：JavaType用来指定实体类中属性的类型；ofType用来指定映射到List或者集合中的pojo类型，泛型中的约束类型。
注意点：Ⅰ保证SQL的可读性，尽量保证通俗易懂。
Ⅱ注意一对多和多对一中，属性名和字段的问题。
Ⅲ如果问题不好排查错误，可以使用日志 ， 建议使用 Log4j。