Springboot 2.6 + Mybatis Plus 3.5 集成 Sharding-jdbc 5.1 分库分表

最新推荐文章于 2024-05-10 09:54:33 发布
最新推荐文章于 2024-05-10 09:54:33 发布
阅读量1w 收藏 29
点赞数 7
分类专栏： Java 文章标签： springboot sharding-jdbc 分库分表 java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Mrqiang9001/article/details/124175654
版权

文章目录

1 摘要

随着业务数据的增加，单个的数据库已经无法支撑系统运行了，为了提升系统的流畅性，分库分表就是为了解决系统在大量数据情况下系统相应时间的问题。Sharding-jdbc 作为成熟的分库分表技术框架，在国内应用广泛。本文将介绍基于 Springboot 2.6 + Mybatis Plus 3.5 集成 Sharding-jdbc 5.1 实现分库分表功能。

Sharding-jdbc 官方文档: Use ShardingSphere Data Source in Spring

Sharding-jdbc 分库分表基本原理: 根据 id 进行取余，余数为多少，就对应相同编号的数据库、数据库表。

准备工作

数据库: 2个+

数据库表: 3个+

这里以最简分库分表为例: 一个单表(设备信息表)，三张分表(路由器配置表)

./doc/sql/sharding_jdbc.sql

/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     2022/4/7 10:35:20                            */
/*==============================================================*/


DROP TABLE IF EXISTS DEVICE_INFO;

DROP TABLE IF EXISTS ROUTER_CONFIG_0;

DROP TABLE IF EXISTS ROUTER_CONFIG_1;

DROP TABLE IF EXISTS ROUTER_CONFIG_2;

/*==============================================================*/
/* Table: DEVICE_INFO                                           */
/*==============================================================*/
CREATE TABLE DEVICE_INFO
(
   ID                   BIGINT UNSIGNED NOT NULL COMMENT 'id',
   TYPE                 TINYINT COMMENT '设备类型,1-路由器,2-音响,3-摄像头',
   BRAND                VARCHAR(32) COMMENT '设备品牌',
   MODEL                VARCHAR(32) COMMENT '设备型号',
   NAME                 VARCHAR(32) COMMENT '设备名称',
   CREATE_TIME          DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
   UPDATE_TIME          DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
   PRIMARY KEY (ID)
)
ENGINE = INNODB DEFAULT
CHARSET = UTF8MB4;

ALTER TABLE DEVICE_INFO COMMENT '设备信息';

/*==============================================================*/
/* Table: ROUTER_CONFIG_0                                       */
/*==============================================================*/
CREATE TABLE ROUTER_CONFIG_0
(
   ID                   BIGINT UNSIGNED NOT NULL COMMENT '主键',
   DEVICE_ID            BIGINT COMMENT '设备id',
   WIFI_NAME            VARCHAR(32) COMMENT 'wifi名称',
   WIFI_PASSWORD        VARCHAR(64) COMMENT 'wifi密码',
   ENCRYPT_TYPE         TINYINT DEFAULT 0 COMMENT '加密类型,0-不加密,1-WPA-PSK,2-WPA2-PSK,3-WPA/WPA2-PSK',
   ADMIN_PASSWORD       VARCHAR(64) COMMENT '管理员密码',
   WIFI_SWITCH          TINYINT DEFAULT 1 COMMENT 'wifi开关,0-关闭,1-开启',
   HIDE_SWITCH          TINYINT DEFAULT 0 COMMENT '是否隐藏 wifi,0-不隐藏,1-隐藏',
   CREATE_TIME          DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
   UPDATE_TIME          DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
   PRIMARY KEY (ID)
)
ENGINE = INNODB DEFAULT
CHARSET = UTF8MB4;

ALTER TABLE ROUTER_CONFIG_0 COMMENT '路由器配置信息';

/*==============================================================*/
/* Table: ROUTER_CONFIG_1                                       */
/*==============================================================*/
CREATE TABLE ROUTER_CONFIG_1
(
   ID                   BIGINT UNSIGNED NOT NULL COMMENT '主键',
   DEVICE_ID            BIGINT COMMENT '设备id',
   WIFI_NAME            VARCHAR(32) COMMENT 'wifi名称',
   WIFI_PASSWORD        VARCHAR(64) COMMENT 'wifi密码',
   ENCRYPT_TYPE         TINYINT DEFAULT 0 COMMENT '加密类型,0-不加密,1-WPA-PSK,2-WPA2-PSK,3-WPA/WPA2-PSK',
   ADMIN_PASSWORD       VARCHAR(64) COMMENT '管理员密码',
   WIFI_SWITCH          TINYINT DEFAULT 1 COMMENT 'wifi开关,0-关闭,1-开启',
   HIDE_SWITCH          TINYINT DEFAULT 0 COMMENT '是否隐藏 wifi,0-不隐藏,1-隐藏',
   CREATE_TIME          DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
   UPDATE_TIME          DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
   PRIMARY KEY (ID)
)
ENGINE = INNODB DEFAULT
CHARSET = UTF8MB4;

ALTER TABLE ROUTER_CONFIG_1 COMMENT '路由器配置信息';

/*==============================================================*/
/* Table: ROUTER_CONFIG_2                                       */
/*==============================================================*/
CREATE TABLE ROUTER_CONFIG_2
(
   ID                   BIGINT UNSIGNED NOT NULL COMMENT '主键',
   DEVICE_ID            BIGINT COMMENT '设备id',
   WIFI_NAME            VARCHAR(32) COMMENT 'wifi名称',
   WIFI_PASSWORD        VARCHAR(64) COMMENT 'wifi密码',
   ENCRYPT_TYPE         TINYINT DEFAULT 0 COMMENT '加密类型,0-不加密,1-WPA-PSK,2-WPA2-PSK,3-WPA/WPA2-PSK',
   ADMIN_PASSWORD       VARCHAR(64) COMMENT '管理员密码',
   WIFI_SWITCH          TINYINT DEFAULT 1 COMMENT 'wifi开关,0-关闭,1-开启',
   HIDE_SWITCH          TINYINT DEFAULT 0 COMMENT '是否隐藏 wifi,0-不隐藏,1-隐藏',
   CREATE_TIME          DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
   UPDATE_TIME          DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
   PRIMARY KEY (ID)
)
ENGINE = INNODB DEFAULT
CHARSET = UTF8MB4;

ALTER TABLE ROUTER_CONFIG_2 COMMENT '路由器配置信息';

注意事项:

无论是分库还是分表，编号都要从 0 开始。数据源名称 ds0,ds1,表名 table_0,table_1 因为取余匹配数据库、表会出现余数为 0 的情况

2 Maven 核心依赖

./demo-sharding-jdbc/pom.xml

    <dependencies>
        <!-- web mvc -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>${springboot.version}</version>
        </dependency>
        <!-- sharding-jdbc -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>${shardingsphere.version}</version>
        </dependency>
        <!-- mysql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>
        <!-- mybatis plus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatis-plus.version}</version>
        </dependency>
        <!-- hutool -->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>${hutool.version}</version>
        </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>
    </dependencies>

其中对应的版本如下:

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>${java.version}</maven.compiler.source>
        <maven.compiler.target>${java.version}</maven.compiler.target>
        <java.version>1.8</java.version>
        <springboot.version>2.6.6</springboot.version>
        <shardingsphere.version>5.1.0</shardingsphere.version>
        <mysql.version>8.0.28</mysql.version>
        <mybatis-plus.version>3.5.1</mybatis-plus.version>
        <hutool.version>5.7.21</hutool.version>

    </properties>

3 核心配置

3.1 公共配置
./demo-sharding-jdbc/src/main/resources/application.yml

## server
server:
  port: 8950

## spring
spring:
  application:
    name: demo-sharding-jdbc
  profiles:
    active: dev

