springboot2.x配置druid sql监控

心已赠人 2023-08-17 16:41 97阅读 0赞

后端接口响应慢，通常我们就需要优化代码和sql，如果项目中使用druid连接池，那么我们可以利用其提供的sql监控功能，来帮助我们快速定位慢sql已经sql执行次数等问题，springboot2之后，durid监控配置变的更简单了，不需要额外的代码，只需要添加配置即可。整个项目配置如下：

依赖

<dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
    
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid-spring-boot-starter</artifactId>
                <version>1.1.10</version>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-jpa</artifactId>
            </dependency>
    
            <dependency>
                <groupId>org.postgresql</groupId>
                <artifactId>postgresql</artifactId>
                <scope>runtime</scope>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>

application.yml

spring:
      datasource:
        druid:
          url: jdbc:postgresql://127.0.0.1:5432/test01?characterEncoding=utf-8
          username: admin
          password: 123456
          driver-class-name: org.postgresql.Driver
          initial-size: 1
          max-active: 20
          max-wait: 6000
          pool-prepared-statements: true
          max-pool-prepared-statement-per-connection-size: 20
          connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=2000
          min-idle: 1
          time-between-eviction-runs-millis: 60000
          min-evictable-idle-time-millis: 300000
          validation-query: select 1
          test-while-idle: true
          test-on-borrow: false
          test-on-return: false
          web-stat-filter:
            enabled: true
            url-pattern: "/*"
            exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"
            session-stat-max-count: 1000
            session-stat-enable: true
            profile-enable: true
          stat-view-servlet:
            enabled: true
            url-pattern: "/druid/*"
            login-username: root
            login-password: root
            allow: 127.0.0.1
            reset-enable: true
    
    
    
        type: com.alibaba.druid.pool.DruidDataSource
        url:
        username: admin
        password: 123456
        driver-class-name:
        filters: stat,wall,slf4j
        maxActive: 20
      jpa:
        database: postgresql
        show-sql: true
        properties:
          hibernate:
            temp:
              use_jdbc_metadata_defaults: false

测试添加的相关类

package com.junlin.druid.Entity;
    
    import javax.persistence.*;
    import java.io.Serializable;
    
    @Entity
    @Table(name="newtable")
    public class DemoEntity implements Serializable {
    
        @Id
        @GeneratedValue
        @Column(name="id")
        private int id;
    
        @Column(name="name")
        private String name;
    
        public int getId() {
            return id;
        }
    
        public void setId(int id) {
            this.id = id;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    }

package com.junlin.druid.Dao;
    
    import com.junlin.druid.Entity.DemoEntity;
    import org.springframework.data.jpa.repository.JpaRepository;
    
    import java.util.List;
    
    public interface DemoJPA  extends JpaRepository<DemoEntity,Integer> {
    
        List<DemoEntity> findByName(String name);
    }

package com.junlin.druid.Controller;
    
    import com.junlin.druid.Dao.DemoJPA;
    import com.junlin.druid.Entity.DemoEntity;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import java.util.List;
    
    
    @RestController
    public class DemoController {
    
    
        @Autowired
        private DemoJPA demoJPA;
    
        @GetMapping("/findName")
        public Object findEntity(String name){
            List<DemoEntity> list = demoJPA.findByName(name);
            return list;
        }
    }

启动项目，访问localhost:8080/druid/,进入登陆页面输入在application.yml配置好的用户和密码(demo中都是root)，即可看到监控页面

![1105132-20190819181310270-1131755312.png][]

访问一下我们准备好的接口，执行几次sql查询，在监控页面点看sql监控页面，就能看到我们的sql执行情况

![1105132-20190819181445824-974904395.png][]

其它相关的配置以及页面相关内容可以参考druid官网文档，地址[https://github.com/alibaba/druid/wiki/常见问题][https_github.com_alibaba_druid_wiki]

转载于:https://www.cnblogs.com/hhhshct/p/11378770.html

[1105132-20190819181310270-1131755312.png]: /images/20230809/ab1938d6b1a34f709b9fc0774ce06889.png
[1105132-20190819181445824-974904395.png]: /images/20230809/f88d2be2085446d2bbd2d80c05ae621a.png
[https_github.com_alibaba_druid_wiki]: https://github.com/alibaba/druid/wiki/%E5%B8%B8%E8%A7%81%E9%97%AE%E9%A2%98