最近正在集成SpringBoot与MyBatis-plus, MyBatis-Plus-Join体验感很好啊
基本介绍
MyBatis-Plus (opens new window)（简称 MP）是一个 MyBatis (opens new window)的增强工具，在 MyBatis 的基础上只做增强不做改变，为简化开发、提高效率而生。
MyBatis-Plus特性
无侵入：只做增强不做改变，引入它不会对现有工程产生影响，如丝般顺滑
无感引入, 支持MP风格的查询, 您会MP就会MPJ, 无需额外的学习成本
兼容MP的别名、逻辑删除、枚举列、TypeHandle列等特性
兼容MP的别名、逻辑删除、枚举列、TypeHandle列等特性
支持注解形式一对一、一对多和连表查询形式的一对一和一对多
MyBatis-Plus-Join (opens new window)（简称 MPJ）是一个 MyBatis-Plus (opens new window)的增强工具，在 MyBatis-Plus 的基础上只做增强不做改变，为简化开发、提高效率而生。
MyBatis-Plus-Join特性
无侵入：只做增强不做改变，引入它不会对现有工程产生影响，如丝般顺滑
损耗小：启动即会自动注入基本 CURD，性能基本无损耗，直接面向对象操作
强大的 CRUD 操作：内置通用 Mapper、通用 Service，仅仅通过少量配置即可实现单表大部分 CRUD 操作，更有强大的条件构造器，满足各类使用需求
支持 Lambda 形式调用：通过 Lambda 表达式，方便的编写各类查询条件，无需再担心字段写错
支持主键自动生成：支持多达 4 种主键策略（内含分布式唯一 ID 生成器 - Sequence），可自由配置，完美解决主键问题
支持 ActiveRecord 模式：支持 ActiveRecord 形式调用，实体类只需继承 Model 类即可进行强大的 CRUD 操作
支持自定义全局通用操作：支持全局通用方法注入（ Write once, use anywhere ）
内置代码生成器：采用代码或者 Maven 插件可快速生成 Mapper 、 Model 、 Service 、 Controller 层代码，支持模板引擎，更有超多自定义配置等您来使用
内置分页插件：基于 MyBatis 物理分页，开发者无需关心具体操作，配置好插件之后，写分页等同于普通 List 查询
分页插件支持多种数据库：支持 MySQL、MariaDB、Oracle、DB2、H2、HSQL、SQLite、Postgre、SQLServer 等多种数据库
内置性能分析插件：可输出 SQL 语句以及其执行时间，建议开发测试时启用该功能，能快速揪出慢查询
内置全局拦截插件：提供全表 delete 、 update 操作智能分析阻断，也可自定义拦截规则，预防误操作
丝滑使用开始了
sql
CREATE TABLE `class` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
`name` varchar(30) DEFAULT NULL COMMENT '班级名',
`floor` int(3) DEFAULT NULL COMMENT '楼层',
`teacher_id` int(11) DEFAULT NULL COMMENT '老师编号(FK)',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='班级信息表';
INSERT INTO `class` VALUES ('1', '1年级2班', '4', '2');
INSERT INTO `class` VALUES ('2', '1年级3班', '4', '2');
INSERT INTO `class` VALUES ('3', '2年级1班', '1', '4');
INSERT INTO `class` VALUES ('4', '2年级2班', '2', '5');
INSERT INTO `class` VALUES ('5', '2年级3班', '3', '6');
INSERT INTO `class` VALUES ('6', '3年级1班', '4', '7');
INSERT INTO `class` VALUES ('7', '3年级2班', '1', '8');
INSERT INTO `class` VALUES ('8', '3年级3班', '2', '9');
INSERT INTO `class` VALUES ('9', '4年级1班', '3', '10');
INSERT INTO `class` VALUES ('10', '4年级2班', '4', '11');
INSERT INTO `class` VALUES ('11', '4年级3班', '1', '12');
INSERT INTO `class` VALUES ('12', '5年级1班', '2', '13');
INSERT INTO `class` VALUES ('13', '5年级3班', '4', '15');
INSERT INTO `class` VALUES ('14', '6年级2班', '2', '17');
INSERT INTO `class` VALUES ('15', '6年级3班', '3', '18');
INSERT INTO `class` VALUES ('16', '1年级4班', '4', '99');
INSERT INTO `class` VALUES ('17', '2年级4班', '2', '99');
INSERT INTO `class` VALUES ('18', '3年级4班', '1', '99');
INSERT INTO `class` VALUES ('19', '4年级1班', '1', '20');
INSERT INTO `class` VALUES ('30', '4年级1班', '22', '20');
CREATE TABLE `teacher` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
`name` varchar(30) DEFAULT NULL COMMENT '名字',
`age` int(2) DEFAULT NULL COMMENT '年龄',
`title` varchar(30) DEFAULT NULL COMMENT '职称',
`manager` int(11) DEFAULT NULL COMMENT '上司编号(FK)',
`salary` int(6) DEFAULT NULL COMMENT '工资',
`comm` int(6) DEFAULT NULL COMMENT '奖金',
`gender` char(1) DEFAULT NULL COMMENT '性别',
`subject_id` int(11) DEFAULT NULL COMMENT '科目编号(FK)',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='老师信息表';
INSERT INTO `teacher` VALUES ('1', '刘苍松', '55', '总监', '20', '10000', '5000', '男', '1');
INSERT INTO `teacher` VALUES ('2', '范传奇', '33', '三级讲师', '1', '3000', null, '男', '2');
INSERT INTO `teacher` VALUES ('3', '王克晶', '32', '一级讲师', '1', '8000', '3000', '女', '3');
INSERT INTO `teacher` VALUES ('4', '刘国斌', '29', '二级讲师', '1', '7300', '3400', '男', '4');
INSERT INTO `teacher` VALUES ('5', '成恒', '35', '三级讲师', '1', '5200', '4600', '男', '5');
INSERT INTO `teacher` VALUES ('6', '张皓岚', '33', '二级讲师', '1', '7800', '700', '男', '2');
INSERT INTO `teacher` VALUES ('7', '胡悦', '25', '一级讲师', '1', '9000', '2800', '女', '1');
INSERT INTO `teacher` VALUES ('8', '齐雷', '45', '总监', '20', '9800', '7800', '男', '3');
INSERT INTO `teacher` VALUES ('9', '王海涛', '44', '二级讲师', '8', '3100', '1200', '男', '4');
INSERT INTO `teacher` VALUES ('10', '张久军', '39', '一级讲师', '8', '2000', '6000', '男', '5');
INSERT INTO `teacher` VALUES ('11', '于健', '27', '二级讲师', '12', '3800', null, '男', '2');
INSERT INTO `teacher` VALUES ('12', '张立志', '34', '总监', '20', '13000', '900', '男', '3');
INSERT INTO `teacher` VALUES ('13', '吴华', '46', '总监', '20', '8700', '6900', '男', '5');
INSERT INTO `teacher` VALUES ('14', '李大帅', '29', '三级讲师', '13', '7000', null, '男', '1');
INSERT INTO `teacher` VALUES ('15', '田浩', '26', '二级讲师', '13', '5600', '1900', '男', '4');
INSERT INTO `teacher` VALUES ('16', '肖旭伟', '36', '一级讲师', '12', '6800', '600', '男', '2');
INSERT INTO `teacher` VALUES ('17', '张敏', '29', '三级讲师', '13', '6100', '400', '女', '3');
INSERT INTO `teacher` VALUES ('18', '赵微', '26', '二级讲师', '13', '4600', '6500', '女', '4');
INSERT INTO `teacher` VALUES ('19', '李源', '25', '三级讲师', '13', '6300', '0', '男', '4');
INSERT INTO `teacher` VALUES ('20', '韩少云', '55', '老板', null, '1', '0', '男', null);
pom.xml
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.28</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.8</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.24</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.3</version>
</dependency>
<dependency>
<groupId>com.github.yulichang</groupId>
<artifactId>mybatis-plus-join-boot-starter</artifactId>
<version>1.4.6</version>
</dependency>
application.yml
spring:
# 数据库连接配置
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/hedu?useSSL=false&serverTimeZone=Asia/Shanghai
username: root
password: root
# mybatis-plus配置
mybatis-plus:
mapper-locations: classpath:mappers/*.xml
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
Mybatis-plus的分页插件的配置
package cn.highedu.boot.config;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
public class MybatisPlusConfig {
/**
* 分页插件的配置
* @return
*/
@Bean
public MybatisPlusInterceptor paginationInterceptor(){
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
/**
* DbType.XXX
* XXX 为具体的数据库类型如MYSQL,ORACLE
*/
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
return interceptor;
}
}
创建基本类
Class
package cn.highedu.boot.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Class {
@TableId(type = IdType.AUTO)
private Integer id;
private String name;
private Integer floor;
private Integer teacherId;
}
ClassMapper
package cn.highedu.boot.mapper;
import cn.highedu.boot.entity.Class;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.github.yulichang.base.MPJBaseMapper;
import org.apache.ibatis.annotations.*;
@Mapper
public interface ClassMapper extends BaseMapper<Class>, MPJBaseMapper<Class> {
}
ClassService
package cn.highedu.boot.service;
import cn.highedu.boot.entity.Class;
import com.baomidou.mybatisplus.extension.service.IService;
public interface ClassService extends IService<Class> {
}
ClassServiceImpl
package cn.highedu.boot.service.impl;
import cn.highedu.boot.entity.Class;
import cn.highedu.boot.mapper.ClassMapper;
import cn.highedu.boot.service.ClassService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.stereotype.Service;
@Service
public class ClassServiceImpl extends ServiceImpl<ClassMapper, Class> implements ClassService {
}
Teacher
package cn.highedu.boot.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
private Integer id;
private String name;
private Integer age;
private String title;
private Integer manager;
private Integer salary;
private Integer comm;
private Character gender;
private Integer subjectId;
}
TeacherMapper
package cn.highedu.boot.mapper;
import cn.highedu.boot.entity.Teacher;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.github.yulichang.base.MPJBaseMapper;
import org.apache.ibatis.annotations.*;
@Mapper
public interface TeacherMapper extends BaseMapper<Teacher>, MPJBaseMapper<Teacher> {
}
TeacherService
package cn.highedu.boot.service;
import cn.highedu.boot.entity.Teacher;
import com.baomidou.mybatisplus.extension.service.IService;
public interface TeacherService extends IService<Teacher> {
}
TeacherServiceImpl
package cn.highedu.boot.service.impl;
import cn.highedu.boot.entity.Teacher;
import cn.highedu.boot.mapper.TeacherMapper;
import cn.highedu.boot.service.TeacherService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.stereotype.Service;
@Service
public class TeacherServiceImpl extends ServiceImpl<TeacherMapper, Teacher> implements TeacherService {
}
TeacherDTO
package cn.highedu.boot.pojo.dto;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class TeacherDTO {
@TableId(type = IdType.AUTO)
private Integer id;
private String name;
private Integer age;
private String title;
private Integer manager;
private Integer salary;
private Integer comm;
private Character gender;
private Integer subjectId;
private String className;
private Integer floor;
}
单表操作
SingleTableOperationTest
package cn.highedu.boot.service;
import cn.highedu.boot.entity.Class;
import cn.highedu.boot.mapper.ClassMapper;
import cn.highedu.boot.service.impl.ClassServiceImpl;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
@SpringBootTest
class ClassServiceTest extends ClassServiceImpl {
@Autowired
private ClassMapper classMapperSimple;
//基本操作
//insert
/**
* 新增一条记录
*/
@Test
void simpleAddOne(){
Class addOneClazz = new Class();
addOneClazz.setName("4年级1班");
addOneClazz.setFloor(22);
addOneClazz.setTeacherId(20);
//INSERT INTO class ( name, floor, teacher_id ) VALUES ( ?, ?, ? );
int addOneResult = classMapperSimple.insert(addOneClazz);
System.out.println(addOneResult);
}
//delete
/**
* 根据Id删除一条记录
*/
@Test
void simpleDeleteOneById(){
//DELETE FROM class WHERE id=?;
int deleteByIdResult = classMapperSimple.deleteById(1);
System.out.println(deleteByIdResult);
}
//update
/**
* 根据Id更改一条记录
*/
@Test
void simpleUpdateOneById(){
Class updateClass = new Class();
updateClass.setId(2);
updateClass.setName("1年级3班");
updateClass.setFloor(4);
updateClass.setTeacherId(2);
//UPDATE class SET name=?, floor=?, teacher_id=? WHERE id=?;
int updateByIdResult = classMapperSimple.updateById(updateClass);
System.out.println(updateByIdResult);
}
//select
/**
* 根据Id查询一条记录
*/
@Test
void simpleQueryOneById(){
//SELECT id,name,floor,teacher_id FROM class WHERE id=?;
Class queryOneClass = classMapperSimple.selectById(1);
System.out.println(queryOneClass);
}
/**
* 查询所有记录
*/
@Test
void simpleQueryAll() {
//SELECT id,name,floor,teacher_id FROM class;
List<Class> queryClasses = classMapperSimple.selectList(null);
System.out.println(queryClasses);
}
/**
*| 函数名 | 说明 |
*| ----------- | ----------- |
*| eq | 等于 |
*| ne | 不等 |
*| gt | 大于 |
*| it | 小于 |
*| between | 在值1到值2之间 |
*| like | 模糊查询 |
*| isNull | 字段为NULL |
*/
@Autowired
private ClassMapper classMapperComplex;
//进阶操作
//单表操作
//select
/**
* 根据Id查询1条数据
*/
@Test
void complexQueryOne(){
LambdaQueryWrapper<Class> queryWrapper = Wrappers.lambdaQuery();
queryWrapper.eq(Class::getId, 1);
//SELECT id,name,floor,teacher_id FROM class WHERE (id = ?);
Class complexQueryOne = classMapperComplex.selectOne(queryWrapper);
System.out.println(complexQueryOne);
}
/**
* 查询相同条件的数据
*/
@Test
void complexQueryAll(){
LambdaQueryWrapper<Class> queryWrapper = Wrappers.lambdaQuery();
queryWrapper.eq(Class::getFloor,1);
//SELECT id,name,floor,teacher_id FROM class WHERE (floor = ?);
List<Class> complexQueryAll = classMapperComplex.selectList(queryWrapper);
System.out.println(complexQueryAll);
}
/**
* 查询范围内的数据
*/
@Test
void complexBetweenQuery(){
LambdaQueryWrapper<Class> queryWrapper = Wrappers.lambdaQuery();
queryWrapper.between(Class::getFloor,1,3);
//SELECT id,name,floor,teacher_id FROM class WHERE (floor BETWEEN ? AND ?);
List<Class> complexQueryAll = classMapperComplex.selectList(queryWrapper);
System.out.println(complexQueryAll);
}
/**
* 模糊查询
* like():前后加百分号,如 %1年级%
* likeLeft():前面加百分号,如 %1年级
* likeRight():后面加百分号,如 1年级%
*/
@Test
void complexLikeQuery(){
LambdaQueryWrapper<Class> queryWrapper = Wrappers.lambdaQuery();
queryWrapper.likeRight(Class::getName,"1年级");
// SELECT id,name,floor,teacher_id FROM class WHERE (name LIKE ?);
List<Class> complexLikeQuery = classMapperComplex.selectList(queryWrapper);
System.out.println(complexLikeQuery);
}
/**
* 按照楼层升序排列,从小到大
*/
@Test
void complexOrderQuery(){
LambdaQueryWrapper<Class> queryWrapper = Wrappers.lambdaQuery();
//condition ：条件，返回boolean，当condition为true，进行排序，如果为false，则不排序
//isAsc：是否为升序，true为升序，false为降序
//columns：需要操作的列
queryWrapper.orderBy(true,true,Class::getFloor);
//SELECT id,teacher_id,name,floor FROM class ORDER BY floor ASC;
List<Class> complexOrderByAscQuery= classMapperComplex.selectList(queryWrapper);
System.out.println(complexOrderByAscQuery);
}
/**
* 分页查询,查询第1页,3条数据
*/
@Test
void complexPageQuery(){
Page<Class> page = new Page<>(1,3);
classMapperComplex.selectPage(page, null);
System.out.println(page.getRecords());//每页数据list集合
System.out.println(page.getCurrent());//当前页
System.out.println(page.getSize());//每页显示记录数
System.out.println(page.getTotal());//总记录数
System.out.println(page.getPages());//总页数
System.out.println(page.hasPrevious());//上一页
System.out.println(page.hasNext());//下一页
}
}
多表操作
MultipleTableOperationTest
package cn.highedu.boot.service;
import cn.highedu.boot.pojo.dto.TeacherDTO;
import cn.highedu.boot.pojo.entity.Class;
import cn.highedu.boot.pojo.entity.Teacher;
import cn.highedu.boot.mapper.TeacherMapper;
import cn.highedu.boot.service.impl.TeacherServiceImpl;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.github.yulichang.wrapper.MPJLambdaWrapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
@SpringBootTest
public class MultipleTableOperationTest extends TeacherServiceImpl {
@Autowired
private TeacherMapper teacherMapper;
/**
*| 函数名 | 说明 |
*| ----------- | ----------- |
*| eq | 等于 |
*| ne | 不等 |
*| gt | 大于 |
*| it | 小于 |
*| between | 在值1到值2之间 |
*| like | 模糊查询 |
*| isNull | 字段为NULL |
*/
/**
* 查询楼层大于2的所有老师及各个老师所在的班级信息,并按楼层的升序排列
*/
@Test
void queryTeachersWhichOverFloor(){
MPJLambdaWrapper<Teacher> wrapper = new MPJLambdaWrapper<Teacher>();
wrapper.selectAll(Teacher.class)//查询Teacher表全部字段
.select(Class::getFloor, Class::getName)
.rightJoin(Class.class, Class::getTeacherId, Teacher::getId)
.gt(Class::getFloor,2)
.orderBy(true,true,Class::getFloor);
/* SELECT
* t.id,t.name,t.age,t.title,t.manager,t.salary,t.comm,t.gender,
* t.subject_id,t1.floor,t1.name
* FROM
* teacher t
* RIGHT JOIN
* class t1
* ON
* (t1.teacher_id = t.id);
* WHERE
* (t1.floor > ?)
* ORDER BY
* t1.floor ASC
*/
List<TeacherDTO> teacherList = teacherMapper.selectJoinList(TeacherDTO.class, wrapper);
System.out.println(teacherList);
}
/**
* 在上面的基础上,增添了分页的功能,查询第2页,每页5条数据
*/
@Test
void queryTeachersWhichOverFloorPage(){
MPJLambdaWrapper<Teacher> wrapper = new MPJLambdaWrapper<Teacher>();
wrapper.selectAll(Teacher.class)//查询Teacher表全部字段
.select(Class::getFloor, Class::getName)
.rightJoin(Class.class, Class::getTeacherId, Teacher::getId)
.gt(Class::getFloor,1)
.orderBy(true,true,Class::getFloor);
/* SELECT
* t.id,t.name,t.age,t.title,t.manager,t.salary,t.comm,t.gender,
* t.subject_id,t1.floor,t1.name
* FROM
* teacher t
* RIGHT JOIN
* class t1
* ON
* (t1.teacher_id = t.id);
* WHERE
* (t1.floor > ?)
* ORDER BY
* t1.floor ASC
* LIMIT
* ?,?
*/
IPage<TeacherDTO> teacherList = teacherMapper.selectJoinPage(new Page<>(2, 5), TeacherDTO.class,wrapper);
System.out.println(teacherList);
}
}
参考
MyBatis-Plus-Join官网--------------MyBatis-Plus官网
https://blog.csdn.net/qq_52880445/article/details/124632069