SpringBoot 整合 Sharding-JDBC

javacTree

已于 2022-09-16 17:21:28 修改

阅读量1.2w

点赞数 5

文章标签： java sharding spring boot mybatis

于 2021-11-25 16:12:03 首次发布

本文链接：https://blog.csdn.net/qq_35850889/article/details/121531810

版权

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

文章目录

前言

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);
    }

}