SpringBoot 2.7.18 集成 Mybatis Plus + Druid

最新推荐文章于 2025-02-20 18:51:30 发布

牛马程序员2025

最新推荐文章于 2025-02-20 18:51:30 发布

阅读量1.1k

点赞数 21

文章标签： spring boot mybatis 后端服务器

本文链接：https://blog.csdn.net/weixin_55010563/article/details/143137671

版权

SpringBoot 2.7.18 集成 Mybatis Plus + Druid

Pom 依赖

yml 配置

Config 配置

Mapper扫描

Entity

Mapper.xml

Mapper - SysMenuMapper

Service

Controller

一并附上 Mybatis 和 Mybatis Plus 的使用区别

MyBatis Mapper.XML 标签使用说明

Pom 依赖

Mybatis

<druid-boot.version>1.1.10</druid-boot.version>

<mybatis-boot.version>2.1.0</mybatis-boot.version>

<mysql-connector.version>8.0.16</mysql-connector.version>

<mssql-jdbc.version>8.2.2.jre8</mssql-jdbc.version>

<oracle-jdbc.version>19.3.0.0</oracle-jdbc.version>

<pagehelper-starter.version>1.2.10</pagehelper-starter.version>

</properties>

<groupId>com.alibaba</groupId>

<artifactId>druid-spring-boot-starter</artifactId>

<version>${druid-boot.version}</version>

</dependency>

<groupId>org.mybatis.spring.boot</groupId>

<artifactId>mybatis-spring-boot-starter</artifactId>

<version>${mybatis-boot.version}</version>

</dependency>

<groupId>com.github.pagehelper</groupId>

<artifactId>pagehelper-spring-boot-starter</artifactId>

<version>${pagehelper-starter.version}</version>

</dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

<version>${mysql-connector.version}</version>

</dependency>

<groupId>com.microsoft.sqlserver</groupId>

<artifactId>mssql-jdbc</artifactId>

<version>${mssql-jdbc.version}</version>

</dependency>

<groupId>com.oracle.database.jdbc</groupId>

<artifactId>ojdbc8</artifactId>

<version>${oracle-jdbc.version}</version>

</dependency>

</dependencies>

</dependencyManagement>

Mybatis Plus

使用框架自带的分布控件，如果使用 pagehelper 会报 JSqlParser 的版本冲突，根据情况排除 pagehelper 版本（不推荐）。

<druid-boot.version>1.2.23</druid-boot.version>

<mybatis-plus.version>3.5.7</mybatis-plus.version>

<mysql-connector.version>8.0.33</mysql-connector.version>

<mssql-jdbc.version>8.2.2.jre8</mssql-jdbc.version>

<oracle-jdbc.version>19.3.0.0</oracle-jdbc.version>

</properties>

<groupId>com.baomidou</groupId>

<artifactId>mybatis-plus-boot-starter</artifactId>

<version>${mybatis-plus.version}</version>

</dependency>

<groupId>com.baomidou</groupId>

<artifactId>mybatis-plus-extension</artifactId>

<version>${mybatis-plus.version}</version>

</dependency>

<groupId>com.baomidou</groupId>

<artifactId>mybatis-plus-annotation</artifactId>

<version>${mybatis-plus.version}</version>

</dependency>

<groupId>com.alibaba</groupId>

<artifactId>druid-spring-boot-starter</artifactId>

<version>${druid-boot.version}</version>

</dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-j</artifactId>

<version>${mysql-connector.version}</version>

</dependency>

<groupId>com.microsoft.sqlserver</groupId>

<artifactId>mssql-jdbc</artifactId>

<version>${mssql-jdbc.version}</version>

</dependency>

<groupId>com.oracle.database.jdbc</groupId>

<artifactId>ojdbc8</artifactId>

<version>${oracle-jdbc.version}</version>

</dependency>

</dependencies>

</dependencyManagement>

yml 配置

Mybatis

mybatis:

# 指定sql映射文件位置

