Spring Boot — Integrate Druid data sources

1. What is Druid

• A large portion of Java programs operate on databases, and to improve performance, they have to use database connection pools.

• Druid is a database connection pool implementation on Alibaba’s open source platform, but it is more than just a database connection pool. It also includes a ProxyDriver, a set of built-in JDBC component libraries, and a SQL Parser.

• Druid combines the advantages of C3P0 and DBCP, as well as log monitoring, to monitor DATABASE connections and SQL execution.

• Druid only supports JDK 6 and above, not JDK 1.4 and JDK 5.0.

• Making the address

Which databases Druid supports

Druid supports all JDBC-compatible databases, including Oracle, MySql, Derby, Postgresql, SQL Server, H2, and more.

Druid provides special optimizations for Oracle and MySql, such as PS Cache usage optimization for Oracle and ping detection optimization for MySql.

How does Druid extend JDBC

Druid provides an extended FILter-chain API on DruidDataSourc and ProxyDriver, which is similar to Serlvet’s Filter.

Druid’s SQL parsing capabilities

Druid provides complete SQL support for MySql, Oracle, Postgresql, AND SQL-92. It is a hand-written, high-performance SQL Parser with Visitor schema that makes it easy to parse abstract syntax trees of SQL.

Simple SQL statements took less than 10 microseconds and complex SQL took 30 microseconds.

The SQL Parser provided by Druid can intercept SQL in the JDBC layer for processing, such as library, table, audit, etc. Druid’s WallFilter protects against SQL injection attacks by parsing semantics using Druid’s SQL Parser.

Druid’s extensibility

Druid provides a filter-chain plugin framework that can be extended by writing a Filter configuration to the Druid datasource to intercept JDBC apis. Druid provides a number of built-in filters.

Druid’s advantages in SQL injection defense

Druid’s advantage is that it does interception at the lowest JDBC level.

Druid implements Parser for Oracle, MySql, Postgresql, and SQL-92. It is implemented based on SQL syntax analysis and understands SQL semantics. It is intelligent, accurate, and has a low false positive rate.

How do I configure Druid

Druid’s configuration is basically the same as that of DBCP. If you’re already using DBCP, it’s easy to migrate. Just put corg.apache.com mons. DBCP. BasicDataSource modification for om. Alibaba. The druid. Pool. DruidDataSource.

Com. Alibaba. Druid. Pool. DruidDataSource basic configuration parameters as follows (the default value: the system default) :

In DruidDataSource, you don’t have to configure DriverClass, it automatically identifies by url. Druid automatically identifies more than 20 urls, including common JDBC drivers.

2. Configure the Druid data source

1. Import maven dependencies:

<! -- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.6</version>
</dependency>

Copy the code

2. Switch the specified data source

spring:
  datasource:
    username: root
    password: 19990802
    url: jdbc:mysql://localhost:3306/mybatis? useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT
    driver-class-name: com.mysql.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
# type Specifies the data source type
Copy the code

3. Test whether the data source is specified successfully

    @Autowired
    DataSource dataSource;
    @Test
    void contextLoads(a) {
        // View the data source
        System.out.println(dataSource.getClass());
        // Get the database connection
        try {
            Connection connection = dataSource.getConnection();
            System.out.println(connection);
            // Close the connection
            connection.close();

        } catch(SQLException e) { e.printStackTrace(); }}Copy the code

Switch success

4. Set the Druid configuration

spring:
  datasource:
    username: root
    password: 19990802
    url: jdbc:mysql://localhost:3306/mybatis? useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT
    driver-class-name: com.mysql.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
# type Specifies the data source type
    #Spring Boot does not inject these property values by default, you need to bind them yourself
    #druid Data source proprietary configuration

    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 filters to monitor statistics interception, stat: monitor statistics, log4j: logging, wall: defend against SQL injection
    # if allowed times wrong Java. Lang. ClassNotFoundException: org.. Apache log4j. Priority
    # import log4j dependence can, Maven address: https://mvnrepository.com/artifact/log4j/log4j
    filters: stat,wall,log4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true; druid.stat.slowSqlMillis=500



Copy the code

Since we used Log4j above, we import log4j’s dependencies

<! -- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>
Copy the code

3. Configure Druid data source monitoring

The Druid data source has monitoring capabilities and provides a Web interface for easy viewing

1. Configure the Servlets in the Druid monitoring management background

package com.cheng.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.util.HashMap;

@Configuration
public class DruidConfig {

    @Bean
    @ConfigurationProperties(prefix ="spring.datasource")// Bind to the configuration file
    public DataSource druidDataSource(a){
        return new DruidDataSource();
    }
    // Get background monitoring
    @Bean
    // Since SpringBoot starts embedded Servlet containers as jar packages by default to start SpringBoot Web applications, there is no web.xml file. So to use the Servlet function, you must borrow the ServletRegistrationBean interface provided by Spring Boot.
    public ServletRegistrationBean servletRegistration(a){
        ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(),"/druid/*")   //StatViewServlet displays Druid statistics
            
        // Set the account and password for background login
        HashMap<String, String> initParameters = new HashMap<>();

        // Add configuration login key is fixed
        initParameters.put("loginUsername"."admin");
        initParameters.put("loginPassword"."123456");

        // Set who can access
        initParameters.put("allow"."");// Anyone can access it

        // Set the initialization parameters
        bean.setInitParameters(initParameters);

        returnbean; }}Copy the code

2. Start SpringBoot test and accesshttp://localhost:8080/druid

Home page:

Execute an SQL statement to see the implementation of SQL monitoring

4. Configure the filter filter

WebStatFilter: Used to configure management association monitoring statistics between Web and Druid data sources

@Bean
public FilterRegistrationBean webStartFilter(a){
    FilterRegistrationBean bean = new FilterRegistrationBean();
    bean.setFilter(new WebStatFilter());

    // Requests that can be filtered
    HashMap<String, String> initParameters = new HashMap<>();

    // Eliminate unnecessary urls
    initParameters.put("exclusions"."*.js,*.css,/druid/*");
    
    bean.setInitParameters(initParameters);// Set the initialization parameters
    return bean;
}
Copy the code

For more information about Druid, see the Druid manual