MyBatis-一对一、一对多、多对多映射

关联映射

数据表直接不需要建立真实外键约束，有对应的字段即可。

例如：学生和班级，学生表有个cid表示对应的班级id就行，不需要写外键语句。

1.一对一关联

1.1 什么是一对一

• 用户表可以有账号、密码、姓名、手机号码、家庭地址等等属性，但是其中手机号码这些都是不太常用的属性
• 我们就可以把所有属性分成两个表，一个是用户基本信息表，一个是用户详情表，用户详情表通过uid进行关联
• 类似很多的网站，我们在注册账户之后，还会让我们完善信息，注册账户是用户基本信息表，完善信息是用户详情表

1.2创建数据库

-- 用户信息表
create table users(
    user_id int primary key auto_increment,
    user_name varchar(20) not null ,
    user_pwd varchar(20) not null
);

-- 用户详情表
create table details(
	  detail_id int primary key auto_increment,
    user_addr varchar(50) not null,
    user_tel char(11) not null,
  	-- 通过uid关联用户信息表
  	uid int not null unique
);

1.3 创建实体类

为了避免创建实体类频繁写构造方法，get、set方法，toString方法

我们可以使用注解形式,在pom.xml引入下面的依赖

    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.24</version>
      <scope>provided</scope>
    </dependency>

@Data 实体类
@NoArgsConstructor 无参构造
@AllArgsConstructor 全部参数构造
@ToString toString方法

• User

package com.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class User {
    private Integer userId;
    private String userName;
    private String userPwd;
    
    //一个User对应一个Detail
    //所以我们在User创建一个Detail对象，存在这个user对应的detail
    private Detail datail;
}

• Detail

package com.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Detail {
    private Integer detailId;
    private String userAddr;
    private String userTel;
    private String userDesc;
    private Integer userId;
}

1.4 实现一对一映射

1.4.1 连接查询

UserMapper接口：

//根据用户id查询用户所有信息
User selectById(Integer id);

UserMapper.xml映射文件：

<resultMap id="userMap" type="User">
    <id property="userId" column="user_id"/>
    <result property="userName" column="user_name"/>
    <result property="userPwd" column="user_pwd"/>

    <result property="detail.detailId" column="detail_id"/>
    <result property="detail.userAddr" column="user_addr"/>
    <result property="detail.userTel" column="user_tel"/>
</resultMap>

<select id="selectById" resultMap="userMap">
    select user_id, user_name, user_pwd, user_addr, user_tel,detail_id
    from users,
         details
    where users.user_id = details.detail_id
      and user_id = #{id}
</select>

测试方法：

@Test
public void selectById() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    User user = mapper.selectById(2);
    System.out.println(user);
    sqlSession.close();
}

1.4.2 子查询

原理：UserMapper.xml对用户表查询，同时根据id在DetailrMapper.xml中查询用户详细信息，最后整合查询出来的数据

UserMapper接口	User selectById(Integer id);
DetailMapper接口	Detail selectByUserId(Integer uid);

UserMapper.xml映射文件：

<resultMap id="userMap" type="User">
    <id property="userId" column="user_id"/>
    <result property="userName" column="user_name"/>
    <result property="userPwd" column="user_pwd"/>
    
    <association property="detail" select="com.Mapper.DetailMapper.selectByUserId" column="user_id"/>
</resultMap>

<select id="selectById" resultMap="userMap">
    select *
    from users
    where user_id = #{id}
</select>

DetailMapper.xml映射文件：

<select id="selectByUserId" resultType="Detail">
    select * from details where uid=#{uid}
</select>

测试：

@Test
public void selectById() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    User user = mapper.selectById(1);
    System.out.println(user);
}

2.一对多关联

2.1什么是一对多

• 一个班级可以有多个学生——一对多（班级角度）

2.2创建数据库

-- 创建班级信息表
create table t_clazz(
    cid int primary key auto_increment,
    cname varchar(30) not null 
);

-- 创建学生信息表
create table t_stu(
    sid int primary key auto_increment,
    sname varchar(30) not null,
    cid int not null
);

