首页 计算机学科 database mybatis mybatisplus-join

# MyBatis-Plus - 基本查询使用

创建数据库：

student

/*
 Navicat Premium Data Transfer
 Source Server         : windows
 Source Server Type    : MySQL
 Source Server Version : 80028
 Source Host           : localhost:3306
 Source Schema         : mpj_test
 Target Server Type    : MySQL
 Target Server Version : 80028
 File Encoding         : 65001
 Date: 30/03/2024 09:50:08
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for tab_student
-- ----------------------------
DROP TABLE IF EXISTS `tab_student`;
CREATE TABLE `tab_student`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '学生id',
  `teacher_id` int NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '学生名称',
  `age` int NULL DEFAULT NULL COMMENT '学生年龄',
  `sex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '学生性别',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of tab_student
-- ----------------------------
INSERT INTO `tab_student` VALUES (1, 1, '张三', 18, '男');
INSERT INTO `tab_student` VALUES (2, 1, '李四', 20, '男');
INSERT INTO `tab_student` VALUES (3, 1, '王五', 14, '男');
SET FOREIGN_KEY_CHECKS = 1;
teacher
/*
 Navicat Premium Data Transfer
 Source Server         : windows
 Source Server Type    : MySQL
 Source Server Version : 80028
 Source Host           : localhost:3306
 Source Schema         : mpj_test
 Target Server Type    : MySQL
 Target Server Version : 80028
 File Encoding         : 65001
 Date: 30/03/2024 09:50:16
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for tab_teacher
-- ----------------------------
DROP TABLE IF EXISTS `tab_teacher`;
CREATE TABLE `tab_teacher`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '老师id',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '老师名称',
  `age` int NULL DEFAULT NULL COMMENT '老师年龄',
  `sex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '老师性别',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of tab_teacher
-- ----------------------------
INSERT INTO `tab_teacher` VALUES (1, '小郭', 19, '男');
SET FOREIGN_KEY_CHECKS = 1;
创建对应的 VO 类
工程结构：
image-20240330095229805
@Data
public class StudentVO
{
    private Integer id;
    private String teacherId;
    private TeacherEntity teacher;
    private String name;
    private String sex;
}
@Data
public class StudentVO1
{
    private Integer studentId;
    private Integer teacherId;
    private String studentName;
    private String teacherName;
    private String studentSex;
    private String teacherSex;
}
@Data
public class TeacherVO
{
    private Integer id;
    private List<String> students;
    private String name;
    private String sex;
}
@Data
public class TeacherVO1
{
    private Integer id;
    private List<StudentEntity> students;
    private String name;
    private String sex;
}
# 一对一 (字段)
@Override
public List<StudentVO1> getSAll()
{
   // 实例化 MPJLambdaWrapper
   MPJLambdaWrapper<StudentEntity> wrapper = new MPJLambdaWrapper<>();
   // 左连接联表查询，根据 teacher 的 id 和 student 的 teacher_id 进行联表查询
   wrapper.leftJoin(TeacherEntity.class, TeacherEntity::getId, StudentEntity::getTeacherId);
   // 查询指定字段并设置别名 因为，数据的字段名都一样而我需要再 VO 类里面进行区别并查询出来
   wrapper.selectAs(StudentEntity::getId, "studentId");
   wrapper.selectAs(TeacherEntity::getId, "teacherId");
   wrapper.selectAs(StudentEntity::getName, "studentName");
   wrapper.selectAs(TeacherEntity::getName, "teacherName");
   wrapper.selectAs(StudentEntity::getSex, "studentSex");
   wrapper.selectAs(TeacherEntity::getSex, "teacherSex");
   // 查询结果返回到 VO1 类
   return studentDao.selectJoinList(StudentVO1.class, wrapper);
}
打印结果
StudentVO1(studentId=1, teacherId=1, studentName=张三, teacherName=小郭, studentSex=男, teacherSex=男)
StudentVO1(studentId=2, teacherId=1, studentName=李四, teacherName=小郭, studentSex=男, teacherSex=男)
StudentVO1(studentId=3, teacherId=1, studentName=王五, teacherName=小郭, studentSex=男, teacherSex=男)

# 一对一 (对象)
@Override
public List<StudentVO> getAll()
{
   // 实例化 MPJLambdaWrapper
   MPJLambdaWrapper<StudentEntity> wrapper = new MPJLambdaWrapper<>();
   // 左连接联表查询 根据 teacher 的 id 和 student 的 teacher_id 来进行联查
   wrapper.leftJoin(TeacherEntity.class, TeacherEntity::getId, StudentEntity::getTeacherId);
   // 查询 student 的所有字段的数据
   wrapper.selectAll(StudentEntity.class);
   // 查询 teacher 表用 StudentVO 中的 teacher 类来进行属性字段的关联
   wrapper.selectAssociation(TeacherEntity.class, StudentVO::getTeacher);
   // 查询结果返回到 StudentVO 类中
   return studentDao.selectJoinList(StudentVO.class, wrapper);
}
打印结果：
StudentVO(id=1, teacherId=1, teacher=TeacherEntity(id=1, name=小郭, age=19, sex=男), name=张三, sex=男)
StudentVO(id=2, teacherId=1, teacher=TeacherEntity(id=1, name=小郭, age=19, sex=男), name=李四, sex=男)
StudentVO(id=3, teacherId=1, teacher=TeacherEntity(id=1, name=小郭, age=19, sex=男), name=王五, sex=男)

# 一对多 (字段)
@Override
public List<TeacherVO> getAll()
{
   // 实例化 MPJLambdaWrapper
   MPJLambdaWrapper<TeacherEntity> wrapper = new MPJLambdaWrapper<>();
   // 左连接链表查询 根据 teacher 的 id 和 student 的 id 进行联表查询
   wrapper.leftJoin(StudentEntity.class, StudentEntity::getTeacherId, TeacherEntity::getId);
   // 查询 teacher 的所有字段数据
   wrapper.selectAll(TeacherEntity.class);
   // 查询的结果返回的是 List 集合，而且通过 map.result 进行映射下结果 只要学生名称 如数据：[张三，李四，王五]
   wrapper.selectCollection(StudentEntity.class, TeacherVO::getStudents, map -> map.result(StudentEntity::getName));
   // 返回查询结果到 VO 类
   return teacherDao.selectJoinList(TeacherVO.class, wrapper);
}
打印结果：
TeacherVO(id=1, students=[王五, 李四, 张三], name=小郭, sex=男)

# 一对多 (对象)
@Override
public List<TeacherVO1> findAll()
{
   // 实例化 MPJLambdaWrapper
   MPJLambdaWrapper<TeacherEntity> wrapper = new MPJLambdaWrapper<>();
   // 左连接联表查询，根据 teacher 的 id 和 student 的 teacher_id 进行联表查询
   wrapper.leftJoin(StudentEntity.class, StudentEntity::getTeacherId, TeacherEntity::getId);
   // 查询 teacher 的所有字段数据
   wrapper.selectAll(TeacherEntity.class);
   // 这里只是少了 map 去映射返回的结果，而我们这次得到的是多个对象
   wrapper.selectCollection(StudentEntity.class, TeacherVO1::getStudents);
   // 返回查询结果到 VO1 类
   return teacherDao.selectJoinList(TeacherVO1.class, wrapper);
}
打印结果：
TeacherVO1(id=1, students=[StudentEntity(id=3, teacherId=1, name=王五, age=14, sex=男), StudentEntity(id=2, teacherId=1, name=李四, age=20, sex=男), StudentEntity(id=1, teacherId=1, name=张三, age=18, sex=男)], name=小郭, sex=男)

# 多对多查询
数据库表：
class
/*
 Navicat Premium Data Transfer
 Source Server         : windows
 Source Server Type    : MySQL
 Source Server Version : 80028
 Source Host           : localhost:3306
 Source Schema         : mpj_test
 Target Server Type    : MySQL
 Target Server Version : 80028
 File Encoding         : 65001
 Date: 30/03/2024 10:56:43
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for tab_class
-- ----------------------------
DROP TABLE IF EXISTS `tab_class`;
CREATE TABLE `tab_class`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '课程表id',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '课程名称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of tab_class
-- ----------------------------
INSERT INTO `tab_class` VALUES (1, '数学');
INSERT INTO `tab_class` VALUES (2, '语文');
INSERT INTO `tab_class` VALUES (3, '英语');
INSERT INTO `tab_class` VALUES (4, '化学');
INSERT INTO `tab_class` VALUES (5, '生物');
INSERT INTO `tab_class` VALUES (6, '物理');
INSERT INTO `tab_class` VALUES (7, '政治');
INSERT INTO `tab_class` VALUES (8, '历史');
INSERT INTO `tab_class` VALUES (9, '地理');
SET FOREIGN_KEY_CHECKS = 1;
student
/*
 Navicat Premium Data Transfer
 Source Server         : windows
 Source Server Type    : MySQL
 Source Server Version : 80028
 Source Host           : localhost:3306
 Source Schema         : mpj_test
 Target Server Type    : MySQL
 Target Server Version : 80028
 File Encoding         : 65001
 Date: 30/03/2024 10:56:55
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for tab_studenttwo
-- ----------------------------
DROP TABLE IF EXISTS `tab_studenttwo`;
CREATE TABLE `tab_studenttwo`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '学生id',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '学生名称',
  `age` int NULL DEFAULT NULL COMMENT '学生年龄',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of tab_studenttwo
-- ----------------------------
INSERT INTO `tab_studenttwo` VALUES (1, '张三', 18);
INSERT INTO `tab_studenttwo` VALUES (2, '李四', 19);
INSERT INTO `tab_studenttwo` VALUES (3, '王五', 20);
SET FOREIGN_KEY_CHECKS = 1;
cs
/*
 Navicat Premium Data Transfer
 Source Server         : windows
 Source Server Type    : MySQL
 Source Server Version : 80028
 Source Host           : localhost:3306
 Source Schema         : mpj_test
 Target Server Type    : MySQL
 Target Server Version : 80028
 File Encoding         : 65001
 Date: 30/03/2024 10:56:49
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for tab_cs
-- ----------------------------
DROP TABLE IF EXISTS `tab_cs`;
CREATE TABLE `tab_cs`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '中间表id(查询学生与课程的关系)',
  `c_id` int NULL DEFAULT NULL COMMENT '课程表id',
  `s_id` int NULL DEFAULT NULL COMMENT '学生表id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of tab_cs
-- ----------------------------
INSERT INTO `tab_cs` VALUES (1, 1, 1);
INSERT INTO `tab_cs` VALUES (2, 2, 1);
INSERT INTO `tab_cs` VALUES (3, 3, 1);
INSERT INTO `tab_cs` VALUES (4, 1, 2);
INSERT INTO `tab_cs` VALUES (5, 2, 2);
INSERT INTO `tab_cs` VALUES (6, 3, 2);
INSERT INTO `tab_cs` VALUES (7, 4, 2);
INSERT INTO `tab_cs` VALUES (8, 1, 3);
INSERT INTO `tab_cs` VALUES (9, 6, 3);
SET FOREIGN_KEY_CHECKS = 1;
联查参考 sql 语句：
SELECT
	ts.`name` student_name,
	ts.age student_age,
	tcs.`name` class_name
FROM
	tab_studenttwo ts
	LEFT JOIN tab_cs tc ON tc.s_id = ts.id
	LEFT JOIN tab_class tcs ON tcs.id = tc.c_id
WHERE
	ts.`name` = '王五'
查询结果：
image-20240330105633348
# java 代码
vo 类:
@Data
public class StudentClassVO
{
    private String studentName;
    private Integer studentAge;
    private String className;
}
serviceImpl:
@Override
public List<StudentClassVO> getAll()
{
   MPJLambdaWrapper<StudenttwoEntity> wrapper = new MPJLambdaWrapper<>();
   wrapper.leftJoin(CsEntity.class, CsEntity::getSId, StudenttwoEntity::getId);
   wrapper.leftJoin(ClassEntity.class, ClassEntity::getId, CsEntity::getCId);
   wrapper.selectAs(StudenttwoEntity::getName, "studentName");
   wrapper.selectAs(StudenttwoEntity::getAge, "studentAge");
   wrapper.selectAs(ClassEntity::getName, "className");
   return studenttwoDao.selectJoinList(StudentClassVO.class, wrapper);
}
查询结果：
StudentClassVO(studentName=张三, studentAge=18, className=英语)
StudentClassVO(studentName=张三, studentAge=18, className=语文)
StudentClassVO(studentName=张三, studentAge=18, className=数学)
StudentClassVO(studentName=李四, studentAge=19, className=化学)
StudentClassVO(studentName=李四, studentAge=19, className=英语)
StudentClassVO(studentName=李四, studentAge=19, className=语文)
StudentClassVO(studentName=李四, studentAge=19, className=数学)
StudentClassVO(studentName=王五, studentAge=20, className=物理)
StudentClassVO(studentName=王五, studentAge=20, className=数学)