mapper-locations: classpath*:mapper/*.xml

configuration:

map-underscore-to-camel-case: true

log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

type-handlers-package: com.vipsoft.base.handler # MySQL 8.0 用以mysql中json格式的字段，进行转换的自定义转换器，转换为实体类的JSONObject属性

Mybatis-Plus

mybatis-plus:

mapper-locations: classpath*:mapper/*Mapper.xml

global-config:

banner: true

db-config:

id-type: auto

where-strategy: not_empty

insert-strategy: not_empty

update-strategy: not_null

type-handlers-package: com.vipsoft.base.handler # MySQL 8.0 用以mysql中json格式的字段，进行转换的自定义转换器，转换为实体类的JSONObject属性

configuration:

log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

map-underscore-to-camel-case: true

jdbc-type-for-null: 'null'

call-setters-on-nulls: true

shrink-whitespaces-in-sql: true

druid

SpringBoot 配置多数据源

spring:

profiles:

active: dev

resources:

static-locations: classpath:/META-INF/resources/,classpath:/resources/,classpath:/static/,classpath:/public/,classpath:/web/,file:${cuwor.file.path}

datasource:

type: com.alibaba.druid.pool.DruidDataSource

driver-class-name: com.mysql.cj.jdbc.Driver

#数据源基本配置

url: jdbc:mysql://192.168.1.100:3306/production_education?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=CONVERT_TO_NULL

username: root

password: root

#连接池的设置

druid:

initial-size: 5 #初始化时建立物理连接的个数

min-idle: 5 #最小连接池数量

max-active: 200 #最大连接池数量 maxIdle已经不再使用

max-wait: 60000 #获取连接时最大等待时间，单位毫秒

test-while-idle: true #申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。

time-between-eviction-runs-millis: 60000 #既作为检测的间隔时间又作为testWhileIdel执行的依据

#销毁线程时检测当前连接的最后活动时间和当前时间差大于该值时，关闭当前连接

min-evictable-idle-time-millis: 30000

validation-query: select 'x' #用来检测连接是否有效的sql 必须是一个查询语句（ mysql中为 select 'x' oracle中为 select 1 from dual）

test-on-borrow: false #申请连接时会执行validationQuery检测连接是否有效,开启会降低性能,默认为true

test-on-return: false #归还连接时会执行validationQuery检测连接是否有效,开启会降低性能,默认为true

#exception-sorter: true #当数据库抛出不可恢复的异常时,抛弃该连接

#pool-prepared-statements: true #是否缓存preparedStatement,mysql5.5+建议开启

max-pool-prepared-statement-per-connection-size: 20 #当值大于0时poolPreparedStatements会自动修改为true

filters: stat,wall #配置扩展插件

connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500 #通过connectProperties属性来打开mergeSql功能；慢SQL记录

use-global-data-source-stat: true #合并多个DruidDataSource的监控数据

#设置访问druid监控页的账号和密码,默认没有--放DrugConfig配置中

#stat-view-servlet.login-username: admin

#stat-view-servlet.login-password: admin

Config 配置

Druid 配置没有变化

package com.vipsoft.base.config;

import com.alibaba.druid.pool.DruidDataSource;

import com.alibaba.druid.support.http.StatViewServlet;

import com.alibaba.druid.support.http.WebStatFilter;

import org.springframework.boot.context.properties.ConfigurationProperties;

import org.springframework.boot.web.servlet.FilterRegistrationBean;

import org.springframework.boot.web.servlet.ServletRegistrationBean;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

import java.util.Arrays;

import java.util.HashMap;

import java.util.Map;

@Configuration

public class DruidConfig {

@ConfigurationProperties(prefix = "spring.datasource")

@Bean

public DataSource druid(){

return new DruidDataSource();

}

//配置Druid的监控

//1、配置一个管理后台的Servlet

@Bean

public ServletRegistrationBean statViewServlet(){

ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");

Map<String,String> initParams = new HashMap<>();

initParams.put("loginUsername","admin");

initParams.put("loginPassword","vipsoft");

initParams.put("resetEnable","false");

initParams.put("allow","");//默认就是允许所有访问

initParams.put("deny","192.168.15.21"); //IP黑名单（同时存在时，deny优先于allow）

bean.setInitParameters(initParams);

return bean;

}

//2、配置一个web监控的filter

@Bean

public FilterRegistrationBean webStatFilter(){

FilterRegistrationBean bean = new FilterRegistrationBean();

bean.setFilter(new WebStatFilter());

Map<String,String> initParams = new HashMap<>();

initParams.put("exclusions","*.js,*.css,/druid/*");

bean.setInitParameters(initParams);

bean.setUrlPatterns(Arrays.asList("/*"));

return bean;

}

Mybatis Plus 分页，需要添加拦截器配置，否则分页不生效

package com.vipsoft.base.config;

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 mybatisPlusInterceptor() {

MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();

interceptor.addInnerInterceptor(new PaginationInnerInterceptor());

return interceptor;

}

Mapper扫描

@MapperScan({"com.vipsoft.admin.mapper"}) 和 Mybatis 无区别

package com.vipsoft.admin;

import org.mybatis.spring.annotation.MapperScan;

import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;

import org.springframework.context.annotation.ComponentScan;

@ComponentScan(basePackages = {"com.vipsoft"})

@SpringBootApplication

@MapperScan({"com.vipsoft.admin.mapper"})

public class VipSoftAdminApplication {

public static void main(String[] args) {

SpringApplication.run(VipSoftAdminApplication.class, args);

}

Entity

SysMenu

//@TableName("sys_menu") 默认会将 SysMenu 解析成 sys_menu 如果解析后不是正确的表名，需要通过 TableName进行指定，

public class SysMenu extends BaseEntity

{

private static final long serialVersionUID = 1L;

/** 菜单ID */

