Categories
Uncategorized

SpringBoot integration Druid

Has recently been tossing springboot, also based on the need to integrate a number of useful libraries, mybatis as a continuous layer, data manipulation, and I want to do with what the famous Druid connection pool and database monitoring service. So one afternoon to explore, sorting out this small article, hoping to help fellow, but also to reflect on their own to be a record.

    1. About Druid

Druid is open-source, high-performance team Ali database connection pool, extensive domestic use, especially in monitoring and sql database performance is very powerful.

Connection pooling can effectively save the database connection consumption and high write, high real-time requirement is very appropriate.

    2. introducing into the pom.xml dependency.


   com.alibaba
   druid-spring-boot-starter
   1.1.10

    3. Set the configuration in application.yml

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/test_go?characterEncoding=UTF-8&useSSL=false
    username: xxx
    password: xxxxxx
    driver-class-name: com.mysql.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      initial-size: 5
      min-idle: 5
      max-active: 20
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 20
      max-wait: 60000
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 30000
      filters: stat
      async-init: true

The most important thing is

type: com.alibaba.druid.pool.DruidDataSource

So you do not use the default connection pool Springboot Hikari.

Since the entry into force of SpringBoot not written in yml configuration file, you need to write a separate Bean file to load.

    4. Define Druid configuration class.

package com.tony.testspringboot.config;


import com.alibaba.druid.pool.DruidDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;
import java.sql.SQLException;

@Configuration
public class DruidConfig {
    private Logger logger = LoggerFactory.getLogger(DruidConfig.class);

    @Value("${spring.datasource.url}")
    private String dbUrl;

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;
    @Value("${spring.datasource.test-druid.driver-class-name}")
    private String driverClassName;

    @Value("${spring.datasource.druid.initial-size}")
    private int initialSize;

    @Value("${spring.datasource.druid.min-idle}")
    private int minIdle;

    @Value("${spring.datasource.druid.max-active}")
    private int maxActive;

    @Value("${spring.datasource.druid.max-wait}")
    private int maxWait;

    @Value("${spring.datasource.druid.time-between-eviction-runs-millis}")
    private int timeBetweenEvictionRunsMillis;

    @Value("${spring.datasource.druid.min-evictable-idle-time-millis}")
    private int minEvictableIdleTimeMillis;

    @Value("${spring.datasource.druid.test-while-idle}")
    private boolean testWhileIdle;

    @Value("${spring.datasource.druid.test-on-borrow}")
    private boolean testOnBorrow;

    @Value("${spring.datasource.druid.test-on-return}")
    private boolean testOnReturn;

    @Value("${spring.datasource.druid.pool-prepared-statements}")
    private boolean poolPreparedStatements;

    @Value("${spring.datasource.druid.max-pool-prepared-statement-per-connection-size}")
    private int maxPoolPreparedStatementPerConnectionSize;

    @Value("${spring.datasource.druid.filters}")
    private String filters;

    @Bean
    @Primary
    public DataSource dataSource() {
        DruidDataSource datasource = new DruidDataSource();

        datasource.setUrl(this.dbUrl);
        datasource.setUsername(this.username);
        datasource.setPassword(this.password);
        datasource.setDriverClassName(this.driverClassName);

        // configuration
        datasource.setInitialSize(this.initialSize);
        datasource.setMinIdle(this.minIdle);
        datasource.setMaxActive(this.maxActive);
        datasource.setMaxWait(this.maxWait);
        datasource.setTimeBetweenEvictionRunsMillis(this.timeBetweenEvictionRunsMillis);
        datasource.setMinEvictableIdleTimeMillis(this.minEvictableIdleTimeMillis);
        datasource.setTestWhileIdle(this.testWhileIdle);
        datasource.setTestOnBorrow(this.testOnBorrow);
        datasource.setTestOnReturn(this.testOnReturn);
        datasource.setPoolPreparedStatements(this.poolPreparedStatements);
        datasource.setMaxPoolPreparedStatementPerConnectionSize(this.maxPoolPreparedStatementPerConnectionSize);

        try {
            datasource.setFilters(this.filters);
        } catch (SQLException e) {
            logger.error("druid configuration init fail!");
        }

        return datasource;
    }
}

So you can make configuration parameters take effect and as the preferred DataSource for use.

5. Test in the Controller.

JdbcTemplate can use to query.
    Test code is as follows:

@RequestMapping(value = "/hey", method = RequestMethod.GET)
public ResultResponse testDruid() {

    String sql = "SELECT mobile FROM user WHERE id = ?";

    String mobile = jdbcTemplate.queryForObject(sql, new Object[]{1}, String.class);

    return new ResultResponse(201, "hey" + mobile);
}

PS: ResultResponse common response is the object of my project package.

Set parameters on multiple data sources.

Only the settings in the yml (application.yml), the following format:

datasource:
    one-source:
      url: jdbc:mysql://127.0.0.1:3306/test_go?characterEncoding=UTF-8&useSSL=false
      username: xxxx1
      password: xxxx
      driver-class-name: com.mysql.jdbc.Driver
      type: com.alibaba.druid.pool.DruidDataSource
    two-source:
      url: jdbc:mysql://127.0.0.1:3306/demo2?characterEncoding=UTF-8&useSSL=false
      username: xxxx
      password: 1xxxxxx
      driver-class-name: com.mysql.jdbc.Driver
      type: com.alibaba.druid.pool.DruidDataSource
    ...

When used also follow this structure, such as to obtain a first data source url @Value configure the corresponding item in DruidConfig.java file in write:

@Value("${spring.datasource.one-source.url}")
private String dbUrl;

    6. Configure database monitoring.

a) first increase in application.yml follows:

druid:
   .....
   # 通过connectProperties属性来打开mergeSql功能;慢SQL记录
   connection-properties: druid.stat.mergeSql=true;druid.stat.SlowSqlMills=5000
   # 监控后台的配置,如登录账号和密码等
   monitor:
     allow: 127.0.0.1
     loginUsername: admin
     loginPassword: admin

b) Preparation of DruidMonitorConfiguration class alone.

public class DruidMonitorConfiguration {

    @Value("${spring.datasource.druid.monitor.allow}")
    private String allow;
//    @Value("${spring.datasource.druid.monitor.deny}")
//    private String deny;
    @Value("${spring.datasource.druid.monitor.loginUsername}")
    private String loginUsername;
    @Value("${spring.datasource.druid.monitor.loginPassword}")
    private String loginPassword;
    @Value("${spring.datasource.druid.monitor.resetEnable")
    private String resetEnable;
    @Bean
    public ServletRegistrationBean druidStatViewServlet() {
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        servletRegistrationBean.addInitParameter("allow", this.allow);
//        servletRegistrationBean.addInitParameter("deny", this.deny);
        servletRegistrationBean.addInitParameter("loginUsername", this.loginUsername);
        servletRegistrationBean.addInitParameter("loginPassword", this.loginPassword);
        servletRegistrationBean.addInitParameter("resetEnable", this.resetEnable);
        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;
    }

}

Access Method is: http: //project-name.com/druid/login.html

Sign in to verify. The entire monitoring function is very powerful, sql monitoring, URI monitoring, Session Monitoring, Web applications, and so on.

Leave a Reply