【spring boot】15.spring boot项目采用Druid数据库连接池,并启用druid监控功能

2021-12-16 12:13:51

在http://www.cnblogs.com/sxdcgaq8080/p/9039442.html的基础上，来看看spring boot项目中采用Druid连接池。

============================================================================

注：使用Druid连接池与否和使用JPA查询还是使用Mybatis查询没有任何关系。

注：采用Druid连接池只需要配置文件中相关配置+一个配置类覆盖spring boot默认的DataSource+几个jar包即可

============================================================================

Spring Boot的数据源配置默认类型是org.apache.tomcat.jdbc.pool.DataSource，为了使用Druid连接池，可以将数据源类型更换为com.alibaba.druid.pool.DruidDataSource

图片来源： https://blog.csdn.net/awj321000/article/details/52169686

============================================================================

1.首先添加druid的jar包

<!-- https://mvnrepository.com/artifact/com.alibaba/druid  数据库连接池-->

        <dependency>

            <groupId>com.alibaba</groupId>

            <artifactId>druid</artifactId>

            <version>1.1.9</version>

        </dependency>

2.接下来，application.properties中添加配置【其中有一部分是原本就需要配置的连接数据库的】

#datasource

spring.datasource.continue-on-error=false

spring.datasource.url=jdbc:mysql://localhost:3306/swapping?useSSL=false&useUnicode=true&characterEncoding=UTF-8

spring.datasource.username=root

spring.datasource.password=root

spring.datasource.driver-class-name=com.mysql.jdbc.Driver

#druid  下面为连接池的补充设置，应用到上面所有数据源中

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

# 初始化大小，最小，最大

spring.datasource.initialSize=5

spring.datasource.minIdle=5

spring.datasource.maxActive=20

# 配置获取连接等待超时的时间

spring.datasource.maxWait=60000

# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒

spring.datasource.timeBetweenEvictionRunsMillis=60000

# 配置一个连接在池中最小生存的时间，单位是毫秒

spring.datasource.minEvictableIdleTimeMillis=300000

spring.datasource.validationQuery=SELECT 'x'

spring.datasource.testWhileIdle=true

spring.datasource.testOnBorrow=false

spring.datasource.testOnReturn=false

# 打开PSCache，并且指定每个连接上PSCache的大小

spring.datasource.poolPreparedStatements=true

spring.datasource.maxPoolPreparedStatementPerConnectionSize=20

spring.datasource.maxOpenPreparedStatements=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

3.自己写一个配置类，用于覆盖spring boot 默认的DataSource

package com.sxd.swapping.config;

import com.alibaba.druid.pool.DruidDataSource;

import com.sxd.swapping.base.UniVerResponse;

import com.sxd.swapping.util.MyException;

import org.springframework.beans.factory.annotation.Value;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.Configuration;

import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;

import java.sql.SQLException;

/**

 * 使用阿里的Druid数据库连接池

 * 唯一需要提供的配置类

 *

 * 作用：覆盖默认的DataSource

 */

@Configuration

public class DruidDBConfig {

    @Value("${spring.datasource.url}")

    private String dbUrl;

    @Value("${spring.datasource.username}")

    private String username;

    @Value("${spring.datasource.password}")

    private String password;

    @Value("${spring.datasource.driver-class-name}")

    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     //声明其为Bean实例

    @Primary  //在同样的DataSource中，首先使用被标注的DataSource

    public DataSource dataSource(){

        DruidDataSource datasource = new DruidDataSource();

        datasource.setUrl(this.dbUrl);

        datasource.setUsername(username);

        datasource.setPassword(password);

        datasource.setDriverClassName(driverClassName);

        //configuration

        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) {

            new MyException("druid阿里数据库连接池 初始化失败", UniVerResponse.ERROR_SYS_EXCPTION,e);

        }

        datasource.setConnectionProperties(connectionProperties);

        return datasource;

    }

}

4.启动启动类，报错了

