【笔记】整合Druid数据源 Druid监控以及属性绑定配置在浏览器输入端口进入监控页面查看

古城微笑少年丶 2023-01-22 14:49 4阅读 0赞

整合Druid数据源  
步骤：

## 1.导入依赖 ##

<dependency>
    		   <groupId>com.alibaba</groupId>
    		   <artifactId>druid-spring-boot-starter</artifactId>
    		   <version>1.1.10</version>
    		</dependency>

1.yml配置

server:
      port: 8001
    spring:
      datasource:
        name: druidDataSource
        type: com.alibaba.druid.pool.DruidDataSource
        druid:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://localhost:3308/new?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8
          username: he
          password: 123456
          filters: stat,wall,log4j,config
          max-active: 100
          initial-size: 1
          max-wait: 60000
          min-idle: 1
          time-between-eviction-runs-millis: 60000
          min-evictable-idle-time-millis: 300000
          validation-query: select 'x'
          test-while-idle: true
          test-on-borrow: false
          test-on-return: false
          pool-prepared-statements: true
          max-open-prepared-statements: 50
          max-pool-prepared-statement-per-connection-size: 20

注意： type: com.alibaba.druid.pool.DruidDataSource  
username、password属性和DatasourceProperties中的属性进行绑定了，但是initialSize、minIdle等属性没法与DatasourceProperties中的属性绑定，它需要和DruidDataSource进行绑定。

## 自定义一个Druid数据源来进行绑定： ##

@Configuration
    public class Config { 
    // 把配置文件中spring.datasource.initialSize等属性和DruidDataSource中属
    性进行绑定
    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DruidDataSource druidDataSource () { 
    return new DruidDataSource();
    }
    }

配置Druid监控、过滤器

@Configuration
    public class DruidConfig { 
    /** * spring.datasource中的配置和DruidDataSource中的属性绑定 * @return */
    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DruidDataSource druidDataSource () { 
    return new DruidDataSource();
    }
    /** * 配置Druid的监控 * 1、配置一个管理后台的Servlet，拦截登陆 * @return */
    @Bean
    public ServletRegistrationBean statViewServlet(){ 
    ServletRegistrationBean servletRegistrationBean = new
    ServletRegistrationBean(new StatViewServlet(),"/druid/*");
    Map<String,String> initParams = new HashMap<>();
    initParams.put("loginUsername","admin");
    initParams.put("loginPassword","123456");
    initParams.put("allow",""); // 允许所有访问
    servletRegistrationBean.setInitParameters(initParams);
    return servletRegistrationBean;
    }
    // 配置一个web监控的filter，哪些请求会被监控，哪些排除。
    @Bean
    public FilterRegistrationBean webStatFilter() { 
    FilterRegistrationBean bean = new FilterRegistrationBean(new
    WebStatFilter());
    Map<String,String> initParams = new HashMap<>();
    initParams.put("exclusions","*.js,*.css,/druid/*");
    bean.setInitParameters(initParams);
    bean.setUrlPatterns(Arrays.asList("/*"));
    return bean;
    }
    }

DruidDataSourceProperties :

