資訊專欄INFORMATION COLUMN
摘要：這篇文篇將介紹，如何通過整合數據庫鏈接池實時監控數據庫鏈接信息，為優化數據庫性能提供更好的指導，同樣將通過配置文件形式進行配置方便簡潔。
這篇文篇將介紹，如何通過SpringBoot整合Druid數據庫鏈接池,實時監控數據庫鏈接信息，為優化數據庫性能提供更好的指導，同樣將通過YML配置文件形式進行配置,方便簡潔。
準備工作環境：
windows jdk 8 maven 3.0 IDEA創建數據庫表
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sno` int(15) NOT NULL,
`sname` varchar(50) DEFAULT NULL,
`sex` char(2) DEFAULT NULL,
`dept` varchar(25) DEFAULT NULL,
`birth` date DEFAULT NULL,
`age` int(3) DEFAULT NULL,
PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ("1", "李同學", "1", "王同學學習成績很不錯", "2010-07-22", "17");
構建工程
4.0.0 cn.zhangbox spring-boot-study 1.0-SNAPSHOT cn.zhangbox spring-boot-druid 0.0.1-SNAPSHOT jar spring-boot-druid this project for Spring Boot UTF-8 UTF-8 1.8 3.4 1.10 1.2.0 1.16.14 1.2.41 1.1.2 aliyunmaven http://maven.aliyun.com/nexus/content/groups/public/ org.mybatis.spring.boot mybatis-spring-boot-starter ${mybatis-spring-boot.version} org.springframework.boot spring-boot-starter-web mysql mysql-connector-java runtime org.springframework.boot spring-boot-starter-test test org.apache.commons commons-lang3 ${commons-lang3.version} commons-codec commons-codec ${commons-codec.version} com.alibaba fastjson ${fastjson.version} com.alibaba druid-spring-boot-starter ${druid.version} org.projectlombok lombok ${lombok.version} spring-boot-druid org.apache.maven.plugins maven-compiler-plugin 3.5.1 1.8 1.8 UTF-8 org.apache.maven.plugins maven-surefire-plugin 2.19.1 org.springframework.boot spring-boot-maven-plugin org.springframework springloaded 1.2.4.RELEASE cn.zhangbox.admin.SpringBootDruidApplication -Dfile.encoding=UTF-8 -Xdebug -Xrunjdwp:transport=dt_socket,server=y,suspend=n,address=5005 true true org.springframework.boot spring-boot-maven-plugin cn.zhangbox.admin.SpringBootDruidApplication -Dfile.encoding=UTF-8 -Xdebug -Xrunjdwp:transport=dt_socket,server=y,suspend=n,address=5005 true true
注意：這里引入了lombok插件節省編寫實體類時候寫get和set方法,這里在idea中進行set和get操作需要下載lombok插件,在設置頁面的plugins中搜索lombok插件在中央插件庫下載后重啟idea即可,更詳細的lombok使用教程可以查考:
程序員DD的lombok系列教程:Lombok：讓JAVA代碼更優雅
修改YML配置#公共配置
server:
port: 80
tomcat:
uri-encoding: UTF-8
spring:
#激活哪一個環境的配置文件
profiles:
active: dev
#連接池配置
datasource:
driver-class-name: com.mysql.jdbc.Driver
# 使用druid數據源
type: com.alibaba.druid.pool.DruidDataSource
druid:
# 配置測試查詢語句
validationQuery: SELECT 1 FROM DUAL
# 初始化大小，最小，最大
initialSize: 10
minIdle: 10
maxActive: 200
# 配置一個連接在池中最小生存的時間，單位是毫秒
minEvictableIdleTimeMillis: 180000
testOnBorrow: false
testWhileIdle: true
removeAbandoned: true
removeAbandonedTimeout: 1800
logAbandoned: true
# 打開PSCache，并且指定每個連接上PSCache的大小
poolPreparedStatements: true
maxOpenPreparedStatements: 100
# 配置監控統計攔截的filters，去掉后監控界面sql無法統計，"wall"用于防火墻
filters: stat,wall,log4j
# 通過connectProperties屬性來打開mergeSql功能；慢SQL記錄
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
#mybatis
mybatis:
# 實體類掃描
type-aliases-package: cn.zhangbox.springboot.entity
# 配置映射文件位置
mapper-locations: classpath:mapper/*.xml
# 開啟駝峰匹配
mapUnderscoreToCamelCase: true
---
#開發環境配置
server:
#端口
port: 8080
spring:
profiles: dev
# 數據源配置
datasource:
url: jdbc:mysql://127.0.0.1:3306/student?useUnicode=true&characterEncoding=utf8&useSSL=false&tinyInt1isBit=true
username: root
password: 123456
#日志
logging:
config: classpath:log/logback.xml
path: log/spring-boot-druid
---
#測試環境配置
server:
#端口
port: 80
spring:
profiles: test
# 數據源配置
datasource:
url: jdbc:mysql://127.0.0.1:3306/spring-boot-druid?useUnicode=true&characterEncoding=utf8&useSSL=false&tinyInt1isBit=true
username: root
password: 123456
#日志
logging:
config: classpath:log/logback.xml
path: /home/log/spring-boot-druid
---
#生產環境配置
server:
#端口
port: 8080
spring:
profiles: prod
# 數據源配置
datasource:
url: jdbc:mysql://127.0.0.1:3306/spring-boot-druid?useUnicode=true&characterEncoding=utf8&useSSL=false&tinyInt1isBit=true
username: root
password: 123456
#日志
logging:
config: classpath:log/logback.xml
path: /home/log/spring-boot-druid
這里進行了mybatis整合,如果不會mybatis整合可以參考我寫的這篇文章:
SpringBoot非官方教程 | 第六篇：SpringBoot整合mybatis
在工程resources文件夾下新建文件夾log，并在該文件夾下創建logback.xml文件,加入以下配置：
${CONSOLE_LOG_PATTERN} UTF-8 info ${LOG_PATH}/log_debug.log %d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - %msg%n UTF-8 ${LOG_PATH}/debug/log-debug-%d{yyyy-MM-dd}.%i.log 500MB 30 debug ACCEPT DENY ${LOG_PATH}/log_info.log %d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - %msg%n UTF-8 ${LOG_PATH}/info/log-info-%d{yyyy-MM-dd}.%i.log 500MB 30 info ACCEPT DENY ${LOG_PATH}/log_warn.log %d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - %msg%n UTF-8 ${LOG_PATH}/warn/log-warn-%d{yyyy-MM-dd}.%i.log 500MB 30 warn ACCEPT DENY ${LOG_PATH}/log_error.log %d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - %msg%n UTF-8 ${LOG_PATH}/error/log-error-%d{yyyy-MM-dd}.%i.log 500MB 30 error ACCEPT DENY
注意：：loback配置文件中
name的屬性值一定要是當前工程的java代碼的完整目錄,因為mybatis打印的日志級別是debug級別的，因此需要配置debug級別日志掃描的目錄。
創建Druid配置類在工程java代碼目錄下創建config的目錄在下面創建DruidDBConfig類加入以下代碼:
@Configuration
public class DruidDBConfig {
@Bean
public ServletRegistrationBean druidServlet() {
ServletRegistrationBean reg = new ServletRegistrationBean();
reg.setServlet(new StatViewServlet());
reg.addUrlMappings("/druid/*");
//設置控制臺管理用戶
reg.addInitParameter("loginUsername","root");
reg.addInitParameter("loginPassword","root");
// 禁用HTML頁面上的“Reset All”功能
reg.addInitParameter("resetEnable","false");
//reg.addInitParameter("allow", "127.0.0.1"); //白名單
return reg;
}
@Bean
public FilterRegistrationBean filterRegistrationBean() {
//創建過濾器
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
filterRegistrationBean.setFilter(new WebStatFilter());
Map initParams = new HashMap();
//忽略過濾的形式
initParams.put("exclusions", "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*");
filterRegistrationBean.setInitParameters(initParams);
//設置過濾器過濾路徑
filterRegistrationBean.addUrlPatterns("/*");
return filterRegistrationBean;
}
}
注意：這里ServletRegistrationBean 配置bean中通過addInitParameter設置了管控臺的用戶名密碼都是root，可以在這里進行自定義配置也可以將這里的用戶名密碼通過轉移數據庫進行定制化配置實現。
創建實體在工程java代碼目錄下創建entity的目錄在下面創建Student類加入以下代碼:
@Data
@EqualsAndHashCode(callSuper = false)
public class Student {
private static final long serialVersionUID = 1L;
/**
* 主鍵id
*/
private Integer sno;
/**
* 學生姓名
*/
private String sname;
/**
* 性別
*/
private String sex;
/**
* 生日
*/
private String birth;
/**
* 年齡
*/
private String age;
/**
* 簡介
*/
private String dept;
}
創建Controller
在工程java代碼目錄下創建controller的目錄在下面創建StudentConteroller類加入以下代碼:
@Controller
@RequestMapping("/student")
public class StudentConteroller {
private static final Logger LOGGER = LoggerFactory.getLogger(StudentConteroller.class);
@Autowired
protected StudentService studentService;
/**
* 查詢所有的學生信息
*
* @param sname
* @param age
* @param modelMap
* @return
*/
@ResponseBody
@GetMapping("/list")
public String list(String sname, Integer age, ModelMap modelMap) {
String json = null;
try {
List studentList = studentService.getStudentList(sname, age);
modelMap.put("ren_code", "0");
modelMap.put("ren_msg", "查詢成功");
modelMap.put("studentList", studentList);
json = JSON.toJSONString(modelMap);
} catch (Exception e) {
e.printStackTrace();
modelMap.put("ren_code", "0");
modelMap.put("ren_msg", "查詢失敗===>" + e);
LOGGER.error("查詢失敗===>" + e);
json = JSON.toJSONString(modelMap);
}
return json;
}
}
創建Service
在工程java代碼目錄下面創建service目錄在下面創建StudentService類加入以下代碼:
public interface StudentService {
/**
* 查詢所有的學生信息
*
* @param sname
* @param age
* @return
*/
List getStudentList(String sname, Integer age);
}
創建ServiceImpl
在工程java代碼目錄下的service的目錄下面創建impl目錄在下面創建StudentServiceImpl類加入以下代碼:
@Service("StudentService")
@Transactional(readOnly = true, rollbackFor = Exception.class)
public class StudentServiceImpl implements StudentService {
@Autowired
StudentDao studentDao;
@Override
public List getStudentList(String sname, Integer age) {
return studentDao.getStudentList(sname,age);
}
}
創建Dao
在工程java代碼目錄下創建dao的目錄在下面創建StudentDao類加入以下代碼:
public interface StudentDao {
List getStudentList(@Param("sname")String sname, @Param("age")Integer age);
}
創建Mapper映射文件
在工程resource目錄下創建mapper的目錄在下面創建StudentMapper.xml映射文件加入以下代碼:
創建啟動類
@SpringBootApplication
@MapperScan("cn.zhangbox.springboot.dao")//配置mybatis接口包掃描
public class SpringBootDruidApplication extends SpringBootServletInitializer {
public static void main(String[] args) {
SpringApplication.run(SpringBootDruidApplication.class, args);
}
}
啟動項目進行測試：
控制臺打印
. ____ _ __ _ _
/ / ___"_ __ _ _(_)_ __ __ _
( ( )\___ | "_ | "_| | "_ / _` |
/ ___)| |_)| | | | | || (_| | ) ) ) )
" |____| .__|_| |_|_| |_\__, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot :: (v1.5.3.RELEASE)
2018-07-07 10:54:52.404 INFO 24084 --- [ main] c.z.s.SpringBootDruidApplication : Starting SpringBootDruidApplication on HP with PID 24084 (C:UsersAdministratorDesktopspring-boot-studyspring-boot-druid argetclasses started by Administrator in C:UsersAdministratorDesktopspring-boot-study)
2018-07-07 10:54:52.445 INFO 24084 --- [ main] c.z.s.SpringBootDruidApplication : The following profiles are active: dev
2018-07-07 10:54:52.570 INFO 24084 --- [ main] ationConfigEmbeddedWebApplicationContext : Refreshing org.springframework.boot.context.embedded.AnnotationConfigEmbeddedWebApplicationContext@53142455: startup date [Sat Jul 07 10:54:52 CST 2018]; root of context hierarchy
2018-07-07 10:54:52.997 INFO 24084 --- [kground-preinit] o.h.validator.internal.util.Version : HV000001: Hibernate Validator 5.3.5.Final
2018-07-07 10:54:53.736 INFO 24084 --- [ main] o.s.b.f.s.DefaultListableBeanFactory : Overriding bean definition for bean "filterRegistrationBean" with a different definition: replacing [Root bean: class [null]; scope=; abstract=false; lazyInit=false; autowireMode=3; dependencyCheck=0; autowireCandidate=true; primary=false; factoryBeanName=druidDBConfig; factoryMethodName=filterRegistrationBean; initMethodName=null; destroyMethodName=(inferred); defined in class path resource [cn/zhangbox/springboot/config/DruidDBConfig.class]] with [Root bean: class [null]; scope=; abstract=false; lazyInit=false; autowireMode=3; dependencyCheck=0; autowireCandidate=true; primary=false; factoryBeanName=com.alibaba.druid.spring.boot.autoconfigure.stat.DruidWebStatFilterConfiguration; factoryMethodName=filterRegistrationBean; initMethodName=null; destroyMethodName=(inferred); defined in class path resource [com/alibaba/druid/spring/boot/autoconfigure/stat/DruidWebStatFilterConfiguration.class]]
2018-07-07 10:54:54.887 INFO 24084 --- [ main] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat initialized with port(s): 8080 (http)
2018-07-07 10:54:54.903 INFO 24084 --- [ main] o.apache.catalina.core.StandardService : Starting service Tomcat
2018-07-07 10:54:54.905 INFO 24084 --- [ main] org.apache.catalina.core.StandardEngine : Starting Servlet Engine: Apache Tomcat/8.5.14
2018-07-07 10:54:55.123 INFO 24084 --- [ost-startStop-1] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring embedded WebApplicationContext
2018-07-07 10:54:55.124 INFO 24084 --- [ost-startStop-1] o.s.web.context.ContextLoader : Root WebApplicationContext: initialization completed in 2554 ms
2018-07-07 10:54:55.522 INFO 24084 --- [ost-startStop-1] o.s.b.w.servlet.ServletRegistrationBean : Mapping servlet: "statViewServlet" to [/druid/*]
2018-07-07 10:54:55.525 INFO 24084 --- [ost-startStop-1] o.s.b.w.servlet.ServletRegistrationBean : Mapping servlet: "dispatcherServlet" to [/]
2018-07-07 10:54:55.525 INFO 24084 --- [ost-startStop-1] o.s.b.w.servlet.ServletRegistrationBean : Mapping servlet: "statViewServlet" to [/druid/*]
2018-07-07 10:54:55.526 INFO 24084 --- [ost-startStop-1] o.s.b.w.servlet.ServletRegistrationBean : Servlet statViewServlet was not registered (possibly already registered?)
2018-07-07 10:54:55.531 INFO 24084 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean : Mapping filter: "characterEncodingFilter" to: [/*]
2018-07-07 10:54:55.531 INFO 24084 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean : Mapping filter: "hiddenHttpMethodFilter" to: [/*]
2018-07-07 10:54:55.532 INFO 24084 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean : Mapping filter: "httpPutFormContentFilter" to: [/*]
2018-07-07 10:54:55.533 INFO 24084 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean : Mapping filter: "requestContextFilter" to: [/*]
2018-07-07 10:54:55.533 INFO 24084 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean : Mapping filter: "webStatFilter" to urls: [/*]
2018-07-07 10:54:57.216 INFO 24084 --- [ main] s.w.s.m.m.a.RequestMappingHandlerAdapter : Looking for @ControllerAdvice: org.springframework.boot.context.embedded.AnnotationConfigEmbeddedWebApplicationContext@53142455: startup date [Sat Jul 07 10:54:52 CST 2018]; root of context hierarchy
2018-07-07 10:54:57.322 INFO 24084 --- [ main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/student/list],methods=[GET]}" onto public java.lang.String cn.zhangbox.springboot.controller.StudentConteroller.list(java.lang.String,java.lang.Integer,org.springframework.ui.ModelMap)
2018-07-07 10:54:57.326 INFO 24084 --- [ main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/error]}" onto public org.springframework.http.ResponseEntity> org.springframework.boot.autoconfigure.web.BasicErrorController.error(javax.servlet.http.HttpServletRequest)
2018-07-07 10:54:57.328 INFO 24084 --- [ main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/error],produces=[text/html]}" onto public org.springframework.web.servlet.ModelAndView org.springframework.boot.autoconfigure.web.BasicErrorController.errorHtml(javax.servlet.http.HttpServletRequest,javax.servlet.http.HttpServletResponse)
2018-07-07 10:54:57.376 INFO 24084 --- [ main] o.s.w.s.handler.SimpleUrlHandlerMapping : Mapped URL path [/webjars/**] onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
2018-07-07 10:54:57.376 INFO 24084 --- [ main] o.s.w.s.handler.SimpleUrlHandlerMapping : Mapped URL path [/**] onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
2018-07-07 10:54:57.451 INFO 24084 --- [ main] o.s.w.s.handler.SimpleUrlHandlerMapping : Mapped URL path [/**/favicon.ico] onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
2018-07-07 10:54:58.066 INFO 24084 --- [ main] o.s.j.e.a.AnnotationMBeanExporter : Registering beans for JMX exposure on startup
2018-07-07 10:54:58.068 INFO 24084 --- [ main] o.s.j.e.a.AnnotationMBeanExporter : Bean with name "statFilter" has been autodetected for JMX exposure
2018-07-07 10:54:58.069 INFO 24084 --- [ main] o.s.j.e.a.AnnotationMBeanExporter : Bean with name "dataSource" has been autodetected for JMX exposure
2018-07-07 10:54:58.077 INFO 24084 --- [ main] o.s.j.e.a.AnnotationMBeanExporter : Located MBean "dataSource": registering with JMX server as MBean [com.alibaba.druid.spring.boot.autoconfigure:name=dataSource,type=DruidDataSourceWrapper]
2018-07-07 10:54:58.081 INFO 24084 --- [ main] o.s.j.e.a.AnnotationMBeanExporter : Located MBean "statFilter": registering with JMX server as MBean [com.alibaba.druid.filter.stat:name=statFilter,type=StatFilter]
2018-07-07 10:54:58.101 INFO 24084 --- [ main] o.a.coyote.http11.Http11NioProtocol : Initializing ProtocolHandler ["http-nio-8080"]
2018-07-07 10:54:58.118 INFO 24084 --- [ main] o.a.coyote.http11.Http11NioProtocol : Starting ProtocolHandler ["http-nio-8080"]
2018-07-07 10:54:58.145 INFO 24084 --- [ main] o.a.tomcat.util.net.NioSelectorPool : Using a shared selector for servlet write/read
2018-07-07 10:54:58.172 INFO 24084 --- [ main] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat started on port(s): 8080 (http)
2018-07-07 10:54:58.179 INFO 24084 --- [ main] c.z.s.SpringBootDruidApplication : Started SpringBootDruidApplication in 7.666 seconds (JVM running for 10.386)
2018-07-07 11:00:00.245 INFO 24084 --- [nio-8080-exec-5] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring FrameworkServlet "dispatcherServlet"
2018-07-07 11:00:00.246 INFO 24084 --- [nio-8080-exec-5] o.s.web.servlet.DispatcherServlet : FrameworkServlet "dispatcherServlet": initialization started
2018-07-07 11:00:00.302 INFO 24084 --- [nio-8080-exec-5] o.s.web.servlet.DispatcherServlet : FrameworkServlet "dispatcherServlet": initialization completed in 56 ms
2018-07-07 11:00:01.606 INFO 24084 --- [nio-8080-exec-5] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} inited
瀏覽器請求測試
本地日志打印效果
2018-07-07 10:54:52.439 [main] DEBUG cn.zhangbox.springboot.SpringBootDruidApplication - Running with Spring Boot v1.5.3.RELEASE, Spring v4.3.8.RELEASE 2018-07-07 11:00:01.715 [http-nio-8080-exec-5] DEBUG c.z.springboot.dao.StudentDao.getStudentList - ==> Preparing: SELECT s.sno, s.sname, s.sex, s.dept, s.birth, s.age FROM student s WHERE 1 = 1 2018-07-07 11:00:01.881 [http-nio-8080-exec-5] DEBUG c.z.springboot.dao.StudentDao.getStudentList - ==> Parameters: 2018-07-07 11:00:01.931 [http-nio-8080-exec-5] DEBUG c.z.springboot.dao.StudentDao.getStudentList - <== Total: 2
這里使用logback配置中將不同級別的日志設置了在不同文件中打印，這樣很大程度上方便項目出問題查找問題。
Druid管控臺Spring Boot整合Druid連接池以及Druid監控源碼
歡迎關注我的微信公眾號獲取更多更全的學習資源，視頻資料，技術干貨！
文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。
轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/71506.html
摘要：下一篇介紹基于的服務注冊與調用。服務提供者工程配置這里服務提供者是使用之前進階教程第三篇整合連接池以及監控改造而來，這里一樣的部分就不再重復說明，下面將說明新增的部分。 Spring Cloud簡介 Spring Cloud是一個基于Spring Boot實現的云應用開發工具，它為基于JVM的云應用開發中涉及的配置管理、服務發現、斷路器、智能路由、微代理、控制總線、全局鎖、決策競選、分...
摘要：前言由于寫的文章已經是有點多了，為了自己和大家的檢索方便，于是我就做了這么一個博客導航。 前言 由于寫的文章已經是有點多了，為了自己和大家的檢索方便，于是我就做了這么一個博客導航。 由于更新比較頻繁，因此隔一段時間才會更新目錄導航哦～想要獲取最新原創的技術文章歡迎關注我的公眾號：Java3y Java3y文章目錄導航 Java基礎 泛型就這么簡單 注解就這么簡單 Druid數據庫連接池...
閱讀 904·2021-09-29 09:35
閱讀 1253·2021-09-28 09:36
閱讀 1522·2021-09-24 10:38
閱讀 1066·2021-09-10 11:18
閱讀 631·2019-08-30 15:54
閱讀 2496·2019-08-30 13:22
閱讀 1964·2019-08-30 11:14
閱讀 697·2019-08-29 12:35