## mybatis-plus
mybatis-plus:
  mapper-locations: classpath*:mybatis/*.xml
  type-aliases-package: com.ljq.demo.springboot.sharding.jdbc.model.entity

./demo-sharding-jdbc/src/main/java/com/ljq/demo/springboot/sharding/jdbc/common/MybatisPlusConfig.java

package com.ljq.demo.springboot.sharding.jdbc.common;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * @Description: MybatisPlus 配置信息
 * @Author: junqiang.lu
 * @Date: 2020/9/1
 */
@Configuration
public class MybatisPlusConfig {

    /**
     * 新的分页插件,一缓和二缓遵循 mybatis 的规则
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }

}
3.2 只分表,不分库
./demo-sharding-jdbc/src/main/resources/application-dev.yml

## 不分库,分表

spring:
  shardingsphere:
    mode:
      type: Memory ## Sharding-jdbc 运行模式
    datasource:
      names: ds1  ## 数据源别名
      ds1:
        type: com.zaxxer.hikari.HikariDataSource  ## 数据源连接池连接类型,支持 Durid,Hikari 等常见数据库连接池
        driver-class-name: com.mysql.cj.jdbc.Driver  ## 数据库连接驱动
        jdbc-url: "jdbc:mysql://127.0.0.1:3306/demo?useUnicode=true&characterEncoding=utf8\
                    &useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2b8\
                    &useSSL=true&allowMultiQueries=true&autoReconnect=true&nullCatalogMeansCurrent=true"
        username: root
        password: "xxxx"
    rules:  ## 分库分表规则配置
      sharding:
        tables:
          router_config:  ## 数据库表名前缀
            actual-data-nodes: ds1.router_config_$->{0..2}  ## 实际表名
            table-strategy:  ## 分表策略
              standard:
                sharding-column: id  ## 按照分表的列
                sharding-algorithm-name: router-config-inline  ## 分表算法名称(使用 yml 配置不能包含下划线,否则不生效)
            key-generate-strategy:  ## 主键生成策略
              column: id  ## 主键列
              key-generator-name: snowflake  ## 策略算法名称(推荐使用雪花算法)
        sharding-algorithms:
          router-config-inline:
            type: INLINE
            props:
              algorithm-expression: router_config_$->{id % 3}  ## 分表算法(取余)
        key-generators:
          snowflake:
            type: SNOWFLAKE
    props:
      sql-show: true  ## 展示执行 SQL

官方配置说明:

在 Spring 中使用 ShardingSphere

3.3 分库,不分表
./demo-sharding-jdbc/src/main/resources/application-test.yml

## 分库,不分表

spring:
  shardingsphere:
    datasource:
      names: ds0,ds1
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: "jdbc:mysql://127.0.0.1:3306/demo?useUnicode=true&characterEncoding=utf8\
                    &useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2b8\
                    &useSSL=true&allowMultiQueries=true&autoReconnect=true&nullCatalogMeansCurrent=true"
        username: root
        password: "xxxx"
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: "jdbc:mysql://127.0.0.1:3306/demo2?useUnicode=true&characterEncoding=utf8\
                          &useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2b8\
                          &useSSL=true&allowMultiQueries=true&autoReconnect=true&nullCatalogMeansCurrent=true"
        username: root
        password: "xxxx"
    rules:
      sharding:
        default-database-strategy:
          standard:
            sharding-column: id
            sharding-algorithm-name: database-inline
        tables:
          device_info:  ## 数据库表名
            actual-data-nodes: ds$->{0..1}.device_info  ## 实际表节点
            key-generate-strategy:
              column: id
              key-generator-name: snowflake
        sharding-algorithms:
          database-inline:
            type: INLINE
            props:
              algorithm-expression: ds$->{id % 2}
        key-generators:
          snowflake:
            type: SNOWFLAKE
    props:
      sql-show: true
3.4 分库分表
./demo-sharding-jdbc/src/main/resources/application-prd.yml

## 分库,分表

spring:
  shardingsphere:
    mode:
      type: Standalone
      repository:
        type: File
      overwrite: true
    datasource:
      names: ds0,ds1
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: "jdbc:mysql://127.0.0.1:3306/demo?useUnicode=true&characterEncoding=utf8\
                    &useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2b8\
                    &useSSL=true&allowMultiQueries=true&autoReconnect=true&nullCatalogMeansCurrent=true"
        username: root
        password: "xxxx"
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: "jdbc:mysql://127.0.0.1:3306/demo2?useUnicode=true&characterEncoding=utf8\
                          &useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2b8\
                          &useSSL=true&allowMultiQueries=true&autoReconnect=true&nullCatalogMeansCurrent=true"
        username: root
        password: "xxxx"
    rules:
      sharding:
        default-database-strategy:
          standard:
            sharding-column: id
            sharding-algorithm-name: database-inline
        tables:
          router_config:
            actual-data-nodes: ds$->{0..1}.router_config_$->{0..2}
            table-strategy:
              standard:
                sharding-column: id
                sharding-algorithm-name: t-router-config-inline
            key-generate-strategy:
              column: id
              key-generator-name: snowflake
        sharding-algorithms:
          database-inline:
            type: INLINE
            props:
              algorithm-expression: ds$->{id % 2}
          t-router-config-inline:
            type: INLINE
            props:
              algorithm-expression: router_config_$->{id % 3}
        key-generators:
          snowflake:
            type: SNOWFLAKE
    props:
      sql-show: true

4 核心代码

4.1 实体类

基础实体类

./demo-sharding-jdbc/src/main/java/com/ljq/demo/springboot/sharding/jdbc/model/entity/BaseEntity.java

package com.ljq.demo.springboot.sharding.jdbc.model.entity;

import com.baomidou.mybatisplus.annotation.TableField;
import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import com.fasterxml.jackson.databind.ser.std.ToStringSerializer;
import lombok.Data;

import java.io.Serializable;
import java.util.Date;

/**
 * @Description: 基础实体类
 * @Author: junqiang.lu
 * @Date: 2022/4/1
 */
@Data
public class BaseEntity implements Serializable {

    private static final long serialVersionUID = 2494094207811184245L;

    /**
     * id
     **/
    @JsonSerialize(using= ToStringSerializer.class)
    private Long id;
    /**
     * 创建时间
     **/
    @TableField(value = "CREATE_TIME")
    private Date createTime;
    /**
     * 更新时间
     **/
    @TableField(value = "UPDATE_TIME")
    private Date updateTime;


}

分表对应的实体类

./demo-sharding-jdbc/src/main/java/com/ljq/demo/springboot/sharding/jdbc/model/entity/RouterConfigEntity.java

package com.ljq.demo.springboot.sharding.jdbc.model.entity;

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

/**
 * @Description: 路由器配置实体
 * @Author: junqiang.lu
 * @Date: 2022/4/1
 */
@Data
@ToString(callSuper = true)
@TableName(value = "router_config")
public class RouterConfigEntity extends BaseEntity {

    private static final long serialVersionUID = 4849091384661358788L;

    /**
     * 设备 ID
     */
    private Long deviceId;
    /**
     * wifi 名称
     */
    private String wifiName;
    /**
     * wifi 密码
     */
    private String wifiPassword;
    /**
     * 加密类型,0-不加密,1-WPA-PSK,2-WPA2-PSK,3-WPA/WPA2-PSK
     */
    private Integer encryptType;
    /**
     * 管理员密码
     */
    private String adminPassword;
    /**
     * wifi开关,0-关闭,1-开启
     */
    private Integer wifiSwitch;
    /**
     * 是否隐藏 wifi,0-不隐藏,1-隐藏
     */
    private Integer hideSwitch;


}

注意事项:

对于分表的表名，使用逻辑表名，即实际表名包含编号，使用 Mybatis Plus 的 @TableName 不包含编号

只分库的数据库表对应的实体类

./demo-sharding-jdbc/src/main/java/com/ljq/demo/springboot/sharding/jdbc/model/entity/DeviceInfoEntity.java

package com.ljq.demo.springboot.sharding.jdbc.model.entity;

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

/**
 * @Description: 设备信息实体
 * @Author: junqiang.lu
 * @Date: 2022/4/1
 */
@Data
@ToString(callSuper = true)
@TableName(value = "device_info")
public class DeviceInfoEntity extends BaseEntity {

