网站怎么做用户登录数据库,做网站gzip压缩,事业单位网站建设算固定资产吗,用系统建购物网站Sharding-JDBC系列
1、Sharding-JDBC分库分表的基本使用
2、Sharding-JDBC分库分表之SpringBoot分片策略
3、Sharding-JDBC分库分表之SpringBoot主从配置
4、SpringBoot集成Sharding-JDBC-5.3.0分库分表
5、SpringBoot集成Sharding-JDBC-5.3.0实现按月动态建表分表
前言 … Sharding-JDBC系列
1、Sharding-JDBC分库分表的基本使用
2、Sharding-JDBC分库分表之SpringBoot分片策略
3、Sharding-JDBC分库分表之SpringBoot主从配置
4、SpringBoot集成Sharding-JDBC-5.3.0分库分表
5、SpringBoot集成Sharding-JDBC-5.3.0实现按月动态建表分表
前言
随着业务量的递增项目产生海量的数据在某些场景中需要将数据按月存储。本篇基于Sharding-JDBC 5.3.0分享一下按月自动建表以及分表的实现。
准备工作
创建一个数据库创建一张表表名为tb_order。该表作为基准表。
引入依赖
?xml version1.0 encodingUTF-8?
project xmlnshttp://maven.apache.org/POM/4.0.0xmlns:xsihttp://www.w3.org/2001/XMLSchema-instancexsi:schemaLocationhttp://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsdparentgroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-parent/artifactIdversion2.7.1/versionrelativePath/ !-- lookup parent from repository --/parentmodelVersion4.0.0/modelVersionartifactIdSharding-JDBC-demo2/artifactIddependenciesdependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-web/artifactId/dependencydependencygroupIdcom.baomidou/groupIdartifactIdmybatis-plus-boot-starter/artifactIdversion3.4.1/version/dependencydependencygroupIdorg.apache.shardingsphere/groupIdartifactIdshardingsphere-jdbc-core/artifactIdversion5.3.0/version/dependencydependencygroupIdorg.yaml/groupIdartifactIdsnakeyaml/artifactIdversion1.33/version/dependencydependencygroupIdmysql/groupIdartifactIdmysql-connector-java/artifactIdversion8.0.28/version/dependencydependencygroupIdcom.alibaba/groupIdartifactIddruid/artifactIdversion1.2.6/version/dependencydependencygroupIdorg.projectlombok/groupIdartifactIdlombok/artifactIdversion1.18.22/versionscopecompile/scope/dependency!--dependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-devtools/artifactIdoptionaltrue/optionalscoperuntime/scope/dependency--/dependencies/project
1引入shardingsphere-jdbc-core 5.3.0 的版本
2项目中不要引入spring-boot-devtools否则在调试启动时会报错 spring-boot-devtools 会在类路径上的文件发生更改时自动重启方便开发调试。在项目部署时通过 java -jar 启动项目时会自动禁用开发工具。报错的原因下面说明。 分片规则配置
4.1 application.yml
server:port: 8080
spring:main:# 处理连接池冲突allow-bean-definition-overriding: truedatasource:# shardingsphere5.3.0引入ShardingSphereDriver数据库驱动driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriverurl: jdbc:shardingsphere:classpath:sharding.yml
指定分片规则的文件为sharding.yml。
4.2 sharding.yml
dataSources:order_ds:dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriverClassName: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://localhost:3306/shardingjdbctest?useUnicodetruecharacterEncodingutf8serverTimezoneGMT%2B8useSSLfalseusername: rootpassword: 123456rules:
- !SHARDINGtables:tb_order: #逻辑表actualDataNodes: order_ds.tb_order #表是自动创建keyGenerateStrategy: # 指定主键生成策略column: order_idkeyGeneratorName: snowflaketableStrategy:standard: shardingColumn: order_time #分片键shardingAlgorithmName: custom-time-shardingshardingAlgorithms: #分片算法custom-time-sharding:type: CLASS_BASED #自定义类props:strategy: standardalgorithmClassName: com.jingai.sharding.jdbc.algorithm.OrderTimeShardingAlgorithm #分片算法keyGenerators: # 主键生成器snowflake:type: SNOWFLAKE
props:sql-show: true # 是否打印sql
1配置真实表为tb_order作为分表的表前缀
2配置分表策略为standard标准策略以订单创建日期为分片键
3配置分表算法为自定义类OrderTimeShardingAlgorithm
分片算法OrderTimeShardingAlgorithm
package com.jingai.sharding.jdbc.algorithm;Slf4j
public class OrderTimeShardingAlgorithm implements StandardShardingAlgorithmDate {private static final DateFormat TABLE_SHARD_TIME_FORMAT new SimpleDateFormat(yyyyMM);// 表分片符号。如tb_order_202407private static final String TABLE_SPLIT_SYMBOL _;private Properties props;Overridepublic String doSharding(CollectionString availableTargetNames, PreciseShardingValueDate shardingValue) {String logicTableName shardingValue.getLogicTableName();log.info(精准分片逻辑表名{}节点表名{}, logicTableName, availableTargetNames);Date time shardingValue.getValue();String result logicTableName TABLE_SPLIT_SYMBOL TABLE_SHARD_TIME_FORMAT.format(time);// 在配置中只配置了逻辑表名。如果只有一个且是逻辑表名说明需要获取所有表名initAvailableTargetNames(availableTargetNames, logicTableName);return getAndCreateShardingTable(logicTableName, result, availableTargetNames);}Overridepublic CollectionString doSharding(CollectionString availableTargetNames, RangeShardingValueDate shardingValue) {String logicTableName shardingValue.getLogicTableName();log.info(精准分片逻辑表名{}节点表名{}, logicTableName, availableTargetNames);// 在配置中只配置了逻辑表名。如果只有一个且是逻辑表名说明需要获取所有表名initAvailableTargetNames(availableTargetNames, logicTableName);RangeDate valueRange shardingValue.getValueRange();// 如果没有最大值或最小值则全库扫描if(!valueRange.hasLowerBound() || !valueRange.hasUpperBound()) {return availableTargetNames;}Date min valueRange.lowerEndpoint();Date max valueRange.upperEndpoint();SetString rs new HashSet();while (min.compareTo(max) 0) {String tableName logicTableName _ TABLE_SHARD_TIME_FORMAT.format(min);rs.add(tableName);min DateUtils.addMonths(min, 1);}return getAndCreateShardingTable(logicTableName, rs, availableTargetNames);}private void initAvailableTargetNames(CollectionString availableTargetNames, String logicTableName) {if(availableTargetNames.size() 1 availableTargetNames.contains(logicTableName)) {SetString allTableNameBySchema ShardingAlgorithmUtil.getAllTableNameBySchema(logicTableName);availableTargetNames.clear();availableTargetNames.addAll(allTableNameBySchema);}}/*** 检查可用的真实表如果表名不存在则创建新表* param logicTableName 逻辑表* param resultTableNames 操作需要的真实表* param availableTargetNames 可用的真实表* return 分片的真实表*/private ListString getAndCreateShardingTable(String logicTableName, SetString resultTableNames, CollectionString availableTargetNames) {return resultTableNames.stream().map(name - getAndCreateShardingTable(logicTableName, name, availableTargetNames)).collect(Collectors.toList());}/*** 检查可用的真实表如果表名不存在则创建新表* param logicTableName* param resultTableName* param availableTargetNames* return*/private String getAndCreateShardingTable(String logicTableName, String resultTableName, CollectionString availableTargetNames) {if(availableTargetNames.contains(resultTableName)) {return resultTableName;}boolean rs ShardingAlgorithmUtil.createShardingTable(logicTableName, resultTableName);if(rs) {availableTargetNames.add(resultTableName);return resultTableName;}return null;}Overridepublic Properties getProps() {return props;}Overridepublic void init(Properties properties) {this.props properties;}
}1实现StandardShardingAlgorithm接口重写doSharding()方法
2根据传入的时间分片值解析出年月和逻辑表组合为实际操作的真实表
3如果当前的真实表不存在则调用工具类ShardingAlgorithmUtil创建一个真实表
工具类ShardingAlgorithmUtil
package com.jingai.sharding.jdbc.util;Slf4j
public class ShardingAlgorithmUtil {// 表分片符号。如tb_order_202407private static final String TABLE_SPLIT_SYMBOL _;// 配置的数据库源private volatile static MapString, MapString, Object dataSources null;public static void init(String url) {Assert.hasText(url, 分片策略不能为空);log.info(数据源获取...);byte[] bytes new ShardingSphereDriverURL(url).toConfigurationBytes();try {YamlRootConfiguration yamlRootConfiguration YamlEngine.unmarshal(bytes, YamlRootConfiguration.class);dataSources yamlRootConfiguration.getDataSources();} catch(Exception e) {e.printStackTrace();log.error(分片策略配置解析失败);throw new IllegalArgumentException(分片策略解析失败);}}/*** 获取所有真实表名*/public static SetString getAllTableNameBySchema(String logicTableName) {Assert.notNull(dataSources, 分片策略配置未初始化);SetString rs new HashSet();// 获取配置的数据源String startTable logicTableName TABLE_SPLIT_SYMBOL;for (MapString, Object dataSource : dataSources.values()) {try (Connection conn DriverManager.getConnection(dataSource.get(url).toString(),dataSource.get(username).toString(), dataSource.get(password).toString())){Statement statement conn.createStatement();ResultSet resultSet statement.executeQuery(show tables like startTable %);while (resultSet.next()) {String tableName resultSet.getString(1);if(StringUtils.hasText(tableName) tableName.replaceFirst(startTable, ).matches(\\d{6})) {rs.add(tableName);}}} catch(Exception e) {e.printStackTrace();throw new IllegalArgumentException(数据库连接失败);}}return rs;}/*** 创建分表* param logicTableName* param resultTableName* return*/public static boolean createShardingTable(String logicTableName, String resultTableName) {synchronized (logicTableName.intern()) {for (MapString, Object dataSource : dataSources.values()) {try (Connection conn DriverManager.getConnection(dataSource.get(url).toString(),dataSource.get(username).toString(), dataSource.get(password).toString())){Statement statement conn.createStatement();log.info(创建{}表, resultTableName);statement.execute(create table if not exists resultTableName like logicTableName ;);} catch(Exception e) {e.printStackTrace();throw new IllegalArgumentException(数据库连接失败);}}return true;}}}1init(String url) 初始化方法通过传入的urlapplication.yml中配置的spring.datasource.url解析分片配置文件得到配置的datasources信息
2getAllTableNameBySchema(String logicTableName)通过传入的逻辑表配置中的tb_order结合配置的datasources信息创建连接从数据库中获取表名以tb_order为前缀的表。即数据库中的真实表 真实表只需从主库中获取即可此处可以完善。 3createShardingTable()结合配置的datasources信息创建连接创建真实表
初始化类
package com.jingai.sharding.jdbc.listener;Component
Slf4j
public class ShardingInitRunner implements InitializingBean {Value(${spring.datasource.url})private String url;Overridepublic void afterPropertiesSet() throws Exception {log.info(sharding初始化...);ShardingAlgorithmUtil.init(url);}}该类获取spring.datasource.url的配置值在初始化方法中调用ShardingAlgorithmUtil.init(url)初始化ShardingAlgorithmUtil中的datasource值。 1如果引入了spring-boot-devtools依赖开启开发工具。项目启动的时候ShardingAlgorithmUtil类的类加载器为devtools包下的RestartClassLoader并执行了初始化获取了datasources 2在分片算法OrderTimeShardingAlgorithm的类加载器为AppClassLoaderOrderTimeShardingAlgorithm中调用ShardingAlgorithmUtil时会用AppClassLoader重新加载一次ShardingAlgorithmUtil此时的datasources为null 3此时执行ShardingAlgorithmUtil操作数据库时会报空指针 如果一定要引入spring-boot-devtools依赖可以在项目的resources目录下增加一个文件META-INF/spring-devtools.properties在文件下添加RestartClassLoader额外要加载的包的信息。代码如下 restart.include.shardingsphere/shardingsphere-[\\w\\d-\.]\.jar OrderTimeShardingAlgorithm是在shardingsphere中使用AppClassLoader加载的所以设置shardingsphere使用RestartClassLoader加载。 实体类
package com.jingai.sharing.jdbc.entity;Data
ToString
TableName(tb_order)
public class OrderEntity {private long orderId;private long memberId;private float totalPrice;private String status;private Date orderTime;}
在实体类中TableName指定配置中的逻辑表。
Mapper类
package com.jingai.sharing.jdbc.dao;public interface OrderMapper extends BaseMapperOrderEntity {Insert(insert into tb_order(member_id, total_price, status, order_time) values (#{memberId}, #{totalPrice}, #{status}, #{orderTime}))Options(useGeneratedKeys true, keyProperty orderId)int insert2(OrderEntity order);
}在4.2的配置中通过key-generator设置了逻辑表的主键生成策略为雪花算法。当进行数据插入时需要编写新的插入接口不能直接使用Mybatis-plus中的insert()接口。因为在默认的insert()接口中实体对象的orderId为0不会走配置的雪花算法。
Service类
package com.jingai.sharing.jdbc.service;Service
public class OrderService extends ServiceImplOrderMapper, OrderEntity {Resourceprivate OrderMapper orderMapper;public long insert2(OrderEntity order) {int rs orderMapper.insert2(order);return rs 0 ? order.getOrderId() : 0;}}为了便于测试此处省略了Service的接口类。
Controller类
RestController
public class OrderController {Resourceprivate OrderService orderService;RequestMapping(order)public String order(OrderEntity order) {order.setOrderTime(new Date());long insert orderService.insert2(order);return insert 0 ? success : fail;}RequestMapping(list)public ListOrderEntity list() {return orderService.list();}}
小结
以上为本篇分享的全部内容。以下做一个小结
1创建一个基准表tb_order
2配置分片规则标准策略、以订单时间为分片键、自定义分片算法
3在分片算法中根据分片键的值日期值找到对应月份的表。如果真实表不存在则创建 关于本篇内容你有什么自己的想法或独到见解欢迎在评论区一起交流探讨下吧。
