SpringBoot项目整合MybatisPlus持久层框架+Druid数据库连接池，以及实现增删改查功能

前言

之前搭建SpringBoot项目工程，所使用的持久层框架不是Mybatis就是JPA，还没试过整合MybatisPlus框架并使用，原来也如此简单。在此简单记录一下在SpringBoot项目中，整合MybatisPlus持久层框架、Druid数据库连接池的过程。

一、导入依赖

（1）pom.xml

<?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>org.example</groupId>
    <artifactId>帅龍之龍</artifactId>
    <version>1.0-SNAPSHOT</version>

    <!-- SpringBoot应用的父级依赖：提供了SpringBoot统一的依赖管理和插件管理 -->
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
         <version>2.5.3</version>
        <!-- <version>3.0.1</version> -->
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <!-- JDK版本 -->
    <properties>
        <java.version>1.8</java.version>
        <!-- <java.version>17</java.version> -->
    </properties>

    <dependencies>
        <!-- Web启动器 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- MyBatis 持久层框架 -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>

        <!-- MyBatisPlus 增强框架 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.2</version>
        </dependency>

        <!-- Druid 数据库连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.15</version>
        </dependency>

        <!-- MySQL 驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!-- Thymeleaf -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>

        <!-- Lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

        <!-- UserAgent -->
        <dependency>
            <groupId>eu.bitwalker</groupId>
            <artifactId>UserAgentUtils</artifactId>
            <version>1.20</version>
        </dependency>
    </dependencies>

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

二、项目配置

（1）application.yml

server:
  port: 8090

spring:
  datasource:
    #---- ^ MySQL 数据库配置 ----#
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/帅龍之龍?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
    username: 
    password: 
    type: com.alibaba.druid.pool.DruidDataSource
    #---- / MySQL 数据库配置 ----#

    #---- ^ Druid 数据库连接池配置 ----#
    druid:
      # 初始化连接池数量
      initial-size: 5
      # 最小连接池数量
      min-idle: 5
      # 最大连接池数量
      max-active: 30
      # 配置获取连接等待超时的时间，单位毫秒
      max-wait: 60000
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      time-between-eviction-runs-millis: 60000
      # 配置一个连接在池中最小生存的时间，单位是毫秒
      min-evictable-idle-time-millis: 300000
      # 验证数据库连接的有效性，若返回结果不为空，则说明连接可用
      validation-query: select 1
      # 在检查闲置连接时同时检查连接可用性
      test-while-idle: true
      # 申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
      test-on-borrow: false
      # 归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
      test-on-return: false
      # 是否缓存preparedStatement，也就是PSCache，PSCache对支持游标的数据库性能提升巨大，比如说Oracle，而在MySQL下建议关闭
      pool-prepared-statements: true
      # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
      max-pool-prepared-statement-per-connection-size: 20
      filters: stat,wall
      # 合并多个DruidDataSource的监控数据
      #useGlobalDataSourceStat: true
      # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
      connect-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
      # 采集web-jdbc关联监控的数据
      web-stat-filter:
        enabled: true
        url-pattern: "/*"
        exclusions: "*.txt,*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*"
      # 监控配置
      stat-view-servlet:
        enabled: true
        url-pattern: "/druid/*"
        reset-enable: false
        login-username: root
        login-password: 123456
        allow:
        deny:
    #---- / Druid ^ 数据库连接池配置 ----#

  #---- ^ 文件上传大小限制 ----#
  servlet:
    multipart:
      max-file-size: 30MB
      max-request-size: 30MB
  #---- / 文件上传大小限制 ----#

  #---- ^ thymeleaf 前端模板配置 ----#
  thymeleaf:
    mode: HTML
    encoding: UTF-8
    cache: false
    prefix: classpath:/templates/
    suffix: .html
  #---- / thymeleaf 前端模板配置 ----#

