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