springboot集成druid，多数据源可视化，p6spy

╰+攻爆jí腚メ 2021-05-16 20:36 623阅读 0赞

1。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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.2.2.RELEASE</version>
            <relativePath/> 
        </parent>
        <groupId>com.hanhuide</groupId>
        <artifactId>driver</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>driver</name>
        <description>Demo project for Spring Boot</description>
    
        <properties>
            <java.version>1.8</java.version>
        </properties>
    
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter</artifactId>
            </dependency>
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus</artifactId>
                <version>2.3</version>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <scope>runtime</scope>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
                <exclusions>
                    <exclusion>
                        <groupId>org.junit.vintage</groupId>
                        <artifactId>junit-vintage-engine</artifactId>
                    </exclusion>
                </exclusions>
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <version>1.18.10</version>
                <scope>compile</scope>
            </dependency>
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatisplus-spring-boot-starter</artifactId>
                <version>1.0.5</version>
            </dependency>
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid-spring-boot-starter</artifactId>
                <version>1.1.21</version>
            </dependency>
            <dependency>
                <groupId>p6spy</groupId>
                <artifactId>p6spy</artifactId>
                <version>3.8.0</version>
            </dependency>
            <dependency>
                <groupId>org.aspectj</groupId>
                <artifactId>aspectjweaver</artifactId>
                <version>1.9.5</version>
                <scope>compile</scope>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
                <version>2.2.2.RELEASE</version>
                <scope>compile</scope>
            </dependency>
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
        </build>
    
    </project>

2. 修改application.yml

server:
      port: 9000
    spring:
      datasource:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.p6spy.engine.spy.P6SpyDriver
        druid:
          initial-size: 10
          max-active: 100
          min-idle: 10
          max-wait: 60000
          pool-prepared-statements: true
          max-pool-prepared-statement-per-connection-size: 20
          time-between-eviction-runs-millis: 60000
          min-evictable-idle-time-millis: 300000
          validation-query: SELECT 1 FROM DUAL
          test-while-idle: true
          test-on-borrow: false
          test-on-return: false
          stat-view-servlet:
            enabled: true
            url-pattern: /druid/*
            login-username: admin
            login-password: admin
          filter:
            stat:
              log-slow-sql: true
              slow-sql-millis: 1000
            merge-sql: true
            wall:
              config:
                multi-statement-allow: true
        master:  #数据源1
          url: jdbc:p6spy:mysql://localhost:3306/springcloud?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=true
          username: root
          password: root
        cluster: #数据源2
          url: jdbc:p6spy:mysql://localhost:3306/occs?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=true
          username: root
          password: root
    mybatis-plus:
      mapper-locations: classpath:/mapper/*.xml
      type-aliases-package: com.hanhuide.driver.mapper
      configuration:
        map-underscore-to-camel-case: true

password: root

3.添加p6spy日志输出配置（spy.properties）与yal同级

module.log=com.p6spy.engine.logging.P6LogFactory,com.p6spy.engine.outage.P6OutageFactory
    # 自定义日志打印
    logMessageFormat=com.p6spy.engine.spy.appender.CustomLineFormat
    # 使用日志系统记录sql
    appender=com.p6spy.engine.spy.appender.Slf4JLogger
    ## 配置记录Log例外
    excludecategories=info,debug,result,batc,resultset
    # 设置使用p6spy driver来做代理
    deregisterdrivers=true
    # 日期格式
    dateformat=yyyy-MM-dd HH:mm:ss
    # 实际驱动
    driverlist=com.mysql.cj.jdbc.Driver
    # 是否开启慢SQL记录
    outagedetection=true
    # 慢SQL记录标准 秒
    outagedetectioninterval=2
    customLogMessageFormat=%(currentTime) | SQL耗时： %(executionTime) ms | 连接信息： %(category)-%(connectionId) | 执行语句： %(sql)

4. 定义数据源名称

package com.hanhuide.driver.dataSource;
    
    /**
     * 数据源名称
     * @author 韩惠德
     */
    public interface DataSourceNames {
        String MASTER = "MASTER";
        String CLUSTER = "CLUSTER";
    }

5.配置多数据源 ，将数据源添加到系统中

