MO_ Or integrating Druid with spring boot

Time:2020-10-19

1、 Druid?

1.1 what is Druid

  • Druid is a very good database connection pool at present. In terms of function, performance and scalability, Druid surpasses other database connection pools, including DBCP, c3p0, bonecp, Proxool and JBoss datasource
  • Druid supports all JDBC compatible databases, including Oracle, mysql, Derby, PostgreSQL, SQL server, H2, etc., and Druid has made special optimization for Oracle and MYSQL, such as Oracle’s PS cache memory utilization optimization, MySQL Ping detection optimization
  • Through the monitoring function provided by Druid, the execution time of SQL, the holding time of resultset, the number of returned rows, the number of updated rows, the number of errors, and the error stack information can be monitored. The working conditions of connection pool and SQL can be clearly known, and the execution performance of SQL can be counted in detail, which is helpful for online analysis of database access performance

1.2 the role of Druid

  1. Replace DBCP and c3p0. Druid provides an efficient, powerful and scalable database connection pool
  2. It can monitor database access performance. Druid provides a powerful statfilter plug-in, which can make detailed statistics of SQL execution performance, which is helpful for online analysis of database access performance
  3. Database password encryption. It is a bad behavior to write the database password in the configuration file directly, which is easy to cause security problems. Both druiddruiver and druiddatasource support passwordcallback
  4. For SQL execution log, Druid provides different logfilters, which can support common logging, log4j and jdklog. You can select the corresponding logfilter as needed to monitor the database access of your application
  5. Extend JDBC. If you want to have programming requirements for JDBC layer, you can use filter mechanism provided by druid to write JDBC layer extension plug-ins conveniently

Summary

  1. Act as a database connection pool
  2. Can monitor database access performance
  3. Get SQL execution log

2、 Imported Maven dependencies

<! -- integrate Druid -- >
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.10</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-configuration-processor</artifactId>
    <optional>true</optional>
</dependency>
<! -- integrate Druid -- >

3、 Configure related data sources

MO_ Or integrating Druid with spring boot

Note the yellow background parameter in the red box because there is no corresponding attribute mapping in the entity class
In case some parameters are not valid, the attributes of the entity class need to correspond to the parameters in the data source one by one

Here is application.yml Related configuration in

spring:
  datasource:
    #Configure Druid connection pool
    druid:
      username: root
      password: root
      url: jdbc:mysql://localhost:3306/MOxor?serverTimezone=UTC
      driver-class-name: com.mysql.cj.jdbc.Driver
      initialSize: 5
      minIdle: 5
      maxActive: 20
      maxWait: 60000
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 1 FROM DUAL
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      poolPreparedStatements: true
      #Configure the filters that are intercepted by monitoring statistics. After the filters are removed, the monitoring interface SQL cannot make statistics, and 'wall' is used for firewalls
      filters: stat,wall
      maxPoolPreparedStatementPerConnectionSize: 20
      useGlobalDataSourceStat: true
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

Here is the entity class druiddatasourceproperties

@NoArgsConstructor
@AllArgsConstructor
@Getter
@Setter
@ToString
@ConfigurationProperties(prefix = " spring.datasource.druid ") // scan the property prefix of configuration class
public class DruidDataSourceProperties {

    private String username;
    private String password;
    private String url;
    private String driverClassName;
    private Integer initialSize;
    private Integer maxActive;
    private Integer minIdle;
    private long maxWait;
    private boolean poolPreparedStatements;
    private Integer timeBetweenEvictionRunsMillis;
    private Integer minEvictableIdleTimeMillis;
    private String validationQuery;
    private boolean testWhileIdle;
    private boolean testOnBorrow;
    private boolean testOnReturn;
    private String filters;
    private Integer maxPoolPreparedStatementPerConnectionSize;
    private boolean useGlobalDataSourceStat;
    private String connectionProperties;
    
}

explain:
The following comments are Lombok’s methods to simplify the creation of get, set, toString, and constructor of entity classes

@NoArgsConstructor
@AllArgsConstructor
@Getter
@Setter
@ToString

The purpose of this note is to simplify application.yml There is no need to use @ value annotation on each attribute

@ConfigurationProperties(prefix = " spring.datasource.druid ") // scan the property prefix of configuration class

4、 Custom configuration class

@Configuration
@EnableConfigurationProperties(value = DruidDataSourceProperties.class)
public class DruidDataSourceConfig {

    @Autowired
    private DruidDataSourceProperties druidDataSourceProperties;

    @Bean
    public DataSource dataSource() throws SQLException {
        System.out.println(druidDataSourceProperties);
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUsername(druidDataSourceProperties.getUsername());
        druidDataSource.setPassword(druidDataSourceProperties.getPassword());
        druidDataSource.setUrl(druidDataSourceProperties.getUrl());
        druidDataSource.setDriverClassName(druidDataSourceProperties.getDriverClassName());
        druidDataSource.setInitialSize(druidDataSourceProperties.getInitialSize());
        druidDataSource.setMinIdle(druidDataSourceProperties.getMinIdle());
        druidDataSource.setFilters(druidDataSourceProperties.getFilters());
        druidDataSource.setMaxActive(druidDataSourceProperties.getMaxActive());
        druidDataSource.setMaxWait(druidDataSourceProperties.getMaxWait());
        druidDataSource.setPoolPreparedStatements(druidDataSourceProperties.isPoolPreparedStatements());
        return druidDataSource;
    }

    /**
     *Configure the servlet * @ return of Druid management background
     */
    @Bean
    public ServletRegistrationBean statViewServlet() {
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        Map<String, Object> initParameters = new HashMap<>();
        initParameters.put("loginUsername", "admin");
        initParameters.put("loginPassword", "123456");
        bean.setInitParameters(initParameters);
        return bean;
    }

    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
        filterRegistrationBean.setUrlPatterns(Arrays.asList("/*"));
        Map<String, Object> initParams = new HashMap<>();
        initParams.put("exclusions", "*.js,*.css,/druid/*");
        filterRegistrationBean.setInitParameters(initParams);
        return filterRegistrationBean;
    }

}

be careful:
Statviewservlet() and filterregistrationbean() are not methods that must be configured

However, it is highly recommended to add it here, because the background monitoring interface of Druid can only be used after configuration

But pay attention to the application.yml Must have filters: stat parameter in, and ensure that the entity class can be mapped correctly. Finally, datasource() contains druidDataSource.setFilters

5、 Testing

@RestController
public class DruidTestController {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @RequestMapping("/testJdbc")
    public List<Map<String, Object>> testJdbcTemplate() {
        List<Map<String, Object>> testList = jdbcTemplate.queryForList("select * from test");
        return testList;
    }
}
Request path: http://localhost :8081/testJdbc
The author uses chrome and fehelper plug-in to beautify JSON format data

MO_ Or integrating Druid with spring boot
Fehelper plug-in download

Through the path: http://localhost :8081/druid
Take a look at the Druid monitoring interface. The account password is:
Loginusername and loginpassword in statviewservlet()

MO_ Or integrating Druid with spring boot

Enter the interface and select "SQL monitor" to monitor the SQL just executed!

MO_ Or integrating Druid with spring boot

6、 Reference

  • Druid
  • About Druid

7、 Finally

If there is any deficiency, please correct it
An open mind is a fool and a thirst for knowledge

Recommended Today

Blog based on beego, go blog

Go Blog A beego based development, can quickly create personal blog, CMS system Include functions see Official website of go bloggo-blog.cn Demo siteleechan.online Update log time function January 23, 2020 New top post function February 2, 2020 New custom navigation function February 4, 2020 New site announcement function February 6, 2020 New link module February […]