SpringBoot data access - SQL data source configuration (Hikar, Druid)

JDBC (Java DataBase Connectivity), that is, Java database connection, uses Java language to operate the database.
Jdbc provides a unified interface for the database, and various database manufacturers provide database drivers (i.e. API s) based on the Jdbc specification.

1.1.2. Importing jdbc from springboot

Introducing jdbc development scenarios

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>

Dependency information automatically imported by jdbc scenario

1.2. Import database driver

1.2.1. Import mysql database driver

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

1.2.2. mySql version number setting

1) springboot sets the mySql version number by default: < mySql version>8.0.22</mySql. version>

2) Modify mysql version number:

Method 1: add the version number information when importing the driver (maven's proximity dependency principle)

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.49</version>
</dependency>

Method 2: redeclare the version (the nearest priority principle in maven's attributes)

<properties>
    <java.version>1.8</java.version>
    <mysql.version>5.1.49</mysql.version>
</properties>

1.3. Modify database configuration information

In the configuration file of application.boot Add database configuration information to yaml:

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

1.4 test

Test code: total records of query database

@Slf4j
@SpringBootTest
class SpringBootThymeleafApplicationTests {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Autowired
    DataSource dataSource;

    @Test
    void contextLoads() {
        Long along = jdbcTemplate.queryForObject("select count(*) from account", long.class);
        log.info("Total records{}", along);
        log.info("Data source type{}", dataSource.getClass());
    }

}

Return result:

2. Using Druid data sources

After jdbc and database driver are configured, the default Hikari of springboot is not used, but Druid data source is used

druid official github address: https://github.com/alibaba/druid

There are two ways to integrate third-party technologies: customize and directly import starter scenarios

2.1. User defined method

2.1.1. Create data source

1) Introducing druid dependency

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.17</version>
</dependency>

2) Modify database configuration information

Method 1: direct configuration

1. Create DataSource data source configuration class

2. Create DruidDataSource in the configuration class and add properties

@Configuration
public class MyDataSourceConfig {

    // Default automatic configuration: the Hikari data source will be configured only after judging that there is no data source in the container
    // @ConditionalOnMissingBean({DataSource.class, XADataSource.class})
    @Bean
    public DataSource dataSource() throws SQLException {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUrl();
        druidDataSource.setUsername();
        druidDataSource.setPassword();
        return druidDataSource;
    }
}

Method 2: configure in the configuration file

1. Create DataSource data source configuration class

2. Create a DruidDataSource in the configuration class and bind it to the configuration file

@Configuration
public class MyDataSourceConfig {

    // Default automatic configuration: the Hikari data source will be configured only after judging that there is no data source in the container
    // @ConditionalOnMissingBean({DataSource.class, XADataSource.class})
    @ConfigurationProperties("spring.datasource")    //Bind the properties in the component to the configuration file
    @Bean
    public DataSource dataSource() throws SQLException {
        DruidDataSource druidDataSource = new DruidDataSource();
        return druidDataSource;
    }
}

3. Add configuration information to the configuration file

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mydb1
    username: root
    password: 123
    # Database driver name
    driver-class-name: com.mysql.cj.jdbc.Driver

2.1.2 test

Test code:

@Slf4j
@SpringBootTest
class SpringBootThymeleafApplicationTests {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Autowired
    DataSource dataSource;

    @Test
    void contextLoads() {
        Long along = jdbcTemplate.queryForObject("select count(*) from account", long.class);
        log.info("Total records{}", along);
        log.info("Data source type{}", dataSource.getClass());
    }

}

Test results:

2.1.3 configure other functions of Druid data source

1)StatViewServlet:

Main functions:

Provide html page for monitoring information display
JSON API that provides monitoring information

Configuration method:

Official documents

	<servlet>
		<servlet-name>DruidStatView</servlet-name>
		<servlet-class>com.alibaba.druid.support.http.StatViewServlet</servlet-class>
	</servlet>
	<servlet-mapping>
		<servlet-name>DruidStatView</servlet-name>
		<url-pattern>/druid/*</url-pattern>
	</servlet-mapping>

Custom configuration

1. Data source enable monitoring function: druiddatasource setFilters("stat, wall");

2. Register Servlet component: ServletRegistrationBean

@Configuration
public class MyDataSourceConfig {

    // Default automatic configuration: the Hikari data source will be configured only after judging that there is no data source in the container
    // @ConditionalOnMissingBean({DataSource.class, XADataSource.class})
    @ConfigurationProperties("spring.datasource")    //Bind the properties in the component to the configuration file
    @Bean
    public DataSource dataSource() throws SQLException {
        DruidDataSource druidDataSource = new DruidDataSource();
//        druidDataSource.setUrl();
//        druidDataSource.setUsername();
//        druidDataSource.setPassword();
        //Add monitoring function and firewall function
        druidDataSource.setFilters("stat, wall");
        return druidDataSource;
    }

    //Configure the monitoring page function of druid
    @Bean
    public ServletRegistrationBean statViewServlet() {
        StatViewServlet statViewServlet = new StatViewServlet();
        ServletRegistrationBean<StatViewServlet> registrationBean = 
        new ServletRegistrationBean<StatViewServlet>(statViewServlet, "/druid/*");

        registrationBean.addInitParameter("loginUsername", "root");
        registrationBean.addInitParameter("loginPassword", "123");

        return registrationBean;
    }
}

3. View monitoring page

2)StatFilter:

Main functions:

Used for statistical monitoring information; Such as SQL monitoring and URI monitoring

Configuration method:

Official documents Disposition_ StatFilter · alibaba/druid Wiki · GitHub

Custom configuration

1. Data source enable monitoring function: druiddatasource setFilters("stat, wall");

2 Register Filter component: FilterRegistrationBean

@Configuration
public class MyDataSourceConfig {

    // Default automatic configuration: the Hikari data source will be configured only after judging that there is no data source in the container
    // @ConditionalOnMissingBean({DataSource.class, XADataSource.class})
    @ConfigurationProperties("spring.datasource")    //Bind the properties in the component to the configuration file
    @Bean
    public DataSource dataSource() throws SQLException {
        DruidDataSource druidDataSource = new DruidDataSource();
//        druidDataSource.setUrl();
//        druidDataSource.setUsername();
//        druidDataSource.setPassword();
        //Add monitoring function and firewall function
        druidDataSource.setFilters("stat, wall");
        return druidDataSource;
    }

    //Configure the monitoring page function of druid
    @Bean
    public ServletRegistrationBean statViewServlet() {
        StatViewServlet statViewServlet = new StatViewServlet();
        ServletRegistrationBean<StatViewServlet> registrationBean = new ServletRegistrationBean<StatViewServlet>(statViewServlet, "/druid/*");

        registrationBean.addInitParameter("loginUsername", "root");
        registrationBean.addInitParameter("loginPassword", "123");

        return registrationBean;
    }

    // WebStatFilter is used to collect web JDBC Association monitoring data
    @Bean
    public FilterRegistrationBean webStatFilter () {
        WebStatFilter webStatFilter = new WebStatFilter();
        FilterRegistrationBean<WebStatFilter> filterRegistrationBean = new FilterRegistrationBean<WebStatFilter>(webStatFilter);
        //Set interception path
        filterRegistrationBean.setUrlPatterns(Arrays.asList("/*"));
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");

        return filterRegistrationBean;
    }
}

3. View monitoring page

2.2. Use the official starter mode

2.2.1. Introducing druid scenario

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.17</version>
</dependency>

2.2.2. Analyze automatic configuration items

Extension configuration item spring datasource. druid
DruidSpringAopConfiguration.class to monitor the of SpringBean; Configuration item: spring datasource. druid. aop-patterns
DruidStatViewServletConfiguration.class, monitoring page configuration: spring datasource. druid. stat-view-servlet； Default on
DruidWebStatFilterConfiguration.class, web monitoring configuration; spring.datasource.druid.web-stat-filter； Default on
DruidFilterConfiguration.class}) configuration of all Druid filter s

    private static final String FILTER_STAT_PREFIX = "spring.datasource.druid.filter.stat";
    private static final String FILTER_CONFIG_PREFIX = "spring.datasource.druid.filter.config";
    private static final String FILTER_ENCODING_PREFIX = "spring.datasource.druid.filter.encoding";
    private static final String FILTER_SLF4J_PREFIX = "spring.datasource.druid.filter.slf4j";
    private static final String FILTER_LOG4J_PREFIX = "spring.datasource.druid.filter.log4j";
    private static final String FILTER_LOG4J2_PREFIX = "spring.datasource.druid.filter.log4j2";
    private static final String FILTER_COMMONS_LOG_PREFIX = "spring.datasource.druid.filter.commons-log";
    private static final String FILTER_WALL_PREFIX = "spring.datasource.druid.filter.wall";

2.2.3 configuration example

Official documents: https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter

Configuration item list: https://github.com/alibaba/druid/wiki/DruidDataSource%E9%85%8D%E7%BD%AE%E5%B1%9E%E6%80%A7%E5%88%97%E8%A1%A8

Custom configuration:

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

    druid:
      aop-patterns: com.atguigu.admin.*  #Monitoring springbeans
      filters: stat,wall     # Bottom enabling function, stat (sql monitoring), wall (firewall)

      stat-view-servlet:   # Configure monitoring page function
        enabled: true
        login-username: admin
        login-password: admin
        resetEnable: false

      web-stat-filter:  # Monitoring web
        enabled: true
        urlPattern: /*
        exclusions: '*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*'


      filter:
        stat:    # Detailed configuration of stat in the above filters
          slow-sql-millis: 1000
          logSlowSql: true
          enabled: true
        wall:
          enabled: true
          config:
            drop-table-allow: false

3. Data source configuration principle

DataSourceAutoConfiguration: automatic configuration of data source
- Modify the configuration related to the data source: spring datasource
- The configuration of database connection pool is automatically configured only when there is no DataSource in its own container
- The bottom configured connection pool is HikariDataSource

	@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

DataSourceTransactionManagerAutoConfiguration: automatic configuration of the transaction manager
JdbcTemplateAutoConfiguration: the automatic configuration of JdbcTemplate can be used to crud the database
- You can modify the configuration item @ ConfigurationProperties(prefix = "spring.jdbc") to modify the JdbcTemplate
- @ Bean@Primary JdbcTemplate； There is this component in the container
JndiDataSourceAutoConfiguration: automatic configuration of jndi
XADataSourceAutoConfiguration: distributed transaction related

Tags: Java SQL Spring Boot

Posted by yuan22m on Mon, 18 Apr 2022 17:19:44 +0930

Programs Wiki