Shardingsphere-JDBC入门使用

鱼上黔
90 阅读3分钟

前言

本文章基于Shardingsphere做简单的读写分离, 分库分表, 提供初学者了解Shardingsphere.

步骤

  • SQL 准备, ds0,ds1作为主库, ds0_slave0,ds0_slave1, ds1_slave0,ds1_slave1 作为从库,sql如下:
create database ds0;
create database ds0_slave0;
create database ds0_slave1;

create database ds1;
create database ds1_slave1;
create database ds1_slave1;

-- id   bigint(20) not null comment 'id',
-- name varchar(25) not null comment '名称',
-- age  int(4)      not null comment '年龄',

create table ds0.t_order_0 ();
create table ds0.t_order_2 ();
create table ds0.t_order_4 ();
create table ds0_slave0.t_order_0 ();
create table ds0_slave0.t_order_2 ();
create table ds0_slave0.t_order_4 ();
create table ds0_slave1.t_order_0 ();
create table ds0_slave1.t_order_2 ();
create table ds0_slave1.t_order_4 ();

create table ds1.t_order_1 ();
create table ds1.t_order_3 ();
create table ds1.t_order_5 ();
create table ds1_slave0.t_order_1 ();
create table ds1_slave0.t_order_3 ();
create table ds1_slave0.t_order_5 ();
create table ds1_slave1.t_order_1 ();
create table ds1_slave1.t_order_3 ();
create table ds1_slave1.t_order_5 ();
  • 新建maven项目, 导入jar包
<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <version>4.1.1</version>
    </dependency>


    <!-- mybatisplus相关Jar -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.15</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.1.0</version>
    </dependency>
    <!-- 保证 Spring AOP 相关的依赖包 -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-aspects</artifactId>
    </dependency>


    <!-- freemarker 模板引擎 -->
    <dependency>
        <groupId>org.freemarker</groupId>
        <artifactId>freemarker</artifactId>
        <version>2.3.28</version>
    </dependency>
    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-lang3</artifactId>
    </dependency>
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-generator</artifactId>
        <version>3.1.0</version>
        <scope>compile</scope>
    </dependency>


</dependencies>
  • 自动生成CRUD代码
package com.sharding.shardingtest.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import java.io.Serializable;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;


@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName(value = "t_order")
public class Order implements Serializable {

    @TableId(value = "id", type = IdType.NONE)
    private Long id;

    /**
     * 名称
     */
    private String name;

    /**
     * 年龄
     */
    private Integer age;


}
  • Shardingsphere配置文件 application-sharding-master-slave.yml
spring:
  shardingsphere:
    datasource:
      names: ds0,ds1,slave0,slave1,slave2,slave3
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://127.0.0.1:3306/ds0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true
        username: root
        password: 
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://127.0.0.1:3306/ds1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true
        username: root
        password: 
      slave0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/ds0_slave0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true
        username: root
        password: 
      slave1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/ds0_slave1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true
        username: root
        password: 
      slave2:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/ds1_slave0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true
        username: root
        password: 
      slave3:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:3306/ds1_slave1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true
        username: root
        password: 

    sharding:
      binding-tables: t_order
      default-database-strategy:
        inline:
          sharding-column: id
          algorithm-expression: ds_$->{id % 2}
      tables:
        t_order:
          actual-data-nodes: ds_0.t_order_$->{[0,2,4]}, ds_1.t_order_$->{[1,3,5]}
          table-strategy:
            inline:
              sharding-column: id
              algorithm-expression: t_order_$->{id % 6}
          key-generator:
            column: id
            type: SNOWFLAKE
            props:
              worker:
                id: 123
      master-slave-rules:  # 读写分离配置
        ds_0:
          master-data-source-name: ds0
          slave-data-source-names:
            - slave0
            - slave1
          loadBalanceAlgorithmType: Random
        ds_1:
          master-data-source-name: ds1
          slave-data-source-names:
            - slave2
            - slave3
          loadBalanceAlgorithmType: ROUND_ROBIN


      props:
        sql:
          show: true # 打印 SQL
#        acceptor:
#          size: # accept连接的线程数量,默认为cpu核数2倍
#        executor:
#          size: #工作线程数量最大，默认值: 无限制
#        max:
#          connections:
#            size:
#              per:
#                query: # 每个查询可以打开的最大连接数量,默认为1
#        proxy:
#          frontend:
#            flush:
#              threshold: # proxy的服务时候,对于单个大查询,每多少个网络包返回一次
#          transaction:
#            type: # 默认LOCAL,proxy的事务模型 允许LOCAL,XA,BASE三个值 LOCAL无分布式事务,XA则是采用atomikos实现的分布式事务 BASE目前尚未实现
#          opentracing:
#            enabled: # 是否启用opentracing
#          backend:
#            use:
#              nio: # 是否采用netty的NIO机制连接后端数据库,默认False ,使用epoll机制
#            max:
#              connections: # 使用NIO而非epoll的话,proxy后台连接每个netty客户端允许的最大连接数量(注意不是数据库连接限制) 默认为8
#            connection:
#              timeout:
#                seconds: #使用nio而非epoll的话,proxy后台连接的超时时间,默认60s
#        check:
#          table:
#            metadata:
#              enabled: #是否在启动时检查分表元数据一致性，默认值: false
  • application.yml 配置
spring:
  profiles:
    active: sharding-master-slave
#    include: orchestration-zookeeper

mybatis-plus:
  mapper-locations: classpath:mapper/*.xml
  typeAliasesPackage: com.sharding.shardingtest.entity
  configuration:
    map-underscore-to-camel-case: true
  global-config:
    id-type: NONE
    field-strategy: NOT_EMPTY
    db-column-underline: true
    refresh-mapper: true
    #capital-mode: true
    #key-generator: com.baomidou.springboot.xxx
#    logic-delete-value: 1
#    logic-not-delete-value: 0
    #meta-object-handler: com.baomidou.springboot.xxx
    #sql-injector: com.baomidou.springboot.xxx
  • 单元测试
package com.sharding.shardingtest;

import com.sharding.shardingtest.api.IOrderService;
import com.sharding.shardingtest.entity.Order;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import javax.annotation.Resource;
import java.util.Random;

@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class ShardingTestApplicationTests {

    @Resource
    IOrderService orderService;

    @Test
    public void contextLoads() {
        for (int i = 0 ;i < 10;i++){
            Order order = new Order();
            order.setAge(new Random().nextInt(100));
            order.setName("第" + order.getAge());
            orderService.save(order);
        }

//        Long id = 1628732873341222914L;
//        Order order = orderService.getById(id);
//        log.info("order: {}", order == null);
    }

}
  • 观察数据

1677318825123.png

1677318847815.png

1677318876160.png

已经实现分库分表功能!!!

avatar
文章
阅读
粉丝