【编程不良人】MyBatis从入门到精通学习笔记04--关联查询

短命女 2023-10-09 14:02 45阅读 0赞

### 1 一对一关联关系 ###

**配套视频：**[【编程不良人】Mybatis 从入门到精通\_哔哩哔哩\_bilibili][Mybatis _bilibili]

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAQ29kZXJfQ3Vp_size_20_color_FFFFFF_t_70_g_se_x_16][]

#### 1.1 建表 ####

-- 身份信息表
      DROP TABLE IF EXISTS `t_card`;
      CREATE TABLE IF NOT EXISTS `t_card`(
        `id` VARCHAR(36) PRIMARY KEY,
        `no` VARCHAR(18),
        `address` VARCHAR(100),
        `fork` VARCHAR(30)
      )ENGINE=INNODB CHARSET=utf8;
      
      -- 用户表
      DROP TABLE IF EXISTS `t_users`;
      CREATE TABLE IF NOT EXISTS `t_users`(
        `id` VARCHAR(36) PRIMARY KEY,
        `name` VARCHAR(40),
        `age` INTEGER,
        `bir` DATE,
        `cardId` VARCHAR(36) REFERENCES `t_card`(`id`)
      )ENGINE=INNODB CHARSET=utf8;
      
      -- 插入t_card数据
      INSERT INTO t_card VALUES
      ('1','1','北京市海淀区','汉族'),
      ('2','2','北京市昌平区','满族');
      
      -- 插入t_users数据
      INSERT INTO t_users VALUES
      ('1','小黑',15,CURDATE(),'1'),
      ('2','小白',23,CURDATE(),'2');
      
      -- 查看表中数据
      SELECT * FROM t_card;
      SELECT * FROM t_users;
      
      -- 进行连接查询
      SELECT u.`id`,u.`name`,u.`age`,u.`bir`,c.`no`,c.`address`,c.`fork` FROM t_users u
      LEFT JOIN t_card c
      ON u.`cardId`=c.`id`;
      -- 或
      SELECT u.id userId,u.name,u.age,u.bir,
             c.id cardId,c.no,c.fork,c.address
      FROM t_users u
      LEFT JOIN t_card c
      ON u.cardId=c.id;
      -- 或
      SELECT c.id cardId,c.no,c.address,c.fork,
             u.id userId,u.name,u.age,u.bir
      FROM t_card c
      LEFT JOIN t_users u
      ON c.id=u.cardId;

**查询结果：**

**![823f8b545c5640adb31c5e63006b7937.png][]**

#### 1.2 创建实体类 ####

> **User**
> 
> **注意：**先添加User的属性、无参有参构造方法、set、get方法、toString方法，再添加Card属性以及get、set方法，防止在toString方法里面产生死环，打印时异常，栈溢出

package entity;
      
      import java.util.Date;
      
      /**
       * @ClassName User
       * @Description TODO
       * @Author Jiangnan Cui
       * @Date 2022/4/16 14:31
       * @Version 1.0
       */
      public class User {
          private String id;
          private String name;
          private Integer age;
          private Date bir;
      
          //关系属性，需要额外添加get、set方法
          //注意此属性不能出现在toString方法里面，否则会产生死环，打印时异常，栈溢出
          private Card card;
      
          public Card getCard() {
              return card;
          }
      
          public void setCard(Card card) {
              this.card = card;
          }
      
          public User(String id, String name, Integer age, Date bir) {
              this.id = id;
              this.name = name;
              this.age = age;
              this.bir = bir;
          }
      
          public User() {
          }
      
          public String getId() {
              return id;
          }
      
          public void setId(String id) {
              this.id = id;
          }
      
          public String getName() {
              return name;
          }
      
          public void setName(String name) {
              this.name = name;
          }
      
          public Integer getAge() {
              return age;
          }
      
          public void setAge(Integer age) {
              this.age = age;
          }
      
          public Date getBir() {
              return bir;
          }
      
          public void setBir(Date bir) {
              this.bir = bir;
          }
      
          @Override
          public String toString() {
              return "User{" +
                      "id='" + id + '\'' +
                      ", name='" + name + '\'' +
                      ", age=" + age +
                      ", bir=" + bir +
                      '}';
          }
      }

> **Card**
> 
> **注意：**先添加Card的属性、无参有参构造方法、set、get方法、toString方法，再添加User属性以及get、set方法，防止在toString方法里面产生死环，打印时异常，栈溢出