@TableId(value = "menu_id", type = IdType.ASSIGN_ID)

private Long menuId;

/** 菜单名称 */

@TableField(value = "menu_name")

private String menuName;

/** 父菜单名称 */

@TableField(exist = false) //非数据库字段，进行排除

private String parentName;

....省略

}

Mapper.xml

SysMenuMapper.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">

</resultMap>

select menu_id, menu_name, parent_id, order_num, create_time from sys_menu

<where>

AND menu_name like concat('%', #{menuName}, '%')

</if>

AND visible = #{visible}

</if>

AND status = #{status}

</if>

</where>

order by parent_id, order_num

</select>

select menu_id, menu_name, parent_id, order_num, create_time from sys_menu

<where>

AND menu_name like concat('%', #{query.menuName}, '%')

</if>

AND visible = #{query.visible}

</if>

AND status = #{query.status}

</if>

</where>

order by parent_id, order_num

</select>

</mapper>

Mapper - SysMenuMapper

需要继承 BaseMapper

package com.vipsoft.admin.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import com.baomidou.mybatisplus.core.metadata.IPage;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;

import com.vipsoft.admin.entity.SysMenu;

import org.apache.ibatis.annotations.Param;

import java.util.List;

/**

* 菜单表数据层

public interface SysMenuMapper extends BaseMapper<SysMenu> {

List<SysMenu> listMenu(SysMenu menu);

IPage<SysMenu> listMenuPage(Page page, @Param("query") SysMenu menu);

}

Service

ISysMenuService

package com.vipsoft.admin.service;

import com.baomidou.mybatisplus.core.metadata.IPage;

import com.vipsoft.admin.entity.SysMenu;

import java.util.List;

/**

* 菜单业务层

public interface ISysMenuService

{

/**

* 列表查询(自定义SQL，分页)

List<SysMenu> listMenu(SysMenu menu);

/**

* 列表查询（框架分页）

IPage selectPage(SysMenu menu);

/**

* 列表查询（自定义SQL，分页）

IPage listMenuPage(SysMenu menu);

}

SysMenuService

package com.vipsoft.admin.service.impl;

import com.baomidou.mybatisplus.core.metadata.IPage;

import com.baomidou.mybatisplus.core.metadata.OrderItem;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;

import com.vipsoft.admin.entity.SysMenu;

import com.vipsoft.admin.mapper.SysMenuMapper;

import com.vipsoft.admin.service.ISysMenuService;

import com.github.pagehelper.PageHelper;

import com.github.pagehelper.PageInfo;

import com.github.pagehelper.PageParam;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;

import java.util.*;

import java.util.stream.Collectors;

/**

* 菜单业务层处理

* @author ruoyi

@Service

public class SysMenuServiceImpl implements ISysMenuService {

@Autowired

private SysMenuMapper menuMapper;

/**

* 列表查询(自定义SQL，分页)

@Override

public List<SysMenu> listMenu(SysMenu menu) {

return menuMapper.listMenu(new SysMenu());

}

/**

* 列表查询（框架分页）

@Override

public IPage selectPage(SysMenu menu) {

Page page = new Page();

page.setCurrent(2);

page.setSize(10);

List<OrderItem> orderItems = new ArrayList<>();

orderItems.add(OrderItem.desc("menu_id"));

page.setOrders(orderItems);

Page pageList = menuMapper.selectPage(page, null);

return pageList;

}

/**

* 列表查询（自定义SQL，分页）

@Override

public IPage listMenuPage(SysMenu menu) {

Page page = new Page();

page.setCurrent(2);

page.setSize(10);

List<OrderItem> orderItems = new ArrayList<>();

orderItems.add(OrderItem.desc("menu_id")); //先按 menu_id 排序，再按 mapper.xml 中的排（可以在查询输出的SQL中查看）

page.setOrders(orderItems);

IPage<SysMenu> pageList = menuMapper.listMenuPage(page, menu);

return pageList;

}

Controller

package com.vipsoft.admin.controller;

import com.baomidou.mybatisplus.core.metadata.IPage;

import com.vipsoft.admin.entity.SysMenu;

import com.vipsoft.admin.service.ISysMenuService;

import com.vipsoft.base.core.ApiResult;

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;

/**

* 菜单信息

@RestController

@RequestMapping("/menu")

public class SysMenuContro

ller {

@Autowired

private ISysMenuService menuService;

/**

* 获取菜单列表

@GetMapping("/selectPage")

public ApiResult selectPage(SysMenu menu) {

IPage menus = menuService.selectPage(menu);

return new ApiResult(menus);

}

/**

* 获取菜单列表

@GetMapping("/list")

public ApiResult listMenu(SysMenu menu) {

List<SysMenu> menus = menuService.listMenu(menu);

return new ApiResult(menus);

}

/**

* 获取菜单列表