import org.springframework.boot.context.properties.ConfigurationProperties;
    
    
    @ConfigurationProperties(prefix = "spring.datasource.druid")
    public class DruidDataSourceProperties { 
    
    	// jdbc
    	private String driverClassName;
    	private String url;
    	private String username;
    	private String password;
    	// jdbc connection pool
    	private int initialSize;
    	private int minIdle;
    	private int maxActive = 100;
    	private long maxWait;
    	private long timeBetweenEvictionRunsMillis;
    	private long minEvictableIdleTimeMillis;
    	private String validationQuery;
    	private boolean testWhileIdle;
    	private boolean testOnBorrow;
    	private boolean testOnReturn;
    	private boolean poolPreparedStatements;
    	private int maxPoolPreparedStatementPerConnectionSize;
    	// filter
    	private String filters;
    
    	public int getInitialSize() { 
    		return initialSize;
    	}
    
    	public void setInitialSize(int initialSize) { 
    		this.initialSize = initialSize;
    	}
    
    	public int getMinIdle() { 
    		return minIdle;
    	}
    
    	public void setMinIdle(int minIdle) { 
    		this.minIdle = minIdle;
    	}
    
    	public int getMaxActive() { 
    		return maxActive;
    	}
    
    	public void setMaxActive(int maxActive) { 
    		this.maxActive = maxActive;
    	}
    
    	public long getMaxWait() { 
    		return maxWait;
    	}
    
    	public void setMaxWait(long maxWait) { 
    		this.maxWait = maxWait;
    	}
    
    	public long getTimeBetweenEvictionRunsMillis() { 
    		return timeBetweenEvictionRunsMillis;
    	}
    
    	public void setTimeBetweenEvictionRunsMillis(long timeBetweenEvictionRunsMillis) { 
    		this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
    	}
    
    	public long getMinEvictableIdleTimeMillis() { 
    		return minEvictableIdleTimeMillis;
    	}
    
    	public void setMinEvictableIdleTimeMillis(long minEvictableIdleTimeMillis) { 
    		this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
    	}
    
    	public String getValidationQuery() { 
    		return validationQuery;
    	}
    
    	public void setValidationQuery(String validationQuery) { 
    		this.validationQuery = validationQuery;
    	}
    
    	public boolean isTestWhileIdle() { 
    		return testWhileIdle;
    	}
    
    	public void setTestWhileIdle(boolean testWhileIdle) { 
    		this.testWhileIdle = testWhileIdle;
    	}
    
    	public boolean isTestOnBorrow() { 
    		return testOnBorrow;
    	}
    
    	public void setTestOnBorrow(boolean testOnBorrow) { 
    		this.testOnBorrow = testOnBorrow;
    	}
    
    	public boolean isTestOnReturn() { 
    		return testOnReturn;
    	}
    
    	public void setTestOnReturn(boolean testOnReturn) { 
    		this.testOnReturn = testOnReturn;
    	}
    
    	public boolean isPoolPreparedStatements() { 
    		return poolPreparedStatements;
    	}
    
    	public void setPoolPreparedStatements(boolean poolPreparedStatements) { 
    		this.poolPreparedStatements = poolPreparedStatements;
    	}
    
    	public int getMaxPoolPreparedStatementPerConnectionSize() { 
    		return maxPoolPreparedStatementPerConnectionSize;
    	}
    
    	public void setMaxPoolPreparedStatementPerConnectionSize(int maxPoolPreparedStatementPerConnectionSize) { 
    		this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;
    	}
    
    	public String getFilters() { 
    		return filters;
    	}
    
    	public void setFilters(String filters) { 
    		this.filters = filters;
    	}
    
    	public String getDriverClassName() { 
    		return driverClassName;
    	}
    
    	public void setDriverClassName(String driverClassName) { 
    		this.driverClassName = driverClassName;
    	}
    
    	public String getUrl() { 
    		return url;
    	}
    
    	public void setUrl(String url) { 
    		this.url = url;
    	}
    
    	public String getUsername() { 
    		return username;
    	}
    
    	public void setUsername(String username) { 
    		this.username = username;
    	}
    
    	public String getPassword() { 
    		return password;
    	}
    
    	public void setPassword(String password) { 
    		this.password = password;
    	}
    
    }

如果以注解的方式：  
DataSourceConfig.java：