package entity;
      
      /**
       * @ClassName Card
       * @Description TODO
       * @Author Jiangnan Cui
       * @Date 2022/4/16 14:37
       * @Version 1.0
       */
      public class Card {
          private String id;
          private String no;
          private String address;
          private String fork;
      
          //关系属性，需要额外添加get、set方法
          // 注意此属性不能出现在toString方法里面，否则会产生死环，打印时异常，栈溢出
          private User user;
      
          public User getUser() {
              return user;
          }
      
          public void setUser(User user) {
              this.user = user;
          }
      
          public Card() {
          }
      
          public Card(String id, String no, String address, String fork) {
              this.id = id;
              this.no = no;
              this.address = address;
              this.fork = fork;
          }
      
          public String getId() {
              return id;
          }
      
          public void setId(String id) {
              this.id = id;
          }
      
          public String getNo() {
              return no;
          }
      
          public void setNo(String no) {
              this.no = no;
          }
      
          public String getAddress() {
              return address;
          }
      
          public void setAddress(String address) {
              this.address = address;
          }
      
          public String getFork() {
              return fork;
          }
      
          public void setFork(String fork) {
              this.fork = fork;
          }
      
          @Override
          public String toString() {
              return "Card{" +
                      "id='" + id + '\'' +
                      ", no='" + no + '\'' +
                      ", address='" + address + '\'' +
                      ", fork='" + fork + '\'' +
                      '}';
          }
      }

#### 1.3 创建DAO组件 ####

> **UserDAO**

package dao;
      
      import entity.User;
      
      import java.util.List;
      
      /**
       * @ClassName UserDAO
       * @Description TODO
       * @Author Jiangnan Cui
       * @Date 2022/4/16 14:42
       * @Version 1.0
       */
      public interface UserDAO {
          /**
           * @MethodName selectAll
           * @Description 查询用户信息及身份信息
           * @return: java.util.List<entity.User>
           * @Author Jiangnan Cui
           * @Date 2022/4/16 14:43
           */
          List<User> selectAll();
      }

> **CardrDAO**

package dao;
      
      import entity.Card;
      
      import java.util.List;
      
      /**
       * @ClassName CardDAO
       * @Description TODO
       * @Author Jiangnan Cui
       * @Date 2022/4/16 16:03
       * @Version 1.0
       */
      public interface CardDAO {
          /**
           * @MethodName selectAll
           * @Description 查询身份信息及用户信息
           * @return: java.util.List<entity.Card>
           * @Author Jiangnan Cui
           * @Date 2022/4/16 16:03
           */
          List<Card> selectAll();
      }

#### 1.4 创建mapper配置文件 ####

> **UserDAOMapper.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="dao.UserDAO">
          
          <resultMap id="userMap" type="entity.User">
              
              <id column="userId" property="id"/>
              
              <result column="name" property="name"/>
              <result column="age" property="age"/>
              <result column="bir" property="bir"/>
      
              
              <association property="card" javaType="entity.Card">
                  
                  <id column="cardId" property="id"/>
                  <result column="no" property="no"/>
                  <result column="address" property="address"/>
                  <result column="fork" property="fork"/>
              </association>
          </resultMap>
      
          
          <select id="selectAll" resultMap="userMap">
              select u.id userId,u.name,u.age,u.bir,
                     c.id cardId,c.no,c.fork,c.address
              from t_users u
              left join t_card c
              on u.cardId=c.id
          </select>
      
      </mapper>

> **CardDAOMapper.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="dao.CardDAO">
          <resultMap id="cardMap" type="entity.Card">
              <id column="userId" property="id"/>
              <result column="no" property="no"/>
              <result column="address" property="address"/>
              <result column="fork" property="fork"/>
      
              <association property="user" javaType="entity.User">
                  <id column="userId" property="id"/>
                  <result column="name" property="name"/>
                  <result column="age" property="age"/>
                  <result column="bir" property="bir"/>
              </association>
          </resultMap>
      
          
          <select id="selectAll" resultMap="cardMap">
              select c.id cardId,c.no,c.address,c.fork,
                     u.id userId,u.name,u.age,u.bir
              from t_card c
              left join t_users u
              on c.id=u.cardId
          </select>
      
      </mapper>

#### 1.5 mybatis-config.xml配置mapper ####

#### 1.6 测试连接查询 ####

