使用MyBatisPlus实现一对多

使用MyBatisPlus实现一对多

一、创建数据库

/*
 Navicat Premium Data Transfer

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 80027
 Source Host           : localhost:3306
 Source Schema         : school

 Target Server Type    : MySQL
 Target Server Version : 80027
 File Encoding         : 65001

 Date: 14/04/2022 21:05:24
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class`  (
  `cid` int NOT NULL,
  `cname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `did` int NULL DEFAULT NULL,
  PRIMARY KEY (`cid`) USING BTREE,
  INDEX `fk_did`(`did`) USING BTREE,
  CONSTRAINT `fk_did` FOREIGN KEY (`did`) REFERENCES `dept` (`did`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES (1, 'cd01', 1);
INSERT INTO `class` VALUES (2, 'cd02', 1);
INSERT INTO `class` VALUES (3, 'cd03', 2);
INSERT INTO `class` VALUES (4, 'cd04', 2);

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `did` int NOT NULL,
  `dname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`did`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES (1, 'd01');
INSERT INTO `dept` VALUES (2, 'd02');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `sid` int NOT NULL,
  `sname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `cid` int NULL DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE,
  INDEX `fk_cid`(`cid`) USING BTREE,
  CONSTRAINT `fk_cid` FOREIGN KEY (`cid`) REFERENCES `class` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, 'sc01', 1);
INSERT INTO `student` VALUES (2, 'sc02', 1);
INSERT INTO `student` VALUES (3, 'sc03', 1);
INSERT INTO `student` VALUES (4, 'sc04', 2);
INSERT INTO `student` VALUES (5, 'sc05', 2);
INSERT INTO `student` VALUES (6, 'sc06', 2);
INSERT INTO `student` VALUES (7, 'sc07', 3);
INSERT INTO `student` VALUES (8, 'sc08', 3);
INSERT INTO `student` VALUES (9, 'sc09', 4);
INSERT INTO `student` VALUES (10, 'sc10', 4);

SET FOREIGN_KEY_CHECKS = 1;

二、添加依赖

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>fastjson</artifactId>
        <version>1.2.79</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.5.1</version>
    </dependency>
</dependencies>

三、创建application.yml文件

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/school?characterEncoding=utf8
    username: root
    password: 123456

四、创建实体类

package com.example.demoexcel.entity;

import com.baomidou.mybatisplus.annotation.TableField;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.List;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Dept {

    private Integer did;

    private String dname;

    @TableField(exist = false)
    private List<Class> classList;

}

package com.example.demoexcel.entity;

import com.baomidou.mybatisplus.annotation.TableField;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.List;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Class {

    private Integer cid;

    private String cname;

    @TableField(exist = false)
    private List<Student> studentList;
}

package com.example.demoexcel.entity;

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

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {

    private Integer sid;

    private String sname;

}

五、创建Dao

package com.example.demoexcel.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.demoexcel.entity.Dept;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface DeptDao extends BaseMapper<Dept> {
}

package com.example.demoexcel.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.demoexcel.entity.Class;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface ClassDao extends BaseMapper<Class> {
}

package com.example.demoexcel.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.demoexcel.entity.Student;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface StudentDao extends BaseMapper<Student> {
}

六、测试

@Autowired
    private DeptDao deptDao;

    @Autowired
    private ClassDao classDao;

    @Autowired
    private StudentDao studentDao;

    @Test
    void test01(){
        List<Dept> depts = deptDao.selectList(null);

        List<Dept> deptList = new ArrayList<>();

        for (Dept dept : depts) {
            //1、根据部门ID查看班级信息
            List<Class> classList = classDao.selectList(new QueryWrapper<Class>().eq("did", dept.getDid()));
            dept.setClassList(classList);
            //2、根据班级ID查看学生信息
            for (Class aClass : classList) {
                List<Student> studentList = studentDao.selectList(new QueryWrapper<Student>().eq("cid", aClass.getCid()));
                aClass.setStudentList(studentList);
            }
            deptList.add(dept);
        }

        System.out.println(JSON.toJSONString(deptList));

    }

[
    {
        "classList":[
            {
                "cid":1,
                "cname":"cd01",
                "studentList":[
                    {
                        "sid":1,
                        "sname":"sc01"
                    },
                    {
                        "sid":2,
                        "sname":"sc02"
                    },
                    {
                        "sid":3,
                        "sname":"sc03"
                    }
                ]
            },
            {
                "cid":2,
                "cname":"cd02",
                "studentList":[
                    {
                        "sid":4,
                        "sname":"sc04"
                    },
                    {
                        "sid":5,
                        "sname":"sc05"
                    },
                    {
                        "sid":6,
                        "sname":"sc06"
                    }
                ]
            }
        ],
        "did":1,
        "dname":"d01"
    },
    {
        "classList":[
            {
                "cid":3,
                "cname":"cd03",
                "studentList":[
                    {
                        "sid":7,
                        "sname":"sc07"
                    },
                    {
                        "sid":8,
                        "sname":"sc08"
                    }
                ]
            },
            {
                "cid":4,
                "cname":"cd04",
                "studentList":[
                    {
                        "sid":9,
                        "sname":"sc09"
                    },
                    {
                        "sid":10,
                        "sname":"sc10"
                    }
                ]
            }
        ],
        "did":2,
        "dname":"d02"
    }
]