shardingsphere,shardingjdbc按年月时间分库,定时创建库表
码云地址:
修正版sharding-jdbc-test
介绍
sharding-jdbc-test
shardingjdbc 4.1 分库分表,按关键字段分库,时间月份分表。 springboot2.* , yml格式配置文件。
插入和查询都是按时间分表,困难点是表无法创建。 通过解析配置文件,初始化创建表,还可以定时任务定时创建表。
软件架构
软件架构说明
安装教程
创建两个库, shard1, shard2 创建表语句 SQL: CREATE TABLE insert_month
( finance_no
bigint(20) NOT NULL COMMENT '流水号', shop_id
int(11) DEFAULT NULL COMMENT '店铺id', create_date_time
datetime DEFAULT NULL, remark
varchar(255) DEFAULT NULL, PRIMARY KEY (finance_no
) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
根据create_date_time 分表, shop_id 分库
分库 shop_id字段 对应关系 alipay:1,sales:0
分库分表逻辑在包 com.test.sharding.config.sharding
自动创建表在 com.test.sharding.scheduled.TableCreate
使用说明
- 批量插入 localhost:8092/insert
- 插入单个 localhost:8092/one
- 查询 http://localhost:8092/get
原来版本https://gitee.com/liran123/sharding_jdbc_test
maven
<dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>4.1.1</version> </dependency> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-namespace</artifactId> <version>4.1.1</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.22</version> </dependency>
启动类
@SpringBootApplication(exclude = {DruidDataSourceAutoConfigure.class})
数据库路由规则类,可以去码云看
DefaultDatabaseRangeShardingAlgorithm DefaultDatabaseShardingAlgorithm DefaultTableRangeShardingAlgorithm DefaultTableShardingAlgorithm
package com.chenfan.finance.config.sharding; import com.google.common.collect.Range; import org.apache.shardingsphere.api.sharding.standard.RangeShardingAlgorithm; import org.apache.shardingsphere.api.sharding.standard.RangeShardingValue; import java.time.LocalDateTime; import java.time.format.DateTimeFormatter; import java.util.Collection; import java.util.LinkedHashSet; /** * 分表的自定义规则类(范围) * * @author lr */ public class DefaultTableRangeShardingAlgorithm extends CommonShardDataBase implements RangeShardingAlgorithm<LocalDateTime> { @Override public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<LocalDateTime> rangeShardingValue) { Range<LocalDateTime> ranges = rangeShardingValue.getValueRange(); LocalDateTime start = ranges.lowerEndpoint(); LocalDateTime end = ranges.upperEndpoint(); int startYear = start.getYear(); int endYear = end.getYear(); int startMonth = start.getMonthValue(); int endMonth = end.getMonthValue(); Collection<String> tables = new LinkedHashSet<>(); if (start.getNano() <= end.getNano()) { for (String c : availableTargetNames) { int cMonth = Integer.parseInt(c.substring(c.length() - 6)); if (cMonth >= Integer.parseInt("" + startYear + startMonth) && cMonth <= Integer.parseInt("" + endYear + endMonth)) { tables.add(c); } } } return tables; } } package com.chenfan.finance.config.sharding; import org.apache.commons.lang3.StringUtils; import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm; import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue; import java.text.SimpleDateFormat; import java.time.LocalDateTime; import java.time.format.DateTimeFormatter; import java.util.Collection; import java.util.Date; /** * @author lr * 分表的自定义规则类(精确) */ public class DefaultTableShardingAlgorithm extends CommonShardDataBase implements PreciseShardingAlgorithm<LocalDateTime> { @Override public String doSharding(Collection<String> collection, PreciseShardingValue<LocalDateTime> preciseShardingValue) { LocalDateTime createTime = preciseShardingValue.getValue(); String timeValue = createTime.format(DateTimeFormatter.ofPattern(DB_SHARD_TIME_FORMAT)); String columnName = preciseShardingValue.getColumnName(); // 需要分库的逻辑表 String table = preciseShardingValue.getLogicTableName(); if (StringUtils.isBlank(timeValue)) { throw new UnsupportedOperationException(columnName + ":列,分表精确分片值为NULL;"); } for (String each : collection) { if (each.startsWith(table)) { return table + "_" + timeValue; } } return table; // throw new UnsupportedOperationException("table not found by shopId ,please config first "); } }
yml配置文件
sharding: create: ## 最多创建当前时间前12个月 maxMonth: 12 ## 默认创建前几个月的 beforeDefaultMonth: 2 ## 默认创建后几个月的 afterDefaultMonth: 0 tables: 202004: toc_trade_refund_order_header,toc_trade_refund_order_detail,toc_sales_order_header, toc_sales_order_detail,toc_sales_deliver_header,toc_sales_deliver_detail,toc_trade_order_header,toc_trade_order_details, toc_stockin_order_header,toc_stockin_order_detail,reference_table 202011: toc_charge spring: shardingsphere: props: sql: show: true datasource: #数据库名称(可以与数据库中的库名不一致) names: alipay,sales,order,eopfinance alipay: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://10.228.84.47:3306/toc_alipay_dev?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai username: root password: 123456 sales: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://10.228.84.47:3306/tmp_toc?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai username: root password: 123456 order: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://10.228.84.47:3306/tmp_toc?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai username: root password: 123456 eopfinance: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://10.228.81.19:38309/eop_finance_dev?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai username: root password: Chenfan@123.com.cn.. sharding: default-data-source-name: eopfinance binding-tables: toc_trade_refund_order_header,toc_trade_refund_order_detail,toc_sales_order_header, toc_sales_order_detail,toc_sales_deliver_header,toc_sales_deliver_detail,toc_alipay_origin, toc_alipay_mapping_income,toc_alipay_mapping_expend,toc_u8_detail,toc_u8_header,reference_table toc_charge,toc_stockin_order_detail,toc_stockin_order_header toc_alipay_origin: actual-data-nodes: alipay.toc_alipay_origin_$->{2020..2021}0$->{1..9}, alipay.toc_alipay_origin_$->{2020..2021}$->{10..12} table-strategy: standard: range-algorithm-class-name: com.chenfan.finance.config.sharding.DefaultTableRangeShardingAlgorithm precise-algorithm-class-name: com.chenfan.finance.config.sharding.DefaultTableShardingAlgorithm sharding-column: account_date
自动创建表的,默认创建前一个月的,其他的可以在配置文件里面配置。
package com.chenfan.finance.scheduled; import com.xxl.job.core.biz.model.ReturnT; import lombok.Getter; import lombok.Setter; import lombok.extern.slf4j.Slf4j; import org.apache.commons.lang3.StringUtils; import org.apache.shardingsphere.shardingjdbc.jdbc.core.datasource.ShardingDataSource; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.boot.env.OriginTrackedMapPropertySource; import org.springframework.core.env.MutablePropertySources; import org.springframework.core.env.PropertySource; import org.springframework.stereotype.Component; import org.springframework.util.Assert; import org.springframework.web.context.support.StandardServletEnvironment; import javax.annotation.PostConstruct; import javax.annotation.Resource; import javax.sql.DataSource; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.time.LocalDateTime; import java.util.HashMap; import java.util.Map; /** * @author liran */ @Slf4j @Setter @Getter @Component @ConfigurationProperties(prefix = "sharding.create") public class TableCreate { @Resource private ShardingDataSource dataSource; @Autowired StandardServletEnvironment env; Map<String, Object> allTable = new HashMap<>(); Map<String, Object> dbAndTable = new HashMap<>(); Map<String, Object> createdTables = new HashMap<>(); private Map<String, String> tables; private Integer maxMonth; private Integer beforeDefaultMonth; private Integer afterDefaultMonth; @PostConstruct public void init() { String v1 = "actual-data-nodes"; String v2 = "spring.shardingsphere.sharding.tables"; MutablePropertySources propertySources = env.getPropertySources(); for (PropertySource<?> propertySource : propertySources) { if (propertySource instanceof OriginTrackedMapPropertySource) { OriginTrackedMapPropertySource originTrackedMapPropertySource = (OriginTrackedMapPropertySource) propertySource; String[] propertyNames = originTrackedMapPropertySource.getPropertyNames(); for (String propertyName : propertyNames) { if (propertyName.startsWith(v2)) { Object property = originTrackedMapPropertySource.getProperty(propertyName); allTable.put(propertyName, property); } } } } String finalSp = "$"; allTable.forEach((k, v) -> { if (k.contains(v1) && String.valueOf(v).contains(finalSp)) { String table = StringUtils.substringBetween(k, v2 + ".", "." + v1); String dbname = StringUtils.substringBefore(String.valueOf(v), "." + table); table = table.trim(); dbname = dbname.trim(); dbAndTable.put(table, dbname); } }); dbAndTable.forEach((k, v) -> { createTable(k, String.valueOf(v)); }); createTablePreMonths(); } private void createTablePreMonths() { tables.forEach((date, d) -> { createPreTables(date, d); }); } private void createPreTables(String date, String tables) { String[] tabs = tables.split(","); for (String table : tabs) { table = table.trim(); if (dbAndTable.containsKey(table)) { String db = (String) dbAndTable.get(table); for (int j = 1; j <= maxMonth; j++) { String localDateString = getLocalDateString(LocalDateTime.now().plusMonths(-j), table); if (localDateString.contains(date)) { createNeedTime(table, db, localDateString); break; } createNeedTime(table, db, localDateString); } } } } private void createTable(String table, String db) { if (afterDefaultMonth != null && afterDefaultMonth > 0) { createNeedTime(table, db, getLocalDateString(LocalDateTime.now(), table)); for (int i = 1; i <= afterDefaultMonth; i++) { createNeedTime(table, db, getLocalDateString(LocalDateTime.now().plusMonths(i), table)); } } if (beforeDefaultMonth != null) { for (int i = 1; i <= beforeDefaultMonth; i++) { createNeedTime(table, db, getLocalDateString(LocalDateTime.now().plusMonths(-i), table)); } } } private void createNeedTime(String table, String db, String create) { DataSource dataSource = this.dataSource.getDataSourceMap().get(db); String sql = "SHOW CREATE TABLE " + table; String existSql = "select * from information_schema.tables where table_name ='" + table + "'; "; doCreate(dataSource, sql, existSql, create, db, table); } private void doCreate(DataSource dataSource, String sql, String existSql, String create, String db, String table) { String msg = " create table: " + create + " origin table: " + table + " db: " + db; Connection conn = null; Statement stmt = null; try { conn = dataSource.getConnection(); stmt = conn.createStatement(); ResultSet resultSet = stmt.executeQuery(existSql); Assert.isTrue(resultSet.next(), msg + "初始化表不存在"); ResultSet resTable = stmt.executeQuery(sql); Assert.isTrue(resTable.next(), msg + "初始化表不存在"); String existTableName = resTable.getString(1); String createSqlOrigin = resTable.getString(2); // log.info(existTableName, createSqlOrigin); String existSqlNew = StringUtils.replaceOnce(existSql, existTableName, create); ResultSet executeQuery = stmt.executeQuery(existSqlNew); if (executeQuery.next()) { log.info("table exist :" + msg); } else { String creatsql = StringUtils.replaceOnce(createSqlOrigin, existTableName, create); if (0 == stmt.executeUpdate(creatsql)) { log.info(msg + "success !"); createdTables.put(create, db); } else { log.error(msg + "fail !"); } } } catch (Exception e) { log.error("create table fail error : {} ", e.getMessage()); } finally { if (stmt != null) { try { stmt.close(); } catch (SQLException e) { log.error("SQLException", e); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { log.error("SQLException", e); } } } } private String getLocalDateString(LocalDateTime now, String table) { int startYear = now.getYear(); int startMonth = now.getMonthValue(); int month = 10; if (startMonth < month) { return table + "_" + startYear + "0" + startMonth; } return table + "_" + startYear + startMonth; } /** * 定时建表 * * @return * @throws SQLException */ // @Scheduled(cron = "0/60 * * * * ?") public ReturnT<String> cfWdtRdCalculateTask() throws SQLException { init(); return ReturnT.SUCCESS; } }
官方文档:
shardingsphere,shardingjdbc按年月时间分库,定时创建库表