项目需要需要分表,并且是动态的创建数据表,根据月份创建表。研究半天以及网上找到了一些博客和资料完成需求。
话不多说直接上代码。
配置文件 我直接在 applicationContext.xml中加入的配置。
<bean id="ds0" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://localhost:3306/cd_wms?characterEncoding=UTF-8" /> <property name="username" value="root" /> <property name="password" value="root" /> <property name="defaultAutoCommit" value="true"/> <property name="RemoveAbandonedTimeout" value="${c3p0.checkoutTimeout}"/> <property name="initialSize" value="${c3p0.minPoolSize}"/> <property name="minIdle" value="${c3p0.minPoolSize}"/> <property name="maxActive" value="${c3p0.maxPoolSize}"/> <property name="maxIdle" value="${c3p0.maxPoolSize}"/> <property name="maxWait" value="${c3p0.maxIdleTimeExcessConnections}"/> </bean> <!-- 分表算法--> <bean id="preciseModuloTableShardingAlgorithm" class="com.cdsoft.framework.sharding.algorithm.TableShardingAlgorithm" /> <bean id="messageDetailTableShardingAlgorithm" class="com.cdsoft.framework.sharding.algorithm.MessageDetailTableShardingAlgorithm"/> <sharding:standard-strategy id="tableShardingStrategy" sharding-column="create_time" precise-algorithm-ref="preciseModuloTableShardingAlgorithm" /> <sharding:standard-strategy id="messageDetailTableShardingStrategy" sharding-column="id" precise-algorithm-ref="messageDetailTableShardingAlgorithm"/> <sharding:key-generator id="orderKeyGenerator" type="SNOWFLAKE" column="id" /> <sharding:key-generator id="messageDetailKeyGenerator" type="SNOWFLAKE" column="id"/> <sharding:data-source id="shardingDataSource"> <sharding:sharding-rule data-source-names="ds0"> <sharding:table-rules> <sharding:table-rule logic-table="wms_message" actual-data-nodes="ds0.wms_message_20200$->{1..9},ds0.wms_message_20201$->{0..1}" key-generator-ref="orderKeyGenerator" table-strategy-ref="tableShardingStrategy" /> <sharding:table-rule logic-table="wms_message_detail" actual-data-nodes="ds0.wms_message_detail_$->{1..2}" key-generator-ref="messageDetailKeyGenerator" table-strategy-ref="messageDetailTableShardingStrategy"/> </sharding:table-rules> <sharding:binding-table-rules> <sharding:binding-table-rule logic-tables="wms_message,wms_message_detail" /> </sharding:binding-table-rules> <sharding:broadcast-table-rules> <sharding:broadcast-table-rule table="t_config" /> </sharding:broadcast-table-rules> </sharding:sharding-rule> <!-- 日志--> <sharding:props> <prop key="sql.show">true</prop> </sharding:props> </sharding:data-source>
java代码
package com.cdsoft.framework.sharding.jdbc; import com.cdsoft.framework.sharding.config.ActiveConfig; import com.cdsoft.framework.sharding.config.DynamicTablesProperties; import jxl.write.DateTime; import org.apache.commons.configuration.ConfigurationException; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Value; import org.springframework.context.annotation.PropertySource; import org.springframework.stereotype.Repository; import javax.annotation.PostConstruct; import java.sql.*; import java.text.SimpleDateFormat; import java.util.Date; @Repository @PropertySource({"classpath:config/db.properties"}) public class DatabaseTableInit { @Value(value = "${db.dirverClass}") private String driver; @Value(value = "${db.url}") private String url; @Value(value = "${db.username}") private String userName; @Value(value = "${db.password}") private String password; @Autowired private ActiveConfig activeConfig; @Autowired private DynamicTablesProperties dynamicTables; public void init() throws SQLException, ClassNotFoundException{ //连接数据库 Class.forName(driver); //测试url中是否包含useSSL字段,没有则添加设该字段且禁用 if( url.indexOf("?") == -1 ){ url = url + "?useSSL=false" ; } else if( url.indexOf("useSSL=false") == -1 || url.indexOf("useSSL=true") == -1 ) { url = url + "&useSSL=false"; } Connection conn = DriverManager.getConnection(url, userName, password); Statement stat = conn.createStatement(); //要创建数据表的名称 String tableName = "wms_message"; SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM"); String tableSuffix = sdf.format(new Date()); String actualTableName= tableName+"_"+tableSuffix; //获取数据库表名 ResultSet rs = conn.getMetaData().getTables(null, null, actualTableName, null); // 判断表是否存在,如果存在则什么都不做,否则创建表 if( rs.next() ){ return; } else{ // 先判断是否纯在表名,有则先删除表在创建表 // stat.executeUpdate("DROP TABLE IF EXISTS sys_admin_divisions;CREATE TABLE sys_admin_divisions(" //创建行政区划表 // stat.executeUpdate("CREATE TABLE sys_admin_divisions(" // + "ID varchar(32) NOT NULL COMMENT '行政区划ID(行政区划代码)这里不使用32位的UUID,使用全数字的行政区域代码作为ID(如:440000)'," // + "TYPE varchar(50) DEFAULT NULL COMMENT '类型(1省级 2市级 3区县)'," // + "CODE varchar(50) DEFAULT NULL COMMENT '字母代码'," // + "NAME varchar(100) DEFAULT NULL COMMENT '名称'," // + "PINYIN varchar(100) DEFAULT NULL COMMENT '拼音'," // + "PARENT_ID varchar(32) DEFAULT NULL COMMENT '上级行政区划数字代码'," // + "IS_DISPLAY int(1) DEFAULT NULL COMMENT '是否显示( 0:否 1:是 )'," // + "SORT bigint(20) DEFAULT NULL COMMENT '排序标识'," // + "DEL_FLAG int(1) DEFAULT NULL COMMENT '删除标识(0:正常 1:已删除)'," // + "PRIMARY KEY (ID)" // + ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='行政区划 (省市区)';" // ); //创建信息表 stat.executeUpdate("CREATE TABLE "+actualTableName+"(\n" + " `id` bigint(20) NOT NULL,\n" + " `message` varchar(255) DEFAULT NULL COMMENT '信息',\n" + " `create_time` datetime NOT NULL COMMENT '创建事件',\n" + " `type_id` int(11) DEFAULT NULL COMMENT '类型id',\n" + " `detail_id` bigint(20) DEFAULT NULL COMMENT '详情id',\n" + " PRIMARY KEY (`id`) USING BTREE\n" + ") ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;"); //修改配置文件 try { dynamicTables.setNames(new String[]{tableName}); dynamicTables.setActualTableName(actualTableName); activeConfig.configInit(); } catch (Exception e) { e.printStackTrace(); } } // 释放资源 stat.close(); conn.close(); } }
package com.cdsoft.framework.sharding.config; import org.springframework.stereotype.Repository; /** * 动态分表配置 * */ @Repository public class DynamicTablesProperties { String[] names; //需要动态分表的逻辑表明 String actualTableName; //创建的真是表明 public String[] getNames() { return names; } public void setNames(String[] names) { this.names = names; } public String getActualTableName() { return actualTableName; } public void setActualTableName(String actualTableName) { this.actualTableName = actualTableName; } }
package com.cdsoft.framework.sharding.config; import com.google.common.collect.Lists; import com.google.common.collect.Maps; import com.google.common.collect.Sets; import org.apache.commons.configuration.ConfigurationException; import org.apache.commons.configuration.XMLConfiguration; import org.apache.commons.configuration.reloading.FileChangedReloadingStrategy; import org.apache.shardingsphere.core.exception.ShardingConfigurationException; import org.apache.shardingsphere.core.rule.DataNode; import org.apache.shardingsphere.core.rule.ShardingRule; import org.apache.shardingsphere.core.rule.TableRule; import org.apache.shardingsphere.shardingjdbc.jdbc.core.datasource.ShardingDataSource; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Repository; import org.springframework.web.context.ConfigurableWebApplicationContext; import javax.activation.DataSource; import javax.annotation.Resource; import javax.servlet.ServletContext; import java.io.FileNotFoundException; import java.lang.reflect.Field; import java.lang.reflect.Modifier; import java.util.*; import java.util.concurrent.atomic.AtomicInteger; /** * 另一种 基于java.util.properties * 这种方式不会造成写入的键值对顺序紊乱(配置文件里面参数多的情况下极力推荐),不过需要引入commons-configuration包 */ @Repository public class ActiveConfig { Logger log = LoggerFactory.getLogger(getClass()); /** * message_id_mapping 逻辑表名 */ public static final String MESSAGE_ID_MAPPING = "wms_message"; /** * message_id_mapping 分表前缀 */ public static final String MESSAGE_ID_MAPPING_PREFIX = "message_id_mapping_"; /** * message_id_mapping 单分片表数量 */ public static final Long MESSAGE_ID_MAPPING_SINGLE_TABLE_CAPACITY = 20000000L; @Autowired private ConfigurableWebApplicationContext configurableWebApplicationContext; @Autowired private ServletContext servletContext; @Autowired private ShardingDataSource shardingDataSource; @Autowired private DynamicTablesProperties dynamicTables; // @Resource(name = "shardingDataSource") // private DataSource dataSource; private String[] names={"wms_message"}; public void configInit() throws ConfigurationException, FileNotFoundException, Exception { //获取配置文件路径 /* String path = "/config/spring-shardingjdbc.xml"; PropertiesConfiguration config = new PropertiesConfiguration(path); System.out.println(path); config.setAutoSave(true); config.setProperty("current.term", "35"); //重新加载配置文件使属性生效 //重新加载元数据 // XmlWebApplicationContext context = (XmlWebApplicationContext) WebApplicationContextUtils.getWebApplicationContext(servletContext); // context.refresh(); //重新load所有bean configurableWebApplicationContext.refresh(); */ /****************************方案二************************************/ String path = "/config/applicationContext.xml"; XMLConfiguration config = new XMLConfiguration(path); String go=config.getString("sharding:data-source.sharding:sharding-rule.sharding:table-rules.sharding:table-rule(0)[@actual-data-nodes]"); System.out.println(go); //修改配置文件 // String toSave = "ds0.wms_message_20200$->{1..9},ds0.wms_message_20201$->{0..2}"; // config.setAutoSave(true); // config.setProperty("sharding:data-source.sharding:sharding-rule.sharding:table-rules.sharding:table-rule(0)[@actual-data-nodes]",toSave); // String go2=config.getString("sharding:data-source.sharding:sharding-rule.sharding:table-rules.sharding:table-rule(0)[@actual-data-nodes]"); // System.out.println(go2); //从新加载配置文件 // config.setReloadingStrategy(new FileChangedReloadingStrategy()); refreshActualDataNodes(); } /** * 动态刷新配置文件 * @throws NoSuchFieldException * @throws IllegalAccessException */ public void refreshActualDataNodes() throws NoSuchFieldException, IllegalAccessException { log.info("Job 动态刷新 actualDataNodes START"); if (dynamicTables.getNames() == null || dynamicTables.getNames().length == 0) { log.error("【dynamic.table.names】配置为空!"); return; } for (int i = 0; i < dynamicTables.getNames().length; i++) { String dynamicTableName = dynamicTables.getNames()[i]; TableRule tableRule = null; try { ShardingRule shardingRule = shardingDataSource.getShardingContext().getShardingRule(); tableRule = shardingRule.getTableRule(dynamicTableName); } catch (ShardingConfigurationException e) { log.error(String.format("逻辑表:%s 动态分表配置错误!", dynamicTableName)); } String dataSourceName = tableRule.getActualDataNodes().get(0).getDataSourceName(); String logicTableName = tableRule.getLogicTable(); assert tableRule != null; List<DataNode> newDataNodes = getDataNodes(dynamicTableName, dataSourceName, logicTableName); if (newDataNodes.isEmpty()) { throw new UnsupportedOperationException(); } dynamicRefreshDatasource(dataSourceName, tableRule, newDataNodes); } log.info("Job 动态刷新 actualDataNodes END"); } /** * 获取数据节点 */ private List<DataNode> getDataNodes(String tableName, String dataSourceName, String logicTableName) { Set<DataNode> newDataNodes = Sets.newHashSet(); // StringBuilder stringBuilder = new StringBuilder().append(dataSourceName).append(".").append(logicTableName); //创建的逻辑表明 StringBuilder stringBuilder = new StringBuilder().append(dataSourceName).append(".").append(dynamicTables.getActualTableName()); final int length = stringBuilder.length(); // 根据自增id范围分表的场景 // if (tableName.equals(MESSAGE_ID_MAPPING)) { // stringBuilder.setLength(length); // stringBuilder.append("_").append("202010"); //创建新的节点 // DataNode dataNode = new DataNode(stringBuilder.toString()); // newDataNodes.add(dataNode); // } //传入的表明 stringBuilder.setLength(length); DataNode dataNode = new DataNode(stringBuilder.toString()); newDataNodes.add(dataNode); // 扩展点 return Lists.newArrayList(newDataNodes); } /** * 动态刷新数据源 */ private void dynamicRefreshDatasource(String dataSourceName, TableRule tableRule, List<DataNode> newDataNodes) throws NoSuchFieldException, IllegalAccessException { Set<String> actualTables = Sets.newHashSet(); Map<DataNode, Integer> dataNodeIndexMap = Maps.newHashMap(); AtomicInteger index = new AtomicInteger(0); //把现有的加进去 List<DataNode> actualDataNodes = tableRule.getActualDataNodes(); for (DataNode actualDataNode : actualDataNodes) { dataNodeIndexMap.put(actualDataNode,index.intValue()); actualTables.add(actualDataNode.getTableName()); index.set(index.intValue()+1); } dataNodeIndexMap.put(newDataNodes.get(0), index.intValue()); actualTables.add(newDataNodes.get(0).getTableName()); //加入现有的表 newDataNodes.addAll(actualDataNodes); // 动态刷新:actualDataNodesField Field actualDataNodesField = TableRule.class.getDeclaredField("actualDataNodes"); Field modifiersField = Field.class.getDeclaredField("modifiers"); modifiersField.setAccessible(true); modifiersField.setInt(actualDataNodesField, actualDataNodesField.getModifiers() & ~Modifier.FINAL); actualDataNodesField.setAccessible(true); actualDataNodesField.set(tableRule, newDataNodes); // 动态刷新:actualTablesField Field actualTablesField = TableRule.class.getDeclaredField("actualTables"); actualTablesField.setAccessible(true); actualTablesField.set(tableRule, actualTables); // 动态刷新:dataNodeIndexMapField Field dataNodeIndexMapField = TableRule.class.getDeclaredField("dataNodeIndexMap"); dataNodeIndexMapField.setAccessible(true); dataNodeIndexMapField.set(tableRule, dataNodeIndexMap); // 动态刷新:datasourceToTablesMapField // Map<String, Collection<String>> datasourceToTablesMap = Maps.newHashMap(); // datasourceToTablesMap.put(dataSourceName, actualTables); // Field datasourceToTablesMapField = TableRule.class.getDeclaredField("datasourceToTablesMap"); // datasourceToTablesMapField.setAccessible(true); // datasourceToTablesMapField.set(tableRule, datasourceToTablesMap); } }
//分表策略
package com.cdsoft.framework.sharding.algorithm; import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm; import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue; import org.springframework.stereotype.Service; import java.text.SimpleDateFormat; import java.util.Collection; import java.util.Date; /** * message 分表策略 * 按月份分表 */ @Service public class TableShardingAlgorithm implements PreciseShardingAlgorithm<Long> { private static SimpleDateFormat formatter = new SimpleDateFormat("yyyyMM"); @Override public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> preciseShardingValue) { // 基本的表名_年份月份 base_199001 // String targetTable = preciseShardingValue.getLogicTableName() + "_" + formatter.format(new Date()); String targetTable = preciseShardingValue.getLogicTableName() + "_" + formatter.format(preciseShardingValue.getValue()); if (availableTargetNames.contains(targetTable)){ return targetTable; } throw new UnsupportedOperationException("无效的表名称: " + targetTable); } }
触发点在
DatabaseTableInit.java 类中,一次顺序完成,已经测试代码没有问题。这里我是准备做定时任务去触发的。代码中有很多需要优化的地方。这里就不作优化了。感兴趣的同学们可以优化一下。
参考博客:https://qimok.cn/1237.html 非常感谢博主的分享。