Druid is alibaba’s open source database connection pool, which provides excellent monitoring of database operations. This article explains how to integrate Druid into springBoot project.

This article was developed under a JPA-based project. First, we added additional Druid dependencies to the POM file as follows:

<? xml version="1.0" encoding="UTF-8"? > <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> < modelVersion > 4.0.0 < / modelVersion > < groupId > com. Dalaoyang < / groupId > < artifactId > springboot_druid < / artifactId > < version > 0.0.1 - the SNAPSHOT < / version > < packaging > jar < / packaging > < name > springboot_druid < / name > <description>springboot_druid</description> <parent> <groupId>org.springframework.boot</groupId> The < artifactId > spring - the boot - starter - parent < / artifactId > < version > 1.5.12. RELEASE < / version > < relativePath / > <! -- lookup parent from repository --> </parent> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> < project. Reporting. OutputEncoding > utf-8 < / project. Reporting. OutputEncoding > < Java version > 1.8 < / Java version > </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-devtools</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope>  </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.0.28</version> </dependency> </dependencies> <build> <plugins> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>Copy the code

The top half of the application. Properties section and the integrated JPA section remain unchanged, and some druid configurations are added below. If you don’t understand druid’s configuration, you can check it out online. (I think this article is very well written, portal)

# port
server.port=8888

##validate creates a database table structure when hibernate is loaded
Each time Hibernate is loaded, the database table structure is recreated, which is why the database table data is lost.
##create-drop Is created when hibernate is loaded
Hibernate automatically updates the database structure
##validate validates the structure of the table at startup. The table is not created
## None Does nothing when started
spring.jpa.hibernate.ddl-auto=create

## Console prints SQL
spring.jpa.show-sql=true

Database configuration
# database address
spring.datasource.url=jdbc:mysql://localhost:3306/test? characterEncoding=utf8&useSSL=false
Database user name
spring.datasource.username=root
Database password
spring.datasource.password=root
## Database driver
spring.datasource.driver-class-name=com.mysql.jdbc.Driver


# Here is different
Druid = spring.datasource. Type = spring.datasource. Type = spring.datasource
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource  


Connection pool configuration information
# initialize size, min, Max
spring.datasource.initialSize=5  
spring.datasource.minIdle=5  
spring.datasource.maxActive=20  
Set the connection wait timeout
spring.datasource.maxWait=60000  
Configure how often to detect idle connections that need to be closed, in milliseconds
spring.datasource.timeBetweenEvictionRunsMillis=60000  
Set the minimum time for a connection to live in the pool in milliseconds
spring.datasource.minEvictableIdleTimeMillis=300000  
spring.datasource.validationQuery=SELECT 1 FROM DUAL  
spring.datasource.testWhileIdle=true  
spring.datasource.testOnBorrow=false  
spring.datasource.testOnReturn=false  
Turn on PSCache and specify the size of PSCache on each connection
spring.datasource.poolPreparedStatements=true  
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20  
# Configure the filters for monitoring statistics interception. After removing the filters, the MONITORING interface SQL cannot be counted. 'wall' is used for the firewall
spring.datasource.filters=stat,wall,log4j
Enable mergeSql via connectProperties; Slow SQL record
Copy the code

Add DruidConfig to your project. For a brief introduction, this configuration file is used to load the application. Properties configuration as follows:

package com.dalaoyang.config;

import java.sql.SQLException;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
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 com.alibaba.druid.pool.DruidDataSource;
/**
 * @author dalaoyang
 * @Description
 * @project springboot_learn
 * @package com.dalaoyang.config
 * @email [email protected]
 * @date 2018/4/12
 */
@Configuration
public class DruidConfig {
    private Logger logger = Logger.getLogger(this.getClass());

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

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

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

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

    @Value("${spring.datasource.initialSize}")
    private int initialSize;

    @Value("${spring.datasource.minIdle}")
    private int minIdle;

    @Value("${spring.datasource.maxActive}")
    private int maxActive;

    @Value("${spring.datasource.maxWait}")
    private int maxWait;

    @Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
    private int timeBetweenEvictionRunsMillis;

    @Value("${spring.datasource.minEvictableIdleTimeMillis}")
    private int minEvictableIdleTimeMillis;

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

    @Value("${spring.datasource.testWhileIdle}")
    private boolean testWhileIdle;

    @Value("${spring.datasource.testOnBorrow}")
    private boolean testOnBorrow;

    @Value("${spring.datasource.testOnReturn}")
    private boolean testOnReturn;

    @Value("${spring.datasource.poolPreparedStatements}")
    private boolean poolPreparedStatements;

    @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
    private int maxPoolPreparedStatementPerConnectionSize;

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

    @Value("{spring.datasource.connectionProperties}") private String connectionProperties; @bean@primary // Public DataSourcedataSource(){
        DruidDataSource datasource = new DruidDataSource();

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

        //configuration
        datasource.setInitialSize(initialSize);
        datasource.setMinIdle(minIdle);
        datasource.setMaxActive(maxActive);
        datasource.setMaxWait(maxWait);
        datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        datasource.setValidationQuery(validationQuery);
        datasource.setTestWhileIdle(testWhileIdle);
        datasource.setTestOnBorrow(testOnBorrow);
        datasource.setTestOnReturn(testOnReturn);
        datasource.setPoolPreparedStatements(poolPreparedStatements);
        datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        try {
            datasource.setFilters(filters);
        } catch (SQLException e) {
            logger.error("druid configuration Exception", e);
        }
        datasource.setConnectionProperties(connectionProperties);