@Configuration
    @MapperScan(basePackages = MasterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory")
    public class MasterDataSourceConfig { 
    static final String PACKAGE = "com.pancm.dao.master";
    static final String MAPPER_LOCATION = "classpath:mapper/master/*.xml";
    @Value("${master.datasource.url}")
    private String url;
    @Value("${master.datasource.username}")
    private String username;
    @Value("${master.datasource.password}")
    private String password;
    @Value("${master.datasource.driverClassName}")
    private String driverClassName;
    @Value("${spring.datasource.initialSize}")
    private int initialSize;
    @Value("${spring.datasource.minIdle}")
    private int minIdle;
    @Value("${spring.datasource.maxActive}")
    private int maxActive;
    @Value("${spring.datasource.maxWait}")
    private int maxWait;
    @Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
    private int timeBetweenEvictionRunsMillis;
    @Value("${spring.datasource.minEvictableIdleTimeMillis}")
    private int minEvictableIdleTimeMillis;
    @Value("${spring.datasource.validationQuery}")
    private String validationQuery;
    @Value("${spring.datasource.testWhileIdle}")
    private boolean testWhileIdle;
    @Value("${spring.datasource.testOnBorrow}")
    private boolean testOnBorrow;
    @Value("${spring.datasource.testOnReturn}")
    private boolean testOnReturn;
    @Value("${spring.datasource.poolPreparedStatements}")
    private boolean poolPreparedStatements;
    @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
    private int maxPoolPreparedStatementPerConnectionSize;
    @Value("${spring.datasource.filters}")
    private String filters;
    @Value("{spring.datasource.connectionProperties}")
    private String connectionProperties;
    @Bean(name = "masterDataSource")
    @Primary
    public DataSource masterDataSource() { 
    DruidDataSource dataSource = new DruidDataSource();
    dataSource.setUrl(url);
    dataSource.setUsername(username);
    dataSource.setUsername(username);
    dataSource.setPassword(password);
    dataSource.setDriverClassName(driverClassName);
    //具体配置
    dataSource.setInitialSize(initialSize);
    dataSource.setMinIdle(minIdle);
    dataSource.setMaxActive(maxActive);
    dataSource.setMaxWait(maxWait);
    dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
    dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
    dataSource.setValidationQuery(validationQuery);
    dataSource.setTestWhileIdle(testWhileIdle);
    dataSource.setTestOnBorrow(testOnBorrow);
    dataSource.setTestOnReturn(testOnReturn);
    dataSource.setPoolPreparedStatements(poolPreparedStatements);
    dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
    try { 
    dataSource.setFilters(filters);
    } catch (SQLException e) { 
    e.printStackTrace();
    }
    dataSource.setConnectionProperties(connectionProperties);
    return dataSource;
    }
    @Bean(name = "masterTransactionManager")
    @Primary
    public DataSourceTransactionManager masterTransactionManager() { 
    return new DataSourceTransactionManager(masterDataSource());
    }
    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource)
    throws Exception { 
    final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
    sessionFactory.setDataSource(masterDataSource);
    sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
    .getResources(MasterDataSourceConfig.MAPPER_LOCATION));
    return sessionFactory.getObject();
    }
    }

@Primary ：标志这个 Bean 如果在多个同类 Bean 候选时，该 Bean优先被考虑。多数据源配置的时候注意，必须要有⼀个主数据源，⽤ @Primary 标志该 Bean。  
@MapperScan： 扫描 Mapper 接⼝并容器管理。

DruidConfig:  
在SpringBoot中运⽤Druid的监控作⽤，只需要编写StatViewServlet和WebStatFilter类，**实现注册服务和过滤规则。**  
这⾥我们可以将这两个写在⼀起，使⽤@Configuration和@Bean。