2.3创建实体类

• Clazz

package com.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

import java.util.List;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Clazz {
    private Integer cid;
    private String cname;
    //一个班级对应多个学生，所以使用list集合
    private List<Student> students;
}

• Student

  package com.pojo;
  
  import lombok.AllArgsConstructor;
  import lombok.Data;
  import lombok.NoArgsConstructor;
  import lombok.ToString;
  
  @Data
  @AllArgsConstructor
  @NoArgsConstructor
  @ToString
  public class Student {
      private Integer sid;
      private String sname;
      private Integer cid;
  }
  

2.4实现一对多映射

2.4.1连接查询

ClazzMapper接口：

Clazz selectById(Integer id);

ClazzMapper.xml映射文件：

<resultMap id="clazzMap" type="Clazz">
    <id property="cid" column="cid"/>
    <result property="cname" column="cname"/>
    
    <!--集合用collection，单个对象用association-->
    <collection property="students" ofType="Student">
        <result property="sid" column="sid"/>
        <result property="sname" column="sname"/>
        <result property="cid" column="cid"/>
    </collection>
</resultMap>

<select id="selectById" resultMap="clazzMap">
    select *
    from t_clazz,
         t_stu
    where t_clazz.cid = t_stu.cid
      and t_clazz.cid = #{id}
</select>

测试：

@Test
public void selectById() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
     ClazzMapper mapper = sqlSession.getMapper(ClazzMapper.class);
     Clazz clazz = mapper.selectById(1001);
     System.out.println(clazz);
     sqlSession.close();
}

2.4.2子查询

ClazzMapper接口	User selectById(Integer id);
StudentMapper接口	List selectByCid(Integer cid);

ClazzMapper.xml映射文件：

<resultMap id="clazzMap" type="Clazz">
    <id property="cid" column="cid"/>
    <result property="cname" column="cname"/>
    <collection property="students" select="com.Mapper.StudentMapper.selectByCid" column="cid"/>
</resultMap>

<select id="selectById" resultMap="clazzMap">
    select *
    from t_clazz
    where cid = #{id}
</select>

StudentMapper.xml映射文件：

<select id="selectByCid" resultType="Student">
    select *
    from t_stu
    where cid = #{cid}
</select>

测试：

@Test
public void selectById() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
     ClazzMapper mapper = sqlSession.getMapper(ClazzMapper.class);
     Clazz clazz = mapper.selectById(1001);
     System.out.println(clazz);
     sqlSession.close();
}

3.多对一关联

3.1什么是多对一

• 一个学生属于一个班级——多对一（学生角度）
• 一对多，在多的一端添加外键进行关联

3.2 创建实体类

• Clazz

package com.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

import java.util.List;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Clazz {
    private Integer cid;
    private String cname;
    //private List<Student> students;
}

• Student

  package com.pojo;
  
  import lombok.AllArgsConstructor;
  import lombok.Data;
  import lombok.NoArgsConstructor;
  import lombok.ToString;
  
  @Data
  @AllArgsConstructor
  @NoArgsConstructor
  @ToString
  public class Student {
      private Integer sid;
      private String sname;
      private Integer cid;
      //学生对应一个班级对象
      private Clazz clazz;
  }
  

3.2实现多对一映射

3.2.1连接查询

StudentMapper接口：

Student selectById(Integer sid);

StudentMapper.xml映射文件：

    <resultMap id="StudentMap" type="Student">
        <id property="sid" column="sid"/>
        <result property="sname" column="sname"/>
        <result property="cid" column="cid"/>
        <result property="clazz.cid" column="cid"/>
        <result property="clazz.cname" column="cname"/>
    </resultMap>
    <select id="selectById" resultMap="StudentMap">
        select *
        from t_stu,
             t_clazz
        where t_stu.cid = t_clazz.cid
          and sid = #{sid};
    </select>

测试：

    @Test
    public void selectById() {
        SqlSession sqlSession = SqlSessionUtil.openSqlSession();
         StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        Student student = mapper.selectById(5);
        System.out.println(student);
        sqlSession.close();
    }

