Springboot集成MybatisPlus、Druid

随影随行

已于 2023-06-05 13:57:34 修改

阅读量3.5k

点赞数

于 2020-05-29 16:22:02 首次发布

本文链接：https://blog.csdn.net/qq_40664441/article/details/106427470

版权

java 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

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

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

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.zhl</groupId>
    <artifactId>testDemoMybatisPlus</artifactId>
    <version>1.0-SNAPSHOT</version>

    <!--父级依赖，它用来提供相关的 Maven 默认依赖。
      使用它之后，常用的springboot包依赖可以省去version 标签
      配置UTF-8编码，指定JDK8-->
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.3.RELEASE</version>
        <relativePath ></relativePath>
    </parent>


    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
        <log4j.version>1.3.8.RELEASE</log4j.version>
        <druid.version>1.0.26</druid.version>
        <mybatis-plus.version>2.1.9</mybatis-plus.version>
        <mybatisplus-spring-boot-starter.version>1.0.5</mybatisplus-spring-boot-starter.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.17</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-starter -->
        <!--        <dependency>-->
        <!--            <groupId>com.alibaba</groupId>-->
        <!--            <artifactId>druid-spring-boot-starter</artifactId>-->
        <!--            <version>1.1.21</version>-->
        <!--        </dependency>-->
        <!--自动生成getter/setter插件-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.0.5</version>
        </dependency>
        <!-- 模板引擎 代码生成器使用-->
        <!--        <dependency>-->
        <!--            <groupId>org.apache.velocity</groupId>-->
        <!--            <artifactId>velocity-engine-core</artifactId>-->
        <!--            <version>2.0</version>-->
        <!--        </dependency>-->
       <!-- 引入阿里数据库连接池 -->
<!--        <dependency>-->
<!--            <groupId>com.alibaba</groupId>-->
<!--            <artifactId>druid</artifactId>-->
<!--&lt;!&ndash;            <version>1.1.6</version>&ndash;&gt;-->
<!--        </dependency>-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

配置如下

# 配置端口
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);
    }
}

编写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();
    }
}

   /**
     * 这个版本乐观锁配置
     *
     * @return
     */
    @Bean
    public OptimisticLockerInterceptor optimisticLockerInterceptor() {
        return new OptimisticLockerInterceptor();
    }

编写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;
}

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<UserInfoEntity> {

    /**
     * 查询大于该分数的学生
     * @Param  page  分页参数
     * @Param  fraction  分数
     * @Return IPage<UserInfoEntity> 分页数据

     */
    IPage<UserInfoEntity> selectUserInfoByGtFraction(IPage<UserInfoEntity> page, Long fraction);
}

编写Service类

package com.zhl.demo.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.zhl.demo.entity.UserInfoEntity;

/**
 * @author 
 * @date Create in 15:01 2020/5/28 0028
 * @description 用户信息Service
 */
public interface UserInfoService extends IService<UserInfoEntity> {

    /**
     * 查询大于该分数的学生
     * @Param  page  分页参数
     * @Param  fraction  分数
     * @Return IPage<UserInfoEntity> 分页数据

     */
    IPage<UserInfoEntity> selectUserInfoByGtFraction(IPage<UserInfoEntity> page, Long fraction);
}

编写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<UserInfoDao, UserInfoEntity> implements UserInfoService {

    @Override
    public IPage<UserInfoEntity> selectUserInfoByGtFraction(IPage<UserInfoEntity> page, Long fraction) {
        return baseMapper.selectUserInfoByGtFraction(page, fraction);
    }
}