package com.hanhuide.driver.config;
    
    import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
    import com.hanhuide.driver.dataSource.DataSourceNames;
    import com.hanhuide.driver.dataSource.DynamicDataSource;
    import lombok.extern.slf4j.Slf4j;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.Primary;
    
    import javax.sql.DataSource;
    import java.util.HashMap;
    import java.util.Map;
    
    /**
     * 配置多数据源 ，将数据源添加到系统中
     *
     * @author 韩惠德
     * @date 2018-05-14
     */
    @Slf4j
    @Configuration
    public class DynamicDataSourceConfig {
    
        /**
         * 创建 DataSource Bean
         */
    
        @Bean
        @ConfigurationProperties("spring.datasource.master")
        public DataSource oneDataSource() {
            DataSource dataSource = DruidDataSourceBuilder.create().build();
            return dataSource;
        }
    
        @Bean
        @ConfigurationProperties("spring.datasource.cluster")
        public DataSource twoDataSource() {
            DataSource dataSource = DruidDataSourceBuilder.create().build();
            return dataSource;
        }
    
        /**
         * 如果还有数据源,在这继续添加 DataSource Bean
         */
    
        @Bean
        @Primary
        public DynamicDataSource dataSource(DataSource oneDataSource, DataSource twoDataSource) {
            Map<Object, Object> targetDataSources = new HashMap<>();
            targetDataSources.put(DataSourceNames.MASTER, oneDataSource);
            targetDataSources.put(DataSourceNames.CLUSTER, twoDataSource);
            // 还有数据源,在targetDataSources中继续添加
            log.info("DataSources:" + targetDataSources);
            return new DynamicDataSource(oneDataSource, targetDataSources);
        }
    }

6. 动态数据源

package com.hanhuide.driver.dataSource;
    
    import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
    
    import javax.sql.DataSource;
    import java.util.Map;
    
    /**
     * 动态数据源
     * @author 韩惠德
     */
    public class DynamicDataSource extends AbstractRoutingDataSource {
        private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
    
        /**
         * 配置DataSource, defaultTargetDataSource为主数据库
         */
        public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
            super.setDefaultTargetDataSource(defaultTargetDataSource);
            super.setTargetDataSources(targetDataSources);
            super.afterPropertiesSet();
        }
    
        @Override
        protected Object determineCurrentLookupKey() {
            return getDataSource();
        }
    
        public static void setDataSource(String dataSource) {
            contextHolder.set(dataSource);
        }
    
        public static String getDataSource() {
            return contextHolder.get();
        }
    
        public static void clearDataSource() {
            contextHolder.remove();
        }
    }

7. 配置p6spy输出日志

package com.hanhuide.driver.logger;
    
    import com.p6spy.engine.spy.appender.MessageFormattingStrategy;
    
    import java.time.LocalDateTime;
    
    /**
     * @program: maven
     * @description:配置p6spy输出日志
     * @author: 韩惠德
     * @create: 2019-12-24 15:03
     * @version: 1.0
     **/
    public class P6SpyLogger implements MessageFormattingStrategy {
        
        @Override
        public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql, String s4) {
            return !"".equals(sql.trim()) ? "[ " + LocalDateTime.now() + " ] --- | took "
                    + elapsed + "ms | " + category + " | connection " + connectionId + "\n "
                    + sql + ";" : "";
        }
    }

8. 利用注解自由切换数据库，自定义注解

package com.hanhuide.driver.annotation;
    
    
    import com.hanhuide.driver.dataSource.DataSourceNames;
    
    import java.lang.annotation.*;
    
    /**
     * 多数据源注解
     *
     * @author 韩惠德
     * @date 2018-05-14
     */
    @Documented
    @Target({ElementType.METHOD})
    @Retention(RetentionPolicy.RUNTIME)
    public @interface DataSource {
        String value() default DataSourceNames.MASTER;
    }

9.利用切面添加注解

