提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
前言
SpringBoot 整合 Sharding-JDBC
提示:以下是本篇文章正文内容,下面案例可供参考
一、Sharding-JDBC简介
是轻量级的 java 框架,是增强版的 JDBC 驱动,就是简化分库分表后对数据相关操作
二、使用步骤
1.初始化环境
项目需求就是银行流水信息入mysql,水平按照流水时间分片分库分表
新建springboot项目引入下面依赖
按照水平分表的方式,创建数据库和数据库表
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.20</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.0.5</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
</dependencies>
CREATE TABLE `bank_flow_2021`.money_flow_202201 (
id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
flow_id VARCHAR(50) NOT NULL COMMENT '流水id',
money DECIMAL(10,4) NOT NULL COMMENT '金额',
create_time TIMESTAMP COMMENT '创建时间',
flow_time TIMESTAMP NOT NULL COMMENT '流水时间',
sharding_time BIGINT(20) NOT NULL COMMENT '分片时间'
)
准备工作已经完成下面我们看下代码
2.实战
首先我们需要修改配置文件,可以参考官网Spring Boot配置 :: ShardingSphere
# shardingjdbc 分片策略
# 配置数据源,给数据源起名称
spring.shardingsphere.datasource.names=bank2021,bank2022
# 一个实体类对应多张表,覆盖
spring.main.allow-bean-definition-overriding=true
#配置数据源具体内容,包含连接池,驱动,地址,用户名和密码
spring.shardingsphere.datasource.bank2021.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.bank2021.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.bank2021.jdbc-url=jdbc:mysql://127.0.0.1:3306/bank_flow_2021?autoReconnect=true&allowMultiQueries=true
spring.shardingsphere.datasource.bank2021.username=root
spring.shardingsphere.datasource.bank2021.password=123456
#配置数据源具体内容,包含连接池,驱动,地址,用户名和密码
spring.shardingsphere.datasource.bank2022.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.bank2022.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.bank2022.jdbc-url=jdbc:mysql://127.0.0.1:3306/bank_flow_2022?autoReconnect=true&allowMultiQueries=true
spring.shardingsphere.datasource.bank2022.username=root
spring.shardingsphere.datasource.bank2022.password=123456
#指定 money_flow 表分布情况,配置表在哪个数据库里面,表名称都是什么
spring.shardingsphere.sharding.tables.money_flow.actual-data-nodes=bank2021.money_flow_20210${1..9},bank2021.money_flow_20211${0..2},bank2022.money_flow_20220${1..9},bank2022.money_flow_20221${0..2}
#指定数据库 表分片策略 数据库和表都用这个字段分片
spring.shardingsphere.sharding.tables.money_flow.database-strategy.standard.sharding-column=sharding_time
spring.shardingsphere.sharding.tables.money_flow.table-strategy.standard.sharding-column=sharding_time
#数据库分片策略
spring.shardingsphere.sharding.tables.money_flow.database-strategy.standard.preciseAlgorithmClassName=com.example.demo.config.PreciseDatabaseShardingAlgorithm
spring.shardingsphere.sharding.tables.money_flow.database-strategy.standard.rangeAlgorithmClassName=com.example.demo.config.RangeDatabaseShardingAlgorithm
#数据表分片策略
spring.shardingsphere.sharding.tables.money_flow.table-strategy.standard.preciseAlgorithmClassName=com.example.demo.config.PreciseTableShardingAlgorithm
spring.shardingsphere.sharding.tables.money_flow.table-strategy.standard.rangeAlgorithmClassName=com.example.demo.config.RangeTableShardingAlgorithm
# 打开 sql 输出日志
spring.shardingsphere.props.sql.show=true
分片策略也就是通过分片字段怎么找的所需表,需要代码,数据库和表相关分片策略见下面代码
- 精确分片算法用于处理使用单一键作为分片键的=与IN进行分片的场景
/**
* 库精确分片算法
*
*/
public class PreciseDatabaseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
/**
* 库精确分片算法
*
* @param availableTargetNames 所有配置的库列表
* @param shardingValue 分片值
* @return 所匹配库的结果
*/
@Override
public String doSharding(Collection<String> availableTargetNames,
PreciseShardingValue<Long> shardingValue) {
// 分片键值
Long value = shardingValue.getValue();
// 库后缀
String yearStr = ShardingAlgorithmUtil.getYearByMillisecond(value);
if (value <= 0) {
throw new UnsupportedOperationException("preciseShardingValue is null");
}
for (String availableTargetName : availableTargetNames) {
if (availableTargetName.endsWith(yearStr)) {
return availableTargetName;
}
}
throw new UnsupportedOperationException();
}
}
- 范围分片算法用于处理使用单一键作为分片键的BETWEEN AND、>、<、>=、<=进行分片的场景
/**
* 库范围分片算法
*
*/
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) {
ArrayList<String> result = new ArrayList<>();
Range<Long> range = rangeShardingValue.getValueRange();
// 起始年和结束年
int startYear = Integer.parseInt(ShardingAlgorithmUtil.getYearByMillisecond(range.lowerEndpoint()));
int endYear = Integer.parseInt(ShardingAlgorithmUtil.getYearByMillisecond(range.upperEndpoint()));
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;
}
}
/**
* 表精确分片算法
*/
public class PreciseTableShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
/**
* 表精确分片算法
*
* @param availableTargetNames 所有配置的表列表,这里代表所匹配到库的所有表
* @param shardingValue 分片值
* @return 所匹配表的结果
*/
@Override
public String doSharding(Collection<String> availableTargetNames,
PreciseShardingValue<Long> shardingValue) {
// 分片键值
long value = shardingValue.getValue();
if (value <= 0) {
throw new UnsupportedOperationException("preciseShardingValue is null");
}
String yearJoinMonthStr = ShardingAlgorithmUtil.getYearJoinMonthByMillisecond(value);
for (String availableTargetName : availableTargetNames) {
if (availableTargetName.endsWith(yearJoinMonthStr)) {
return availableTargetName;
}
}
throw new UnsupportedOperationException();
}
}
/**
* 表范围分片算法
*
*/
public class RangeTableShardingAlgorithm implements RangeShardingAlgorithm<Long> {
/**
* 表范围分片算法
*/
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames,
RangeShardingValue<Long> rangeShardingValue) {
Range<Long> range = rangeShardingValue.getValueRange();
long startMillisecond = range.lowerEndpoint();
long endMillisecond = range.upperEndpoint();
return getMonthBetween(startMillisecond, endMillisecond, availableTargetNames);
}
/**
* 计算有效的库表名
*/
public static Collection<String> getMonthBetween(long minTime, long maxTime,
Collection<String> availableTargetNames) {
Collection<String> result = new ArrayList<>();
Calendar min = Calendar.getInstance();
Calendar max = Calendar.getInstance();
min.setTime(new Date(minTime));
min.set(min.get(Calendar.YEAR), min.get(Calendar.MONTH), 1);
max.setTime(new Date(maxTime));
max.set(max.get(Calendar.YEAR), max.get(Calendar.MONTH), 2);
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");
while (min.before(max)) {
String yyyyMM = sdf.format(min.getTime());
availableTargetNames.forEach(availableTargetName -> {
if (availableTargetName.endsWith(yyyyMM)) {
result.add(availableTargetName);
}
});
min.add(Calendar.MONTH, 1);
}
return result;
}
}
/**
* 分片算法工具类
*/
public class ShardingAlgorithmUtil {
/**
* 获取年份
*/
public static String getYearByMillisecond(long millisecond) {
return new SimpleDateFormat("yyyy").format(new Date(millisecond));
}
/**
* 获取年月
*/
public static String getYearJoinMonthByMillisecond(long millisecond) {
return new SimpleDateFormat("yyyyMM").format(new Date(millisecond));
}
}
新建实体类
@Data
@TableName(value = "money_flow")
public class BankFlow {
private Long id;
private String flowId;
private BigDecimal money;
private Instant flowTime;
private Instant createTime;
private Long shardingTime;
}
新建mapper
@Repository
public interface BankFlowMapper extends BaseMapper<BankFlow> {
}
代码测试下
@SpringBootTest
class DemoApplicationTests {
@Autowired
private BankFlowMapper bankFlowMapper;
@Test
void saveFlow() {
BankFlow bankFlow = new BankFlow();
bankFlow.setId(100L);
bankFlow.setCreateTime(Instant.now());
bankFlow.setFlowTime(Instant.now());
bankFlow.setFlowId("ceshi");
bankFlow.setMoney(new BigDecimal("888.88"));
bankFlow.setShardingTime(new Date().getTime());
bankFlowMapper.insert(bankFlow);
}
}
数据已经进入相关的库表里面了
总结
以上就是今天要讲的内容,范围的分片之前查询的时候使用分片字段使用between..and ..就可以了