一对一、一对多、多对多（实操）

前言：为了方便起见，一对一和一对多用相同的数据库表

理解：（以下面的数据库表为例子）

1. 一对一：相当于一个学生对应一个老师
2. 一对多：相当于一个老师对应多个学生（也可以使用一个学生对应多个老师）
3. 多对多：相当于多个老师对应多个学生（也就是一个老师对应多个学生，一个学生对应多个老师的合并）

1.一对一

1. 先建立对应的数据库表

   /*创建student表 */
   DROP TABLE IF EXISTS `student`;
   /*设置student表的字段 */
   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;
   /*往student表中插入一些数据 */
   insert  into `student`(`id`,`name`,`tid`) values (1,'小明',1),(2,'小红',1),(3,'小张',1),(4,'小李',1),(5,'小王',1),(6,'小黄',2),(7,'小吕',2),(8,'小绿',2),(9,'小蓝',2),(10,'小兰',2),(11,'小天',3),(12,'小田',3),(13,'小花',3),(14,'小华',3),(15,'小宗',3),(16,'小芬',4),(17,'小分',4),(18,'小亲',4),(19,'小琴',4),(20,'小新',4);
   /*创建teacher表 */
   DROP TABLE IF EXISTS `teacher`;
   /*设置teacher表的字段 */
   CREATE TABLE `teacher` (
     `id` int(10) NOT NULL,
     `name` varchar(30) DEFAULT NULL,
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   /*往teacher表中插入一些数据 */
   insert  into `teacher`(`id`,`name`) values (1,'秦老师'),(2,'黄老师'),(3,'蔡老师'),(4,'马老师');
   
   
   

2. 创建实体类

   创建Student实体类

   package com.bjpowernode.ba01.pojo;
   
   public class Student {
       private Integer id;
       private String name;
       private Teacher teacher;
   
       public Student(Integer id, String name, Teacher teacher) {
           this.id = id;
           this.name = name;
           this.teacher = teacher;
       }
   
       public Student() {
       }
   
       public Integer getId() {
           return id;
       }
   
       public void setId(Integer id) {
           this.id = id;
       }
   
       public String getName() {
           return name;
       }
   
       public void setName(String name) {
           this.name = name;
       }
   
       public Teacher getTeacher() {
           return teacher;
       }
   
       public void setTeacher(Teacher teacher) {
           this.teacher = teacher;
       }
   
   
       @Override
       public String toString() {
           return "Student{" +
                   "id=" + id +
                   ", name='" + name + '\'' +
                   ", teacher=" + teacher +
                   '}';
       }
   }
   
   

   创建Teacher实体类

   package com.bjpowernode.ba01.pojo;
   
   public class Teacher {
       private Integer id;
       private String name;
   
       public Teacher(Integer id, String name) {
           this.id = id;
           this.name = name;
       }
   
       public Teacher() {
       }
   
       public Integer getId() {
           return id;
       }
   
       public void setId(Integer id) {
           this.id = id;
       }
   
       public String getName() {
           return name;
       }
   
       public void setName(String name) {
           this.name = name;
       }
   
   
       @Override
       public String toString() {
           return "Teacher{" +
                   "id=" + id +
                   ", name='" + name + '\'' +
                   '}';
       }
   }
   
   

3. 写接口

   package com.bjpowernode.ba01.dao;
   
   import com.bjpowernode.ba01.pojo.Student;
   
   public interface StudentDao {
   
       public Student getstuandteaById(Integer id);
   }
   
   

4. 写接口的配置文件

   <?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.bjpowernode.ba01.dao.StudentDao">
       <!-- 定义结果集，column表示sql查询列,property表示映射到实体的属性 -->
   <resultMap id="mystu" type="com.bjpowernode.ba01.pojo.Student">
       <id  property="sid"  column="id"/>
       <result property="sname" column="name"/>
       <association property="teacher" javaType="com.bjpowernode.ba01.pojo.Teacher">
           <id property="tid" column="id" />
           <result property="tname" column="name"/>
       </association>
   </resultMap>
       <select id="getstuandteaById" resultMap="mystu">
           select s.id sid,s.name sname,t.id tid,t.name tname
           from student s,teacher t
           where s.tid=t.id and s.id=#{id}
       </select>
   </mapper>
   
   

5. 在主配置文件添加配置文件

   <?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>
       <settings>
           <setting name="logImpl" value="STDOUT_LOGGING"/>
       </settings>
       <environments default="mydev">
           <environment id="mydev">
               <transactionManager type="JDBC"/>
               <dataSource type="POOLED">
                   <!--数据库的驱动类名-->
                   <property name="driver" value="com.mysql.jdbc.Driver"/>
                   <!--连接数据库的url字符串-->
                   <property name="url" value="jdbc:mysql://localhost:3306/task?useUnicode=true&amp;characterEncoding=utf-8"/>
                   <!--访问数据库的用户名-->
                   <property name="username" value="root"/>
                   <!--密码-->
                   <property name="password" value="123456"/>
               </dataSource>
           </environment>
   
       </environments>
   
       <mappers>
   <!--        <mapper resource="com/bjpowernode/dao/StudentDao.xml"/>-->
           <package name="com.bjpowernode.ba01"/>
       </mappers>
   </configuration>
   
   

6. 编写测试类

   package com.bjpowernode;
   
   import com.bjpowernode.ba01.dao.StudentDao;
   import com.bjpowernode.ba01.pojo.Student;
   import com.bjpowernode.utils.MyBatisUtils;
   import org.apache.ibatis.session.SqlSession;
   import org.junit.Test;
   
   public class MyTest1 {
       @Test
       public void test01(){
           SqlSession sqlSession = MyBatisUtils.getSqlSession();
           StudentDao mapper = sqlSession.getMapper(StudentDao.class);
           Student student = mapper.getstuandteaById(2);
           System.out.println(student);
           sqlSession.close();
       }
   }
   

7. 测试结果

Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@61322f9d]
==>  Preparing: select s.id sid,s.name sname,t.id tid,t.name tname from
student s,teacher t where s.tid=t.id and s.id=? 
==> Parameters: 2(Integer)
<==    Columns: sid, sname, tid, tname
<==        Row: 2, 小红, 1, 秦老师
<==      Total: 1
Student{id=2, name='小红', teacher=Teacher{id=1, name='秦老师'}}
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@61322f9d]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@61322f9d]
Returned connection 1630678941 to pool.

