SpringBoot整合 Sharding Jdbc
就是一款数据库中间件,可以做分库分表、读写分离专用，属于Apache基金会的顶级项目
package com.shard.jdbc.config;
import io.shardingsphere.api.config.rule.ShardingRuleConfiguration; import io.shardingsphere.api.config.rule.TableRuleConfiguration; import io.shardingsphere.api.config.strategy.StandardShardingStrategyConfiguration; import io.shardingsphere.core.keygen.DefaultKeyGenerator; import io.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration;
import javax.annotation.Resource; import javax.sql.DataSource; import java.util.HashMap; import java.util.LinkedHashMap; import java.util.Map; import java.util.Properties; @Configuration public class ShardingDataSourceConfig { @Resource private ShardJdbcCon shardJdbcCon; /** * Shard-JDBC 分库配置 */ @Bean public DataSource dataSource () throws Exception { ShardingRuleConfiguration shardJdbcConfig = new ShardingRuleConfiguration(); shardJdbcConfig.getTableRuleConfigs().add(getTableRule01()); // shardJdbcConfig.getTableRuleConfigs().add(getTableRule02()); shardJdbcConfig.setDefaultDataSourceName(“ds_0”); Properties prop = new Properties(); prop.setProperty(“sql.show”,“true”);//开启日志sql prop.put(“executor.size”,“100”);//sharding 线程池 Map<String,DataSource> dataMap = new LinkedHashMap<>() ; String prefix=“ds_”; String data=“shard_”; String url=“jdbc:mysql://localhost:3306/%s?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false”; for (int i = 0; i <DataSourceConstant.DATABASE_COUNT ; i++) { String s=String.format(url,data+i); dataMap.put(prefix+i,shardJdbcCon.dataOneSource(s)); } return ShardingDataSourceFactory.createDataSource(dataMap, shardJdbcConfig, new HashMap<>(), prop); }
/** * Shard-JDBC 分表配置 */ private static TableRuleConfiguration getTableRule01() { TableRuleConfiguration result = new TableRuleConfiguration(); result.setLogicTable("table_one"); result.setActualDataNodes(DataSourceConstant.get()); DefaultKeyGenerator defaultKeyGenerator=new DefaultKeyGenerator(); result.setKeyGenerator(defaultKeyGenerator); result.setKeyGeneratorColumnName("id"); result.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("phone", new DataSourceAlg())); result.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("phone", new TableOneAlg())); return result; }}
package com.shard.jdbc.config;
import com.shard.jdbc.utils.HashUtil; import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue; import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.util.Collection;
/**
数据库映射计算 */ public class DataSourceAlg implements PreciseShardingAlgorithm {
private static Logger LOG = LoggerFactory.getLogger(DataSourceAlg.class); @Override public String doSharding(Collection names, PreciseShardingValue value) { LOG.debug(“分库算法参数 {},{}”,names,value); int hash = HashUtil.getIndex(DataSourceConstant.DATABASE_COUNT,value.getValue()); return “ds_” + hash; } }
package com.shard.jdbc.config;
import com.shard.jdbc.utils.HashUtil; import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue; import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.util.Collection;
/**
分表算法 */ public class TableOneAlg implements PreciseShardingAlgorithm {
private static Logger LOG = LoggerFactory.getLogger(TableOneAlg.class);
/**
该表每个库分5张表 */ @Override public String doSharding(Collection names, PreciseShardingValue value) { LOG.debug(“分表算法参数 {},{}”,names,value); int hash = HashUtil.getIndex(DataSourceConstant.TABLE_COUNT,String.valueOf(value.getValue())); return “table_one_”+ hash; } }这里使用的flyway生成结合存储过程
package com.shard.jdbc.config;
import io.shardingsphere.shardingjdbc.jdbc.core.datasource.ShardingDataSource; import lombok.extern.slf4j.Slf4j; import org.flywaydb.core.api.configuration.ClassicConfiguration; import org.springframework.context.annotation.Bean; import org.springframework.stereotype.Component;
import javax.annotation.Resource; import javax.sql.DataSource; import java.util.Map;
@Component @Slf4j public class FlywayInit { @Resource private ShardingDataSource dataSource;
@Bean public void flyway2() { Map<String, DataSource> dataSourceMap = dataSource.getDataSourceMap(); for (Map.Entry<String, DataSource> entry : dataSourceMap.entrySet()) { ClassicConfiguration configuration = new ClassicConfiguration(); configuration.setBaselineOnMigrate(true); configuration.setDataSource(entry.getValue()); configuration.setCleanOnValidationError(true); org.flywaydb.core.Flyway flyway = new org.flywaydb.core.Flyway(configuration); flyway.migrate(); } }}
//存储过程代码:
DELIMITER $$
DROP PROCEDURE IF EXISTS pro_TableCreate$$
CREATE PROCEDURE pro_TableCreate( ) BEGIN DECLARE i INT; DECLARE table_name VARCHAR(20); SET i = 0;
WHILE i<25 DO #为了使表名成为xxx00这样的格式加的条件判断 IF i<10 THEN SET table_name = CONCAT(‘table_one_’,i); ELSE SET table_name = CONCAT(‘table_one_’,i); END IF;
SET @csql = CONCAT( 'CREATE TABLE ‘,table_name,’( id bigint NOT NULL auto_increment COMMENT"主键ID", phone varchar(20) comment"注释", back_one varchar(50) comment"注释", back_two varchar(50) comment"注释", back_three varchar(50) comment"注释",
PRIMARY KEY(id) )ENGINE=Innodb default charset=utf8;’ );
PREPARE create_stmt FROM @csql; EXECUTE create_stmt; SET i = i+1; END WHILE;
END$$ DELIMITER ; call pro_TableCreate();
主要用来做哈希路由使用。 package com.shard.jdbc.utils;
import org.springframework.util.CollectionUtils;
import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.TreeMap; import java.util.stream.Collectors;
public class HashUtil { /** * bkd一致性哈希算法 * * @param str * @return */ public static int bkdrhash(String str) { final int seed = 131;
int hash = 0; for (int i = 0; i < str.length(); i++) { hash = hash * seed + (int) str.charAt(i); } return hash & 0x7FFFFFFF; } /** * pjw一致性哈希算法 * * @param str * @return */ public static int pjwHash(String str) { int BitsInUnignedInt = 32; int ThreeQuarters = 24; int OneEighth = 4; int HighBits = (int) (0xFFFFFFFF) << (BitsInUnignedInt - OneEighth); int hash = 0; int test = 0; for (int i = 0; i < str.length(); i++) { hash = (hash << OneEighth) + (int) str.charAt(i); if ((test = hash & HighBits) != 0) { hash = ((hash ^ (test >> ThreeQuarters)) & (~HighBits)); } } return hash & 0x7FFFFFFF; } /** * rs一致性哈希算法 * * @param value * @return */ public static int rsHash(String value) { int one = 378551; int two = 63689; int hash = 0; for (int i = 0; i < value.length(); i++) { hash = hash * two + value.charAt(i); two = two * one; } return (hash & 0x7FFFFFFF); } /** * 在分布式服务中，获取路由 * * @param num * @param str * @return */ public static int getIndex1(Integer num, String str) { return pjwHash(str) % num; } public static int getIndex(Integer num, String str) { return rsHash(str) % num; } /** * 计算能均匀分布的数据库数量和表数量 * 默认为10000个数,需要手动选择符合条件的数据库数量和表数量: 数据库数量*表数量=250 * @param x * @param y * @return */ public static Map<String, String> cal(int x, int y) { Map<String, String> result = new TreeMap<>(); for (int c = 1; c < x; c++) { for (int d = 1; d < y; d++) { Map<String, Integer> map =getCountMap(x,y); List<Integer> result4 = map.values().stream().filter(e -> e.equals(0)) .collect(Collectors.toList()); if (CollectionUtils.isEmpty(result4)) { result.put(c + "" + d, c + "$" + d); } } } return result; } /** * 统计分布的总数量 * @param a * @param b * @return */public static Map<String,Integer> getCountMap(int a,int b){ Map<String, Integer> map = new HashMap<>(); for (int i = 0; i < a; i++) { for (int j = 0; j < b; j++) { String s = i + “” + j; map.put(s, 0); } } for (int i = 0; i < 10000; i++) { int data = getIndex(a, String.valueOf(i)); int col = getIndex(b, String.valueOf(i)); String s = data + “” + col; int count = map.get(s) + 1; map.put(s, count); } // map.forEach((x, y) -> { // System.out.println(“key=” + x + " value=" + y); // }); return map; } }
package com.shard.jdbc.config;
public class DataSourceConstant { public static Integer DATABASE_COUNT=11; public static Integer TABLE_COUNT=23; public static String get(){ String s=“ds_KaTeX parse error: Expected '}', got 'EOF' at end of input: ….%s}.table_one_{0…%s}”; String endD=String.valueOf(DATABASE_COUNT-1); String table=String.valueOf(TABLE_COUNT-1); System.out.println(String.format(s,endD,table)); return String.format(s,endD,table); } }
这里我使用了11个数据库，23张表，HashUtils方法可以计算250张表如果要均匀分配需要几个库并且每个库几张表。
csdn下载:https://download.csdn.net/download/qq_38926472/12635187 github地址:https://github.com/yanhua0/shard-jdbc