package test;
      
      import dao.CardDAO;
      import dao.UserDAO;
      import org.apache.ibatis.session.SqlSession;
      import org.junit.Test;
      import util.MybatisUtil;
      
      /**
       * @ClassName TestDAO
       * @Description TODO
       * @Author Jiangnan Cui
       * @Date 2022/4/16 16:37
       * @Version 1.0
       */
      public class TestDAO {
          /**
           * @MethodName testUserDAO
           * @Description 测试UserDAO组件连接查询
           * @Author Jiangnan Cui
           * @Date 2022/4/16 16:38
           */
          @Test
          public void testUserDAO(){
              SqlSession sqlSession = MybatisUtil.getSqlSession();
              UserDAO userDAO = sqlSession.getMapper(UserDAO.class);
              userDAO.selectAll().forEach(user -> System.out.println("user = " + user + " " + user.getCard()));
              MybatisUtil.close();
          }
      
          /**
           * @MethodName testCardDAO
           * @Description 测试CardDAO组件连接查询
           * @Author Jiangnan Cui
           * @Date 2022/4/16 16:39
           */
          @Test
          public void testCardDAO(){
              SqlSession sqlSession = MybatisUtil.getSqlSession();
              CardDAO cardDAO = sqlSession.getMapper(CardDAO.class);
              cardDAO.selectAll().forEach(card -> System.out.println("card = " + card + " " + card.getUser()));
              MybatisUtil.close();
          }
      }

**测试结果：**

**（1）UserDAO**

[DEBUG] 2022-04-16 main dao.UserDAO.selectAll ==>  Preparing: select u.id userId,u.name,u.age,u.bir, c.id cardId,c.no,c.fork,c.address from t_users u left join t_card c on u.cardId=c.id 
      [DEBUG] 2022-04-16 main dao.UserDAO.selectAll ==> Parameters: 
      [DEBUG] 2022-04-16 main dao.UserDAO.selectAll <==      Total: 2
      user = User{id='1', name='小黑', age=15, bir=Sat Apr 16 00:00:00 CST 2022} Card{id='1', no='1', address='北京市海淀区', fork='汉族'}
      user = User{id='2', name='小白', age=23, bir=Sat Apr 16 00:00:00 CST 2022} Card{id='2', no='2', address='北京市昌平区', fork='满族'}

**（2）CardDAO**

[DEBUG] 2022-04-16 main dao.CardDAO.selectAll ==>  Preparing: select c.id cardId,c.no,c.address,c.fork, u.id userId,u.name,u.age,u.bir from t_card c left join t_users u on c.id=u.cardId 
      [DEBUG] 2022-04-16 main dao.CardDAO.selectAll ==> Parameters: 
      [DEBUG] 2022-04-16 main dao.CardDAO.selectAll <==      Total: 2
      card = Card{id='1', no='1', address='北京市海淀区', fork='汉族'} User{id='1', name='小黑', age=15, bir=Sat Apr 16 00:00:00 CST 2022}
      card = Card{id='2', no='2', address='北京市昌平区', fork='满族'} User{id='2', name='小白', age=23, bir=Sat Apr 16 00:00:00 CST 2022}

### 2 一对多关联关系 ###

**配套视频：**[【编程不良人】Mybatis 从入门到精通\_哔哩哔哩\_bilibili][Mybatis _bilibili 1]

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAQ29kZXJfQ3Vp_size_20_color_FFFFFF_t_70_g_se_x_16 1][]

#### 2.1 建表 ####

-- 部门表
      DROP TABLE IF EXISTS t_dept;
      CREATE TABLE IF NOT EXISTS `t_dept`(
        `id` VARCHAR(36) PRIMARY KEY,
        `name` VARCHAR(40)
      )ENGINE=INNODB CHARSET=utf8;
      
      -- 员工表
      DROP TABLE IF EXISTS t_emps;
      CREATE TABLE IF NOT EXISTS `t_emps`(
        `id` VARCHAR(36) PRIMARY KEY,
        `name` VARCHAR(40),
        `age` INTEGER,
        `bir` DATE,
        `salary` DOUBLE(7,2),
        `deptId` VARCHAR(36) REFERENCES `t_dept`(`id`)
      )ENGINE=INNODB CHARSET=utf8;
      
      -- 部门测试数据
      INSERT INTO t_dept VALUES
      ('1','教学部'),
      ('2','研发部');
      
      -- 员工的测试数据
      INSERT INTO t_emps VALUES
      ('1','小黑',12,CURDATE(),23000.13,'1'),
      ('2','小三',13,CURDATE(),24000.14,'2'),
      ('3','小黄',14,CURDATE(),25000.15,'1'),
      ('4','小牛',15,CURDATE(),26000.16,'1'),
      ('5','小金',16,CURDATE(),27000.17,'1'),
      ('6','小陈',17,CURDATE(),28000.18,'2');
      
      -- 查看表数据
      SELECT * FROM t_dept;
      SELECT * FROM t_emps;
      
      -- 根据部门找员工信息
      SELECT 
        d.id,d.`name`,
        e.`id`,e.`name`,e.`age`,e.`bir`,e.`salary`,e.`deptId`
      FROM t_dept d
      LEFT JOIN t_emps e
      ON d.`id`=e.`deptId`;

