Druid is a relational database connection pool that is an open source project of Alibaba. Druid supports all JDBC-compatible databases, including Oracle, MySQL, PostgreSQL, SQL Server, H2, and more. Druid offers significant advantages in monitoring, scalability, stability, and performance. Druid allows you to monitor database connection pools and SQL queries in real time. Druid connection pooling improves data access efficiency to a certain extent.
Application. Yml and druidconfig. Java
Step 1: Introduce dependencies
<! -- Core initiator -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<! Error creating bean with name 'druidDataSource' -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<! -- druid, add version number -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.21</version>
</dependency>
<! Mysql > select * from 'mysql';
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.48</version>
</dependency>
<! -- Using druid to monitor pages needs to be a Web project, introducing a Web initiator -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
Copy the codeStep 2: Switch data sources in application.yml
spring:
datasource:
username: root
password: 123456
url: JDBC: mysql: / / 192.168.10.32:3306 / JDBC
driver-class-name: com.mysql.jc.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
Copy the codeStep 3: Set the data source configuration in application.yml
spring:
datasource:
username: root
password: 123456
url: JDBC: mysql: / / 192.168.10.32:3306 / JDBC
driver-class-name: com.mysql.jc.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
#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 an error is allowed, Java. Lang. ClassNotFoundException: org.. Apache Log4j. Properity
Import log4j dependencies
filters: stat,wall,log4j
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true; druid.stat.slowSqlMillis=500
Copy the codeStep 4: Configure DruidConfig
/** * Created by baidou on 2021/2/26. * * class:DruidConfig */
@Configuration
public class DruidConfig {
@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource druidDataSource(a) {
return new DruidDataSource();
}
// Background monitoring
@Bean
public ServletRegistrationBean statViewServlet(a) {
/ / register ServletBean
ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
// Set the password for background login
HashMap<String, String> initParameters = new HashMap<>();
initParameters.put("loginUsername"."admin");
initParameters.put("loginPassword"."123456");
// allow who can access initparameters. put("username","password");
bean.setInitParameters(initParameters);// Initialize parameters
return bean;
}
//Filter
public FilterRegistrationBean webStatFilter(a) {
/ / register FilterBean
FilterRegistrationBean bean = new FilterRegistrationBean();
bean.setFilter(new WebStatFilter());
// Which requests can be filtered?
HashMap<String, String> initParameters = new HashMap<>();
// These things are not counted
initParameters.put("exclusions"."*.js,*.css,/druid/*");
bean.setInitParameters(initParameters);// Initialize parameters
returnbean; }}Copy the codeStep 5: Access Druid monitoring
http://localhost:8000/druid/login.html
http://localhost:8000/druid/
Copy the codeApplication. Yml Configure Druid
Step 1: Introduce dependencies
<! -- Core initiator -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<! Error creating bean with name 'druidDataSource' -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<! -- druid, add version number -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.21</version>
</dependency>
<! Mysql > select * from 'mysql';
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.48</version>
</dependency>
<! -- Using druid to monitor pages needs to be a Web project, introducing a Web initiator -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
Copy the codeStep 2: Configure the data source in application.yml
spring:
datasource:
druid:
# data source type
db-type: com.alibaba.druid.pool.DruidDataSource
The url to connect to the database varies from database to database. Such as:
# mysql : jdbc:mysql://ip:port/database
# oracle : jdbc:oracle:thin:@ip:port:database
url: JDBC: mysql: / / 127.0.0.1:3306 / joint_force? useSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
username: root
password: 795200
Druid automatically identifies dbType by URL if you do not configure druid to automatically identify dbType by URL
# select the corresponding driverClassName(recommended)
driver-class-name: com.mysql.cj.jdbc.Driver
Number of physical connections established at initiation. Initialization occurs when the display calls the init method, or the first getConnection (default: 0)
initial-size: 2
Minimum number of connection pools
min-idle: 5
# Maximum number of connection pools
max-active: 10
If an application requests a connection from the connection pool and the value of maxWait exceeds the threshold, the request is considered to have failed
max-wait: 5000
PreparedStatement (PSCache) PSCache provides a huge performance boost for databases that support cursors, such as Oracle. You are advised to disable this function in mysql. (Default value: false)
The default is false
pool-prepared-statements: false
To enable PSCache, the configuration must be greater than 0. When greater than 0, poolPreparedStatements automatically triggers a change to true.
In Druid, there is no problem with Oracle PSCache using too much memory. You can set this value to a higher value, such as 100
max-open-prepared-statements: - 1
The maximum number of SQL cached per connection
max-pool-prepared-statement-per-connection-size: 20
Configure how often to detect idle connections that need to be closed, in milliseconds
time-between-eviction-runs-millis: 60000
Set the minimum time for a connection to live in the pool in milliseconds
min-evictable-idle-time-millis: 300000
Set the maximum number of milliseconds for a connection to live in the pool
max-evictable-idle-time-millis: 900000
SQL to check whether a connection is valid. TestOnBorrow, testOnReturn, and testWhileIdle will not function if validationQuery is null.
validation-query: SELECT now()
# It is recommended to set this parameter to true, which does not affect performance and ensures security. Apply for connection, if free time is more than timeBetweenEvictionRunsMillis, performing validationQuery test connection is valid.
test-while-idle: true
Check the validity of the connection when the application requests a connection.
test-on-borrow: false
Check the validity of the connection when the application returns the connection.
test-on-return: false
SQL to execute when initializing physical connections
connection-init-sqls: SELECT now()
# program does not have close connection and free time more than minEvictableIdleTimeMillis, will perform validationQuery specified SQL, to ensure that the program connection won't kill off the pool, its scope is not more than minIdle specified number of connections.
keep-alive: true
Druid: select * from pool; druid: select * from pool; druid: select * from pool; druid: select * from pool;
remove-abandoned: true
Whether to log the Stack trace when druid forces the connection to be reclaimed
log-abandoned: true
Druid reclaims connections in seconds after the druid gets the connection from the pool. The value must be longer than the maximum service running time
remove-abandoned-timeout: 6000
# Connect properties. For example, set up some connection pool statistics configuration.
connection-properties: druid.stat.mergeSql=true; druid.stat.slowSqlMillis=5000
Filter :log4j to defend against SQL injection filter:wall
filters: stat, wall, slf4j
filter:
stat: # Monitoring statistics
enabled: true
log-slow-sql: true Slow SQL logging
slow-sql-millis: 1000 Slow SQL execution time
merge-sql: true Whether to merge SQL
db-type: mysql # database type
wall: Defend against SQL injection
enabled: true
db-type: mysql
config:
delete-allow: false
drop-table-allow: false
multi-statement-allow: true
# statViewServlet configuration
stat-view-servlet:
enabled: true # Enable/disable
allow: 127.0. 01. Set whitelist to allow all access
deny: 192.168. 01. If allow and deny exist at the same time, deny takes precedence over Allow
reset-enable: false Disable manual resetting of monitoring data
url-pattern: /druid/*
login-username: admin # Set the login password of the monitoring page
login-password: admin # Set the login name of the monitoring page
# WebStatFilter configuration
web-stat-filter:
enabled: true # Enable/disable
url-pattern: "/ *" Access path
exclusions: "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*" # Eliminate unnecessary urls
session-stat-max-count: 1000 # Monitor the maximum number of sessions. Default is 1000
session-stat-enable: true # Whether to enable the session statistics function
profile-enable: true Whether to enable the SQL list to monitor a single Url call
principalSessionName: session_user_key Druid specifies the user of the current session. Session_user_key is the sessionName that you saved into the session
principalCookieName: cookie_user_key The cookie_user_key is the cookieName you saved in the cookie
Copy the codeStep 3: Add servlet support
Because the Druid monitor page is a servlet, you need to make SpingBoot support servlets, so add the @servletComponentScan annotation to the application portal, otherwise you will get a 404 error.
@SpringBootApplication
@ServletComponentScan
public class SingerwblogApplication {
public static void main(String[] args) { SpringApplication.run(SingerwblogApplication.class, args); }}Copy the codeStep 4: Access Druid monitoring
Start the project, visit http://localhost:8080/druid/login.html, enter configuration file just now configure the user name and password!
It can be seen that it roughly includes the following modules: data source, SQL monitoring, SQL firewall, Web application, URI monitoring, Session monitoring, JSONAPI and so on.
QL monitoring: You can view all executed SQL statements
SQL firewall: Druid provides access to the blacklist and whitelist, so you can clearly see how SQL is protected.
Web applications: You can see details about the Web applications that are currently running.
URI monitoring: Monitors the request times and request time of all request paths.
Session monitoring: You can view detailed parameters such as the current Session status, creation time, last active time, request times, and request time.
JSONAPI: Access Druid’s monitoring interface through an API that returns Json data.