SpringBoot整合MyBatis、通过Druid进行数据库访问监控

最新推荐文章于 2022-01-13 17:29:17 发布

CoderBruis

最新推荐文章于 2022-01-13 17:29:17 发布

阅读量1.3k

点赞数 1

分类专栏：《SpringBoot基础与源码》文章标签： SpringBoot Druid

本文链接：https://blog.csdn.net/CoderBruis/article/details/90267122

版权

《SpringBoot基础与源码》专栏收录该内容

7 篇文章 4 订阅

订阅专栏

前言

前一篇介绍了SpringBoot整合MyBatis的三种方式，整合了MyBatis之后，可以通过Druid来对数据库操作进行监控。

正题

一、整合MyBatis

先通过IDEA新建web项目，勾选好如下几个模块，如下图：

mapper接口

import com.sbwithmybatisweb.model.User;
import java.util.List;
public interface UserMapper {
    List<User> getUser();
}

mapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC
        "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sbwithmybatisweb.mapper.UserMapper">
    <select id="getUser" resultType="com.sbwithmybatisweb.model.User">
        select * from tb_user
    </select>
</mapper>

application.yml

mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.sbwithmybatis2.model
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #配置mybatis输出日志

引入log4j配置文件，输出MyBatis语句

# log4j配置文件
#控制日志级别，在哪里输出Output pattern : date [thread] priority category - message   FATAL 0  ERROR 3  WARN 4  INFO 6  DEBUG 7
log4j.rootLogger=info,ServerDailyRollingFile,stdout
log4j.appender.ServerDailyRollingFile=org.apache.log4j.DailyRollingFileAppender
log4j.appender.ServerDailyRollingFile.DatePattern='.'yyyy-MM-dd
# linux下目录为/
log4j.appender.ServerDailyRollingFile.File=D://logs/log.log
log4j.appender.ServerDailyRollingFile.layout=org.apache.log4j.PatternLayout
log4j.appender.ServerDailyRollingFile.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} [%t] %-5p [%c] - %m%n
log4j.appender.ServerDailyRollingFile.Append=true
#控制台  显示的的方式为控制台普通方式
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
#日志输出的格式
log4j.appender.stdout.layout.ConversionPattern=%d %-5p [%c{5}] - %m%n
#打印sql部分
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Connection = DEBUG  
log4j.logger.java.sql.Statement = DEBUG  
log4j.logger.java.sql.PreparedStatement = DEBUG  
log4j.logger.java.sql.ResultSet = DEBUG
log4j
#配置logger扫描的包路径  这样才会打印sql
log4j.logger.com.sbwithmybatis2.mapper=DEBUG

引入Druid配置，在application.yml中添加如下配置

spring:
  datasource:
    username: root
    password: bruis
    url: jdbc:mysql://localhost:3306/jdy?characterEncoding=UTF-8
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    ### 数据源的其他配置
    initial-size: 5
    min-idle: 5
    max-active: 20
    tomcat:
      max-wait: 60000
    time-between-eviction-runs-millis: 60000
    min-evictable-idle-time-millis: 300000
    validation-query: SELECT 1 FROM DUAL
    test-while-idle: true
    test-on-borrow: false
    test-on-return: false
    pool-prepared-statements: true
    ### 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    filters: stat, wall, log4j
    max-pool-prepared-statement-per-connection-size: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

需要配置druid的监控页面，就需要在Druid配置文件中添加druidStatViewServlet()、druidWebStatFilter()两个方法。

DruidConfig配置文件

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.boot.context.properties.ConfigurationProperties;
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.support.PropertySourcesPlaceholderConfigurer;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import javax.sql.DataSource;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
@Configuration
public class DruidConfig {
    //将所有前缀为spring.datasource下的配置项都加载到DataSource中
    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource dataSource() {
        return new DruidDataSource();
    }
    @Bean
    public static PropertySourcesPlaceholderConfigurer propertyConfigure(){
        return new PropertySourcesPlaceholderConfigurer();
    }
    @Bean
    public PlatformTransactionManager transactionManager() throws Exception {
        DataSourceTransactionManager txManager = new DataSourceTransactionManager();
        txManager.setDataSource(dataSource());
        return txManager;
    }
    @Bean
    public ServletRegistrationBean druidStatViewServlet() {
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(),"/druid/*");
        Map<String, String> initParams = new HashMap<>();
        //　可配的属性都在 StatViewServlet 和其父类下
        initParams.put("loginUsername", "admin-druid");
        initParams.put("loginPassword", "111111");
        servletRegistrationBean.setInitParameters(initParams);
        return servletRegistrationBean;
    }
    @Bean
    public FilterRegistrationBean druidWebStatFilter() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
        Map<String, String> initParams = new HashMap<>();
        initParams.put("exclusions", "*.js,*.css,/druid/*");
        filterRegistrationBean.setInitParameters(initParams);
        filterRegistrationBean.setUrlPatterns(Arrays.asList("/*"));
        return filterRegistrationBean;
    }
}