#### 2.2 创建实体类 ####

> Dept

package entity;
      
      import java.util.List;
      
      /**
       * @ClassName Dept
       * @Description TODO
       * @Author Jiangnan Cui
       * @Date 2022/4/16 20:19
       * @Version 1.0
       */
      public class Dept {
          private String id;
          private String name;
      
          //关系属性
          private List<Emp> emps;
      
          public List<Emp> getEmps() {
              return emps;
          }
      
          public void setEmps(List<Emp> emps) {
              this.emps = emps;
          }
      
          public Dept() {
          }
      
          public Dept(String id, String name) {
              this.id = id;
              this.name = name;
          }
      
          public String getId() {
              return id;
          }
      
          public void setId(String id) {
              this.id = id;
          }
      
          public String getName() {
              return name;
          }
      
          public void setName(String name) {
              this.name = name;
          }
      
          @Override
          public String toString() {
              return "Dept{" +
                      "id='" + id + '\'' +
                      ", name='" + name + '\'' +
                      '}';
          }
      }

> Emp

package entity;
      
      import java.util.Date;
      
      /**
       * @ClassName Emp
       * @Description TODO
       * @Author Jiangnan Cui
       * @Date 2022/4/16 20:21
       * @Version 1.0
       */
      public class Emp {
          private String id;
          private String name;
          private Integer age;
          private Double salary;
          private Date bir;
      
          //关系属性
          private Dept dept;
      
          public Dept getDept() {
              return dept;
          }
      
          public void setDept(Dept dept) {
              this.dept = dept;
          }
      
          public Emp() {
          }
      
          public Emp(String id, String name, Integer age, Double salary, Date bir) {
              this.id = id;
              this.name = name;
              this.age = age;
              this.salary = salary;
              this.bir = bir;
          }
      
          public String getId() {
              return id;
          }
      
          public void setId(String id) {
              this.id = id;
          }
      
          public String getName() {
              return name;
          }
      
          public void setName(String name) {
              this.name = name;
          }
      
          public Integer getAge() {
              return age;
          }
      
          public void setAge(Integer age) {
              this.age = age;
          }
      
          public Double getSalary() {
              return salary;
          }
      
          public void setSalary(Double salary) {
              this.salary = salary;
          }
      
          public Date getBir() {
              return bir;
          }
      
          public void setBir(Date bir) {
              this.bir = bir;
          }
      
          @Override
          public String toString() {
              return "Emp{" +
                      "id='" + id + '\'' +
                      ", name='" + name + '\'' +
                      ", age=" + age +
                      ", salary=" + salary +
                      ", bir=" + bir +
                      '}';
          }
      }

#### 2.3 创建DAO组件 ####

> DeptDAO

package dao;
      
      import entity.Dept;
      
      import java.util.List;
      
      /**
       * @ClassName DeptDAO
       * @Description TODO
       * @Author Jiangnan Cui
       * @Date 2022/4/16 20:25
       * @Version 1.0
       */
      public interface DeptDAO {
          /**
           * @MethodName selectAll
           * @Description 查询部门的同时将部门中所有的员工信息一并查到
           * @return: java.util.List<entity.Dept>
           * @Author Jiangnan Cui
           * @Date 2022/4/16 20:26
           */
          List<Dept> selectAll();
      }

> EmpDAO

package dao;
      
      import entity.Emp;
      
      import java.util.List;
      
      /**
       * @ClassName EmpDAO
       * @Description TODO
       * @Author Jiangnan Cui
       * @Date 2022/4/16 20:53
       * @Version 1.0
       */
      public interface EmpDAO {
          /**
           * @MethodName selectAll
           * @Description 查询员工信息的同时将部门的信息一并查到
           * @return: java.util.List<entity.Emp>
           * @Author Jiangnan Cui
           * @Date 2022/4/16 20:54
           */
          List<Emp> selectAll();
      }

#### 2.4 创建mapper配置文件 ####

> DeptDAOMapper