2.一对多

1. 先建立对应的数据库表**（使用一对一的数据库表）**

2. 创建实体类

   创建Student实体类

   package com.bjpowernode.ba02.pojo;
   
   public class Student {
       private Integer id;
       private String name;
       private Integer tid;
   
       public Student(Integer id, String name, Integer tid) {
           this.id = id;
           this.name = name;
           this.tid = tid;
       }
   
       public Student() {
       }
   
   
       @Override
       public String toString() {
           return "Student{" +
                   "id=" + id +
                   ", name='" + name + '\'' +
                   ", tid=" + tid +
                   '}';
       }
   
   
       public Integer getId() {
           return id;
       }
   
       public void setId(Integer id) {
           this.id = id;
       }
   
       public String getName() {
           return name;
       }
   
       public void setName(String name) {
           this.name = name;
       }
   
       public Integer getTid() {
           return tid;
       }
   
       public void setTid(Integer tid) {
           this.tid = tid;
       }
   }
   
   

   创建Teacher实体类

   package com.bjpowernode.ba02.pojo;
   
   import java.util.List;
   
   public class Teacher {
       private Integer id;
       private String name;
       private List<Student> student;
   
       public Integer getId() {
           return id;
       }
   
       public void setId(Integer id) {
           this.id = id;
       }
   
       public String getName() {
           return name;
       }
   
       public void setName(String name) {
           this.name = name;
       }
   
       public List<Student> getStudent() {
           return student;
       }
   
       public void setStudent(List<Student> student) {
           this.student = student;
       }
   
   
       public Teacher() {
       }
   
       public Teacher(Integer id, String name, List<Student> student) {
           this.id = id;
           this.name = name;
           this.student = student;
       }
   
       @Override
       public String toString() {
           return "Teacher{" +
                   "id=" + id +
                   ", name='" + name + '\'' +
                   ", student=" + student +
                   '}';
       }
   }
   
   

3. 写接口

   package com.bjpowernode.ba02.dao;
   
   import com.bjpowernode.ba02.pojo.Teacher;
   
   public interface TeacherDao {
      Teacher getteastu(Integer id);
   }
   
   

4. 写接口的配置文件

   <?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.bjpowernode.ba02.dao.TeacherDao">
       <resultMap id="mytea" type="com.bjpowernode.ba02.pojo.Teacher">
           <id column="tid" property="id"/>
           <result column="tname" property="name"/>
           <collection property="student"  ofType="com.bjpowernode.ba02.pojo.Student">
               <result column="sid" property="id"/>
               <result column="sname" property="name"/>
               <result column="stid" property="tid"/>
           </collection>
       </resultMap>
   <select id="getteastu" resultMap="mytea">
       select t.id tid,t.name tname,s.id sid,s.name sname,s.tid stid
       from teacher t,student s
       where t.id=s.tid and t.id=#{id}
   </select>
   
   </mapper>
   
   

5. 在主配置文件添加配置文件

      <!--        其余部分与一对一的主配置文件一样--> 
   <mappers>
   <!--        <mapper resource="com/bjpowernode/dao/StudentDao.xml"/>-->
           <package name="com.bjpowernode.ba02"/>
    </mappers>
   

