mysql读写分离sharejdbc_Spring Boot + Sharding-JDBC实现读写分离和数据脱敏

Spring Boot + Sharding-JDBC实现读写分离和数据脱敏

使用官网上的读写分离配置+脱敏配置，发现脱敏没有效果，

尝试了手动配置数据源，测试有效。

注：如果脱敏字段的值为null时,脱敏会报空指针异常。

1.pom文件

org.apache.commons

commons-dbcp2

2.6.0

org.apache.shardingsphere

sharding-jdbc-core

4.0.0-RC1

2.添加property配置

datasource.master.type=org.apache.commons.dbcp2.BasicDataSource

datasource.master.driver-class-name=com.mysql.jdbc.Driver

datasource.master.url=jdbc:mysql://192.168.1.196:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false

datasource.master.username=

datasource.master.password=

datasource.slave0.type=org.apache.commons.dbcp2.BasicDataSource

datasource.slave0.driver-class-name=com.mysql.jdbc.Driver

datasource.slave0.url=jdbc:mysql://192.168.1.195:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false

datasource.slave0.username=

datasource.slave0.password=

#加密方式 自带AES和MD5 可自定义

encrypt.type=AES

#加密字段，多个用,分隔，格式：表名.字段名

encrypt.qualifiedColumns=

#AES加密所需密钥

encrypt.aes.key.value=

3.配置数据源

import org.apache.commons.dbcp2.BasicDataSource;

import org.apache.shardingsphere.api.config.encryptor.EncryptRuleConfiguration;

import org.apache.shardingsphere.api.config.encryptor.EncryptorRuleConfiguration;

import org.apache.shardingsphere.api.config.masterslave.MasterSlaveRuleConfiguration;

import org.apache.shardingsphere.shardingjdbc.api.EncryptDataSourceFactory;

import org.apache.shardingsphere.shardingjdbc.api.MasterSlaveDataSourceFactory;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.context.annotation.Bean;

import org.springframework.context.annotation.ComponentScan;

import org.springframework.context.annotation.Configuration;

import org.springframework.core.env.Environment;

import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;

import java.sql.SQLException;

import java.util.*;

/**

* Created by 南柯一梦 on 2019/6/22.

*/

@Configuration

@ComponentScan

@EnableTransactionManagement

public class DataSourceConfig {

private final Environment environment;

@Autowired

public DataSourceConfig(Environment environment) {

this.environment = environment;

}

@Bean(name = "dataSource")

public DataSource getDataSource() throws SQLException {

//主数据源名称

String masterName = "master";

String slaveName0 = "slave0";

List slaveNames = new ArrayList<>();

slaveNames.add(slaveName0);

// 配置脱敏规则

Properties props = new Properties();

props.setProperty("aes.key.value", environment.getProperty("encrypt.aes.key.value"));

EncryptorRuleConfiguration encryptorConfig = new EncryptorRuleConfiguration(environment.getProperty("encrypt.type"), environment.getProperty("encrypt.qualifiedColumns"), props);

EncryptRuleConfiguration ruleConfiguration = new EncryptRuleConfiguration();

ruleConfiguration.getEncryptorRuleConfigs().put("encryptor", encryptorConfig);

// 配置真实数据源

Map dataSourceMap = new HashMap<>();

// 配置主库

BasicDataSource masterDataSource = new BasicDataSource();

masterDataSource.setDriverClassName(environment.getProperty("datasource.master.driver-class-name"));

masterDataSource.setUrl(environment.getProperty("datasource.master.url"));

masterDataSource.setUsername(environment.getProperty("datasource.master.username"));

masterDataSource.setPassword(environment.getProperty("datasource.master.password"));

// 获取数据源对象,加入脱敏规则

DataSource dataSource = EncryptDataSourceFactory.createDataSource(masterDataSource, ruleConfiguration);

dataSourceMap.put(masterName, dataSource);

// 配置第一个从库

BasicDataSource slaveDataSource1 = new BasicDataSource();

slaveDataSource1.setDriverClassName(environment.getProperty("datasource.slave0.driver-class-name"));

slaveDataSource1.setUrl(environment.getProperty("datasource.slave0.url"));

slaveDataSource1.setUsername(environment.getProperty("datasource.slave0.username"));

slaveDataSource1.setPassword(environment.getProperty("datasource.slave0.password"));

// 获取数据源对象,加入脱敏规则

DataSource dataSource1 = EncryptDataSourceFactory.createDataSource(slaveDataSource1, ruleConfiguration);

dataSourceMap.put(slaveName0, slaveDataSource1);

// 配置读写分离规则

MasterSlaveRuleConfiguration masterSlaveRuleConfig = new MasterSlaveRuleConfiguration("ds_master_slave", masterName, slaveNames);

// 获取数据源对象

DataSource dataSource2 = MasterSlaveDataSourceFactory.createDataSource(dataSourceMap, masterSlaveRuleConfig, new Properties());

return dataSource2;

}

}

