在SpringBoot中使用Druid数据库连接池

Dear 丶 2021-09-21 18:54 354阅读 0赞

### 目录 ###

*  导入依赖
 *  在 application.yml 编写配置信息
 *  编写配置类
 *  测试访问

# 导入依赖 #

<dependency>
    	<groupId>com.alibaba</groupId>
    	<artifactId>druid</artifactId>
    	<version>1.1.21</version>
    </dependency>
    
    <dependency>
    	<groupId>log4j</groupId>
    	<artifactId>log4j</artifactId>
    	<version>1.2.17</version>
    </dependency>

# 在 application.yml 编写配置信息 #

spring:
      datasource:
        username: root
        password: 123456
        #?serverTimezone=UTC解决时区的报错
        url: jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
        # mysql5 的驱动是 com.mysql.jdbc.Driver, mysql8的驱动是 com.mysql.cj.jdbc.Driver
        driver-class-name: com.mysql.cj.jdbc.Driver
        # 自定义数据源
        type: com.alibaba.druid.pool.DruidDataSource
    
        #Spring Boot 默认是不注入这些属性值的，需要自己绑定
        #druid 数据源专有配置
        initialSize: 5
        minIdle: 5
        maxActive: 20
        maxWait: 60000
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 300000
        validationQuery: SELECT 1 FROM DUAL
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        poolPreparedStatements: true
    
        #配置监控统计拦截的filters，stat:监控统计、log4j：日志记录、wall：防御sql注入
        #如果允许时报错 java.lang.ClassNotFoundException: org.apache.log4j.Priority
        #则导入 log4j 依赖即可，Maven 地址：https://mvnrepository.com/artifact/log4j/log4j
        filters: stat,wall,log4j
        maxPoolPreparedStatementPerConnectionSize: 20
        useGlobalDataSourceStat: true
        connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

在测试类中查看一下是否能获取到Druid

@SpringBootTest
    class Springboot02DataApplicationTests { 
    	@Autowired
    	DataSource dataSource;
    	
    	@Test
    	void contextLoads() throws SQLException { 
    		// 看一下默认数据源
    		System.out.println("getClass ---> " + dataSource.getClass());
    		Connection connection = dataSource.getConnection();
    		
    		// 将数据源强转为 druid 数据源
    		DruidDataSource druidDataSource = (DruidDataSource) dataSource;
    		// 查看连接
    		System.out.println("connection ---> " + connection);
    		
    		// 查看druid配置
    		System.out.println("最大连接数 ---> " + druidDataSource.getMaxActive());
    		System.out.println("初始连接数 ---> " + druidDataSource.getInitialSize());
    		
    		// 关闭连接
    		connection.close();
    	}
    }

可以看到已经能获取到了  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDk1MzIyNw_size_16_color_FFFFFF_t_70]

# 编写配置类 #

`ServletRegistrationBean` 配置后台访问的账号密码等信息, `FilterRegistrationBean` 配置过滤器

package com.pro.config;
    
    import com.alibaba.druid.pool.DruidDataSource;
    import com.alibaba.druid.support.http.StatViewServlet;
    import com.alibaba.druid.support.http.WebStatFilter;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.boot.web.servlet.FilterRegistrationBean;
    import org.springframework.boot.web.servlet.ServletRegistrationBean;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    
    import javax.sql.DataSource;
    import java.util.HashMap;
    import java.util.Map;
    
    @Configuration
    public class DruidConfig { 
        // @ConfigurationProperties 作用是将 application 配置文件中 datasource 的所有配置注入到 DruidDataSource 同名参数参数中
        @ConfigurationProperties(prefix = "spring.datasource")
        @Bean
        public DataSource druidDataSource() { 
            return new DruidDataSource();
        }
    
        // 配置Druid数据源监控后台参数
        // SpringBoot 内置了 Servlet 容器, 没有 web.xml, 使用方法代替: ServletRegistrationBean
        @Bean
        public ServletRegistrationBean servletRegistrationBean() { 
            ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
    
            // 设置后台登录的账号密码
            Map<String, Object> hashMap = new HashMap<>();
    
            // 账号密码配置的key是固定的 loginUsername, loginPassword
            hashMap.put("loginUsername", "admin");
            hashMap.put("loginPassword", "admin");
    
            // 设置允许谁可以访问 localhost 本机, "" 所有人
            hashMap.put("allow", "");
    
            // 禁止谁访问
            // hashMap.put("test", "192.168.13.126"); // 禁止此ip访问
    
            // 初始化参数
            bean.setInitParameters(hashMap);
    
            return bean;
        }
    
        // 配置 Druid web 监控 filter 过滤器
        @Bean
        public FilterRegistrationBean filterRegistrationBean() { 
            FilterRegistrationBean bean = new FilterRegistrationBean();
            bean.setFilter(new WebStatFilter());
    
            // exclusions: 设置过滤哪些请求, 不进行统计
            Map<String, Object> hashMap = new HashMap<>();
            hashMap.put("exclusions", "*.js,*.css,/druid/*");
    
            // 初始化参数
            bean.setInitParameters(hashMap);
    
            return bean;
        }
    }

# 测试访问 #

启动程序测试直接访问 `http://localhost:8080/druid` 会跳到登录页登录成功之后就可以查看了  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDk1MzIyNw_size_16_color_FFFFFF_t_70 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDk1MzIyNw_size_16_color_FFFFFF_t_70]: /images/20210920/cb3489c76a1b4dfaba8af81fcb7d241d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDk1MzIyNw_size_16_color_FFFFFF_t_70 1]: /images/20210920/09fdc01e5ba04f768278b7fce5b305f1.png