SpringBoot(伍)SpringBoot整合Druid实现数据库可视化监控

数据库可视化监控"/>

SpringBoot(伍)SpringBoot整合Druid实现数据库可视化监控

SpringBoot(伍)SpringBoot整合Druid实现数据库可视化监控

文章目录

• SpringBoot(伍)SpringBoot整合Druid实现数据库可视化监控
• • 1、初始化信息
  • • 1.1、数据库脚本
    • 1.2、编写相关代码
    • • StudInfoController
      • StudentInfoServiceImpl
      • StudentInfoDAO
      • StudentInfoDao.xml
  • 2、配置
  • • 2.1、修改配置文件
    • • application.properties
    • 2.2、讲解关键配置
    • • DruidStatViewServletConfiguration
      • DruidStatProperties
      • StatViewServlet
  • 3、测试验证
  • • • 3.1、getAll
      • 3.2、druid监控台

本文介绍了SpringBoot整合Druid实现数据库可视化监控的配置过程，供参考

1、初始化信息

1.1、数据库脚本

新建数据库表及初始化一些数据，脚本如下：

create table if not exists david.t_student_info
(id varchar(20) not null comment '主键'primary key,name varchar(50) default ' ' null comment '姓名',age decimal(3) default 20 null comment '年龄',sex char default 'M' null comment '性别 M-男，W-女',phone varchar(20) null comment '手机号',email varchar(30) null comment '电子邮箱',rsv1 varchar(100) null comment '备注字段'
)
comment '学生信息表';
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10001', '张三', 20, 'M', '13988887777', 'zhangsan@163', '备注');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10002', '李四', 18, 'W', '15988887777', 'lisi@163', '李四是张三的妹妹');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10003', '苍老师', 18, 'W', '15866668888', 'canglaoshi@163', '这里是苍老师的备注');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10004', '刘备', 30, 'M', '18666666666', 'liubei@163', '我是刘皇叔，专门卖草鞋');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10005', '关羽', 29, 'M', '18655555555', 'guanyu@163', '我是关羽，卖绿豆的');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10006', ' 孙悟空', 500, 'M', '19100002222', 'wukong@163', '我是孙悟空，我有金箍棒');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10007', ' 猪八戒', 488, 'M', '18900003333', 'bajie@163', '猪八戒');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10008', ' 沙僧', 200, 'M', '18678652388', 'shaseng@163', '我是沙僧');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10009', ' 唐僧', 25, 'M', '18766809012', 'shaseng@163', '我是唐僧，你们都是弟弟');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10010', ' 老树龙井', 20, 'W', '15988801234', 'longjing@163', 'laoshulongjing');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10011', ' 苏檀儿', 22, 'W', '18809826678', 'sutaner@163', '苏檀儿');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10012', ' 宁毅', 20, 'M', '15900008888', 'ningyi@163', '宁毅');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10013', ' 小婵', 18, 'W', '18566780092', 'xiaochan@163', '我是小婵');

1.2、编写相关代码

编写Controller，Service，Dao，Mapper等，实现业务逻辑

StudInfoController

/*** @author zhang_wei* @version 1.0.0* @Classname StudInfoController* @Date 2021/2/26 11:07* @Created by zhang_wei* @since 1.0.0*/
@Controller
public class StudInfoController {Logger logger = LoggerFactory.getLogger(StudInfoController.class);@AutowiredStudentInfoService studentInfoService;@RequestMapping("/getAll")@ResponseBodypublic List<StudentInfo> getAllStudentInfos() {List<StudentInfo> result = studentInfoService.findAll();logger.info("result={}", result);return result;}
}

StudentInfoServiceImpl

/*** @author zhang_wei* @version 1.0.0* @Classname StudentInfoServiceImpl* @Date 2021/2/26 15:35* @Created by zhang_wei* @since 1.0.0*/
@Service
public class StudentInfoServiceImpl implements StudentInfoService {Logger logger = LoggerFactory.getLogger(StudentInfoServiceImpl.class);@AutowiredStudentInfoDAO studentInfoDAO;@Overridepublic List<StudentInfo> findAll() {return studentInfoDAO.findAll();}
}

StudentInfoDAO

