接以上两篇,在已集成mybatis基础上,集成druid
改造后得项目结构图如下
1. pom.xml添加druid jar包依赖
<!-- 配置数据源 阿里的 durid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.31</version>
</dependency>
2. application.properties 配置数据库
spring.datasource.name=orcl
spring.datasource.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
spring.datasource.username=charge_0201
spring.datasource.password=charge3728
# 使用druid数据源
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
#log4j选项需要添加log4j jar包依赖
spring.datasource.filters=stat,wall,log4j
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
#连接池的配置信息
spring.datasource.maxActive=20
spring.datasource.initialSize=1
# 配置获取连接等待超时的时间
spring.datasource.maxWait=60000
spring.datasource.minIdle=1
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
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.maxOpenPreparedStatements=20
#设置mybatis的mapper和model扫描路径
mybatis.mapperLocations=classpath:*.xml
mybatis.typeAliasesPackage=com.example.demo.model
3. com.example.demo.druidConfig 包下代码
(1)创建一个配置类DruidConfig.java
package com.example.demo.druidConfig;
import javax.sql.DataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import com.alibaba.druid.pool.DruidDataSource;
/**
* 创建一个配置类
* @author lee
*/
@Configuration
public class DruidConfig {
@Bean
@ConfigurationProperties(prefix = "spring.datasource")
public DataSource druidDataSource() {
DruidDataSource druidDataSource = new DruidDataSource();
return druidDataSource;
}
}
(2)配置监控拦截器DruidStatFilter.java
package com.example.demo.druidConfig;
import javax.servlet.annotation.WebFilter;
import javax.servlet.annotation.WebInitParam;
import com.alibaba.druid.support.http.WebStatFilter;
@WebFilter(filterName="druidWebStatFilter",urlPatterns="/*",
initParams={
@WebInitParam(name="exclusions",value="*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*")// 忽略资源
})
/**
* 配置监控拦截器
* @author lee
*/
public class DruidStatFilter extends WebStatFilter{
}
(3) 配置监控视图DruidStatViewServlet.java
package com.example.demo.druidConfig;
import javax.servlet.annotation.WebInitParam;
import javax.servlet.annotation.WebServlet;
import com.alibaba.druid.support.http.StatViewServlet;
@WebServlet(urlPatterns = "/druid/*",
initParams={
@WebInitParam(name="allow",value=""),// IP白名单 (没有配置或者为空,则允许所有访问)
@WebInitParam(name="deny",value=""),// IP黑名单 (存在共同时,deny优先于allow)
@WebInitParam(name="loginUsername",value="admin"),// 用户名
@WebInitParam(name="loginPassword",value="admin"),// 密码
@WebInitParam(name="resetEnable",value="false")// 禁用HTML页面上的“Reset All”功能
})
/**
* 配置监控视图
* @author lee
*/
public class DruidStatViewServlet extends StatViewServlet {
}
(4)主类上添加注解 @ServletComponentScan
在SpringBootApplication上使用@ServletComponentScan注解后,Servlet、Filter、Listener可以直接通过@WebServlet、@WebFilter、@WebListener注解自动注册,无需其他代码。
4. 启动项目
发现报错如下
Failed to bind properties under'spring.datasource' to javax.sql.DataSource
这是因为项目中缺少只需要引入log4j jar包,引入jar包依赖就能正常运行项目了
更新pom.xml文件
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
再次启动项目,启动成功。
访问 http://localhost:8080/druid/login.html
输入admin/admin
成功进入监控页面
我们再次访问 http://localhost:8080/hello/login
试一下sql监控,发现无法查询数据库,后台报错如下
2019-04-01 10:46:27.513 ERROR 4900 --- [nio-8080-exec-7] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.UncategorizedSQLException: Error attempting to get column 'id' from result set. Cause: java.sql.SQLException: 流已被关闭
; uncategorized SQLException; SQL state [99999]; error code [17027]; 流已被关闭; nested exception is java.sql.SQLException: 流已被关闭] with root cause
java.sql.SQLException: 流已被关闭
这是因为我们得测试表tab_users 得id为long类型,改为number
delete from tab_users;
ALTER TABLE tab_users modify id number;
insert into tab_users (ID, USERNAME, PASSWORD)
values ('1', '张三', '123456');
insert into tab_users (ID, USERNAME, PASSWORD)
values ('2', '李四', 'lisi');
insert into tab_users (ID, USERNAME, PASSWORD)
values ('3', '王五', 'wangwu');
sql监控如下:
结束。