花了2天時(shí)間研究了一下Sharding-JDBC的使用脐供，這里簡(jiǎn)單記錄一下。
代碼弄的比較亂，就沒(méi)放到git上孕惜，需要源碼的可以留下郵箱或者其他聯(lián)系方式愧薛，我看到了發(fā)你。
遺留問(wèn)題：Druid無(wú)法監(jiān)控2個(gè)數(shù)據(jù)源的sql衫画。
應(yīng)該需要通過(guò)編寫(xiě)DataSourceConfig.java的方式解決毫炉。
目錄
1.POM文件的內(nèi)容
2.分庫(kù)分表+分庫(kù)不分表
3.讀寫(xiě)分離
4.ComplexKeysShardingAlgorithm實(shí)現(xiàn)
1.POM文件的內(nèi)容
pom文件在引用Sharding-jdbc時(shí)有2張方式：
sharding-jdbc-spring-boot-starter（后面的描述用的是這種方式）
這種方式可以不用自己寫(xiě)DataSourceConfig文件-
sharding-jdbc-core
這種方式的例子，基本都是要自己寫(xiě)DataSourceConfig文件的
參考：http://www.reibang.com/p/3b2ab87b0de7
https://blog.csdn.net/a992795427/article/details/85102918
https://www.cnblogs.com/mr-yang-localhost/p/8280500.html#sharding-jdbc-spring-boot-starter方式的參考pom文件 <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.0</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.21</version> </dependency> <dependency> <groupId>io.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>3.1.0.M1</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.23</version> </dependency> </dependencies>
2.分庫(kù)分表+分庫(kù)不分表
用的的數(shù)據(jù)庫(kù)
image.png
# 相關(guān)表結(jié)構(gòu)
CREATE TABLE `order0` (
`id` bigint(11) NOT NULL COMMENT '主鍵ID',
`user_id` bigint(11) DEFAULT NULL COMMENT '用戶ID',
`order_id` bigint(11) DEFAULT NULL COMMENT '訂單ID',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `user_info` (
`user_id` bigint(19) NOT NULL,
`user_name` varchar(45) DEFAULT NULL,
`account` varchar(45) NOT NULL,
`password` varchar(45) DEFAULT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
application.properties文件參考
spring.application.name=sharding-jdbc
#mybatis
mybatis.configuration.map-underscore-to-camel-case=true
mybatis.type-aliases-package=com.lbc.demo.model
mybatis.mapper-locations=classpath:mapper/*Mapper.xml
#當(dāng)注冊(cè)遇到相同名字是否允許被注冊(cè)削罩，在配置中心無(wú)效
spring.main.allow-bean-definition-overriding=true
#所有主從庫(kù)
sharding.jdbc.datasource.names=user0,user1
#dsmaster0
sharding.jdbc.datasource.user0.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.user0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.user0.url=jdbc:mysql://localhost:3306/user0?useSSL=false
sharding.jdbc.datasource.user0.username=root
sharding.jdbc.datasource.user0.password=password
#slave for ds_master_0
sharding.jdbc.datasource.user1.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.user1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.user1.url=jdbc:mysql://localhost:3306/user1?useSSL=false
sharding.jdbc.datasource.user1.username=root
sharding.jdbc.datasource.user1.password=password
#分庫(kù)規(guī)則
sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=user_id
sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=user$->{user_id % 2}
# 綁定邏輯表
sharding.jdbc.config.sharding.binding-tables=order
# 設(shè)置分表規(guī)則
# sharding.jdbc.config.sharding.tables.邏輯表.actual-data-nodes：邏輯表對(duì)應(yīng)的真實(shí)表
# sharding.jdbc.config.sharding.tables.邏輯表.table-strategy.inline.sharding-column：分表列
# sharding.jdbc.config.sharding.tables.邏輯表.table-strategy.inline.algorithm-expression：分表算法
# sharding.jdbc.config.sharding.tables.邏輯表.key-generator-column-name：主鍵列
# 分庫(kù)瞄勾，也分表
sharding.jdbc.config.sharding.tables.order.actual-data-nodes=user$->{0..1}.order$->{0..1}
sharding.jdbc.config.sharding.tables.order.table-strategy.inline.sharding-column=order_id
sharding.jdbc.config.sharding.tables.order.table-strategy.inline.algorithm-expression=order$->{order_id % 2}
sharding.jdbc.config.sharding.tables.order.key-generator-column-name=id
# 只分庫(kù)，不分表
sharding.jdbc.config.sharding.tables.user_info.actual-data-nodes=user$->{0..1}.user_info
sharding.jdbc.config.sharding.tables.user_info.table-strategy.inline.sharding-column=user_id
sharding.jdbc.config.sharding.tables.user_info.table-strategy.inline.algorithm-expression=user_info
sharding.jdbc.config.sharding.tables.user_info.key-generator-column-name = user_id
#邏輯主從庫(kù)名和實(shí)際主從庫(kù)映射關(guān)系
#sharding.jdbc.config.sharding.master-slave-rules.ds0.master-data-source-name=dsmaster0
#用逗號(hào)分隔
#sharding.jdbc.config.sharding.master-slave-rules.ds0.slave-data-source-names=dsmaster0
#sharding.jdbc.config.sharding.master-slave-rules.dsmaster1.masterDataSourceName=dsmaster1
#sharding.jdbc.config.sharding.master-slave-rules.dsmaster1.slaveDataSourceNames=dsmaster1slave0
server.port=8080
3.讀寫(xiě)分離
用到的數(shù)據(jù)庫(kù)和表機(jī)構(gòu)同上
application.yml參考文件
sharding:
jdbc:
dataSource:
names: db-test0,db-test1
# 配置主庫(kù)
db-test0: #org.apache.tomcat.jdbc.pool.DataSource
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/user0?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
username: root
password: password
#最大連接數(shù)
maxPoolSize: 3
db-test1: # 配置第一個(gè)從庫(kù)
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/user1?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
username: root
password: password
maxPoolSize: 3
config:
masterslave: # 配置讀寫(xiě)分離
load-balance-algorithm-type: round_robin # 配置從庫(kù)選擇策略弥激，提供輪詢與隨機(jī)进陡，這里選擇用輪詢//random 隨機(jī) //round_robin 輪詢
name: db1s2
master-data-source-name: db-test0
slave-data-source-names: db-test1
props:
sql: # 開(kāi)啟SQL顯示，默認(rèn)值: false秆撮，注意：僅配置讀寫(xiě)分離時(shí)不會(huì)打印日志Ｋ谋簟；豢觥职辨！
show: true
mybatis:
configuration:
map-underscore-to-camel-case: true
type-aliases-package: com.lbc.demo.model
mapper-locations: classpath:mapper/*Mapper.xml
4.ComplexKeysShardingAlgorithm實(shí)現(xiàn)
Complex方式的配置文件和2.分庫(kù)分表+分庫(kù)不分表的配置文件類(lèi)似，只改了一個(gè)東西
分庫(kù)戈二，也分表
sharding.jdbc.config.sharding.tables.order.actual-data-nodes=user$->{0..1}.order$->{0..1}
sharding.jdbc.config.sharding.tables.order.table-strategy.complex.sharding-columns=user_id,order_id
sharding.jdbc.config.sharding.tables.order.table-strategy.complex.algorithm-class-name=com.lbc.demo.config.ComplexShardingAlgorithm
然后舒裤，需要增加一個(gè)路由類(lèi)ComplexShardingAlgorithm，這里最好debug一下觉吭。
public class ComplexShardingAlgorithm implements ComplexKeysShardingAlgorithm {
/**
* @param collection 在加載配置文件時(shí)腾供，會(huì)解析表分片規(guī)則。將結(jié)果存儲(chǔ)到 collection中鲜滩，doSharding（）參數(shù)使用
* @param shardingValues SQL中對(duì)應(yīng)的
* @return
*/
@Override
public Collection<String> doSharding(Collection<String> collection, Collection<ShardingValue> shardingValues) {
System.out.println("collection:" + collection + ",shardingValues:" + shardingValues);
Collection<Long> orderIdValues = getShardingValue(shardingValues, "order_id");
Collection<Long> userIdValues = getShardingValue(shardingValues, "user_id");
List<String> shardingSuffix = new ArrayList<>();
// user_id伴鳖，order_id分片鍵進(jìn)行分表
for (Long userId : userIdValues) {
for (Long orderId : orderIdValues) {
String suffix = userId % 2 + "_" + orderId % 2;
//寫(xiě)死測(cè)試
suffix="0";
for (String s : collection) {
if (s.endsWith(suffix)) {
shardingSuffix.add(s);
}
}
}
}
return shardingSuffix;
}
private Collection<Long> getShardingValue(Collection<ShardingValue> shardingValues, final String key) {
Collection<Long> valueSet = new ArrayList<>();
Iterator<ShardingValue> iterator = shardingValues.iterator();
while (iterator.hasNext()) {
ShardingValue next = iterator.next();
if (next instanceof ListShardingValue) {
ListShardingValue value = (ListShardingValue) next;
// user_id，order_id分片鍵進(jìn)行分表
if (value.getColumnName().equals(key)) {
return value.getValues();
}
}
}
return valueSet;
}
}
參考了太多了徙硅，記不清了：
http://www.reibang.com/p/3b2ab87b0de7
http://www.reibang.com/p/9eebfae039c9
https://segmentfault.com/a/1190000019906637
https://www.cnblogs.com/yixinjishu/p/10876071.html
https://www.cnblogs.com/mr-yang-localhost/p/8280500.html
[https://blog.csdn.net/hy245120020/article/details/85335446]
http://www.reibang.com/p/e4f291052c83
(https://blog.csdn.net/hy245120020/article/details/85335446)
https://blog.csdn.net/forezp/article/details/94343671
https://blog.csdn.net/a992795427/article/details/85102918
https://www.cnblogs.com/mr-yang-localhost/p/8280500.html