@Repository
public interface StudentInfoDAO {int deleteByPrimaryKey(String id);int insert(StudentInfo record);int insertSelective(StudentInfo record);StudentInfo selectByPrimaryKey(String id);int updateByPrimaryKeySelective(StudentInfo record);int updateByPrimaryKey(StudentInfo record);/*** 查询所有* @return*/List<StudentInfo> findAll();
}

StudentInfoDao.xml

   <select id="findAll" parameterType="java.lang.String" resultMap="BaseResultMap">select<include refid="Base_Column_List"/>from t_student_info</select>

2、配置

2.1、修改配置文件

在application.properties中增加如下的配置，即可启用druid的监控功能

application.properties

# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.druid.filters=stat,slf4j
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
spring.datasource.druid.connection-properties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000;#可以设置Druid的监控密码
#spring.datasource.druid.stat-view-servlet.login-username=zhangsan
#spring.datasource.druid.stat-view-servlet.login-password=123456

2.2、讲解关键配置

DruidStatViewServletConfiguration

我们的配置通过DruidStatViewServletConfiguration这个类注册了StatViewServlet嵌入式Servlet，默认拦截的请求地址是/druid/*，代码如下：

public class DruidStatViewServletConfiguration {public DruidStatViewServletConfiguration() {}@Beanpublic ServletRegistrationBean statViewServletRegistrationBean(DruidStatProperties properties) {StatViewServlet config = properties.getStatViewServlet();ServletRegistrationBean registrationBean = new ServletRegistrationBean();registrationBean.setServlet(new com.alibaba.druid.support.http.StatViewServlet());registrationBean.addUrlMappings(new String[]{config.getUrlPattern() != null ? config.getUrlPattern() : "/druid/*"});if (config.getAllow() != null) {registrationBean.addInitParameter("allow", config.getAllow());}if (config.getDeny() != null) {registrationBean.addInitParameter("deny", config.getDeny());}if (config.getLoginUsername() != null) {registrationBean.addInitParameter("loginUsername", config.getLoginUsername());}if (config.getLoginPassword() != null) {registrationBean.addInitParameter("loginPassword", config.getLoginPassword());}if (config.getResetEnable() != null) {registrationBean.addInitParameter("resetEnable", config.getResetEnable());}return registrationBean;}
}

我们启动容器可以看一看到控制台的输出：

拦截/druid/*的Servlet被注册进了容器

DruidStatProperties

我们可以看到相关的配置属性Java对象为com.alibaba.druid.spring.boot.autoconfigure.properties.DruidStatProperties

DruidStatProperties有两个静态内部类WebStatFilter和StatViewServlet

这里我们关心StatViewServlet类，

allow为IP白名单，默认为所有

loginUsername可以指定登录Druid监控台的用户名，对应：spring.datasource.druid.stat-view-servlet.login-username

loginPassword指定Druid监控台的密码，对应：spring.datasource.druid.stat-view-servlet.login-password

public static class StatViewServlet {private boolean enabled = true;private String urlPattern;private String allow;private String deny;private String loginUsername;private String loginPassword;private String resetEnable;public StatViewServlet() {}
}

StatViewServlet

com.alibaba.druid.support.http.StatViewServlet

这个类里面指定了静态资源的存放地址，

在jar包中

public class StatViewServlet extends ResourceServlet {private static final Log LOG = LogFactory.getLog(StatViewServlet.class);private static final long serialVersionUID = 1L;public static final String PARAM_NAME_RESET_ENABLE = "resetEnable";public static final String PARAM_NAME_JMX_URL = "jmxUrl";public static final String PARAM_NAME_JMX_USERNAME = "jmxUsername";public static final String PARAM_NAME_JMX_PASSWORD = "jmxPassword";private DruidStatService statService = DruidStatService.getInstance();private String jmxUrl = null;private String jmxUsername = null;private String jmxPassword = null;private MBeanServerConnection conn = null;public StatViewServlet() {super("support/http/resources");}
}

3、测试验证

启动服务之后，我们访问http://localhost:8080/getAll之后，

打开druid的监控页面http://localhost:8080/druid/index.html

3.1、getAll

访问getAll，浏览器正常返回

3.2、druid监控台

如果设置了访问密码的话，则需要登录，如下：

登录成功之后，可以在上面选择选项卡，查看我们需要的内容，

例如这里，我们可以查看URI的访问记录，如下：