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: 8090spring:datasource:#---- ^ MySQL 数据库配置 ----#driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3306/帅龍之龍?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTCusername: 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: 20filters: stat,wall# 合并多个DruidDataSource的监控数据#useGlobalDataSourceStat: true# 通过connectProperties属性来打开mergeSql功能；慢SQL记录connect-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000# 采集web-jdbc关联监控的数据web-stat-filter:enabled: trueurl-pattern: "/*"exclusions: "*.txt,*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*"# 监控配置stat-view-servlet:enabled: trueurl-pattern: "/druid/*"reset-enable: falselogin-username: rootlogin-password: 123456allow:deny:#---- / Druid ^ 数据库连接池配置 ----##---- ^ 文件上传大小限制 ----#servlet:multipart:max-file-size: 30MBmax-request-size: 30MB#---- / 文件上传大小限制 ----##---- ^ thymeleaf 前端模板配置 ----#thymeleaf:mode: HTMLencoding: UTF-8cache: falseprefix: classpath:/templates/suffix: .html#---- / thymeleaf 前端模板配置 ----##---- ^ mybatis-plus 配置 ----#
mybatis-plus:mapper-locations: mapper/*.xmlconfiguration:log-impl: org.apache.ibatis.logging.stdout.StdOutImplmap-underscore-to-camel-case: truetype-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 {@Autowiredprivate RecordServiceImpl recordService;/*** 保存用户访问记录*/@GetMapping(value = "saveUserAccessRecord")@ResponseBody@CrossOriginpublic <T> T saveUserAccessRecord (HttpServletRequest request) {return recordService.saveUserAccessRecord(request);}/*** 删除用户访问记录*/@GetMapping(value = "deleteUserAccessRecord")@ResponseBody@CrossOriginpublic <T> T deleteUserAccessRecord (@RequestParam("recordId") Integer recordId) {return recordService.deleteUserAccessRecord(recordId);}/*** 修改用户访问记录*/@GetMapping(value = "modifyUserAccessRecord")@ResponseBody@CrossOriginpublic <T> T modifyUserAccessRecord () {return recordService.modifyUserAccessRecord();}/*** 查询用户访问记录*/@GetMapping(value = "queryUserAccessRecord")@ResponseBody@CrossOriginpublic <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);@Autowiredpublic RecordMapper recordMapper;@Overridepublic <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;}@Overridepublic <T> T deleteUserAccessRecord(Integer recordId) {try {recordMapper.deleteById(recordId);return (T) "success";} catch (Exception e) {return (T) "fail";}}@Overridepublic <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";}}@Overridepublic <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);}
}

九、运行效果

（1）德鲁伊监控面板

（2）增删改查示例