SpringBoot统计、监控SQL运行情况的方法详解

关于SpringBoot统计、监控SQL运行情况的方法，可以采用以下两种方式实现：

1. 使用Druid Spring Boot Starter

Druid Spring Boot Starter是阿里巴巴为了简化Druid在Spring Boot中的配置而推出的开箱即用的库。它基于Druid DataSource和Spring Boot自动配置机制，并提供大量属性配置供用户个性化定制。使用这种方法可以方便地监控SQL运行情况，具体步骤如下：

1. 在Spring Boot中添加Druid Spring Boot Starter依赖
   ```
   

com.alibaba
druid-spring-boot-starter
${druid.version}

2. 在application.properties/application.yaml文件中进行属性配置
# 配置数据源基本属性
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=

# 配置Druid监控器
spring.datasource.druid.initial-size=1
spring.datasource.druid.min-idle=1
spring.datasource.druid.max-active=20
spring.datasource.druid.filters=stat,wall,slf4j

# 配置Druid统计监控Servlet
spring.datasource.druid.stat-view-servlet.enabled=true
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=admin
```

通过访问http://localhost:8080/druid/stat/sql.html即可查看SQL运行情况的监控信息

2. 自定义拦截器实现SQL统计与监控

除了使用Druid Spring Boot Starter之外，我们还可以通过自定义拦截器实现SQL统计与监控。以MySQL为例，具体步骤如下：

1. 在application.properties/application.yaml文件中配置数据源参数
   # 配置数据源基本属性
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

2. 自定义拦截器实现SQL统计与监控
   ```java
   public class SQLInterceptor implements HandlerInterceptor {

   // 统计SQL执行总耗时
   private ThreadLocal totalTime = new ThreadLocal<>();
   // 统计SQL执行次数
   private ThreadLocal count = new ThreadLocal<>();

   @Override
   public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
   totalTime.set(System.currentTimeMillis());
   count.set(0L);
   return true;
   }

   @Override
   public void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex) throws Exception {
   long time = System.currentTimeMillis() - totalTime.get();
   long sqlCount = count.get();
   String sql = SQLContextHolder.getCurrentSQL();
   // 输出SQL信息
   System.out.println("[SQL Monitor] Executed SQL: " + sql + ", Time: " + time + ", Count: " + sqlCount);
   }

   @Override
   public void postHandle(HttpServletRequest request, HttpServletResponse response, Object handler, ModelAndView modelAndView) throws Exception {
   // 统计SQL执行次数
   long sqlCount = count.get();
   count.set(sqlCount + 1);
   }

}
3. 在拦截器中获取SQL语句，存储到ThreadLocal中java
public class SQLContextHolder {

  private static ThreadLocal<String> currentSQL = new ThreadLocal<>();

  public static String getCurrentSQL() {
     return currentSQL.get();
  }

  public static void setCurrentSQL(String sql) {
     currentSQL.set(sql);
  }

  public static void clear() {
     currentSQL.set(null);
  }

}
4. 注册拦截器java
@Configuration
public class WebMvcConfig implements WebMvcConfigurer {

  @Override
  public void addInterceptors(InterceptorRegistry registry) {
     registry.addInterceptor(new SQLInterceptor());
  }

}
```

这样，每次执行SQL语句时，就能在控制台输出SQL执行信息。

阅读剩余 64%