1.項目結(jié)構(gòu)
主要結(jié)構(gòu)
Springboot 2.7.5
Redis
MyBatisPlus 3.5.3.1
MyBatisPlusJoin 1.4.5
ShardingSphere 5.1.2
2.maven引入
特別注意： springboot 2.7.5 和shardingsphere 5.1.2
?(1)springboot版本
<parent>?
? ? <groupId>org.springframework.boot</groupId>?
? ? <artifactId>spring-boot-starter-parent</artifactId>?
? ? <version>2.7.5</version>?
</parent>
?(2)maven插件
<!-- 開始 shardingsphere 5.1.2版本分庫分表? -->?
<dependency>?
? ? <groupId>org.apache.shardingsphere</groupId>?
? ? <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>?
? ? <version>5.1.2</version>?
</dependency>?
<dependency>?
? ? <groupId>org.springframework.boot</groupId>?
? ? <artifactId>spring-boot-starter-jdbc</artifactId>?
</dependency>?
<dependency>?
? ? <groupId>org.springframework.boot</groupId>?
? ? <artifactId>spring-boot-configuration-processor</artifactId>
? ? <optional>true</optional>?
</dependency>?
<dependency>?
? ? <groupId>com.baomidou</groupId>?
? ? <artifactId>dynamic-datasource-spring-boot-starter</artifactId>?
? ? <version>3.3.2</version>?
</dependency>?
<dependency>?
? ? <groupId>org.springframework.boot</groupId>?
? ? <artifactId>spring-boot-starter</artifactId>?
? ? <!-- 由于Mybatis Plus使用了druid作為默認的數(shù)據(jù)源，分庫分表使用的是HikariDataSource作為數(shù)據(jù)源管理，所以需要排除druid的starter -->?
? ? <exclusions>?
? ? ? ? <exclusion>? ? ? ? ?
? ? ? ? <groupId>com.alibaba</groupId>?
? ? ? ? ? ? <artifactId>druid-spring-boot-starter</artifactId>?
? ? ? ? </exclusion>?
? ? </exclusions>
</dependency>?
<!-- 結(jié)束 shardingsphere 5.1.2版本分庫分表? -->
... 其他內(nèi)容
?3. yml文件配置
*? 放的獨立的環(huán)境配置文件中，如test,prod等
* 如 application-test.yml
# 開發(fā)環(huán)境?
spring:?
? # redis配置?
? redis:?
? ? host: xxx.xxx.xxx.xxx?
? ? port: 6379?
? ? timeout: 120000 # 連接超時時間（毫秒）?
? ? password: xxx #密碼?
? ? database: 0 #Redis數(shù)據(jù)庫索引0-255其中一個，默認0?
? ? jedis:?
? ? ? pool:?
? ? ? ? max-active: 50? #連接池最大連接數(shù)（使用負值表示沒有限制）?
? ? ? ? max-idle: 50? # 連接池中的最大空閑連接?
? ? ? ? min-idle: 10? # 連接池中的最小空閑連接?
? ? ? ? max-wait: 2000 # 連接池最大阻塞等待時間（使用負值表示沒有限制）?
? ? ? ? time-between-eviction-runs: 60000?
? # 分表數(shù)據(jù)庫配置?
? shardingsphere:?
? ? enabled: true?
? ? props:?
? ? ? sql-show: true?
? ? datasource:?
? ? ? names: master?
? ? ? master:?
? ? ? ? type: com.zaxxer.hikari.HikariDataSource?
? ? ? ? driver-class-name: com.mysql.cj.jdbc.Driver?
? ? ? ? jdbcUrl: jdbc:mysql://xxx.xxx.xxx.xxx:3306/test_db?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8?
? ? ? ? url: jdbc:mysql://xxx.xxx.xxx.xxx:3306/test_db?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8?
? ? ? ? username: root?
? ? ? ? password: root?
? ? rules:?
? ? ? sharding:?
? ? ? ? sharding-algorithms:?
? ? ? ? ? # 自定義分庫算法-此處為單庫不需要分庫?
? ? ? ? ? db-strategy-algorithms-default:?
? ? ? ? ? ? type: INLINE?
? ? ? ? ? ? props:?
? ? ? ? ? ? ? algorithm-expression: master?
? ? ? ? ? # 自定義分表算法-按照月份分表?
? ? ? ? ? table-strategy-algorithms-month:?
? ? ? ? ? ? type: INTERVAL?
? ? ? ? ? ? props:?
? ? ? ? ? ? ? datetime-pattern: yyyy-MM-dd HH:mm:ss?
? ? ? ? ? ? ? datetime-lower: 2023-01-01 00:00:00?
? ? ? ? ? ? ? datetime-upper: 2030-12-31 23:59:59?
? ? ? ? ? ? ? sharding-suffix-pattern: yyyyMM? # 月份作為后綴?
? ? ? ? ? ? ? datetime-interval-amount: 1?
? ? ? ? ? ? ? datetime-interval-unit: MONTHS?
? ? ? ? tables:?
? # t_order 表分片規(guī)則?
? t_order:?
? ? actual-data-nodes: master.t_order_$->{2024..2030}0$->{1..9},master.t_order_$->{2024..2030}1$->{0..2}?
? ? database-strategy:?
? ? ? standard:?
? ? ? ? sharding-column: id?
? ? ? ? sharding-algorithm-name: db-strategy-algorithms-default?
? ? table-strategy:?
? ? ? standard:?
? ? ? ? sharding-column: work_date?
? ? ? ? sharding-algorithm-name: table-strategy-algorithms-month?
? ? key-generate-strategy:?
? ? ? column: id?
? ? ? key-generator-name: my-snowflake
? ? broadcast-tables:?
? ? ? - t_order?
key-generators:?
? my-snowflake:?
? ? type: SNOWFLAKE
?4.數(shù)據(jù)源配置
package com.expamle.admin.config;?
import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;?
import com.baomidou.dynamic.datasource.creator.DataSourceCreator;?
import com.baomidou.dynamic.datasource.creator.DefaultDataSourceCreator;?
import com.baomidou.dynamic.datasource.provider.AbstractDataSourceProvider;?
import com.baomidou.dynamic.datasource.provider.DynamicDataSourceProvider;?
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceAutoConfiguration;?
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;?
import org.apache.shardingsphere.driver.jdbc.adapter.AbstractDataSourceAdapter;?
import org.springframework.beans.factory.annotation.Value;?
import org.springframework.boot.SpringBootConfiguration;?
import org.springframework.boot.autoconfigure.AutoConfigureBefore;?
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;?
import org.springframework.boot.context.properties.EnableConfigurationProperties;?
import org.springframework.context.annotation.Bean;?
import org.springframework.context.annotation.Configuration;?
import org.springframework.context.annotation.Lazy;?
import org.springframework.context.annotation.Primary;?
import javax.annotation.Resource;?
import javax.sql.DataSource;?
import java.util.Arrays;?
import java.util.HashMap;?
import java.util.List;?
import java.util.Map;?
/**?
* shardingSphere 分庫分表插件引入后所需的配置內(nèi)容 */@Configuration?
@AutoConfigureBefore({DynamicDataSourceAutoConfiguration.class, SpringBootConfiguration.class})?
@EnableConfigurationProperties(DynamicDataSourceProperties.class)?
public class ShardingSphereDataSourceConfig {?
? ? @Resource?
? ? private DynamicDataSourceProperties dynamicDataSourceProperties;?
? ? @Lazy?
? ? @Resource?
? ? private AbstractDataSourceAdapter shardingSphereDataSource;?
? ? @Value("${spring.shardingsphere.datasource.names}")?
? ? private String dataSourceNames;?
? ? @Primary?
? ? @Bean?
? ? @ConditionalOnMissingBean?
? ? public DefaultDataSourceCreator defaultDataSourceCreator(List<DataSourceCreator> dataSourceCreators) {?
? ? ? ? DefaultDataSourceCreator defaultDataSourceCreator = new DefaultDataSourceCreator();?
? ? ? ? defaultDataSourceCreator.setCreators(dataSourceCreators);?
? ? ? ? defaultDataSourceCreator.setProperties(dynamicDataSourceProperties);?
? ? ? ? return defaultDataSourceCreator;?
? ? }?
? ? @Bean?
? ? public DynamicDataSourceProvider dynamicDataSourceProvider() {?
? ? ? ? String[] names = dataSourceNames.split(",");?
? ? ? ? return new AbstractDataSourceProvider() {?
? ? ? ? ? ? @Override?
? ? ? ? ? ? public Map<String, DataSource> loadDataSources() {?
? ? ? ? ? ? ? ? Map<String, DataSource> dataSourceMap = new HashMap<>();?
? ? ? ? ? ? ? ? Arrays.stream(names).forEach(name -> dataSourceMap.put(name, shardingSphereDataSource));?
? ? ? ? ? ? ? ? return dataSourceMap;?
? ? ? ? ? ? }?
? ? ? ? };?
? ? }?
? ? @Primary?
? ? @Bean?
? ? public DataSource dataSource(DynamicDataSourceProvider dynamicDataSourceProvider) {?
? ? ? ? DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();?
? ? ? ? dataSource.setPrimary(dynamicDataSourceProperties.getPrimary());?
? ? ? ? dataSource.setStrict(dynamicDataSourceProperties.getStrict());?
? ? ? ? dataSource.setStrategy(dynamicDataSourceProperties.getStrategy());?
? ? ? ? dataSource.setProvider(dynamicDataSourceProvider);?
? ? ? ? dataSource.setP6spy(dynamicDataSourceProperties.getP6spy());?
? ? ? ? dataSource.setSeata(dynamicDataSourceProperties.getSeata());?
? ? ? ? return dataSource;?
? ? }?
}
?5.MybatisPlusConfig
package com.expamle.admin.config;?
import com.baomidou.mybatisplus.annotation.DbType;?
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;?
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;?
import org.springframework.context.annotation.Bean;?
import org.springframework.context.annotation.Configuration;?
import org.springframework.transaction.annotation.EnableTransactionManagement;?
import java.util.Collections;?
@EnableTransactionManagement?
@Configuration?
public class MybatisPlusConfig {?
? ? /**?
? ? * 分頁插件 3.5.X? ? *? ? * @author zhengkai.blog.csdn.net?
? ? */? ? @Bean?
? ? public PaginationInnerInterceptor paginationInnerInterceptor() {?
? ? ? ? PaginationInnerInterceptor paginationInterceptor = new PaginationInnerInterceptor();?
? ? ? ? // 設(shè)置最大單頁限制數(shù)量，默認 500 條，-1 不受限制?
? ? ? ? paginationInterceptor.setMaxLimit(-1L);?
? ? ? ? paginationInterceptor.setDbType(DbType.MYSQL);?
? ? ? ? // 開啟 count 的 join 優(yōu)化,只針對部分 left join?
? ? ? ? paginationInterceptor.setOptimizeJoin(true);?
? ? ? ? return paginationInterceptor;?
? ? }?
? ? @Bean?
? ? public MybatisPlusInterceptor mybatisPlusInterceptor() {?
? ? ? ? MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();?
? ? ? ? mybatisPlusInterceptor.setInterceptors(Collections.singletonList(paginationInnerInterceptor()));?
? ? ? ? return mybatisPlusInterceptor;?
? ? }?
}
?6.注意啟動類
@EnableAsync?
@SpringBootApplication(scanBasePackages = {"com.expamle.admin", "com.expamle.common"})?
@MapperScan("com.expamle.admin.sys.dao")?
public class AdminApplication {?
? ? public static void main(String[] args) {?
? ? ? ? SpringApplication.run(WhzzcgAdminApplication.class, args);?
? ? }?
}
?7.特別注意
1.在使用crate_time等日期數(shù)據(jù)生成分表建時，傳入查詢的格式一定要滿足 yyyy-MM-dd HH:mm:ss 格式才行，否則將報錯
2.如果在項目中已有了主表且數(shù)據(jù)非常多，則需要手動生成對應子表并將對應數(shù)據(jù)導入到子表，否則聯(lián)合查詢將會報錯
