SpringBoot integrates JDBC and Druid

For data access layer, whether SQL (relational database) or NOSQL (non-relational database), SpringBoot bottom uses the way of SpringData for unified processing. SpringData is also a well-known project in the Spring family bucket, along with SpringBoot and SpringCloud.

IO /projects/sp…

To integrate a database, springBoot requires the following launcher JAR packages, which are related to data

1. SpringBoot integrates JDBC

1.1 Knowledge supplement: Data source and database connection pool

The DataSource in Java is javax.sql.DataSource. The creation of a DataSource can be implemented differently. A DataSource is usually referred to as a DataSource. It contains two parts: connection pool and connection pool management.

Creating a DataSource in JNDI mode :(Java Naming and Directory Interface) The first step is to configure the connection information for the data source, which is the data source connection pool. This configuration should be configured in conf/context. XML in the Tomcat installation directory.

Basic idea of connection pool:

When the system is initialized, the database connection is stored in memory as an object. When the user needs to access the database, instead of creating a new connection, an established free connection object is fetched from the connection pool.
Database connection pooling is responsible for allocating, managing, and releasing database connections and allows applications to reuse an existing database connection rather than re-creating one.

Connection pooling:

Database Connection pool is to prepare a pool, there are a lot of generated connections, users request to get a Connection, do not need getConnection, just take one from the pool to him on the line, so as to save the generation of Connection time, efficiency will be greatly improved. But of course will take up some memory, slightly larger sites will use the database connection pool ~

Advantages of database connection pooling:

Resource reuse
Faster system response times
New means of resource allocation
Unified connection management to avoid database connection leakage

In conclusion:

To configure a database connection is to configure accounts, passwords, database urls, and so on.
To configure a database connection pool is to configure multiple database connections in batches.

1.2. Import the JDBC scenario

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

Click on the JDBC scenario source code: by viewing dependencies, we can see that by importing JDBC scenarios, SpringBoot automatically imports data sources, database operations, transaction management and other configurations.

However, it is easy to see from the above: why did the JDBC scenario import, the official why not import the database driver? because

The authorities don’t know what database we’re working on next.
When importing the database driver, note that the imported database driver corresponds to the local database version. (Backward compatibility with older mysql drivers)

1.3 Importing the database driver Package (MySQL as an example)

Mysql automatic arbitration version: default latest version, here is version 8.0

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
Copy the code

But if you want to change the version (database version should be the same as driver version) : there are two ways to do this.

Direct dependencies import concrete versions (Maven’s nearby dependency principle)

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.49</version>// Write shortest path first</dependency>
Copy the code

Reversioning (Maven’s attributes are nearest first)

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

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
Copy the code

Note: You can select JDBC and Mysql driver scenarios in advance when creating the SpringBoot project

1.4. Class analysis is automatically configured for relevant data sources

Access the JDBC dependency package: The following information is displayed

DataSourceAutoConfiguration: Automatic configuration of data sources.
1. Modify data source-related configurations:spring.datasourceInitial configuration
2. The database connection pool configuration is automatically configured when there is no DataSource in the database.
3. The connection pools automatically configured at the bottom are:HikariDataSource
DataSourceTransactionManagerAutoConfiguration: automatic configuration of the transaction manager.
JdbcTemplateAutoConfiguration: JdbcTemplate automatic configuration (spring for a team in the database operation), can come to CRUD database.
1. The JdbcTemplate can be modified by modifying the @configurationProperties (prefix = “spring.jdbc”) configuration item prefixed with spring.jdbc.
```
spring:
  jdbc:
    query-timeout: 3
Copy the code
```
2. @bean@primary JdbcTemplate: The Spring container has this JdbcTemplate component, which is automatically injected using @AutoWired
JndiDataSourceAutoConfiguration: Automatic configuration of JNDI.
XADataSourceAutoConfiguration: distributed transaction related.

1.5. Modify the configuration items and configure the data source

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/runoob? serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
    username: root
    password: 123456
    driver-class-name: com.mysql.jdbc.Driver
Copy the code

Note: mysql8.0 + database connection configuration:

The className has changed and is nowcom.mysql.cj.jdbc.Driver
ServerTimezone =UTC for global consistency of database timestamps. Otherwise, an error will be reported

driverClassName=com.mysql.cj.jdbc.Driver 
#8.0 drivers are marked in red, but not affected
#serverTimezone-UTC: springBoot time zone is set in springBoot
# useunicode-true&charac terEncoding= utf-8 representation: useUnicode and utf-8 encoding to prevent garbled characters
url=jdbc:mysql://localhost:3306/project? serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
username=root
password=root
Copy the code

1.6 unit test data source [test directory]

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;

@Slf4j
@SpringBootTest
class Boot05WebAdminApplicationTests {

