一. 概述

sharding-jdbc官网
参考开源项目https://github.com/xkcoding/spring-boot-demo
分库分表在面试时经常会问到, 理论很多人都懂. 但怎样实现可能很多人没有实践过. 参考了官网的demo,结合自己的理解写个例子给大家参考吧

二. SpringBootDemo

2.1 Demo数据库脚本

CREATE DATABASE IF NOT EXISTS `spring_boot_demo_1`
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_unicode_ci;

CREATE DATABASE IF NOT EXISTS `spring_boot_demo_2`
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_unicode_ci;

create table `spring_boot_demo_1`.t_order_0
(
    id       bigint auto_increment comment '主键'
        primary key,
    user_id  bigint                  not null comment '用户id',
    order_id bigint                  not null comment '订单id',
    remark   varchar(200) default '' null comment '备注'
);

create table `spring_boot_demo_1`.t_order_1
(
    id       bigint auto_increment comment '主键'
        primary key,
    user_id  bigint                  not null comment '用户id',
    order_id bigint                  not null comment '订单id',
    remark   varchar(200) default '' null comment '备注'
);

create table `spring_boot_demo_1`.t_order_2
(
    id       bigint auto_increment comment '主键'
        primary key,
    user_id  bigint                  not null comment '用户id',
    order_id bigint                  not null comment '订单id',
    remark   varchar(200) default '' null comment '备注'
);

create table `spring_boot_demo_2`.t_order_0
(
    id       bigint auto_increment comment '主键'
        primary key,
    user_id  bigint                  not null comment '用户id',
    order_id bigint                  not null comment '订单id',
    remark   varchar(200) default '' null comment '备注'
);

create table `spring_boot_demo_2`.t_order_1
(
    id       bigint auto_increment comment '主键'
        primary key,
    user_id  bigint                  not null comment '用户id',
    order_id bigint                  not null comment '订单id',
    remark   varchar(200) default '' null comment '备注'
);

create table `spring_boot_demo_2`.t_order_2
(
    id       bigint auto_increment comment '主键'
        primary key,
    user_id  bigint                  not null comment '用户id',
    order_id bigint                  not null comment '订单id',
    remark   varchar(200) default '' null comment '备注'
);

2.2 引入依赖

  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter</artifactId>
    </dependency>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>

    <dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>mybatis-plus-boot-starter</artifactId>
      <version>3.1.0</version>
    </dependency>

    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
    </dependency>

    <dependency>
      <groupId>io.shardingsphere</groupId>
      <artifactId>sharding-jdbc-core</artifactId>
      <version>3.1.0</version>
    </dependency>

    <dependency>
      <groupId>cn.hutool</groupId>
      <artifactId>hutool-all</artifactId>
    </dependency>

    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <optional>true</optional>
    </dependency>
  </dependencies>

2.3 启动类

@SpringBootApplication
@EnableTransactionManagement(proxyTargetClass = true)
@MapperScan("com..sharding.jdbc.mapper")
public class SpringBootDemoShardingJdbcApplication {
    public static void main(String[] args) {
        SpringApplication.run(SpringBootDemoShardingJdbcApplication.class, args);
    }
}

2.4 实体类: Order.java

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@TableName(value = "t_order")
public class Order {
    /**
     * 主键
     */
    @TableId(type = IdType.AUTO)
    private Long id;
    /**
     * 用户id
     */
    private Long userId;

    /**
     * 订单id
     */
    private Long orderId;
    /**
     * 备注
     */
    private String remark;
}

2.5 持久层: OrderMapper.java

@Component
public interface OrderMapper extends BaseMapper<Order> {
}

2.6 数据源分片配置: DataSourceShardingConfig.java

@Configuration
public class DataSourceShardingConfig {