    private static final long serialVersionUID = -8417097323674970608L;

    /**
     * 设备类型,1-路由器,2-音响,3-摄像头
     */
    private Integer type;
    /**
     * 设备品牌
     */
    private String brand;
    /**
     * 设备型号
     */
    private String model;
    /**
     * 设备名称
     */
    private String name;


}

对于只分库的表名，和源库中的表名保持一致就可以

4.2 DAO 层

DAO 层按照正常 Mybatis Plus 写法

./demo-sharding-jdbc/src/main/java/com/ljq/demo/springboot/sharding/jdbc/dao/RouterConfigDao.java

package com.ljq.demo.springboot.sharding.jdbc.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ljq.demo.springboot.sharding.jdbc.model.entity.RouterConfigEntity;
import org.springframework.stereotype.Repository;

/**
 * @Description: 路由器配置 DAO
 * @Author: junqiang.lu
 * @Date: 2022/4/1
 */
@Repository
public interface RouterConfigDao extends BaseMapper<RouterConfigEntity> {


}

./demo-sharding-jdbc/src/main/java/com/ljq/demo/springboot/sharding/jdbc/dao/DeviceInfoDao.java

package com.ljq.demo.springboot.sharding.jdbc.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ljq.demo.springboot.sharding.jdbc.model.entity.DeviceInfoEntity;
import org.springframework.stereotype.Repository;

/**
 * @Description: 设备信息 DAO
 * @Author: junqiang.lu
 * @Date: 2022/4/1
 */
@Repository
public interface DeviceInfoDao extends BaseMapper<DeviceInfoEntity> {
}
4.3 Service 层

Service 接口

./demo-sharding-jdbc/src/main/java/com/ljq/demo/springboot/sharding/jdbc/service/RouterConfigService.java

package com.ljq.demo.springboot.sharding.jdbc.service;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.ljq.demo.springboot.sharding.jdbc.model.entity.RouterConfigEntity;
import com.ljq.demo.springboot.sharding.jdbc.model.param.RouterConfigAddParam;
import com.ljq.demo.springboot.sharding.jdbc.model.param.RouterConfigQueryOneParam;
import com.ljq.demo.springboot.sharding.jdbc.model.param.RouterConfigQueryPageParam;

/**
 * @Description: 路由器配置业务接口
 * @Author: junqiang.lu
 * @Date: 2022/4/1
 */
public interface RouterConfigService {

    /**
     * 路由器配置新增
     *
     * @param addParam
     * @return
     */
    RouterConfigEntity add(RouterConfigAddParam addParam);

    /**
     * 路由器配置查询单条
     *
     * @param queryOneParam
     * @return
     */
    RouterConfigEntity queryOne(RouterConfigQueryOneParam queryOneParam);

    /**
     * 路由器配置分页查询
     *
     * @param queryPageParam
     * @return
     */
    IPage<RouterConfigEntity> queryPage(RouterConfigQueryPageParam queryPageParam);



}

./demo-sharding-jdbc/src/main/java/com/ljq/demo/springboot/sharding/jdbc/service/DeviceInfoService.java

package com.ljq.demo.springboot.sharding.jdbc.service;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.ljq.demo.springboot.sharding.jdbc.model.entity.DeviceInfoEntity;
import com.ljq.demo.springboot.sharding.jdbc.model.param.DeviceInfoAddParam;
import com.ljq.demo.springboot.sharding.jdbc.model.param.DeviceInfoQueryOneParam;
import com.ljq.demo.springboot.sharding.jdbc.model.param.DeviceInfoQueryPageParam;

/**
 * @Description: 设备信息业务接口
 * @Author: junqiang.lu
 * @Date: 2022/4/1
 */
public interface DeviceInfoService {

    /**
     * 设备信息新增
     *
     * @param addParam
     * @return
     */
    DeviceInfoEntity add(DeviceInfoAddParam addParam);

    /**
     * 设备信息查询单条
     *
     * @param queryOneParam
     * @return
     */
    DeviceInfoEntity queryOne(DeviceInfoQueryOneParam queryOneParam);

    /**
     * 设备信息分页查询
     *
     * @param queryPageParam
     * @return
     */
    IPage<DeviceInfoEntity> queryPage(DeviceInfoQueryPageParam queryPageParam);
}

Service 实现类

Service 实现类和单数据源 Mybatis Plus 使用方式一致

./demo-sharding-jdbc/src/main/java/com/ljq/demo/springboot/sharding/jdbc/service/impl/RouterConfigServiceImpl.java

package com.ljq.demo.springboot.sharding.jdbc.service.impl;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.bean.copier.CopyOptions;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ljq.demo.springboot.sharding.jdbc.dao.RouterConfigDao;
import com.ljq.demo.springboot.sharding.jdbc.model.entity.RouterConfigEntity;
import com.ljq.demo.springboot.sharding.jdbc.model.param.RouterConfigAddParam;
import com.ljq.demo.springboot.sharding.jdbc.model.param.RouterConfigQueryOneParam;
import com.ljq.demo.springboot.sharding.jdbc.model.param.RouterConfigQueryPageParam;
import com.ljq.demo.springboot.sharding.jdbc.service.RouterConfigService;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.util.Objects;

/**
 * @Description: 路由器配置业务实现类
 * @Author: junqiang.lu
 * @Date: 2022/4/1
 */
@Service("routerConfigService")
public class RouterConfigServiceImpl extends ServiceImpl<RouterConfigDao, RouterConfigEntity>
        implements RouterConfigService {


    /**
     * 设备信息新增
     *
     * @param addParam
     * @return
     */
    @Transactional(propagation = Propagation.REQUIRED, rollbackFor = {Exception.class})
    @Override
    public RouterConfigEntity add(RouterConfigAddParam addParam) {
        RouterConfigEntity routerConfigEntity = new RouterConfigEntity();
        BeanUtil.copyProperties(addParam, routerConfigEntity, CopyOptions.create().ignoreError().ignoreNullValue());
        this.save(routerConfigEntity);
        return routerConfigEntity;
    }

    /**
     * 设备信息查询单条
     *
     * @param queryOneParam
     * @return
     */
    @Override
    public RouterConfigEntity queryOne(RouterConfigQueryOneParam queryOneParam) {
        return this.getById(queryOneParam.getId());
    }

    /**
     * 设备信息分页查询
     *
     * @param queryPageParam
     * @return
     */
    @Override
    public IPage<RouterConfigEntity> queryPage(RouterConfigQueryPageParam queryPageParam) {
        IPage<RouterConfigEntity> page = new Page<>(queryPageParam.getCurrentPage(), queryPageParam.getPageSize());

        LambdaQueryWrapper<RouterConfigEntity> queryWrapper = Wrappers.lambdaQuery();
        queryWrapper.eq(Objects.nonNull(queryPageParam.getDeviceId()), RouterConfigEntity::getDeviceId,
                queryPageParam.getDeviceId());
        queryWrapper.orderBy(true,true,RouterConfigEntity::getId);
        return this.page(page, queryWrapper);
    }
}

./demo-sharding-jdbc/src/main/java/com/ljq/demo/springboot/sharding/jdbc/service/impl/DeviceInfoServiceImpl.java

package com.ljq.demo.springboot.sharding.jdbc.service.impl;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.bean.copier.CopyOptions;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ljq.demo.springboot.sharding.jdbc.dao.DeviceInfoDao;
import com.ljq.demo.springboot.sharding.jdbc.model.entity.DeviceInfoEntity;
import com.ljq.demo.springboot.sharding.jdbc.model.param.DeviceInfoAddParam;
import com.ljq.demo.springboot.sharding.jdbc.model.param.DeviceInfoQueryOneParam;
import com.ljq.demo.springboot.sharding.jdbc.model.param.DeviceInfoQueryPageParam;
import com.ljq.demo.springboot.sharding.jdbc.service.DeviceInfoService;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.util.Objects;

/**
 * @Description: 设备信息业务实现类
 * @Author: junqiang.lu
 * @Date: 2022/4/2
 */
@Service("deviceInfoService")
public class DeviceInfoServiceImpl extends ServiceImpl<DeviceInfoDao, DeviceInfoEntity> implements DeviceInfoService {


    /**
     * 设备信息新增
     *
     * @param addParam
     * @return
     */
    @Transactional(propagation = Propagation.REQUIRED, rollbackFor = {Exception.class})
    @Override
    public DeviceInfoEntity add(DeviceInfoAddParam addParam) {
        DeviceInfoEntity  deviceInfoEntity = new DeviceInfoEntity();
        BeanUtil.copyProperties(addParam, deviceInfoEntity, CopyOptions.create().ignoreError().ignoreNullValue());
        this.save(deviceInfoEntity);
        return deviceInfoEntity;
    }

    /**
     * 设备信息查询单条
     *
     * @param queryOneParam
     * @return
     */
    @Override
    public DeviceInfoEntity queryOne(DeviceInfoQueryOneParam queryOneParam) {
        return this.getById(queryOneParam.getId());
    }

    /**
     * 设备信息分页查询
     *
     * @param queryPageParam
     * @return
     */
    @Override
    public IPage<DeviceInfoEntity> queryPage(DeviceInfoQueryPageParam queryPageParam) {
        Page<DeviceInfoEntity> page = new Page<>(queryPageParam.getCurrentPage(), queryPageParam.getPageSize());
        LambdaQueryWrapper<DeviceInfoEntity> queryWrapper = Wrappers.lambdaQuery();
        queryWrapper.eq(Objects.nonNull(queryPageParam.getType()), DeviceInfoEntity::getType, queryPageParam.getType());
        queryWrapper.orderBy(true, true, DeviceInfoEntity::getType);
        return this.page(page, queryWrapper);
    }
}
4.4 控制层
./demo-sharding-jdbc/src/main/java/com/ljq/demo/springboot/sharding/jdbc/controller/RouterConfigController.java

package com.ljq.demo.springboot.sharding.jdbc.controller;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.ljq.demo.springboot.sharding.jdbc.model.entity.RouterConfigEntity;
import com.ljq.demo.springboot.sharding.jdbc.model.param.RouterConfigAddParam;
import com.ljq.demo.springboot.sharding.jdbc.model.param.RouterConfigQueryOneParam;
import com.ljq.demo.springboot.sharding.jdbc.model.param.RouterConfigQueryPageParam;
import com.ljq.demo.springboot.sharding.jdbc.service.RouterConfigService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

/**
 * @Description: 路由器配置控制层
 * @Author: junqiang.lu
 * @Date: 2022/4/1
 */
@RestController
@RequestMapping(value = "/api/sharding/jdbc/router/config")
public class RouterConfigController {

    @Autowired
    private RouterConfigService routerConfigService;

    /**
     * 路由器配置新增单条
     *
     * @param addParam
     * @return
     */
    @PostMapping(value = "/add", produces = {MediaType.APPLICATION_JSON_VALUE})
    public ResponseEntity<RouterConfigEntity> add(@RequestBody RouterConfigAddParam addParam) {
        return ResponseEntity.ok(routerConfigService.add(addParam));
    }

    /**
     * 路由器配置查询单条
     *
     * @param queryOneParam
     * @return
     */
    @GetMapping(value = "/query/one", produces = {MediaType.APPLICATION_JSON_VALUE})
    public ResponseEntity<RouterConfigEntity> queryOne(RouterConfigQueryOneParam queryOneParam) {
        return ResponseEntity.ok(routerConfigService.queryOne(queryOneParam));
    }

    /**
     * 路由器配置分页查询
     *
     * @param queryPageParam
     * @return
     */
    @GetMapping(value = "/query/page", produces = {MediaType.APPLICATION_JSON_VALUE})
    public ResponseEntity<IPage<RouterConfigEntity>> queryPage(RouterConfigQueryPageParam queryPageParam) {
        return ResponseEntity.ok(routerConfigService.queryPage(queryPageParam));
    }


}

./demo-sharding-jdbc/src/main/java/com/ljq/demo/springboot/sharding/jdbc/controller/DeviceInfoController.java

package com.ljq.demo.springboot.sharding.jdbc.controller;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.ljq.demo.springboot.sharding.jdbc.model.entity.DeviceInfoEntity;
import com.ljq.demo.springboot.sharding.jdbc.model.param.DeviceInfoAddParam;
import com.ljq.demo.springboot.sharding.jdbc.model.param.DeviceInfoQueryOneParam;
import com.ljq.demo.springboot.sharding.jdbc.model.param.DeviceInfoQueryPageParam;
import com.ljq.demo.springboot.sharding.jdbc.service.DeviceInfoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

/**
 * @Description: 设备信息控制层
 * @Author: junqiang.lu
 * @Date: 2022/4/2
 */
@RestController
@RequestMapping(value = "/api/sharding/jdbc/device")
public class DeviceInfoController {

    @Autowired
    private DeviceInfoService deviceInfoService;

    /**
     * 设备信息新增单条
     *
     * @param addParam
     * @return
     */
    @PostMapping(value = "/add", produces = {MediaType.APPLICATION_JSON_VALUE})
    public ResponseEntity<DeviceInfoEntity> add(@RequestBody DeviceInfoAddParam addParam) {
        return ResponseEntity.ok(deviceInfoService.add(addParam));
    }

    /**
     * 设备信息查询单条
     *
     * @param queryOneParam
     * @return
     */
    @GetMapping(value = "/query/one", produces = {MediaType.APPLICATION_JSON_VALUE})
    public ResponseEntity<DeviceInfoEntity> queryOne(DeviceInfoQueryOneParam queryOneParam) {
        return ResponseEntity.ok(deviceInfoService.queryOne(queryOneParam));
    }

    /**
     * 设备信息分页查询
     *
     * @param queryPageParam
     * @return
     */
    @GetMapping(value = "/query/page", produces = {MediaType.APPLICATION_JSON_VALUE})
    public ResponseEntity<IPage<DeviceInfoEntity>> queryPage(DeviceInfoQueryPageParam queryPageParam) {
        return ResponseEntity.ok(deviceInfoService.queryPage(queryPageParam));
    }

}
4.5 SpringBoot 启动类
./demo-sharding-jdbc/src/main/java/com/ljq/demo/springboot/sharding/jdbc/DemoShardingJdbcApplication.java

package com.ljq.demo.springboot.sharding.jdbc;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
 * @author lujunqiang
 */
@MapperScan(basePackages = {"com.ljq.demo.springboot.sharding.jdbc.dao"})
@SpringBootApplication
public class DemoShardingJdbcApplication {

    public static void main(String[] args) {
        SpringApplication.run(DemoShardingJdbcApplication.class, args);
    }

}

5 测试

5.1 只分表,不分库
5.1.1 插入数据

分表请求接口:

http://127.0.0.1:8950/api/sharding/jdbc/router/config/add

请求方式: POST

请求参数:

{
    "deviceId": "1",
    "wifiName": "Lexus",
    "wifiPassword": "OGBFs9Ksa2E0m8M",
    "encryptType": 1,
    "adminPassword": "vbo7u3kgPeFJYU6",
    "wifiSwitch": 1,
    "hideSwitch": 1
}

后台日志:

2022-04-12 19:18:36.781  INFO 21080 --- [nio-8950-exec-2] ShardingSphere-SQL                       : Logic SQL: INSERT INTO router_config  ( id,
device_id,
wifi_name,
wifi_password,
encrypt_type,
admin_password,
wifi_switch,
hide_switch )  VALUES  ( ?,
?,
?,
?,
?,
?,
?,
? )
2022-04-12 19:18:36.781  INFO 21080 --- [nio-8950-exec-2] ShardingSphere-SQL                       : SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
2022-04-12 19:18:36.781  INFO 21080 --- [nio-8950-exec-2] ShardingSphere-SQL                       : Actual SQL: ds1 ::: INSERT INTO router_config_2  ( id,
device_id,
wifi_name,
wifi_password,
encrypt_type,
admin_password,
wifi_switch,
hide_switch )  VALUES  (?, ?, ?, ?, ?, ?, ?, ?) ::: [1513838994770907137, 1, Lexus, OGBFs9Ksa2E0m8M, 1, vbo7u3kgPeFJYU6, 1, 1]

从日志可以看出，SQL 分为逻辑 SQL 以及实际执行的 SQL ，根据 id 取余，最终插入的表为 router_config_2

对比没有分表的数据插入

请求接口:

http://127.0.0.1:8950/api/sharding/jdbc/device/add

请求方式: POST

请求参数:

{
    "type": 1,
    "brand": "TP-LINK",
    "model": "TL-WDR5620",
    "name": "TP-LINK双频千兆路由169"
}

后台日志:

2022-04-12 19:22:13.147  INFO 21080 --- [nio-8950-exec-3] ShardingSphere-SQL                       : Logic SQL: INSERT INTO device_info  ( id,
type,
brand,
model,
name )  VALUES  ( ?,
?,
?,
?,
? )
2022-04-12 19:22:13.147  INFO 21080 --- [nio-8950-exec-3] ShardingSphere-SQL                       : SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
2022-04-12 19:22:13.147  INFO 21080 --- [nio-8950-exec-3] ShardingSphere-SQL                       : Actual SQL: ds1 ::: INSERT INTO device_info  ( id,
type,
brand,
model,
name )  VALUES  (?, ?, ?, ?, ?) ::: [1513839902246326273, 1, TP-LINK, TL-WDR5620, TP-LINK双频千兆路由169]

从日志可以看出，实际执行SQL与逻辑SQL一致，对于同一数据库中不分表的数据库表，SQL 执行不受影响

5.1.2 查询单条

分表请求接口:

http://127.0.0.1:8950/api/sharding/jdbc/router/config/query/one?id=1510093606222135297

请求方式: GET

后台日志:

2022-04-12 19:24:19.691  INFO 21080 --- [nio-8950-exec-6] ShardingSphere-SQL                       : Logic SQL: SELECT id,device_id,wifi_name,wifi_password,encrypt_type,admin_password,wifi_switch,hide_switch,CREATE_TIME,UPDATE_TIME FROM router_config WHERE id=? 
2022-04-12 19:24:19.691  INFO 21080 --- [nio-8950-exec-6] ShardingSphere-SQL                       : SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2022-04-12 19:24:19.691  INFO 21080 --- [nio-8950-exec-6] ShardingSphere-SQL                       : Actual SQL: ds1 ::: SELECT id,device_id,wifi_name,wifi_password,encrypt_type,admin_password,wifi_switch,hide_switch,CREATE_TIME,UPDATE_TIME FROM router_config_1 WHERE id=?  ::: [1510093606222135297]

从日志可以看出，也是根据 id 值先确定表，再执行查询

5.1.3 分页查询

分表请求接口:

http://127.0.0.1:8950/api/sharding/jdbc/router/config/query/page?currentPage=1&pageSize=10&deviceId=1

请求方式: GET

后台日志:

2022-04-12 19:27:52.471  INFO 21080 --- [nio-8950-exec-8] ShardingSphere-SQL                       : Logic SQL: SELECT COUNT(*) AS total FROM router_config WHERE (device_id = ?)
2022-04-12 19:27:52.471  INFO 21080 --- [nio-8950-exec-8] ShardingSphere-SQL                       : SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2022-04-12 19:27:52.471  INFO 21080 --- [nio-8950-exec-8] ShardingSphere-SQL                       : Actual SQL: ds1 ::: SELECT COUNT(*) AS total FROM router_config_0 WHERE (device_id = ?) UNION ALL SELECT COUNT(*) AS total FROM router_config_1 WHERE (device_id = ?) UNION ALL SELECT COUNT(*) AS total FROM router_config_2 WHERE (device_id = ?) ::: [1, 1, 1]
2022-04-12 19:27:52.653  INFO 21080 --- [nio-8950-exec-8] ShardingSphere-SQL                       : Logic SQL: SELECT  id,device_id,wifi_name,wifi_password,encrypt_type,admin_password,wifi_switch,hide_switch,CREATE_TIME,UPDATE_TIME  FROM router_config 
 
 WHERE (device_id = ?) ORDER BY id ASC LIMIT ?
2022-04-12 19:27:52.653  INFO 21080 --- [nio-8950-exec-8] ShardingSphere-SQL                       : SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional[org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.limit.LimitSegment@5ac88e33], lock=Optional.empty, window=Optional.empty)
2022-04-12 19:27:52.653  INFO 21080 --- [nio-8950-exec-8] ShardingSphere-SQL                       : Actual SQL: ds1 ::: SELECT  id,device_id,wifi_name,wifi_password,encrypt_type,admin_password,wifi_switch,hide_switch,CREATE_TIME,UPDATE_TIME  FROM router_config_0 
 
 WHERE (device_id = ?) ORDER BY id ASC LIMIT ? ::: [1, 10]
2022-04-12 19:27:52.653  INFO 21080 --- [nio-8950-exec-8] ShardingSphere-SQL                       : Actual SQL: ds1 ::: SELECT  id,device_id,wifi_name,wifi_password,encrypt_type,admin_password,wifi_switch,hide_switch,CREATE_TIME,UPDATE_TIME  FROM router_config_1 
 
 WHERE (device_id = ?) ORDER BY id ASC LIMIT ? ::: [1, 10]
2022-04-12 19:27:52.654  INFO 21080 --- [nio-8950-exec-8] ShardingSphere-SQL                       : Actual SQL: ds1 ::: SELECT  id,device_id,wifi_name,wifi_password,encrypt_type,admin_password,wifi_switch,hide_switch,CREATE_TIME,UPDATE_TIME  FROM router_config_2 
 
 WHERE (device_id = ?) ORDER BY id ASC LIMIT ? ::: [1, 10]

从日志可以看出，实际执行的SQL是根据分表的数量执行了多次，然后将每个分表的查询结果聚合

5.2 只分库，不分表
5.2.1 插入数据

请求接口:

http://127.0.0.1:8950/api/sharding/jdbc/device/add

请求方式: POST

请求参数:

{
    "type": 1,
    "brand": "TP-LINK",
    "model": "TL-WDR5620",
    "name": "TP-LINK双频千兆路由992"
}

后台日志:

2022-04-12 19:33:43.878  INFO 15896 --- [nio-8950-exec-4] ShardingSphere-SQL                       : Logic SQL: INSERT INTO device_info  ( id,
type,
brand,
model,
name )  VALUES  ( ?,
?,
?,
?,
? )
2022-04-12 19:33:43.879  INFO 15896 --- [nio-8950-exec-4] ShardingSphere-SQL                       : SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
2022-04-12 19:33:43.879  INFO 15896 --- [nio-8950-exec-4] ShardingSphere-SQL                       : Actual SQL: ds1 ::: INSERT INTO device_info  ( id,
type,
brand,
model,
name )  VALUES  (?, ?, ?, ?, ?) ::: [1513842798769786881, 1, TP-LINK, TL-WDR5620, TP-LINK双频千兆路由992]

日志中可以看出数据实际插入的数据库是根据 id 进行取余，然后得到的余数即为数据库的编码

