1、多对一的处理

什么是多对一

• 多个学生，对应一个老师
• 对于学生这边而言， 关联 .. 多个学生，关联一个老师 【多对一】 关联对象
• 对于老师而言， 集合 ， 一个老师，有很多学生 【一对多】 关联集合

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(`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‘);
INSERT INTO `student` (`id`, `name`, `tid`) VALUES (‘6‘, ‘小明‘, ‘1‘);

测试环境搭建

1. 导入依赖 【mysql驱动，Mybatis，junit，lombok】

   <dependency>
       <groupId>mysql</groupId>
       <artifactId>mysql-connector-java</artifactId>
       <version>5.1.49</version>
   </dependency>
   <dependency>
       <groupId>org.mybatis</groupId>
       <artifactId>mybatis</artifactId>
       <version>3.4.6</version>
   </dependency>
   <dependency>
       <groupId>junit</groupId>
       <artifactId>junit</artifactId>
       <version>4.12</version>
   </dependency>
   <dependency>
       <groupId>org.projectlombok</groupId>
       <artifactId>lombok</artifactId>
       <version>1.18.20</version>
       <scope>provided</scope>
   </dependency>
   

2. 建立实体类，Teacher，Student

   student

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

   Teacher

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

3. 建立接口 StudentMapper，TeacherMapper

4. 建立StudentMapper.xml，TeacherMapper.xml文件

5. 在核心配置中绑定注册Mapper接口或文件

6. 定义一个简单方法，测试查询是否能成功

2、按照查询嵌套处理

思路：

1. 先查询所有的学生信息
2. 根据查询出来的学生的 tid，寻找对应的老师 ！子查询

实现代码：

1. StudentMapper 接口中定义查询方法 （ 查询所有的学生信息，以及对应的老师信息

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

2. 编写 StudentMapper.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.qn.dao.StudentMapper">
       <select id="getStudent" resultMap="StudentTeacher">
           select * from student
       </select>
   
       <resultMap id="StudentTeacher" type="Student">
           <result column="id" property="id"/>
           <result column="name" property="name"/>
           <!-- 复杂的属性需要单独处理
           对象：association
           集合：collection
           -->
           <association column="tid" property="teacher" javaType="Teacher" select="getTeacher"/>
       </resultMap>
       
   	<select id="getTeacher" resultType="Teacher">
           select * from teacher
           where id = #{id}
       </select>
   
   </mapper>
   

复杂的属性需要单独处理：

• 对象：association
• 集合：collection

测试代码：

@Test
public void getStudent(){
    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();
}

结果：

3、按照结果嵌套处理

实现代码：

1. StudentMapper 接口中定义查询方法

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

2. 编写 StudentMapper.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.qn.dao.StudentMapper">
   
       <select id="studentTeacher" resultMap="StudentMap">
           select s.id sid, s.name sname, t.name tname
           from student s, teacher t
           where tid = t.id
       </select>
       
       <resultMap id="StudentMap" type="Student">
           <result property="id" column="sid"/>
           <result property="name" column="sname"/>
   
           <association property="teacher" column="tid" javaType="Teacher">
               <result column="tname" property="name"/>
           </association>
       </resultMap>
       
   </mapper>
   

测试代码：

@Test
public void studentTeacher(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();

    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

    List<Student> students = mapper.studentTeacher();
    for (Student student : students) {
        System.out.println(student);
    }
    sqlSession.close();
}

结果：

mysql 中多对一查询方式：

• 子查询 ---->> 查询嵌套
• 联表查询 --->> 结果嵌套

2、一对多的处理

1、测试环境

与上方一致

2、按照查询嵌套处理

代码实现：

1. TeacherMapper 接口中定义查询方法

   public interface TeacherMapper {
   
       // 获取指定老师下的所有学生
       List<Teacher> getTeacherById(@Param("tid") int id);
   }
   

2. 编写 TeacherMapper.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.qn.dao.TeacherMapper">
   
       <select id="getTeacherById" resultMap="TeacherMap">
           select s.id sid, s.name sname, t.name tname, t.id tid
           from student s, teacher t
           where tid = t.id and t.id = #{tid}
       </select>
       
       <resultMap id="TeacherMap" type="Teacher">
           <result property="id" column="tid"/>
           <result property="name" column="tname"/>
           
           <collection property="students" ofType="Student">
               <result property="id" column="sid"/>
               <result property="name" column="sname"/>
           </collection>
       </resultMap>
       
   </mapper>
   

测试代码：

@Test
public void getTeacherById() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);

    List<Teacher> teachers = mapper.getTeacherById(1);
    for (Teacher teacher : teachers) {
        System.out.println(teacher);
    }

    sqlSession.close();
}

结果：

/*
* Teacher{
*   id=0, 
*   name=‘秦老师‘, 
*   students=[
*     Student{id=1, name=‘遇见星光‘, tid=1}, 
*     Student{id=2, name=‘小红‘, tid=1}, 
*     Student{id=3, name=‘小张‘, tid=1}, 
*     Student{id=4, name=‘小李‘, tid=1}, 
*     Student{id=5, name=‘小王‘, tid=1}, 
*     Student{id=6, name=‘小明‘, tid=1}
*   ]
* }
 */

3、按照结果嵌套处理

代码实现：

1. TeacherMapper 接口中定义查询方法

   public interface TeacherMapper {
       // 获取指定老师下的所有学生
       List<Teacher> getTeacherByIdTwo(@Param("tid") int id);
   }
   

2. 编写 TeacherMapper.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.qn.dao.TeacherMapper">
   
       <select id="getTeacherByIdTwo" resultMap="TeacherMap">
           select * from teacher
           where id = #{tid}
       </select>
       
   	<resultMap id="TeacherMap" type="Teacher">
           <collection property="students" javaType="ArrayList" ofType="Student" 
                       select="getStudentByTeacherId" column="id"/>
       </resultMap>
           
       <select id="getStudentByTeacherId" resultType="Student">
           select * from student
           where tid = #{tid}
       </select>
   
   </mapper>
   

测试代码：

@Test
public void getTeacherById() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);

    List<Teacher> teachers = mapper.getTeacherByIdTwo(1);
    for (Teacher teacher : teachers) {
        System.out.println(teacher);
    }

    sqlSession.close();
}

结果：

/*
* Teacher{
*   id=0,
*   name=‘秦老师‘,
*   students=[
*     Student{id=1, name=‘遇见星光‘, tid=1},
*     Student{id=2, name=‘小红‘, tid=1},
*     Student{id=3, name=‘小张‘, tid=1},
*     Student{id=4, name=‘小李‘, tid=1},
*     Student{id=5, name=‘小王‘, tid=1},
*     Student{id=6, name=‘小明‘, tid=1}
*   ]
* }
 */

3、总结

1. 关联： association （多对于一 （对象
2. 集合： collection（一对多 （集合

javaType="" 指定 实体类 属性的类型

ofType="" 用来指定映射 集合中的泛型

注意点：

1. 保证SQL的可读性，尽量保证通俗易懂
2. 注意一对多和多对一中，属性名和字段的问题
3. 如果问题不好排查错误，可以使用日志，建议使用Log4j

Mybatis-05-多对一｜一对多

原文：https://www.cnblogs.com/Right-A/p/14811482.html