#---- ^ mybatis-plus 配置 ----#
mybatis-plus:
  mapper-locations: mapper/*.xml
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    map-underscore-to-camel-case: true
  type-aliases-package: org.example.pojo.entity
#---- / mybatis-plus 配置 ----#

三、控制层

（1）/src/org/example/controller/RecordController.java

package org.example.controller;

import org.example.service.impl.RecordServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;

@Controller
@RequestMapping(value = "api")
public class RecordController {
   
   

    @Autowired
    private RecordServiceImpl recordService;

    /**
     * 保存用户访问记录
     */
    @GetMapping(value = "saveUserAccessRecord")
    @ResponseBody
    @CrossOrigin
    public <T> T saveUserAccessRecord (HttpServletRequest request) {
   
   
        return recordService.saveUserAccessRecord(request);
    }

    /**
     * 删除用户访问记录
     */
    @GetMapping(value = "deleteUserAccessRecord")
    @ResponseBody
    @CrossOrigin
    public <T> T deleteUserAccessRecord (@RequestParam("recordId") Integer recordId) {
   
   
        return recordService.deleteUserAccessRecord(recordId);
    }

    /**
     * 修改用户访问记录
     */
    @GetMapping(value = "modifyUserAccessRecord")
    @ResponseBody
    @CrossOrigin
    public <T> T modifyUserAccessRecord () {
   
   
        return recordService.modifyUserAccessRecord();
    }

    /**
     * 查询用户访问记录
     */
    @GetMapping(value = "queryUserAccessRecord")
    @ResponseBody
    @CrossOrigin
    public <T> T queryUserAccessRecord () {
   
   
        return recordService.queryUserAccessRecord();
    }
}

四、接口层

（1）/src/org/example/service/IRecordService.java

package org.example.service;

import javax.servlet.http.HttpServletRequest;

public interface IRecordService {
   
   
    <T> T saveUserAccessRecord(HttpServletRequest request);

    <T> T deleteUserAccessRecord(Integer recordId);

    <T> T modifyUserAccessRecord();

    <T> T queryUserAccessRecord();
}

五、实现层

（1）/src/org/example/service/impl/RecordServiceImpl.java

package org.example.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import eu.bitwalker.useragentutils.UserAgent;
import org.example.mapper.RecordMapper;
import org.example.pojo.entity.Record;
import org.example.service.IRecordService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import java.util.HashMap;

@Service
public class RecordServiceImpl implements IRecordService {
   
   

    private static final Logger log = LoggerFactory.getLogger(RecordServiceImpl.class);

    @Autowired
    public RecordMapper recordMapper;