2018-05-18 16:39:43.161 ERROR 6708 --- [  restartedMain] o.s.boot.SpringApplication               : Application run failed

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaConfiguration': Unsatisfied dependency expressed through constructor parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in class path resource [com/sxd/swapping/config/DruidDBConfig.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [javax.sql.DataSource]: Factory method 'dataSource' threw exception; nested exception is java.lang.NoClassDefFoundError: org/apache/log4j/Logger

    at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:729) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:192) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireConstructor(AbstractAutowireCapableBeanFactory.java:1270) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1127) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:541) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:501) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:317) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:315) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:368) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1250) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1099) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:541) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:501) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:317) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:315) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1089) ~[spring-context-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:859) ~[spring-context-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:550) ~[spring-context-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:140) ~[spring-boot-2.0.1.RELEASE.jar:2.0.1.RELEASE]

    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:759) [spring-boot-2.0.1.RELEASE.jar:2.0.1.RELEASE]

    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:395) [spring-boot-2.0.1.RELEASE.jar:2.0.1.RELEASE]

    at org.springframework.boot.SpringApplication.run(SpringApplication.java:327) [spring-boot-2.0.1.RELEASE.jar:2.0.1.RELEASE]

    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1255) [spring-boot-2.0.1.RELEASE.jar:2.0.1.RELEASE]

    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1243) [spring-boot-2.0.1.RELEASE.jar:2.0.1.RELEASE]

    at com.sxd.swapping.SwappingApplication.main(SwappingApplication.java:12) [classes/:na]

    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_171]

    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_171]

    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_171]

    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_171]

    at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49) [spring-boot-devtools-2.0.1.RELEASE.jar:2.0.1.RELEASE]

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSource' defined in class path resource [com/sxd/swapping/config/DruidDBConfig.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [javax.sql.DataSource]: Factory method 'dataSource' threw exception; nested exception is java.lang.NoClassDefFoundError: org/apache/log4j/Logger

    at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:587) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1250) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1099) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:541) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:501) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:317) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:315) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:251) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1138) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1065) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:815) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:721) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    ... 33 common frames omitted

Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [javax.sql.DataSource]: Factory method 'dataSource' threw exception; nested exception is java.lang.NoClassDefFoundError: org/apache/log4j/Logger

    at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:185) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:579) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    ... 46 common frames omitted

Caused by: java.lang.NoClassDefFoundError: org/apache/log4j/Logger

    at com.alibaba.druid.filter.logging.Log4jFilter.<init>(Log4jFilter.java:26) ~[druid-1.1.9.jar:1.1.9]

    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_171]

    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_171]

    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_171]

    at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_171]

    at java.lang.Class.newInstance(Class.java:442) ~[na:1.8.0_171]

    at com.alibaba.druid.filter.FilterManager.loadFilter(FilterManager.java:114) ~[druid-1.1.9.jar:1.1.9]

    at com.alibaba.druid.pool.DruidAbstractDataSource.addFilters(DruidAbstractDataSource.java:1286) ~[druid-1.1.9.jar:1.1.9]

    at com.alibaba.druid.pool.DruidAbstractDataSource.setFilters(DruidAbstractDataSource.java:1275) ~[druid-1.1.9.jar:1.1.9]

    at com.sxd.swapping.config.DruidDBConfig.dataSource(DruidDBConfig.java:101) ~[classes/:na]

    at com.sxd.swapping.config.DruidDBConfig$$EnhancerBySpringCGLIB$$7c40a88c.CGLIB$dataSource$0(<generated>) ~[classes/:na]

    at com.sxd.swapping.config.DruidDBConfig$$EnhancerBySpringCGLIB$$7c40a88c$$FastClassBySpringCGLIB$$315a9d60.invoke(<generated>) ~[classes/:na]

    at org.springframework.cglib.proxy.MethodProxy.invokeSuper(MethodProxy.java:228) ~[spring-core-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at org.springframework.context.annotation.ConfigurationClassEnhancer$BeanMethodInterceptor.intercept(ConfigurationClassEnhancer.java:361) ~[spring-context-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    at com.sxd.swapping.config.DruidDBConfig$$EnhancerBySpringCGLIB$$7c40a88c.dataSource(<generated>) ~[classes/:na]

    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_171]

    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_171]

    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_171]

    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_171]

    at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:154) ~[spring-beans-5.0.5.RELEASE.jar:5.0.5.RELEASE]

    ... 47 common frames omitted

Caused by: java.lang.ClassNotFoundException: org.apache.log4j.Logger

    at java.net.URLClassLoader.findClass(URLClassLoader.java:381) ~[na:1.8.0_171]

    at java.lang.ClassLoader.loadClass(ClassLoader.java:424) ~[na:1.8.0_171]

    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:349) ~[na:1.8.0_171]

    at java.lang.ClassLoader.loadClass(ClassLoader.java:357) ~[na:1.8.0_171]

    ... 67 common frames omitted

