- 官网资料
- 官网
- github
- 集成mybatisplus
- 问题
- 数据库表SQL
- 项目结构
- 父工程POM
- 模块pom
- 配置文件application.yml
- 启动类Main
- 分片规则配置MyShardingRuleConfiguration
- mybatis全局配置MybatisPlusConfig
- domain service mapper
- 最后写个测试TestController
- 测试
官网资料 官网
shardingsphere官网地址https://shardingsphere.apache.org/index_zh.html,这里有各种配置说明和部分示例代码。
如果想要复杂的配置，可以参考官网实现。
源码：https://github.com/apache/shardingsphere
源码中的demo:
不过把这个项目下载下来比较大，如果只想下载examples的话，可以访问：
https://github.com/apache/shardingsphere-example
集成mybatisplus 问题
本来是想使用纯yml的配置方式实现，但是发现这个数据源无法集成到mybatisplus，最后改成数据源和shardingsphere的写一个独立可拔插的@Configuration配置Bean来实现。具体所有源码如下：
数据库表SQLCREATE TABLE `t_order_1` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `site_id` bigint(20) unsigned NOT NULL COMMENT '站点ID', `name` varchar(64) NOT NULL DEFAULT '' COMMENT '订单名称', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='订单表'; CREATE TABLE `t_order_2` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `site_id` bigint(20) unsigned NOT NULL COMMENT '站点ID', `name` varchar(64) NOT NULL DEFAULT '' COMMENT '订单名称', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='订单表';项目结构 父工程POM
模块pom4.0.0 com.dongshan study 1.0-SNAPSHOT sdsp study 学习 pom UTF-8 1.8 1.8 2.3.2.RELEASE Hoxton.SR9 2.2.6.RELEASE 1.1.24 3.3.2 org.springframework.boot spring-boot-dependencies ${spring.boot.version} pom import org.springframework.cloud spring-cloud-dependencies ${spring.cloud.version} pom import com.alibaba.cloud spring-cloud-alibaba-dependencies ${spring.cloud.alibaba.version} pom import com.alibaba druid-spring-boot-starter ${druid.version} com.baomidou mybatis-plus-boot-starter ${mybatis.plus.boot.version} org.apache.maven.plugins maven-surefire-plugin true
配置文件application.ymlstudy com.dongshan 1.0-SNAPSHOT 4.0.0 sdsp shardingsphere分库分表学习 org.springframework.boot spring-boot-starter-test test org.springframework.boot spring-boot-starter-web com.baomidou mybatis-plus-boot-starter com.alibaba druid-spring-boot-starter mysql mysql-connector-java org.springframework.boot spring-boot-starter-jdbc org.projectlombok lombok org.apache.shardingsphere shardingsphere-jdbc-core 5.0.0-alpha org.springframework.boot spring-boot-maven-plugin
# 开发环境配置
server:
# 服务器的HTTP端口，默认为80
port: 80
spring:
application:
name: sdsp #应用名称
datasource: #数据源配置
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://IP:3306/dycrm_coll?useUnicode=true&characterEncoding=utf-8&useSSL=false
username: root
password: root
启动类Main
package com.dongshan.sdsp;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class Main {
public static void main(String[] args) {
SpringApplication.run(Main.class, args);
}
}
分片规则配置MyShardingRuleConfiguration
package com.dongshan.sdsp.config;
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory;
import org.apache.shardingsphere.infra.config.RuleConfiguration;
import org.apache.shardingsphere.infra.config.algorithm.ShardingSphereAlgorithmConfiguration;
import org.apache.shardingsphere.sharding.api.config.ShardingRuleConfiguration;
import org.apache.shardingsphere.sharding.api.config.rule.ShardingTableRuleConfiguration;
import org.apache.shardingsphere.sharding.api.config.strategy.sharding.StandardShardingStrategyConfiguration;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.*;
@Configuration
public class MyShardingRuleConfiguration {
// 数据源属性配置，这样可以直接从配置文件的spring.datasource中获取配置，保持原配置文件不变
@Autowired
private DataSourceProperties dataSourceProperties;
@Bean
public DataSource dataSource() throws SQLException {
// 配置Druid数据源
DruidDataSource ds = new DruidDataSource();
ds.setDriverClassName(dataSourceProperties.getDriverClassName());
ds.setUrl(dataSourceProperties.getUrl());
ds.setUsername(dataSourceProperties.getUsername());
ds.setPassword(dataSourceProperties.getPassword());
// 数据源映射
Map dataSourceMap = new HashMap<>();
dataSourceMap.put("ds", ds);
// 分配规则
Collection rules = Collections.singleton(shardingRuleConfiguration());
// 经过ShardingSphere拦截的数据源
return ShardingSphereDataSourceFactory.createDataSource(dataSourceMap, rules, new Properties());
}
private ShardingRuleConfiguration shardingRuleConfiguration() {
// 配置t_order表规则 按照站点分表，站点ID只能确定为100个左右，此处为了兼容性更强，配置10000个表
ShardingTableRuleConfiguration orderTableRuleConfig = new ShardingTableRuleConfiguration("t_order", "ds.t_order_$->{0..9999}");
// 配置分库策略，不做分库，只做分表
//orderTableRuleConfig.setDatabaseShardingStrategy(new StandardShardingStrategyConfiguration("column_id", "dbShardingAlgorithm"));
// 配置分库算法
// Properties orderDbShardingAlgorithmrProps = new Properties();
// dbShardingAlgorithmrProps.setProperty("algorithm-expression", "ds${column_id % 2}");
// 配置分表策略
orderTableRuleConfig.setTableShardingStrategy(new StandardShardingStrategyConfiguration("site_id", "tableShardingAlgorithm"));
// 配置分表算法
Properties orderTableShardingAlgorithmrProps = new Properties();
orderTableShardingAlgorithmrProps.setProperty("algorithm-expression", "t_order_${site_id % 10000}");
// 配置分片规则
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getTables().add(orderTableRuleConfig);
// shardingRuleConfig.getShardingAlgorithms().put("dbShardingAlgorithm", new ShardingSphereAlgorithmConfiguration("INLINE", dbShardingAlgorithmrProps));
shardingRuleConfig.getShardingAlgorithms().put("tableShardingAlgorithm", new ShardingSphereAlgorithmConfiguration("INLINE", orderTableShardingAlgorithmrProps));
return shardingRuleConfig;
}
}
mybatis全局配置MybatisPlusConfig
package com.dongshan.sdsp.config;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.pagination.optimize.JsqlParserCountOptimize;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@MapperScan("com.dongshan.sdsp.mapper")
@Configuration
public class MybatisPlusConfig {
@Bean
public PaginationInterceptor paginationInterceptor() {
PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
// 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求 默认false
// paginationInterceptor.setOverflow(false);
// 设置最大单页限制数量，默认 500 条，-1 不受限制
// paginationInterceptor.setLimit(500);
// 开启 count 的 join 优化,只针对部分 left join
paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
return paginationInterceptor;
}
}
domain service mapper
这些是通过idea的Mybatisx插件生成
package com.dongshan.sdsp.controller;
import com.dongshan.sdsp.domain.TOrder;
import com.dongshan.sdsp.service.TOrderService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
@RestController
@RequestMapping("/order")
public class TestController {
@Autowired
TOrderService tOrderService ;
@GetMapping("/save/{siteId}")
public Boolean save(@PathVariable("siteId") Long siteId) {
TOrder order = new TOrder() ;
order.setSiteId(siteId);
order.setName("订单分表测试"+siteId);
boolean ok = tOrderService.save(order) ;
return ok ;
}
}
测试
-
浏览器访问http://localhost/order/save/1
-
查看数据库