    @Override
    public <T> T saveUserAccessRecord(HttpServletRequest request) {
   
   
        HashMap<String, Object> responseObj = new HashMap<>();

        try {
   
   
            String agent = request.getHeader("User-Agent");
            UserAgent userAgent = UserAgent.parseUserAgentString(agent);

            // 获取发起请求的IP地址
            String ip = request.getHeader("x-forwarded-for");
            if (ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {
   
   
                ip = request.getHeader("Proxy-Client-IP");
            }
            if (ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {
   
   
                ip = request.getHeader("WL-Proxy-Client-IP");
            }
            if (ip == null || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) {
   
   
                ip = request.getRemoteAddr();
            }

            Record record = new Record();
            record.setIp(ip);
            record.setBrowser(userAgent.getBrowser().getName());
            record.setOs(userAgent.getOperatingSystem().getName());
            record.setDeviceType(userAgent.getOperatingSystem().getDeviceType().getName());
            recordMapper.insert(record);

            responseObj.put("code", 200);
            responseObj.put("success", true);
            responseObj.put("data", record);
            responseObj.put("msg", "保存成功");
        } catch (Exception e) {
   
   
            responseObj.put("code", 500);
            responseObj.put("success", false);
            responseObj.put("msg", e.getMessage());
        }

        return (T) responseObj;
    }

    @Override
    public <T> T deleteUserAccessRecord(Integer recordId) {
   
   
        try {
   
   
            recordMapper.deleteById(recordId);
            return (T) "success";
        } catch (Exception e) {
   
   
            return (T) "fail";
        }
    }

    @Override
    public <T> T modifyUserAccessRecord() {
   
   
        try {
   
   
            Record record = new Record();
            record.setId(1L);
            record.setIp("localhost");
            record.setBrowser("帅龍之龍");
            record.setOs("Windows 11");
            record.setDeviceType("Android");
            recordMapper.updateById(record);
            return (T) "success";
        } catch (Exception e) {
   
   
            return (T) "fail";
        }
    }

    @Override
    public <T> T queryUserAccessRecord() {
   
   
        // 根据ID查询
        // Long recordId = (long) 1;
        // return (T) recordMapper.selectById(recordId);

        // 使用 QueryWrapper 构造器查询全部用户
        // QueryWrapper<Record> queryWrapper = new QueryWrapper<>();
        // queryWrapper.eq("ip", "0:0:0:0:0:0:0:1");
        // return (T) recordMapper.selectList(queryWrapper);

        // 使用 LambdaQueryWrapper 构造器查询全部用户
        LambdaQueryWrapper<Record> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.eq(Record::getIp, "127.0.0.1");
        return (T) recordMapper.selectList(lambdaQueryWrapper);
    }
}

六、对象实体

（1）/src/org/example/pojo/entity/Record.java

package org.example.pojo.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;
import lombok.ToString;

import java.sql.Timestamp;

@Data
@ToString
public class Record {
   
   
    // 指定主键名、主键生产策略
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;

    private String ip;

    private String os;

    private String browser;

    // 指定列名，若一致则可以不用指定，若不一致则需要指定
    @TableField("device_type")
    private String deviceType;

    // 指定列名，若一致则可以不用指定，若不一致则需要指定
    @TableField("create_time")
    private Timestamp createTime;
}

七、持久层

（1）/src/org/example/mapper/RecordMapper.java

package org.example.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.example.pojo.entity.*;
import org.springframework.stereotype.Repository;

@Repository
public interface RecordMapper extends BaseMapper<Record> {
   
   
}

八、启动器

（1）App.java

package org.example;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;

@MapperScan("org.example.*")
@EnableWebMvc
@EnableTransactionManagement
@SpringBootApplication
public class App {
   
   
    public static void main(String[] args) {
   
   
        SpringApplication.run(App.class, args);
    }
}

九、数据表

--
-- 表的结构 `record`
--

CREATE TABLE IF NOT EXISTS `record` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `ip` varchar(30) DEFAULT NULL,
  `os` varchar(30) DEFAULT NULL,
  `browser` varchar(30) DEFAULT NULL,
  `device_type` varchar(30) DEFAULT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

--
-- 转存表中的数据 `record`
--

INSERT INTO `record` (`id`, `ip`, `os`, `browser`, `device_type`, `create_time`) VALUES
(1, 'localhost', 'Windows 11', '帅龍之龍', 'Android', '2021-11-13 17:33:24'),
(2, '0:0:0:0:0:0:0:1', 'WINDOWS_10', 'CHROME', 'Computer', '2021-11-13 17:43:54'),
(3, '0:0:0:0:0:0:0:1', 'WINDOWS_10', 'CHROME', 'Computer', '2021-11-13 17:48:28'),
(4, '127.0.0.1', 'WINDOWS_10', 'CHROME', 'Computer', '2021-11-13 17:49:38'),
(5, '127.0.0.1', 'WINDOWS_10', 'CHROME', 'Computer', '2021-11-13 17:54:42'),
(6, '192.168.0.101', 'Windows 10', 'Chrome 9', 'Computer', '2023-10-09 14:18:32'),
(7, '192.168.0.101', 'Windows 10', 'Chrome 9', 'Computer', '2023-10-09 14:19:29');

十、运行效果

（1）德鲁伊登录页面

（2）查询示例