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:
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
- [SpringBook Notes] SpringBook Integrates Druid Data Connection Pool
- The Use of Druid (New starter) under Spring Boot
- Springboot 2.0 integrates druid and springboot automatic assembly DataSource principles
- spring-boot integrates Ali druid