使用Druid实现SQL监控实例

1.导入druid

新建Spring Boot项目
导入MySQL Driver

导入druid

		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid-spring-boot-starter</artifactId>
			<version>1.1.21</version>
		</dependency>

2.配置文件

修改application.properties为application.yml 依照个人习惯

配置如下：

server:
  port: 8080
spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://localhost:3306/gm?serverTimezone=Asia/Shanghai
      username: 用户名
      password: 密码
      initial-size: 5
      min-idle: 5
      max-active: 20
      #获取连接等待超时的时间
      max-wait: 60000
      #是否在连接空闲一段时间后检测其可用性
      test-while-idle: true
      #是否在获得连接后检测其可用性
      test-on-borrow: false
      #是否在连接放回连接池后检测其可用性
      test-on-return: false
      #是否缓存preparedStatement
      pool-prepared-statements: false
      #监控统计拦截的filters
      filters: stat,wall
      #DruidStatFilter
      web-stat-filter:
        enabled: true
        url-pattern: /*
        exclusions: .js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*
      #监控服务器
      stat-view-servlet:
        enabled: true
        allow:
        login-username: root
        login-password: 123456
        reset-enable: false
        url-pattern: /druid/*

3.测试

导入mybatis库

		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.1.3</version>
		</dependency>

配置文件新增

mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.gm.druidtest.entity

启动类添加 @MapperScan

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.gm.druidtest.dao")
public class DruidTestApplication {

	public static void main(String[] args) {
		SpringApplication.run(DruidTestApplication.class, args);
	}

}

建立简单测试表

新建dao、entity、service

先放一个整体结构

---

代码如下：

• 实体类

public class User {

    private String name;

    private String phone;
	
	//GET、SET
}

• dao

import com.gm.druidtest.entity.User;

import java.util.List;

public interface UserDao {

    List<User> queryAll();

}

• service

import com.gm.druidtest.entity.User;

import java.util.List;

public interface UserService {

    List<User> queryAll();

}

• service实现

import com.gm.druidtest.dao.UserDao;
import com.gm.druidtest.entity.User;
import com.gm.druidtest.service.UserService;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

@Service
public class UserServiceImpl implements UserService {

    @Resource
    private UserDao userDao;

    @Override
    public List<User> queryAll() {
        return userDao.queryAll();
    }
}

• Controller

import com.gm.druidtest.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("user")
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping("query")
    public Object queryAll() {
        return userService.queryAll();
    }

}

配置UserMapper.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.gm.druidtest.dao.UserDao">

    <resultMap type="com.gm.druidtest.entity.User" id="UserMap">
        <result property="name" column="name" jdbcType="VARCHAR"/>
        <result property="phone" column="phone" jdbcType="VARCHAR"/>
    </resultMap>

    <!--查询所有-->
    <select id="queryAll" resultMap="UserMap">
        select
        name, phone
        from gm.user
    </select>

</mapper>

配置Druid：DruidConfiguration

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

//连接池配置
@Configuration
public class DruidConfiguration {

    @Bean
    //将所有前缀为spring.datasource下的配置项都加载DataSource中
    @ConfigurationProperties(prefix = "spring.datasource.druid")
    public DataSource druidDataSource() {
        return new DruidDataSource();
    }

}

启动项目

访问http://localhost:8080/druid

输入配置的用户名密码

注意现在点击数据源出现

这时访问 http://localhost:8080/user/query

再返回druid监控页面刷新