SpringBoot（九）：SpringBoot使用Druid数据源

骑猪看日落 2022-05-29 13:40 136阅读 0赞

> Druid是阿里巴巴开源平台上一个数据库连接池实现，它结合了C3P0、DBCP、PROXOOL等DB池的优点，同时加入了日志监控，可以很好的监控DB池连接和SQL的执行情况，可以说是针对监控而生的DB连接池(据说是目前最好的连接池)

### 一、依赖 ###

为了测试，使用jdbcTemplate

<dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    
    
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.0.26</version>
    </dependency>
    
    
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15
 *  16
 *  17
 *  18
 *  19

### 二、Druid配置 ###

druid.properties

#数据库设置
    spring.datasource.driverClassName=com.mysql.jdbc.Driver
    spring.datasource.url=jdbc:mysql://localhost:3306/uu_core?useUnicode=true&characterEncoding=utf8&autoReconnect=true&useSSL=true
    spring.datasource.username=root
    spring.datasource.password=root
    #--------------------------
    # 下面为连接池的补充设置，应用到上面所有数据源中
    # 初始化大小，最小，最大
    spring.datasource.initialSize=5
    spring.datasource.minIdle=1
    spring.datasource.maxActive=50
    # 配置获取连接等待超时的时间
    spring.datasource.maxWait=60000
    # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
    spring.datasource.timeBetweenEvictionRunsMillis=60000
    # 配置一个连接在池中最小生存的时间，单位是毫秒
    spring.datasource.minEvictableIdleTimeMillis=300000
    spring.datasource.validationQuery=SELECT 1 FROM DUAL
    spring.datasource.testWhileIdle=true
    spring.datasource.testOnBorrow=false
    spring.datasource.testOnReturn=false
    # 打开PSCache，并且指定每个连接上PSCache的大小
    spring.datasource.poolPreparedStatements=false
    #spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
    # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    spring.datasource.filters=stat,wall,log4j
    # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
    spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    # 合并多个DruidDataSource的监控数据
    #spring.datasource.useGlobalDataSourceStat=true

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15
 *  16
 *  17
 *  18
 *  19
 *  20
 *  21
 *  22
 *  23
 *  24
 *  25
 *  26
 *  27
 *  28
 *  29
 *  30
 *  31

### 三、实例化Druid Datasource ###

package cn.aduu.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 org.springframework.context.annotation.PropertySource;
    
    import javax.sql.DataSource;
    
    /** * @author zh * @ClassName cn.aduu.config.DruidConfiguration * @Description */
    @Configuration
    @PropertySource(value = "classpath:druid.properties")
    public class DruidConfiguration { 
    
        @Bean(destroyMethod = "close", initMethod = "init")
        @ConfigurationProperties(prefix = "spring.datasource")
        public DataSource druidDataSource() {
            DruidDataSource druidDataSource = new DruidDataSource();
            return druidDataSource;
        }
    
        /** * 注册一个StatViewServlet * @return */
        @Bean
        public ServletRegistrationBean druidStatViewServlet(){
            //org.springframework.boot.context.embedded.ServletRegistrationBean提供类的进行注册.
            ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(),"/druid/*");
    
            //添加初始化参数：initParams
            //白名单：
            servletRegistrationBean.addInitParameter("allow","127.0.0.1");
            //IP黑名单 (存在共同时，deny优先于allow) : 如果满足deny的话提示:Sorry, you are not permitted to view this page.
            servletRegistrationBean.addInitParameter("deny","192.168.1.73");
            //登录查看信息的账号密码.
            servletRegistrationBean.addInitParameter("loginUsername","admin");
            servletRegistrationBean.addInitParameter("loginPassword","123456");
            //是否能够重置数据.
            servletRegistrationBean.addInitParameter("resetEnable","false");
            return servletRegistrationBean;
        }
    
        /** * 注册一个：filterRegistrationBean * @return */
        @Bean
        public FilterRegistrationBean druidStatFilter(){
    
            FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
    
            //添加过滤规则.
            filterRegistrationBean.addUrlPatterns("/*");
    
            //添加不需要忽略的格式信息.
            filterRegistrationBean.addInitParameter("exclusions","*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
            return filterRegistrationBean;
        }
    }

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15
 *  16
 *  17
 *  18
 *  19
 *  20
 *  21
 *  22
 *  23
 *  24
 *  25
 *  26
 *  27
 *  28
 *  29
 *  30
 *  31
 *  32
 *  33
 *  34
 *  35
 *  36
 *  37
 *  38
 *  39
 *  40
 *  41
 *  42
 *  43
 *  44
 *  45
 *  46
 *  47
 *  48
 *  49
 *  50
 *  51
 *  52
 *  53
 *  54
 *  55
 *  56
 *  57
 *  58
 *  59
 *  60
 *  61
 *  62
 *  63
 *  64
 *  65
 *  66
 *  67
 *  68
 *  69
 *  70

### 四、监控 ###

访问 [http://localhost:8080/druid][http_localhost_8080_druid]， 使用上面配置的账号密码。

![这里写图片描述][SouthEast]

![这里写图片描述][SouthEast 1]

### 五、测试 ###

@RestController
    public class HelloController{ 
    
        private static final Logger logger = LoggerFactory.getLogger(HelloController.class);
    
        @Autowired
        private JdbcTemplate jdbcTemplate;
    
        @RequestMapping("hello")
        public List<Map<String, Object>> hello() {
            List<Map<String, Object>> list = jdbcTemplate.queryForList("SELECT user,password FROM mysql.user ", new Object[]{});
            return list;
        }
    }

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14

访问localhost:8080/hello

[
        {
            "user": "root",
            "password": "*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B" },
        {
            "user": "root",
            "password": "*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B" }
    ]

[http_localhost_8080_druid]: http://localhost:8080/druid
[SouthEast]: /images/20220529/a9f1d562350e4285a9e7cca005cffb7c.png
[SouthEast 1]: /images/20220529/8ac642c4b15245a49b954be4a472ff0d.png