5.添加log4j的jar包引用

<!-- https://mvnrepository.com/artifact/log4j/log4j -->

        <dependency>

            <groupId>log4j</groupId>

            <artifactId>log4j</artifactId>

            <version>1.2.</version>

        </dependency>

这里可能会出现一个问题：http://www.cnblogs.com/sxdcgaq8080/p/9065796.html

6.重新启动

可以看到 druid连接池已经连接成功了。

然后通过spring data JPA进行查询，打印结果如下：

DEBUG - {conn-} pool-connect

DEBUG - {conn-} setAutoCommit false

Hibernate: select huayangare0_.id as id1_0_, huayangare0_.create_date as create_d2_0_, huayangare0_.create_id as create_i3_0_, huayangare0_.uid as uid4_0_, huayangare0_.update_date as update_d5_0_, huayangare0_.update_id as update_i6_0_, huayangare0_.area_name as area_nam7_0_, huayangare0_.area_person as area_per8_0_ from hua_yang_area huayangare0_ where huayangare0_.area_name like ?

DEBUG - {conn-, pstmt-} Parameters : [%华阳%]

DEBUG - {conn-, pstmt-} Types : [VARCHAR]

DEBUG - {conn-, pstmt-, rs-} query executed. 1.052502 millis. select huayangare0_.id as id1_0_, huayangare0_.create_date as create_d2_0_, huayangare0_.create_id as create_i3_0_, huayangare0_.uid as uid4_0_, huayangare0_.update_date as update_d5_0_, huayangare0_.update_id as update_i6_0_, huayangare0_.area_name as area_nam7_0_, huayangare0_.area_person as area_per8_0_ from hua_yang_area huayangare0_ where huayangare0_.area_name like ?

DEBUG - {conn-, pstmt-, rs-} open

DEBUG - {conn-, pstmt-, rs-} Header: [id, create_date, create_id, uid, update_date, update_id, area_name, area_person]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, 7cbfc7a7-db06--aaf9-38a461c90eea, 27b0debd-c2fc--99c7-ec1ed11ca46f, -- ::50.0, 39b3cade-5f5f-4a9f-961c-3f33615983fc, 华阳汽车站, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, 6e10dc22-5b87-43e2-8f44-c6f07ed68798, 09d0a84d-feac-4f27-91f6-2ce35780bb76, -- ::18.0, a9b51196-c3e1-4f0f-8b64-5229fa00747e, 华阳车站, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, 39631b5b--4e5f-8ed3-f5ad45710262, 23bc1a9f-2e6d--8c10-abd3ecbd6f12, -- ::48.0, null, 华阳, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, cf9c4f76-25aa--bfd0-eabbcc7f49fd, d18cd6ed-bd7d-4f03-82bf-05227e455f39, -- ::48.0, null, 华阳, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, 709fd7b9----87ccb98bf7f9, b353fe1e-93b1-4ba9-8dc9-dfd2cb59ecff, -- ::48.0, null, 华阳, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, 5b3dd786-e124-41e5-a8db-4a7b695145f5, e0e76526-e1a5-40cd-8e42-7e9588f4ca7b, -- ::48.0, null, 华阳, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, 0c672acd-5ac2-4f0c--3258b3991f97, 77712ec8-85dd-4a0c--b5c09e41dfbc, -- ::48.0, null, 华阳, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, 568c06de-f688-4d93-a215-1dc1fe4ce8b5, d02a4912-cde1-4a01-8c78-b0038926929f, -- ::48.0, null, 华阳, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, 387bd87f-46bd-4f54-b4bf-de41f07a6e02, fd328b55-bd72-49ab-aecc-13e7f850d7e5, -- ::48.0, null, 华阳, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, 3d45cdef-56cf--9f3a-7776c0aca4a4, fd4744fc-b30e-434e--1f147cc3995a, -- ::48.0, null, 华阳, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, 00bdb5eb-c717--821e-703c3f3fd1e4, 8acd09d7--468f-9bce-54fffb3d4768, -- ::48.0, null, 华阳, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, 2e5ea2a6-293c-45cb-b929-aa7aab898381, f4729928-0a23-46bd-aa55-868079fd1fcb, -- ::48.0, null, 华阳, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, d3cea945-672c-4a97-a65c-7c85d598c800, bb3f262c-b3a8--8e03-85b8d9c03c70, -- ::48.0, null, 华阳, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, b3fed9e0-c548-4a36-afa9-003fb0afc28d, 09fba2ab-575c-44ba-8e99-5f34a724cce4, -- ::48.0, null, 华阳, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, ee602561-59d7-448f-93af-de4d660bcc00, c9f4d213-774a-40fb-9e46-21c7eacf14c6, -- ::48.0, null, 华阳, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::38.0, f3572e33-2a2e--8f64-0173b06c0eb0, 78d51f00-d81b--90e7-020682084bf7, null, null, 华阳站牌, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::51.0, 46d7bef8-86b6-4dc8-9adb-825c3e3dc01e, b4d709da--407b--b529b437d44c, null, null, 华阳站牌, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::17.0, 42c19e53-1f0a--b1af-c8b9a1f38419, 68e42c79--495c-952f-d83007cf6775, null, null, 华阳站牌, ]

