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.數(shù)據(jù)庫表

數(shù)據(jù)庫要分的表如下所示：后綴以年月分，表結(jié)構(gòu)都一樣，每張表只存當(dāng)月數(shù)據(jù)

?

數(shù)據(jù)庫表t_commerce_order如上所示，根據(jù)它表名年月分表，分表范圍為2000-2099期間中的所有月份，表結(jié)構(gòu)如下所示：

?

3.yaml配置文件

詳情請看官方文檔http://shardingsphere.apache.org/document/legacy/2.x/cn/02-guide/configuration/

這里DB0為數(shù)據(jù)源名，只配置了一個數(shù)據(jù)庫源DB0，其他庫一樣配置：

yaml配置文件進(jìn)行數(shù)據(jù)庫配置，并對指定表t_commerce_order制定分表策略

dataSources:DB0: !!com.alibaba.druid.pool.DruidDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3306/newbee?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF-8&amp;useAffectedRows=trueusername: rootpassword: 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_noalgorithmExpression: 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 {@Beanpublic DataSource dataSource() throws SQLException, IOException {//得到配置sharding-jdbc配置文件路徑String path = MybatisConfig.class.getClassLoader().getResource("config/sharding-jdbc.yaml").getPath();//數(shù)據(jù)源設(shè)置File yamlFile = new File(path);DataSource datasource = YamlShardingDataSourceFactory.createDataSource(yamlFile);return datasource;}@Beanpublic 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.查詢結(jié)果

根據(jù)t_commerce_order表中的orderNo字段進(jìn)行查詢，根據(jù)分表策略，對這個字段前6位的年月值yyyymm進(jìn)入對應(yīng)表進(jìn)行查詢，其他操作也一樣，只需要保證分表策略中的表和它對應(yīng)的列符合規(guī)則即可

?

總結(jié)

以上是生活随笔為你收集整理的sharding jdbc根据年月分表的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。