        returndatasource; }}Copy the code

Then create the DruidFilter as follows:

package com.dalaoyang.filter;
import javax.servlet.annotation.WebFilter;
import javax.servlet.annotation.WebInitParam;

import com.alibaba.druid.support.http.WebStatFilter;
/**
 * @author dalaoyang
 * @Description
 * @project springboot_learn
 * @package com.dalaoyang.filter
 * @email [email protected]
 * @date 2018/4/12
 */


@WebFilter(filterName="druidWebStatFilter",urlPatterns="/ *",
        initParams={
                @WebInitParam(name="exclusions",value="*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*"}) public class extends WebStatFilter {}Copy the code

Create a DruidServlet and add a @webservlet annotation on the top of the class to configure the DruidServlet account password, whitelist, and other Settings as follows:

package com.dalaoyang.servlet;
import javax.servlet.annotation.WebInitParam;
import javax.servlet.annotation.WebServlet;

import com.alibaba.druid.support.http.StatViewServlet;
/**
 * @author dalaoyang
 * @Description
 * @project springboot_learn
 * @package com.dalaoyang.servlet
 * @email [email protected]
 * @date 2018/4/12
 */


@WebServlet(urlPatterns="/druid/*",
        initParams={
                @WebInitParam(name="allow",value=""),// IP whitelist (not configured or empty, all access allowed) @webinitparam (name="deny",value=""),// IP blacklist (deny precedence over allow) @webinitparam (name="loginUsername",value="admin"@webinitparam (name=)"loginPassword",value="admin"}) public class DruidServlet extends StatViewServlet {}Copy the code

Then add the @ServletComponentScan annotation to the startup class to scan the project into the servlet as follows:

package com.dalaoyang; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.boot.web.servlet.ServletComponentScan; @springBootApplication // The bootstrap class must be annotated with @ServletComponentScan, Otherwise unable to scan to the servlet @ ServletComponentScan public class SpringbootDruidApplication {public static void main (String [] args) { SpringApplication.run(SpringbootDruidApplication.class, args); }}Copy the code

All that remains is the entity (entity class), Repository (data manipulation layer), and Controller (the controller used in the test) as integrated with JPA, showing the code directly.

City

package com.dalaoyang.entity;

import javax.persistence.*;

/**
 * @author dalaoyang
 * @Description
 * @project springboot_learn
 * @package com.dalaoyang.Entity
 * @email [email protected]
 * @date 2018/4/7
 */
@Entity
@Table(name="city")
public class City {

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private int cityId;
    private String cityName;
    private String cityIntroduce;

    public City(int cityId, String cityName, String cityIntroduce) {
        this.cityId = cityId;
        this.cityName = cityName;
        this.cityIntroduce = cityIntroduce;
    }

    public City(String cityName, String cityIntroduce) {
        this.cityName = cityName;
        this.cityIntroduce = cityIntroduce;
    }

    public City() {
    }

    public int getCityId() {
        return cityId;
    }

    public void setCityId(int cityId) {
        this.cityId = cityId;
    }

    public String getCityName() {
        return cityName;
    }

    public void setCityName(String cityName) {
        this.cityName = cityName;
    }

    public String getCityIntroduce() {
        return cityIntroduce;
    }

    public void setCityIntroduce(String cityIntroduce) { this.cityIntroduce = cityIntroduce; }}Copy the code

CityRepository

package com.dalaoyang.repository;

import com.dalaoyang.entity.City;
import org.springframework.data.jpa.repository.JpaRepository;

/**
 * @author dalaoyang
 * @Description
 * @project springboot_learn
 * @package com.dalaoyang.Repository
 * @email [email protected]
 * @date 2018/4/7
 */
public interface CityRepository extends JpaRepository<City,Integer> {
}
Copy the code

CityController

package com.dalaoyang.controller; import com.dalaoyang.entity.City; import com.dalaoyang.repository.CityRepository; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RestController; /** * @author dalaoyang * @Description * @project springboot_learn * @package com.dalaoyang.controller * @email [email protected] * @date 2018/4/7 */ @RestController public class CityController { @Autowired private CityRepository cityRepository; //http://localhost:8888/saveCity? CityName = Beijing & cityrule = GetMapping(value = "cityName""saveCity")
    public String saveCity(String cityName,String cityIntroduce){
        City city = new City(cityName,cityIntroduce);
        cityRepository.save(city);
        return "success"; } //http://localhost:8888/deleteCity? cityId=2 @GetMapping(value ="deleteCity")
    public String deleteCity(int cityId){
        cityRepository.delete(cityId);
        return "success"; } //http://localhost:8888/updateCity? CityId =3&cityName= Shenyang & cityrule = @getMapping (value ="updateCity")
    public String updateCity(int cityId,String cityName,String cityIntroduce){
        City city = new City(cityId,cityName,cityIntroduce);
        cityRepository.save(city);
        return "success"; } //http://localhost:8888/getCityById? cityId=3 @GetMapping(value ="getCityById")
    public City getCityById(int cityId){
        City city = cityRepository.findOne(cityId);
        returncity; }}Copy the code

Then start the project and you can see that the console has created the City table.

Then go to http://localhost:8888/druid, you can see the diagram below:

Enter the account password admin, admin, as shown below

Then we can visit http://localhost:8888/saveCity? CityName = Beijing &cityIntroduce= The capital of China

Then click the SQL monitor above the navigation, as shown below.

You can see the SQL that has just been executed to start the project create table. At this point the integration is done.

Source code download: elder Yang code cloud

Personal website: dalaoyang.cn