DEBUG - {conn-, pstmt-, rs-} closed

DEBUG - {conn-, pstmt-} clearParameters.

DEBUG - {conn-} commited

DEBUG - {conn-} setAutoCommit true

DEBUG - {conn-} pool-recycle

通过mybatis查询，查询结果如下：

DEBUG - {conn-} pool-connect

-- ::56.869 DEBUG  --- [nio--exec-] c.s.s.d.m.H.selectByNameLike             : ==>  Preparing: SELECT * FROM hua_yang_area WHERE area_name like '%华阳%'

DEBUG - {conn-, pstmt-} created. SELECT * FROM hua_yang_area WHERE area_name like '%华阳%'

-- ::56.870 DEBUG  --- [nio--exec-] c.s.s.d.m.H.selectByNameLike             : ==> Parameters:

DEBUG - {conn-, pstmt-} Parameters : []

DEBUG - {conn-, pstmt-} Types : []

DEBUG - {conn-, pstmt-} executed. 0.573573 millis. SELECT * FROM hua_yang_area WHERE area_name like '%华阳%'

DEBUG - {conn-, pstmt-, rs-} open

DEBUG - {conn-, pstmt-, rs-} Header: [id, create_date, create_id, uid, update_date, update_id, area_name, area_person]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, 7cbfc7a7-db06--aaf9-38a461c90eea, 27b0debd-c2fc--99c7-ec1ed11ca46f, -- ::50.0, 39b3cade-5f5f-4a9f-961c-3f33615983fc, 华阳汽车站, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, 6e10dc22-5b87-43e2-8f44-c6f07ed68798, 09d0a84d-feac-4f27-91f6-2ce35780bb76, -- ::18.0, a9b51196-c3e1-4f0f-8b64-5229fa00747e, 华阳车站, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, 39631b5b--4e5f-8ed3-f5ad45710262, 23bc1a9f-2e6d--8c10-abd3ecbd6f12, -- ::48.0, null, 华阳, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, cf9c4f76-25aa--bfd0-eabbcc7f49fd, d18cd6ed-bd7d-4f03-82bf-05227e455f39, -- ::48.0, null, 华阳, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, 709fd7b9----87ccb98bf7f9, b353fe1e-93b1-4ba9-8dc9-dfd2cb59ecff, -- ::48.0, null, 华阳, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, 5b3dd786-e124-41e5-a8db-4a7b695145f5, e0e76526-e1a5-40cd-8e42-7e9588f4ca7b, -- ::48.0, null, 华阳, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, 0c672acd-5ac2-4f0c--3258b3991f97, 77712ec8-85dd-4a0c--b5c09e41dfbc, -- ::48.0, null, 华阳, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, 568c06de-f688-4d93-a215-1dc1fe4ce8b5, d02a4912-cde1-4a01-8c78-b0038926929f, -- ::48.0, null, 华阳, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, 387bd87f-46bd-4f54-b4bf-de41f07a6e02, fd328b55-bd72-49ab-aecc-13e7f850d7e5, -- ::48.0, null, 华阳, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, 3d45cdef-56cf--9f3a-7776c0aca4a4, fd4744fc-b30e-434e--1f147cc3995a, -- ::48.0, null, 华阳, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, 00bdb5eb-c717--821e-703c3f3fd1e4, 8acd09d7--468f-9bce-54fffb3d4768, -- ::48.0, null, 华阳, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, 2e5ea2a6-293c-45cb-b929-aa7aab898381, f4729928-0a23-46bd-aa55-868079fd1fcb, -- ::48.0, null, 华阳, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, d3cea945-672c-4a97-a65c-7c85d598c800, bb3f262c-b3a8--8e03-85b8d9c03c70, -- ::48.0, null, 华阳, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, b3fed9e0-c548-4a36-afa9-003fb0afc28d, 09fba2ab-575c-44ba-8e99-5f34a724cce4, -- ::48.0, null, 华阳, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::48.0, ee602561-59d7-448f-93af-de4d660bcc00, c9f4d213-774a-40fb-9e46-21c7eacf14c6, -- ::48.0, null, 华阳, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::38.0, f3572e33-2a2e--8f64-0173b06c0eb0, 78d51f00-d81b--90e7-020682084bf7, null, null, 华阳站牌, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::51.0, 46d7bef8-86b6-4dc8-9adb-825c3e3dc01e, b4d709da--407b--b529b437d44c, null, null, 华阳站牌, ]

