Spring boot 集成Druid连接池

 Druid连接池是阿里开源的数据库连接池，并且提供SQL执行的监控能力；Druid 提供了不同的 LogFilter，能够支持Common-Logging、Log4j和 JdkLog；具有数据库密码加密提供安全保证.

一、maven依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>3.1.7</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.ld</groupId>
	<artifactId>druid-demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<properties>
		<java.version>17</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus-boot-starter</artifactId>
			<version>3.5.5</version>
		</dependency>
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid-spring-boot-starter</artifactId>
			<version>1.2.18</version>
		</dependency>
		<dependency>
			<groupId>com.mysql</groupId>
			<artifactId>mysql-connector-j</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-configuration-processor</artifactId>
			<optional>true</optional>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

二、yml文件配置

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      # 数据库 换成实际地址
      url: jdbc:mysql:/XXXXXXXXe
      # 换成实际用户名和密码
      username: XXX
      password: XXXXX
      driver-class-name: com.mysql.cj.jdbc.Driver
      # 连接池-初始化大小
      initial-size: 10
      # 连接池-最大连接数
      max-active: 100
      # 最大等待时间
      max-wait: 60000
      # 连接池-最小空闲数
      min-idle: 10
      # 检测空闲连接
      test-while-idle: true
      # 最小空闲时间
      min-evictable-idle-time-millis: 300000   
mybatis-plus:
  configuration:
   log-impl: org.apache.ibatis.logging.stdout.StdOutImpl	  

三、配置连接池

配置连接池和配置了jdbcTemplate用来通过jdbc访问，以便使用jdbcTemplate编写例子。

@Configuration
public class DruidConfig {
	@Bean("dataSource")
	@ConfigurationProperties(prefix = "spring.datasource.druid")
	 DruidDataSource dataSource() {
		return new DruidDataSource();
	}

	@Bean("jdbcTemplate")
	 JdbcTemplate jdbcTemplate() {
		return new JdbcTemplate(dataSource());
	}
}

四、Service类

 SubCompany一个pojo类，还需要一个数据库表，这些都是很简单的，读者自己来实现

@Service
public class JdbcService {
     @Resource
    private JdbcTemplate jdbcTemplate ;

    /**
     * 查询全部
     */
    public List<SubCompany> queryAll (){
        return jdbcTemplate.query("select * from contract_company_info",new BeanPropertyRowMapper<>(SubCompany.class));
    } 
}

五、 Controller类

 请求地址http://localhost:8080/druid/jdbcSelect，即可查看返回数据。

@RestController
@RequestMapping("/druid")
public class JdbcController {
	@Autowired
	private JdbcService jdbcService; 
	@GetMapping(value = "/jdbcSelect")
	public Object jdbcSelect(){
		return jdbcService.queryAll();
	}
}

六、密码加密

  1、加密工具类：

package com.ld.druid.util;

import com.alibaba.druid.filter.config.ConfigTools;

public class EncryptorUtils {
	public static void main(String[] args) throws Exception {
		String password="XXXXXXXXX";
		//arr[0]私钥 arr[1]公钥
		String[] arr = ConfigTools.genKeyPair(512);
		String encPassword=ConfigTools.encrypt(arr[0], password);
		System.out.println("arr[0]私钥=="+arr[0]);
		System.out.println("arr[1]公钥=="+arr[1]);
		System.out.println("加密密碼=="+encPassword);
		System.out.println("解密結果=="+ConfigTools.decrypt( arr[1], encPassword));
	}
}

2、配置文件修改为

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      publicKey:XXXXXXXXX  
      # 数据库
      url: XXXX
      username: root
      password: XXXX 
      driver-class-name: com.mysql.cj.jdbc.Driver
      # 连接池-初始化大小
      initial-size: 10
      # 连接池-最大连接数
      max-active: 100
      # 最大等待时间
      max-wait: 60000
      # 连接池-最小空闲数
      min-idle: 10
      # 检测空闲连接
      test-while-idle: true
      # 最小空闲时间
      min-evictable-idle-time-millis: 300000 
      connection-properties: config.decrypt=true;config.decrypt.key=${spring.datasource.druid.publicKey} 
mybatis-plus:
  configuration:
   log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

3、配置类改为

package com.ld.druid.config;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;

import com.alibaba.druid.filter.config.ConfigTools;
import com.alibaba.druid.pool.DruidDataSource;

@Configuration
public class DruidConfig {
	@Value("${spring.datasource.druid.publicKey}")
	private String publicKey;
    // 这里可以不用ConfigurationProperties  
    //直接new DruidDataSource(),并设定相关属性，也就是 
    代码实现改类对象
	@Bean("dataSource")
	@ConfigurationProperties(prefix = "spring.datasource.druid")
	 DruidDataSource dataSource() {
		return new DruidDataSource();
	}

	@Bean("jdbcTemplate")
	 JdbcTemplate jdbcTemplate() throws Exception {
		DruidDataSource dataSource=	  dataSource();
        String passwordPlainText =         String passwordPlainText = 
        ConfigTools.decrypt(publicKey, dataSource.getPassword()); 
        dataSource.setPassword(passwordPlainText);
		return new JdbcTemplate(dataSource());
	}
}