1、使用的框架有

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

數(shù)據(jù)庫連接池：HikariDataSource

jdbc驅(qū)動：mysql-connector-java-6.0.6.jar

2、分庫分表方案：

• 分庫：根據(jù)create_time字段切換不同的數(shù)據(jù)源big-data或者big-data-2021

• 分表：根據(jù)school_id字段取模，數(shù)據(jù)保存到student_analysis_0~5的表中

建表SQL語句：

CREATE TABLE `student_analysis_0` (`ID` bigint(20) NOT NULL,`SCHOOL_ID` bigint(20) NOT NULL COMMENT '學(xué)校id',`CREATE_TIME` datetime NOT NULL COMMENT '創(chuàng)建時(shí)間',`UPDATE_TIME` datetime NOT NULL COMMENT '更新時(shí)間',`CREATE_USER_ID` bigint(20) NOT NULL COMMENT '創(chuàng)建用戶主鍵',`UPDATE_USER_ID` bigint(20) NOT NULL COMMENT '更新用戶主鍵',`STATUS` int(1) NOT NULL COMMENT '狀態(tài) -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:# 數(shù)據(jù)庫名稱，多個(gè)以逗號隔開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:#默認(rèn)庫default-data-source-name: ds2020tables:student_analysis:#物理表的結(jié)點(diǎn),下面代表的是ds2020.student_analysis_0..10、ds2021.student_analysis_0..1actual-data-nodes: ds$->{2020..2021}.student_analysis_$->{0..4}#分庫策略，按照創(chuàng)建時(shí)間的年份分庫，如果不用分庫的，直接注釋掉分庫相關(guān)的代碼database-strategy:standard:sharding-column: create_timeprecise-algorithm-class-name: com.auge.big.data.sharding.CreateTimeShardingDatabaseAlgorithmtable-strategy:#分表策略，根據(jù)school_id字段的值模10inline:sharding-column: school_idalgorithm-expression: student_analysis_$->{school_id % 5}props:#是否打印邏輯SQL語句和實(shí)際SQL語句，建議調(diào)試時(shí)打印，在生產(chǎn)環(huán)境關(guān)閉sql:show: true

5、分庫策略自定義算法類

/*** 分庫策略自定義算法。*/ public class CreateTimeShardingDatabaseAlgorithm implements PreciseShardingAlgorithm<Date> {/*** 按創(chuàng)建時(shí)間分庫*/@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;} }

總結(jié)

以上是生活随笔為你收集整理的分库分表 springboot+dubbo+mybatisPlus+shardingSphere的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

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