6. 编写测试类

   package com.bjpowernode;
   
   import com.bjpowernode.ba02.dao.TeacherDao;
   import com.bjpowernode.ba02.pojo.Teacher;
   import com.bjpowernode.utils.MyBatisUtils;
   import org.apache.ibatis.session.SqlSession;
   import org.junit.Test;
   
   public class MyTest2 {
       @Test
       public void test01(){
           SqlSession sqlSession = MyBatisUtils.getSqlSession();
           TeacherDao mapper = sqlSession.getMapper(TeacherDao.class);
           Teacher getteastu = mapper.getteastu(1);
           System.out.println(getteastu);
           sqlSession.close();
       }
   }
   
   

   7.测试结果

   Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@61322f9d]
   ==>  Preparing: select t.id tid,t.name tname,s.id sid,s.name sname,s.tid stid from teacher t,student s where t.id=s.tid and t.id=? 
   ==> Parameters: 1(Integer)
   <==    Columns: tid, tname, sid, sname, stid
   <==        Row: 1, 秦老师, 1, 小明, 1
   <==        Row: 1, 秦老师, 2, 小红, 1
   <==        Row: 1, 秦老师, 3, 小张, 1
   <==        Row: 1, 秦老师, 4, 小李, 1
   <==        Row: 1, 秦老师, 5, 小王, 1
   <==      Total: 5
   Teacher{id=1, name='秦老师', student=[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}]}
   Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@61322f9d]
   Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@61322f9d]
   Returned connection 1630678941 to pool.
   

3.多对多