--------20190624更新---------

解决空指针异常：

1.定位问题原因：

通过debug，发现问题出在在EncryptSQLRewriteEngine类中通过字段名获取值转字符串的时候

private List> getOriginalColumnValuesFromUpdateItem(final EncryptColumnToken encryptColumnToken) {

List> result = new LinkedList<>();

SQLExpression sqlExpression = ((DMLStatement) sqlStatement).getUpdateColumnValues().get(encryptColumnToken.getColumn());

if (sqlExpression instanceof SQLPlaceholderExpression) {

//当脱敏字段值为null时，((SQLPlaceholderExpression) sqlExpression).getIndex())为null

result.add(parameters.get(((SQLPlaceholderExpression) sqlExpression).getIndex()).toString());

} else if (sqlExpression instanceof SQLTextExpression) {

result.add(((SQLTextExpression) sqlExpression).getText());

} else if (sqlExpression instanceof SQLNumberExpression) {

result.add((Comparable) ((SQLNumberExpression) sqlExpression).getNumber());

}

return result;

}

2.解决方案：

增加一个判断，如果为null则赋值空字符串

private List> getOriginalColumnValuesFromUpdateItem(final EncryptColumnToken encryptColumnToken) {

List> result = new LinkedList<>();

SQLExpression sqlExpression = ((DMLStatement) sqlStatement).getUpdateColumnValues().get(encryptColumnToken.getColumn());

if (sqlExpression instanceof SQLPlaceholderExpression) {

Object object = parameters.get(((SQLPlaceholderExpression) sqlExpression).getIndex());

if (object != null) {

result.add(object.toString());

} else {

result.add("");

}

} else if (sqlExpression instanceof SQLTextExpression) {

result.add(((SQLTextExpression) sqlExpression).getText());

} else if (sqlExpression instanceof SQLNumberExpression) {

result.add((Comparable) ((SQLNumberExpression) sqlExpression).getNumber());

}

return result;

}

工程目录下执行mvn clean install -Prelease重新构建工程，测试，问题已解决。

--------20190625更新---------

上述方法有个缺陷：数据库中会保存空字符串的密文。

改进如下：

1.在获取值的时间判断是否为空，为空则不处理

private List> getOriginalColumnValuesFromUpdateItem(final EncryptColumnToken encryptColumnToken) {

List> result = new LinkedList<>();

SQLExpression sqlExpression = ((DMLStatement) sqlStatement).getUpdateColumnValues().get(encryptColumnToken.getColumn());

if (sqlExpression instanceof SQLPlaceholderExpression) {

Object object = parameters.get(((SQLPlaceholderExpression) sqlExpression).getIndex());

//为空不处理

if (object != null) {

result.add(object.toString());

}

} else if (sqlExpression instanceof SQLTextExpression) {

result.add(((SQLTextExpression) sqlExpression).getText());

} else if (sqlExpression instanceof SQLNumberExpression) {

result.add((Comparable) ((SQLNumberExpression) sqlExpression).getNumber());

}

return result;

}

2.在用密文替换原SQL中的值的时候判断是否有值，为空则不处理

private void encryptParameters(final Map positionIndexes, final List> encryptColumnValues) {

if (!positionIndexes.isEmpty()) {

for (Entry entry : positionIndexes.entrySet()) {

//为空不处理

if (!encryptColumnValues.isEmpty()){

parameters.set(entry.getValue(), encryptColumnValues.get(entry.getKey()));

}

}

}

}

重新构建并测试通过。