[SpringCloud]~Druid（数据库连接池+加密解密）_cloud 连接数据库解密

官方文档

配置文件

spring:
  datasource:
    url: xxxxxx # url
    username: xxxxxx # 用户名
    password: xxxxxx # 私钥加密过的密码
    publicKey: xxxxxx #公钥
  #Druid 连接池通用配置
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      # 下面为连接池的补充设置，应用到上面所有数据源中
      # 初始化大小，最小，最大
      initial-size: 5
      min-idle: 5
      max-active: 20
      # 配置获取连接等待超时的时间
      max-wait: 60000
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      time-between-eviction-runs-millis: 60000
      # 配置一个连接在池中最小生存的时间，单位是毫秒
      min-evictable-idle-time-millis: 300000
      # sql 校验
      validation-query: select count(1) from sys.objects Where type='U' And type_desc='USER_TABLE'
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      # 打开PSCache，并且指定每个连接上PSCache的大小
      pool-prepared-statements: true
      #   配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
      max-pool-prepared-statement-per-connection-size: 20
      filters: stat # wall 若开启 wall，会把 if 中的 and 判断为注入进行拦截
      use-global-data-source-stat: true
      # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
      connect-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

POM引用

Druid 内置的非对称加密方式，因此这里的数据库密码是加密过的
我们需要通过这个配置文件对密码进行个解密，然后再连接数据库
加依赖，通过这个依赖，才能通过配置文件对对象中的值进行注入

<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>druid</artifactId>
	<version>1.1.21</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.21</version>
</dependency>
<!-- 配置文件处理器 -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-configuration-processor</artifactId>
</dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

Config代码及加解密测试代码

package com.qy.test.demo;

import com.alibaba.druid.filter.config.ConfigTools;
import com.alibaba.druid.pool.DruidDataSource;
import lombok.Data;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
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.context.annotation.Primary;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @Auther: Administrator
 * @Date: 2019/12/6 11:36
 * @Description: 
 */
@Configuration
@ConfigurationProperties(prefix = "spring.datasource")
@Data
public class DruidTest {

    private Logger logger = LoggerFactory.getLogger(DruidTest.class);

    @Value("${spring.datasource.url}")
    private String url;

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;

    @Value("${spring.datasource.url}")
    private String type;

    @Value("${spring.datasource.publicKey}")
    private String publicKey;

    @Value("${spring.datasource.druid.initial-size}")
    private Integer initialSize;

    @Value("${spring.datasource.druid.min-idle}")
    private Integer minIdle;

    @Value("${spring.datasource.druid.max-active}")
    private Integer maxActive;

    @Value("${spring.datasource.druid.max-wait}")
    private Integer maxWait;

    @Value("${spring.datasource.druid.time-between-eviction-runs-millis}")
    private Integer timeBetweenEvictionRunsMillis;

    @Value("${spring.datasource.druid.min-evictable-idle-time-millis}")
    private Integer minEvictableIdleTimeMillis;

    @Value("${spring.datasource.druid.validation-query}")
    private String validationQuery;

    @Value("${spring.datasource.druid.test-while-idle}")
    private Boolean testWhileIdle;

    @Value("${spring.datasource.druid.test-on-borrow}")
    private Boolean testOnBorrow;

    @Value("${spring.datasource.druid.test-on-return}")
    private Boolean testOnReturn;

    @Value("${spring.datasource.druid.pool-prepared-statements}")
    private Boolean poolPreparedStatements;

    @Value("${spring.datasource.druid.max-pool-prepared-statement-per-connection-size}")
    private Integer maxPoolPreparedStatementPerConnectionSize;

    @Value("${spring.datasource.druid.filters}")
    private String filters;

    @Value("${spring.datasource.druid.use-global-data-source-stat}")
    private Boolean useGlobalDataSourceStat;

    @Value("${spring.datasource.druid.connect-properties}")
    private Properties connectProperties;

    /**
     * 数据库参数注入
     *
     * @return
     * @throws Exception
     */
    @Bean
    @Primary
    public DataSource druidDataSource() throws Exception {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(url);
        datasource.setUsername(username);
        // 解密后，再 set 进对象
        datasource.setPassword(ConfigTools.decrypt(publicKey, password));
        logger.info("密码：" + ConfigTools.decrypt(publicKey, password));
        datasource.setInitialSize(initialSize);
        datasource.setMinIdle(minIdle);
        datasource.setMaxActive(maxActive);
        datasource.setMaxWait(maxWait);
        datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        datasource.setValidationQuery(validationQuery);
        datasource.setTestWhileIdle(testWhileIdle);
        datasource.setTestOnBorrow(testOnBorrow);
        datasource.setTestOnReturn(testOnReturn);
        datasource.setUseGlobalDataSourceStat(useGlobalDataSourceStat);
        datasource.setConnectProperties(connectProperties);

        try {
            datasource.setFilters(filters);
        } catch (SQLException e) {
            logger.error("========druid configuration initialization filter========", e);
        }
        return datasource;
    }

    /**
     * 生成公私钥以及加密密码
     *
     * @param args
     * @throws Exception
     */
    public static void main(String[] args) throws Exception {
        String password = "koda%2)-s";
        //生成私钥公钥
        String[] arr = ConfigTools.genKeyPair(512);
        System.out.println("password:" + password);
        System.out.println("privateKey:" + arr[0]);
        System.out.println("publicKey:" + arr[1]);
        //用私钥进行加密
        System.out.println("加密后password:" + ConfigTools.encrypt(arr[0], password));
        //用公钥进行解密
        System.out.println("解密后password:" + ConfigTools.decrypt(arr[1], ConfigTools.encrypt(arr[0], password)));
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145

加解密结果

password:koda%2)-s
privateKey:MIIBVQIBADANBgkqhkiG9w0BAQEFAASCAT8wggE7AgEAAkEAuwvgIyIceAfBIdGCbBkgRkLff0ryZ/10yZy5LvbJC8lmxaHO5oQXL0PmatcFK98paY+swJnc4XXaPVAy5IHIDwIDAQABAkB10MUdXB8Se8/qvUypSipKF1UvmMWXOs6VL2J1cnywLtZfUVPiNyZbUCg1vujdP4yLbljfiafJxix8TCfVPWQBAiEA9QFZvqPDbl69rhBCik6LnaUuPkHiYYPtwZE8+HQ/vrkCIQDDcK+7zGh+oPSJSN+zck9RWtZKsB1wh1Fieue+AH+ZBwIhAIOo8CNEObdL7j5lunw8bUaQB2OpYA/rJ48Hj24sejzxAiBclVdKeazOv+TX3CPM2Evm2EdHnmJz2kadhmedxJKtpQIhAO3ITiPHxSqnQYltVFepMRMcRnRdQHkk7e4ixqvVaPUT
publicKey:MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALsL4CMiHHgHwSHRgmwZIEZC339K8mf9dMmcuS72yQvJZsWhzuaEFy9D5mrXBSvfKWmPrMCZ3OF12j1QMuSByA8CAwEAAQ==
加密后password:k3JtO4MQyh9lE3wzhbPL3lXw3gMaL0vUHMXwLlV41/RdK/nTmLUJAX42Q6ujfIle9eynoiUCE6nBSUINhEDdNA==
解密后password:koda%2)-s
1
2
3
4
5