多數(shù)據(jù)源模式JPA整合sharding-jdbc實現(xiàn)數(shù)據(jù)脫敏

更新時間：2022年02月24日 09:56:52 作者：kl

這篇文章主要為大家介紹了JPA項目中多數(shù)據(jù)源模式整合sharding-jdbc來實現(xiàn)數(shù)據(jù)脫敏，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步

前言

前一篇博文，透明化Sharding-JDBC數(shù)據(jù)庫字段加解密方案

已經(jīng)完整的介紹了數(shù)據(jù)庫脫敏的場景以及方案，來自京東數(shù)科的Sharding-JDBC開源項目通過對數(shù)據(jù)源中間代理的方式透明化的實現(xiàn)了這個功能，但是，功能雖然實現(xiàn)了，sql兼容的小問題還是很多，比如目前還不支持子查詢，數(shù)據(jù)庫定義的關鍵字不允許使用，等等問題，反觀我們需要加解密的字段，其實占比非常小，即使遇到了和組件不兼容的地方也可以稍加改動解決掉，所以最后博主給出了一個比較完善的組件集成方案：多數(shù)據(jù)源模式，需要加解密的數(shù)據(jù)源和業(yè)務其他數(shù)據(jù)源隔離。

既解決了數(shù)據(jù)庫字段加解密的額問題，同時也解決了組件對sql的兼容問題。

下面是具體的集成步驟以及需要注意的點

引入依賴

            <dependency>
                <groupId>org.apache.shardingsphere</groupId>
                <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
                <version>${sharding.jdbc.version}</version>
            </dependency>

這里需要說明下，雖然采用多數(shù)據(jù)源兼容后，不能使用組件基于spring boot自動裝配功能了，但是這里還是建議導入sharding-spring-boot-starter包，因為這個包下內(nèi)置了配置映射的類，在自定義數(shù)據(jù)源的時候非常有用

添加sharding數(shù)據(jù)源配置

#數(shù)據(jù)庫源配置
spring.shardingsphere.datasource.name = ds
spring.shardingsphere.datasource.ds.type = com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds.jdbc-url = jdbc:mysql://xxx?autoReconnect=true&useUnicode=true&characterEncoding=utf-8
spring.shardingsphere.datasource.ds.username = root
spring.shardingsphere.datasource.ds.password = xxx
spring.shardingsphere.encrypt.encryptors.encryptor_aes.type = aes
spring.shardingsphere.encrypt.encryptors.encryptor_aes.props.aes.key.value = 123456
spring.shardingsphere.encrypt.tables.account.columns.password.plainColumn = password
spring.shardingsphere.encrypt.tables.account.columns.password.cipherColumn = password_encrypt
spring.shardingsphere.encrypt.tables.account.columns.password.encryptor = encryptor_aes
spring.shardingsphere.props.sql.show = true
spring.shardingsphere.props.query.with.cipher.column = true

排除自動裝配

@SpringBootApplication(exclude = SpringBootConfiguration.class)

由于導入了starter包，所以這里需要手動排除自動裝載類，

業(yè)務數(shù)據(jù)源配置

多數(shù)據(jù)源后，業(yè)務本身的數(shù)據(jù)源也需要手動配置，默認的spring boot jpa自動轉載類會判斷上線文中是否存在EntityManagerFactory類，如果有就不會初始化了，所以兩個數(shù)據(jù)源都需要手動配置

@Configuration
@EnableConfigurationProperties(JpaProperties.class)
public class DataSourceConfiguration{
    private final JpaProperties jpaProperties;
    private final Environment environment;

    public DataSourceConfiguration(JpaProperties jpaProperties, Environment environment) {
        this.jpaProperties = jpaProperties;
        this.environment = environment;
    }
    @Primary
    @Bean
    public DataSource dataSource(){
        String prefix = "spring.shardingsphere.datasource.";
        String each = getDataSourceNames(prefix).get(0);
        try {
            return  getDataSource(prefix, each);
        } catch (final ReflectiveOperationException ex) {
            throw new ShardingSphereException("Can't find datasource type!", ex);
        }
    }
    @Primary
    @Bean
    public EntityManagerFactory entityManagerFactory() {
        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        vendorAdapter.setDatabase(Database.MYSQL);
        vendorAdapter.setGenerateDdl(true);
        vendorAdapter.setShowSql(true);
        LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
        factory.setJpaVendorAdapter(vendorAdapter);
        factory.setPersistenceUnitName("default");
        factory.setPackagesToScan(Constants.BASE_PACKAGES);
        factory.setDataSource(dataSource());
        factory.setJpaPropertyMap(jpaProperties.getProperties());
        factory.afterPropertiesSet();
        return factory.getObject();
    }
    @Bean
    @Primary
    public EntityManager entityManager(EntityManagerFactory entityManagerFactory){
        return SharedEntityManagerCreator.createSharedEntityManager(entityManagerFactory);
    }
    @Primary
    @Bean
    public PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory){
        JpaTransactionManager txManager = new JpaTransactionManager();
        txManager.setEntityManagerFactory(entityManagerFactory);
        return txManager;
    }
    private ListgetDataSourceNames(final String prefix) {
        StandardEnvironment standardEnv = (StandardEnvironment) environment;
        standardEnv.setIgnoreUnresolvableNestedPlaceholders(true);
        return null == standardEnv.getProperty(prefix + "name")
                ? new InlineExpressionParser(standardEnv.getProperty(prefix + "names")).splitAndEvaluate() : Collections.singletonList(standardEnv.getProperty(prefix + "name"));
    }
    @SuppressWarnings("unchecked")
    private DataSource getDataSource(final String prefix, final String dataSourceName) throws ReflectiveOperationException {
        Map dataSourceProps = PropertyUtil.handle(environment, prefix + dataSourceName.trim(), Map.class);
        Preconditions.checkState(!dataSourceProps.isEmpty(), "Wrong datasource properties!");
        DataSource result = DataSourceUtil.getDataSource(dataSourceProps.get("type").toString(), dataSourceProps);
        DataSourcePropertiesSetterHolder.getDataSourcePropertiesSetterByType(dataSourceProps.get("type").toString()).ifPresent(
                dataSourcePropertiesSetter -> dataSourcePropertiesSetter.propertiesSet(environment, prefix, dataSourceName, result));
        return result;
    }
}

