springboot - Integrated druid

Configuration of pom.xml

<dependencies>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-web</artifactId>
	</dependency>
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-jdbc</artifactId>
	</dependency>
	<dependency>
		<groupId>com.alibaba</groupId>
		<artifactId>druid-spring-boot-starter</artifactId>
		<version>1.1.10</version>
	</dependency>
	<dependency>
		<groupId>com.baomidou</groupId>
		<artifactId>mybatis-plus-boot-starter</artifactId>
		<version>3.2.0</version>
	</dependency>
	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<scope>runtime</scope>
	</dependency>
	<dependency>
		<groupId>org.projectlombok</groupId>
		<artifactId>lombok</artifactId>
		<optional>true</optional>
	</dependency>
</dependencies>

application.properties configuration

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.name=druidDataSource
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/billdelivery?useSSL=false&serverTimezone=Asia/Shanghai
spring.datasource.username=root
spring.datasource.password=seeyon123456
# maximum connection
spring.datasource.druid.max-active=20
# Initial number of connections
spring.datasource.druid.initial-size=5
# Minimum number of connections
spring.datasource.druid.min-idle=5
# Get the maximum waiting time for a connection, in milliseconds
spring.datasource.druid.max-wait=60000
# How often is the configuration interval detected to detect idle connections that need to be closed in milliseconds?
spring.datasource.druid.time-between-eviction-runs-millis=60000
# Configure the minimum lifetime of a connection in the pool in milliseconds
spring.datasource.druid.min-evictable-idle-time-millis=300000
# Test connection
spring.datasource.druid.validation-query=SELECT 1 FROM DUAL
# When applying for a connection, it is recommended to configure it as true, without affecting performance and ensuring security.
spring.datasource.druid.test-while-idle=true
# Perform detection when accessing connections, recommend closing, affect performance
spring.datasource.druid.test-on-borrow=false
# Perform detection when returning connections, recommend closing, affect performance
spring.datasource.druid.test-on-return=false
# Whether to cache preparedStatement is recommended to close in mysql environment because it consumes a lot of database performance
spring.datasource.druid.pool-prepared-statements=false
spring.datasource.druid.max-pool-prepared-statement-per-connection-size=20
# Configure filters intercepted by monitoring statistics. After removing the filters, the monitoring interface SQL can not be used for statistics,'wall'is used for firewalls.
spring.datasource.druid.filters=stat,wall,slf4j
# Open mergeSql function; slow SQL record
spring.datasource.druid.connection-properties=druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
spring.datasource.druid.web-stat-filter.enabled=true
spring.datasource.druid.web-stat-filter.url-pattern=/*
spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
# IP white list
spring.datasource.druid.stat-view-servlet.allow=127.0.0.1
# IP blacklist
#spring.datasource.druid.stat-view-servlet.deny=192.168.1.73
# Disable "Reset All" functionality on HTML pages
spring.datasource.druid.stat-view-servlet.reset-enable=true
# Landing name
spring.datasource.druid.stat-view-servlet.login-username=admin
# Login password
spring.datasource.druid.stat-view-servlet.login-password=123456

3. Test code

@RunWith(SpringRunner.class)
@SpringBootTest
public class DruidApplicationTests {
    @Autowired
    private DataSource dataSource;

    @Test
    public void contextLoads() throws Exception {
        Connection connection = dataSource.getConnection();
        PreparedStatement prepareStatement = connection.prepareStatement("select * from bill_item");
        ResultSet resultSet = prepareStatement.executeQuery();
        while (resultSet.next()) {
            String cityName = resultSet.getString("id");
            System.out.println(cityName);
        }
    }
}

Implementation results:

-2592516606220286509
511882148076407261
2223847085336053894
7407386164171327186

IV. druid Gets SQL Execution Log

Focus on the following sections in application.properties:

# Open mergeSql function; slow SQL record
spring.datasource.druid.connection-properties=druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
spring.datasource.druid.web-stat-filter.enabled=true
spring.datasource.druid.web-stat-filter.url-pattern=/*
spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
# IP white list
spring.datasource.druid.stat-view-servlet.allow=127.0.0.1
# IP blacklist
#spring.datasource.druid.stat-view-servlet.deny=192.168.1.73
# Disable "Reset All" functionality on HTML pages
spring.datasource.druid.stat-view-servlet.reset-enable=true
# Landing name
spring.datasource.druid.stat-view-servlet.login-username=admin
# Login password
spring.datasource.druid.stat-view-servlet.login-password=123456

We do a query example based on mybatis:

@Repository
public interface BillShopMapper extends BaseMapper<BillShop> {
}

@RestController
public class HelloController {
    @Autowired
    private BillShopMapper billShopMapper;

    @RequestMapping(path = "/listAll", method = RequestMethod.GET)
    public List<BillShop> listAll() {
        return billShopMapper.selectList(null);
    }
}

Access / listAll requests to trigger sql queries:


Note: Don't upgrade druid-spring-boot-starter easily. It has been proved that upgrade to the latest 1.1.20 will not be accessible.

V. Reference Links

  1. [SpringBook Notes] SpringBook Integrates Druid Data Connection Pool
  2. The Use of Druid (New starter) under Spring Boot
  3. Springboot 2.0 integrates druid and springboot automatic assembly DataSource principles
  4. spring-boot integrates Ali druid

Tags: Druid Spring MySQL SQL

Posted on Fri, 04 Oct 2019 11:21:52 -0400 by Ajdija