DEBUG - {conn-, pstmt-, rs-} Result: [, -- ::17.0, 42c19e53-1f0a--b1af-c8b9a1f38419, 68e42c79--495c-952f-d83007cf6775, null, null, 华阳站牌, ]

-- ::56.876 DEBUG  --- [nio--exec-] c.s.s.d.m.H.selectByNameLike             : <==      Total:

DEBUG - {conn-, pstmt-, rs-} closed

DEBUG - {conn-, pstmt-} clearParameters.

DEBUG - {conn-} pool-recycle

7.最后开启druid的监控功能

在DruidConfig.java中配置监控服务器和过滤器，完整的config.java如下

package com.sxd.swapping.config;

import com.alibaba.druid.pool.DruidDataSource;

import com.alibaba.druid.support.http.StatViewServlet;

import com.alibaba.druid.support.http.WebStatFilter;

import com.sxd.swapping.base.UniVerResponse;

import com.sxd.swapping.util.MyException;

import org.springframework.beans.factory.annotation.Value;

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.Primary;

import javax.sql.DataSource;

import java.sql.SQLException;

/**

 * 使用阿里的Druid数据库连接池

 * 唯一需要提供的配置类

 *

 * 作用：覆盖默认的DataSource

 */

@Configuration

public class DruidDBConfig {

    @Value("${spring.datasource.url}")

    private String dbUrl;

    @Value("${spring.datasource.username}")

    private String username;

    @Value("${spring.datasource.password}")

    private String password;

    @Value("${spring.datasource.driver-class-name}")

    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     //声明其为Bean实例

    @Primary  //在同样的DataSource中，首先使用被标注的DataSource

    public DataSource dataSource(){

        DruidDataSource datasource = new DruidDataSource();

        datasource.setUrl(this.dbUrl);

        datasource.setUsername(username);

        datasource.setPassword(password);

        datasource.setDriverClassName(driverClassName);

        //configuration

        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) {

            new MyException("druid阿里数据库连接池 初始化失败", UniVerResponse.ERROR_SYS_EXCPTION,e);

        }

        datasource.setConnectionProperties(connectionProperties);

        return datasource;

    }

    /**

     * 配置监控服务器

     *

     * @return 返回监控注册的servlet对象

     */

    @Bean

    public ServletRegistrationBean statViewServlet() {

        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");

        // 添加IP白名单

        servletRegistrationBean.addInitParameter("allow", "192.168.25.125,127.0.0.1");

        // 添加IP黑名单，当白名单和黑名单重复时，黑名单优先级更高

        servletRegistrationBean.addInitParameter("deny", "192.168.25.123");

        // 添加控制台管理用户

        servletRegistrationBean.addInitParameter("loginUsername", "druid");

        servletRegistrationBean.addInitParameter("loginPassword", "123456");

        // 是否能够重置数据

        servletRegistrationBean.addInitParameter("resetEnable", "false");

        return servletRegistrationBean;

    }

    /**

     * 配置服务过滤器

     *

     * @return 返回过滤器配置对象

     */

    @Bean

    public FilterRegistrationBean statFilter() {

        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());

        // 添加过滤规则

        filterRegistrationBean.addUrlPatterns("/*");

        // 忽略过滤格式

        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*,");

        return filterRegistrationBean;

    }

}

8.再重启项目，访问地址：

http://localhost:9666/druid/login.html

#我自己配置的端口是9666，默认8080访问就行

访问用户名密码在config中已经配置了的：

servletRegistrationBean.addInitParameter("loginUsername", "druid");

servletRegistrationBean.addInitParameter("loginPassword", "123456");

登录后页面如下：

码农公寓

相关文章