package com.hanhuide.driver.aspect;
    
    import com.hanhuide.driver.annotation.DataSource;
    import com.hanhuide.driver.dataSource.DataSourceNames;
    import com.hanhuide.driver.dataSource.DynamicDataSource;
    import lombok.extern.slf4j.Slf4j;
    import org.aspectj.lang.JoinPoint;
    import org.aspectj.lang.ProceedingJoinPoint;
    import org.aspectj.lang.annotation.Around;
    import org.aspectj.lang.annotation.Aspect;
    import org.aspectj.lang.annotation.Before;
    import org.aspectj.lang.annotation.Pointcut;
    import org.aspectj.lang.reflect.MethodSignature;
    import org.springframework.core.Ordered;
    import org.springframework.stereotype.Component;
    
    import java.lang.reflect.Method;
    
    /**
     * 数据源AOP切面处理
     *
     * @author geYang
     * @date 2018-05-14
     */
    @Slf4j
    @Aspect
    @Component
    public class DataSourceAspect implements Ordered {
        /**
         * 切点: 所有配置 DataSource 注解的方法
         */
        @Pointcut("@annotation(com.hanhuide.driver.annotation.DataSource)")
        public void dataSourcePointCut() {
        }
    
        @Around("dataSourcePointCut()")
        public Object around(ProceedingJoinPoint point) throws Throwable {
            MethodSignature signature = (MethodSignature) point.getSignature();
            Method method = signature.getMethod();
            DataSource ds = method.getAnnotation(DataSource.class);
            if (ds == null) {
                DynamicDataSource.setDataSource(DataSourceNames.MASTER);
                log.debug("set datasource is " + DataSourceNames.MASTER);
            } else {
                DynamicDataSource.setDataSource(ds.value());
                log.debug("set datasource is " + ds.value());
            }
    
            try {
                return point.proceed();
            } finally {
                DynamicDataSource.clearDataSource();
                log.debug("clean datasource");
            }
        }
    
        @Override
        public int getOrder() {
            return 1;
        }
    }

8.配置启动类

package com.hanhuide.driver;
    
    import com.hanhuide.driver.config.DynamicDataSourceConfig;
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
    import org.springframework.context.annotation.Import;
    
    /**
     * 动态数据源配置,需要将自有的配置依赖(DynamicDataSourceConfig),将原有的依赖去除(DataSourceAutoConfiguration)
     * @author 韩惠德
     * @date 2018-05-15
     */
    @Import({DynamicDataSourceConfig.class})
    @SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
    @MapperScan(basePackages = "com.hanhuide.driver.Service")//这是我测试数据的扫描地址
    public class DriverApplication {
    
        public static void main(String[] args) {
            SpringApplication.run(DriverApplication.class, args);
        }
    }

\--------------------------------------------------------------------------------------到此springboot配置druid多数据源 ，注解切换的代码就完事了

接下来我们编辑测试类

1.随便一个实体类

package com.hanhuide.driver.Service;
    
    import com.baomidou.mybatisplus.annotations.TableField;
    import com.baomidou.mybatisplus.annotations.TableId;
    import com.baomidou.mybatisplus.annotations.TableName;
    import com.baomidou.mybatisplus.enums.IdType;
    import lombok.Data;
    
    import java.io.Serializable;
    import java.util.Date;
    import java.util.List;
    
    /**
     * <p>
     *
     * </p>
     *
     * @author 韩惠德
     * @since 2019-11-29
     */
    @TableName("sys_user")
    @Data
    public class SysUser implements Serializable{
    
        private static final long serialVersionUID = 1L;
    
        /**
         * 用户ID
         */
        @TableId(value = "USER_ID", type = IdType.AUTO)
        private Long userId;
        /**
         * 用户名
         */
        @TableField("USERNAME")
        private String username;
        /**
         * 密码
         */
        @TableField("PASSWORD")
        private String password;
        /**
         * 部门ID
         */
        @TableField("DEPT_ID")
        private Long deptId;
        /**
         * 邮箱
         */
        @TableField("EMAIL")
        private String email;
        /**
         * 联系电话
         */
        @TableField("MOBILE")
        private String mobile;
        /**
         * 状态 0锁定 1有效
         */
        @TableField("STATUS")
        private String status;
        /**
         * 创建时间
         */
        @TableField("CREATE_TIME")
        private Date createTime;
        /**
         * 修改时间
         */
        @TableField("MODIFY_TIME")
        private Date modifyTime;
        /**
         * 最近访问时间
         */
        @TableField("LAST_LOGIN_TIME")
        private Date lastLoginTime;
        /**
         * 性别 0男 1女 2保密
         */
        @TableField("SSEX")
        private String ssex;
        /**
         * 描述
         */
        @TableField("DESCRIPTION")
        private String description;
        /**
         * 用户头像
         */
        @TableField("AVATAR")
        private String avatar;
    
    
        public void setAvatar(String avatar) {
            this.avatar = avatar;
        }
    
        public static final String USER_ID = "USER_ID";
    
        public static final String USERNAME = "USERNAME";
    
        public static final String PASSWORD = "PASSWORD";
    
        public static final String DEPT_ID = "DEPT_ID";
    
        public static final String EMAIL = "EMAIL";
    
        public static final String MOBILE = "MOBILE";
    
        public static final String STATUS = "STATUS";
    
        public static final String CREATE_TIME = "CREATE_TIME";
    
        public static final String MODIFY_TIME = "MODIFY_TIME";
    
        public static final String LAST_LOGIN_TIME = "LAST_LOGIN_TIME";
    
        public static final String SSEX = "SSEX";
    
        public static final String DESCRIPTION = "DESCRIPTION";
    
        public static final String AVATAR = "AVATAR";
    
    
        @Override
        public String toString() {
            return "SysUser{" +
                    "userId=" + userId +
                    ", username=" + username +
                    ", password=" + password +
                    ", deptId=" + deptId +
                    ", email=" + email +
                    ", mobile=" + mobile +
                    ", status=" + status +
                    ", createTime=" + createTime +
                    ", modifyTime=" + modifyTime +
                    ", lastLoginTime=" + lastLoginTime +
                    ", ssex=" + ssex +
                    ", description=" + description +
                    ", avatar=" + avatar +
                    "}";
        }
    }