MyBatisDataSourceConfig.java

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import javax.sql.DataSource;
public class MyBatisDataSourceConfig {
    @Autowired
    private DataSource dataSource;
    @Bean(name="sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactoryBean() {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        // 添加XML目录
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        try {
            bean.setMapperLocations(resolver.getResources("classpath:mapper/*.xml"));
            SqlSessionFactory sqlSessionFactory = bean.getObject();
            sqlSessionFactory.getConfiguration().setCacheEnabled(Boolean.TRUE);

            return sqlSessionFactory;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    @Bean
    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

MyBatisMapperScannerConfig.java

import org.mybatis.spring.mapper.MapperScannerConfigurer;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
@AutoConfigureAfter(MyBatisDataSourceConfig.class)
public class MyBatisMapperScannerConfig {
    @Bean
    public MapperScannerConfigurer mapperScannerConfigurer() {
        MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
        mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactory");
        mapperScannerConfigurer.setBasePackage("com.sbwithmybatisweb.mapper");
        return mapperScannerConfigurer;
    }
}

以上就完成了SpringBoot整合MyBatis的代码。

二、引入Swagger

通过Swagger来调用项目的接口。

2.1 添加Swagger2依赖

<dependency>
    <groupId>io.springfox</groupId>
    <artifactId>springfox-swagger2</artifactId>
    <version>2.6.1</version>
</dependency>
<dependency>
    <groupId>io.springfox</groupId>
    <artifactId>springfox-swagger-ui</artifactId>
    <version>2.6.1</version>
</dependency>

2.2 添加Swagger2配置类

在XXXApplication.java同级目录创建类Swagger2，basePackage指定将生成文档的接口路径。

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;
import springfox.documentation.swagger2.annotations.EnableSwagger2;

@Configuration
@EnableSwagger2
public class Swagger2 {
    @Bean
    public Docket createRestApi() {
        return new Docket(DocumentationType.SWAGGER_2)
                .apiInfo(apiInfo())
                .select()
                .apis(RequestHandlerSelectors.basePackage("com.sbwithmybatisweb"))
                .paths(PathSelectors.any())
                .build();
    }
    private ApiInfo apiInfo() {
        return new ApiInfoBuilder()
                .title("sbwithmybatisweb")
                .description("服务名：sbwithmybatisweb")
                .version("1.0")
                .build();
    }
}

3 接口使用

@ApiOperation(value = "invoke", notes = "invoke测试")
@ApiImplicitParam(name = "param", value = "invoke入参", required = true, dataType = "String", paramType = "query")
@GetMapping(value = "/invoke")
public String invoke(String param) {
    System.out.println("This springCloudConsumerService 1:" + param);
    String result = param + "_consumer_" + System.currentTimeMillis();
    return result;
}

4 访问swagger管理页面

访问 http://localhost:port/swagger-ui.html，看到如下界面即表示成功。

5 在代码的controller中添加swagger注解

UserService.java

import com.sbwithmybatisweb.mapper.UserMapper;
import com.sbwithmybatisweb.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserService {
    @Autowired
    UserMapper userMapper;
    public List<User> getUsers() {
        return userMapper.getUser();
    }
}

UserController.java

import com.sbwithmybatisweb.model.User;
import com.sbwithmybatisweb.service.UserService;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
@Slf4j
@RestController
public class UserController {
    @Autowired
    UserService userService;
    @ApiOperation(value = "获取用户信息", notes = "获取用户信息")
    @GetMapping("getUsers")
    public String getUsers(@RequestParam String operator) {
        log.info("===================="+operator);
        StringBuilder res = new StringBuilder();
        for (User user : userService.getUsers()) {
            res.append(user.getName());
        }
        return res.toString();
    }
}

输入网址：http://localhost:8080/swagger-ui.html#/

进入Swagger页面，如图：

这里调用了两次接口，结果都被Druid进行了监控。先进入Druid监控页面

输入：
http://localhost:8080/druid/login.html

登录进入Druid监控首页

SQL语句调用的监控结果：

URI调用的监控结果，注意，Swagger的URI也被监控了：

至此，SpringBoot整合MyBatis并通过Druid进行数据库访问监控的实现完成。

CoderBruis

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
SpringBoot整合MyBatis、通过Druid进行数据库访问监控

前言前一篇介绍了SpringBoot整合MyBatis的三种方式，整合了MyBatis之后，可以通过Druid来对数据库操作进行监控。正题一、整合MyBatis先通过IDEA新建web项目，勾选好如下几个模块，如下图：mapper接口import com.sbwithmybatisweb.model.User;import java.util.List;public interf...
复制链接

扫一扫

专栏目录