3、ShardingSphere 之 Sharding-JDBC 实现水平分库

文章目录

• • 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 实现水平分库的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。