springboot整合sharding-jdbc实现按年分库按月分表（实现、简析篇）
1.前言：
此方案为适用对时间依赖度较高的数据进行水平切分，如果你正好符合，那么你可以看看此篇，不符合也可以看看，毕竟sharding-jdbc对分库分表的方案实现度很高，可根据自身实际情况进行数据切分，如果你对以下概念已经有共了解，可直接跳到代码环节
2.分库分表概念
在做实现之前，我们需要了解到何为分库分表，为何要这样做，这样做有何利弊？
由于关系型数据库的先天特性，在单表数据量达到一定体量的时候，就会出现性能瓶颈，至于具体是多少，此篇不做研究，说法众多，后期有时间的话可以进行测试，为了解决性能瓶颈问题，行业内的先驱们则提出了对数据进行分而治之的思想，对数据以贴合实际情况进行切分，达到对数据库性能的优化，主要的切分方式有行业先驱总结了一下几种，也可参考：
1.垂直拆分
1）垂直分库：垂直分库其实是一种简单逻辑分割，把单一数据库按照业务进行划分，达到一个专库专表的效果，例如可将系统拆分为配置库、历史数据库、用户库…
2）垂直分表：比较适用于那种字段比较多的表，且有些字段并非所有的需求都要使用到，操作数据库中的某张表，将表中一部分字段信息存储至一张新表中，原表中不在存储被拆分出去的字段信息，例如机组信息表可拆分为机组基础信息表、机组详细信息表
垂直拆分有以下优点：
跟随业务进行分割，和最近流行的微服务概念相似，方便解耦之后的管理及扩展。
高并发的场景下，垂直拆分使用多台服务器的CPU、I/O、内存能提升性能，同时对单机数据库连接数、一些资源限制也得到了提升。
能实现冷热数据的分离。
垂直拆分的缺点：
部分业务表无法join，应用层需要很大的改造，只能通过聚合的方式来实现。增加了开发的难度。
当单库中的表数据量增大的时候依然没有得到有效的解决。
分布式事务也是一个难题。
2.水平拆分
当某张表数据量达到一定的程度的时候，例如MySQL单表出现2000万以上数据就会出现性能上的分水岭。此时发现没有办法根据业务规则再进行拆分了，就会导致单库上的读写性能出现瓶颈。此时就只能进行水平拆分了。
1）库内分表：假设当我们的c_vib_point表达到了5000万行记录的时候，非常影响数据库的读写效率，怎么办呢？我们可以考虑按照订单编号的id进行rang分区,就是把订单编号在1-1000万的放在order1表中，将编号在1000万-2000万的放在order2中，以此类推，每个表中存放1000万数据，或者根据插入时间进行分表，一年一个表，也可控制单表数量，但是表的数据仍然存放在一个库内，使用的是该主机的CPU、IO、内存。单库的连接数也有限制。并不能完全的降低系统的压力
2）分库分表：分库分表在库内分表的基础上，将分的表挪动到不同的主机和数据库上。可以充分的使用其他主机的CPU、内存和IO资源。并且分库之后，单库的连接数限制也不在成为瓶颈。但是“成也萧何败也萧何”，如果你执行一个扫描不带分片键，则需要在每个库上查一遍。刚刚我们按照id分成了5个库，但是我们查询是name='AAA’的条件并且不带id字段时，它并不知道在哪个分片上查，则会创建5个连接，然后每个库都检索一遍。这种广播查询则会造成连接数增多。因为它需要在每个库上都创立连接。如果是高并发的系统，执行这种广播查询，系统的thread很快就会告警，所以对于开发者的数据库操作会更加严格。
水平拆分的优点：
水平扩展能无线扩展。不存在某个库某个表过大的情况。
能够较好的应对高并发，同时可以将热点数据打散。
应用侧的改动较小，不需要根据业务来拆分。
水平拆分的缺点：
路由是个问题，需要增加一层路由的计算，而且像前面说的一样，不带分片键查询会产生广播SQL。
跨库join的性能比较差。
需要处理分布式事务的一致性问题。
个人觉得，分库分表有利有弊，在使用分库分表之前，应优先考虑缓存技术、读写分离、优化SQL、使用索引等方式，分库分表作为最后的方案，且最好是在项目初期就应该做好前瞻性考虑，如果你确定项目在未来的一定时间内，数据量确确实实会达到一个非常影响性能的体量，那么就可以考虑做分库分表，当然，你可以暂时先不做分库分表，等数据量上来了再做
3.sharding-jdbc概念
一个技术的出现必然是被需求所驱动，sharding-jdbc就是分库分表的产物，当然它并非唯一实现，只是此篇以sharding-jdbc作为技术支持来实现分库分表，下面可了解一下其相关概念，最好做到知其然知其所以然。
1.简介
sharding-jdbc定位为轻量级Java框架，在Java的JDBC层提供的额外服务。 它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。
适用于任何基于JDBC的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。
支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer，PostgreSQL以及任何遵循SQL92标准的数据库。
2.SQL
1）逻辑表
水平拆分的数据库（表）的相同逻辑和数据结构表的总称。例如根据时间将order一年表拆分为12个表，分别是order_01到order_12，则他们的逻辑表名为order
2）真实表
在分片的数据库中真实存在的物理表。即上个示例中order_01到order_12
3）数据节点
数据分片的最小单元。由数据源名称和数据表组成，例：dataSource.order_01
4）绑定表
指分片规则一致的主表和子表。例如：t_order表和t_order_item表，均按照order_id分片，则此两张表互为绑定表关系。绑定表之间的多表关联查询不会出现笛卡尔积关联，关联查询效率将大大提升。举例说明，如果SQL为
SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);在不配置绑定表关系时，假设分片键order_id将数值10路由至第0片，将数值11路由至第1片，那么路由后的SQL应该为4条，它们呈现为笛卡尔积：
SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);
SELECT i.* FROM t_order_0 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);
SELECT i.* FROM t_order_1 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);
SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);在配置绑定表关系后，路由的SQL应该为2条：
SELECT i.* FROM t_order_0 o JOIN t_order_item_0 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);
SELECT i.* FROM t_order_1 o JOIN t_order_item_1 i ON o.order_id=i.order_id WHERE o.order_id in (10, 11);其中t_order在FROM的最左侧，ShardingSphere将会以它作为整个绑定表的主表。 所有路由计算将会只使用主表的策略，那么t_order_item表的分片计算将会使用t_order的条件。故绑定表之间的分区键要完全相同。
5）广播表
指所有的分片数据源中都存在的表，表结构和表中的数据在每个数据库中均完全一致。适用于数据量不大且需要与海量数据的表进行关联查询的场景，例如：字典表。
2.分片
1）分片键
用于分片的数据库字段，是将数据库(表)水平拆分的关键字段。例：将订单表中的主键的尾数取模分片，则订单表主键为分片字段。 SQL中如果无分片字段，将执行全路由，性能较差。 除了对单分片字段的支持，ShardingSphere也支持根据多个字段进行分片。
2）分片算法
通过分片算法将数据分片，支持通过=、>=、<=、>、<、BETWEEN和IN分片。分片算法需要应用方开发者自行实现，可实现的灵活度非常高。
目前提供4种分片算法。由于分片算法和业务实现紧密相关，因此并未提供内置分片算法，而是通过分片策略将各种场景提炼出来，提供更高层级的抽象，并提供接口让应用开发者自行实现分片算法。
精确分片算法
对应PreciseShardingAlgorithm，用于处理使用单一键作为分片键的=与IN进行分片的场景。需要配合StandardShardingStrategy使用。
范围分片算法
对应RangeShardingAlgorithm，用于处理使用单一键作为分片键的BETWEEN AND、>、<、>=、<=进行分片的场景。需要配合StandardShardingStrategy使用。
复合分片算法
对应ComplexKeysShardingAlgorithm，用于处理使用多键作为分片键进行分片的场景，包含多个分片键的逻辑较复杂，需要应用开发者自行处理其中的复杂度。需要配合ComplexShardingStrategy使用。
Hint分片算法
对应HintShardingAlgorithm，用于处理使用Hint行分片的场景。需要配合HintShardingStrategy使用。
3）分片策略
包含分片键和分片算法，由于分片算法的独立性，将其独立抽离。真正可用于分片操作的是分片键 + 分片算法，也就是分片策略。目前提供5种分片策略。
标准分片策略
对应StandardShardingStrategy。提供对SQL语句中的=, >, <, >=, <=, IN和BETWEEN AND的分片操作支持。StandardShardingStrategy只支持单分片键，提供PreciseShardingAlgorithm和RangeShardingAlgorithm两个分片算法。PreciseShardingAlgorithm是必选的，用于处理=和IN的分片。RangeShardingAlgorithm是可选的，用于处理BETWEEN AND, >, <, >=, <=分片，如果不配置RangeShardingAlgorithm，SQL中的BETWEEN AND将按照全库路由处理。
复合分片策略
对应ComplexShardingStrategy。复合分片策略。提供对SQL语句中的=, >, <, >=, <=, IN和BETWEEN AND的分片操作支持。ComplexShardingStrategy支持多分片键，由于多分片键之间的关系复杂，因此并未进行过多的封装，而是直接将分片键值组合以及分片操作符透传至分片算法，完全由应用开发者实现，最大的灵活度。
行表达式分片策略
对应InlineShardingStrategy。使用Groovy的表达式，提供对SQL语句中的=和IN的分片操作支持，只支持单分片键。对于简单的分片算法，可以通过简单的配置使用，从而避免繁琐的Java代码开发，如: t_user_$->{u_id % 8} 表示t_user表根据u_id模8，而分成8张表，表名称为t_user_0到t_user_7。
Hint分片策略
对应HintShardingStrategy。通过Hint指定分片值而非从SQL中提取分片值的方式进行分片的策略。
不分片策略
对应NoneShardingStrategy。不分片的策略。
4.上代码，开干
此案例为springboot整合mybatis+sharding-jdbc+druid，项目中可根据实际情况替换，sharding都支持
1）引入依赖
server:
port: 9090
servlet:
context-path: /sharding
spring:
application:
name: sharding-demo
mybatis:
mapper-locations: classpath:mapper/*.xml
type-aliases-package: com.dashuai.pojo
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImplpackage com.dashuai;
import io.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication(exclude = {SpringBootConfiguration.class})
@MapperScan("com.dashuai.mapper")
public class ShardingApplication {
public static void main(String[] args) {
SpringApplication.run(ShardingApplication.class, args);
}
}此处有坑：如果你使用java进行sharding配置，那么这里需要排除sharding的自动配置，注意引包为io.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration，否则会报数据源为空或者sqlSessionFactory为空，原因主要是 springboot与shardingjdbc整合的时候，默认会使用SpringbootConfiguration这个类(在sharding-jdbc包下)自动的从配置文件中读取配置，如果读取不到，那么数据源就配置不成功，因为这里我们使用配置类进行配置，不使用配置文件，所以他就读不到配置，那么就会报错，使用java配置有一定优点，请往下看
4）配置类
package com.dashuai.config;
import com.dashuai.utils.datasource.DataSourceUtil;
import com.dashuai.utils.shardingarithmetic.PreciseDatabaseShardingAlgorithm;
import com.dashuai.utils.shardingarithmetic.PreciseTableShardingAlgorithm;
import com.dashuai.utils.shardingarithmetic.RangeDatabaseShardingAlgorithm;
import com.dashuai.utils.shardingarithmetic.RangeTableShardingAlgorithm;
import org.apache.shardingsphere.api.config.sharding.KeyGeneratorConfiguration;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.StandardShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.springframework.boot.SpringBootConfiguration;
import org.springframework.context.annotation.Bean;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
@SpringBootConfiguration
public class ShardingConfig {
/**
* 逻辑表名称
*/
private final String LOGICAL_TABLE = "t_order";
/**
* 分片键
*/
private final String DATABASE_SHARDING_COLUMN = "save_time_com";
@Bean
DataSource getShardingDataSource() throws SQLException {
// 分片规则配置对象
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
// 规则配置
shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());
// 数据库分片算法（精确、范围），按年分库
shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration(DATABASE_SHARDING_COLUMN, new PreciseDatabaseShardingAlgorithm(), new RangeDatabaseShardingAlgorithm()));
// 表分片算法（精确、范围），按月分表
shardingRuleConfig.setDefaultTableShardingStrategyConfig(new StandardShardingStrategyConfiguration(DATABASE_SHARDING_COLUMN, new PreciseTableShardingAlgorithm(), new RangeTableShardingAlgorithm()));
// 默认库，如果存在广播表和绑定表也可在此配置
shardingRuleConfig.setDefaultDataSourceName("default_dataSource");
// 开启日志打印
final Properties properties = new Properties();
properties.setProperty("sql.show", "true");
return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, properties);
}
TableRuleConfiguration getOrderTableRuleConfiguration() {
// 暂定为两年，关于此表达式，可查看官方文档 https://shardingsphere.apache.org/document/legacy/4.x/document/cn/features/sharding/other-features/inline-expression/
String inLineExpressionStr = "dataSource_2020." + LOGICAL_TABLE + "_20200${1..9}" + "," + "dataSource_2021." + LOGICAL_TABLE + "_20210${1..9}" + "," +
"dataSource_2020." + LOGICAL_TABLE + "_20201${0..2}" + "," + "dataSource_2021." + LOGICAL_TABLE + "_20211${0..2}";
final TableRuleConfiguration ruleConfiguration = new TableRuleConfiguration("t_order", inLineExpressionStr);
// 设置主键生成策略
ruleConfiguration.setKeyGeneratorConfig(getKeyGeneratorConfiguration());
return ruleConfiguration;
}
private KeyGeneratorConfiguration getKeyGeneratorConfiguration() {
return new KeyGeneratorConfiguration("SNOWFLAKE", "id");
}
private Map<String, DataSource> createDataSourceMap() {
// key为数据源名称，后面分片算法取得就是这个，value为具体的数据源
final HashMap<String, DataSource> shardingDataSourceMap = new HashMap<>();
shardingDataSourceMap.put("default_dataSource", DataSourceUtil.createDataSource("com.mysql.cj.jdbc.Driver",
"jdbc:mysql://localhost:3306/sharding?userUnicode=ture&characterEncoding=utf8&serverTimezone=GMT%2B8",
"root",
"xhtest"));
shardingDataSourceMap.put("dataSource_2020", DataSourceUtil.createDataSource("com.mysql.cj.jdbc.Driver",
"jdbc:mysql://localhost:3306/order_2020?userUnicode=ture&characterEncoding=utf8&serverTimezone=GMT%2B8",
"root",
"xhtest"));
shardingDataSourceMap.put("dataSource_2021", DataSourceUtil.createDataSource("com.mysql.cj.jdbc.Driver",
"jdbc:mysql://localhost:3306/order_2021?userUnicode=ture&characterEncoding=utf8&serverTimezone=GMT%2B8",
"root",
"xhtest"));
return shardingDataSourceMap;
}
}5）工具类
创建数据源工具类
package com.dashuai.utils.datasource;
import com.alibaba.druid.pool.DruidDataSource;
import javax.sql.DataSource;
public class DataSourceUtil {
public static DataSource createDataSource(final String driverClass, final String url, String userName, String passWord) {
final DruidDataSource result = new DruidDataSource();
result.setDriverClassName(driverClass);
result.setUrl(url);
result.setUsername(userName);
result.setPassword(passWord);
result.setInitialSize(5);
result.setMinIdle(5);
result.setMaxActive(20);
result.setMaxWait(60000);
result.setTimeBetweenEvictionRunsMillis(60000);
result.setMinEvictableIdleTimeMillis(30000);
return result;
}
}时间格式化工具类
package com.dashuai.utils.date;
import java.text.SimpleDateFormat;
import java.util.Date;
public class DateUtil {
private static final SimpleDateFormat yearFormat = new SimpleDateFormat("yyyy");
private static final SimpleDateFormat monthFormat = new SimpleDateFormat("MM");
private static final SimpleDateFormat yearJoinMonthFormat = new SimpleDateFormat("yyyyMM");
public static String getYearByMillisecond(long millisecond) {
return yearFormat.format(new Date(millisecond));
}
public static String getMonthByMillisecond(long millisecond) {
return monthFormat.format(new Date(millisecond));
}
public static String getYearJoinMonthByMillisecond(long millisecond) {
return yearJoinMonthFormat.format(new Date(millisecond));
}
}6）分片算法
分片算法有多种，此案例只使用了标准分片算法
数据库精确分片算法
package com.dashuai.utils.shardingarithmetic;
import com.dashuai.utils.date.DateUtil;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import java.util.Collection;
public class PreciseDatabaseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
/**
* 精确分片算法
*
* @param availableTargetNames 所有配置的库列表
* @param shardingValue 分片值，也就是save_time_com的值
* @return 所匹配库的结果
*/
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
Long value = shardingValue.getValue();
// 库后缀
String yearStr = DateUtil.getYearByMillisecond(value);
if (value <= 0) throw new UnsupportedOperationException("preciseShardingValue is null");
for (String availableTargetName : availableTargetNames) {
if (availableTargetName.endsWith(yearStr)) {
return availableTargetName;
}
}
throw new UnsupportedOperationException();
}
}数据库范围分片算法
在这里插入代码片package com.dashuai.utils.shardingarithmetic;
import com.dashuai.utils.date.DateUtil;
import com.google.common.collect.Range;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import java.util.ArrayList;
import java.util.Collection;
public class RangeDatabaseShardingAlgorithm implements RangeShardingAlgorithm<Long> {
/**
* 范围分片算法
*
* @param availableTargetNames 所有配置的库列表
* @param rangeShardingValue 分片值，也就是save_time_com的值，范围分片算法必须提供开始时间和结束时间
* @return 所匹配库的结果
*/
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> rangeShardingValue) {
final ArrayList<String> result = new ArrayList<>();
Range<Long> range = rangeShardingValue.getValueRange();
long startMillisecond = range.lowerEndpoint();
long endMillisecond = range.upperEndpoint();
// 起始年和结束年
int startYear = Integer.parseInt(DateUtil.getYearByMillisecond(startMillisecond));
int endYear = Integer.parseInt(DateUtil.getYearByMillisecond(endMillisecond));
return startYear == endYear ? theSameYear(String.valueOf(startYear), availableTargetNames, result) : differentYear(startYear, endYear, availableTargetNames, result);
}
// 同一年，说明只需要一个库
private Collection<String> theSameYear(String startTime, Collection<String> availableTargetNames, ArrayList<String> result) {
for (String availableTargetName : availableTargetNames) {
if (availableTargetName.endsWith(startTime)) result.add(availableTargetName);
}
return result;
}
// 跨年
private Collection<String> differentYear(int startYear, int endYear, Collection<String> availableTargetNames, ArrayList<String> result) {
for (String availableTargetName : availableTargetNames) {
for (int i = startYear; i <= endYear; i++) {
if (availableTargetName.endsWith(String.valueOf(i))) result.add(availableTargetName);
}
}
return result;
}
}表精确分片算法
package com.dashuai.utils.shardingarithmetic;
import com.dashuai.utils.date.DateUtil;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import java.util.Collection;
public class PreciseTableShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
/**
* 精确分片算法
*
* @param availableTargetNames 所有配置的表列表，这里代表所匹配到库的所有表
* @param shardingValue 分片值，也就是dau_id的值
* @return 所匹配表的结果
*/
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
long value = shardingValue.getValue();
if (value <= 0) throw new UnsupportedOperationException("preciseShardingValue is null");
final String yearJoinMonthStr = DateUtil.getYearJoinMonthByMillisecond(value);
for (String availableTargetName : availableTargetNames) {
if (availableTargetName.endsWith(yearJoinMonthStr)) {
return availableTargetName;
}
}
throw new UnsupportedOperationException();
}
}表范围分片算法
package com.dashuai.utils.shardingarithmetic;
import com.dashuai.utils.date.DateUtil;
import com.google.common.collect.Range;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue;
import java.util.ArrayList;
import java.util.Collection;
public class RangeTableShardingAlgorithm implements RangeShardingAlgorithm<Long> {
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> rangeShardingValue) {
final ArrayList<String> result = new ArrayList<>();
final Range<Long> range = rangeShardingValue.getValueRange();
long startMillisecond = range.lowerEndpoint();
long endMillisecond = range.upperEndpoint();
// 起始年和结束年
int startYear = Integer.parseInt(DateUtil.getYearByMillisecond(startMillisecond));
int endYear = Integer.parseInt(DateUtil.getYearByMillisecond(endMillisecond));
// 起始月和结束月
int startMonth = Integer.parseInt(DateUtil.getMonthByMillisecond(startMillisecond));
int endMonth = Integer.parseInt(DateUtil.getMonthByMillisecond(endMillisecond));
int startYearJoinMonth = Integer.parseInt(DateUtil.getYearJoinMonthByMillisecond(startMillisecond));
int endYearJoinMonth = Integer.parseInt(DateUtil.getYearJoinMonthByMillisecond(endMillisecond));
return startYear == endYear ? theSameYear(startMonth, endMonth, availableTargetNames, result)
: differentYear(startYear, endYear, startMonth, endMonth, startYearJoinMonth, endYearJoinMonth, availableTargetNames, result);
}
// 同年，但可能不同月
private Collection<String> theSameYear(int startMonth, int endMonth, Collection<String> availableTargetNames, ArrayList<String> result) {
return startMonth == endMonth ? theSameMonth(startMonth, availableTargetNames, result) : differentMonth(startMonth, endMonth, availableTargetNames, result);
}
// 同年同月
private Collection<String> theSameMonth(int startMonth, Collection<String> availableTargetNames, ArrayList<String> result) {
String startMonthStr = String.valueOf(startMonth);
if (startMonthStr.length() == 1) startMonthStr = "0" + startMonthStr;
for (String availableTargetName : availableTargetNames) {
if (availableTargetName.endsWith(startMonthStr)) result.add(availableTargetName);
}
return result;
}
// 同年不同月
private Collection<String> differentMonth(int startMonth, int endMonth, Collection<String> availableTargetNames, ArrayList<String> result) {
for (String availableTargetName : availableTargetNames) {
for (int i = startMonth; i <= endMonth; i++) {
String monthStr = String.valueOf(i);
if (monthStr.length() == 1) monthStr = "0" + monthStr;
if (availableTargetName.endsWith(monthStr)) result.add(availableTargetName);
}
}
return result;
}
// 不同年，跨年，最少两个月，需要考虑跨两年以上的情况
private Collection<String> differentYear(int startYear, int endYear, int startMonth, int endMonth, int startYearJoinMonth, int endYearJoinMonth, Collection<String> availableTargetNames, ArrayList<String> result) {
return endYear - startYear == 1 ? twoYears(startYear, endYear, startMonth, endMonth, startYearJoinMonth, endYearJoinMonth, availableTargetNames, result) : moreThanTwoYears(startYear, endYear, startMonth, endMonth, availableTargetNames, result);
}
// 两年
private Collection<String> twoYears(int startYear, int endYear, int startMonth, int endMonth, int startYearJoinMonth, int endYearJoinMonth, Collection<String> availableTargetNames, ArrayList<String> result) {
int endCondition;
endCondition = Integer.parseInt(startYear + "12");
for (int i = startYearJoinMonth; i <= endCondition; i++) {
for (String availableTargetName : availableTargetNames) {
// 如果多库此算法sharding会匹配两次，需要年份加月份来判断，只使用月份的话有问题
if (availableTargetName.endsWith(String.valueOf(i))) result.add(availableTargetName);
}
}
endCondition = Integer.parseInt(endYear + "01");
for (int i = endYearJoinMonth; i >= endCondition; i--) {
for (String availableTargetName : availableTargetNames) {
if (availableTargetName.endsWith(String.valueOf(i))) result.add(availableTargetName);
}
}
return result;
}
// 两年以上，如果数据量大的话不建议跨太多库
private Collection<String> moreThanTwoYears(int startYear, int endYear, int startMonth, int endMonth, Collection<String> availableTargetNames, ArrayList<String> result) {
return null;
}
}7）实体
package com.dashuai.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Order {
private long id;
private Integer aId;
private Integer bId;
private Integer cId;
private Integer dId;
private Integer eId;
private Date saveTime;
private Long saveTimeCom;
private Integer param1;
private Integer param2;
private Integer param3;
private Integer param4;
private Integer param5;
private Integer param6;
private Float param7;
private Float param8;
private Float param9;
private Float param10;
private Float param11;
private Float param12;
private Float param13;
private Float param14;
private Float param15;
private Float param16;
private Float param17;
private Float param18;
private Float param19;
public Order(int aId, int bId, int cId, int dId, int eId, Date saveTime, long saveTimeCom, int param1, int param2, int param3, int param4, int param5, int param6, float param7, float param8, float param9, float param10, float param11, float param12, float param13, float param14, float param15, float param16, float param17, float param18, float param19) {
this.aId = aId;
this.bId = bId;
this.cId = cId;
this.dId = dId;
this.eId = eId;
this.saveTime = saveTime;
this.saveTimeCom = saveTimeCom;
this.param1 = param1;
this.param2 = param2;
this.param3 = param3;
this.param4 = param4;
this.param5 = param6;
this.param6 = param6;
this.param7 = param7;
this.param8 = param8;
this.param9 = param9;
this.param10 = param10;
this.param11 = param11;
this.param12 = param12;
this.param13 = param13;
this.param14 = param14;
this.param15 = param15;
this.param16 = param16;
this.param17 = param17;
this.param18 = param18;
this.param19 = param19;
}
}7）controller
如果只是为了测试，controller和service其实可以不用，但是一开始我写了，那就贴上来吧
package com.dashuai.controller;
import com.dashuai.pojo.Order;
import com.dashuai.service.OrderService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
@RestController
@RequestMapping("/order")
public class OrderController {
@Autowired
private OrderService orderService;
@GetMapping("/{id}")
public Order fingById(@PathVariable("id") Long orderId) {
Order order = orderService.findById(orderId);
return order;
}
@GetMapping("/fingBySaveTimeCom/{saveTimeCom}")
public Order fingBySaveTimeCom(@PathVariable("saveTimeCom") Long saveTimeCom) {
Order order = orderService.findBySaveTimeCom(saveTimeCom);
return order;
}
@GetMapping("/createTable")
public String createTable() {
try {
orderService.createTable();
} catch (Exception e) {
e.printStackTrace();
return "建表失败";
}
return "建表成功";
}
}8）service
package com.dashuai.service;
import com.dashuai.pojo.Order;
public interface OrderService {
Order findById(Long orderId);
Order findBySaveTimeCom(Long saveTimeCom);
void createTable();
}package com.dashuai.service.impl;
import com.dashuai.mapper.OrderMapper;
import com.dashuai.pojo.Order;
import com.dashuai.service.OrderService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class OrderServiceImpl implements OrderService {
@Autowired
private OrderMapper orderMapper;
@Override
public Order findById(Long orderId) {
return orderMapper.findById(orderId);
}
@Override
public Order findBySaveTimeCom(Long saveTimeCom) {
Order order = orderMapper.findBySaveTimeCom(saveTimeCom);
return order;
}
@Override
public void createTable() {
orderMapper.createTable();
}
}9）mapper
package com.dashuai.mapper;
import com.dashuai.pojo.Order;
import org.apache.ibatis.annotations.Param;
import java.util.ArrayList;
import java.util.List;
public interface OrderMapper {
Order findById(@Param("id") Long orderId);
Order findBySaveTimeCom(@Param("saveTimeCom") Long saveTimeCom);
void createTable();
void insert(Order order);
void insertBatch(@Param("orders") ArrayList<Order> orders);
List<Order> findByOrderBytemporalInterval(@Param("startTime") long startTime, @Param("endTime") long endTime);
}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.dashuai.mapper.OrderMapper">
<!-- 通用查询映射结果 -->
<resultMap id="BaseResultMap" type="com.dashuai.pojo.Order">
<id column="id" property="id" />
<result column="a_id" property="aId" />
<result column="b_id" property="bId" />
<result column="c_id" property="cId" />
<result column="d_id" property="dId" />
<result column="e_id" property="eId" />
<result column="save_time" property="saveTime" />
<result column="save_time_com" property="saveTimeCom" />
<result column="param1" property="param1" />
<result column="param2" property="param2" />
<result column="param3" property="param3" />
<result column="param4" property="param4" />
<result column="param5" property="param5" />
<result column="param6" property="param6" />
<result column="param7" property="param7" />
<result column="param8" property="param8" />
<result column="param9" property="param9" />
<result column="param10" property="param10" />
<result column="param11" property="param11" />
<result column="param12" property="param12" />
<result column="param13" property="param13" />
<result column="param14" property="param14" />
<result column="param15" property="param15" />
<result column="param16" property="param16" />
<result column="param17" property="param17" />
<result column="param18" property="param18" />
<result column="param19" property="param19" />
</resultMap>
<insert id="insert">
INSERT INTO t_order(`a_id`, `b_id`, `c_id`, `d_id`, `e_id`, `save_time`, `save_time_com`, `param1`, `param2`, `param3`, `param4`, `param5`, `param6`, `param7`, `param8`, `param9`, `param10`, `param11`, `param12`, `param13`, `param14`, `param15`, `param16`, `param17`, `param18`, `param19`)
VALUES
(#{aId}, #{bId}, #{cId}, #{dId}, #{eId}, #{saveTime}, #{saveTimeCom}, #{param1}, #{param2}, #{param3}, #{param4}, #{param5}, #{param6}, #{param7}, #{param8}, #{param9}, #{param10}, #{param11}, #{param12}, #{param13}, #{param14}, #{param15}, #{param16}, #{param17}, #{param18}, #{param19})
</insert>
<insert id="insertBatch">
INSERT INTO t_order(`a_id`, `b_id`, `c_id`, `d_id`, `e_id`, `save_time`, `save_time_com`, `param1`, `param2`, `param3`, `param4`, `param5`, `param6`, `param7`, `param8`, `param9`, `param10`, `param11`, `param12`, `param13`, `param14`, `param15`, `param16`, `param17`, `param18`, `param19`)
VALUES
<foreach collection="orders" item="order" separator=",">
(#{order.aId}, #{order.bId}, #{order.cId}, #{order.dId}, #{order.eId}, #{order.saveTime}, #{order.saveTimeCom}, #{order.param1}, #{order.param2}, #{order.param3}, #{order.param4}, #{order.param5}, #{order.param6}, #{order.param7}, #{order.param8}, #{order.param9}, #{order.param10}, #{order.param11}, #{order.param12}, #{order.param13}, #{order.param14}, #{order.param15}, #{order.param16}, #{order.param17}, #{order.param18}, #{order.param19})
</foreach>
</insert>
<update id="createTable">
CREATE TABLE `t_order` (
`id` bigint(20) NOT NULL COMMENT '主键id',
`a_id` int(11) NULL DEFAULT NULL COMMENT '所属建筑物id',
`b_id` int(11) NULL DEFAULT NULL COMMENT '数采器id',
`c_id` tinyint(3) NULL DEFAULT NULL COMMENT '卡槽id',
`d_id` tinyint(3) NULL DEFAULT NULL COMMENT '通道id',
`e_id` tinyint(3) NULL DEFAULT NULL COMMENT '测点id',
`save_time` datetime(0) NULL DEFAULT NULL COMMENT '保存时间',
`save_time_com` bigint(20) NOT NULL DEFAULT 0 COMMENT '时间戳',
`param1` smallint(6) NULL DEFAULT NULL COMMENT '数据类型：0：实时存储数据 1：定时存储数据 2：报警存储数据（状态变化才存储）3：黑匣子存储数据',
`param2` tinyint(3) NULL DEFAULT NULL COMMENT '单位1:\"µm\", 2:\"mm\", 3:\"mil\", 4:\"mm/s\", 5:\"inch/s\", 6:\"m/s2\", 7:\"g\",10：”KPa” 11:”MPa” 12:dB',
`param3` tinyint(3) NULL DEFAULT NULL COMMENT '数据类型, 0 : RMS ;1 :P ; 2 : P-P;',
`param4` int(11) NULL DEFAULT NULL COMMENT '特征值高报状态标志，按位，1表示报警 ; Bit0: 有效值 Bit 1: 峰值 Bit 2: 峰峰值 Bit 3: 峭度 Bit 4: 波峰因数 Bit 10: 通频值 Bit 11: 1X幅值 Bit 12：1X相位 Bit 13：2X幅值 Bit 14：2X相位',
`param5` int(11) NULL DEFAULT NULL COMMENT '特征值高高报状态标志，按位，1表示报警 ;',
`param6` tinyint(3) NULL DEFAULT NULL COMMENT '综合报警标志，所有特征值中的最高报警标志；',
`param7` float NULL DEFAULT NULL COMMENT '转速',
`param8` float NULL DEFAULT NULL COMMENT '有效值',
`param9` float NULL DEFAULT NULL COMMENT '峰值',
`param10` float NULL DEFAULT NULL COMMENT '峰峰值',
`param11` float NULL DEFAULT NULL COMMENT '峭度',
`param12` float NULL DEFAULT NULL COMMENT '波峰因数',
`param13` float NULL DEFAULT NULL COMMENT '通频值',
`param14` float NULL DEFAULT NULL COMMENT '1倍频幅值',
`param15` float NULL DEFAULT NULL COMMENT '1倍频相位',
`param16` float NULL DEFAULT NULL COMMENT '2倍频幅值',
`param17` float NULL DEFAULT NULL COMMENT '2倍频相位',
`param18` float NULL DEFAULT NULL COMMENT '平均值',
`param19` float NULL DEFAULT NULL COMMENT '间隙电压，单位V',
PRIMARY KEY (`id`, `save_time_com`) USING BTREE,
INDEX `saveTimeCom`(`save_time_com`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '振动/压力脉动 特征值 历史数据存储表' ROW_FORMAT = Compact;
</update>
<select id="findById" resultMap="BaseResultMap">
select * from t_order where id = #{id}
</select>
<select id="findBySaveTimeCom" resultMap="BaseResultMap">
select * from t_order where save_time_com = #{saveTimeCom}
</select>
<select id="findByOrderBytemporalInterval" resultMap="BaseResultMap">
select * from t_order where save_time_com between #{startTime} and #{endTime}
</select>
</mapper>10）建库建表
库的名的话随便叫什么，只要和配置类中的createDataSourceMap()对应起来即可，此案例是order+年份，例如现在2021，那么2021年的库就叫order_2021，注意，这里库名随便取，但是createDataSourceMap()里面构建的map的key不能随便取！！！
表名的话也不能随便取，这里是按年分库按月分表，所以这里使用月份作为表名的后缀，例如订单表2020年的1月份的表为order_202101,2年就是24张表，当然，不用你建，交给sharding-jdbc
11）测试类
package com.dashuai.service;
import com.dashuai.mapper.OrderMapper;
import com.dashuai.pojo.Order;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.concurrent.ThreadLocalRandom;
@RunWith(SpringRunner.class)
@SpringBootTest
public class OrderServiceTest {
@Autowired
private OrderMapper orderMapper;
@Test
public void createTable() {
try {
orderMapper.createTable();
} catch (Exception e) {
e.printStackTrace();
}
}
@Test
public void insertOrder() {
final Date saveTime = new Date();
final long saveTimeCom = saveTime.getTime();
System.out.println("当前时间的毫秒是：" + saveTimeCom);
final Order order = new Order(1, 2, 3, 4, 5, saveTime, saveTimeCom, 1, 2, 3, 4, 5, 6, (float) 7, (float) 8, (float) 9, (float) 10, (float) 11, (float) 12, (float) 13, (float) 14, (float) 15, (float) 16, (float) 17, (float) 18, (float) 19);
orderMapper.insert(order);
}
@Test
public void insertBatch() throws InterruptedException {
final ArrayList<Order> orders = new ArrayList<>();
for (int i = 0; i < 10; i++) {
final Date saveTime = new Date();
final long saveTimeCom = saveTime.getTime();
System.out.println("saveTimeCom = " + saveTimeCom);
final Order order = new Order(1, 2, 3, 4, 5, saveTime, saveTimeCom, 1, 2, 3, 4, 5, 6, (float) 7, (float) 8, (float) 9, (float) 10, (float) 11, (float) 12, (float) 13, (float) 14, (float) 15, (float) 16, (float) 17, (float) 18, (float) 19);
orders.add(order);
Thread.sleep(100);
}
orderMapper.insertBatch(orders);
}
@Test
public void insertBatch2() {
// 采用随机时间进行测试，时间区间为2020-01-01到2121-12-31，经转换，2020开始毫秒为：1577808000000，2021结束毫秒为：1640966399000
final ThreadLocalRandom current = ThreadLocalRandom.current();
final ArrayList<Order> orders = new ArrayList<>();
final SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
for (int i = 0; i < 10; i++) {
final long randomTime = current.nextLong(1577808000000L, 1640966399000L);
final Date saveTime = new Date(randomTime);
System.out.println("随机生成的时间为：" + format.format(saveTime) + "毫秒为：" + randomTime);
final Order order = new Order(1, 2, 3, 4, 5, saveTime, randomTime, 1, 2, 3, 4, 5, 6, (float) 7, (float) 8, (float) 9, (float) 10, (float) 11, (float) 12, (float) 13, (float) 14, (float) 15, (float) 16, (float) 17, (float) 18, (float) 19);
orders.add(order);
}
orderMapper.insertBatch(orders);
}
@Test
public void fingOrderById() {
final Order order = orderMapper.findById(564130608058466311L);
System.out.println("order = " + order);
}
@Test
public void findOrderBySaveTimeCom() {
final Order order = orderMapper.findBySaveTimeCom(1582496739530L);
System.out.println("order = " + order);
}
@Test
public void findByOrderBytemporalInterval() {
// 单库单表
// List<Order> orders = orderMapper.findByOrderBytemporalInterval(1609713297565L, 1611479203727L);
// orders.forEach(order -> System.out.println("order = " + order));
// 跨库范围查询
List<Order> orders = orderMapper.findByOrderBytemporalInterval(1592404225183L, 1618528709850L);
orders.forEach(order -> System.out.println("order = " + order));
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
