在 http://www.cnblogs.com/sxdcgaq8080/p/9039442.html的基础上,来看看spring boot项目中采用Druid连接池。
GitHub地址:示例代码
============================================================================
注:使用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中添加配置【其中有一部分是原本就需要配置的连接数据库的】
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
#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
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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.启动启动类,报错了
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
<!-- https://mvnrepository.com/artifact/log4j/log4j --> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency>
这里可能会出现一个问题:http://www.cnblogs.com/sxdcgaq8080/p/9065796.html
6.重新启动
可以看到 druid连接池已经连接成功了。
然后通过spring data JPA进行查询,打印结果如下:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
DEBUG - {conn-10005} pool-connect DEBUG - {conn-10005} 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-10005, pstmt-20000} Parameters : [%华阳%] DEBUG - {conn-10005, pstmt-20000} Types : [VARCHAR] DEBUG - {conn-10005, pstmt-20000, rs-50005} 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-10005, pstmt-20000, rs-50005} open DEBUG - {conn-10005, pstmt-20000, rs-50005} Header: [id, create_date, create_id, uid, update_date, update_id, area_name, area_person] DEBUG - {conn-10005, pstmt-20000, rs-50005} Result: [28, 2018-05-16 14:32:48.0, 7cbfc7a7-db06-4359-aaf9-38a461c90eea, 27b0debd-c2fc-4548-99c7-ec1ed11ca46f, 2018-05-17 11:34:50.0, 39b3cade-5f5f-4a9f-961c-3f33615983fc, 华阳汽车站, 7788] DEBUG - {conn-10005, pstmt-20000, rs-50005} Result: [29, 2018-05-16 14:32:48.0, 6e10dc22-5b87-43e2-8f44-c6f07ed68798, 09d0a84d-feac-4f27-91f6-2ce35780bb76, 2018-05-17 11:31:18.0, a9b51196-c3e1-4f0f-8b64-5229fa00747e, 华阳车站, 7777] DEBUG - {conn-10005, pstmt-20000, rs-50005} Result: [32, 2018-05-16 14:32:48.0, 39631b5b-1005-4e5f-8ed3-f5ad45710262, 23bc1a9f-2e6d-4623-8c10-abd3ecbd6f12, 2018-05-16 14:32:48.0, null, 华阳, 6666] DEBUG - {conn-10005, pstmt-20000, rs-50005} Result: [33, 2018-05-16 14:32:48.0, cf9c4f76-25aa-4610-bfd0-eabbcc7f49fd, d18cd6ed-bd7d-4f03-82bf-05227e455f39, 2018-05-16 14:32:48.0, null, 华阳, 54444] DEBUG - {conn-10005, pstmt-20000, rs-50005} Result: [34, 2018-05-16 14:32:48.0, 709fd7b9-1954-4646-8077-87ccb98bf7f9, b353fe1e-93b1-4ba9-8dc9-dfd2cb59ecff, 2018-05-16 14:32:48.0, null, 华阳, 333332] DEBUG - {conn-10005, pstmt-20000, rs-50005} Result: [35, 2018-05-16 14:32:48.0, 5b3dd786-e124-41e5-a8db-4a7b695145f5, e0e76526-e1a5-40cd-8e42-7e9588f4ca7b, 2018-05-16 14:32:48.0, null, 华阳, 2222] DEBUG - {conn-10005, pstmt-20000, rs-50005} Result: [36, 2018-05-16 14:32:48.0, 0c672acd-5ac2-4f0c-9829-3258b3991f97, 77712ec8-85dd-4a0c-9752-b5c09e41dfbc, 2018-05-16 14:32:48.0, null, 华阳, 1111] DEBUG - {conn-10005, pstmt-20000, rs-50005} Result: [37, 2018-05-16 14:32:48.0, 568c06de-f688-4d93-a215-1dc1fe4ce8b5, d02a4912-cde1-4a01-8c78-b0038926929f, 2018-05-16 14:32:48.0, null, 华阳, 0] DEBUG - {conn-10005, pstmt-20000, rs-50005} Result: [38, 2018-05-16 14:32:48.0, 387bd87f-46bd-4f54-b4bf-de41f07a6e02, fd328b55-bd72-49ab-aecc-13e7f850d7e5, 2018-05-16 14:32:48.0, null, 华阳, 1231] DEBUG - {conn-10005, pstmt-20000, rs-50005} Result: [39, 2018-05-16 14:32:48.0, 3d45cdef-56cf-4653-9f3a-7776c0aca4a4, fd4744fc-b30e-434e-9839-1f147cc3995a, 2018-05-16 14:32:48.0, null, 华阳, 232342] DEBUG - {conn-10005, pstmt-20000, rs-50005} Result: [40, 2018-05-16 14:32:48.0, 00bdb5eb-c717-4464-821e-703c3f3fd1e4, 8acd09d7-8107-468f-9bce-54fffb3d4768, 2018-05-16 14:32:48.0, null, 华阳, 453453] DEBUG - {conn-10005, pstmt-20000, rs-50005} Result: [41, 2018-05-16 14:32:48.0, 2e5ea2a6-293c-45cb-b929-aa7aab898381, f4729928-0a23-46bd-aa55-868079fd1fcb, 2018-05-16 14:32:48.0, null, 华阳, 456456] DEBUG - {conn-10005, pstmt-20000, rs-50005} Result: [42, 2018-05-16 14:32:48.0, d3cea945-672c-4a97-a65c-7c85d598c800, bb3f262c-b3a8-4295-8e03-85b8d9c03c70, 2018-05-16 14:32:48.0, null, 华阳, 78768] DEBUG - {conn-10005, pstmt-20000, rs-50005} Result: [43, 2018-05-16 14:32:48.0, b3fed9e0-c548-4a36-afa9-003fb0afc28d, 09fba2ab-575c-44ba-8e99-5f34a724cce4, 2018-05-16 14:32:48.0, null, 华阳, 885665] DEBUG - {conn-10005, pstmt-20000, rs-50005} Result: [44, 2018-05-16 14:32:48.0, ee602561-59d7-448f-93af-de4d660bcc00, c9f4d213-774a-40fb-9e46-21c7eacf14c6, 2018-05-16 14:32:48.0, null, 华阳, 9999] DEBUG - {conn-10005, pstmt-20000, rs-50005} Result: [45, 2018-05-17 10:52:38.0, f3572e33-2a2e-4993-8f64-0173b06c0eb0, 78d51f00-d81b-4561-90e7-020682084bf7, null, null, 华阳站牌, 7777] DEBUG - {conn-10005, pstmt-20000, rs-50005} Result: [46, 2018-05-17 11:01:51.0, 46d7bef8-86b6-4dc8-9adb-825c3e3dc01e, b4d709da-1500-407b-8777-b529b437d44c, null, null, 华阳站牌, 7777] DEBUG - {conn-10005, pstmt-20000, rs-50005} Result: [47, 2018-05-17 11:25:17.0, 42c19e53-1f0a-4653-b1af-c8b9a1f38419, 68e42c79-4860-495c-952f-d83007cf6775, null, null, 华阳站牌, 7777] DEBUG - {conn-10005, pstmt-20000, rs-50005} closed DEBUG - {conn-10005, pstmt-20000} clearParameters. DEBUG - {conn-10005} commited DEBUG - {conn-10005} setAutoCommit true DEBUG - {conn-10005} pool-recycle
通过mybatis查询,查询结果如下:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
DEBUG - {conn-10005} pool-connect 2018-05-21 14:56:56.869 DEBUG 3920 --- [nio-9666-exec-8] c.s.s.d.m.H.selectByNameLike : ==> Preparing: SELECT * FROM hua_yang_area WHERE area_name like '%华阳%' DEBUG - {conn-10005, pstmt-20002} created. SELECT * FROM hua_yang_area WHERE area_name like '%华阳%' 2018-05-21 14:56:56.870 DEBUG 3920 --- [nio-9666-exec-8] c.s.s.d.m.H.selectByNameLike : ==> Parameters: DEBUG - {conn-10005, pstmt-20002} Parameters : [] DEBUG - {conn-10005, pstmt-20002} Types : [] DEBUG - {conn-10005, pstmt-20002} executed. 0.573573 millis. SELECT * FROM hua_yang_area WHERE area_name like '%华阳%' DEBUG - {conn-10005, pstmt-20002, rs-50004} open DEBUG - {conn-10005, pstmt-20002, rs-50004} Header: [id, create_date, create_id, uid, update_date, update_id, area_name, area_person] DEBUG - {conn-10005, pstmt-20002, rs-50004} Result: [28, 2018-05-16 14:32:48.0, 7cbfc7a7-db06-4359-aaf9-38a461c90eea, 27b0debd-c2fc-4548-99c7-ec1ed11ca46f, 2018-05-17 11:34:50.0, 39b3cade-5f5f-4a9f-961c-3f33615983fc, 华阳汽车站, 7788] DEBUG - {conn-10005, pstmt-20002, rs-50004} Result: [29, 2018-05-16 14:32:48.0, 6e10dc22-5b87-43e2-8f44-c6f07ed68798, 09d0a84d-feac-4f27-91f6-2ce35780bb76, 2018-05-17 11:31:18.0, a9b51196-c3e1-4f0f-8b64-5229fa00747e, 华阳车站, 7777] DEBUG - {conn-10005, pstmt-20002, rs-50004} Result: [32, 2018-05-16 14:32:48.0, 39631b5b-1005-4e5f-8ed3-f5ad45710262, 23bc1a9f-2e6d-4623-8c10-abd3ecbd6f12, 2018-05-16 14:32:48.0, null, 华阳, 6666] DEBUG - {conn-10005, pstmt-20002, rs-50004} Result: [33, 2018-05-16 14:32:48.0, cf9c4f76-25aa-4610-bfd0-eabbcc7f49fd, d18cd6ed-bd7d-4f03-82bf-05227e455f39, 2018-05-16 14:32:48.0, null, 华阳, 54444] DEBUG - {conn-10005, pstmt-20002, rs-50004} Result: [34, 2018-05-16 14:32:48.0, 709fd7b9-1954-4646-8077-87ccb98bf7f9, b353fe1e-93b1-4ba9-8dc9-dfd2cb59ecff, 2018-05-16 14:32:48.0, null, 华阳, 333332] DEBUG - {conn-10005, pstmt-20002, rs-50004} Result: [35, 2018-05-16 14:32:48.0, 5b3dd786-e124-41e5-a8db-4a7b695145f5, e0e76526-e1a5-40cd-8e42-7e9588f4ca7b, 2018-05-16 14:32:48.0, null, 华阳, 2222] DEBUG - {conn-10005, pstmt-20002, rs-50004} Result: [36, 2018-05-16 14:32:48.0, 0c672acd-5ac2-4f0c-9829-3258b3991f97, 77712ec8-85dd-4a0c-9752-b5c09e41dfbc, 2018-05-16 14:32:48.0, null, 华阳, 1111] DEBUG - {conn-10005, pstmt-20002, rs-50004} Result: [37, 2018-05-16 14:32:48.0, 568c06de-f688-4d93-a215-1dc1fe4ce8b5, d02a4912-cde1-4a01-8c78-b0038926929f, 2018-05-16 14:32:48.0, null, 华阳, 0] DEBUG - {conn-10005, pstmt-20002, rs-50004} Result: [38, 2018-05-16 14:32:48.0, 387bd87f-46bd-4f54-b4bf-de41f07a6e02, fd328b55-bd72-49ab-aecc-13e7f850d7e5, 2018-05-16 14:32:48.0, null, 华阳, 1231] DEBUG - {conn-10005, pstmt-20002, rs-50004} Result: [39, 2018-05-16 14:32:48.0, 3d45cdef-56cf-4653-9f3a-7776c0aca4a4, fd4744fc-b30e-434e-9839-1f147cc3995a, 2018-05-16 14:32:48.0, null, 华阳, 232342] DEBUG - {conn-10005, pstmt-20002, rs-50004} Result: [40, 2018-05-16 14:32:48.0, 00bdb5eb-c717-4464-821e-703c3f3fd1e4, 8acd09d7-8107-468f-9bce-54fffb3d4768, 2018-05-16 14:32:48.0, null, 华阳, 453453] DEBUG - {conn-10005, pstmt-20002, rs-50004} Result: [41, 2018-05-16 14:32:48.0, 2e5ea2a6-293c-45cb-b929-aa7aab898381, f4729928-0a23-46bd-aa55-868079fd1fcb, 2018-05-16 14:32:48.0, null, 华阳, 456456] DEBUG - {conn-10005, pstmt-20002, rs-50004} Result: [42, 2018-05-16 14:32:48.0, d3cea945-672c-4a97-a65c-7c85d598c800, bb3f262c-b3a8-4295-8e03-85b8d9c03c70, 2018-05-16 14:32:48.0, null, 华阳, 78768] DEBUG - {conn-10005, pstmt-20002, rs-50004} Result: [43, 2018-05-16 14:32:48.0, b3fed9e0-c548-4a36-afa9-003fb0afc28d, 09fba2ab-575c-44ba-8e99-5f34a724cce4, 2018-05-16 14:32:48.0, null, 华阳, 885665] DEBUG - {conn-10005, pstmt-20002, rs-50004} Result: [44, 2018-05-16 14:32:48.0, ee602561-59d7-448f-93af-de4d660bcc00, c9f4d213-774a-40fb-9e46-21c7eacf14c6, 2018-05-16 14:32:48.0, null, 华阳, 9999] DEBUG - {conn-10005, pstmt-20002, rs-50004} Result: [45, 2018-05-17 10:52:38.0, f3572e33-2a2e-4993-8f64-0173b06c0eb0, 78d51f00-d81b-4561-90e7-020682084bf7, null, null, 华阳站牌, 7777] DEBUG - {conn-10005, pstmt-20002, rs-50004} Result: [46, 2018-05-17 11:01:51.0, 46d7bef8-86b6-4dc8-9adb-825c3e3dc01e, b4d709da-1500-407b-8777-b529b437d44c, null, null, 华阳站牌, 7777] DEBUG - {conn-10005, pstmt-20002, rs-50004} Result: [47, 2018-05-17 11:25:17.0, 42c19e53-1f0a-4653-b1af-c8b9a1f38419, 68e42c79-4860-495c-952f-d83007cf6775, null, null, 华阳站牌, 7777] 2018-05-21 14:56:56.876 DEBUG 3920 --- [nio-9666-exec-8] c.s.s.d.m.H.selectByNameLike : <== Total: 18 DEBUG - {conn-10005, pstmt-20002, rs-50004} closed DEBUG - {conn-10005, pstmt-20002} clearParameters. DEBUG - {conn-10005} pool-recycle
7.最后开启druid的监控功能
在DruidConfig.java中配置监控服务器和过滤器,完整的config.java如下
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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");
登录后页面如下: