【shardingsphere】整合springboot2.x + jdk11

一.Sharding-JDBC水平分表

1.数据库表

在这里插入图片描述

2.Maven依赖

<!--        sharding分片  -->
        <!-- 分库分表 -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
        <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-namespace</artifactId>
        <version>4.0.0-RC1</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/javax.xml.bind/jaxb-api -->
        <dependency>
            <groupId>javax.xml.bind</groupId>
            <artifactId>jaxb-api</artifactId>
            <version>2.3.0-b170201.1204</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/javax.activation/activation -->
        <dependency>
            <groupId>javax.activation</groupId>
            <artifactId>activation</artifactId>
            <version>1.1</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.glassfish.jaxb/jaxb-runtime -->
        <dependency>
            <groupId>org.glassfish.jaxb</groupId>
            <artifactId>jaxb-runtime</artifactId>
            <version>2.3.0-b170127.1453</version>
        </dependency>
        <dependency>
            <groupId>dom4j</groupId>
            <artifactId>dom4j</artifactId>
            <version>1.1</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.0.5</version>
        </dependency>

3.yaml配置

spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:
      #数据库名称
      names: saas
      saas:
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://xxxx:3300/course_db?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
        password: root
        type: com.zaxxer.hikari.HikariDataSource
        username: root
    #打印sql
    props:
      sql:
        show: true
    sharding:
      tables:
        #数据分表规则
        #指定所需分的表
        course:
          actual-data-nodes: saas.course_$->{1..2}
          # 使用SNOWFLAKE算法生成主键
          key-generator:
            column: cid
            type: SNOWFLAKE
          table-strategy:
            inline:
              #分表规则
              algorithm-expression: course_$->{cid % 2 + 1}
              #指定主键
              sharding-column: cid

4.实体类

package com.example.sharding.entity;

import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;

@Data
public class Course {
    @TableId
    private Long cid;

    private String cname;

    private Long userId;

    private String cstatus;
}

5. 测试

 /**
     * 添加操作方法测试
     */
    @Test
    public void addCourse() {
        for (int i = 0; i < 100; i++) {
            Course course = new Course();
            course.setCname("springcloud");
            course.setUserId(100L);
            course.setCstatus("Normal");
            courseMapper.insert(course);
        }
    }


    /**
     * 添加操作方法测试
     */
    @Test
    public void findCourse() {
        Course course = courseMapper.selectOne(new QueryWrapper<Course>().eq("cid",1374278112328744962L));
        System.out.println(course);
        }

  • =====================================================================================================
  • =====================================================================================================

二.Sharding-JDBC水平分库分表

在这里插入图片描述
在这里插入图片描述

1.yaml配置

spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:
      #数据库名称
      names: ds0,ds1
      ds0:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://xxx:3300/edu_db_2?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
        password: root
        username: root
      ds1:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://127.0.0.1:3306/edu_db_1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
        password: root
        username: root
    #打印sql
    props:
      sql:
        show: true
    sharding:
      #分表
      tables:
        #数据分表规则
        #指定所需分的数据库和表的分布情况
        course:    #表前缀
          actual-data-nodes: ds$->{0..1}.course_$->{1..2}
          # 使用SNOWFLAKE算法生成主键
          key-generator:
            column: cid
            type: SNOWFLAKE
          table-strategy:
            inline:
              #分表规则
              algorithm-expression: course_$->{cid % 2 + 1}
              #指定主键
              sharding-column: cid
          #分库
          database-strategy:
            inline:
              algorithm-expression: ds$->{user_id % 2}
              sharding-column: user_id

2.测试方法

package com.example.demo3;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.demo3.entity.Course;
import com.example.demo3.mapper.CourseMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class Demo3ApplicationTests {
    @Autowired
    private CourseMapper courseMapper;

    /**
     * 添加操作方法测试
     */
    @Test
    public void addCourse() {
        for (int i = 0; i < 100; i++) {
            Course course = new Course();
            course.setCname("springcloud");
            course.setUserId(100L+i);
            course.setCstatus("Normal"+i);
            courseMapper.insert(course);
        }
    }


    /**
     * 添加操作方法测试
     */
    @Test
    public void findCourse() {
        Course course = courseMapper.selectOne(new QueryWrapper<Course>().eq("cid",1374314159688237057L));
        System.out.println(course);
    }
}

  • =====================================================================================================
  • =====================================================================================================

三.Sharding-JDBC垂直分库

在这里插入图片描述

1.yaml配置

spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:
      #数据库名称
      names: ds0,ds1,ds2
      ds0:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://8.131.119.145:3300/edu_db_2?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
        password: root
        username: root
      ds1:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://127.0.0.1:3306/edu_db_1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
        password: root
        username: root
      ds2:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://127.0.0.1:3306/user_db?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
        password: root
        username: root
    #打印sql
    props:
      sql:
        show: true
    sharding:
      #分表
      tables:
        #数据分表规则
        #指定所需分的数据库和表的分布情况
        t_user: #表前缀
          actual-data-nodes: ds$->{2}.t_user
          # 使用SNOWFLAKE算法生成主键
          key-generator:
            column: user_id
            type: SNOWFLAKE
          table-strategy:
            inline:
              #分表规则(无规则就是这样,专库专表)
              algorithm-expression: t_user
              #指定主键
              sharding-column: user_id

        course:    #表前缀
          actual-data-nodes: ds$->{0..1}.course_$->{1..2}
          # 使用SNOWFLAKE算法生成主键
          key-generator:
            column: cid
            type: SNOWFLAKE
          table-strategy:
            inline:
              #分表规则
              algorithm-expression: course_$->{cid % 2 + 1}
              #指定主键
              sharding-column: cid

          #分库
          database-strategy:
            inline:
              algorithm-expression: ds$->{user_id % 2}
              sharding-column: user_id

