springboot集成druid,多數(shù)據(jù)源可視化,p6spy問題

更新時(shí)間：2024年01月12日 09:42:12 作者：砥礪-前行

這篇文章主要介紹了springboot集成druid,多數(shù)據(jù)源可視化,p6spy問題,具有很好的參考價(jià)值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

1.maven添加依賴
2.修改application.yml
3.添加p6spy日志輸出配置（spy.properties）與yal同級
4.定義數(shù)據(jù)源名稱
5.配置多數(shù)據(jù)源，將數(shù)據(jù)源添加到系統(tǒng)中
6.動態(tài)數(shù)據(jù)源
7.配置p6spy輸出日志
8.利用注解自由切換數(shù)據(jù)庫，自定義注解
9.利用切面添加注解
8.配置啟動類
總結(jié)

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/> <!-- lookup parent from repository -->
    </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:  #數(shù)據(jù)源1
      url: jdbc:p6spy:mysql://localhost:3306/springcloud?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=true
      username: root
      password: root
    cluster: #數(shù)據(jù)源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
# 使用日志系統(tǒng)記錄sql
appender=com.p6spy.engine.spy.appender.Slf4JLogger
## 配置記錄Log例外
excludecategories=info,debug,result,batc,resultset
# 設(shè)置使用p6spy driver來做代理
deregisterdrivers=true
# 日期格式
dateformat=yyyy-MM-dd HH:mm:ss
# 實(shí)際驅(qū)動
driverlist=com.mysql.cj.jdbc.Driver
# 是否開啟慢SQL記錄
outagedetection=true
# 慢SQL記錄標(biāo)準(zhǔn) 秒
outagedetectioninterval=2
customLogMessageFormat=%(currentTime) | SQL耗時(shí)： %(executionTime) ms | 連接信息： %(category)-%(connectionId) | 執(zhí)行語句： %(sql)

4.定義數(shù)據(jù)源名稱

package com.hanhuide.driver.dataSource;
 
/**
 * 數(shù)據(jù)源名稱
 * @author 韓惠德
 */
public interface DataSourceNames {
    String MASTER = "MASTER";
    String CLUSTER = "CLUSTER";
}

5.配置多數(shù)據(jù)源，將數(shù)據(jù)源添加到系統(tǒng)中

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;
 
/**
 * 配置多數(shù)據(jù)源 ，將數(shù)據(jù)源添加到系統(tǒng)中
 *
 * @author 韓惠德
 * @date 2018-05-14
 */
@Slf4j
@Configuration
public class DynamicDataSourceConfig {
 
    /**
     * 創(chuàng)建 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;
    }
 
    /**
     * 如果還有數(shù)據(jù)源,在這繼續(xù)添加 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);
        // 還有數(shù)據(jù)源,在targetDataSources中繼續(xù)添加
        log.info("DataSources:" + targetDataSources);
        return new DynamicDataSource(oneDataSource, targetDataSources);
    }
}

6.動態(tài)數(shù)據(jù)源

package com.hanhuide.driver.dataSource;
 
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
 
import javax.sql.DataSource;
import java.util.Map;
 
/**
 * 動態(tài)數(shù)據(jù)源
 * @author 韓惠德
 */
public class DynamicDataSource extends AbstractRoutingDataSource {
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
 
    /**
     * 配置DataSource, defaultTargetDataSource為主數(shù)據(jù)庫
     */
    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.利用注解自由切換數(shù)據(jù)庫，自定義注解

package com.hanhuide.driver.annotation;
 
 
import com.hanhuide.driver.dataSource.DataSourceNames;
 
import java.lang.annotation.*;
 
/**
 * 多數(shù)據(jù)源注解
 *
 * @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;
 
/**
 * 數(shù)據(jù)源AOP切面處理
 *
 * @author geYang
 * @date 2018-05-14
 */
@Slf4j
@Aspect
@Component
public class DataSourceAspect implements Ordered {
    /**
     * 切點(diǎn): 所有配置 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.配置啟動類

到此springboot配置druid多數(shù)據(jù)源，注解切換的代碼就完事了

接下來我們編輯測試類

1.隨便一個(gè)實(shí)體類

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;
    /**
     * 聯(lián)系電話
     */
    @TableField("MOBILE")
    private String mobile;
    /**
     * 狀態(tài) 0鎖定 1有效
     */
    @TableField("STATUS")
    private String status;
    /**
     * 創(chuàng)建時(shí)間
     */
    @TableField("CREATE_TIME")
    private Date createTime;
    /**
     * 修改時(shí)間
     */
    @TableField("MODIFY_TIME")
    private Date modifyTime;
    /**
     * 最近訪問時(shí)間
     */
    @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)//注解切換數(shù)據(jù)源
    List<SysUser> findAll2();
 
}

再寫個(gè)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();
    }
}

到此測試類類及方法就完成了，啟動運(yùn)行