SpringBoot集成Druid

SpringBoot集成Druid

先添加druid依赖

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

application.properties相关配置

spring.datasource.url=jdbc:mysql://127.0.0.1:3306/common?characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

###  数据源类别
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
###  初始化大小, 最小, 最大
spring.datasource.druid.initial-size=5
spring.datasource.druid.min-idle=5
spring.datasource.druid.max-active=20

### 配置获取连接等待超时时间, 单位是毫秒
spring.datasource.druid.max-wait=60000
### 配置间隔多久才进行一次检测, 检测需要关闭的空闲连接, 单位是毫秒
spring.datasource.druid.time-between-eviction-runs-millis=60000
### 配置一个连接在池中最小生存时间, 单位是毫秒
spring.datasource.druid.min-evictable-idle-time-millis=300000
spring.datasource.druid.validation-query=select 1 from dual
spring.datasource.druid.test-while-idle=true
spring.datasource.druid.test-on-borrow=false
spring.datasource.druid.test-on-return=false
###  打开PSCache, 并指定每个连接上PSCache的大小
spring.datasource.druid.pool-prepared-statements=true
spring.datasource.druid.max-pool-prepared-statement-per-connection-size=20
###  配置监控统计拦截的filters, 去掉后监控界面的SQL无法统计, 'wall'用于防火墙
spring.datasource.druid.filters=stat,wall
### 通过connectProperties属性来打开mergeSql功能, 慢SQL记录
spring.datasource.druid.connection-properties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
### 合并多个DruidDataSource的监控数据
#spring.datasource.druid.use-global-data-source-stat=true

注册servlet和filter

@Configuration
public class Config {
    @Bean
    public ServletRegistrationBean druidStatViewServlet(){
        //提供类的进行注册
        ServletRegistrationBean servletRegistrationBean
                = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        //添加初始化参数, initParams
        //白名单,允许
        servletRegistrationBean.addInitParameter("allow", "127.0.0.1");
      
        //开启后,127.0.0.1访问时会被拒绝
//        servletRegistrationBean.addInitParameter("deny", "127.0.0.1");
        //IP黑名单 (存在共同时, deny优先于allow)
        //如果满足deny, 就提示
        //登录查看信息的账号和密码
        servletRegistrationBean.addInitParameter("loginUsername", "admin");
        servletRegistrationBean.addInitParameter("loginPassword", "123456");
        //是否能够重置数据
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        return servletRegistrationBean;
    }

    @Bean
    public FilterRegistrationBean druidStatFilter(){
        FilterRegistrationBean filterRegistrationBean
                = new FilterRegistrationBean(new WebStatFilter());
        //添加过滤规则
        filterRegistrationBean.addUrlPatterns("/*");
        //添加需要忽略的格式信息
        filterRegistrationBean.addInitParameter("exclusions",
                "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return filterRegistrationBean;
    }
}

我们设置了允许127.0.0.1的ip来访问我们的servlet

并且设置了可以使用 admin 和 123456 作为账号密码来访问druid的web管理界面

启动应用

在浏览器输入

http://127.0.0.1:8080/druid/index.html便可访问druid管理界面

如果设置以下参数

servletRegistrationBean.addInitParameter("deny", "127.0.0.1"); 那么访问如下:

现在我们创建5个线程来连接数据库

@Component
public class BackJob {
    @Resource
    JdbcTemplate jdbcTemplate;

    ExecutorService executorService = Executors.newFixedThreadPool(5);

    @Scheduled(fixedDelay = 1000)
    public void test(){
        start();
    }

    public void start(){
        for (int i = 0; i < 5; i++) {
            executorService.submit(new Runnable() {
                @Override
                public void run() {
                    execQuery();
                }
            });
        }
    }

    public void execQuery(){
        String sql = "select * from user";
        List<User> userList = jdbcTemplate.query(sql, new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet resultSet, int i) throws SQLException {
                User user = new User();
                user.setId(resultSet.getInt(resultSet.findColumn("id")));
                user.setAge(resultSet.getInt(resultSet.findColumn("age")));
                user.setName(resultSet.getString(resultSet.findColumn("name")));
                user.setEmail(resultSet.getString(resultSet.findColumn("email")));
                return user;
            }
        });
        System.out.println(userList);
    }
}

上面的User类

public class User {
    private int id;
    private String name;
    private int age;
    private String email;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", email='" + email + '\'' +
                '}';
    }
}

我的数据库信息

注意,使用 @Scheduled(fixedDelay = 1000) 表示每隔1秒执行一次, 要使用这个注解, 还需要在Application类上, 加上 @EnableScheduling

@SpringBootApplication
@EnableScheduling
public class MysqlApplication {

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

}

---

也就是说, 现在每秒有5个线程进行连接数据库查询, 然后我们去web控制台查看一下:

---

Over

如果觉得需要更多技术干货, 来我的CSDN 和 GitHub哦

个人博客: https://uweii.github.io/
Github: https://github.com/uweii