SpringBoot集成Mybatis-Plus、Druid（SQL监控）

r囧r小猫 2023-02-21 09:13 356阅读 0赞

1.创建springboot项目，先实现mybatis-plus、druid

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzcxNzY2_size_16_color_FFFFFF_t_70][]

2.pom文件引入

<dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <optional>true</optional>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.47</version>
            </dependency>
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.1.10</version>
            </dependency>
            
            <dependency>
                <groupId>log4j</groupId>
                <artifactId>log4j</artifactId>
                <version>1.2.17</version>
            </dependency>
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
                <version>3.2.0</version>
            </dependency>

application.yml

spring:
      datasource:
        username: root
        password: 123456
        url: jdbc:mysql://localhost/springboot?characterEncoding=utf-8&useSSl=false
        driver-class-name: com.mysql.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        #监控统计拦截的filters
        filters: stat,wall,log4j
        #druid配置
        #配置初始化大小/最小/最大
        initialSize: 5
        minIdle: 5
        maxActive: 20
        #获取连接等待超时时间
        maxWait: 60000
        #间隔多久进行一次检测，检测需要关闭的空闲连接
        timeBetweenEvictionRunsMillis: 60000
        #一个连接在池中最小生存的时间
        minEvictableIdleTimeMillis: 300000
        validationQuery: SELECT 1 FROM DUAL
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        #打开PSCache，并指定每个连接上PSCache的大小。oracle设为true，mysql设为false。分库分表较多推荐设置为false
        poolPreparedStatements: false
        maxPoolPreparedStatementPerConnectionSize: 20
        # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
        connectionProperties:
          druid:
            stat:
              mergeSql: true
              slowSqlMillis: 5000
    
    #mybatis是独立节点，需要单独配置
    mybatis-plus:
      mapper-locations: classpath*:mapper/*.xml
      type-aliases-package: com.frank.securityjwt.entity
      configuration:
        map-underscore-to-camel-case: true

DruidConfiguration

package com.frank.securityjwt.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 org.springframework.context.annotation.PropertySource;
    
    import javax.sql.DataSource;
    /**
     * @author 小石潭记
     * @date 2020/6/29 21:33
     * @Description: druid参数配置
     *  http://localhost:8080/druid/login.html
     */
    
    @Configuration
    @PropertySource(value = "classpath:application.yml")
    public class DruidConfiguration {
    
        /**
         * @todo 数据源配置
         */
        @Bean(destroyMethod = "close", initMethod = "init")
        @ConfigurationProperties(prefix = "spring.datasource")
        public DataSource druidDataSource() {
            DruidDataSource druidDataSource = new DruidDataSource();
            return druidDataSource;
        }
    
        /**
         * druid
         * 注册一个StatViewServlet
         * @return
         */
        @Bean
        public ServletRegistrationBean druidStatViewServlet(){
            //org.springframework.boot.context.embedded.ServletRegistrationBean提供类的进行注册.
            ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(),"/druid/*");
    
            //添加初始化参数：initParams
            //白名单：
            servletRegistrationBean.addInitParameter("allow","127.0.0.1");
            //IP黑名单 (存在共同时，deny优先于allow) : 如果满足deny的话提示:Sorry, you are not permitted to view this page.
            //servletRegistrationBean.addInitParameter("deny","192.168.1.73");
            //登录查看信息的账号密码.
            servletRegistrationBean.addInitParameter("loginUsername","admin");
            servletRegistrationBean.addInitParameter("loginPassword","123456");
            //是否能够重置数据.
            servletRegistrationBean.addInitParameter("resetEnable","false");
            return servletRegistrationBean;
        }
    
        /**
         * druid过滤器
         * 注册一个：filterRegistrationBean
         * @return
         */
        @Bean
        public FilterRegistrationBean druidStatFilter(){
            FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
            //添加过滤规则.
            filterRegistrationBean.addUrlPatterns("/*");
            //添加不需要忽略的格式信息.
            filterRegistrationBean.addInitParameter("exclusions","*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
            return filterRegistrationBean;
        }
    
    }

DemoMapper

package com.frank.securityjwt.dao;
    
    import com.baomidou.mybatisplus.core.mapper.BaseMapper;
    import com.frank.securityjwt.entity.DemoEntity;
    
    import java.util.List;
    
    /**
     * @author 小石潭记
     * @date 2020/6/29 21:23
     * @Description: ${todo}
     */
    public interface DemoMapper extends BaseMapper<DemoEntity> {
    
        List<DemoEntity> getUser();
    }

DemoEntity

