This is the sixth day of my participation in the August More text Challenge. For details, see:August is more challenging

The data source Druid, using a custom way to achieve, after the article using start initiator implementation, the article learning ideas and methods.

Why use data sources:

Data sources are a common way to improve database connection performance. Data sources are responsible for maintaining a pool of data connections. When an application creates a data source instance, the system creates multiple database connections at once and stores these database connections in the pool.

When a program needs database access, instead of reacquiring the database connection, it pulls an idle database connection from the connection pool.

When the application finishes using the database connection to access the database, instead of closing the database connection, it returns the database connection to the connection pool.

In this way, you can avoid the performance degradation that can result from frequently obtaining and closing database connections

– to quote

Druid 网 站 github Druid 网 站

Druid: Druid

Druid is a database connection pool implementation, it not only combined with C3P0, DBCP, PROXOOL and other advantages of the database connection pool, but also added log monitoring, you can monitor the DB pool connection and SQL execution, is a database connection pool for monitoring

Two ways to integrate third-party technology

• The custom
• Looking for a starter

Custom implementation of Druid data source

Import data source

Blogger version: 1.2.6

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>The version number</version>
</dependency>
Copy the code

Configuring a Data source

If we want to use Druid, we need to create a data source and use it in the container. MyDataSourceConfig file code is as follows:

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;   // Be careful not to misguide the packet

