18-Sharding-Jdbc常规数据源配置和水平分表实战

• 配置文件

spring.application.name=xdclass-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://120.25.217.15:3306/xdclass_shop_order_0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=xdclass.net168

# 第二个数据库
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://120.25.217.15:3306/xdclass_shop_order_1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=xdclass.net168

# 指定product_order表的数据分布情况，配置数据节点,行表达式标识符使用 ${...} 或 $->{...}，
# 但前者与 Spring 本身的文件占位符冲突，所以在 Spring 环境中建议使用 $->{...}
spring.shardingsphere.sharding.tables.product_order.actual-data-nodes=ds0.product_order_$->{0..1}

# 指定product_order表的分片策略，分片策略包括【分片键和分片算法】
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.product_order.table-strategy.inline.algorithm-expression=product_order_$->{user_id % 2}

• 单元测试

@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);
            productOrderDO.setState("PAY");
            productOrderDO.setUserId(Long.valueOf(i + ""));
            productOrderMapper.insert(productOrderDO);
        }
    }

}

• 控制台SQL
  • Logic SQL : 逻辑SQL，没具体到哪个数据节点
  • Actual SQL：真实SQL, 具体到每个数据节点的SQL
    
• 问题
  • 主键重复问题