Springboot 整合MybatisPlus,为数据交互的基础。

1. Mybatis-Plus是一个Mybatis框架的增强插件,根据官方描述,MP只做增强不做改变,引入它不会对现有工程产生影响,如丝般顺滑.并且只需简单配置,即可快速进行 CRUD 操作,从而节省大量时间.代码生成,分页,性能分析等功能一应俱全,最新已经更新到了3.1.1版本了,3.X系列支持lambda语法,让我在写条件构造的时候少了很多的"魔法值",从代码结构上更简洁了.
2. maven 依赖如下

4.0.0com.zhl testDemoMybatisPlus 1.0-SNAPSHOTorg.springframework.boot spring-boot-starter-parent 2.1.3.RELEASE UTF-8UTF-8 1.8 1.3.8.RELEASE 1.0.26 2.1.9 1.0.5 org.springframework.boot spring-boot-starter-web org.springframework.boot spring-boot-starter-test test mysql mysql-connector-java 8.0.17 org.projectlombok lombok true com.baomidou mybatis-plus-boot-starter 3.0.5 com.alibaba druid-spring-boot-starter 1.1.10 【Springboot集成MybatisPlus、Druid】org.springframework.boot spring-boot-maven-plugin

3. 配置如下

# 配置端口 server: port: 8081 spring: # 配置数据源 datasource: driver-class-name: com.mysql.cj.jdbc.Driver url: "jdbc:mysql://xx.xx.xx.xx:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC" username: xxx password: xxx type: com.alibaba.druid.pool.DruidDataSource # mybatis-plus相关配置 mybatis-plus: # xml扫描，多个目录用逗号或者分号分隔（告诉 Mapper 所对应的 XML 文件位置） mapper-locations: classpath:mapper/*.xml # 以下配置均有默认值,可以不设置 global-config: db-config: #主键类型auto:"数据库ID自增" 1:"用户输入ID",2:"全局唯一ID (数字类型唯一ID)", 3:"全局唯一ID UUID"; id-type: auto #字段策略 IGNORED:"忽略判断"NOT_NULL:"非 NULL 判断")NOT_EMPTY:"非空判断" field-strategy: NOT_EMPTY #数据库类型 db-type: MYSQL configuration: # 是否开启自动驼峰命名规则映射:从数据库列名到Java属性驼峰命名的类似映射 map-underscore-to-camel-case: true # 如果查询结果中包含空值的列，则 MyBatis 在映射的时候，不会映射这个字段 call-setters-on-nulls: true # 这个配置会将执行的sql打印出来，在开发或测试的时候可以用 log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

4.项目结构

文章图片

5. 表结构