5.2.2 查询单条

请求接口:

http://127.0.0.1:8950/api/sharding/jdbc/device/query/one?id=1511636056593956866

请求方式: GET

后台日志:

2022-04-12 19:39:31.695  INFO 15896 --- [nio-8950-exec-3] ShardingSphere-SQL                       : Logic SQL: SELECT id,type,brand,model,name,CREATE_TIME,UPDATE_TIME FROM device_info WHERE id=? 
2022-04-12 19:39:31.695  INFO 15896 --- [nio-8950-exec-3] ShardingSphere-SQL                       : SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2022-04-12 19:39:31.695  INFO 15896 --- [nio-8950-exec-3] ShardingSphere-SQL                       : Actual SQL: ds0 ::: SELECT id,type,brand,model,name,CREATE_TIME,UPDATE_TIME FROM device_info WHERE id=?  ::: [1511636056593956866]
5.2.3 分页查询

请求接口:

http://127.0.0.1:8950/api/sharding/jdbc/device/query/page?currentPage=1&pageSize=10&type=1

请求方式: GET

后台日志:

2022-04-12 20:10:29.920  INFO 15896 --- [nio-8950-exec-7] ShardingSphere-SQL                       : Logic SQL: SELECT COUNT(*) AS total FROM device_info WHERE (type = ?)
2022-04-12 20:10:29.920  INFO 15896 --- [nio-8950-exec-7] ShardingSphere-SQL                       : SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2022-04-12 20:10:29.920  INFO 15896 --- [nio-8950-exec-7] ShardingSphere-SQL                       : Actual SQL: ds0 ::: SELECT COUNT(*) AS total FROM device_info WHERE (type = ?) ::: [1]
2022-04-12 20:10:29.920  INFO 15896 --- [nio-8950-exec-7] ShardingSphere-SQL                       : Actual SQL: ds1 ::: SELECT COUNT(*) AS total FROM device_info WHERE (type = ?) ::: [1]
2022-04-12 20:10:30.196  INFO 15896 --- [nio-8950-exec-7] ShardingSphere-SQL                       : Logic SQL: SELECT  id,type,brand,model,name,CREATE_TIME,UPDATE_TIME  FROM device_info 
 
 WHERE (type = ?) ORDER BY type ASC LIMIT ?
2022-04-12 20:10:30.196  INFO 15896 --- [nio-8950-exec-7] ShardingSphere-SQL                       : SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional[org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.limit.LimitSegment@61585c7f], lock=Optional.empty, window=Optional.empty)
2022-04-12 20:10:30.196  INFO 15896 --- [nio-8950-exec-7] ShardingSphere-SQL                       : Actual SQL: ds0 ::: SELECT  id,type,brand,model,name,CREATE_TIME,UPDATE_TIME  FROM device_info 
 
 WHERE (type = ?) ORDER BY type ASC LIMIT ? ::: [1, 10]
2022-04-12 20:10:30.196  INFO 15896 --- [nio-8950-exec-7] ShardingSphere-SQL                       : Actual SQL: ds1 ::: SELECT  id,type,brand,model,name,CREATE_TIME,UPDATE_TIME  FROM device_info 
 
 WHERE (type = ?) ORDER BY type ASC LIMIT ? ::: [1, 10]

从执行语句可以看出，是先将每个数据库查询数据总条数查询出来，然后再分页查询每个数据库的数据，最后进行汇总

5.3 分库分表
5.3.1 插入数据

请求接口:

http://127.0.0.1:8950/api/sharding/jdbc/router/config/add

请求方式: POST

请求参数:

{
    "deviceId": "1",
    "wifiName": "Zoie",
    "wifiPassword": "oWCRA6nfduiWZNi",
    "encryptType": 1,
    "adminPassword": "__2Z4NG6xvhpEQY",
    "wifiSwitch": 1,
    "hideSwitch": 1
}

后台日志:

2022-04-12 20:13:47.488  INFO 20788 --- [nio-8950-exec-2] ShardingSphere-SQL                       : Logic SQL: INSERT INTO router_config  ( id,
device_id,
wifi_name,
wifi_password,
encrypt_type,
admin_password,
wifi_switch,
hide_switch )  VALUES  ( ?,
?,
?,
?,
?,
?,
?,
? )
2022-04-12 20:13:47.489  INFO 20788 --- [nio-8950-exec-2] ShardingSphere-SQL                       : SQLStatement: MySQLInsertStatement(setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
2022-04-12 20:13:47.489  INFO 20788 --- [nio-8950-exec-2] ShardingSphere-SQL                       : Actual SQL: ds1 ::: INSERT INTO router_config_2  ( id,
device_id,
wifi_name,
wifi_password,
encrypt_type,
admin_password,
wifi_switch,
hide_switch )  VALUES  (?, ?, ?, ?, ?, ?, ?, ?) ::: [1513852877632000001, 1, Zoie, oWCRA6nfduiWZNi, 1, __2Z4NG6xvhpEQY, 1, 1]

从执行SQL可以看出数据库、数据库表都是经过id取余计算后得到的

5.3.2 请求单条

请求接口:

http://127.0.0.1:8950/api/sharding/jdbc/router/config/query/one?id=1510093606222135297

请求方式: GET

请求日志:

2022-04-12 20:16:11.335  INFO 20788 --- [nio-8950-exec-3] ShardingSphere-SQL                       : Logic SQL: SELECT id,device_id,wifi_name,wifi_password,encrypt_type,admin_password,wifi_switch,hide_switch,CREATE_TIME,UPDATE_TIME FROM router_config WHERE id=? 
2022-04-12 20:16:11.335  INFO 20788 --- [nio-8950-exec-3] ShardingSphere-SQL                       : SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2022-04-12 20:16:11.335  INFO 20788 --- [nio-8950-exec-3] ShardingSphere-SQL                       : Actual SQL: ds1 ::: SELECT id,device_id,wifi_name,wifi_password,encrypt_type,admin_password,wifi_switch,hide_switch,CREATE_TIME,UPDATE_TIME FROM router_config_1 WHERE id=?  ::: [1510093606222135297]
5.3.3 分页查询

请求接口:

http://127.0.0.1:8950/api/sharding/jdbc/router/config/query/page?currentPage=1&pageSize=10&deviceId=1

请求方式: GET

后台日志:

2022-04-12 20:18:03.387  INFO 20788 --- [nio-8950-exec-5] ShardingSphere-SQL                       : Logic SQL: SELECT COUNT(*) AS total FROM router_config WHERE (device_id = ?)
2022-04-12 20:18:03.387  INFO 20788 --- [nio-8950-exec-5] ShardingSphere-SQL                       : SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2022-04-12 20:18:03.387  INFO 20788 --- [nio-8950-exec-5] ShardingSphere-SQL                       : Actual SQL: ds0 ::: SELECT COUNT(*) AS total FROM router_config_0 WHERE (device_id = ?) UNION ALL SELECT COUNT(*) AS total FROM router_config_1 WHERE (device_id = ?) UNION ALL SELECT COUNT(*) AS total FROM router_config_2 WHERE (device_id = ?) ::: [1, 1, 1]
2022-04-12 20:18:03.387  INFO 20788 --- [nio-8950-exec-5] ShardingSphere-SQL                       : Actual SQL: ds1 ::: SELECT COUNT(*) AS total FROM router_config_0 WHERE (device_id = ?) UNION ALL SELECT COUNT(*) AS total FROM router_config_1 WHERE (device_id = ?) UNION ALL SELECT COUNT(*) AS total FROM router_config_2 WHERE (device_id = ?) ::: [1, 1, 1]
2022-04-12 20:18:03.633  INFO 20788 --- [nio-8950-exec-5] ShardingSphere-SQL                       : Logic SQL: SELECT  id,device_id,wifi_name,wifi_password,encrypt_type,admin_password,wifi_switch,hide_switch,CREATE_TIME,UPDATE_TIME  FROM router_config 
 
 WHERE (device_id = ?) ORDER BY id ASC LIMIT ?
2022-04-12 20:18:03.633  INFO 20788 --- [nio-8950-exec-5] ShardingSphere-SQL                       : SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional[org.apache.shardingsphere.sql.parser.sql.common.segment.dml.pagination.limit.LimitSegment@2fc3d159], lock=Optional.empty, window=Optional.empty)
2022-04-12 20:18:03.633  INFO 20788 --- [nio-8950-exec-5] ShardingSphere-SQL                       : Actual SQL: ds0 ::: SELECT  id,device_id,wifi_name,wifi_password,encrypt_type,admin_password,wifi_switch,hide_switch,CREATE_TIME,UPDATE_TIME  FROM router_config_0 
 
 WHERE (device_id = ?) ORDER BY id ASC LIMIT ? ::: [1, 10]