上面代碼需要注意三個地方

一是數(shù)據(jù)源的配置，是以sharding的配置來解析獲得的，是因為我們已經(jīng)集成過了，不想改動配置，所以如此，如果還沒集成過，可以直接使用spring 配置數(shù)據(jù)源的方式配置即可。

二是EntityManager的初始化，通過SharedEntityManagerCreator包裝了下，是因為我們業(yè)務的查詢通過繼承SimpleJpaRepository來擴展功能的，通過SharedEntityManagerCreator包裝保留了完整的事務功能。

三是需要給所有的業(yè)務數(shù)據(jù)源的配置添加 @Primary注解，讓sprign上下文默認使用業(yè)務數(shù)據(jù)源

加解密數(shù)據(jù)源配置

/**
 * @author: kl @kailing.pub
 * @date: 2020/5/18
 */
@Configuration
@EnableConfigurationProperties({JpaProperties.class,SpringBootEncryptRuleConfigurationProperties.class, SpringBootPropertiesConfigurationProperties.class})
@AutoConfigureAfter({DataSourceAutoConfiguration.class, DataSourceConfiguration.class})
public class EncryptDataSourceConfiguration {
    private final SpringBootPropertiesConfigurationProperties props;
    private final SpringBootEncryptRuleConfigurationProperties encryptRule;
    private final JpaProperties jpaProperties;
    private final DataSource dataSource;
    public EncryptDataSourceConfiguration(SpringBootPropertiesConfigurationProperties props, SpringBootEncryptRuleConfigurationProperties encryptRule, JpaProperties jpaProperties, DataSource dataSource) {
        this.props = props;
        this.encryptRule = encryptRule;
        this.jpaProperties = jpaProperties;
        this.dataSource = dataSource;
    }
    @Bean
    public DataSource encryptDataSource() throws SQLException {
        return EncryptDataSourceFactory.createDataSource(dataSource, new EncryptRuleConfigurationYamlSwapper().swap(encryptRule), props.getProps());
    }
    @Bean
    public EntityManagerFactory encryptEntityManagerFactory() throws SQLException {
        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        vendorAdapter.setDatabase(Database.MYSQL);
        LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
        factory.setJpaVendorAdapter(vendorAdapter);
        factory.setPersistenceUnitName("encryptPersistenceUnit");
        factory.setPackagesToScan(Constants.BASE_PACKAGES);
        factory.setDataSource(encryptDataSource());
        factory.setJpaPropertyMap(jpaProperties.getProperties());
        factory.afterPropertiesSet();
        return factory.getObject();
    }
    @Bean
    public EntityManager encryptEntityManager() throws SQLException {
        return SharedEntityManagerCreator.createSharedEntityManager(encryptEntityManagerFactory());
    }
    @Bean
    public PlatformTransactionManager encryptTransactionManager() throws SQLException {
        JpaTransactionManager txManager = new JpaTransactionManager();
        txManager.setEntityManagerFactory(encryptEntityManagerFactory());
        return txManager;
    }
}

加解密數(shù)據(jù)源的源來自于業(yè)務數(shù)據(jù)源，只是在這里給業(yè)務數(shù)據(jù)源又代理了一層加解密的邏輯。加解密的規(guī)則配置采用了sharding-spring-boot-starter包中的映射類，所以可以保留和spring boot配置方式一致。

加解密數(shù)據(jù)源的使用

在使用時，因為默認使用的是業(yè)務數(shù)據(jù)源，所以需要在需要加解密的地方通過@Qualifier("encryptEntityManager")顯示的注入加解密的數(shù)據(jù)源代理，如：

@Repository
public class AccountRepository extends AbstractJpaRepository {

    public AccountRepository(@Qualifier("encryptEntityManager") EntityManager em) {
        super(AccountModel.class, em);
    }
    @Override
    @Transactional(transactionManager = "encryptTransactionManager")
    public  S save(S entity) {
        return super.save(entity);
    }
}

另，需要手動指定加解密數(shù)據(jù)源的事務管理器

結語

沒有十全十美的組件，Sharding-JDBC的數(shù)據(jù)脫敏方案已經(jīng)趨向于完美了。由于組件本身的架構設計，確實不好做到100%的兼容。在發(fā)現(xiàn)加解密組件不支持子查詢時，博主發(fā)現(xiàn)實現(xiàn)這個功能很簡單，嘗試過向官方添加這個功能提交pr。經(jīng)過對組件的進一步了解發(fā)現(xiàn)，從全局考慮實現(xiàn)這個功能非常復雜，也就放棄了。目前這個多數(shù)據(jù)源模式可以很好的解決這個問題，如果有更好的集成方案，歡迎在下面留言交流

以上就是多數(shù)據(jù)源模式JPA整合sharding-jdbc實現(xiàn)數(shù)據(jù)脫敏的詳細內(nèi)容，更多關于多數(shù)據(jù)源JPA整合sharding-jdbc數(shù)據(jù)脫敏的資料請關注腳本之家其它相關文章！

您可能感興趣的文章: