Spring动态数据源-AbstractRoutingDataSource（实现分库下数据源切换）
一、分库分表思路
https://www.cnblogs.com/yangyongjie/p/13803706.html
二、Spring动态数据源实现分库下数据源切换
在分库分表的情况下，在执行SQL时选择连接不同的数据源（库）的思路：配置多个数据源加到动态数据源对象中，根据实际的情况动态切换到相应的数据源中。
也可实现主从数据源的切换
AbstractRoutingDataSource（ARDS）动态数据源切换的原理：
1、Map<Object, Object> targetDataSources 保存了所有可能的数据源，key为数据库的key，value为DataSource对象或字符串形式的连接信息
2、Object defaultTargetDataSource 保存了默认的数据源，用于找不到具体的数据源时使用
3、afterPropertiesSet() 方法
①：解析targetDataSources数据源信息成<key,DataSource>的形式，保存在Map<Object, DataSource> resolvedDataSources 中
②：将defaultTargetDataSource中的默认数据源信息解析成 DataSource对象保存在 DataSource resolvedDefaultDataSource 中
4、determineCurrentLookupKey() 提供给子类重写，指定当前线程使用的具体的数据源的key
5、determineTargetDataSource() 根据 determineCurrentLookupKey()方法返回的key 返回数据源DataSouce对象，若没有，则使用默认数据源对象
6、getConnection() 根据determineTargetDataSource()返回的数据源，与其建立连接
ARDS 使用步骤：
1、在配置文件中配置所有可用的数据源连接信息
2、自定义DynamicDataSource继承 AbstractRoutingDataSource抽象类
①：重写afterPropertiesSet() 方法，设置 targetDataSources和defaultTargetDataSource，并调用父类的afterPropertiesSet() 方法
②：重写 determineCurrentLookupKey() 方法，从当前线程的 ThreadLocal 变量中获取 当前线程使用的数据源的key
③：配置自定义的DynamicDataSource替代传统的DataSource方式，并配置 SqlSessionTemplate-> SqlSessionFactory-> DataSource 和 DataSourceTransactionManager-> DataSource
3、使用注解拦截的方式，在操作数据库之前，根据当前线程的一些规则，确定其使用的数据源的key，并保存在当前线程的 ThreadLocal中，供determineCurrentLookupKey()获取。
示例：
如存放订单信息的有10个库，每个库中有100张表，根据用户的ID做分库分表。可以选择ID的某一位的值作为选择的第几个库，某两位的值作为对应的表名。
实现：在service中使用AOP拦截具有某个注解的方法，根据方法中的参数的用户信息，按照上面的规则指定要连接的数据源。
环境2.1.0.RELEASE，MySQL
1、所有的数据源信息配置：application.properties
#dataSource sharding tvbss.jdbc.username=${config.tvbss.jdbc.username} tvbss.jdbc.password=${config.tvbss.jdbc.password} tvbss.jdbc.driver=${config.tvbss.jdbc.driver} tvbss.jdbc.server00=${config.tvbss.jdbc.server00} tvbss.jdbc.server01=${config.tvbss.jdbc.server01} tvbss.jdbc.server02=${config.tvbss.jdbc.server02} tvbss.jdbc.server03=${config.tvbss.jdbc.server03} tvbss.jdbc.server04=${config.tvbss.jdbc.server04} tvbss.jdbc.server05=${config.tvbss.jdbc.server05} tvbss.jdbc.server06=${config.tvbss.jdbc.server06} tvbss.jdbc.server07=${config.tvbss.jdbc.server07} tvbss.jdbc.server08=${config.tvbss.jdbc.server08} tvbss.jdbc.server09=${config.tvbss.jdbc.server09}
2、读取配置信息到属性类中
import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.stereotype.Component; import javax.annotation.PostConstruct; import java.util.HashMap; import java.util.Map; /** * 数据源属性配置 * * @author yangyongjie * @date 2019/9/27 * @desc */ @Component @ConfigurationProperties(prefix = "tvbss.jdbc") public class ShardingProperties { private String username; private String password; private String driver; private String server00; private String server01; private String server02; private String server03; private String server04; private String server05; private String server06; private String server07; private String server08; private String server09; private Map<String, String> urlsMap = new HashMap<>(); @PostConstruct public void initUrlList() { urlsMap.put("server00", server00); urlsMap.put("server01", server01); urlsMap.put("server02", server02); urlsMap.put("server03", server03); urlsMap.put("server04", server04); urlsMap.put("server05", server05); urlsMap.put("server06", server06); urlsMap.put("server07", server07); urlsMap.put("server08", server08); urlsMap.put("server09", server09); } }
3、指定动态数据源的关键类
import com.xxx.common.bean.BaseShardedBean; import com.xxx.config.datasource.prop.ShardingProperties; import org.apache.commons.collections.MapUtils; import org.apache.commons.dbcp.BasicDataSource; import org.apache.commons.pool.impl.GenericObjectPool; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; import org.springframework.util.StringUtils; import java.util.HashMap; import java.util.Map; /** * 动态数据源切换 * 方式：配置多个数据源加到动态数据源对象中，根据实际的情况动态的切换到相应的数据源 */ public class DynamicDataSource extends AbstractRoutingDataSource { private static Logger LOGGER = LoggerFactory.getLogger(DynamicDataSource.class); @Autowired private ShardingProperties shardingProperties; /** * 连接池分配的最大连接数 */ private int maxActive = GenericObjectPool.DEFAULT_MAX_ACTIVE; /** * 空闲时保留的最大连接数 */ private int maxIdle = GenericObjectPool.DEFAULT_MAX_IDLE; /** * 空闲时保留的最少连接数 */ private int minIdle = GenericObjectPool.DEFAULT_MIN_IDLE; /** * 当连接池没有空闲时等待的最大毫秒数，小于0表示无限期等待 */ private long maxWait = GenericObjectPool.DEFAULT_MAX_WAIT; /** * 连接池的初始连接数 */ private int initialSize = 0; /** * 返回数据源dataSource的key值，实现数据源切换的关键方法 * 在determineTargetDataSource方法中根据key获取目标连接数据源 * * @return */ @Override protected Object determineCurrentLookupKey() { Object value = CurentDataSourceHolder.getCurentDataSource(); if (StringUtils.isEmpty(value)) { value = BaseShardedBean.defaultKey(); } return value; } /** * 启动时初始化所有的目标数据源 */ @Override public void afterPropertiesSet() { super.setTargetDataSources(buildDataSource()); super.afterPropertiesSet(); } /** * 根据配置文件的配置构建数据源 * * @return */ private Map<Object, Object> buildDataSource() { Map<Object, Object> dataSources = new HashMap<>(16); Map<String, String> urls = shardingProperties.getUrlsMap(); if (MapUtils.isEmpty(urls)) { LOGGER.error("获取配置的数据库urls为空"); return dataSources; } String driver = shardingProperties.getDriver(); String username = shardingProperties.getUsername(); String password = shardingProperties.getPassword(); urls.forEach((key, value) -> { BasicDataSource dataSource = new BasicDataSource(); dataSource.setDriverClassName(driver); dataSource.setUsername(username); dataSource.setPassword(password); dataSource.setUrl(value); dataSource.setMaxWait(maxWait); dataSource.setMaxIdle(maxIdle); dataSource.setMinIdle(minIdle); dataSource.setMaxActive(maxActive); dataSources.put(key, dataSource); }); return dataSources; } /** * 数据源存放对象，用于动态切换用 */ public static class CurentDataSourceHolder { private static ThreadLocal<Object> holder = new ThreadLocal<>(); /** * 设置当前线程的数据源 * * @param source */ public static void setCurentDataSource(Object source) { holder.set(source); } /** * 获取当前线程的数据源 * * @return */ public static Object getCurentDataSource() { return holder.get(); } /** * 清楚数据源 */ public static void remove() { holder.remove(); } } public int getMaxActive() { return maxActive; } public void setMaxActive(int maxActive) { this.maxActive = maxActive; } public int getMaxIdle() { return maxIdle; } public void setMaxIdle(int maxIdle) { this.maxIdle = maxIdle; } public int getMinIdle() { return minIdle; } public void setMinIdle(int minIdle) { this.minIdle = minIdle; } public long getMaxWait() { return maxWait; } public void setMaxWait(long maxWait) { this.maxWait = maxWait; } public int getInitialSize() { return initialSize; } public void setInitialSize(int initialSize) { this.initialSize = initialSize; } }
afterPropertiesSet()方法初始化了所有的目标数据源
determineCurrentLookupKey()方法用来获取当前线程变量中指定的当前数据源的key，若没有，则使用默认数据源
AbstractRoutingDataSource ：
public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean { @Nullable private Map<Object, Object> targetDataSources; // 多个目标数据源 @Nullable private Object defaultTargetDataSource; // 默认的目标数据源 private boolean lenientFallback = true; private DataSourceLookup dataSourceLookup = new JndiDataSourceLookup(); @Nullable private Map<Object, DataSource> resolvedDataSources; @Nullable private DataSource resolvedDefaultDataSource; /** * Specify the map of target DataSources, with the lookup key as key. * The mapped value can either be a corresponding {@link javax.sql.DataSource} * instance or a data source name String (to be resolved via a * {@link #setDataSourceLookup DataSourceLookup}). * <p>The key can be of arbitrary type; this class implements the * generic lookup process only. The concrete key representation will * be handled by {@link #resolveSpecifiedLookupKey(Object)} and * {@link #determineCurrentLookupKey()}. */ public void setTargetDataSources(Map<Object, Object> targetDataSources) { this.targetDataSources = targetDataSources; } /** * Specify the default target DataSource, if any. * <p>The mapped value can either be a corresponding {@link javax.sql.DataSource} * instance or a data source name String (to be resolved via a * {@link #setDataSourceLookup DataSourceLookup}). * <p>This DataSource will be used as target if none of the keyed * {@link #setTargetDataSources targetDataSources} match the * {@link #determineCurrentLookupKey()} current lookup key. */ public void setDefaultTargetDataSource(Object defaultTargetDataSource) { this.defaultTargetDataSource = defaultTargetDataSource; } /** * Specify whether to apply a lenient fallback to the default DataSource * if no specific DataSource could be found for the current lookup key. * <p>Default is "true", accepting lookup keys without a corresponding entry * in the target DataSource map - simply falling back to the default DataSource * in that case. * <p>Switch this flag to "false" if you would prefer the fallback to only apply * if the lookup key was {@code null}. Lookup keys without a DataSource * entry will then lead to an IllegalStateException. * @see #setTargetDataSources * @see #setDefaultTargetDataSource * @see #determineCurrentLookupKey() */ public void setLenientFallback(boolean lenientFallback) { this.lenientFallback = lenientFallback; } /** * Set the DataSourceLookup implementation to use for resolving data source * name Strings in the {@link #setTargetDataSources targetDataSources} map. * <p>Default is a {@link JndiDataSourceLookup}, allowing the JNDI names * of application server DataSources to be specified directly. */ public void setDataSourceLookup(@Nullable DataSourceLookup dataSourceLookup) { this.dataSourceLookup = (dataSourceLookup != null ? dataSourceLookup : new JndiDataSourceLookup()); } @Override public void afterPropertiesSet() { if (this.targetDataSources == null) { throw new IllegalArgumentException("Property 'targetDataSources' is required"); } this.resolvedDataSources = new HashMap<>(this.targetDataSources.size()); this.targetDataSources.forEach((key, value) -> { Object lookupKey = resolveSpecifiedLookupKey(key); DataSource dataSource = resolveSpecifiedDataSource(value); this.resolvedDataSources.put(lookupKey, dataSource); }); if (this.defaultTargetDataSource != null) { this.resolvedDefaultDataSource = resolveSpecifiedDataSource(this.defaultTargetDataSource); } } /** * Resolve the given lookup key object, as specified in the * {@link #setTargetDataSources targetDataSources} map, into * the actual lookup key to be used for matching with the * {@link #determineCurrentLookupKey() current lookup key}. * <p>The default implementation simply returns the given key as-is. * @param lookupKey the lookup key object as specified by the user * @return the lookup key as needed for matching */ protected Object resolveSpecifiedLookupKey(Object lookupKey) { return lookupKey; } /** * Resolve the specified data source object into a DataSource instance. * <p>The default implementation handles DataSource instances and data source * names (to be resolved via a {@link #setDataSourceLookup DataSourceLookup}). * @param dataSource the data source value object as specified in the * {@link #setTargetDataSources targetDataSources} map * @return the resolved DataSource (never {@code null}) * @throws IllegalArgumentException in case of an unsupported value type */ protected DataSource resolveSpecifiedDataSource(Object dataSource) throws IllegalArgumentException { if (dataSource instanceof DataSource) { return (DataSource) dataSource; } else if (dataSource instanceof String) { return this.dataSourceLookup.getDataSource((String) dataSource); } else { throw new IllegalArgumentException( "Illegal data source value - only [javax.sql.DataSource] and String supported: " + dataSource); } } @Override public Connection getConnection() throws SQLException { return determineTargetDataSource().getConnection(); } @Override public Connection getConnection(String username, String password) throws SQLException { return determineTargetDataSource().getConnection(username, password); } @Override @SuppressWarnings("unchecked") public <T> T unwrap(Class<T> iface) throws SQLException { if (iface.isInstance(this)) { return (T) this; } return determineTargetDataSource().unwrap(iface); } @Override public boolean isWrapperFor(Class<?> iface) throws SQLException { return (iface.isInstance(this) || determineTargetDataSource().isWrapperFor(iface)); } /** * Retrieve the current target DataSource. Determines the * {@link #determineCurrentLookupKey() current lookup key}, performs * a lookup in the {@link #setTargetDataSources targetDataSources} map, * falls back to the specified * {@link #setDefaultTargetDataSource default target DataSource} if necessary. * @see #determineCurrentLookupKey() */ protected DataSource determineTargetDataSource() { Assert.notNull(this.resolvedDataSources, "DataSource router not initialized"); Object lookupKey = determineCurrentLookupKey(); DataSource dataSource = this.resolvedDataSources.get(lookupKey); if (dataSource == null && (this.lenientFallback || lookupKey == null)) { dataSource = this.resolvedDefaultDataSource; } if (dataSource == null) { throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]"); } return dataSource; } /** * Determine the current lookup key. This will typically be * implemented to check a thread-bound transaction context. * <p>Allows for arbitrary keys. The returned key needs * to match the stored lookup key type, as resolved by the * {@link #resolveSpecifiedLookupKey} method. */ @Nullable protected abstract Object determineCurrentLookupKey(); }
4、Spring容器中数据源配置类
import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; import org.mybatis.spring.SqlSessionTemplate; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.core.io.support.PathMatchingResourcePatternResolver; import org.springframework.jdbc.datasource.DataSourceTransactionManager; /** * 数据源配置文件 * * @author yangyongjie * @date 2019/9/25 * @desc */ @Configuration public class ShardingDataSourceConfig { private static final Logger LOGGER = LoggerFactory.getLogger(ShardingDataSourceConfig.class); /** * 数据源 * * @return */ @Bean public DynamicDataSource shardingDataSource() { DynamicDataSource dynamicDataSource = new DynamicDataSource(); dynamicDataSource.setMaxActive(8); dynamicDataSource.setMaxIdle(2); dynamicDataSource.setMinIdle(1); dynamicDataSource.setMaxWait(60000); return dynamicDataSource; } @Bean public SqlSessionFactory shardingSqlSessionFactory() { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); // 扫描相关mapper文件 PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver(); SqlSessionFactory sqlSessionFactory = null; try { sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath:/mapper/**/*Mapper.xml")); sqlSessionFactoryBean.setDataSource(shardingDataSource()); // sqlSessionFactoryBean.setConfigLocation(); sqlSessionFactory = sqlSessionFactoryBean.getObject(); } catch (Exception e) { LOGGER.error("创建SqlSessionFactory:"+e.getMessage(),e); } return sqlSessionFactory; } @Bean public SqlSessionTemplate shardingSqlSessionTemplate() { return new SqlSessionTemplate(shardingSqlSessionFactory()); } /** * sharding数据源事务管理器 * * @return */ @Bean public DataSourceTransactionManager shardingTransactionManager() { return new DataSourceTransactionManager(shardingDataSource()); } }
5、指定了默认数据源，提供 generateShardingKey 方法，用来根据当前bean分配库名，并保存在线程本地变量中，以及生成表名，为bean的tableIndex属性赋值
public abstract class BaseShardedBean { static String DEFAULT_SHARDING_SCHAME = "server"; static String DEFAULT_SHARDING_INDEX = "00"; public abstract Object dbKey(); public abstract String tableKey(); /** * 子类必须指定分片的key */ public abstract Object genShardedKey(); /** * 子类必须根据bean的映射选择表名 */ public abstract String genTableName(); /** * 子类必须制定业务数据库类型 */ public abstract String genDbSchema(); /** * 默认的库名,server00 * * @return */ public static Object defaultKey() { return DEFAULT_SHARDING_SCHAME + DEFAULT_SHARDING_INDEX; } /** * 生成库名和表名 */ public void generateShardingKey() { Object key = dbKey(); key = StringUtils.isEmpty(key) ? defaultKey() : key; DynamicDataSource.CurentDataSourceHolder.setCurentDataSource(key); tableKey(); } }
6、具体的库表映射实体，继承了BaseShardedBean ，用户实体需继承它，以及顶一个公共的分库分表规则，即所有的实体类对应的分库分表规则都相同，不同的是库名前缀和表名前缀
** * 支持分片的Bean，只支持单表操作 required ${genShardedKey} 指定类型的分片key ${genTableName} 指定类型映射的表名 **/ public abstract class ShardedMapperBean extends BaseShardedBean { @JsonIgnore protected String orderByClause; @JsonIgnore protected boolean distinct; @JsonIgnore protected String tableName; @JsonIgnore private String tableIndex; @JsonIgnore protected Object shardKey; public String getOrderByClause() { return orderByClause; } public void setOrderByClause(String orderByClause) { this.orderByClause = orderByClause; } public boolean isDistinct() { return distinct; } public void setDistinct(boolean distinct) { this.distinct = distinct; } public String getTableName() { return tableName; } public void setTableName(String tableName) { this.tableName = tableName; } public String getTableIndex() { return tableIndex; } public void setTableIndex(String tableIndex) { this.tableIndex = tableIndex; } @Override public Object dbKey() { String key = genShardedKey() == null ? null : String.valueOf(genShardedKey()); if (StringUtils.isEmpty(key)) { return null; } return genDbSchema() + "0" + Integer.parseInt(getLastSub4to2(key)) / 10; // 库名为key倒数三四位除10取整，即倒数第四位的值，一共分10个库 } @Override public String tableKey() { this.setTableName(genTableName()); String key = genShardedKey() == null ? null : String.valueOf(genShardedKey()); if (StringUtils.isEmpty(key)) { setTableIndex(genTableName()); return null; } else { String tableIndex = genTableName() + "_" + getLastSub2(key); // 表名为倒数一二位的值，一共分100张表 setTableIndex(tableIndex); return tableIndex; } } private static String getLastSub4to2(String key) { if (StringUtils.isEmpty(key) || key.length() < 4) { return ""; } int keyLen = key.length(); return key.substring(keyLen - 4, keyLen - 2); } private static String getLastSub2(String key) { if (StringUtils.isEmpty(key) || key.length() < 2) { return ""; } int keyLen = key.length(); return key.substring(keyLen - 2); } }
如具体的实体继承ShardedMapperBean 之后重写的方法，自定义库名前缀和表名前缀，以及用什么作为分库分表的key
@Override public Object genShardedKey() { return id; } @Override public String genTableName() { return "order"; } @Override public String genDbSchema() { return "databaseNamePrefix"; }
7、最后使用AOP拦截方法的执行，给DynamicDataSource中的静态内部类CurentDataSourceHolder中的线程本地变量指定实际连接的数据源即可。
import com.xxx.common.bean.BaseShardedBean; import com.xxx.config.datasource.DynamicDataSource; import org.apache.commons.lang3.StringUtils; import org.aspectj.lang.JoinPoint; import org.aspectj.lang.annotation.After; import org.aspectj.lang.annotation.Aspect; import org.aspectj.lang.annotation.Before; import org.aspectj.lang.annotation.Pointcut; import org.aspectj.lang.reflect.MethodSignature; import org.springframework.stereotype.Component; import org.springframework.transaction.annotation.Transactional; import java.lang.reflect.Method; /** * 事务注解的拦截器，为事务管理器为shardingTransactionManager的方法指定数据源 * * @author yangyongjie * @date 2019/9/30 * @desc */ @Aspect @Component public class TransactionAspect { private static final String SHARD_TRANSACTION_MANAGER = "shardingTransactionManager"; @Pointcut("@annotation(org.springframework.transaction.annotation.Transactional)") private void transactionbPointCut() { // donothing } /** * 方法执行前的拦截,指定数据源 * 因此如果需要动态指定数据源，则方法参数bean需要继承ShardedMapperBean并重写genShardedKey()、genTableName()和genDbSchema() * * @param joinPoint */ @Before(value = "transactionbPointCut()") public void beforeTransaction(JoinPoint joinPoint) { // 获取方法上的注解 MethodSignature methodSignature = (MethodSignature) joinPoint.getSignature(); Method pointCutMethod = methodSignature.getMethod(); Transactional ann = pointCutMethod.getAnnotation(Transactional.class); if (!StringUtils.equals(ann.value(), SHARD_TRANSACTION_MANAGER)) { return; } // 获取方法参数 Object[] methodArgs = joinPoint.getArgs(); for (Object args : methodArgs) { if (args instanceof BaseShardedBean) { ((BaseShardedBean) args).generateShardingKey(); break; } } } /** * 方法执行后删除存放数据源key的线程本地变量 */ @After(value = "transactionbPointCut()") public void afterTransaction() { DynamicDataSource.CurentDataSourceHolder.remove(); } }
总体流程总结：
1、配置文件中定义分库的多个数据源URL
2、自定义动态数据源DynamicDataSource继承自AbstractRoutingDataSource
3、重写afterPropertiesSet方法，为ARD的私有属性 Map<key,Object>赋值，key为定义的多个数据源的key，如server00-09，value为BasicDataSource对象（设置好属性信息）
4、重写determineCurrentLookupKey方法，从线程本地变量中（使用ThreadLocal）取出当前线程要连接的数据源的key
5、将自定义的DynamicDataSource作为普通数据源配置到Spring中
6、在请求过程中，如利用AOP或拦截器，根据请求参数如orderId根据规则生成数据源对应的key，并将key保存到当前线程的本地变量中
7、当前线程在连接数据库时，ARD会根据key取出实际要连接的数据源并连接 getConnection()方法
END.