2，编写mapper

package com.hanhuide.driver.Service;
    
    import com.baomidou.mybatisplus.mapper.BaseMapper;
    import com.hanhuide.driver.annotation.DataSource;
    import com.hanhuide.driver.dataSource.DataSourceNames;
    import org.apache.ibatis.annotations.Select;
    import org.springframework.stereotype.Service;
    
    import java.util.List;
    
    /**
     * @program: maven
     * @description:
     * @author: 韩惠德
     * @create: 2019-12-24 16:39
     * @version: 1.0
     **/
    @Service
    public interface CeshiMapper extends BaseMapper<SysUser> {
        @Select("select * from sys_user")
        List<SysUser> findAll();
    
        @Select("select * from sys_user")
        @DataSource(DataSourceNames.CLUSTER)//注解切换数据源
        List<SysUser> findAll2();
    
    }

再写个contrller

package com.hanhuide.driver.Service;
    
    import lombok.extern.slf4j.Slf4j;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import javax.annotation.Resource;
    import java.util.List;
    
    /**
     * @program: maven
     * @description:
     * @author: 韩惠德
     * @create: 2019-12-24 16:41
     * @version: 1.0
     **/
    @RestController
    @Slf4j
    public class Contrller11 {
        @Resource
        private CeshiMapper ceshiMapper;
    
        @GetMapping("ceshi")
        public List<SysUser> ceshi() {
            return ceshiMapper.findAll();
        }
    
        @GetMapping("ceshi2")
        public List<SysUser> ceshi2() {
            return ceshiMapper.findAll2();
        }
    }

===============到此测试类类及方法就完成了，启动运行

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI3MDgxMDE1_size_16_color_FFFFFF_t_70][]

两个数据源已经连接

![2019122417154133.png][]

项目已启动

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI3MDgxMDE1_size_16_color_FFFFFF_t_70 1][]

连接主数据库成功

![20191224171700716.png][]

p6spy 搭建成功  但是乱码没有解决

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI3MDgxMDE1_size_16_color_FFFFFF_t_70 2][]

说明数据库切换成功

![20191224171841371.png][]

打开druid的监控页面查看当前数据源的相关信息，登录用户名密码在配置文件共配置了

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI3MDgxMDE1_size_16_color_FFFFFF_t_70 3][]

查看两个数据源

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI3MDgxMDE1_size_16_color_FFFFFF_t_70 4][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI3MDgxMDE1_size_16_color_FFFFFF_t_70 5][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI3MDgxMDE1_size_16_color_FFFFFF_t_70]: /images/20210516/63fff09dc9794f10a6c0f2fee5df1f9d.png
[2019122417154133.png]: /images/20210516/57791692614d45e6855329723e3c3f67.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI3MDgxMDE1_size_16_color_FFFFFF_t_70 1]: /images/20210516/c45faa480e504430ba7d38dfe9236cd5.png
[20191224171700716.png]: /images/20210516/809dd440384c41c195cb3b303d484603.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI3MDgxMDE1_size_16_color_FFFFFF_t_70 2]: /images/20210516/59a32f4bebdc4a4e8b90986c912ff909.png
[20191224171841371.png]: /images/20210516/4f00180cbabe4eff86cb8eda3852a6a1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI3MDgxMDE1_size_16_color_FFFFFF_t_70 3]: /images/20210516/0d6d218a643645e49a6fbbbf7f865193.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI3MDgxMDE1_size_16_color_FFFFFF_t_70 4]: /images/20210516/248e01e103d14cdba27fc17b5d55931c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI3MDgxMDE1_size_16_color_FFFFFF_t_70 5]: /images/20210516/9dac43d181a4434597e0d116e49f7fa0.png