CREATE TABLE `user_info` ( `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT 'ID', `name` varchar(32) DEFAULT NULL COMMENT '姓名', `age` int(11) DEFAULT NULL COMMENT '年龄', `skill` varchar(32) DEFAULT NULL COMMENT '技能', `evaluate` varchar(64) DEFAULT NULL COMMENT '评价', `fraction` bigint(11) DEFAULT NULL COMMENT '分数', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COMMENT='学生信息表';

表数据

INSERT INTO `user_info` VALUES (1, '小明', 20, '画画', '该学生在画画方面有一定天赋', 89); INSERT INTO `user_info` VALUES (2, '小兰', 19, '游戏', '近期该学生由于游戏的原因导致分数降低了', 64); INSERT INTO `user_info` VALUES (3, '张张', 18, '英语', '近期该学生参加英语比赛获得二等奖', 90); INSERT INTO `user_info` VALUES (4, '大黄', 20, '体育', '该学生近期由于参加篮球比赛,导致脚伤', 76); INSERT INTO `user_info` VALUES (5, '大白', 17, '绘画', '该学生参加美术大赛获得三等奖', 77); INSERT INTO `user_info` VALUES (7, '小龙', 18, 'JAVA', '该学生是一个在改BUG的码农', 59); INSERT INTO `user_info` VALUES (9, 'Sans', 18, '睡觉', 'Sans是一个爱睡觉,并且身材较矮骨骼巨大的骷髅小胖子', 60); INSERT INTO `user_info` VALUES (10, 'papyrus', 18, 'JAVA', 'Papyrus是一个讲话大声、个性张扬的骷髅，给人自信、有魅力的骷髅小瘦子', 58); INSERT INTO `user_info` VALUES (11, '删除数据1', 3, '画肖像', NULL, 61); INSERT INTO `user_info` VALUES (12, '删除数据2', 3, NULL, NULL, 61); INSERT INTO `user_info` VALUES (13, '删除数据3', 3, NULL, NULL, 61); INSERT INTO `user_info` VALUES (14, '删除数据4', 5, '删除', NULL, 10); INSERT INTO `user_info` VALUES (15, '删除数据5', 6, '删除', NULL, 10);

/* Navicat Premium Data Transfer Source Server: 39.105.192.66 Source Server Type: MySQL Source Server Version : 50722 Source Host: 39.105.192.66:3306 Source Schema: test1 Target Server Type: MySQL Target Server Version : 50722 File Encoding: 65001 Date: 17/06/2020 09:07:17 */SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for class -- ---------------------------- DROP TABLE IF EXISTS `class`; CREATE TABLE `class`( `cid` int(11) NOT NULL DEFAULT 0, `caption` varchar(20) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '年级班级', PRIMARY KEY (`cid`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Compact; -- ---------------------------- -- Records of class -- ---------------------------- INSERT INTO `class` VALUES (1, '三年二班'); INSERT INTO `class` VALUES (2, '一年三班'); INSERT INTO `class` VALUES (3, '三年一班'); -- ---------------------------- -- Table structure for course -- ---------------------------- DROP TABLE IF EXISTS `course`; CREATE TABLE `course`( `cid` int(11) NULL DEFAULT NULL, `cname` varchar(20) CHARACTER SET gbk COLLATE gbk_chinese_ci NULL DEFAULT NULL COMMENT '姓名', `tearch` int(11) NULL DEFAULT NULL ) ENGINE = InnoDB CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Compact; -- ---------------------------- -- Records of course -- ---------------------------- INSERT INTO `course` VALUES (1, '生物', 1); INSERT INTO `course` VALUES (2, '体育', 2); INSERT INTO `course` VALUES (3, '物理', 3); -- ---------------------------- -- Table structure for facultylist -- ---------------------------- DROP TABLE IF EXISTS `facultylist`; CREATE TABLE `facultylist`( `id` bigint(20) NOT NULL, `facultyName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of facultylist -- ---------------------------- INSERT INTO `facultylist` VALUES (1, '计算机与通信工程学院'); INSERT INTO `facultylist` VALUES (2, ' 数学与统计学院'); INSERT INTO `facultylist` VALUES (3, '文法学院'); -- ---------------------------- -- Table structure for score -- ---------------------------- DROP TABLE IF EXISTS `score`; CREATE TABLE `score`( `sid` int(11) NULL DEFAULT NULL, `student_id` int(11) NULL DEFAULT NULL, `cousre_id` int(11) NULL DEFAULT NULL, `number` int(11) NULL DEFAULT NULL ) ENGINE = InnoDB CHARACTER SET = gbk COLLATE = gbk_chinese_ci ROW_FORMAT = Compact; -- ---------------------------- -- Records of score -- ---------------------------- INSERT INTO `score` VALUES (1, 1, 1, 60); INSERT INTO `score` VALUES (2, 1, 2, 59); INSERT INTO `score` VALUES (3, 2, 2, 100); -- ---------------------------- -- Table structure for student -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student`( `id` bigint(20) NOT NULL, `stuName` varchar(32) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL, `stuAge` bigint(20) NULL DEFAULT NULL, `graduateDate` datetime(0) NULL DEFAULT NULL, `facultyId` int(20) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of student -- ---------------------------- INSERT INTO `student` VALUES (1, 'dsad', 21, '2019-11-20 20:29:20', 1); INSERT INTO `student` VALUES (2, '2fsf', 20, '2019-11-27 20:29:40', 2); INSERT INTO `student` VALUES (3, '3dfsf', 22, '2019-11-28 20:29:53', 3); INSERT INTO `student` VALUES (4, '4fsf', 17, '2019-11-28 20:30:20', 2); INSERT INTO `student` VALUES (5, '5gfdgdf', 17, '2019-11-21 20:29:20', 1); INSERT INTO `student` VALUES (6, '6fsdfsd', 17, '2025-12-11 20:29:20', 3); INSERT INTO `student` VALUES (7, '7fdsfdsf', 20, '2019-11-20 20:29:20', 2); INSERT INTO `student` VALUES (9, '9', 17, '2025-12-11 20:29:20', 2); INSERT INTO `student` VALUES (10, '10', 21, '2019-11-28 20:30:20', 1); INSERT INTO `student` VALUES (11, '11', 17, '2019-11-21 20:29:20', 1); INSERT INTO `student` VALUES (12, '12', 17, '2019-11-11 20:29:20', 3); INSERT INTO `student` VALUES (13, '13', 17, '2019-11-20 20:29:20', 2); INSERT INTO `student` VALUES (14, '14', 18, '2025-12-11 20:29:20', 3); INSERT INTO `student` VALUES (15, '15', 22, '2019-11-28 20:29:53', 3); INSERT INTO `student` VALUES (16, '16', 22, '2019-11-28 20:30:20', 1); INSERT INTO `student` VALUES (17, '17', 18, '2019-11-21 20:29:20', 1); INSERT INTO `student` VALUES (18, '18', 20, '2025-12-11 20:29:20', 2); INSERT INTO `student` VALUES (19, '19', 21, '2019-11-21 20:29:20', 3); INSERT INTO `student` VALUES (20, '20', 19, '2025-12-11 20:29:20', 3); INSERT INTO `student` VALUES (21, '21', 18, '2019-11-28 22:16:17', 1); -- ---------------------------- -- Table structure for sys_role_info -- ---------------------------- DROP TABLE IF EXISTS `sys_role_info`; CREATE TABLE `sys_role_info`( `id` bigint(20) NOT NULL COMMENT '主键', `code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '角色编码', `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '角色名称', `parent_id` bigint(20) NULL DEFAULT NULL COMMENT '父级角色', `create_by` bigint(20) NULL DEFAULT NULL COMMENT '创建人', `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间', `update_by` bigint(20) NULL DEFAULT NULL COMMENT '更新人', `update_time` datetime(0) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间', `enable` tinyint(1) NULL DEFAULT 1 COMMENT '逻辑删除（0-否 1-是）', `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注', `ext1` varchar(0) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `ext2` varchar(0) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `ext3` varchar(0) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `ext4` varchar(0) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `ext5` json NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户角色表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of sys_role_info -- ---------------------------- INSERT INTO `sys_role_info` VALUES (1061134047709073409, 'generalManager', '超级管理员', 0, 1060394570963267585, '2018-11-10 13:50:46', 1060394570963267585, '2018-12-13 01:32:34', 1, '更新测试', NULL, NULL, NULL, NULL, NULL); INSERT INTO `sys_role_info` VALUES (1061136060048039938, 'projectManager', '普通管理员', 1061134047709073409, 1060394570963267585, '2018-11-10 13:58:46', 1060394570963267585, '2018-12-13 01:32:34', 1, '', NULL, NULL, NULL, NULL, NULL); INSERT INTO `sys_role_info` VALUES (1061137469111885826, 'ProjectMembers', '普通人员', 1061136060048039938, 1060394570963267585, '2018-11-10 14:04:22', 1060394570963267585, '2018-12-13 01:32:34', 1, '哈哈', NULL, NULL, NULL, NULL, NULL); SET FOREIGN_KEY_CHECKS = 1;

6.写基础类
在启动类上添加扫描DAO的注解

package com.zhl.demo; import org.mybatis.spring.annotation.MapperScan; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; /** * @author * @date Create in 11:39 2020/5/28 0028 * @description 启动类 */ @SpringBootApplication @MapperScan(basePackages = {"com.zhl.demo.dao"}) //扫描DAO public class TestMybaitPlusAppliaction {public static void main(String[] args) { SpringApplication.run(TestMybaitPlusAppliaction.class,args); } }

7. 编写Config配置类

package com.zhl.demo.config; import com.baomidou.mybatisplus.extension.plugins.*; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; /** * @author * @date Create in 14:53 2020/5/28 0028 * @description MybatisPlus 配置类 */ @Configuration public class MybatisPlusConfig {/** *mybatis-plus sql执行效率插件【生产环境可以关闭】 * @return */ @Bean public PerformanceInterceptor performanceInterceptor() { return new PerformanceInterceptor(); }/** * 分页插件 * @return */ @Bean public PaginationInterceptor paginationInterceptor() { return new PaginationInterceptor(); } }

8. 编写Entity类

package com.zhl.demo.entity; import com.baomidou.mybatisplus.annotation.*; import lombok.Data; /** * @author * @date Create in 14:59 2020/5/28 0028 * @description 学生信息实体类 */ @TableName("user_info") @Data public class UserInfoEntity {/** * 主键 * @TableId中可以决定主键的类型,不写会采取默认值,默认值可以在yml中配置 * AUTO: 数据库ID自增 * INPUT: 用户输入ID * ID_WORKER: 全局唯一ID，Long类型的主键 * ID_WORKER_STR: 字符串全局唯一ID * UUID: 全局唯一ID，UUID类型的主键 * NONE: 该类型为未设置主键类型 */ @TableId(type = IdType.AUTO) private Long id; /** * 姓名 */ private String name; /** * 年龄 */ private Integer age; /** * 技能 */ private String skill; /** * 评价 */ private String evaluate; /** * 分数 */ private Long fraction; }

9. dao层

package com.zhl.demo.dao; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.baomidou.mybatisplus.core.metadata.IPage; import com.zhl.demo.entity.UserInfoEntity; /** * @author * @date Create in 15:01 2020/5/28 0028 * @description 用户信息DAO */ public interface UserInfoDao extends BaseMapper {/** * 查询大于该分数的学生 * @Parampage分页参数 * @Paramfraction分数 * @Return IPage 分页数据*/ IPage selectUserInfoByGtFraction(IPage page, Long fraction); }

10. 编写Service类

package com.zhl.demo.dao; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.baomidou.mybatisplus.core.metadata.IPage; import com.zhl.demo.entity.UserInfoEntity; /** * @author * @date Create in 15:01 2020/5/28 0028 * @description 用户信息DAO */ public interface UserInfoDao extends BaseMapper {/** * 查询大于该分数的学生 * @Parampage分页参数 * @Paramfraction分数 * @Return IPage 分页数据*/ IPage selectUserInfoByGtFraction(IPage page, Long fraction); }

11. 编写ServiceImpl类

package com.zhl.demo.service.Impl; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.zhl.demo.dao.UserInfoDao; import com.zhl.demo.entity.UserInfoEntity; import com.zhl.demo.service.UserInfoService; import org.springframework.stereotype.Service; /** * @author * @date Create in 15:04 2020/5/28 0028 * @description 用户业务实现 */ @Service public class UserInfoSerivceImpl extends ServiceImpl implements UserInfoService {@Override public IPage selectUserInfoByGtFraction(IPage page, Long fraction) { return baseMapper.selectUserInfoByGtFraction(page, fraction); } }

12. 基础演示

package com.zhl.demo.controller; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.zhl.demo.entity.UserInfoEntity; import com.zhl.demo.service.UserInfoService; import org.apache.ibatis.annotations.Delete; import org.mybatis.spring.annotation.MapperScan; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.DeleteMapping; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.*; /** * @author * @date Create in 15:05 2020/5/28 0028 * @description */ @RestController @RequestMapping("test") public class UserInfoController {@Autowired private UserInfoService userInfoService; /** * 根据ID获取用户信息 * @return */ @GetMapping("/getInfo") public UserInfoEntity getInfo(String userId) { UserInfoEntity userInfoEntity = userInfoService.getById(userId); return userInfoEntity; }/** * 用户实体的集合 * @return */ @GetMapping("/getList") public List getList() { List userInfoEntityList = userInfoService.list(new QueryWrapper<>()); return userInfoEntityList; }/** * 分页查询全部数据 * @return */ @GetMapping("/getInfoListPage") public IPage getInfoListPage() { //需要在config配置类配置分页插件 IPage page = new Page<>(); page.setCurrent(2); //当前页面 page.setSize(2); //每页的条数 page = userInfoService.page(page, new QueryWrapper<>()); return page; }/** * 根据指定字段查询用户信息集合 * @return */ @GetMapping("/getListMap") public Collection getListMap() { Map map = new HashMap<>(); //key是字段名 value是字段值 map.put("age",20); Collection userInfoEntityList = userInfoService.listByMap(map); return userInfoEntityList; }/** * 新增用户的信息 */ @GetMapping("/saveInfo") public boolean saveInfo() { UserInfoEntity userInfoEntity = new UserInfoEntity(); userInfoEntity.setName("小龙"); userInfoEntity.setSkill("JAVA"); userInfoEntity.setAge(18); userInfoEntity.setFraction(59L); userInfoEntity.setEvaluate("该学生是一个在改BUG的码农"); return userInfoService.save(userInfoEntity); }/** * 批量新增用户 */ @GetMapping("/saveInfoList") public boolean saveInfoList() { //创建对象 UserInfoEntity sans = new UserInfoEntity(); sans.setName("Sans"); sans.setSkill("睡觉"); sans.setAge(18); sans.setFraction(60L); sans.setEvaluate("Sans是一个爱睡觉,并且身材较矮骨骼巨大的骷髅小胖子"); UserInfoEntity papyrus = new UserInfoEntity(); papyrus.setName("papyrus"); papyrus.setSkill("JAVA"); papyrus.setAge(18); papyrus.setFraction(58L); papyrus.setEvaluate("Papyrus是一个讲话大声、个性张扬的骷髅，给人自信、有魅力的骷髅小瘦子"); //批量保存 List list = new ArrayList<>(); list.add(sans); list.add(papyrus); return userInfoService.saveBatch(list); }/** * 更新用户信息 */ @GetMapping("/updateInfo") public boolean updateInfo(){ //根据实体中的ID去更新，其他字段如果值为null 则不会更新改字段，参考yml配置文件 UserInfoEntity userInfoEntity = new UserInfoEntity(); userInfoEntity.setId(1L); userInfoEntity.setAge(19); return userInfoService.updateById(userInfoEntity); }/** * 新增或者更新用户信息 */ @GetMapping("/saveOrupdate") public boolean saveOrupdate() { //传入的实体类userEntity中ID为null就会新增（ID自增） //实体类ID值存在，如果数据库存在ID就会更新，如果不存在就会新增 UserInfoEntity userInfoEntity = new UserInfoEntity(); //userInfoEntity.setId(1L); userInfoEntity.setAge(28); return userInfoService.saveOrUpdate(userInfoEntity); }/** * 根据Id删除用户的信息 * @param userId */ @DeleteMapping("deleteInfo") public boolean deleteInfo(String userId) { return userInfoService.removeById(Long.valueOf(userId)); }@GetMapping("/deleteInfoList") public boolean deleteInfoList() { List userIdList = new ArrayList<>(); userIdList.add("18"); userIdList.add("19"); return userInfoService.removeByIds(userIdList); }/** * 根据指定字段删除用户信息 * @Author Sans * @CreateTime 2019/6/8 16:57 */ @GetMapping("/deleteInfoMap") public boolean deleteInfoMap(){ //kay是字段名 value是字段值 Map map = new HashMap<>(); map.put("skill","删除"); map.put("fraction",10L); return userInfoService.removeByMap(map); }}

package com.zhl.demo.controller; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.zhl.demo.entity.UserInfoEntity; import com.zhl.demo.service.UserInfoService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.HashMap; import java.util.List; import java.util.Map; /** * @author * @date Create in 10:51 2020/5/29 0029 * @description mybatis-plus 常见的的实例 */ @RestController @RequestMapping("test2") public class UserInfoPlusController {@Autowired private UserInfoService userInfoService; @GetMapping("/getInfoListPage") public Map getInfoListPage() { //初始化返回类 Map result = new HashMap<>(); //查询年龄等于18岁的学生 //等价于SQL: select id, name, age, skill,evaluate, fraction from user_info where age = 18 QueryWrapper queryWrapper = new QueryWrapper<>(); //queryWrapper.lambda().eq(UserInfoEntity::getAge, 18); //List userInfoEntityList = userInfoService.list(queryWrapper); //result.put("studentAge18", userInfoEntityList); //查询年龄大于5岁的学生且小于等于18岁的学生 //等价于SQL: select id, name, age, skill, evaluate, fraction from user_info where age > 5 and age <= 18 //queryWrapper.lambda().gt(UserInfoEntity::getAge, 5); //queryWrapper.lambda().le(UserInfoEntity::getAge, 18); //List userInfoEntityList = userInfoService.list(queryWrapper); //result.put("studentAge5to18", userInfoEntityList); //模糊查询技能字段带有 “画” 的数据， 并按照年龄降序 //等价于SQL: select id, name, age, skill, evaluate, fraction,from user_info where skill like '%画%' order by age DESC //queryWrapper.lambda().like(UserInfoEntity::getSkill, "画"); //queryWrapper.lambda().orderByDesc(UserInfoEntity::getAge); //List userInfoEntityList = userInfoService.list(queryWrapper); //result.put("studentAgeSkill", userInfoEntityList); //模糊查询名字带有 “小” 或者年龄大于18的学生 //等价于SQL: select id,name, age, skill, evaluate, fraction from user_info where name like "%小%" or age > 18 //queryWrapper.lambda().like(UserInfoEntity::getName, "小"); //queryWrapper.lambda().or().gt(UserInfoEntity::getAge, 18); //List userInfoEntityList = userInfoService.list(queryWrapper); //result.put("studentOr", userInfoEntityList); //查询评价不为null的学生，并且分页 //等价SQL: select id, name, age, skill, evaluate, fraction from user_info where evaluate is not null limit 0,5 IPage page = new Page<>(); page.setCurrent(2); page.setSize(5); queryWrapper.lambda().isNotNull(UserInfoEntity::getEvaluate); page = userInfoService.page(page, queryWrapper); result.put("studentPage", page); return result; }/** * 自定义sql * @return */ @GetMapping("/getInfoListSQL") public IPage getInfoListSQL() { //查询大于60分以上的学生，并且分页 IPage page = new Page<>(); page.setCurrent(2); page.setSize(5); page = userInfoService.selectUserInfoByGtFraction(page, 60L); return page; } }

12. xml

select * from user_info where fraction > #{fraction}

controller

package com.zhl.demo.controller; import com.zhl.demo.entity.Select; import com.zhl.demo.entity.Student; import com.zhl.demo.entity.UserInfoEntity; import com.zhl.demo.service.StudentService; import com.zhl.demo.service.UserInfoService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.List; /** * @author * @date Create in 10:22 2020/5/30 0030 * @description 测试类 */ @RestController @RequestMapping("test3") public class TestController {@Autowired private UserInfoService userInfoService; @Autowired private StudentService studentService; @GetMapping("/test") public UserInfoEntity test() { return userInfoService.test(); }@GetMapping("/test1") public List test1() { return studentService.infoList(); }@GetMapping("/test2") public List test2() { int nowPageIndex = 1; int pageSize = 5; Select select = new Select(); select.setStuName(""); select.setMinAge(19); select.setMaxAge(21); select.setIsGraduate(true); select.setOrderBy("stuAge"); select.setHighToLow(true); select.setPageNumber((nowPageIndex - 1) * pageSize); select.setPageSize(pageSize); return studentService.findResultByInfo(select); } }

service

package com.zhl.demo.service; import com.baomidou.mybatisplus.extension.service.IService; import com.zhl.demo.entity.Select; import com.zhl.demo.entity.Student; import java.util.List; public interface StudentService extends IService { List infoList(); List findResultByInfo(Select select); }

serviceImpl

package com.zhl.demo.service.Impl; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.zhl.demo.dao.StudentDao; import com.zhl.demo.entity.Select; import com.zhl.demo.entity.Student; import com.zhl.demo.service.StudentService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.List; /** * @author shanghai * @date Create in 16:52 2020/6/11 0011 * @description */ @Service public class StudentServiceImpl extends ServiceImpl implements StudentService {@Autowired private StudentDao studentDao; @Override public List infoList() { return studentDao.infoList(); }@Override public List findResultByInfo(Select select) { List> result =studentDao.findResultByInfo(select); System.out.println(result); return result; } }

实体类

package com.zhl.demo.entity; import lombok.Data; import java.io.Serializable; /** * @author shanghai * @date Create in 14:58 2020/6/11 0011 * @description */ @Data public class Select implements Serializable { StringstuName; //模糊搜索学生名 Integer minAge; //最小年龄 用Integer不用int是因为用户可以不选择此条件（null）即没有最小年龄限制 用int默认值是0 Integer maxAge; //最大年龄 Boolean isGraduate; //是否毕业 为null就是不管毕业还是没毕业都要 Integer pageNumber; //第几页 从1开始 Integer pageSize; //每页几个数据 StringorderBy; //排序字段 Boolean highToLow; //是否降序 为false和null就是升序 为true就是降序 }

xml

select `student`.`id` from student--select SQL_CALC_FOUND_ROWS, select `student`.`id` AS `id`, `student`.`stuName` AS stuName, `student`.`stuAge` AS `stuAge`, `student`.`graduateDate` AS `graduateDate`, `facultylist`.`facultyName` AS `facultyName` FROM (`facultylist` JOIN`student`) where (`facultylist`.`id` = `student`.`facultyId`) -- 标题模糊查询 and `student`.`stuName` like concat('%',#{stuName},'%') -- > =是大于等于 and `student`.`stuAge` > = #{minAge} -- < =是小于等于 and `student`.`stuAge` < = #{maxAge} -- 没毕业 毕业时间大于现在 and`student`.`graduateDate` > = NOW() -- 毕业了 毕业时间小于现在 and `student`.`graduateDate` < = NOW() ORDER BY ${orderBy} ASC,`student`.`id` ASC -- 加id ASC是为了保证分页结果的唯一性 mysql排序是不稳定的 https://www.jianshu.com/p/1e8a19738ae4 order by ${orderBy} DESC, `student`.`id` ASC -- 分页查询 limit #{pageNumber},#{pageSize}; -- 接着查询符合条件个数 --select FOUND_ROWS();

MyBatis-Plus的QueryWrapper条件构造器

当查询条件复杂的时候,我们可以使用MP的条件构造器,请参考下面的QueryWrapper条件参数说明查询方式 方法说明 setSqlSelect 设置 SELECT 查询字段 where WHERE 语句，拼接 + WHERE 条件 and AND 语句，拼接 + AND 字段=值 or OR 语句，拼接 + OR 字段=值 eq 等于= allEq 基于 map 内容等于= ne 不等于<> gt 大于> ge 大于等于>= lt 小于< le 小于等于<= like 模糊查询 LIKE notLike 模糊查询 NOT LIKE in IN 查询 notIn NOT IN 查询 isNull NULL 值查询 isNotNull IS NOT NULL groupBy 分组 GROUP BY having HAVING 关键词 orderBy 排序 ORDER BY orderByAsc ASC 排序 ORDER BY orderByDesc DESC 排序 ORDER BY exists EXISTS 条件语句 notExists NOT EXISTS 条件语句 between BETWEEN 条件语句 notBetween NOT BETWEEN 条件语句 addFilter 自由拼接 SQL last 拼接在最后，例如：last("LIMIT 1")

推荐阅读

• 

  讣告上存殁均感的殁字是什么意思 讣告上存殁均感的殁字是啥意思
• 

  门前清水尚能西上一句
• 

  6670换7610外壳外一篇
• 

  广东省有哪些土特产食品类的
• 

  牛奶加蜂蜜可以解酒吗
• 

  桑葚树怎么种 桑葚树怎么移栽
• 

  空调没有遥控器怎么开
• 

  100克食物有多少 多少是一克
• 

  575
• 

  80平米房屋装修价格 80平米房屋装修价格东莞
• 

  早餐吃燕麦能减肥吗
• 

  感赏生活第一天（这个周日好好玩）
• 

  孩子|女性有没有生过孩子，其实三个部位一看就知道了，想遮掩都遮不住
• 

  夫妻离婚按揭房屋怎么分配 离婚后按揭房款怎么分配
• 

  春日杂咏是一首什么诗 春日杂咏是一首什么律诗
• 

  DIY改造TP-Link 7650小音箱成为具备三防、mesh、poe的高性能户外AP
• 

  2020年北京长城文化节 2023年北京长城梅花节活动时间
• 

  材料化学属于什么类 材料化学属于什么类专业
• 

  汽车天窗冰甲和隔热膜哪个好
• 

  redis高并发架构设计与源码剖析 高并发下redis的瓶颈

• Mybatis|SpringBoot整合Mybatisplus
• java|SpringBoot+MyBatisPlus
• 微服务|Springboot集成Mybatisplus,轻松CRUD
• springboot系列|【springboot系列】springboot整合mybatisplus实现CRUD
• jvm|关于JVM和JDK
• java|Spring Boot干货系列（（一）优雅的入门篇 | 掘金技术征文）
• java|Java 中台技术盘点，这些技术你了解还远远不够
• java|朱晔的互联网架构实践心得S2E2（写业务代码最容易掉的10种坑 | 掘金年度征文...）
• Spring|猿创征文｜Spring Boot运行原理及功能实现方式