2022-04-12 20:18:03.633  INFO 20788 --- [nio-8950-exec-5] ShardingSphere-SQL                       : Actual SQL: ds0 ::: SELECT  id,device_id,wifi_name,wifi_password,encrypt_type,admin_password,wifi_switch,hide_switch,CREATE_TIME,UPDATE_TIME  FROM router_config_1 
 
 WHERE (device_id = ?) ORDER BY id ASC LIMIT ? ::: [1, 10]
2022-04-12 20:18:03.633  INFO 20788 --- [nio-8950-exec-5] ShardingSphere-SQL                       : Actual SQL: ds0 ::: SELECT  id,device_id,wifi_name,wifi_password,encrypt_type,admin_password,wifi_switch,hide_switch,CREATE_TIME,UPDATE_TIME  FROM router_config_2 
 
 WHERE (device_id = ?) ORDER BY id ASC LIMIT ? ::: [1, 10]
2022-04-12 20:18:03.633  INFO 20788 --- [nio-8950-exec-5] ShardingSphere-SQL                       : Actual SQL: ds1 ::: SELECT  id,device_id,wifi_name,wifi_password,encrypt_type,admin_password,wifi_switch,hide_switch,CREATE_TIME,UPDATE_TIME  FROM router_config_0 
 
 WHERE (device_id = ?) ORDER BY id ASC LIMIT ? ::: [1, 10]
2022-04-12 20:18:03.633  INFO 20788 --- [nio-8950-exec-5] ShardingSphere-SQL                       : Actual SQL: ds1 ::: SELECT  id,device_id,wifi_name,wifi_password,encrypt_type,admin_password,wifi_switch,hide_switch,CREATE_TIME,UPDATE_TIME  FROM router_config_1 
 
 WHERE (device_id = ?) ORDER BY id ASC LIMIT ? ::: [1, 10]
2022-04-12 20:18:03.634  INFO 20788 --- [nio-8950-exec-5] ShardingSphere-SQL                       : Actual SQL: ds1 ::: SELECT  id,device_id,wifi_name,wifi_password,encrypt_type,admin_password,wifi_switch,hide_switch,CREATE_TIME,UPDATE_TIME  FROM router_config_2 
 
 WHERE (device_id = ?) ORDER BY id ASC LIMIT ? ::: [1, 10]

分库分表的分页查询相对复杂一些，会先将同一个数据库中的多个分表数据联合查询，得到单库总条数，然后再汇总所有分库数据得到总的数据条数；每一个分库中的每一个分表都执行一条分页查询SQL，得到分页结果，最后将所有的结果聚合返回

6 推荐参考资料

Use ShardingSphere Data Source in Spring

ShardingSphere-example 官方示例

shardingsphere 5.0控制台输出sql语句

【Sharding-JDBC】（一） 整合mybatis-plus 水平分表

7 Github 源码

Gtihub 源码地址 : https://github.com/Flying9001/springBootDemo

个人公众号:404Code,分享半个互联网人的技术与思考，感兴趣的可以关注.
404Code

