devbox
正经夜光杯
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

SpringBoot2.0.5 + jpa + druid 多数据源 数据库密码加密_spring.datasource.druid.primary

作者：正经夜光杯 | 2024-07-18 07:06:30

赞

踩

spring.datasource.druid.primary

SpringBoot2.0.5 + jpa + druid 多数据源 数据库密码加密

文章目录

一、druid 多数据源

1. pom.xml

建议使用1.1.10以上版本

<!-- druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <!-- 若项目本来用了logback 可以不引入，设置filters 为slf4j  -->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
		<!--   sqlserver数据库连接  -->
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!-- mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.21</version>
            <scope>runtime</scope>
        </dependency>
		<!--用于读取配置-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>

  • 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

2.配置文件 application.properties

#druid的一些基本配置
spring.datasource.druid.default.initialSize = 5
spring.datasource.druid.default.minIdle = 5
spring.datasource.druid.default.maxActive = 30
spring.datasource.druid.default.maxWait = 1200000
spring.datasource.druid.default.timeBetweenEvictionRunsMillis = 1200000
spring.datasource.druid.default.minEvictableIdleTimeMillis = 300000
spring.datasource.druid.default.primary-validationQuery = SELECT 'x'
spring.datasource.druid.default.secondary-validationQuery = SELECT 1
spring.datasource.druid.default.testWhileIdle = true
spring.datasource.druid.default.testOnBorrow = false
spring.datasource.druid.default.testOnReturn = false
spring.datasource.druid.default.poolPreparedStatements = true
spring.datasource.druid.default.maxPoolPreparedStatementPerConnectionSize = 30
spring.datasource.druid.primary.filters=stat,wall,slf4j
spring.datasource.druid.default.connectionProperties = druid.stat.mergeSql=false;druid.stat.slowSqlMillis=5000
spring.aop.proxy-target-class = true
# merge multi DruidDataSource Data
spring.datasource.druid.default.useGlobalDataSourceStat = true

# 主数据源
spring.datasource.druid.primary.url=jdbc:sqlserver://xxx:1433;DatabaseName=xxx
spring.datasource.druid.primary.username=xxx
spring.datasource.druid.primary.password=xxx
spring.datasource.druid.primary.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.druid.primary.primary-dialect=org.hibernate.dialect.SQLServer2012Dialect
# 部分sql由于wall查询存在错误
spring.datasource.druid.primary.filters=
# 第二数据源 mysql
spring.datasource.druid.secondary.url=jdbc:mysql://xxx:3306/xxx?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
spring.datasource.druid.secondary.username=xxx
spring.datasource.druid.secondary.password=xxx
spring.datasource.druid.secondary.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.druid.secondary.primary-dialect=org.hibernate.dialect.MySQLDialect

  • 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

3.DruidDefaultPropertiesConfig 用于接收通用的druid配置

import lombok.Data;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

@Data
@Component
@ConfigurationProperties(prefix = "spring.datasource.druid.default")
public class DruidDefaultPropertiesConfig {
    private int initialSize;
    private int minIdle;
    private int maxActive;
    private int maxWait;
    private int timeBetweenEvictionRunsMillis;
    private int minEvictableIdleTimeMillis;
    private String validationQuery;
    private boolean testWhileIdle;
    private boolean testOnBorrow;
    private boolean testOnReturn;
    private boolean poolPreparedStatements;
    private int maxPoolPreparedStatementPerConnectionSize;
    private String filters;
    private String connectionProperties;
    private boolean useGlobalDataSourceStat;
}

  • 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

4.设置DataSource

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
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 javax.xml.crypto.Data;
import java.sql.SQLException;

@Configuration
public class DruidDataSourceConfig {

    @Autowired
    DruidDefaultPropertiesConfig druidDefaultPropertiesConfig;

    @Primary
    @Qualifier("primaryDataSource")
    @Bean(name = "primaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.druid.primary")
    public DataSource primaryDataSource() {
        return getDruidDataSource();
    }

    @Qualifier("secondaryDataSource")
    @Bean(name = "secondaryDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.druid.secondary")
    public DataSource secondaryDataSource() {
        return getDruidDataSource();
    }