<?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="dao.DeptDAO">
          
          <resultMap id="deptMap" type="entity.Dept">
              <id column="id" property="id"/>
              <result column="name" property="name"/>
      
              
              <collection property="emps" javaType="list" ofType="entity.Emp">
                  <id column="eid" property="id"/>
                  <result column="ename" property="name"/>
                  <result column="age" property="age"/>
                  <result column="salary" property="salary"/>
                  <result column="bir" property="bir"/>
              </collection>
          </resultMap>
      
          
          <select id="selectAll" resultMap="deptMap">
              select
                  d.id,d.name,
                  e.id eid,e.name ename,e.age,e.salary,e.bir
              from t_dept d
              left join t_emps e
              on d.id=e.deptId
          </select>
      </mapper>

> EmpDAOMapper

<?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="dao.EmpDAO">
          
          <resultMap id="empMap" type="entity.Emp">
              <id column="id" property="id"/>
              <result column="name" property="name"/>
              <result column="age" property="age"/>
              <result column="salary" property="salary"/>
              <result column="bir" property="bir"/>
      
              
              <association property="dept" javaType="entity.Dept">
                  <id column="did" property="id"/>
                  <result column="dname" property="name"/>
              </association>
          </resultMap>
      
          
          <select id="selectAll" resultMap="empMap">
              select
                  e.id,e.name,e.age,e.salary,e.bir,
                  d.id did,d.name dname
              from t_dept d
              left join t_emps e
              on d.id=e.deptId
          </select>
      </mapper>

#### 2.5 mybatis-config.xml配置mapper ####

#### 2.6 测试连接查询 ####

package test;
      
      import dao.CardDAO;
      import dao.DeptDAO;
      import dao.EmpDAO;
      import dao.UserDAO;
      import org.apache.ibatis.session.SqlSession;
      import org.junit.Test;
      import util.MybatisUtil;
      
      /**
       * @ClassName TestDAO
       * @Description TODO
       * @Author Jiangnan Cui
       * @Date 2022/4/16 16:37
       * @Version 1.0
       */
      public class TestDAO {
          /**
           * @MethodName testDeptDAO
           * @Description 测试DeptDAO组件连接查询
           * @Author Jiangnan Cui
           * @Date 2022/4/16 20:50
           */
          @Test
          public void testDeptDAO(){
              SqlSession sqlSession = MybatisUtil.getSqlSession();
              DeptDAO deptDAO = sqlSession.getMapper(DeptDAO.class);
              deptDAO.selectAll().forEach(dept -> System.out.println("dept = " + dept + " " + dept.getEmps()));
              MybatisUtil.close();
          }
      
          /**
           * @MethodName testEmpDAO
           * @Description 测试EmpDAO组件连接查询
           * @Author Jiangnan Cui
           * @Date 2022/4/16 20:50
           */
          @Test
          public void testEmpDAO(){
              SqlSession sqlSession = MybatisUtil.getSqlSession();
              EmpDAO empDAO = sqlSession.getMapper(EmpDAO.class);
              empDAO.selectAll().forEach(emp -> System.out.println("emp = " + emp + " " + emp.getDept()));
              MybatisUtil.close();
          }
      }

**测试结果：**

**（1）DeptDAO**

[DEBUG] 2022-04-16 main dao.DeptDAO.selectAll ==>  Preparing: select d.id,d.name, e.id eid,e.name ename,e.age,e.salary,e.bir from t_dept d left join t_emps e on d.id=e.deptId 
      [DEBUG] 2022-04-16 main dao.DeptDAO.selectAll ==> Parameters: 
      [DEBUG] 2022-04-16 main dao.DeptDAO.selectAll <==      Total: 6
      dept = Dept{id='1', name='教学部'} [Emp{id='1', name='小黑', age=12, salary=23000.13, bir=Sat Apr 16 00:00:00 CST 2022}, Emp{id='3', name='小黄', age=14, salary=25000.15, bir=Sat Apr 16 00:00:00 CST 2022}, Emp{id='4', name='小牛', age=15, salary=26000.16, bir=Sat Apr 16 00:00:00 CST 2022}, Emp{id='5', name='小金', age=16, salary=27000.17, bir=Sat Apr 16 00:00:00 CST 2022}]
      dept = Dept{id='2', name='研发部'} [Emp{id='2', name='小三', age=13, salary=24000.14, bir=Sat Apr 16 00:00:00 CST 2022}, Emp{id='6', name='小陈', age=17, salary=28000.18, bir=Sat Apr 16 00:00:00 CST 2022}]