Flying9001
关注
  • 7
    点赞
  • 踩
  • 29
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
专栏目录
sharding-jdbc-spring-boot-starter 整合 mybatis-plus-boot-starter + druid
蕃薯耀的博客
10-21 1649
sharding-jdbc-spring-boot-starter 整合 mybatis-plus-boot-starter +druid sharding-jdbc整合mybatis-plus +druid shardingsphere分库分表 ================================ ©Copyright 蕃薯耀 2020-10-21 https://www.c...
基于yml 配置方式 ，实现springBoot+sharding-jdbc+mybatis-plus 实现分库分表，读写分离，以及全局表，子表的配置
10-26
1、基于yml 配置方式 ，实现springBoot+sharding-jdbc+mybatis-plus 实现分库分表，读写分离，以及全局表，子表的配置。 2、实现mybatis-plus 整合到springboot 详细使用请看 测试用例
SpringBoot+Mybatis-Plus整合Sharding-JDBC5.1.1实现单库分表【全网最新】
小王博客基地
05-30 1344
一、前言 小编最近一直在研究关于分库分表的东西，前几天docker安装了mycat实现了分库分表，但是都在说mycat的bug很多。很多人还是倾向于shardingsphere，其实他是一个全家桶，有JDBC、Proxy 和 Sidecar组成，小编今天以最简单的JDBC来简单整合一下！ 现在最新版已经是5.1.1，经过一天的研究用于解决了所有问题，完成了单库分表！！ 想了解4.0.0版本的可以看一下小编刚刚写的：SpringBoot+Mybatis-Plus整合Sharding-JDBC4.0.0实现单库
ShardingSphere:SpringBoot2+MybatisPlus+读写分离+分库分表
12-24
ShardingSphere:SpringBoot2+MybatisPlus+读写分离+分库分表课程目标快速的掌握读写分离+分表的实战，即插即用适用人群IT从业人员，开发人员，Java从业者，互联网从业者，性能调优人群课程简介ShardingSphere是一套开源的分布式数据库中间件解决方案组成的生态圈。它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（计划中）这3款相互独立的产品组成，shardingSphere定位为关系型数据库中间件。 Sharding-JDBCSharding-JDBC是Sharding-Sphere的第一个产品，也是Sharding-Sphere的前身，是当当网开源的一个产品。定位为轻量级的Java框架，在Java的JDBC层提供额外服务。 它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。他们均提供标准化的数据分片、读写分离、柔性事务和数据治理功能，可适用于如Java同构、异构语言、容器、云原生等各种多样化的应用场景。Sharding-JDBC可以通过Java，YAML，Spring命名空间和Spring Boot Starter四种方式配置，开发者可根据场景选择适合的配置方式。课程特色 本章节以尽量短的时间，为使用者提供最简单的ShardingSphere的快速入门。课程说明该课程属于系列课程，分为读写分离，分库不分表，不分库分表，分库分表，读写分离+分库分表共5个回合。本课程属于其中一个回合，请各位小哥哥们注意，课程的标题哦~
sharding-jdbc如何实现分页查询
最新发布
Hi，我是sharkchili，CSDN Java领域博客专家，开源项目JavaGuide维护人员，我想写一些有意思的东西，希望对你有帮助。
05-10 1330
但由于每个结果集的记录是有序的，因此ShardingSphere每次比较仅获取各个分片的当前结果集记录，驻留在内存中的记录仅为当前路由到的分片的结果集的当前游标指向而已。分表分页查询则比较粗暴，它会将对应分页及之前的数据全部查询来，然后进行排序，跳过对应页码的数据后，再取出对应量级的数据返回。数据的深分页查询发现，它的做法和我们上文源码所说的一致，就是将当前页以及之前的结果全部加载到内存中，所以笔者认为使用。逻辑和之前差不多，就是通过轮询优先队列中的每一组分表对象的队首元素，将其存到。
Mybatis-Plus官方分库分表神器，一个依赖轻松搞定！
石杉的架构笔记
06-27 2391
文章来源：http://suo.nz/18CuNV目录前言主要功能使用前言今天介绍一个 MyBatis - Plus 官方发布的神器：mybatis-mate 为 mp 企业级模块，支持分库分表，数据审计、数据敏感词过滤（AC 算法），字段加密，字典回写（数据绑定），数据权限，表结构自动生成 SQL 维护等，旨在更敏捷优雅处理数据。主要功能如下：字典绑定字段加密数据脱敏表...
springboot整合mybatis-plus+shardingjdbc4.x总结
qq446152019的博客
08-19 452
springboot整合mybatis-plus+shardingjdbc4.x总结 背景 随着业务量的增长，业务表单表记录已经达到百万级别，并且仍在不断增加，查询效率也不断下降，查询时间长，经过讨论决定单库分表。技术选型沿用公司技术栈shardingjdbc，通过看官方文档，发现最新版出到5.x，但是文档个别使用描述不甚清楚，及网上使用5.x资料颇少，所以退求其次选择4.x版本。 直接开始 pom springboot+mybatis-plus部分maven就不贴了，网上资料大把，直接说shardingj
Mybatis Plus 数据分片-水平分库分表策略
逸轩
01-27 1926
在实际应用中，需要根据业务需求和数据量的大小，选择合适的分片键和分片规则，以达到提高数据库性能和可扩展性的目的。在使用MyBatis Plus进行数据分片时，需要谨慎处理跨库跨表的查询，注意数据迁移和扩容的问题，以确保整个数据分片系统的稳定性和可维护性。数据分片是一种处理大量数据的有效策略，但在实施过程中需要注意一些关键的事项，以确保分片系统的性能、稳定性和可维护性。分片规则的设计直接关系到数据分片的正确性，以及每个分片的数据能否被正确路由到相应的库和表中。选择合适的分片键是数据分片中至关重要的一步。
Spring Boot 集成 Sharding-JDBC + Mybatis-Plus 实现分库分表功能
09-07
主要介绍了Spring Boot 集成 Sharding-JDBC + Mybatis-Plus 实现分库分表功能,本文通过实例代码给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值，需要的朋友可以参考下
SpringBoot+Mybatis-Plus整合Sharding-JDBC5.1.1实现单库分表【全网最新】.doc
07-09
SpringBoot+Mybatis-Plus整合Sharding-JDBC5.1.1实现单库分表【全网最新】.doc
Java+Springboot+mybatis+sharding jdbc 实现分库分表
10-28
本项目基于Java、SpringBoot、MyBatis以及ShardingJDBC实现了一个分库分表的解决方案，旨在帮助开发者理解并掌握这一技术。以下是关于这些技术的详细介绍： **Java**： Java是一种广泛使用的面向对象的编程语言，...
sharding + mybatis-plus 分库分表
02-15
sharding-jdbc 分库分表 CRUD
Springboot2+JPA|MyBatis+Sharding-JDBC示例
07-24
下面将详细阐述这些技术及其在"Springboot2+JPA|MyBatis+Sharding-JDBC示例"中的应用。 首先，Spring Boot是基于Spring框架的轻量级开发工具，它简化了初始化、配置和部署Spring应用程序的过程。Spring Boot 2.x...
Springboot + Mybatis-Plus + Sharding-JDBC
abc20090208的博客
03-16 3165
Springboot + Mybatis-Plus + Sharding-JDBC 现在开源的分库分表方案中, 常用的就是MyCat和Sharding-JDBC, MyCat需要有专门的服务器, 通常要的配置也比较高, 公司之前就是专门部署了两台64G的MyCat服务器, 后面为了节省资源, 逐步切换成Sharding-JDBC 这里主要介绍Springboot, Mybatis-Plus, Sh...
springboott整合mybatis-plus和sharding-jdbc实现分库分表和读写分离（含完整项目代码）
gorylee的博客
10-04 1998
springboott整合mybatis-plus和sharding-jdbc实现分库分表和读写分离（含完整项目代码） 一、整合sharding-jdbc 关于springboot整合sharding-jdbc官网有4种方式，而网上千篇一律都是用.properties文件。我在这里主要是用yml+bean两种方式结合，bean主要是为了实现分片键分片规则更灵活。 1.1引入maven依赖 这里有个小坑，我一开始引入是最新的sharding-jdbc和mybatis-plus的依赖，结果启动项目直接报错。所以
SpringBoot+Mybatis-Plus整合Sharding-JDBC实现单库分表及其相关测试
小王博客基地
05-27 3966
文章目录一、前言二、导入maven依赖三、4.1.1版本bug解决四、新建三张分表1. 新建二张表2. 数据库结构五、框架全局展示1. User实体类2. controller3. mapper4. application.yml配置5. 启动类六、测试插入九条数据1. 插入数据2. 展示插入数据3. 单个查询4. 全查询5. 分页查询6. 非分片属性查询七、总结 一、前言 小编最近一直在研究关于分库分表的东西，前几天docker安装了mycat实现了分库分表，但是都在说mycat的bug很多。很多人还是倾
springboot + mybatis-plus 集成 sharding-jdbc
weixin_43421537的博客
03-14 3072
springboot+mybatis-plus+sharding-jdbc集成过程；mybatis-plus+sharding-jdbc集成后无法使用count(1)函数问题；报错T4CConnection.getSchema()方法不存在问题
springboot+mybatis-plus基于拦截器实现分表
(づ￣ 3￣)づxl
11-07 2184
前言 最近在工作遇到数据量比较多的情况，单表压力比较大，crud的操作都受到影响，于是开发一款基于mybatis-plus的拦截器
SpringBoot SpringBoot 基础篇 3 SpringBoot 整合第三方技术 3.5 SpringBoot 整合 Mybatis-Plus
谢谢你们的关注
11-11 942
SpringBoot SpringBoot 基础篇 3 SpringBoot 整合第三方技术 3.5 SpringBoot 整合 Mybatis-Plus
springboot mybatis plus sharding-jdbc
09-14
您好！关于Spring Boot、MyBatis Plus和Sharding-JDBC的集成问题，我可以提供一些基本指导。首先，确保您已经在Spring Boot项目中成功集成了MyBatis Plus和Sharding-JDBC的依赖。 在pom.xml文件中添加MyBatis Plus和Sharding-JDBC的依赖： ```xml <!-- MyBatis Plus --> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>${mybatis-plus.version}</version> </dependency> <!-- Sharding-JDBC --> <dependency> <groupId>io.shardingjdbc</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>${sharding-jdbc.version}</version> </dependency> ``` 接下来，您需要配置Sharding-JDBC。在application.yml（或application.properties）文件中添加相应的配置信息。以下是一个示例： ```yaml spring: shardingsphere: datasource: names: ds0, ds1 # 数据源名称 ds0: url: jdbc:mysql://localhost:3306/db0?serverTimezone=UTC username: root password: root driver-class-name: com.mysql.jdbc.Driver ds1: url: jdbc:mysql://localhost:3306/db1?serverTimezone=UTC username: root password: root driver-class-name: com.mysql.jdbc.Driver sharding: tables: user: actualDataNodes: ds$->{0..1}.user tableStrategy: inline: shardingColumn: id algorithmExpression: user$->{id % 2} keyGenerator: type: SNOWFLAKE ``` 这是一个简单的配置示例，其中包含了两个数据源（ds0和ds1），以及一个名为user的分片表的配置。您可以根据您的实际需求进行调整。 最后，您可以在MyBatis Plus的Mapper接口中使用Sharding-JDBC提供的分片功能。例如： ```java @Mapper public interface UserMapper extends BaseMapper<User> { // 自定义SQL查询方法 List<User> selectUserById(@Param("id") Long id); } ``` 这只是一个简单的示例，您可以根据自己的业务需求进行扩展和定制。 希望这些信息对您有所帮助！如果您还有其他问题，请随时提问。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值