shardingjdbc分库分表实战

ShardingSphere是一款开源的分布式数据库中间件，它的前身是ShardingJDBC。ShardingSphere提供了分库分表、读写分离、分布式事务等功能，支持的数据库包括MySQL、Oracle、SQL Server等。

下面将介绍ShardingSphere的分库分表实战：

1. 引入依赖

在pom.xml文件中引入ShardingSphere的相关依赖：

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-core</artifactId>
    <version>5.0.0-alpha</version>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>5.0.0-alpha</version>
</dependency>

2. 配置数据源和分片规则

在application.yml文件中配置数据源和分片规则：

spring:
  datasource:
    name: ds
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false
    username: root
    password: root

sharding:
  tables:
    user:
      actualDataNodes: ds.user${0..1}
      tableStrategy:
        inline:
          shardingColumn: id
          algorithmExpression: user${id % 2}
      keyGenerateStrategy:
        column: id
        keyGeneratorName: snowflake
    order:
      actualDataNodes: ds.order${0..1}
      tableStrategy:
        inline:
          shardingColumn: id
          algorithmExpression: order${id % 2}
      keyGenerateStrategy:
        column: id
        keyGeneratorName: snowflake

  default-key-generator:
    type: SNOWFLAKE
    column: id

上述配置中，我们定义了两个表user和order，分别分成两个库，每个库有两张表，使用id字段来进行分片。其中，key-generator用于生成分布式唯一ID，这里使用的是snowflake算法。

3. 配置数据源和事务管理器

在SpringBoot的启动类中配置数据源和事务管理器：

@SpringBootApplication
@MapperScan("com.example.mapper")
@EnableTransactionManagement
@Import(ShardingDataSourceAutoConfiguration.class)
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

    @Bean
    public DataSource dataSource() throws SQLException {
        return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), createShardingRuleConfiguration(), new Properties());
    }

    private Map<String, DataSource> createDataSourceMap() {
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false");
        dataSource.setUsername("root");
        dataSource.setPassword("root");
        dataSourceMap.put("ds", dataSource);
        return dataSourceMap;
    }

    private ShardingRuleConfiguration createShardingRuleConfiguration() {
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(getUserTableRuleConfiguration());
        shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration());
        return shardingRuleConfig;
    }

    private TableRuleConfiguration getUserTableRuleConfiguration() {
        TableRuleConfiguration result = new TableRuleConfiguration("user", "ds.user${0..1}");
        result.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "user${id % 2}"));
        result.setKeyGenerateStrategyConfig(new KeyGenerateStrategyConfiguration("id", "snowflake"));
        return result;
    }

    private TableRuleConfiguration getOrderTableRuleConfiguration() {
        TableRuleConfiguration result = new TableRuleConfiguration("order", "ds.order${0..1}");
        result.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "order${id % 2}"));
        result.setKeyGenerateStrategyConfig(new KeyGenerateStrategyConfiguration("id", "snowflake"));
        return result;
    }

    @Bean
    public PlatformTransactionManager txManager(final DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

4. 编写业务代码

在业务代码中，我们可以使用分片后的数据源来进行CRUD操作，例如：

@Service
@Transactional
public class UserService {

    @Autowired
    private UserMapper userMapper;

    public Long insert(User user) {
        userMapper.insert(user);
        return user.getId();
    }

    public void delete(Long id) {
        userMapper.deleteByPrimaryKey(id);
    }

    public User select(Long id) {
        return userMapper.selectByPrimaryKey(id);
    }

    public void update(User user) {
        userMapper.updateByPrimaryKey(user);
    }
}

在这个例子中，我们使用了@Transactional注解来开启事务，使用了UserMapper来进行CRUD操作。

以上就是ShardingSphere分库分表的实战介绍。