1.项目结构
主要结构
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作为默认的数据源，分库分表使用的是HikariDataSource作为数据源管理，所以需要排除druid的starter -->
<exclusions>
<exclusion>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
</exclusion>
</exclusions>
</dependency>
<!-- 结束 shardingsphere 5.1.2版本分库分表 -->
... 其他内容
3. yml文件配置
* 放的独立的环境配置文件中，如test,prod等
* 如 application-test.yml
# 开发环境
spring:
# redis配置
redis:
host: xxx.xxx.xxx.xxx
port: 6379
timeout: 120000 # 连接超时时间（毫秒）
password: xxx #密码
database: 0 #Redis数据库索引0-255其中一个，默认0
jedis:
pool:
max-active: 50 #连接池最大连接数（使用负值表示没有限制）
max-idle: 50 # 连接池中的最大空闲连接
min-idle: 10 # 连接池中的最小空闲连接
max-wait: 2000 # 连接池最大阻塞等待时间（使用负值表示没有限制）
time-between-eviction-runs: 60000
# 分表数据库配置
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 表分片规则
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.数据源配置
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 分库分表插件引入后所需的配置内容 */@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();
// 设置最大单页限制数量，默认 500 条，-1 不受限制
paginationInterceptor.setMaxLimit(-1L);
paginationInterceptor.setDbType(DbType.MYSQL);
// 开启 count 的 join 优化,只针对部分 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等日期数据生成分表建时，传入查询的格式一定要满足 yyyy-MM-dd HH:mm:ss 格式才行，否则将报错
2.如果在项目中已有了主表且数据非常多，则需要手动生成对应子表并将对应数据导入到子表，否则联合查询将会报错
