shardingjdbc简单使⽤之五种分⽚策略⽂章⽬录
上源码：
@Getter
@Setter
public class YamlShardingStrategyConfiguration implements YamlConfiguration {
i like that音译歌词private YamlStandardShardingStrategyConfiguration standard;
private YamlComplexShardingStrategyConfiguration complex;
private YamlHintShardingStrategyConfiguration hint;
private YamlInlineShardingStrategyConfiguration inline;
private YamlNoneShardingStrategyConfiguration none;
}
即在yml配置⽂件中对应分库分表策略：
每⼀种不同的分⽚策略各⾃的属性值不同
1、inline(⾏表达式)
三维设计培训使⽤很简单，通过书写表达式即可完成(groovy表达式)
public final class YamlInlineShardingStrategyConfiguration implements YamlBaShardingStrategyConfiguration { private String shardingColumn;
private String algorithmExpression;
}
与配置⽂件对应
2、standard
这⾥⽤分表来举例
指定分⽚键，⾃定义分⽚策略算法
@Getter
@Setter
public final class YamlStandardShardingStrategyConfiguration implements YamlBaShardingStrategyConfiguration { private String shardingColumn;
private String preciAlgorithmClassName;
private String rangeAlgorithmClassName;
}
preciAlgorithmClassName：精准算法(sql语句中有=、in) 此必须实现rangeAlgorithmClassName：范围算法(between and)
preciAlgorithmClassName使⽤场景：以分⽚键作为查询字段并使⽤=、in
public interface PreciShardingAlgorithm<T extends Comparable<?>>extends ShardingAlgorithm {
/**
* Sharding.
摘取梦想的启明星*
* @param availableTargetNames available data sources or tables's names
* @param shardingValue sharding value
* @return sharding result for data source or table's name
*/
String doSharding(Collection<String> availableTargetNames, PreciShardingValue<T> shardingValue);
}
⾸先准备配置
#逻辑表名
ur:
actual-data-nodes: db1.table_$->{[1,2]}
#actual-data-nodes: db.table_1,db.table_2
admiration
key-generator:
column: id #⾃定填充字段
type: SNOWFLAKE #雪花算法 UUID、SNOWFLAKE两种(实现ShardingKeyGenerator接⼝)
table-strategy:#分表策略
standard:
shardingColumn: id
itv是什么
preciAlgorithmClassName: ample.sharding_jdbc.standard.MyPreciShardingAlgorithm #rangeAlgorithmClassName:
⾃定义的PreciShardingAlgorithm：
打上断点：测试插⼊⼀条数据运⾏
小学二年级数学应用题
Ur ur =new Ur();
ur.tName("sam");
ur.tAge(22);
ur.tSex(1);
urMapper.inrt(ur);
doSharding⽅法的参数：
1、availableTargetNames：所有的数据存储节点
2、shardingValue：分⽚键值外国朋友
这⾥所有存储节点与配置⽂件中的对应，配置的分⽚键就是id，这⾥也拿到。
doSharding⽅法的参数：
⽅法的返回值就是最终选择的表
实现⼀个偶数存table_1、奇数id存table_2
public class MyPreciShardingAlgorithm implements PreciShardingAlgorithm<Long>{
@Override
public String doSharding(Collection<String> availableTargetNames, PreciShardingValue<Long> shardingValue){
for(String targetName : availableTargetNames){
//偶数存table_1、基数存table_2
String value = Value()% availableTargetNames.size()+1+"";
dsWith(value)){
return targetName;
}
}
throw new RuntimeException("分⽚策略出错");
}烦恼的英文
}
运⾏测试结果：
Actual SQL: db1 ::: INSERT INTO table_2 (id, age, name, x) VALUES (?,?,?,?):::[1475333493534035969,22, sam,1]
rangeAlgorithmClassName
测试范围查询：使⽤场景sql语句中使⽤分⽚键做查询并使⽤between and做范围查询
List<Ur> urs = urMapper.lectList(new QueryWrapper<Ur>().gt("age",20));
如果不实现RangeShardingAlgorithm，默认会从所以的数据节点中查询然后合并结果
: Actual SQL: db1 ::: SELECT id,age,name,x FROM table_1
WHERE (age >?):::[20]
Actual SQL: db1 ::: SELECT id,age,name,x FROM table_2
WHERE (age >?):::[20]
⾃定义实现RangeShardingAlgorithm
public class MyRangeShardingAlgorithm implements RangeShardingAlgorithm<Long>{
@Override
public Collection<String>doSharding(Collection<String> availableTargetNames, RangeShardingValue<Long> shardingValue){ Set<String> list =new HashSet<>();
Long lower = ValueRange().lowerEndpoint();
Long upper = ValueRange().upperEndpoint();
list.add("table_1");
return list;
}
}
⽅法参数
1、availableTargetNames所有实际数据节点
2、shardingValue范围值
⽅法返回值
返回查询的最终数据节点集合
这⾥我指定返回单⼀数据的集合(可⾃定义逻辑返回集合)，那么最终查询只会在这⼀个数据节点上查询
查询语句：要以对应分⽚键作为查询字段
List<Ur> urs = urMapper.lectList(new QueryWrapper<Ur>()
.between("id",1475333493534035969L,1475333493534035979L));
⽇志
Actual SQL: db1 ::: SELECT id,age,name,x FROM table_1
WHERE (id BETWEEN ? AND ?):::[1475333493534035969,1475333493534035979]
最终只在指定的数据节点上做查询
3、hint
不需要在配置⽂件中指定分⽚键，在代码中定义最终选择的库和表
英国留学生政策@Getter
@Setter
public final class YamlHintShardingStrategyConfiguration implements YamlBaShardingStrategyConfiguration {
private String algorithmClassName;
}
配置⽂件：分库分表使⽤同⼀个实现HintShardingAlgorithm的类
tables:
#逻辑表名
ur:
actual-data-nodes: db1.table_$->{[1,2]}
databa-strategy:
hint:
algorithm-class-name: ample.sharding_jdbc.hint.MyHintShardingAlgorithm
table-strategy:#分表策略
hint:
algorithmClassName: ample.sharding_jdbc.hint.MyHintShardingAlgorithm
实现HintShardingAlgorithm接⼝
public class MyHintShardingAlgorithm implements HintShardingAlgorithm<Integer>{
@Override
public Collection<String>doSharding(Collection<String> availableTargetNames, HintShardingValue<Integer> shardingValue){
ArrayList<String> list =new ArrayList<String>();
for(String targetName : availableTargetNames){
Values().contains(targetName)){
list.add(targetName);//选择库、表
break;
}
}
return list;
}
}
在调⽤sql语句之前要使⽤hintmannager设置分库分表的分⽚键，因为hintmannager是由ThreadLocal管理，每次使⽤记得调⽤clear()从threadLocal中清除
public static HintManager getInstance(){
Preconditions.checkState(null== HINT_(),"Hint has previous value, plea clear first.");
HintManager result =new HintManager();
HINT_MANAGER_HOLDER.t(result);
return result;
}
测试
owa
HintManager.clear();
HintManager hintManager = Instance();
hintManager.addTableShardingValue("ur","table_2");
hintManager.addDatabaShardingValue("ur","db1");
// hintManager.tDatabaShardingValue("db1");
List<Ur> urs = urMapper.lectList(new QueryWrapper<Ur>().lect("*"));
addTableShardingValue("ur","table_2")：参数⼀是要指定分表的逻辑表名(配置⽂件中配置的)，参数⼆设置最终选择的实际数据表addDatabaShardingValue("ur","db1")：参数⼀是要指定分表的逻辑表名(配置⽂件中配置的)，参数⼆设置最终选择的实际数据库tDatabaShardingValue：之分库不分表
待定…
4、complex
