Druid在Spring和SpringBoot项目中的使用

ゝ一纸荒年。 2021-12-23 06:23 256阅读 0赞

# 是什么？ #

数据库连接池的一种，管理和释放数据库的连接，实现连接的重复利用，可以对比线程池。

## 参数配置 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25pbmdtZW5nYmFieQ_size_16_color_FFFFFF_t_70][]

# 能干什么？ #

1,连接池2,监控3,加密4,扩展JDBC

# 怎么使用？ #

添加依赖：

<dependency>    
        <groupId>com.alibaba</groupId>   
        <artifactId>druid</artifactId>
    </dependency>

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

连接池：

spring形式

<bean id="dataSourceDefault" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
            <property name="url" value="${itoo_jdbc_url}"/>
            <property name="username" value="${itoo_jdbc_username}"/>
            <property name="password" value="${itoo_jdbc_password}"/>
            <property name="maxActive" value="${itoo_jdbc_maxActive}"/>
            <property name="initialSize" value="${itoo_jdbc_initialSize}"/>
            <property name="maxWait" value="${itoo_jdbc_maxWait}"/>
            <property name="minIdle" value="${itoo_jdbc_minIdle}"/>
            <property name="validationQuery" value="${itoo_jdbc_validationQuery}"/> // 用来检测连接是否有效的sql
            <property name="testWhileIdle" value="${itoo_jdbc_testWhileIdle}"/> //超时检测
            <property name="testOnBorrow" value="${itoo_jdbc_testOnBorrow}"/>
            <property name="testOnReturn" value="${itoo_jdbc_testOnReturn}"/>
            <property name="filters" value="mycat,stat,config"/>  // mycat是自定义配置的过滤器插件，接下来会说明
            <property name="connectionProperties" value="${connectionProperties}"/>
    
        </bean>

spring-boot方式：

datasource:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.p6spy.engine.spy.P6SpyDriver
        url: jdbc:p6spy:mysql://192.168.22.58:3306/aa
        username: root
        password: root
        druid:
          initial-size: 2
          min-idle: 2
          max-active: 50
          validation-query: SELECT 1
          test-while-idle: true
          aop-patterns: com.dmsdbj.integral.provider.*  // spring监控页面的配置
      # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙      
        filters: stat,wall,log4j
          stat-view-servlet:
            url-pattern: /druid/*
            reset-enable: true
            login-username: druid
            login-password: druid

监控：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25pbmdtZW5nYmFieQ_size_16_color_FFFFFF_t_70 1][]

<bean id="druid-stat-interceptor" class="com.alibaba.druid.support.spring.stat.DruidStatInterceptor"/>
        <bean id="druid-stat-pointcut" class="org.springframework.aop.support.JdkRegexpMethodPointcut" scope="prototype">
            <property name="patterns">
                <list>
                    <value>com.tfjybj.itoo.exam.provider.service.*</value>
                    <value>com.tfjybj.itoo.exam.provider.dao.*</value>
                </list>
            </property>
        </bean>
        <aop:config proxy-target-class="true">
            <aop:advisor advice-ref="druid-stat-interceptor" pointcut-ref="druid-stat-pointcut"/>
        </aop:config>
    
    
    </beans>

aop-patterns: com.dmsdbj.integral.provider.*

加密：

![20190625210714650.png][]

过滤器插件

config ，stat ，wall

自定义过滤器插件

mycat

采用的是在druid上进行改造，不在p6spy上是因为传到druid的依然是不带参数的sql，p6spy只是旁路监控。

定义一个工具类

@Configuration
    public class DruidMycatConfig {
        private  static final String FILTER_MYCAT_PREFIX = "spring.datasource.druid.filter.mycat";
    
        @Bean
        @ConfigurationProperties(FILTER_MYCAT_PREFIX)
        @ConditionalOnProperty(prefix = FILTER_MYCAT_PREFIX, name = "enabled", matchIfMissing = true)
        @ConditionalOnMissingBean
        public DruidMycatFilter mycatFilter(){
            return new DruidMycatFilter();
        }
    }

public class DruidMycatFilter extends WallFilter implements Filter {
        public DruidMycatFilter() {
            this.setDbType("mysql");
            this.configFromProperties(System.getProperties());
        }
    
        public String check(String sql) {
            String schema;
            if(TenancyContext.TenancyID.get() != null) {
                String schema = (String)TenancyContext.TenancyID.get();
                schema = "/* !mycat:schema = " + schema + " */ ";
                sql = sql.startsWith(schema)?sql:schema + sql;
            }
    }

Spring形式：

1.在web对应的resource中增加META-INF文件，在文件中添加druid-filter.properties的配置

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25pbmdtZW5nYmFieQ_size_16_color_FFFFFF_t_70 2][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25pbmdtZW5nYmFieQ_size_16_color_FFFFFF_t_70 3][]

2.在spring-db.xml中增加druid的过滤配置:<property name="filters" value="mycat,stat,config"/>

![20190625211909731.png][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25pbmdtZW5nYmFieQ_size_16_color_FFFFFF_t_70]: /images/20211223/84e2d2fab9d541e8b7bcff58cba62a1e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25pbmdtZW5nYmFieQ_size_16_color_FFFFFF_t_70 1]: /images/20211223/e160f43958524505be211cfcae1bff06.png
[20190625210714650.png]: /images/20211223/ba4f6c0c0dab438fb78c1a180d60a9b9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25pbmdtZW5nYmFieQ_size_16_color_FFFFFF_t_70 2]: /images/20211223/c17a9ccd2ba24a1ba72afde2c04c262b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25pbmdtZW5nYmFieQ_size_16_color_FFFFFF_t_70 3]: /images/20211223/ae6621cef11248238c00d803130d7c2b.png
[20190625211909731.png]: /images/20211223/fda1ec390b2541b5b75626fd4ab076b8.png