    /**
     * 需要手动配置事务管理器
     */
    @Bean
    public DataSourceTransactionManager transactionManager(@Qualifier("dataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "dataSource")
    @Primary
    public DataSource dataSource() throws SQLException {
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();

        // 设置分表策略
        shardingRuleConfig.getTableRuleConfigs().add(orderTableRule());
        // 设置默认数据库
        shardingRuleConfig.setDefaultDataSourceName("ds0");
        // 设置默认表分片策略配置
        shardingRuleConfig.setDefaultTableShardingStrategyConfig(new NoneShardingStrategyConfiguration());
        // 设置默认数据库分片策略配置
        shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new NoneShardingStrategyConfiguration());
        return ShardingDataSourceFactory.createDataSource(dataSourceMap(), shardingRuleConfig, new ConcurrentHashMap<>(16), new Properties());
    }

    /**
     * t_order 分表策略
     * @return
     */
    private TableRuleConfiguration orderTableRule() {
        TableRuleConfiguration tableRule = new TableRuleConfiguration();
        // 设置逻辑表名
        tableRule.setLogicTable("t_order");
        // 设置实际数据节点 ds${0..1}.t_order_${0..2} 也可以写成 ds$->{0..1}.t_order_$->{0..1}
        tableRule.setActualDataNodes("ds${0..1}.t_order_${0..2}");

        // 配置分库 + 分表策略
        tableRule.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "ds${user_id % 2}"));
        tableRule.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order_${order_id % 3}"));

        // 配置主键生成策略
        tableRule.setKeyGenerator(customKeyGenerator());
        tableRule.setKeyGeneratorColumnName("id");
        return tableRule;
    }

    /**
     * 数据源
     * @return
     */
    private Map<String, DataSource> dataSourceMap() {
        Map<String, DataSource> dataSourceMap = new HashMap<>(16);

        // 配置第一个数据源
        HikariDataSource ds0 = new HikariDataSource();
        ds0.setDriverClassName("com.mysql.cj.jdbc.Driver");
        ds0.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/spring-boot-demo-1?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8");
        ds0.setUsername("root");
        ds0.setPassword("123456");

        // 配置第二个数据源
        HikariDataSource ds1 = new HikariDataSource();
        ds1.setDriverClassName("com.mysql.cj.jdbc.Driver");
        ds1.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/spring-boot-demo-2?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8");
        ds1.setUsername("root");
        ds1.setPassword("123456");

        dataSourceMap.put("ds0", ds0);
        dataSourceMap.put("ds1", ds1);
        return dataSourceMap;
    }

    /**
     * 自定义主键生成器
     */
    private KeyGenerator customKeyGenerator() {
        return new CustomSnowflakeKeyGenerator(IdUtil.createSnowflake(1, 1));
    }

}

2.7 测试

@Slf4j
@RunWith(SpringRunner.class)
@SpringBootTest
public class SpringBootDemoShardingJdbcApplicationTests {
    @Autowired
    private OrderMapper orderMapper;

    /**
     * 测试新增
     */
    @Test
    public void testInsert() {
        for (long i = 1; i < 10; i++) {
            for (long j = 1; j < 20; j++) {
                Order order = Order.builder().userId(i).orderId(j).remark(RandomUtil.randomString(20)).build();
                orderMapper.insert(order);
            }
        }
    }

    /**
     * 测试新增
     */
    @Test
    public void testUseQuery() {
        System.out.println(JSONUtil.toJsonStr(userMapper.selectById(1)));
    }

    /**
     * 测试更新
     */
    @Test
    public void testUpdate() {
        Order update = new Order();
        update.setRemark("修改备注信息");
        orderMapper.update(update, Wrappers.<Order>update().lambda().eq(Order::getOrderId, 2).eq(Order::getUserId, 2));
    }

    /**
     * 测试删除
     */
    @Test
    public void testDelete() {
        orderMapper.delete(new QueryWrapper<>());
    }

    /**
     * 测试查询
     */
    @Test
    public void testSelect() {
        List<Order> orders = orderMapper.selectList(Wrappers.<Order>query().lambda().in(Order::getOrderId, 1, 2));
        log.info("【orders】= {}", JSONUtil.toJsonStr(orders));
    }

}