这是整个项目的目录
properties文件 注意：一定要手动创建好数据库和表4.0.0 net.demo sharding-jdbc 1.0-SNAPSHOT 1.8 1.8 1.8 2.5.5 3.4.0 1.18.16 4.1.1 4.12 1.1.16 true org.springframework.boot spring-boot-starter-web ${spring.boot.version} org.springframework.boot spring-boot-starter-test ${spring.boot.version} test com.baomidou mybatis-plus-boot-starter ${mybatisplus.boot.starter.version} mysql mysql-connector-java 8.0.27 org.projectlombok lombok ${lombok.version} org.apache.shardingsphere sharding-jdbc-spring-boot-starter ${sharding-jdbc.version} junit junit ${junit.version} org.springframework.boot spring-boot-maven-plugin ${spring.boot.version} true true
spring.application.name=sharding-jdbc
server.port=8080
# 打印执行的数据库以及语句
spring.shardingsphere.props.sql.show=true
# 数据源 db0
spring.shardingsphere.datasource.names=ds0,ds1
# 第一个数据库
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://127.0.0.1:3306/sharding-jdbc_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=root
# 第二个数据库
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://127.0.0.1:3306/sharding-jdbc_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=root
#指定表的主键生成策略
spring.shardingsphere.sharding.tables.product_order.key-generator.column=id
spring.shardingsphere.sharding.tables.product_order.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.product_order.key-generator.props.worker.id=1
# 默认数据源，没有分片的走这个数据源
spring.shardingsphere.sharding.default-data-source-name=ds0
#分库策略 根据user_id字段取模
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{user_id % 2}
# 指定product_order表的数据分布情况，配置数据节点,行表达式标识符使用 ${...} 或 $->{...}，
# 但前者与 Spring 本身的文件占位符冲突，所以在 Spring 环境中建议使用 $->{...}
spring.shardingsphere.sharding.tables.product_order.actual-data-nodes=ds$->{0..1}.product_order_$->{0..1}
#分表策略 指定product_order表的分片策略，分片策略包括【分片键（id）和分片算法（product_order_$->{id % 2}）】
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.algorithm-expression=product_order_$->{id % 2}
#自定义分库分表策略
#spring.shardingsphere.sharding.tables.product_order.table-strategy.standard.sharding-column=id
#spring.shardingsphere.sharding.tables.product_order.table-strategy.standard.precise-algorithm-class-name=net.xdclass.config.MyTablePreciseShardingAlgorithm
#spring.shardingsphere.sharding.tables.product_order.table-strategy.standard.range-algorithm-class-name=net.xdclass.config.MyTablePreciseShardingAlgorithm
启动文件DemoApplication
@MapperScan("net.xdclass.mapper")
@EnableTransactionManagement
@SpringBootApplication
public class DemoApplication {
public static void main(String [] args){
SpringApplication.run(DemoApplication.class,args);
}
}
实体类ProductOrderDO
product_order是逻辑表，可以没有， 但是product_order_0,product_order_1必须有
@Data
@TableName("product_order")
@EqualsAndHashCode(callSuper = false)
public class ProductOrderDO {
// @TableId(value = "id")
private Long id;
private String outTradeNo;
private String state;
private Date createTime;
private Double payAmount;
private String nickname;
private Long userId;
}
ProductOrderMapper
public interface ProductOrderMapper extends baseMapper测试类DbTest{ @Select("select * from product_order ") List selectByUserAll(); }
@RunWith(SpringRunner.class)
@SpringBootTest(classes = DemoApplication.class)
@Slf4j
public class DbTest {
@Autowired
private ProductOrderMapper productOrderMapper;
@Test
public void testSaveProductOrder(){
for(int i=0; i<10;i++){
ProductOrderDO productOrderDO = new ProductOrderDO();
productOrderDO.setCreateTime(new Date());
productOrderDO.setNickname("分库分表i="+i);
productOrderDO.setOutTradeNo(UUID.randomUUID().toString().substring(0,32));
productOrderDO.setPayAmount(100.00+i);
productOrderDO.setState("PAY");
productOrderDO.setUserId(Long.valueOf(i+""));
productOrderMapper.insert(productOrderDO);
try {
Thread.sleep(100);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
}
@Test
public void testShardingReadCount(){
QueryWrapper queryWrapper=new QueryWrapper();
long count = productOrderMapper.selectCount(queryWrapper);
System.out.println("count:"+count);
}
@Test
public void testShardingReadList(){
List list = productOrderMapper.selectByUserAll();
System.out.println("list大小:"+list.size());
System.out.println("list:"+list);
}
}
sql文件
在数据库sharding-jdbc_0、sharding-jdbc_1中分别执行sql
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for product_order_0 -- ---------------------------- DROP TABLE IF EXISTS `product_order_0`; CREATE TABLE `product_order_0` ( `id` bigint(20) NOT NULL , `out_trade_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '订单唯一标识', `state` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'NEW 未支付订单,PAY已经支付订单,CANCEL超时取消订单', `create_time` datetime(0) NULL DEFAULT NULL COMMENT '订单生成时间', `pay_amount` decimal(16, 2) NULL DEFAULT NULL COMMENT '订单实际支付价格', `nickname` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '昵称', `user_id` bigint(20) NULL DEFAULT NULL COMMENT '用户id', PRIMARY KEY (`id`) USING BTREE ) CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic; SET FOREIGN_KEY_CHECKS = 1; DROP TABLE IF EXISTS `product_order_1`; CREATE TABLE `product_order_1` ( `id` bigint(20) NOT NULL , `out_trade_no` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '订单唯一标识', `state` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT 'NEW 未支付订单,PAY已经支付订单,CANCEL超时取消订单', `create_time` datetime(0) NULL DEFAULT NULL COMMENT '订单生成时间', `pay_amount` decimal(16, 2) NULL DEFAULT NULL COMMENT '订单实际支付价格', `nickname` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '昵称', `user_id` bigint(20) NULL DEFAULT NULL COMMENT '用户id', PRIMARY KEY (`id`) USING BTREE ) CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic; SET FOREIGN_KEY_CHECKS = 1;执行test文件中testSaveProductOrder方法 ：成功
sharding-jdbc_0：
sharding-jdbc_01：
user_id 为偶数在第一个库里面， user_id 为奇数在第二个库里面 id 为偶数在第一个表里面， id为奇数在第二个表里面
因为我们是根据user_id分库。 根据id分表。
所以成功了！！！
使用自定义策略：standard修改配置文件
把策略inline 注释掉，打开下面的注释即可。
再添加一个配置文件MyTablePreciseShardingAlgorithm
@Slf4j
public class MyTablePreciseShardingAlgorithm implements PreciseShardingAlgorithm, RangeShardingAlgorithm {
@Override
public String doSharding(Collection collection, PreciseShardingValue preciseShardingValue) {
log.info("collection:{}",collection);
log.info("preciseShardingValue:{}",preciseShardingValue);
Long id = (Long)preciseShardingValue.getValue();
String index = String.valueOf(id % 2);
String logicTableName = preciseShardingValue.getLogicTableName();
logicTableName = logicTableName.concat("_"+index);
return logicTableName;
}
@Override
public Collection doSharding(Collection collection, RangeShardingValue rangeShardingValue) {
return null;
}
}
测试也是正常的！！！！
