分库分表 springboot+dubbo+mybatisPlus+shardingSphere

1、使用的框架有

springboot 2.1.1 + apache dubbo 2.7.2+ mybatisPlus 3.1.0 +shardingSphere 4.1.0

数据库连接池：HikariDataSource

jdbc驱动：mysql-connector-java-6.0.6.jar

2、分库分表方案：

• 分库：根据create_time字段切换不同的数据源big-data或者big-data-2021

• 分表：根据school_id字段取模，数据保存到student_analysis_0~5的表中

建表SQL语句：

CREATE TABLE `student_analysis_0` (`ID` bigint(20) NOT NULL,`SCHOOL_ID` bigint(20) NOT NULL COMMENT '学校id',`CREATE_TIME` datetime NOT NULL COMMENT '创建时间',`UPDATE_TIME` datetime NOT NULL COMMENT '更新时间',`CREATE_USER_ID` bigint(20) NOT NULL COMMENT '创建用户主键',`UPDATE_USER_ID` bigint(20) NOT NULL COMMENT '更新用户主键',`STATUS` int(1) NOT NULL COMMENT '状态 -1:删除,0:停用，1-启用',PRIMARY KEY (`ID`)) ;

3、maven依赖 pom.xml文件：

<!-- Dubbo Spring Boot Starter --><dependency><groupId>${dubbo.groupId}</groupId><artifactId>dubbo-spring-boot-starter</artifactId></dependency><dependency><groupId>${dubbo.groupId}</groupId><artifactId>dubbo</artifactId></dependency><dependency><groupId>org.apache.zookeeper</groupId><artifactId>zookeeper</artifactId></dependency><dependency><groupId>${dubbo.groupId}</groupId><artifactId>dubbo-configcenter-zookeeper</artifactId><exclusions><exclusion><artifactId>objenesis</artifactId><groupId>org.objenesis</groupId></exclusion></exclusions></dependency><dependency><groupId>${dubbo.groupId}</groupId><artifactId>dubbo-rpc-rest</artifactId><exclusions><exclusion><artifactId>jetty-server</artifactId><groupId>org.eclipse.jetty</groupId></exclusion><exclusion><artifactId>jetty-servlet</artifactId><groupId>org.eclipse.jetty</groupId></exclusion></exclusions></dependency><dependency><groupId>org.apache.tomcat.embed</groupId><artifactId>tomcat-embed-logging-juli</artifactId></dependency><dependency><groupId>javax.el</groupId><artifactId>javax.el-api</artifactId><version>3.0.0</version></dependency><dependency><groupId>org.glassfish.web</groupId><artifactId>javax.el</artifactId><version>2.2.6</version><exclusions><exclusion><artifactId>javax.el-api</artifactId><groupId>javax.el</groupId></exclusion></exclusions></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-log4j2</artifactId><version>2.1.4.RELEASE</version></dependency><!-- MyBatis Plus --><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><version>6.0.6</version></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><version>2.1.5.RELEASE</version><scope>test</scope></dependency><!--sharding jdbc springboot--><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-spring-boot-starter</artifactId><version>4.1.0</version><!--<version>4.0.0-RC2</version>--></dependency><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-spring-namespace</artifactId><version>4.1.0</version><!--<version>4.0.0-RC2</version>--></dependency>

4、application.yml配置：

spring:shardingsphere:datasource:# 数据库名称，多个以逗号隔开names: ds2020,ds2021ds2020:type: com.zaxxer.hikari.HikariDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverjdbc-url: jdbc:mysql://${database.mysql.ip}:${database.mysql.port}/big-data?useUnicode=true&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai&useSSL=false&nullNamePatternMatchesAll=trueusername: ${database.mysql.username}password: ${database.mysql.password}minimum-idle: 1maximum-pool-size: 5connection-test-query: SELECT 1connection-timeout: 6000ds2021:type: com.zaxxer.hikari.HikariDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverjdbc-url: jdbc:mysql://${database.mysql.ip}:${database.mysql.port}/big-data-2021?useUnicode=true&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai&useSSL=false&nullNamePatternMatchesAll=trueusername: ${database.mysql.username}password: ${database.mysql.password}minimum-idle: 1maximum-pool-size: 5connection-test-query: SELECT 1connection-timeout: 6000sharding:#默认库default-data-source-name: ds2020tables:student_analysis:#物理表的结点,下面代表的是ds2020.student_analysis_0..10、ds2021.student_analysis_0..1actual-data-nodes: ds$->{2020..2021}.student_analysis_$->{0..4}#分库策略，按照创建时间的年份分库，如果不用分库的，直接注释掉分库相关的代码database-strategy:standard:sharding-column: create_timeprecise-algorithm-class-name: com.auge.big.data.sharding.CreateTimeShardingDatabaseAlgorithmtable-strategy:#分表策略，根据school_id字段的值模10inline:sharding-column: school_idalgorithm-expression: student_analysis_$->{school_id % 5}props:#是否打印逻辑SQL语句和实际SQL语句，建议调试时打印，在生产环境关闭sql:show: true

5、分库策略自定义算法类

/*** 分库策略自定义算法。*/ public class CreateTimeShardingDatabaseAlgorithm implements PreciseShardingAlgorithm<Date> {/*** 按创建时间分库*/@Overridepublic String doSharding(Collection<String> collection, PreciseShardingValue<Date> preciseShardingValue) {Date value = preciseShardingValue.getValue();SimpleDateFormat sdf = new SimpleDateFormat("yyyy");String dataSource = "ds" + sdf.format(value);System.out.println("switch datasource:" + dataSource);return dataSource;} }

总结

以上是生活随笔为你收集整理的分库分表 springboot+dubbo+mybatisPlus+shardingSphere的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。