文章目錄

• • 1.1 按照水平分庫的方式創建數據庫和數據表
  • • 1.1.1 創建分庫1
    • 1.1.2 創建分庫2
    • 1.1.3 創建分庫1中的表1
    • 1.1.4 創建分庫1中的表2
    • 1.1.5 創建分庫2中的表1
    • 1.1.6 創建分庫2中的表2
  • 1.2 創建SpringBoot工程
  • • 1.2.1 pom.xml 如下：
    • 1.2.2 創建po實體類
    • 1.2.3 創建mapper 接口
    • 1.2.4 添加到MapperScan掃描
  • 1.3 配置水平分庫策略
  • 1.4 測試水平分庫
  • 1.5 Test Result
  • • 1.5.1 分庫1表1結果
    • 1.5.2 分庫2表2結果
    • 1.5.3 根據cid和user_id從分庫分表中查詢結果

1.1 按照水平分庫的方式創建數據庫和數據表

1.1.1 創建分庫1

CREATE SCHEMA `edudb1` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;

1.1.2 創建分庫2

CREATE SCHEMA `edudb2` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;

1.1.3 創建分庫1中的表1

CREATE TABLE `edudb1`.`course1` ( `cid` BIGINT NOT NULL, `cname` VARCHAR(45) NOT NULL, `user_id` BIGINT NOT NULL, `cstatus` VARCHAR(45) NOT NULL, PRIMARY KEY (`cid`));

1.1.4 創建分庫1中的表2

CREATE TABLE `edudb1`.`course2` ( `cid` BIGINT NOT NULL, `cname` VARCHAR(45) NOT NULL, `user_id` BIGINT NOT NULL, `cstatus` VARCHAR(45) NOT NULL, PRIMARY KEY (`cid`));

1.1.5 創建分庫2中的表1

CREATE TABLE `edudb2`.`course1` ( `cid` BIGINT NOT NULL, `cname` VARCHAR(45) NOT NULL, `user_id` BIGINT NOT NULL, `cstatus` VARCHAR(45) NOT NULL, PRIMARY KEY (`cid`));

1.1.6 創建分庫2中的表2

CREATE TABLE `edudb2`.`course2` ( `cid` BIGINT NOT NULL, `cname` VARCHAR(45) NOT NULL, `user_id` BIGINT NOT NULL, `cstatus` VARCHAR(45) NOT NULL, PRIMARY KEY (`cid`));

1.2 創建SpringBoot工程

1.2.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 https://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.3.1.RELEASE</version><relativePath/> <!-- lookup parent from repository --></parent><groupId>com.ccb</groupId><artifactId>shardingsphere</artifactId><version>0.0.1-SNAPSHOT</version><name>shardingsphere</name><description>Sharding sphere project for Spring Boot</description><properties><java.version>1.8</java.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId></dependency><dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-starter</artifactId><version>1.1.20</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>5.1.6</version></dependency><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-spring-boot-starter</artifactId><version>4.0.0-RC1</version></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.0.5</version></dependency></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId></plugin></plugins></build></project>

1.2.2 創建po實體類

package com.ccb.sharding.po;public class Course {private Long cid;private String cname;private Long userId;private String cstatus;public Long getCid() {return cid;}public void setCid(Long cid) {this.cid = cid;}public String getCname() {return cname;}public void setCname(String cname) {this.cname = cname;}public Long getUserId() {return userId;}public void setUserId(Long userId) {this.userId = userId;}public String getCstatus() {return cstatus;}public void setCstatus(String cstatus) {this.cstatus = cstatus;}}

1.2.3 創建mapper 接口

package com.ccb.sharding.mapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.ccb.sharding.po.Course; import org.springframework.stereotype.Repository;@Repository public interface CourseMapper extends BaseMapper<Course> { }

1.2.4 添加到MapperScan掃描

package com.ccb.sharding;import org.mybatis.spring.annotation.MapperScan; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication;@SpringBootApplication @MapperScan("com.ccb.sharding.mapper") public class ShardingApplication {public static void main(String[] args) {SpringApplication.run(ShardingApplication.class, args);}}

1.3 配置水平分庫策略

# sharding-JDBC分片策略 # 配置數據源，給數據源命名 # 水平分庫，配置兩個數據源 spring.shardingsphere.datasource.names=ds1,ds2# 配置ds1數據源具體內容，連接池、驅動、地址、用戶名和密碼 spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/edudb1?serverTimezone=GMT%2B8 spring.shardingsphere.datasource.ds1.username=root spring.shardingsphere.datasource.ds1.password=chengwen# 配置ds2數據源具體內容，連接池、驅動、地址、用戶名和密碼 spring.shardingsphere.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.ds2.url=jdbc:mysql://localhost:3306/edudb2?serverTimezone=GMT%2B8 spring.shardingsphere.datasource.ds2.username=root spring.shardingsphere.datasource.ds2.password=chengwen# 一個實體類對應兩張表，覆蓋 spring.main.allow-bean-definition-overriding=true# 指定數據庫分布情況，數據庫里面表分布情況 # ds1 ds2 course1 course2 spring.shardingsphere.sharding.tables.course.actual-data-nodes=ds$->{1..2}.course$->{1..2}# 指定course 表里面主鍵cid 生成策略 SNOWFLAKE 雪花算法 spring.shardingsphere.sharding.tables.course.key-generator.column=cid spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE# 指定表分片策略 約定cid的值偶數添加到course1表，奇數添加到course2表 spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course$->{cid % 2 + 1}# 指定數據庫分片策略 約定user_id 的值是偶數添加到ds1庫，奇數添加到ds2庫 #spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column==user_id #spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{user_id % 2 + 1}spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=user_id spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=ds$->{user_id % 2 + 1} # 打印sql輸出日志 spring.shardingsphere.props.sql.show=true

1.4 測試水平分庫

package com.ccb.sharding;import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.ccb.sharding.mapper.CourseMapper; import com.ccb.sharding.po.Course; import org.junit.jupiter.api.Test; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest;@SpringBootTest class ShardingApplicationTests {@AutowiredCourseMapper courseMapper;// =============== 測試水平分庫 ===============@Testpublic void addCourseDB() {Course course = new Course();course.setCname("Mysql");course.setCstatus("Normal");course.setUserId(100L);courseMapper.insert(course);}@Testpublic void addCourseDBS() {for (int i = 1; i <= 10; i ++){Course course = new Course();course.setCname("Mysql" + i);course.setCstatus("Normal" + i);course.setUserId(100L + i);courseMapper.insert(course);}}@Testpublic void getCourseDB() {QueryWrapper queryWrapper = new QueryWrapper();queryWrapper.eq("cid",482547343917318145L);queryWrapper.eq("user_id",100L);Course course = courseMapper.selectOne(queryWrapper);System.out.println(course);} }

1.5 Test Result

1.5.1 分庫1表1結果

1.5.2 分庫2表2結果

1.5.3 根據cid和user_id從分庫分表中查詢結果

總結

以上是生活随笔為你收集整理的3、ShardingSphere 之 Sharding-JDBC 实现水平分库的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。