2.实体类

@Data
@TableName(value = "t_user")
public class User {
    @TableId
    private Long userId;

    private String username;

    private String ustatus;
}

3.测试类


    /**
     * 添加操作方法测试
     */
    @Test
    public void addUser() {
        //for (int i = 0; i < 100; i++) {
            User user = new User();
            user.setUsername("lucy");
            user.setUstatus("n");
            userMapper.insert(user);
        //}
    }


    /**
     * 添加操作方法测试
     */
    @Test
    public void findUser() {
        User user = userMapper.selectOne(new QueryWrapper<User>().eq("user_id",1374321115400331266L));
        System.out.println(user);
    }

四. Sharding-JDBC公共表操作

在这里插入图片描述

1.yaml配置

spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:
      #数据库名称
      names: ds0,ds1,ds2
      ds0:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://8.131.119.145:3300/edu_db_2?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
        password: root
        username: root
      ds1:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://127.0.0.1:3306/edu_db_1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
        password: root
        username: root
      ds2:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://127.0.0.1:3306/user_db?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
        password: root
        username: root
    #打印sql
    props:
      sql:
        show: true
    sharding:
      #分表
      tables:
        #数据分表规则
        #指定所需分的数据库和表的分布情况
        t_user: #表前缀
          actual-data-nodes: ds$->{2}.t_user
          # 使用SNOWFLAKE算法生成主键
          key-generator:
            column: user_id
            type: SNOWFLAKE
          table-strategy:
            inline:
              #分表规则(无规则就是这样,专库专表)
              algorithm-expression: t_user
              #指定主键
              sharding-column: user_id

        course:    #表前缀
          actual-data-nodes: ds$->{0..1}.course_$->{1..2}
          # 使用SNOWFLAKE算法生成主键
          key-generator:
            column: cid
            type: SNOWFLAKE
          table-strategy:
            inline:
              #分表规则
              algorithm-expression: course_$->{cid % 2 + 1}
              #指定主键
              sharding-column: cid

          #分库
          database-strategy:
            inline:
              algorithm-expression: ds$->{user_id % 2}
              sharding-column: user_id

        #公共表配置
        t_udict:
            # 使用SNOWFLAKE算法生成主键
            key-generator:
              column: dictid
              type: SNOWFLAKE
      #公共表配置
      broadcast-tables: t_udict


2.实体类

package com.example.demo3.entity;

import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

@Data
@TableName(value = "t_udict")
public class Udict {

    @TableId
    private Long dictid;

    private String ustatus;

    private String uvalue;
}

3.测试代码

  /**
     * 添加操作方法测试
     */
    @Test
    public void addUdict() {
        //for (int i = 0; i < 100; i++) {
        Udict udict = new Udict();
        udict.setUstatus("a");
        udict.setUvalue("已启用");
        udictMapper.insert(udict);
        //}
    }


    /**
     * 删除操作方法测试
     */
    @Test
    public void deleteUdict() {
        udictMapper.delete(new QueryWrapper<Udict>().eq("dictid",1374329986848129025L));

    }

五.Sharding-JDBC主从实战

1.MySQL主从配置

  • docker 启动两个数据库服务器
  • 在Master配置文件
    vim my.ini
#开启日志
log-bin = mysql-bin
#选择模式
binlog_format=ROW
#设置服务id,主从不一致
server_id = 1
#需同步的数据库
binlog-do-db=user_db
#不需要同步的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
mysql -h localhost -uroot -p
GRANT REPLICATION SLAVE ON *.* TO 'db_sync'@'%' IDENTIFIED BY 'db_sync';
FLUSH PRIVILEGES;
show master status;
  • 在Slave配置文件
#开启日志
log-bin = mysql-bin

relay_log=mysql-relay-bin
#选择模式
binlog_format=ROW
server
#设置服务id,主从不一致
server_id = 2
#需同步的数据库
replicate_wild_do_table=user_db.%
#不需要同步的数据库
replicate_wild_ignore_table=mysql
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
mysql -h localhost -uroot -p
STOP SLAVE;
CHANGE MASTER TO
master_host='xxxx',
master_user='db_sync',
master_password='db_sync',
master_log_file='mysql-bin.000002',
master_log_pos = 107;
START SLAVE;
#产看IO_Runing SQL_Runing都为yes才行
show slave status
#注意:如果之前此从库已有主库指向 需要先执行以下命令清空
STOP SLAVE IO_THREAD FOR CHANNEL '';
reset slave all;

2.Sharding-JDBC实现读写分离

spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:
      #数据库名称
      names: ds0,s0
      #主服务器
      ds0:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://8.131.119.145:3300/user_db?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
        password: root
        username: root
      #从服务器
      s0:
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://127.0.0.1:3306/user_db?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false&verifyServerCertificate=false&autoReconnct=true&autoReconnectForPools=true&allowMultiQueries=true
        password: root
        username: root
    #打印sql
    props:
      sql:
        show: true
    sharding:
      master-slave-rules:
        #load-balance-algorithm-type: round_robin

        #指向的主数据库名称
        ds0:
          master-data-source-name: ds0
          #多个丛用逗号隔开
          slave-data-source-names: s0
      tables:
        t_user:
          actual-data-nodes: ds0.t_user

六.总结

  • 注:可以通过Seata进行分布式事务控制
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值