package com.frank.securityjwt.entity;
    
    import lombok.Data;
    
    /**
     * @author 小石潭记
     * @date 2020/6/29 21:16
     * @Description: ${todo}
     */
    @Data
    public class DemoEntity {
    
        private Integer id;
    
        private Integer age;
    
        private String name;
    
        private Float height;
    
    }

DemoService

package com.frank.securityjwt.service;
    
    import com.frank.securityjwt.entity.DemoEntity;
    
    import java.util.List;
    
    /**
     * @author 小石潭记
     * @date 2020/6/29 21:20
     * @Description: ${todo}
     */
    public interface DemoService {
    
        List<DemoEntity> getUser();
    
    }

DemoServiceImpl

package com.frank.securityjwt.service.impl;
    
    import com.frank.securityjwt.dao.DemoMapper;
    import com.frank.securityjwt.entity.DemoEntity;
    import com.frank.securityjwt.service.DemoService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import java.util.List;
    
    /**
     * @author 小石潭记
     * @date 2020/6/29 21:21
     * @Description: ${todo}
     */
    @Service
    public class DemoServiceImpl implements DemoService {
    
        @Autowired
        private DemoMapper demoMapper;
    
        @Override
        public List<DemoEntity> getUser() {
            return demoMapper.getUser();
        }
    }

DemoController

package com.frank.securityjwt.web;
    
    import com.frank.securityjwt.entity.DemoEntity;
    import com.frank.securityjwt.service.DemoService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import java.util.List;
    
    /**
     * @author 小石潭记
     * @date 2020/6/29 21:19
     * @Description: ${todo}
     */
    @RestController
    @RequestMapping("/test")
    public class DemoController {
    
        @Autowired
        private DemoService service;
    
        @RequestMapping("/getUser")
        public List<DemoEntity> getUser(){
            List<DemoEntity> result = service.getUser();
            return result;
        }
    
    }

SecurityJwtApplication

package com.frank.securityjwt;
    
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    
    @SpringBootApplication
    @MapperScan(basePackages = "com.frank.securityjwt.dao")
    public class SecurityJwtApplication {
    
        public static void main(String[] args) {
            SpringApplication.run(SecurityJwtApplication.class, args);
        }
    
    }

DemoMapper.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.frank.securityjwt.dao.DemoMapper">
        <select id="getUser" resultType="com.frank.securityjwt.entity.DemoEntity">
            SELECT * FROM girl
        </select>
    </mapper>

sql脚本：

-- --------------------------------------------------------
    -- 主机:                           127.0.0.1
    -- 服务器版本:                        5.6.40 - MySQL Community Server (GPL)
    -- 服务器操作系统:                      Win64
    -- HeidiSQL 版本:                  8.2.0.4675
    -- --------------------------------------------------------
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET NAMES utf8 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    
    -- 导出 springboot 的数据库结构
    CREATE DATABASE IF NOT EXISTS `springboot` /*!40100 DEFAULT CHARACTER SET utf8 */;
    USE `springboot`;
    
    
    -- 导出  表 springboot.girl 结构
    CREATE TABLE IF NOT EXISTS `girl` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `age` int(11) DEFAULT NULL,
      `name` varchar(50) DEFAULT NULL,
      `height` int(10) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
    
    -- 正在导出表  springboot.girl 的数据：~3 rows (大约)
    DELETE FROM `girl`;
    /*!40000 ALTER TABLE `girl` DISABLE KEYS */;
    INSERT INTO `girl` (`id`, `age`, `name`, `height`) VALUES
    	(1, 12, '小红', 1),
    	(2, 16, '小皇', 2),
    	(3, 20, '小花', 1);
    /*!40000 ALTER TABLE `girl` ENABLE KEYS */;
    /*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
    /*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

启动项目测试：[http://localhost:8080/test/getUser][http_localhost_8080_test_getUser]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzcxNzY2_size_16_color_FFFFFF_t_70 1][]

http://localhost:8080/druid/login.html 使用配置的账号密码登陆

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzcxNzY2_size_16_color_FFFFFF_t_70 2][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzcxNzY2_size_16_color_FFFFFF_t_70 3][]

自此springboot集成mybatis、druid成功。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzcxNzY2_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2020062921425446.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzcxNzY2,size_16,color_FFFFFF,t_70
[http_localhost_8080_test_getUser]: http://localhost:8080/test/getUser
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzcxNzY2_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200629214611500.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzcxNzY2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzcxNzY2_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200629214654109.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzcxNzY2,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzcxNzY2_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200629214703944.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzMzcxNzY2,size_16,color_FFFFFF,t_70