    // Springboot has already configured most of it for us, so we only need to configure a small amount of information to connect to the database
    // Here we inject directly
    @Autowired
    DataSource dataSource ;

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Test// @org.junit.Test generates a null pointer exception, possibly related to the new version of junit
    void contextLoads(a) {
        //jdbcTemplate.queryForObject("select * from account_tbl")
        //jdbcTemplate.queryForList("select * from account_tbl",)
        Long aLong = jdbcTemplate.queryForObject("select count(*) from account_tbl", Long.class);
        log.info("Total records: {}",aLong);
        // View the class object of the data source object, which is the default data source objectSystem.out.println(dataSource.getClass()); }}Copy the code

The results of

2. Custom integration of third-party data sources — Druid

Druid: What is Druid?

A large part of Java programs need to operate on databases, and to improve the performance of operating databases, you have to use database connection pools. Druid is a database connection pool implementation on Alibaba’s open source platform. It combines the advantages of C3P0, DBCP and other DB pools, as well as log monitoring.

Druid is designed to monitor DB pool connections and SQL execution. Hikari data sources are the default for SpringBoot2.0 and beyond, and Hikari and Druid are among the best data sources currently available on the JAVAweb.

Druid’s website: github.com/alibaba/dru…

Druid Connection Pool

Github.com/alibaba/dru…
Github.com/alibaba/dru…

2. SpringBoot integrates third-party Druid connection pools

Looking for starter scenario

Druid Spring Boot Starter: Simplifies the above configuration

Introduce scene dependencies:

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

Analyzing automatic configuration:

Extended configuration item spring.datasource. Druid
Automatic DruidDataSourceAutoConfigure configuration
Monitor SpringBean DruidSpringAopConfiguration. Class,; Configuration items: spring datasource. The druid. Aop – patterns
DruidStatViewServletConfiguration. Class, monitoring configuration page. Spring. The datasource. The druid. Stat – view – servlet opens by default.
DruidWebStatFilterConfiguration. Class, web monitoring configuration. Spring. The datasource. The druid. Web – stat – filter opened by default.
DruidFilterConfiguration. All the Druid class of filter configuration:

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";
Copy the code

Example configuration data source: Here we do not use the monitoring configuration provided by Druid above

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

Test: Write a controller and use JDBC to get the following information:

package com.example.boot.controller;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;

@RestController
public class JdbcController {
    @Resource
    JdbcTemplate jdbcTemplate;

    @GetMapping("/getCount")
    public int getCount(a){
        String sql = "select count(*) from student";
        Integer number = jdbcTemplate.queryForObject(sql, Integer.class);
        returnnumber; }}Copy the code

3. Configure the Druid monitoring page function and enable SQL monitoring and Web firewall functions.

The YAML file configures corresponding monitoring functions

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/runoob? serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
    username: root
    password: ajt19980607###
    driver-class-name: com.mysql.jdbc.Driver

    druid:
      aop-patterns: com.lemon.admin.*  # monitoring SpringBean
      filters: stat,wall     Stat (SQL monitor), Wall (firewall)

      stat-view-servlet: Configure the 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 the stat in the filters above
          slow-sql-millis: 1000
          logSlowSql: true
          enabled: true
        wall:
          enabled: true
          config:
            drop-table-allow: false
Copy the code

Testing:

Get the information again using JDBC:

package com.example.boot.controller;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;

@RestController
public class JdbcController {
    @Resource
    JdbcTemplate jdbcTemplate;

    @GetMapping("/getCount")
    public int getCount(a){
        String sql = "select count(*) from student";
        Integer number = jdbcTemplate.queryForObject(sql, Integer.class);
        returnnumber; }}Copy the code

After configured, start the server, open the address: localhost: 8080 / druid/HTML and use the configuration information to log in, you can see some of the information of program execution

Let’s look at Druid’s monitoring: he tells us that I refreshed that page once in 165 seconds, and the slowest one in 165 seconds. The number of rows read is 1.

SpringBoot integrates JDBC and Druid

SpringBoot integrates JDBC and Druid

1. SpringBoot integrates JDBC

1.1 Knowledge supplement: Data source and database connection pool

1.2. Import the JDBC scenario

1.3 Importing the database driver Package (MySQL as an example)

1.4. Class analysis is automatically configured for relevant data sources

1.5. Modify the configuration items and configure the data source

1.6 unit test data source [test directory]

2. Custom integration of third-party data sources — Druid

Druid: What is Druid?

2. SpringBoot integrates third-party Druid connection pools

3. Configure the Druid monitoring page function and enable SQL monitoring and Web firewall functions.

Related Posts

For further analysis of String, StringBuffer, and StringBuiler, read this article

Summary of Microservice Performance Optimization -4. Increase the monitoring of HTTP request waiting queues of synchronous microservices

Go language is 11 years old this year, where to Go, what is the status quo?