@Configuration
// Configure the data source
public class MyDtaSourceConfig {
    @Bean
    public DataSource dataSource(a) {

        return new DruidDataSource();   // Create Druid data source}}Copy the code

Why is the data source we created available?

In DataSourceAutoConfiguration can be found in the following parts:

@Configuration(proxyBeanMethods = false)
	@Conditional(PooledDataSourceCondition.class)
	@ConditionalOnMissingBean({ DataSource.class, XADataSource.class })
	@Import({ DataSourceConfiguration.Hikari.class, DataSourceConfiguration.Tomcat.class, DataSourceConfiguration.Dbcp2.class, DataSourceConfiguration.OracleUcp.class, DataSourceConfiguration.Generic.class, DataSourceJmxConfiguration.class })
	protected static class PooledDataSourceConfiguration {}Copy the code

Have introduced DataSourceConfiguration. Hikari. Class this default class, click enter,

@Configuration(proxyBeanMethods = false)
	@ConditionalOnClass(HikariDataSource.class)
	@ConditionalOnMissingBean(DataSource.class)
	@ConditionalOnProperty(name = "spring.datasource.type", havingValue = "com.zaxxer.hikari.HikariDataSource", matchIfMissing = true)
	static class Hikari {

		@Bean
		@ConfigurationProperties(prefix = "spring.datasource.hikari")  
		HikariDataSource dataSource(DataSourceProperties properties) {
			HikariDataSource dataSource = createDataSource(properties, HikariDataSource.class);
			if (StringUtils.hasText(properties.getName())) {
				dataSource.setPoolName(properties.getName());
			}
			returndataSource; }}Copy the code

As you can see from the code above, the default configuration source is HikariDataSource, with one requirement:

@ConditionalOnMissingBean(DataSource.class)

If there is no data source in the container, the following code takes effect, using HikariDataSource, but if there is already a developer-created data source in the container, the developer-created data source takes precedence.

When using a data source, you inevitably need to configure basic properties, such as URL, user name, password, and port number. So we can extract that information, put it in a configuration file,

Application. Yaml:

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/vuesite
    username: root
    password: 123456
    driver-class-name: com.mysql.jdbc.Driver
Copy the code

Use the ConfigurationProperties annotation to bind properties:

package com.xbhog.Config;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Configuration
// Configure the data source
public class MyDtaSourceConfig {
    @ConfigurationProperties("spring.datasource")  // Attribute binding to add the position
    @Bean
    public DataSource dataSource(a) {
        DruidDataSource druidDataSource = new DruidDataSource();

        returndruidDataSource; }}Copy the code

Unit testing:

package com.xbhog;

import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

@SpringBootTest
@Slf4j
class FirstAdminApplicationTests {
    @Autowired
    DataSource dataSource;
    
    @Test
    void contextLoads(a) {
        log.info("Data type: {}",dataSource.getClass()); }}Copy the code

The result data types: class com. Alibaba. Druid. Pool. DruidDataSource

Specific configuration binding: personal blog

Other features in Druid

Configure the monitoring page:

Intimate document link: web address

We need to configure a servlet configuration, and then put it into the container, enter the account password, enter the link address can access;

Under the configuration data source code:

/** * Configure the monitoring page */
@Bean
public ServletRegistrationBean staViewServlet(a){
    // Instantiate StatViewServlet
    StatViewServlet statViewServlet = new StatViewServlet();
	 // Pass the instantiated StatViewServlet into the ServletRegistrationBean and set the access path
    ServletRegistrationBean<StatViewServlet> registrationBean = new ServletRegistrationBean<>(statViewServlet, "/druid/*");

    return registrationBean;
}
Copy the code

Results:

Enable the login function:

Since the login Settings are still set in the servlet, the relevant information is omitted and here is a screenshot of the document:

Code added:

@Bean
public ServletRegistrationBean staViewServlet(a){
    StatViewServlet statViewServlet = new StatViewServlet();

    ServletRegistrationBean<StatViewServlet> registrationBean = new ServletRegistrationBean<>(statViewServlet, "/druid/*");
    // Configure the login information
    registrationBean.addInitParameter("loginUsername"."admin");
    registrationBean.addInitParameter("loginPassword"."123456");
    return registrationBean;
}
Copy the code

Results:

Enable the monitoring statistics function:

Test the Sql monitoring function.

Web address:

To enable monitoring, create a DruidDataSource component and configure the value of the filters attribute to stat.

So we need to configure the filters attribute where we create the data source:

public class MyDtaSourceConfig {
    @ConfigurationProperties("spring.datasource")
    @Bean
    public DataSource dataSource(a) throws SQLException {
        DruidDataSource druidDataSource = new DruidDataSource();
        // Add the monitoring function
        druidDataSource.setFilters("stat");
        returndruidDataSource; }}Copy the code

Properties can also be configured to achieve the above effect :(second method)

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/vuesite
    username: root
    password: 123456
    driver-class-name: com.mysql.jdbc.Driver

    filters: stat
    tomcat:
      max-active: 12
Copy the code

Then set up a request to test the monitoring functionality;

Create a controller:

@Controller
public class tablecontro {

  	  @Autowired
    JdbcTemplate jdbcTemplate;

    @ResponseBody   // Not through the view parser
    @GetMapping("/sql")
    public String druidquery(a){
        Long aLong = jdbcTemplate.queryForObject("select count(*) from user",Long.class);
        returnaLong.toString(); }}Copy the code

To access localhost: 8080 / SQL, and then enter the SQL to monitor http://localhost:8080/druid/index.html page to check the number of executions.

There are various distributions behind the maximum concurrency, refer to the official documentation: Introduction to Druid Connection Pools

Enabling the Web application function:

It’s basically the same thing;

The WebStatFilter command is used to collect the web-JDBC associated monitoring data.

The web address

Configure a WebStatFilter, and then initialize Exclusions to exclude unnecessary urls. You have to do the SQL request first

/** * WebStatFilter Used to collect the web-JDBC associated monitoring data. * /
@Bean
public FilterRegistrationBean webRegistrationBean(a){
    WebStatFilter webStatFilter = new WebStatFilter();
    FilterRegistrationBean<WebStatFilter> registrationBean = new FilterRegistrationBean<>(webStatFilter);
    registrationBean.setUrlPatterns(Arrays.asList("/ *"));
    // Add unnecessary ULRs and execute them at initialization time
    registrationBean.addInitParameter("exclusions"."*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
    return  registrationBean;
}
Copy the code

Results:

The Web application:

The URL to monitor

Configure a firewall:

wallfilter

So we can use the Filter with the code as follows: the SQL request must be made first.

public class MyDtaSourceConfig {
    @ConfigurationProperties("spring.datasource")
    @Bean
    public DataSource dataSource(a) throws SQLException {
        DruidDataSource druidDataSource = new DruidDataSource();
        // Add the monitoring function
        druidDataSource.setFilters("stat,wall");
        returndruidDataSource; }}Copy the code

Results:

reference

Druid official Github address

Druid Chinese document

reference

SpringBoot2 Getting started with springBoot

The end:

If you see here or just to help you, I hope you can point to follow or recommend, thank you;

If there are any errors, please point them out in the comments and the author sees them will be corrected.