ShardingJDBC《三》自定义扩展篇
程序员文章站
2022-03-26 15:47:58
1.分布式主键1.1.雪花算法1.2.自定义ID生成器1.3.整合IdGenerator2.自定义分片策略3.Update语句的坑...
源码Github地址
1.分布式主键
1.1.数据库存储
（1）、添加TB_SEQUENCE表
| 字段名 | 数据类型 | 默认值 | 可为空 | 备注 |
|---|---|---|---|---|
| ID | int(11) | 无 | no | 主键（自增） |
| SEQ_NAME | varchar(50) | 无 | no | 表名称 |
| MIN_VALUE | int(11) | 1 | no | 最小值 |
| MAX_VALUE | int(11) | 99999999999999 | no | 最大值 |
| CURRENT_VAL | int(11) | 1 | no | 当前值 |
| INCREMENT_VAL | int(11) | 1 | no | 增长值距 |
| CREATE_TIME | datetime | no | 创建时间 | |
| OPT_TIME | datetime | no | 最近修改时间 |
注：application-sharding.properties添加如下配置项：
spring.shardingsphere.sharding.tables.tb_sequence.actual-data-nodes=basic.tb_sequence（2）、封装获取主键SEQUENCE值的dao及service类封装
@Repository
public interface ISequenceDao extends JpaRepository<SequenceEntity, Long> {
/**
*
* @param seqName
* @return
*/
@Query("from SequenceEntity where seqName = :seqName for update")
SequenceEntity findBySeqName(@Param("seqName") String seqName);
}@Service
public class SequenceService {
private static final Logger logger = LoggerFactory.getLogger(SequenceService.class);
/**
* 注入SequenceDAO
*/
@Autowired
private ISequenceDao seqDAO;
/**
* 获取下一个自增ID
*
* @param seqName 序列名称
* @return id
*/
@Transactional(readOnly = false, propagation = Propagation.REQUIRES_NEW)
public long getNextVal(String seqName) {
try {
SequenceEntity se = seqDAO.findBySeqName(seqName);
if (Objects.isNull(se)) {
SequenceEntity sequence = new SequenceEntity();
sequence.setSeqName(seqName);
sequence.setMinValue(1L);
sequence.setMaxValue(99999999999999L);
sequence.setCurrentVal(1L);
sequence.setIncrementVal(1L);
se = seqDAO.save(sequence);
return 1L;
}
long incrementV = se.getIncrementVal();
if (incrementV < 1) {
incrementV = 1;
}
Long val = se.getCurrentVal();
Long min = se.getMinValue();
Long max = se.getMaxValue();
Long nextVal = val + incrementV;
if (nextVal > max) {
nextVal = min;
}
if (nextVal < min) {
nextVal = min;
}
se.setCurrentVal(nextVal);
seqDAO.save(se);
return nextVal;
} catch (Exception e) {
logger.error("使用数据库生成{}表的主键发生异常！",seqName, e);
}
return 0;
}
}（3）、使用示例
public class MyClassUtil {
/**
*
* @param cls
* @return
*/
public static String getTableName(Class<?> cls) {
// 获取表名
String tableName = JavaNameToTableName(cls.getSimpleName());
Table annotation[] = cls.getDeclaredAnnotationsByType(Table.class);
if (annotation != null && annotation.length > 0) {
tableName = annotation[0].name();
}
return tableName;
}
/**
*
* @param fieldName
* @return
*/
private static String JavaNameToTableName(String fieldName) {
if (!StringUtils.isEmpty(fieldName)) {
StringBuilder columnName = new StringBuilder();
char chars[] = fieldName.toCharArray();
for (int i = 0; i < chars.length; i++) {
// 如果出现大写字母,增加一个下划线
if (i != 0 && Character.isUpperCase(chars[i])) {
columnName.append("_");
}
columnName.append(chars[i]);
}
return columnName.toString().toLowerCase();
}
return null;
}
}@Transactional
public Long saveAndReturnByDB(UserinfoVO param) {
String seqName = MyClassUtil.getTableName(UserinfoEntity.class);
Long id = sequenceService.getNextVal(seqName);
param.setId(id);
userinfoDao.save(this.getEntity(param));
return id;
}@RunWith(SpringRunner.class)
@SpringBootTest(classes = InitApp.class)
@FixMethodOrder(MethodSorters.NAME_ASCENDING)
public class UserinfoServiceTest {
@Autowired
private IUserinfoService userinfoService;
@Test
public void test1SaveAndReturnByDB() {
for(int i=0;i<200;i++) {
UserinfoVO userinfo = new UserinfoVO("user" + i,"**" + i + "**","test" + i + "@qq.com");
userinfoService.saveAndReturnByDB(userinfo);
}
}
}（4）、执行效果
1.2.Redis生成
例如Redis的原子性特性，使用increment方法进行主键生成
（1）、业务表Redis中的主键key常量定义
public class IdGeneraterKey {
public static final String REDIS_KEY_GENERATER_USERINFO = "sharding:generater:key:userinfo";
public static final String REDIS_KEY_GENERATER_ADDRESS = "sharding:generater:key:address";
}（2）、使用示例
public Long saveAndReturnByRedis(UserinfoVO param) {
Long id = redisTemplate.opsForValue().increment(IdGeneraterKey.REDIS_KEY_GENERATER_USERINFO,1);
param.setId(id);
try {
UserinfoEntity entity = userinfoDao.save(this.getEntity(param));
if(entity != null) {
return id;
}
logger.error("使用Redis方式生成主键保存TB_USERINFO数据失败！");
redisTemplate.opsForValue().decrement(IdGeneraterKey.REDIS_KEY_GENERATER_USERINFO,1);
} catch (Exception e) {
logger.error("使用Redis方式生成主键保存TB_USERINFO数据出错！",e);
redisTemplate.opsForValue().decrement(IdGeneraterKey.REDIS_KEY_GENERATER_USERINFO,1);
}
return null;
}@Test
public void test1SaveAndReturnByRedis() {
for(int i=0;i<200;i++) {
UserinfoVO userinfo = new UserinfoVO("user" + i,"**" + i + "**","test" + i + "@qq.com");
userinfoService.saveAndReturnByRedis(userinfo);
}
}（3）、执行效果
执行前：
执行后：
1.3.自带雪花算法整合
采用整合ShardingSphere自带的雪花算法（配置文件方式），在连接srd_study_db0的情况下，将tb_userinfo水平切分为4张表；使用单元测试添加200条
（1）、雪花算法工具类
public class SnowFlakeUtil {
/**
* 起始的时间戳
*/
private final static long START_STMP = 1480166465631L;
/**
* 每一部分占用的位数
*/
private final static long SEQUENCE_BIT = 12; //序列号占用的位数
private final static long MACHINE_BIT = 5; //机器标识占用的位数
private final static long DATACENTER_BIT = 5;//数据中心占用的位数
/**
* 每一部分的最大值
*/
private final static long MAX_DATACENTER_NUM = -1L ^ (-1L << DATACENTER_BIT);
private final static long MAX_MACHINE_NUM = -1L ^ (-1L << MACHINE_BIT);
private final static long MAX_SEQUENCE = -1L ^ (-1L << SEQUENCE_BIT);
/**
* 每一部分向左的位移
*/
private final static long MACHINE_LEFT = SEQUENCE_BIT;
private final static long DATACENTER_LEFT = SEQUENCE_BIT + MACHINE_BIT;
private final static long TIMESTMP_LEFT = DATACENTER_LEFT + DATACENTER_BIT;
private long datacenterId; //数据中心
private long machineId; //机器标识
private long sequence = 0L; //序列号
private long lastStmp = -1L;//上一次时间戳
public SnowFlakeUtil(long datacenterId, long machineId) {
if (datacenterId > MAX_DATACENTER_NUM || datacenterId < 0) {
throw new IllegalArgumentException("datacenterId can't be greater than MAX_DATACENTER_NUM or less than 0");
}
if (machineId > MAX_MACHINE_NUM || machineId < 0) {
throw new IllegalArgumentException("machineId can't be greater than MAX_MACHINE_NUM or less than 0");
}
this.datacenterId = datacenterId;
this.machineId = machineId;
}
/**
* 产生下一个ID
*
* @param ifEvenNum 是否偶数 true 时间不连续全是偶数 时间连续 奇数偶数 false 时间不连续 奇偶都有 所以一般建议用false
* @return
*/
public synchronized long nextId(boolean ifEvenNum) {
long currStmp = getNewstmp();
if (currStmp < lastStmp) {
throw new RuntimeException("Clock moved backwards. Refusing to generate id");
}
/**
* 时间不连续出来全是偶数
*/
if(ifEvenNum){
if (currStmp == lastStmp) {
//相同毫秒内，序列号自增
sequence = (sequence + 1) & MAX_SEQUENCE;
//同一毫秒的序列数已经达到最大
if (sequence == 0L) {
currStmp = getNextMill();
}
} else {
//不同毫秒内，序列号置为0
sequence = 0L;
}
}else {
//相同毫秒内，序列号自增
sequence = (sequence + 1) & MAX_SEQUENCE;
}
lastStmp = currStmp;
return (currStmp - START_STMP) << TIMESTMP_LEFT //时间戳部分
| datacenterId << DATACENTER_LEFT //数据中心部分
| machineId << MACHINE_LEFT //机器标识部分
| sequence; //序列号部分
}
private long getNextMill() {
long mill = getNewstmp();
while (mill <= lastStmp) {
mill = getNewstmp();
}
return mill;
}
private long getNewstmp() {
return System.currentTimeMillis();
}
}（2）、添加自定义配置
# 自定义雪花算法配置
# 分布式数据中心ID（最大值为31，从0开始）
config.snowflake.distributed.data.center.worker.id=0
# 雪花算法机器ID（最大值为31，从0开始）
config.snowflake.machine.worker.id=0（3）、使用
@Value("${config.snowflake.distributed.data.center.worker.id:0}")
private int dataCenterWorkerId;
@Value("${config.snowflake.machine.worker.id:0}")
private int machineWorkerId;
@Test
public void test1Save() {
SnowFlakeUtil snowFlake = new SnowFlakeUtil(dataCenterWorkerId,machineWorkerId);
for(int i=0;i<200;i++) {
UserinfoVO userinfo = new UserinfoVO("user" + i,"**" + i + "**","test" + i + "@qq.com");
userinfo.setId(snowFlake.nextId(false));
userinfoService.save(userinfo);
}
}（4）、实现效果
2.自定义分片策略
2.1.自定义分库类
public class UserinfoDbShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
private static Logger logger = LoggerFactory.getLogger(UserinfoDbShardingAlgorithm.class);
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
long dbNum = shardingValue.getValue() % 8 / 4;
logger.info("当前分库分表生成的主键ID为：{}，分配到的数据库为：db{}",shardingValue.getValue(),dbNum);
return "db" + dbNum;
}
}2.2.自定义分表类
public class UserinfoTableShardingAlgorithm implements PreciseShardingAlgorithm<Long> {
private static Logger logger = LoggerFactory.getLogger(UserinfoTableShardingAlgorithm.class);
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
long tableNum = shardingValue.getValue() % 4;
logger.info("当前分库分表生成的主键ID为：{}，分配到的表为：tb_userinfo_{}",shardingValue.getValue(),tableNum);
return "tb_userinfo_" + tableNum;
}
}2.3.添加配置文件
# 用户表配置（使用Java类配置自定义分库分表）
spring.shardingsphere.sharding.tables.tb_userinfo.database-strategy.standard.sharding-column=id
spring.shardingsphere.sharding.tables.tb_userinfo.database-strategy.standard.precise-algorithm-class-name=com.xiudoua.micro.study.config.UserinfoDbShardingAlgorithm
spring.shardingsphere.sharding.tables.tb_userinfo.table-strategy.standard.sharding-column=id
spring.shardingsphere.sharding.tables.tb_userinfo.table-strategy.standard.precise-algorithm-class-name=com.xiudoua.micro.study.config.UserinfoTableShardingAlgorithm2.4.执行结果
本文地址：https://blog.csdn.net/u012459871/article/details/113814423