introduction

In the previous blog post, the database desensitization scenario and scheme have been completely introduced. The sharding JDBC open source project from Jingdong Digital Technology Co., Ltd. has realized this function transparently through the way of data source intermediate agent. However, although the function has been realized, there are still many small sql compatible problems, such as no sub query support at present, no keyword defined by the database, etc In contrast, the fields we need to encrypt and decrypt account for a very small proportion compared with the business sql. Even if they are not compatible with the components, they can be slightly modified and solved. Therefore, the blogger finally gives a relatively complete component integration scheme: multi data source mode. The data source that needs to be encrypted and decrypted is isolated from other data sources of the business. It not only solves the problem of database field encryption and decryption, but also solves the problem of sql compatibility of components. Here are the specific integration steps and points to be noted

Introduce dependency

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

It needs to be explained here that, although the components can not be automatically assembled based on spring boot after the adoption of multi data source compatibility, it is recommended to import the sharding spring boot starter package, because the class of configuration mapping is built in this package, which is very useful when customizing the data source

Add sharding data source configuration

#Database source configuration
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

Exclude automatic assembly

@SpringBootApplication(exclude = SpringBootConfiguration.class)

Since the starter package is imported, you need to exclude the auto loading class manually,

Business data source configuration

After multiple data sources, the data sources of the business itself also need to be configured manually. The default spring boot jpa automatic transfer class will determine whether there is an EntityManagerFactory class in the online document. If there is one, it will not be initialized, so both data sources need to be configured manually

@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;
    }
}

The above code needs to pay attention to three points. First, the configuration of data source is obtained by analyzing the configuration of sharding. Because we have integrated it and do not want to change the configuration, so if we haven't integrated it, we can directly use spring to configure the data source. Second, the initialization of EntityManager is wrapped by SharedEntityManagerCreator, because our business query extends the function by inheriting simplejparpository, and retains the transaction function defined in simplejparpository through SharedEntityManagerCreator. Third, we need to add @ Primary annotation to the configuration of all business data sources, so that the business data source is used by default in the sprign context

Encryption and decryption data source configuration

/**
 * @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;
    }
}

The source of the encryption and decryption data source comes from the business data source, but here it represents a layer of encryption and decryption logic for the business data source. The rules configuration of encryption and decryption adopts the mapping class in the sharding spring boot starter package, so it can be kept consistent with the spring boot configuration.

Use of encryption and decryption data source

When using, because the business data source is used by default, you need to use @ Qualifier("encryptEntityManager") to display the data source agent for injection encryption and decryption where encryption is needed, such as:

@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);
    }
}

In addition, you need to manually specify the transaction manager of the encryption and decryption data source.

If you use interfaces such as JpaRepository, in the annotation enabling @ EnableJpaRepositories, the EntityManagerFactory instance configured by the default loaded business data source needs to be encrypted and decrypted, the following parameters should be set:

@EnableJpaRepositories(basePackages = "com.xxx",entityManagerFactoryRef = "encryptEntityManagerFactory",transactionManagerRef = "encryptTransactionManager")

epilogue

Without perfect components, the data desensitization scheme of sharding JDBC has become perfect. Because of the architecture design of the component itself, it is really not easy to achieve 100% compatibility. When it is found that the encryption and decryption components do not support subqueries, bloggers find that it is very simple to implement this function, and have tried to submit pr of this function to the official. After further understanding of components, it is found that it is very complex to realize this function from a global perspective, so we give up. DBA s who are familiar with java can try it. At present, this multi data source mode can solve the problem of sql compatibility. If there is a better integration scheme, please leave a message below

About the author:

Chen Kailing joined Kaijing technology in May 2016. Responsible for infrastructure middleware iteration, fire brigade leader. Independent blog KL blog( http://www.kailing.pub )Blogger.