SpringBoot整合sharding-jdbc实现分库分表

<dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC2</version>
        </dependency>

# 定义两个数据源分别为ds0，ds1
spring.shardingsphere.datasource.names=ds1,ds2
# ds1具体配置
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.jdbcUrl=jdbc:mysql://192.168.174.128:3306/sharding_jdbc_node1
spring.shardingsphere.datasource.ds1.username=zhushanglin
spring.shardingsphere.datasource.ds1.password=ZSL1992@!@#zsl
# ds2具体配置
spring.shardingsphere.datasource.ds2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds2.jdbcUrl=jdbc:mysql://192.168.174.129:3306/sharding_jdbc_node2
spring.shardingsphere.datasource.ds2.username=zhushanglin
spring.shardingsphere.datasource.ds2.password=ZSL1992@!@#zsl
# 分片策略: 注意一定要将mapper.xml中的t_order_1全局替换为t_order逻辑表,否则报错:Missing the data source name: 'null'
# t_order表的真实数据节点是ds1或ds2下的t_order_1或t_order_2
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{1..2}.t_order_$->{1..2}
# 数据库选择策略,根据user_id字段对2取余数得到0或1，再+1 就是 ds1或ds2
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ds$->{user_id % 2 + 1}
# 表选择策略，在MySharding类中自定义
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=com.example.shardingjdbcdemo.sharding.MySharding
##主键通过雪花算法，自动填充（分布式id）
# 定义主键列
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
# 定义主键生产方式 雪花算法
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
# 定义worker.id 1024内的数字随便给
spring.shardingsphere.sharding.tables.t_order.key-generator.props.worker.id=345
# 定义回调时间，10毫秒
spring.shardingsphere.sharding.tables.t_order.key-generator.props.max.tolerate.time.difference.milliseconds=10
# 配置mybatis
mybatis.mapper-locations=/mybatis/*.xml
logging.pattern.dateformat=HH:mm:ss

import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import java.util.Collection;
@Slf4j
public class MySharding implements PreciseShardingAlgorithm<Long> {
    /**
     * availableTargetNames 是可选择的表，t_order_1和t_order_2, actual-data-nodes=ds$->{0..1}.t_order_$->{1..2}配置的
     * shardingValue 是封装了逻辑表logicTableName=t_order，分片字段columnName=order_id和雪花算法算出来的值value=756917110673084417
     */
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        Long id = shardingValue.getValue();
        long mode =id % availableTargetNames.size();
        String[] strings = availableTargetNames.toArray(new String[0]);
        mode = Math.abs(mode);
        log.info("选择的表是：t_order_"+(mode+1));
        return strings[(int)mode];
    }
}

/**
     * 测试插入数据
     */
    @Test
    public void testOrderSharding(){
        for(int i=0; i<100; i++){
            Order order = new Order();
            order.setUserId(32);
//        order.setOrderId(2);
            order.setOrderAmount(BigDecimal.TEN);
            order.setOrderStatus(3);
            orderMapper.insertSelective(order);
        }
    }
    /**
     * 测试查询数据
     */
    @Test
    public void testSelectOrderSharding(){
        OrderExample orderExample = new OrderExample();
        orderExample.createCriteria().andIdEqualTo(2).andUserIdEqualTo(22);
        List<Order> orders = orderMapper.selectByExample(orderExample);
        orders.forEach(e -> System.out.println(e.getId()+"---"+e.getUserId()));
    }

#配置广播表（每个库里都有的表也叫全局表）
spring.shardingsphere.sharding.broadcast-tables=area

@Test
    public void testGlobal(){
        Area area = new Area();
        area.setId(2);
        area.setName("上海");
        areaMapper.insert(area);
    }

@Test
    public void testSelectGlobal() {
        AreaExample areaExample = new AreaExample();
        areaExample.createCriteria().andIdEqualTo(2);
        List<Area> areas = areaMapper.selectByExample(areaExample);
        System.out.println("area.size() = "+areas.size());
    }

#配置绑定表（也叫子表, 要保证t_order,t_order_item两张表的分库分表策略一致）
spring.shardingsphere.sharding.binding-tables=t_order,t_order_item
# 分片策略: **注意有绑定关系的两张表分库分表的规则要保持一模一样**
# t_order表的真实数据节点是ds0下的t_order_1和t_order_2
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order
# t_order_item表的真实数据节点是ds0下的t_order_item_1和t_order_item_2
spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds$->{0..1}.t_order_item
# t_order表 数据库选择策略,根据user_id字段
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id
# t_order表 数据库选择策略,算法表达式user_id对2取余数
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ds$->{user_id % 2}
# t_order_item表 数据库选择策略,根据user_id字段
spring.shardingsphere.sharding.tables.t_order_item.database-strategy.inline.sharding-column=user_id
# t_order_item表 数据库选择策略,算法表达式user_id对2取余数
spring.shardingsphere.sharding.tables.t_order_item.database-strategy.inline.algorithm-expression=ds$->{user_id % 2}
## t_order表选择策略,根据id字段
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
## t_order表选择策略,算法表达式id对2取余
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{order_id % 2 + 1}
## t_order_item表选择策略,根据id字段
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id
## t_order_item表选择策略,算法表达式id对2取余
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item_$->{order_id % 2 + 1}