博客

1.配置Maven依赖

<!--shardingsphere分表策略-->
<dependency>
    <groupId>io.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>3.0.0.M2</version>
</dependency>
<dependency>
    <groupId>io.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-namespace</artifactId>
    <version>3.0.0.M2</version>
</dependency>

2.数据库表

数据库要分的表如下所示：后缀以年月分，表结构都一样，每张表只存当月数据

数据库表t_commerce_order如上所示，根据它表名年月分表，分表范围为2000-2099期间中的所有月份，表结构如下所示：

3.yaml配置文件

详情请看官方文档

http://shardingsphere.apache.org/document/legacy/2.x/cn/02-guide/configuration/

这里DB0为数据源名，只配置了一个数据库源DB0，其他库一样配置：

yaml配置文件进行数据库配置，并对指定表t_commerce_order制定分表策略

dataSources:
  DB0: !!com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/newbee?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&useAffectedRows=true
    username: root
    password: 123456
​
shardingRule:
  tables:
    t_product:
      actualDataNodes: DB0.t_product
​
    t_user:
      actualDataNodes: DB0.t_user
​
    t_commerce_order:
      actualDataNodes: DB0.t_commerce_order_$->{2000..2099}0$->{1..9},DB0.t_commerce_order_$->{2000..2099}1$->{0..2}
      tableStrategy:
        inline:
          shardingColumn: order_no
          algorithmExpression: t_commerce_order_$->{order_no[0..5]}
​
props:
  sql.show: true

4.加载配置文件yaml

@Configuration
@EnableTransactionManagement
@MapperScan("com.vivo.internet.e.commence.dao.mapper")
public class MybatisConfig {
    @Bean
    public DataSource dataSource() throws SQLException, IOException {
        //得到配置sharding-jdbc配置文件路径
        String path = MybatisConfig.class.getClassLoader().getResource("config/sharding-jdbc.yaml").getPath();
        //数据源设置
        File yamlFile = new File(path);
        DataSource datasource = YamlShardingDataSourceFactory.createDataSource(yamlFile);
        return datasource;
    }
    @Bean
    public SqlSessionFactoryBean sqlSessionFactory(DataSource dataSource) throws IOException {
        SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();
        sqlSessionFactory.setDataSource(dataSource);
        PathMatchingResourcePatternResolver pmrpr = new PathMatchingResourcePatternResolver();
        //mapper扫描地址
        String mapperPattern = "META-INF/mybatis/mapper/*.xml";
        Resource[] configResource = pmrpr.getResources(mapperPattern);
        sqlSessionFactory.setMapperLocations(configResource);
        sqlSessionFactory.setConfigLocation(new ClassPathResource("config/mybatis-config.xml"));
        return sqlSessionFactory;
    }
}

5.查询结果

根据t_commerce_order表中的orderNo字段进行查询，根据分表策略，对这个字段前6位的年月值yyyymm进入对应表进行查询，其他操作也一样，只需要保证分表策略中的表和它对应的列符合规则即可