3.2.1 子查询

ClazzMapper接口	Clazz selectByCid(Integer cid);
StudentMapper接口	Student selectById(Integer sid);

ClazzMapper.xml映射文件：

    <select id="selectByCid" resultType="Clazz">
        select *
        from t_clazz
        where cid = #{cid}
    </select>

StudentMapper.xml映射文件：

    <resultMap id="StudentMap" type="Student">
        <id property="sid" column="sid"/>
        <result property="sname" column="sname"/>
        <association property="clazz" select="com.Mapper.ClazzMapper.selectByCid" column="cid"/>
    </resultMap>
    <select id="selectById" resultMap="StudentMap">
        select *
        from t_stu
        where sid = #{sid};
    </select>

测试：

@Test
public void selectById() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
     StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    Student student = mapper.selectById(5);
    System.out.println(student);
    sqlSession.close();
}

4.多对多关联

4.1什么是多对多

在我们实际生活中，一个顾客可以购买多个商品，一个商品可以被多个顾客购买，那么我们就可以生成一张中间表(订单表)，在订单表中展示谁购买了什么。学生选课也是同样的道理。

4.2创建数据库

-- 学生表
create table t_stu(
    sid int primary key auto_increment,
    sname varchar(30) not null
);
-- 课程信息表
create table courses(
    course_id int primary key auto_increment,
    course_name varchar(50) not null
);
-- 选课信息表/成绩表(学号、课程号、成绩)
create table grades(
	gid int primary key auto_increment,
    sid int not null,
    cid int not null,
    score int not null
);

4.3创建实体类

• Student

package com.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
import java.util.List;

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Student {
    private Integer sid;
    private String sname;
    //一个学生可以选择多个课程
    private List<Course> courses;
}

• Course

package com.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

import java.util.List;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Course {
    private Integer cid;
    private String cname;
    //一个课程可以被多个学生选择
    private List<Student> students;
}

4.4实现多对多映射

需求：根据学生id，查询这个学生信息，并且查询这个学生选的所有课程

反过来，根据课表id，找到所有选这门课程的学生是一样的

4.4.1 连接查询

StudentMapper接口：

Student selectBySid(Integer sid);

StudentMapper.xml:

<resultMap id="StudentMap" type="Student">
    <id property="sid" column="sid"/>
    <result property="sname" column="sname"/>
   <collection property="courses" ofType="Course">
       <result property="cid" column="course_id"/>
       <result property="cname" column="course_name"/>
   </collection>

</resultMap>
<select id="selectBySid" resultMap="StudentMap">
    select *
    from t_stu,
         courses,
         grades
    where t_stu.sid = grades.sid
      and courses.course_id = grades.cid
      and t_stu.sid = #{sid}
</select>

测试：

@Test
public void selectBySid() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    Student student = mapper.selectBySid(1);
    System.out.println(student);
    sqlSession.close();
}

4.4.2 子查询

StudentMapper接口	Student selectBySid(Integer sid);
CourseMapper接口	Course selectBySid(Integer sid);

StudentMapper.xml映射文件：

<resultMap id="StudentMap" type="Student">
    <id property="sid" column="sid"/>
    <result property="sname" column="sname"/>
    <collection property="courses" select="com.Mapper.CourseMapper.selectBySid" column="sid"/>
</resultMap>
<select id="selectBySid" resultMap="StudentMap">
    select *
    from t_stu
    where sid = #{sid}
</select>

CourseMapper.xml映射文件：

<resultMap id="courseMap" type="Course">
    <id property="cid" column="course_id"/>
    <result property="cname" column="course_name"/>
</resultMap>
<select id="selectBySid" resultMap="courseMap">
    select *
    from courses,
         grades
    where courses.course_id = grades.cid
      and grades.sid = #{sid}
</select>

测试：

@Test
public void selectBySid() {
    SqlSession sqlSession = SqlSessionUtil.openSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    Student student = mapper.selectBySid(1);
    System.out.println(student);
    sqlSession.close();
}