1. 先建立对应的数据库表

   
   /*创建stu表 */
   DROP TABLE IF EXISTS `stu`;
   
   /*设置stu表的字段 */
   CREATE TABLE `stu` (
     `sid` int(10) NOT NULL AUTO_INCREMENT,
     `sname` varchar(50) NOT NULL,
     PRIMARY KEY (`sid`)
   ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
   
   /*往stu表中插入数据 */
   
   insert  into `stu`(`sid`,`sname`) values (1,'张三'),(2,'李四'),(3,'王五'),(4,'赵六'),(5,'力力'),(6,'缓缓'),(7,'拍拍');
   
   /*创建stu_tea表 */
   DROP TABLE IF EXISTS `stu_tea`;
   
   /*设置 stu_tea表的字段*/
   
   CREATE TABLE `stu_tea` (
     `s_id` int(10) NOT NULL,
     `t_id` int(10) NOT NULL,
     KEY `s_id` (`s_id`),
     KEY `t_id` (`t_id`),
     CONSTRAINT `stu_tea_ibfk_1` FOREIGN KEY (`s_id`) REFERENCES `stu` (`sid`),
     CONSTRAINT `stu_tea_ibfk_2` FOREIGN KEY (`t_id`) REFERENCES `tea` (`tid`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   
   
   /*往stu_tea表中插入数据 */
   insert  into `stu_tea`(`s_id`,`t_id`) values (1,1),(2,2),(3,1),(4,2),(5,3),(6,3),(4,1),(3,3),(2,1);
   
   /*创建tea表 */
   DROP TABLE IF EXISTS `tea`;
   
   /*设置tea表的字段 */
   CREATE TABLE `tea` (
     `tid` int(10) NOT NULL AUTO_INCREMENT,
     `tname` varchar(50) NOT NULL,
     PRIMARY KEY (`tid`)
   ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
   
   
   /*往tea表中插入数据 */
   insert  into `tea`(`tid`,`tname`) values (1,'李老师'),(2,'王老师'),(3,'张老师');
   
   
   

2. 创建实体类

   创建stu实体类

   package com.bjpowernode.ba04.pojo;
   
   import java.util.List;
   
   public class Stu {
       private Integer sid;
       private String sname;
       private List<Tea> teas;
   
       public Stu() {
       }
   
       public Stu(Integer sid, String sname, List<Tea> teas) {
           this.sid = sid;
           this.sname = sname;
           this.teas = teas;
       }
   
       public Integer getSid() {
           return sid;
       }
   
       public void setSid(Integer sid) {
           this.sid = sid;
       }
   
       public String getSname() {
           return sname;
       }
   
       public void setSname(String sname) {
           this.sname = sname;
       }
   
       public List<Tea> getTeas() {
           return teas;
       }
   
       public void setTeas(List<Tea> teas) {
           this.teas = teas;
       }
   
       @Override
       public String toString() {
           return "Stu{" +
                   "sid=" + sid +
                   ", sname='" + sname + '\'' +
                   ", teas=" + teas +
                   '}';
       }
   }
   
   

   创建tea实体类

   package com.bjpowernode.ba04.pojo;
   
   import java.util.List;
   import java.util.Set;
   
   public class Tea {
       private Integer tid;
       private String tname;
   
       public Tea() {
       }
   
   
       public Tea(Integer tid, String tname) {
           this.tid = tid;
           this.tname = tname;
   
       }
   
       public Integer getTid() {
           return tid;
       }
   
       public void setTid(Integer tid) {
           this.tid = tid;
       }
   
       public String getTname() {
           return tname;
       }
   
       public void setTname(String tname) {
           this.tname = tname;
       }
   
       @Override
       public String toString() {
           return "Tea{" +
                   "tid=" + tid +
                   ", tname='" + tname + '\'' +
                   '}';
       }
   }
   

   创建stu_tea实体类（该表在本次测试用不到）

     package com.bjpowernode.ba04.pojo;
   
   public class Stu_Tea {
   
       private Stu stu;
       private Tea tea;
   
       public Stu_Tea() {
    }
   
   
       public Stu_Tea(Stu stu, Tea tea) {
           this.stu = stu;
           this.tea = tea;
       }
   
       public Stu getStu() {
           return stu;
       }
   
       public void setStu(Stu stu) {
           this.stu = stu;
       }
   
       public Tea getTea() {
           return tea;
       }
   
       public void setTea(Tea tea) {
           this.tea = tea;
       }
   
   
       @Override
       public String toString() {
           return "Stu_Tea{" +
                   "stu=" + stu +
                   ", tea=" + tea +
                   '}';
       }
   }
   
   

3. 写接口

   package com.bjpowernode.ba04.dao;
   
   import com.bjpowernode.ba04.pojo.Stu;
   
   import java.util.List;
   
   public interface StuDao {
       List<Stu> getstuandtea();
   }
   
   

4. 写接口的配置文件

   <?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.bjpowernode.ba04.dao.StuDao">
       <select id="getstuandtea" resultMap="getStuMap">
         SELECT s.`sid`,s.`sname`,t.tid,t.tname
         FROM stu s
         INNER JOIN stu_tea st
         ON st.t_id=s.sid
         INNER JOIN tea t
         ON st.`s_id`=t.`tid`
   </select>
   
       <resultMap id="getStuMap" type="com.bjpowernode.ba04.pojo.Stu">
           <id property="sid" column="sid"/>
           <result property="sname" column="sname"/>
           <collection property="teas" ofType="com.bjpowernode.ba04.pojo.Tea">
               <id property="tid" column="tid"/>
               <result property="tname" column="tname"/>
           </collection>
       </resultMap>
   
   
   </mapper>
   
   

5. 在主配置文件添加配置文件

      <!--        其余部分与一对一的主配置文件一样--> 
   <mappers>
   <!--        <mapper resource="com/bjpowernode/dao/StudentDao.xml"/>-->
           <package name="com.bjpowernode.ba04"/>
    </mappers>
   

6. 编写测试类

   package com.bjpowernode;
   
   import com.bjpowernode.ba04.dao.StuDao;
   import com.bjpowernode.ba04.pojo.Stu;
   import com.bjpowernode.utils.MyBatisUtils;
   import org.apache.ibatis.session.SqlSession;
   import org.junit.Test;
   
   import java.util.List;
   
   public class MyTest4 {
   
       @Test
       public void test01(){
           SqlSession sqlSession = MyBatisUtils.getSqlSession();
           StuDao mapper = sqlSession.getMapper(StuDao.class);
           List<Stu> getstuandtea = mapper.getstuandtea();
           for (Stu s:getstuandtea) {
               System.out.println(s);
           }
           sqlSession.close();
       }
   }
   
   

   7.测试结果

      Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@45c7e403]
   ==>  Preparing: SELECT s.`sid`,s.`sname`,t.tid,t.tname FROM stu s INNER JOIN stu_tea st ON st.t_id=s.sid INNER JOIN tea t ON st.`s_id`=t.`tid` 
   ==> Parameters: 
   <==    Columns: sid, sname, tid, tname
   <==        Row: 1, 张三, 1, 李老师
   <==        Row: 2, 李四, 2, 王老师
   <==        Row: 1, 张三, 2, 王老师
   <==        Row: 1, 张三, 3, 张老师
   <==        Row: 3, 王五, 3, 张老师
   <==      Total: 5
   Stu{sid=1, sname='张三', teas=[Tea{tid=1, tname='李老师'}, Tea{tid=2, tname='王老师'}, Tea{tid=3, tname='张老师'}]}
   Stu{sid=2, sname='李四', teas=[Tea{tid=2, tname='王老师'}]}
   Stu{sid=3, sname='王五', teas=[Tea{tid=3, tname='张老师'}]}
   Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@45c7e403]
   Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@45c7e403]
   Returned connection 1170727939 to pool.
   

以上属于个人见解，若其中有误之处，可以在评论发表你的观点。