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

SpringBoot(伍)SpringBoot整合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.com', '备注');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10002', '李四', 18, 'W', '15988887777', 'lisi@163.com', '李四是张三的妹妹');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10003', '苍老师', 18, 'W', '15866668888', 'canglaoshi@163.com', '这里是苍老师的备注');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10004', '刘备', 30, 'M', '18666666666', 'liubei@163.com', '我是刘皇叔，专门卖草鞋');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10005', '关羽', 29, 'M', '18655555555', 'guanyu@163.com', '我是关羽，卖绿豆的');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10006', ' 孙悟空', 500, 'M', '19100002222', 'wukong@163.com', '我是孙悟空，我有金箍棒');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10007', ' 猪八戒', 488, 'M', '18900003333', 'bajie@163.com', '猪八戒');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10008', ' 沙僧', 200, 'M', '18678652388', 'shaseng@163.com', '我是沙僧');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10009', ' 唐僧', 25, 'M', '18766809012', 'shaseng@163.com', '我是唐僧，你们都是弟弟');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10010', ' 老树龙井', 20, 'W', '15988801234', 'longjing@163.com', 'laoshulongjing');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10011', ' 苏檀儿', 22, 'W', '18809826678', 'sutaner@163.com', '苏檀儿');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10012', ' 宁毅', 20, 'M', '15900008888', 'ningyi@163.com', '宁毅');
INSERT INTO david.t_student_info (id, name, age, sex, phone, email, rsv1) VALUES ('10013', ' 小婵', 18, 'W', '18566780092', 'xiaochan@163.com', '我是小婵');

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

    @Autowired
    StudentInfoService studentInfoService;

    @RequestMapping("/getAll")
    @ResponseBody
    public 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);

    @Autowired
    StudentInfoDAO studentInfoDAO;

    @Override
    public 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() {
    }
    @Bean
    public 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的访问记录，如下：