1 简介
Apache ShardingSphere 是一款分布式的数据库生态系统， 可以将任意数据库转换为分布式数据库，并通过数据分片、弹性伸缩、加密等能力对原有数据库进行增强。
官方网站
https://shardingsphere.apache.org/document/current/cn/overview/
2 创建数据库和表
# 创建数据库
CREATE DATABASE mytest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;# 创建表
CREATE TABLE article_1 (id BIGINT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(50) NOT NULL,summary VARCHAR(300) NOT NULL,keywords VARCHAR(100) NOT NULL,create_time timestamp not null default CURRENT_TIMESTAMP COMMENT '创建时间',update_time timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=INNODB DEFAULT CHARSET=utf8;# 创建表
CREATE TABLE article_2 (id BIGINT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(50) NOT NULL,summary VARCHAR(300) NOT NULL,keywords VARCHAR(100) NOT NULL,create_time timestamp not null default CURRENT_TIMESTAMP COMMENT '创建时间',update_time timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=INNODB DEFAULT CHARSET=utf8;
3 工程目录
[
4 源代码
注意的问题：
（1） 不要使用druid-spring-boot-starter自动装配，直接使用“druid”即可；
（2）我用的shardingsphere-jdbc-core-spring-boot-starter版本是5.0.0，测试5.1.0和5.2.0一直报错，不知道什么原因
4.1 pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><groupId>com.mason</groupId><artifactId>subtable</artifactId><version>1.0-SNAPSHOT</version><properties><maven.compiler.source>11</maven.compiler.source><maven.compiler.target>11</maven.compiler.target></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId><version>2.3.12.RELEASE</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.20</version><scope>provided</scope></dependency><!-- Operate the mysql --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.21</version></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.4.2</version></dependency><!-- 引入druid --><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.2.15</version></dependency><!-- druid不要使用下面的自动装配 --><!--<dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-starter</artifactId><version>1.2.4</version></dependency>--><!-- shardingsphere --><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId><version>5.0.0</version></dependency></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId></plugin></plugins></build></project>
4.2 application.yml
注意：配置数据库和表是重点
# 数据库分表spring:application:name: subtableshardingsphere:# 显示sqlprops:sql:show: truedatasource:# 连接多个数据库时，可以设置多个names和datasource# names: db1,db2# 连接单个数据库names: mytestmytest:# 连接数据库url: jdbc:mysql://192.168.108.200:3306/mytest?useUnicode=true&&characterEncoding=utf-8&&useSSL=falseusername: rootpassword: 123456driver-class-name: com.mysql.cj.jdbc.Driver# set druidtype: com.alibaba.druid.pool.DruidDataSourcedruid:initial-size: 5min-idle: 5max-active: 20max-wait: 6000time-between-eviction-runs-millis: 60000min-evictable-idle-time-millis: 30000test-on-borrow: falsetest-on-return: falsepool-prepared-statements: truemax-pool-prepared-statement-per-connection-size: 20# 配置规则rules:# 配置分片sharding:# 设置主键生成策略key-generators:# 自定义主键生成算法名称snowflake:type: SNOWFLAKE# 自定义分片算法sharding-algorithms:# 自定义分片算法名称article-strategy-inline:type: INLINEprops:algorithm-expression: article_$->{id % 2 + 1}# 配置表tables:# 自定义的表名article:# 配置数据库和全部的表# 含有article_1, article_2两个表actual-data-nodes: mytest.article_$->{1..2}# 主键生成策略key-generate-strategy:# 对应article表中自定义的字段column: id# 使用上面自定义的主键生成算法key-generator-name: snowflake# 配置分表策略table-strategy:standard:# 对应article表中自定义的字段sharding-column: id# 使用上面自定义的分片算法sharding-algorithm-name: article-strategy-inline# print logs
mybatis-plus:configuration:log-impl: org.apache.ibatis.logging.stdout.StdOutImpl# 关闭数据库下划线自动转驼峰map-underscore-to-camel-case: false4.3 Article
package com.mason.subtable.entity;import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;@Data
@NoArgsConstructor
@AllArgsConstructor
@TableName("article")
public class Article {private Long id;private String title;private String summary;private String keywords;@TableField("create_time")private String create_time;@TableField("update_time")private String update_time;
}
4.4 ArticleMapper
package com.mason.subtable.mapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.mason.subtable.entity.Article;
import org.apache.ibatis.annotations.Mapper;@Mapper
public interface ArticleMapper extends BaseMapper<Article> {
}4.5 DataService
package com.mason.subtable.service;import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.mason.subtable.entity.Article;
import com.mason.subtable.mapper.ArticleMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import java.util.List;@Service
public class DataService {@Autowiredprivate ArticleMapper articleMapper;// 添加数据public int addMultiData(){// 批量添加数据Article article = new Article();article.setTitle("河南大学");article.setKeywords("河南,大学");article.setSummary("河南大学在开封");this.articleMapper.insert(article);article = new Article();article.setTitle("软件学院");article.setKeywords("河南,大学");article.setSummary("河南大学在开封");this.articleMapper.insert(article);article = new Article();article.setTitle("河南大学");article.setKeywords("软件,学院");article.setSummary("河南大学软件学院");this.articleMapper.insert(article);return 1;}// 获取数据public List<Article> getMultiData(){QueryWrapper<Article> wrapper = new QueryWrapper<>();wrapper.eq("title","河南大学");return this.articleMapper.selectList(wrapper);}}4.6 DataController
package com.mason.subtable.controller;import com.mason.subtable.entity.Article;
import com.mason.subtable.service.DataService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;import java.util.List;@RestController
@RequestMapping("/data")
public class DataController {@Autowiredprivate DataService dataService;@GetMapping("/add")public int addData(){return this.dataService.addMultiData();}@GetMapping("/query")public List<Article> queryData(){return this.dataService.getMultiData();}}4.7 SubtableApplication
package com.mason.subtable;import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;@SpringBootApplication
public class SubtableApplication {public static void main(String[] args) {SpringApplication.run(SubtableApplication.class, args);}
}5 结果截图
在浏览器中输入以下地址；
http://127.0.0.1:8080/data/add
数据会被根据算法插入到article_1和article_2其中一个表中。