**（2）EmpDAO**

[DEBUG] 2022-04-16 main dao.EmpDAO.selectAll ==>  Preparing: select e.id,e.name,e.age,e.salary,e.bir, d.id did,d.name dname from t_dept d left join t_emps e on d.id=e.deptId 
      [DEBUG] 2022-04-16 main dao.EmpDAO.selectAll ==> Parameters: 
      [DEBUG] 2022-04-16 main dao.EmpDAO.selectAll <==      Total: 6
      emp = Emp{id='1', name='小黑', age=12, salary=23000.13, bir=Sat Apr 16 00:00:00 CST 2022} Dept{id='1', name='教学部'}
      emp = Emp{id='2', name='小三', age=13, salary=24000.14, bir=Sat Apr 16 00:00:00 CST 2022} Dept{id='2', name='研发部'}
      emp = Emp{id='3', name='小黄', age=14, salary=25000.15, bir=Sat Apr 16 00:00:00 CST 2022} Dept{id='1', name='教学部'}
      emp = Emp{id='4', name='小牛', age=15, salary=26000.16, bir=Sat Apr 16 00:00:00 CST 2022} Dept{id='1', name='教学部'}
      emp = Emp{id='5', name='小金', age=16, salary=27000.17, bir=Sat Apr 16 00:00:00 CST 2022} Dept{id='1', name='教学部'}
      emp = Emp{id='6', name='小陈', age=17, salary=28000.18, bir=Sat Apr 16 00:00:00 CST 2022} Dept{id='2', name='研发部'}

### 3 多对多关联关系 ###

**配套视频：**[【编程不良人】Mybatis 从入门到精通\_哔哩哔哩\_bilibili][Mybatis _bilibili 2]

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAQ29kZXJfQ3Vp_size_20_color_FFFFFF_t_70_g_se_x_16 2][]

**思路：**多对多拆分成一对多，添加关系表

#### 3.1 建表 ####

-- 学生表
      DROP TABLE IF EXISTS `t_student`;
      CREATE TABLE IF NOT EXISTS `t_student`(
        `id` VARCHAR(36) PRIMARY KEY,
        `name` VARCHAR(40),
        `age` INTEGER
      )ENGINE=INNODB CHARSET=utf8;
      
      -- 课程表
      DROP TABLE IF EXISTS `t_course`;
      CREATE TABLE IF NOT EXISTS `t_course`(
        `id` VARCHAR(36) PRIMARY KEY,
        `name` VARCHAR(36)
      )ENGINE=INNODB CHARSET=utf8;
      
      -- 学生选课表（关系表:主键作为字段）
      DROP TABLE IF EXISTS `t_student_course`;
      CREATE TABLE IF NOT EXISTS `t_student_course`(
        `sid` VARCHAR(36) REFERENCES t_student(`id`),
        `cid` VARCHAR(36) REFERENCES t_course(`id`)
      )ENGINE=INNODB CHARSET=utf8;
      
      -- 插入测试数据
      INSERT INTO t_student VALUES 
      ('11','小明',23),
      ('22','小王',23);
      
      INSERT INTO t_course VALUES
      ('1','语文'),
      ('2','数学'),
      ('3','政治');
      
      INSERT INTO t_student_course VALUES
      ('11','1'),
      ('11','2'),
      ('11','3'),
      ('22','1'),
      ('22','3');
      
      -- 查看表数据
      SELECT * FROM t_student;
      SELECT * FROM t_course;
      SELECT * FROM t_student_course;
      
      -- 关联查询 （t_student+t_student_course）+t_course
      SELECT 
        s.id,s.name,s.age,
        c.id cid,c.name cname
      FROM t_student s
      LEFT JOIN t_student_course sc
      ON s.`id`=sc.`sid`
      LEFT JOIN t_course c
      ON sc.`cid`=c.`id`;

#### 3.2 创建实体类 ####

> Student

package entity;
      
      import java.util.List;
      
      /**
       * @ClassName Student
       * @Description TODO
       * @Author Jiangnan Cui
       * @Date 2022/4/7 9:17
       * @Version 1.0
       */
      public class Student {
          private String id;
          private String name;
          private int age;
      
          //关联属性
          List<Course> courses;
      
          public List<Course> getCourses() {
              return courses;
          }
      
          public void setCourses(List<Course> courses) {
              this.courses = courses;
          }
      
          public Student() {
          }
      
          public Student(String id, String name, int age) {
              this.id = id;
              this.name = name;
              this.age = age;
          }
      
          public String getId() {
              return id;
          }
      
          public void setId(String id) {
              this.id = id;
          }
      
          public String getName() {
              return name;
          }
      
          public void setName(String name) {
              this.name = name;
          }
      
          public int getAge() {
              return age;
          }
      
          public void setAge(int age) {
              this.age = age;
          }
      
          @Override
          public String toString() {
              return "Student{" +
                      "id='" + id + '\'' +
                      ", name='" + name + '\'' +
                      ", age=" + age +
                      '}';
          }
      }

