目录
- 1. 实现效果
- 2. 注解
- 3. 分库策略
- 3.1 MultipleConstant
- 3.2 IRoutingInterface
- 3.3 AbstractRouting
- 3.4 RoutingDsAndTbStrategy
- 3.5 RoutingDsStrategy
- 3.6 RoutingTbStrategy
- 4. 配置类
- 4.1 MultipleStategyProperties
- 4.2 MultipleStategyProperties
- 4.3 MultipleDataSourceStrategyConfig
- 4.4 MultipleDataSourceConfig
- 4.5 MultiDataSource
- 5. 全局上下文
- 6. 切面
1. 实现效果
1.1 controller
最终实现效果,在接口上标记上 @Router 注解用来标记当前接口需要根据参数中的某个字段进行数据的切换
@RestController @RequestMapping(\"/user\") public class UserController { @Resource private UserMapper userMapper; @GetMapping(\"/save\") @Router(routingFiled = \"id\") @Transactional public void saveUser(@RequestParam(\"id\") String id){ User user = new User(); user.setAge(\"123\"); user.setId(Long.valueOf(id)); user.setName(\"zs\"); //设置表的后缀名称,在mybatis执行sql时可以获取 user.setTableSuffix(MultiDataSourceHolder.getTableIndex()); userMapper.insert(user); } @GetMapping(\"/get\") @Router(routingFiled = \"id\") public User getUser(@RequestParam(\"id\") String id){ return userMapper.selectByPrimaryKey(Long.valueOf(id),MultiDataSourceHolder.getTableIndex()); } }
1.2 mybatis.xml
<?xml version=\"1.0\" encoding=\"UTF-8\"?> <!DOCTYPE mapper PUBLIC \"-//mybatis.org//DTD Mapper 3.0//EN\" \"http://mybatis.org/dtd/mybatis-3-mapper.dtd\"> <mapper namespace=\"com.zhj.multiple.mapper.UserMapper\"> <resultMap id=\"BaseResultMap\" type=\"com.zhj.multiple.entity.User\"> <!--@mbg.generated--> <id column=\"id\" jdbcType=\"BIGINT\" property=\"id\" /> <result column=\"name\" jdbcType=\"VARCHAR\" property=\"name\" /> <result column=\"age\" jdbcType=\"VARCHAR\" property=\"age\" /> </resultMap> <sql id=\"Base_Column_List\"> <!--@mbg.generated--> id, `name`, age </sql> <select id=\"selectByPrimaryKey\" parameterType=\"java.lang.Long\" resultMap=\"BaseResultMap\"> <!--@mbg.generated--> select <include refid=\"Base_Column_List\" /> <!--通过${}获取出表名,为什么不是用#呢?因为表名是在内部进行计算,不用担心sql注入的问题,而且$进行获取是直接将表名进行拼接上,不会使用预处理--> from user${tableName} where id = #{id,jdbcType=BIGINT} </select> <insert id=\"insert\" keyColumn=\"id\" keyProperty=\"id\" parameterType=\"com.zhj.multiple.entity.User\" useGeneratedKeys=\"true\"> <!--@mbg.generated--> insert into user${tableSuffix} (id ,`name`, age) values (#{id},#{name,jdbcType=VARCHAR}, #{age,jdbcType=VARCHAR}) </insert> </mapper>
1.3 application.yml
#showSql logging: level: com: zhj: mapper : debug server: port: 8081 # 配置路由分库分表的策略 datasource: stragegy: dataSourceNum: 2 #库的个数 tableNum: 2 #表的个数 routingFiled: \'userId\' #根据哪个字段来进行分库分表 tableSuffixStyle: \'%04d\' #表的索引值 4位补齐 例如:_0003 tableSuffixConnect: \'_\' #表的连接风格 order_ routingStategy: \'ROUTING_DS_TABLE_STATEGY\' #表的策略,启动时会根据表的策略进行验证 #配置数据源 multiple: data0: user: root password: root url: jdbc:mysql://192.168.60.46:3306/multiple-0?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC driver: com.mysql.jdbc.Driver data1: user: root password: root url: jdbc:mysql://192.168.60.46:3306/multiple-1?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC driver: com.mysql.jdbc.Driver
1.4 启动类
@SpringBootApplication @EnableAspectJAutoProxy @MapperScan(\"com.zhj.multiple.mapper\") @EnableTransactionManagement public class MultipleApplication { public static void main(String[] args) { SpringApplication.run(MultipleApplication.class, args); } }
2. 注解
2.1 @Router
/** * 路由注解 */ @Retention(RetentionPolicy.RUNTIME) @Target(ElementType.METHOD) @Documented public @interface Router { /** * 路由字段 * * @return 默认路由字段是参数中的哪一个 */ String routingFiled() default MultipleConstant.DEFAULT_ROUTING_FIELD; }
3. 分库策略
3.1 MultipleConstant
目前有三种分库的策略:
- 多库多表
- 多库单表
- 单库多表
@Data public class MultipleConstant { /** * 多库多表策略 */ public static final String ROUTING_DS_TABLE_STATEGY = \"ROUTING_DS_TABLE_STATEGY\"; /** * 多库单表策略 */ public static final String ROUTGING_DS_STATEGY = \"ROUTGING_DS_STATEGY\"; /** * 单库多表策略 */ public static final String ROUTGIN_TABLE_STATEGY = \"ROUTGIN_TABLE_STATEGY\"; /** * 默认的路由字段 */ public static final String DEFAULT_ROUTING_FIELD = \"accountId\"; }
3.2 IRoutingInterface
路由的顶级接口,用于定义一些通用的方法
public interface IRoutingInterface { /** * 根据字段key计算出数据库 * @param routingFiled * @return */ String calDataSourceKey(String routingFiled); /** * 获取路由字段的hashCode * @param routingFiled * @return */ Integer getRoutingFileHashCode(String routingFiled); /** * 计算出表名 * @param routingFiled * @return */ String calTableKey(String routingFiled); /** * 计算出表的前缀 * @param tableIndex * @return */ String getFormatTableSuffix(Integer tableIndex); }
3.3 AbstractRouting
@EnableConfigurationProperties({MultipleStategyProperties.class}) @Data @Slf4j public abstract class AbstractRouting implements IRoutingInterface, InitializingBean { @Resource private MultipleStategyProperties multipleStategyProperties; @Override public Integer getRoutingFileHashCode(String routingFiled){ return Math.abs(routingFiled.hashCode()); } /** * 获取表的后缀 * @param tableIndex 表索引 */ @Override public String getFormatTableSuffix(Integer tableIndex){ //获取连接符 String tableSuffixConnect = multipleStategyProperties.getTableSuffixConnect(); //根据配置风格格式化表后缀名称 String format = String.format(multipleStategyProperties.getTableSuffixStyle(), tableIndex); return tableSuffixConnect + format; } /** * 工程启动时,检验配置的数据源是否跟策略相似,实现了 InitializingBean 初始化后会执行当前方法 */ @Override public void afterPropertiesSet(){ switch (multipleStategyProperties.getRoutingStategy()) { case MultipleConstant.ROUTING_DS_TABLE_STATEGY: checkRoutingDsTableStategyConfig(); break; case MultipleConstant.ROUTGING_DS_STATEGY: checkRoutingDsStategyConfig(); break; default: checkRoutingTableStategyConfig(); break; } } /** * 检查多库 多表配置 */ private void checkRoutingDsTableStategyConfig() { if(multipleStategyProperties.getTableNum()<=1 ||multipleStategyProperties.getDataSourceNum()<=1){ log.error(\"你的配置项routingStategy:{}是多库多表配置,数据库个数>1,\" + \"每一个库中表的个数必须>1,您的配置:数据库个数:{},表的个数:{}\",multipleStategyProperties.getRoutingStategy(), multipleStategyProperties.getDataSourceNum(),multipleStategyProperties.getTableNum()); throw new RuntimeException(); } } /** * 检查多库一表的路由配置项 */ private void checkRoutingDsStategyConfig() { if(multipleStategyProperties.getTableNum()!=1 ||multipleStategyProperties.getDataSourceNum()<=1){ log.error(\"你的配置项routingStategy:{}是多库一表配置,数据库个数>1,\" + \"每一个库中表的个数必须=1,您的配置:数据库个数:{},表的个数:{}\",multipleStategyProperties.getRoutingStategy(), multipleStategyProperties.getDataSourceNum(),multipleStategyProperties.getTableNum()); throw new RuntimeException(); } } /** * 检查一库多表的路由配置项 */ private void checkRoutingTableStategyConfig() { if(multipleStategyProperties.getTableNum()<=1 ||multipleStategyProperties.getDataSourceNum()!=1){ log.error(\"你的配置项routingStategy:{}是一库多表配置,数据库个数=1,\" + \"每一个库中表的个数必须>1,您的配置:数据库个数:{},表的个数:{}\",multipleStategyProperties.getRoutingStategy(), multipleStategyProperties.getDataSourceNum(),multipleStategyProperties.getTableNum()); throw new RuntimeException(); } } }
3.4 RoutingDsAndTbStrategy
目前实现了一个多库多表的策略进行配置,其余两个分库算法可以自行实现
@Slf4j public class RoutingDsAndTbStrategy extends AbstractRouting { /** * 确定数据源的key * @param routingFiled * @return */ @Override public String calDataSourceKey(String routingFiled) { //计算hash值 Integer routingFileHashCode = getRoutingFileHashCode(routingFiled); //定位数据源 int dsIndex = routingFileHashCode % getMultipleStategyProperties().getDataSourceNum(); String dataSourceKey = getMultipleStategyProperties().getDataSourceKeysMapping().get(dsIndex); //将数据源key放入持有器当中 MultiDataSourceHolder.setDataSourceHolder(dataSourceKey); log.info(\"根据路由字段:{},值:{},计算出数据库索引值:{},数据源key的值:{}\",getMultipleStategyProperties().getRoutingFiled(),routingFiled,dsIndex,dataSourceKey); return dataSourceKey; } /** * 计算表的key * @param routingFiled * @return */ @Override public String calTableKey(String routingFiled) { //获取到当前key的hash Integer routingFileHashCode = getRoutingFileHashCode(routingFiled); //通过hash值取模,获取到对应的索引值 int tbIndex = routingFileHashCode % getMultipleStategyProperties().getTableNum(); //获取表后缀 String formatTableSuffix = getFormatTableSuffix(tbIndex); //将表名设置到上下文中,方便后续同线程获取到对应的表名 MultiDataSourceHolder.setTableIndexHolder(formatTableSuffix); return formatTableSuffix; } }
3.5 RoutingDsStrategy
多库单表:
public class RoutingDsStrategy extends AbstractRouting { @Override public String calDataSourceKey(String routingFiled) { return null; } @Override public String calTableKey(String routingFiled) { return null; } }
3.6 RoutingTbStrategy
单库多表策略:
public class RoutingTbStrategy extends AbstractRouting { @Override public String calDataSourceKey(String routingFiled) { return null; } @Override public String calTableKey(String routingFiled) { return null; } }
4. 配置类
以下两个配置:
MultipleStategyProperties:用于配置数据库策略,有多少库,多少表,以及表名
4.1 MultipleStategyProperties
@ConfigurationProperties(prefix = \"datasource.stragegy\") @Data public class MultipleStategyProperties { /** * 默认是一个数据库 默认一个 */ private Integer dataSourceNum = 1; /** * 每一个库对应表的个数 默认是一个 */ private Integer tableNum = 1; /** * 路由字段 必须在配置文件中配置(不配置会抛出异常) */ private String routingFiled; /** * 数据库的映射关系 */ private Map<Integer,String> dataSourceKeysMapping; /** * 表的后缀连接风格 比如order_ */ private String tableSuffixConnect=\"_\"; /** * 表的索引值 格式化为四位 不足左补零 1->0001 然后在根据tableSuffixConnect属性拼接成 * 成一个完整的表名 比如 order表 所以为1 那么数据库表明为 order_0001 */ private String tableSuffixStyle= \"%04d\"; /** * 默认的多库多表策略 */ private String routingStategy = MultipleConstant.ROUTING_DS_TABLE_STATEGY; }
4.2 MultipleStategyProperties
@Configuration public class MultipleDataSourceStrategyConfig { /** * 当配置文件里面包含某个配置,并且值是多少时生效 * * @return routing interface * @since 1.0.0 */ @Bean @ConditionalOnProperty(prefix = \"datasource.stragegy\",name = \"routingStategy\",havingValue = \"ROUTING_DS_TABLE_STATEGY\") public IRoutingInterface routingDsAndTbStrategy(){ return new RoutingDsAndTbStrategy(); } /** * Routing ds strategy * * @return the routing interface * @since 1.0.0 */ @Bean @ConditionalOnProperty(prefix = \"datasource.stragegy\",name = \"routingStategy\",havingValue = \"ROUTGING_DS_STATEGY\") public IRoutingInterface routingDsStrategy(){ return new RoutingDsStrategy(); } /** * Routing tb strategy * * @return the routing interface * @since 1.0.0 */ @Bean @ConditionalOnProperty(prefix = \"datasource.stragegy\",name = \"routingStategy\",havingValue = \"ROUTGIN_TABLE_STATEGY\") public IRoutingInterface routingTbStrategy(){ return new RoutingTbStrategy(); } }
4.3 MultipleDataSourceStrategyConfig
根据对应的配置创建不同的分库策略
@Configuration public class MultipleDataSourceStrategyConfig { /** * 当配置文件里面包含某个配置,并且值是多少时生效 * * @return routing interface * @since 1.0.0 */ @Bean @ConditionalOnProperty(prefix = \"datasource.stragegy\",name = \"routingStategy\",havingValue = \"ROUTING_DS_TABLE_STATEGY\") public IRoutingInterface routingDsAndTbStrategy(){ return new RoutingDsAndTbStrategy(); } /** * Routing ds strategy * * @return the routing interface * @since 1.0.0 */ @Bean @ConditionalOnProperty(prefix = \"datasource.stragegy\",name = \"routingStategy\",havingValue = \"ROUTGING_DS_STATEGY\") public IRoutingInterface routingDsStrategy(){ return new RoutingDsStrategy(); } /** * Routing tb strategy * * @return the routing interface * @since 1.0.0 */ @Bean @ConditionalOnProperty(prefix = \"datasource.stragegy\",name = \"routingStategy\",havingValue = \"ROUTGIN_TABLE_STATEGY\") public IRoutingInterface routingTbStrategy(){ return new RoutingTbStrategy(); } }
4.4 MultipleDataSourceConfig
多数据源自动装配类,其中创建了多个数据源,通过 spring提供的 AbstractRoutingDataSource 类进行数据源的切换
@Configuration //开启数据源以及数据分库策略配置 @EnableConfigurationProperties({MultipleDataSourceProperties.class, MultipleStategyProperties.class}) public class MultipleDataSourceConfig { @Resource private MultipleDataSourceProperties multipleDataSourceProperties; @Resource private MultipleStategyProperties multipleStategyProperties; /** * 配置数据源 * @return */ @Bean(\"data0\") public DataSource dataSource0(){ DruidDataSource druidDataSource = new DruidDataSource(); druidDataSource.setUsername(multipleDataSourceProperties.getData0().getUser()); druidDataSource.setPassword(multipleDataSourceProperties.getData0().getPassword()); druidDataSource.setUrl(multipleDataSourceProperties.getData0().getUrl()); druidDataSource.setDriverClassName(multipleDataSourceProperties.getData0().getDriver()); return druidDataSource; } @Bean(\"data1\") public DataSource dataSource1(){ DruidDataSource druidDataSource = new DruidDataSource(); druidDataSource.setUsername(multipleDataSourceProperties.getData1().getUser()); druidDataSource.setPassword(multipleDataSourceProperties.getData1().getPassword()); druidDataSource.setUrl(multipleDataSourceProperties.getData1().getUrl()); druidDataSource.setDriverClassName(multipleDataSourceProperties.getData1().getDriver()); return druidDataSource; } /** * 设置多数据源 * @param data0 * @param data1 * @return */ @Bean public MultiDataSource multiDataSource(DataSource data0,DataSource data1){ //将多个数据与数据源关联起来 MultiDataSource multiDataSource = new MultiDataSource(); HashMap<Object, Object> multiMap = new HashMap<>(); multiMap.put(\"data0\",data0); multiMap.put(\"data1\",data1); //设置目标数据源 multiDataSource.setTargetDataSources(multiMap); //设置默认的数据源 multiDataSource.setDefaultTargetDataSource(data0); //设置数据源名称的映射 Map<Integer, String> multiMappings = new HashMap<>(); multiMappings.put(0,\"data0\"); multiMappings.put(1,\"data1\"); multipleStategyProperties.setDataSourceKeysMapping(multiMappings); return multiDataSource; } /** * 将多数据源设置进mybatis的工厂类中 * @param multiDataSource * @return */ @Bean public SqlSessionFactory sqlSessionFactory(@Qualifier(\"multiDataSource\") MultiDataSource multiDataSource) throws Exception { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(multiDataSource); sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(\"classpath:/mybatis/mappers/*.xml\")); sqlSessionFactoryBean.setTypeAliasesPackage(\"com.zhj.multiple.entity\"); return sqlSessionFactoryBean.getObject(); } @Bean public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory){ return new SqlSessionTemplate(sqlSessionFactory); } /** * 将多数据源设置到事务管理器中 * * @param multiDataSource * @return */ @Bean public DataSourceTransactionManager dataSourceTransactionManager(@Qualifier(\"multiDataSource\") MultiDataSource multiDataSource){ return new DataSourceTransactionManager(multiDataSource); } }
4.5 MultiDataSource
覆写 AbstractRoutingDataSource.determineCurrentLookupKey() 的方法,在mybatis中通过 Datasource.getConnection() 会调用 determineCurrentLookupKey() 获取到对应的数据源,然后通过数据源获取到连接,其中内部维护了一个 Map 来保存数据源的映射关系
public class MultiDataSource extends AbstractRoutingDataSource { /** * 获取到指定的数据源 * @return */ @Override protected Object determineCurrentLookupKey() { return MultiDataSourceHolder.getDataSourceKey(); } }
5. 全局上下文
用于保存数据库、表名,方便后续使用
5.1 MultiDataSourceHolder
@Data public class MultiDataSourceHolder { /** * 存储数据源 */ private static final ThreadLocal<String> dataSourceHolder = new ThreadLocal<>(); /** * 存储表的索引 */ private static final ThreadLocal<String> tableIndexHolder = new ThreadLocal<>(); /** * Get data source key * * @return the string * @since 1.0.0 */ public static String getDataSourceKey(){ return dataSourceHolder.get(); } /** * Get table index * * @return the string * @since 1.0.0 */ public static String getTableIndex(){ return tableIndexHolder.get(); } /** * Clear data source key * * @since 1.0.0 */ public static void clearDataSourceKey(){ dataSourceHolder.remove(); } /** * Clear table index * * @since 1.0.0 */ public static void clearTableIndex(){ tableIndexHolder.remove(); } /** * Set data source holder * * @param key key * @since 1.0.0 */ public static void setDataSourceHolder(String key){ dataSourceHolder.set(key); } /** * Set table index holder * * @param key key * @since 1.0.0 */ public static void setTableIndexHolder(String key){ tableIndexHolder.set(key); } }
6. 切面
6.1 RoutingAspect
通过路由切面进行 @Router 注解的处理,提前将数据库的key以及表名的后缀获取出来进行存储
@Aspect @Component @Slf4j public class RoutingAspect { @Resource private IRoutingInterface iRoutingInterface; @Pointcut(\"@annotation(com.zhj.multiple.annotations.Router)\") public void pointCut(){}; @Before(\"pointCut()\") public void before(JoinPoint joinPoint) throws IllegalAccessException, NoSuchMethodException, InvocationTargetException { long beginTime = System.currentTimeMillis(); //获取方法调用名称 Method method = getInvokeMethod(joinPoint); //获取方法指定的注解 Router router = method.getAnnotation(Router.class); //获取指定的路由key String routingFiled = router.routingFiled(); if(Objects.nonNull(router)){ boolean havingRoutingField = false; //获取到http请求 HttpServletRequest requestAttributes = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getRequest(); //优先获取@ReqeustParam注解中的路由字段 String routingFieldValue = requestAttributes.getParameter(routingFiled); if(!StringUtils.isEmpty(routingFieldValue)){ //计算数据库key String dbKey = iRoutingInterface.calDataSourceKey(routingFieldValue); //计算表索引 String tableIndex = iRoutingInterface.calTableKey(routingFieldValue); log.info(\"选择的dbkey是:{},tableKey是:{}\",dbKey,tableIndex); }else { //获取方法入参 Object[] args = joinPoint.getArgs(); if(args != null && args.length > 0) { for(int index = 0; index < args.length; index++) { //找到参数当中路由字段的值 routingFieldValue = BeanUtils.getProperty(args[index],routingFiled); if(!StringUtils.isEmpty(routingFieldValue)) { //计算数据库key String dbKey = iRoutingInterface.calDataSourceKey(routingFieldValue); //计算表索引 String tableIndex = iRoutingInterface.calTableKey(routingFieldValue); log.info(\"选择的dbkey是:{},tableKey是:{}\",dbKey,tableIndex); havingRoutingField = true; break; } } //判断入参中没有路由字段 if(!havingRoutingField) { log.warn(\"入参{}中没有包含路由字段:{}\",args,routingFiled); throw new RuntimeException(); } } } } } private Method getInvokeMethod(JoinPoint joinPoint) { Signature signature = joinPoint.getSignature(); MethodSignature methodSignature = (MethodSignature)signature; Method targetMethod = methodSignature.getMethod(); return targetMethod; } /** * 清除线程缓存 * @param joinPoint */ @After(\"pointCut()\") public void methodAfter(JoinPoint joinPoint){ MultiDataSourceHolder.clearDataSourceKey(); MultiDataSourceHolder.clearTableIndex(); } }
做猪小侠源码的代理,提供一站式服务
如果你不懂得搭建网站或者服务器,小程序,源码之类的怎么办? 第一通过本站学习各种互联网的技术 第二就是联系客服,我帮帮你搭建(当然要收取部分的费用) 第三成为我们的代理,我们提供整套的服务。