基础演示

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<UserInfoEntity> getList() {
        List<UserInfoEntity> userInfoEntityList = userInfoService.list(new QueryWrapper<>());
        return userInfoEntityList;
    }

    /**
     * 分页查询全部数据
     * @return
     */
    @GetMapping("/getInfoListPage")
    public IPage<UserInfoEntity> getInfoListPage() {
        //需要在config配置类配置分页插件
        IPage<UserInfoEntity> page = new Page<>();
        page.setCurrent(2); //当前页面
        page.setSize(2); //每页的条数
        page = userInfoService.page(page, new QueryWrapper<>());
        return page;
    }

    /**
     * 根据指定字段查询用户信息集合
     * @return
     */
    @GetMapping("/getListMap")
    public Collection<UserInfoEntity> getListMap() {
        Map<String, Object> map = new HashMap<>();
        //key是字段名 value是字段值
        map.put("age",20);
        Collection<UserInfoEntity> 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<UserInfoEntity> 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<String> 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<String,Object> 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<String, Object> getInfoListPage() {
        //初始化返回类
        Map<String, Object> result = new HashMap<>();
        //查询年龄等于18岁的学生
        //等价于SQL: select id, name, age, skill,evaluate, fraction from user_info where age = 18
        QueryWrapper<UserInfoEntity> queryWrapper = new QueryWrapper<>();
//        queryWrapper.lambda().eq(UserInfoEntity::getAge, 18);
//        List<UserInfoEntity> 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<UserInfoEntity> 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<UserInfoEntity> 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<UserInfoEntity> 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<UserInfoEntity> 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<UserInfoEntity> getInfoListSQL() {
        //查询大于60分以上的学生，并且分页
        IPage<UserInfoEntity> page = new Page<>();
        page.setCurrent(2);
        page.setSize(5);
        page = userInfoService.selectUserInfoByGtFraction(page, 60L);
        return page;
    }
}

<?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="com.zhl.demo.dao.UserInfoDao">

    <select id="selectUserInfoByGtFraction" resultType="com.zhl.demo.entity.UserInfoEntity" parameterType="long">
        select * from user_info where fraction > #{fraction}
    </select>
</mapper>

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<Student> 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<Student> {
    List<Student> 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 
 * @date Create in 16:52 2020/6/11 0011
 * @description
 */
@Service
public class StudentServiceImpl extends ServiceImpl<StudentDao, Student> implements StudentService {

    @Autowired
    private StudentDao studentDao;

    @Override
    public List<Student> infoList() {
        return studentDao.infoList();
    }

    @Override
    public List<?> findResultByInfo(Select select) {
        List<List<?>> result =  studentDao.findResultByInfo(select);
        System.out.println(result);
        return result;
    }
}

实体类

package com.zhl.demo.entity;

import lombok.Data;

import java.io.Serializable;

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

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="com.zhl.demo.dao.StudentDao">
    <resultMap id="ResultMap" type="com.zhl.demo.entity.Result"/>
    <resultMap id="RecordsCount" type="integer" />

    <select id="infoList" resultType="com.zhl.demo.entity.Student" >
        select `student`.`id`
        from student
    </select>

    <select id="findResultByInfo" resultMap="ResultMap,RecordsCount" parameterType="com.zhl.demo.entity.Select" resultType="java.util.List">
--         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`)
        -- 标题模糊查询
        <if test="stuName != null || stuName != ''">
            and `student`.`stuName` like concat('%',#{stuName},'%')
        </if>
        -- &gt;=是大于等于
        <if test="minAge != null || minAge = ''">
            and `student`.`stuAge` &gt;= #{minAge}
        </if>
        -- &lt;=是小于等于
        <if test="maxAge != null | maxAge != ''">
            and `student`.`stuAge` &lt;= #{maxAge}
        </if>
        -- 没毕业 毕业时间大于现在
        <if test="isGraduate != null and isGraduate == false">
           and  `student`.`graduateDate` &gt;= NOW()
        </if>
        -- 毕业了 毕业时间小于现在
        <if test="isGraduate != null and isGraduate == true">
            and `student`.`graduateDate` &lt;= NOW()
        </if>
        <if test="orderBy != null and orderBy != ''">
            <if test="highToLow ==null or highToLow ==false">
                ORDER BY ${orderBy} ASC,`student`.`id` ASC -- 加id ASC是为了保证分页结果的唯一性 mysql排序是不稳定的 https://www.jianshu.com/p/1e8a19738ae4
            </if>
            <if test="highToLow != null and highToLow == true">
                order by ${orderBy} DESC, `student`.`id` ASC
            </if>
        </if>
        -- 分页查询
        limit
        #{pageNumber},#{pageSize};
        -- 接着查询符合条件个数
--         select FOUND_ROWS();
    </select>
</mapper>

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")

随影随行

关注

0
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
Springboot集成MybatisPlus、Druid

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

扫一扫