文章目錄

• 1 Sharding-JDBC簡介
• 2 Sharding-JDBC
• 3 Sharding-JDBC實現水平分表
• • 3.1 搭建環境
  • • 3.1.1 總體概覽
    • 3.1.2 創建SpringBoot工程
    • 3.1.3 按照水平分表的方式，創建數據庫和數據庫表
  • 3.2 代碼實現
  • • 3.2.1 創建po實體類
    • 3.2.2 創建mapper 接口
    • 3.2.3 添加到MapperScan掃描
  • 3.3 配置分片策略
  • • 3.4 Test
    • 3.5 Test result

1 Sharding-JDBC簡介

是輕量級的java框架，是增強版的JDBC驅動，

2 Sharding-JDBC

Sharding-JDBC 不是做分庫分表
主要目的是簡化對分庫分表之后相關數據操作
主要做兩個功能 數據分片和讀寫分離

3 Sharding-JDBC實現水平分表

3.1 搭建環境

3.1.1 總體概覽

SpringBoot + MybaitsPlus + Sharding-JDBC + Druid 連接池

3.1.2 創建SpringBoot工程

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.251</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>8.0.15</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><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></dependency></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId></plugin></plugins></build></project>

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

創建數據庫 coursedb

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

創建數據庫表 course1 course2

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

約定規則 如果添加課程id為奇數把數據添加course2，如果添加課程id為偶數把數據添加到course1

3.2 代碼實現

3.2.1 創建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;}@Overridepublic String toString() {return "Course{" +"cid=" + cid +", cname='" + cname + '\'' +", userId=" + userId +", cstatus='" + cstatus + '\'' +'}';} }

3.2.2 創建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> {}

3.2.3 添加到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);} }

3.3 配置分片策略

在application.properties文件中增加如下配置

# sharding-JDBC分片策略 # 配置數據源，給數據源命名 spring.shardingsphere.datasource.names=ds1 # 配置數據源具體內容，連接池、驅動、地址、用戶名和密碼 spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/coursedb?serverTimezone=GMT%2B8 spring.shardingsphere.datasource.ds1.username=root spring.shardingsphere.datasource.ds1.password=chengwen# 一個實體類對應兩張表，覆蓋 spring.main.allow-bean-definition-overriding=true# 指定數據表course分布情況，配置表在哪個數據庫里面，表名稱都是什么 spring.shardingsphere.sharding.tables.course.actual-data-nodes=ds1.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}# 打印sql輸出日志 spring.shardingsphere.properties.sql.show=true

3.4 Test

package com.ccb.sharding;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 addCourse() {for (int i = 1 ; i <= 10 ; i ++){Course course = new Course();course.setCname("Java" + i);course.setCstatus("Normal" + i);course.setUserId(100L);courseMapper.insert(course);}}@Testpublic void getCourse(){QueryWrapper queryWrapper = new QueryWrapper();queryWrapper.eq("cid",481545888309706752L);Course course = courseMapper.selectOne(queryWrapper);System.out.println(course);}}

3.5 Test result

分表1

分表2

getCourse

總結

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

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