@Configuration
    @EnableConfigurationProperties({ DruidDataSourceProperties.class})
    public class DruidConfig { 
        @Autowired
        private DruidDataSourceProperties properties;
    
        @Bean
        @ConditionalOnMissingBean
        public DataSource druidDataSource() { 
            DruidDataSource druidDataSource = new DruidDataSource();
            druidDataSource.setDriverClassName(properties.getDriverClassName());
            druidDataSource.setUrl(properties.getUrl());
            druidDataSource.setUsername(properties.getUsername());
            druidDataSource.setPassword(properties.getPassword());
            druidDataSource.setInitialSize(properties.getInitialSize());
            druidDataSource.setMinIdle(properties.getMinIdle());
            druidDataSource.setMaxActive(properties.getMaxActive());
            druidDataSource.setMaxWait(properties.getMaxWait());
            druidDataSource.setTimeBetweenEvictionRunsMillis(properties.getTimeBetweenEvictionRunsMillis());
            druidDataSource.setMinEvictableIdleTimeMillis(properties.getMinEvictableIdleTimeMillis());
            druidDataSource.setValidationQuery(properties.getValidationQuery());
            druidDataSource.setTestWhileIdle(properties.isTestWhileIdle());
            druidDataSource.setTestOnBorrow(properties.isTestOnBorrow());
            druidDataSource.setTestOnReturn(properties.isTestOnReturn());
            druidDataSource.setPoolPreparedStatements(properties.isPoolPreparedStatements());
            druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(properties.getMaxPoolPreparedStatementPerConnectionSize());
    
            try { 
                druidDataSource.setFilters(properties.getFilters());
                druidDataSource.init();
            } catch (SQLException e) { 
                e.printStackTrace();
            }
    
            return druidDataSource;
        }
    
        /** * 注册Servlet信息， 配置监控视图 * * @return */
        @Bean
        @ConditionalOnMissingBean
        public ServletRegistrationBean<Servlet> druidServlet() { 
            ServletRegistrationBean<Servlet> servletRegistrationBean = new ServletRegistrationBean<Servlet>(new StatViewServlet(), "/druid/*");
    
            //白名单：
            servletRegistrationBean.addInitParameter("allow","127.0.0.1,139.196.87.48");
            //IP黑名单 (存在共同时，deny优先于allow) : 如果满足deny的话提示:Sorry, you are not permitted to view this page.
           // servletRegistrationBean.addInitParameter("deny","192.168.1.119");
            //登录查看信息的账号密码, 用于登录Druid监控后台
            servletRegistrationBean.addInitParameter("loginUsername", "admin");
            servletRegistrationBean.addInitParameter("loginPassword", "admin");
            //是否能够重置数据.
            servletRegistrationBean.addInitParameter("resetEnable", "true");
            return servletRegistrationBean;
    
        }
    
        /** * 注册Filter信息, 监控拦截器 * * @return */
        @Bean
        @ConditionalOnMissingBean
        public FilterRegistrationBean<Filter> filterRegistrationBean() { 
            FilterRegistrationBean<Filter> filterRegistrationBean = new FilterRegistrationBean<Filter>();
            filterRegistrationBean.setFilter(new WebStatFilter());
            // 添加过滤规则
            filterRegistrationBean.addUrlPatterns("/*");
            // 添加不需要忽略的格式信息
            filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
            return filterRegistrationBean;
        }
    }

servletRegistrationBean.addInitParameter(“loginUsername”, “admin”);  
servletRegistrationBean.addInitParameter(“loginPassword”, “admin”);  
为账号密码  
配置完之后，就可以将Druid作为连接池使⽤了。但是Druid并不简简单单的是个连接池，**它也可以说是⼀个监控应⽤，它⾃带了web监控界⾯，可以很清晰的看到SQL相关信息。**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMzU4NTc0_size_16_color_FFFFFF_t_70]  
此页面输入账号密码  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMzU4NTc0_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMzU4NTc0_size_16_color_FFFFFF_t_70 2]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMzU4NTc0_size_16_color_FFFFFF_t_70]: /images/20221020/99a5851d20024b59a99c1743fc584a4b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMzU4NTc0_size_16_color_FFFFFF_t_70 1]: /images/20221020/c39a2d07658740f29f062c4b30939b83.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMzU4NTc0_size_16_color_FFFFFF_t_70 2]: /images/20221020/960726bd0ecd404a9eb772a1babb71ed.png