简介
首先看到了这个议题以后我阅读了sharding jdbc的官方网站。
首先自己搭了一个demo，实现了分库分表，和主从表设置。
1、引入依赖
项目pom.xml文件中，引入sharding-jdbc的依赖
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
2、引入配置文件
# 表要手动创建，不要自动生成
spring.jpa.hibernate.ddl-auto=none
spring.jpa.show-sql=true
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
# 名称要与db一致
spring.shardingsphere.datasource.names=db0,db1
spring.shardingsphere.datasource.db0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.db0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db0.jdbc-url=jdbc:mysql://localhost:3306/db0?useUnicode=true&characterEncoding=utf-8
spring.shardingsphere.datasource.db0.username=root
spring.shardingsphere.datasource.db0.password=
spring.shardingsphere.datasource.db1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.bd1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db1.jdbc-url=jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf-8
spring.shardingsphere.datasource.db1.username=root
spring.shardingsphere.datasource.db1.password=
# 如果是SNOWFLAKE主键要为LONG类型
spring.shardingsphere.sharding.tables.user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.item.key-generator.column=item_id
spring.shardingsphere.sharding.tables.item.key-generator.type=SNOWFLAKE
#spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=db$->{user_id % 2}
#db$->{0..1}.user_$->{0..1} 无法满足当前情况
spring.shardingsphere.sharding.tables.user.actual-data-nodes=db0.user_0,db1.user_1
spring.shardingsphere.sharding.tables.item.actual-data-nodes=db0.item_0,db1.item_1
# 分库字段
spring.shardingsphere.sharding.tables.user.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.item.database-strategy.inline.sharding-column=item_id
# 分表字段
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.item.table-strategy.inline.sharding-column=item_id
# 分表策略，要保证均匀否则会有某些表永远没有数据
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{user_id % 2}
spring.shardingsphere.sharding.tables.item.table-strategy.inline.algorithm-expression=item_$->{item_id % 2}
# 分库策略，要保证均匀否则会有某些表永远没有数据
spring.shardingsphere.sharding.tables.user.database-strategy.inline.algorithm-expression=db$->{ user_id % 2}
spring.shardingsphere.sharding.tables.item.database-strategy.inline.algorithm-expression=db$->{ item_id % 2}
# 打印log
spring.shardingsphere.props.sql.show=true
3、执行sql创建表
DROP TABLE IF EXISTS `user_0`;
CREATE TABLE `user_0` (
`user_id` bigint DEFAULT NULL,
`name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
SET FOREIGN_KEY_CHECKS = 1;
CREATE TABLE `item_0` (
`item_id` bigint DEFAULT NULL,
`user_id` bigint DEFAULT NULL,
`item_name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
SET FOREIGN_KEY_CHECKS = 1;
在db0创建user_0和item_0表，在db1创建user_1和item_1表
4、编写测试接口
1.编写测试用的类
@Entity
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long userId;
private String name;
public Long getUserId() {
return userId;
}
public void setUserId(Long userId) {
this.userId = userId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
@Entity
public class Item {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long itemId;
private Long userId;
private String itemName;
public Long getItemId() {
return itemId;
}
public void setItemId(Long itemId) {
this.itemId = itemId;
}
public Long getUserId() {
return userId;
}
public void setUserId(Long userId) {
this.userId = userId;
}
public String getItemName() {
return itemName;
}
public void setItemName(String itemName) {
this.itemName = itemName;
}
}
总和的测试类
public class UserItemVO {
private Long itemId;
private Long userId;
private String itemName;
private String userName;
public UserItemVO(Long itemId, Long userId, String itemName, String userName) {
this.itemId = itemId;
this.userId = userId;
this.itemName = itemName;
this.userName = userName;
}
public Long getItemId() {
return itemId;
}
public void setItemId(Long itemId) {
this.itemId = itemId;
}
public Long getUserId() {
return userId;
}
public void setUserId(Long userId) {
this.userId = userId;
}
public String getItemName() {
return itemName;
}
public void setItemName(String itemName) {
this.itemName = itemName;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
@Override
public String toString() {
return "UserItemVO{" + "itemId=" + itemId + ", userId=" + userId + ", itemName='" + itemName + '''
+ ", userName='" + userName + ''' + '}';
}
}
2.编写测试用controller
@GetMapping("/insert")
public void get(){
for (int i = 0; i < 10; i++) {
User user = new User();
user.setName("my name is " + i);
// user.setUserId(i+99L);
User userResult = userRepository.save(user);
Item u = new Item();
//u.setUserId(userResult.getUserId());
u.setItemName("my item is " + i);
itemRepository.save(u);
}
List<User> all1 = userRepository.findAll();
List<Item> all = itemRepository.findAll();
all1.forEach(System.out::println);
all.forEach(System.out::println);
// return all;
List<UserItemVO> bySql = userRepository.findBySql();
bySql.forEach(System.out::println);
return bySql;
}
5、 测试结果
向item插入10条数据， item_name分别为从1到10 ，user_id采用雪花算法生成。
- 结果： item_name 为2，4，6，8，10的数据插入到 db0， item_name 为1，3，5，7，9的数据插入到 db1。
6、 代码地址和讨论
github.com/a58492906/s…，项目我上传到了github， 在加解密的这个部分我遭遇了难点，没有在自己的demo上面实现，然后通过了群里的讨论，我重新编译了昨天下载的sharding源码的example部分，研究了example里面实现加密解密的部分代码
遇到 Unable to load authentication plugin 'caching_sha2_password'这个异常，在mysql里面重新设置以后解决。
第一天主要花了太多时间在自己的demo上，使用了spring的sharding-starter， 但是这个stater的版本只到4.1.1，好像导致了一些异常，切换到官方的example以后就解决了。