    private DruidDataSource getDruidDataSource() {
        DruidDataSource datasource = DruidDataSourceBuilder.create().build();
        datasource.setInitialSize(druidDefaultPropertiesConfig.getInitialSize());
        datasource.setMinIdle(druidDefaultPropertiesConfig.getMinIdle());
        datasource.setMaxActive(druidDefaultPropertiesConfig.getMaxActive());
        datasource.setMaxWait(druidDefaultPropertiesConfig.getMaxWait());
        datasource.setTimeBetweenEvictionRunsMillis(druidDefaultPropertiesConfig.getTimeBetweenEvictionRunsMillis());
        datasource.setMinEvictableIdleTimeMillis(druidDefaultPropertiesConfig.getMinEvictableIdleTimeMillis());
        datasource.setValidationQuery(druidDefaultPropertiesConfig.getValidationQuery());
        datasource.setTestWhileIdle(druidDefaultPropertiesConfig.isTestWhileIdle());
        datasource.setTestOnBorrow(druidDefaultPropertiesConfig.isTestOnBorrow());
        datasource.setTestOnReturn(druidDefaultPropertiesConfig.isTestOnReturn());
        datasource.setPoolPreparedStatements(druidDefaultPropertiesConfig.isPoolPreparedStatements());
        datasource.setMaxPoolPreparedStatementPerConnectionSize(druidDefaultPropertiesConfig.getMaxPoolPreparedStatementPerConnectionSize());
        datasource.setUseGlobalDataSourceStat(druidDefaultPropertiesConfig.isUseGlobalDataSourceStat());
        try {
            datasource.setFilters(druidDefaultPropertiesConfig.getFilters());
        } catch (SQLException e) {
            System.err.println("druid configuration initialization filter: "+ e);
        }
        datasource.setConnectionProperties(druidDefaultPropertiesConfig.getConnectionProperties());
        return datasource;
    }
}

  • 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

5.数据源设置

第一数据源

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.Map;

@Configuration
@EnableTransactionManagement
//设置Repository所在位置
@EnableJpaRepositories(
        entityManagerFactoryRef="entityManagerFactoryPrimary",
        transactionManagerRef="transactionManagerPrimary",
        basePackages= { "xx.repository",
                "xx.xx.repository",
                "xx.xx.repository" })
public class PrimaryConfig {

    @Autowired
    @Qualifier("primaryDataSource")
    private DataSource primaryDataSource;

    @Autowired
    private JpaProperties jpaProperties;

    @Primary
    @Bean(name = "entityManagerPrimary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactoryPrimary(builder).getObject().createEntityManager();
    }

    @Primary
    @Bean(name = "entityManagerFactoryPrimary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary (EntityManagerFactoryBuilder builder) {
        //设置实体类位置
        String[] packages = new String[]{
            "xxx.domain",
            "xxx.domain",
        };
        LocalContainerEntityManagerFactoryBean entityManagerFactory = builder
                .dataSource(primaryDataSource)
                .packages(packages)
                .properties(getVendorProperties())
                .persistenceUnit("primaryPersistenceUnit")
                .build();
        return entityManagerFactory;
    }

    @Value("${spring.datasource.druid.primary.primary-dialect}")
    private String primaryDialect;

    private Map<String, Object> getVendorProperties() {
        Map<String, Object> properties = jpaProperties.getHibernateProperties(new HibernateSettings());
        properties.put("hibernate.dialect",primaryDialect);
        return properties;
    }

    @Primary
    @Bean(name = "transactionManagerPrimary")
    public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
    }

}

  • 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

第二数据源

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.orm.jpa.HibernatePropertiesCustomizer;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateSettings;
import org.springframework.boot.autoconfigure.orm.jpa.JpaProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.persistence.EntityManager;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

@Configuration
@EnableTransactionManagement
//设置Repository所在位置
@EnableJpaRepositories(
        entityManagerFactoryRef="entityManagerFactorySecondary",
        transactionManagerRef="transactionManagerSecondary",
        basePackages= { "xx.repository" })
public class SecondaryConfig {
    @Autowired
    @Qualifier("secondaryDataSource")
    private DataSource secondaryDataSource;

    @Autowired
    private JpaProperties jpaProperties;