> Course

package entity;
      
      import java.util.List;
      
      /**
       * @ClassName Course
       * @Description TODO
       * @Author Jiangnan Cui
       * @Date 2022/4/16 21:53
       * @Version 1.0
       */
      public class Course {
          private String id;
          private String name;
      
          //关联属性
          List<Student> students;
      
          public List<Student> getStudents() {
              return students;
          }
      
          public void setStudents(List<Student> students) {
              this.students = students;
          }
      
          public Course() {
          }
      
          public Course(String id, String name) {
              this.id = id;
              this.name = name;
          }
      
          public String getId() {
              return id;
          }
      
          public void setId(String id) {
              this.id = id;
          }
      
          public String getName() {
              return name;
          }
      
          public void setName(String name) {
              this.name = name;
          }
      
          @Override
          public String toString() {
              return "Course{" +
                      "id='" + id + '\'' +
                      ", name='" + name + '\'' +
                      '}';
          }
      }

#### 3.3 创建DAO组件 ####

> StudentDAO

package dao;
      
      import entity.Student;
      import org.apache.ibatis.annotations.Param;
      
      import java.util.List;
      import java.util.Map;
      
      /**
       * @ClassName StudentDAO
       * @Description TODO
       * @Author Jiangnan Cui
       * @Date 2022/4/7 9:18
       * @Version 1.0
       */
      public interface StudentDAO {
          /**
           * @MethodName selectAll
           * @Description 查询学生信息的同时也把课程的信息一并查出来
           * @return: java.util.List<entity.Student>
           * @Author Jiangnan Cui
           * @Date 2022/4/16 21:55
           */
          List<Student> selectAll();
      }

> CourseDAO

package dao;
      
      import entity.Course;
      
      import java.util.List;
      
      /**
       * @ClassName CourseDAO
       * @Description TODO
       * @Author Jiangnan Cui
       * @Date 2022/4/16 21:58
       * @Version 1.0
       */
      public interface CourseDAO {
          /**
           * @MethodName selectAll
           * @Description 查询课程信息的同时把学生信息也一并查出来
           * @return: java.util.List<entity.Course>
           * @Author Jiangnan Cui
           * @Date 2022/4/16 21:58
           */
          List<Course> selectAll();
      }

#### 3.4 创建mapper配置文件 ####

> StudentDAOMapper

<?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="dao.StudentDAO">
          
          <resultMap id="studentMap" type="entity.Student">
              <id column="id" property="id"/>
              <result column="name" property="name"/>
              <result column="age" property="age"/>
              
              <collection property="courses" javaType="list" ofType="entity.Course">
                  <id column="cid" property="id"/>
                  <result column="cname" property="name"/>
              </collection>
          </resultMap>
          
          
          <select id="selectAll" resultMap="studentMap">
              SELECT 
                  s.id,s.name,s.age,
                  c.id cid,c.name cname
              FROM t_student s
              LEFT JOIN t_student_course sc
              ON s.`id`=sc.`sid`
              LEFT JOIN t_course c
              ON sc.`cid`=c.`id`
          </select>
      
      </mapper>

> CourseDAOMapper

<?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="dao.CourseDAO">
          
          <resultMap id="courseMap" type="entity.Course">
              <id column="id" property="id"/>
              <result column="name" property="name"/>
              
              <collection property="students" javaType="list" ofType="entity.Student">
                  <id column="sid" property="id"/>
                  <result column="sname" property="name"/>
                  <result column="age" property="age"/>
              </collection>
          </resultMap>
          
          <select id="selectAll" resultMap="courseMap">
              SELECT
                  c.id,c.name,
                  s.id sid,s.name sname,s.age
              FROM t_student s
              LEFT JOIN t_student_course sc
              ON s.`id`=sc.`sid`
              LEFT JOIN t_course c
              ON sc.`cid`=c.`id`
          </select>
      </mapper>

#### 3.5 mybatis-config.xml配置mapper ####