    @Bean(name = "entityManagerSecondary")
    public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
        return entityManagerFactoryPrimary(builder).getObject().createEntityManager();
    }

    @Bean(name = "entityManagerFactorySecondary")
    public LocalContainerEntityManagerFactoryBean entityManagerFactoryPrimary(EntityManagerFactoryBuilder builder) {
        //设置实体类位置
        String[] packages = new String[]{"xxx.domain"};
        LocalContainerEntityManagerFactoryBean entityManagerFactory = builder
                .dataSource(secondaryDataSource)
                .packages(packages)
                .properties(getVendorProperties())
                .persistenceUnit("secondaryPersistenceUnit")
                .build();
        entityManagerFactory.setJpaPropertyMap(jpaProperties.getProperties());
        return entityManagerFactory;

    }

    @Value("${spring.datasource.druid.secondary.primary-dialect}")
    private String secondaryDialect;

    private Map<String, Object> getVendorProperties() {
        Map<String, Object> properties = jpaProperties.getHibernateProperties(new HibernateSettings());
        properties.put("hibernate.dialect",secondaryDialect);
        return properties;
    }


    @Bean(name = "transactionManagerSecondary")
    public PlatformTransactionManager transactionManagerPrimary(EntityManagerFactoryBuilder builder) {
        return new JpaTransactionManager(entityManagerFactoryPrimary(builder).getObject());
    }
}



  • 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

需要注意这里是springboot 2.0.X的写法
其他写法参考 文章 未验证
SpringBoot 1.5.x 需要修改getVendorPropertiest方法

private Map<String, String> getVendorProperties() {
 return jpaProperties.getHibernateProperties(userDataSource);
}

  • 1
  • 2
  • 3

springBoot2.1的写法

private Map<String, Object> getVendorProperties() {
    return hibernateProperties.determineHibernateProperties(jpaProperties.getProperties(), new HibernateSettings());
}

  • 1
  • 2
  • 3

7.需要注意的坑

1.不同类型数据库需要设置hibernate.dialect ，需要根据数据库版本设置

2.spring.datasource.druid.primary.filters=stat,wall,slf4j 原来项目中有一些sql由于设置了wall ，查询报错，目前只能去掉wall的设置

二.数据库密码加密

1. 使用druid密码加密

druid 官方文档

使用druid jar加密，获取到publickey和password

java -cp druid-1.0.16.jar com.alibaba.druid.filter.config.ConfigTools you_password

  • 1

解密类

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


public class DesPassword extends DruidPasswordCallback {

    public static void main(String[] args) throws Exception{
        String publickey = "";
        String password = "";
        String pwd = ConfigTools.decrypt(publickey, password);
        System.out.println(pwd);
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

修改配置文件

# 公钥
secondary.publickey=${spring.datasource.druid.secondary.publickey}
# 配置 connection-properties，启用加密，配置公钥。
spring.datasource.druid.secondary.connection-properties=config.decrypt=true;config.decrypt.key=${secondary.publickey};druid.stat.mergeSql=false;druid.stat.slowSqlMillis=5000
# 启动ConfigFilter
spring.datasource.druid.secondary.filters=stat,wall,slf4j,config

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

其中spring.datasource.druid.secondary.publickey 设置到jvm中，避免暴露

#idea中
-Dspring.datasource.druid.secondary.publickey=
#Java启动
java -jar xxx.jar --spring.datasource.druid.secondary.publickey=

  • 1
  • 2
  • 3
  • 4

2.使用Jasypt 加密

增加依赖

<!-- jasypt -->
<dependency>
 <groupId>com.github.ulisesbocchio</groupId>
 <artifactId>jasypt-spring-boot-starter</artifactId>
 <version>3.0.3</version>
</dependency>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

配置

 # 加密盐值
jasypt.encryptor.password=abc
# 加密算法设置 3.0.0 以后
jasypt.encryptor.algorithm=PBEWithMD5AndDES
jasypt.encryptor.iv-generator-classname=org.jasypt.iv.NoIvGenerator

  • 1
  • 2
  • 3
  • 4
  • 5

加密盐值不建议配置在配置文件

#设置jvm
 -Djasypt.encryptor.password=abc
#java 
java  -jar xx.jar --jasypt.encryptor.password=abc

  • 1
  • 2
  • 3
  • 4

工具类加密解密

import org.jasypt.util.text.BasicTextEncryptor;
import org.junit.Test;

public class JasyptUtil {
    @Test
    public void jasyptTest() {
        BasicTextEncryptor encryptor = new BasicTextEncryptor();
        // application.properties, jasypt.encryptor.password
        encryptor.setPassword("abc");
        // encrypt root
        System.out.println(encryptor.encrypt("1234"));
        System.out.println(encryptor.decrypt(""));
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

配置密码

spring.datasource.druid.primary.password=ENC(加密后密码)

  • 1

参考文章

Spring Boot Jpa多数据源配置
SpringBoot敏感配置加密：Druid
jasypt:
数据库密码配置项都不加密？心也太大了吧！
在Springboot中通过jasypt 进行加密解密
SpringBoot 集成 Jasypt 对数据库加密以及踩坑的记录分享

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/正经夜光杯/article/detail/844168
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号