#### 3.6 测试连接查询 ####

package test;
      
      import dao.*;
      import entity.Course;
      import org.apache.ibatis.session.SqlSession;
      import org.junit.Test;
      import util.MybatisUtil;
      
      /**
       * @ClassName TestDAO
       * @Description TODO
       * @Author Jiangnan Cui
       * @Date 2022/4/16 16:37
       * @Version 1.0
       */
      public class TestDAO {
          /**
           * @MethodName testStudentDAO
           * @Description 测试StudentDAO组件连接查询
           * @Author Jiangnan Cui
           * @Date 2022/4/16 22:05
           */
          @Test
          public void testStudentDAO(){
              SqlSession sqlSession = MybatisUtil.getSqlSession();
              StudentDAO studentDAO = sqlSession.getMapper(StudentDAO.class);
              studentDAO.selectAll().forEach(student -> System.out.println("student = " + student + " " + student.getCourses()));
              MybatisUtil.close();
          }
      
          /**
           * @MethodName testCourseDAO
           * @Description 测试CourseDAO组件连接查询
           * @Author Jiangnan Cui
           * @Date 2022/4/16 22:12
           */
          @Test
          public void testCourseDAO(){
              SqlSession sqlSession = MybatisUtil.getSqlSession();
              CourseDAO courseDAO = sqlSession.getMapper(CourseDAO.class);
              courseDAO.selectAll().forEach(course -> System.out.println("course = " + course + " " + course.getStudents()));
              MybatisUtil.close();
          }
      }

**测试结果：**

**（1）StudentDAO**

DEBUG] 2022-04-16 main dao.StudentDAO.selectAll ==>  Preparing: SELECT s.id,s.name,s.age, c.id cid,c.name cname FROM t_student s LEFT JOIN t_student_course sc ON s.`id`=sc.`sid` LEFT JOIN t_course c ON sc.`cid`=c.`id` 
      [DEBUG] 2022-04-16 main dao.StudentDAO.selectAll ==> Parameters: 
      [DEBUG] 2022-04-16 main dao.StudentDAO.selectAll <==      Total: 5
      student = Student{id='11', name='小明', age=23} [Course{id='1', name='语文'}, Course{id='2', name='数学'}, Course{id='3', name='政治'}]
      student = Student{id='22', name='小王', age=23} [Course{id='1', name='语文'}, Course{id='3', name='政治'}]

**（2）CourseDAO**

[DEBUG] 2022-04-16 main dao.CourseDAO.selectAll ==>  Preparing: SELECT c.id,c.name, s.id sid,s.name sname,s.age FROM t_student s LEFT JOIN t_student_course sc ON s.`id`=sc.`sid` LEFT JOIN t_course c ON sc.`cid`=c.`id` 
      [DEBUG] 2022-04-16 main dao.CourseDAO.selectAll ==> Parameters: 
      [DEBUG] 2022-04-16 main dao.CourseDAO.selectAll <==      Total: 5
      course = Course{id='1', name='语文'} [Student{id='11', name='小明', age=23}, Student{id='22', name='小王', age=23}]
      course = Course{id='2', name='数学'} [Student{id='11', name='小明', age=23}]
      course = Course{id='3', name='政治'} [Student{id='11', name='小明', age=23}, Student{id='22', name='小王', age=23}]

[Mybatis _bilibili]: https://www.bilibili.com/video/BV1EE411W7Q1?p=10
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAQ29kZXJfQ3Vp_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/33ca4ee0a36f43f4a129a5793212bce0.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAQ29kZXJfQ3Vp,size_20,color_FFFFFF,t_70,g_se,x_16
[823f8b545c5640adb31c5e63006b7937.png]: https://img-blog.csdnimg.cn/823f8b545c5640adb31c5e63006b7937.png
[Mybatis _bilibili 1]: https://www.bilibili.com/video/BV1EE411W7Q1?p=11
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAQ29kZXJfQ3Vp_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/be21a21c55c2411484aa1c1248deff93.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAQ29kZXJfQ3Vp,size_20,color_FFFFFF,t_70,g_se,x_16
[Mybatis _bilibili 2]: https://www.bilibili.com/video/BV1EE411W7Q1?p=13
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAQ29kZXJfQ3Vp_size_20_color_FFFFFF_t_70_g_se_x_16 2]: https://img-blog.csdnimg.cn/ed384713e13d48dda33c38a0e56023f5.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAQ29kZXJfQ3Vp,size_20,color_FFFFFF,t_70,g_se,x_16