Sprting boot2 整合shardingsphere + mybatisPlus 实现(分库分表+读写分离)

于 2021-03-21 17:16:55 发布
阅读量859 收藏 1
点赞数
CC 4.0 BY-SA版权
分类专栏： 分库分表 shardingsphere mybatis-plus 文章标签： sharding mybatis
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/penganchunyan/article/details/115051228
本文介绍了如何在Spring Boot 2项目中整合ShardingSphere和MybatisPlus，实现分库分表及读写分离。详细步骤包括添加依赖、配置、数据库设计、代码实现和自定义分库规则，最后通过测试类验证功能成功。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

前言

Apache ShardingSphere官网 http://shardingsphere.apache.org/index_zh.html
ShardingSphere是一套开源的分布式数据库中间件解决方案组成的生态圈，它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（计划中）这3款相互独立的产品组成。 他们均提供标准化的数据分片、分布式事务和数据库治理功能，可适用于如Java同构、异构语言、云原生等各种多样化的应用场景。

ShardingSphere定位为关系型数据库中间件，旨在充分合理地在分布式的场景下利用关系型数据库的计算和存储能力，而并非实现一个全新的关系型数据库。 它与NoSQL和NewSQL是并存而非互斥的关系。NoSQL和NewSQL作为新技术探索的前沿，放眼未来，拥抱变化，是非常值得推荐的。反之，也可以用另一种思路看待问题，放眼未来，关注不变的东西，进而抓住事物本质。 关系型数据库当今依然占有巨大市场，是各个公司核心业务的基石，未来也难于撼动，我们目前阶段更加关注在原有基础上的增量，而非颠覆。

Sping boot 2 添加依赖 pom.xml

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>2.3.7.RELEASE</version>
        </dependency>
        <!--spring jpa-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <version>2.3.7.RELEASE</version>
        </dependency>

        <!--mysql驱动包-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.22</version>
            <scope>runtime</scope>
        </dependency>

        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.16</version>
        </dependency>

        <!--mybatis-plus 依赖-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.1</version>
        </dependency>
        <!--Mybatis-Plus generator -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.4.1</version>
        </dependency>
        
         <!--shardingsphere 依赖-->
         <dependency>
                <groupId>org.apache.shardingsphere</groupId>
                <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
                <version>4.1.1</version>
          </dependency>

          <dependency>
                <groupId>org.apache.shardingsphere</groupId>
                <artifactId>sharding-jdbc-spring-namespace</artifactId>
                <version>4.1.1</version>
          </dependency>

        <!--jackson依赖-->
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-annotations</artifactId>
            <version>2.11.3</version>
        </dependency>

        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
            <version>2.11.3</version>
        </dependency>
</dependencys>

application.properties配置

spring.profiles.active=dev
server.port=8080
server.tomcat.uri-encoding=utf-8
spring.application.name=boutique-api
spring.jpa.open-in-view=false

#定义Spring MVC Jackson日期输出格式,时区
spring.jackson.date-format=yyyy-MM-dd HH:mm:ss
spring.jackson.time-zone=GMT+8

#配置三主三从数据库方案
spring.shardingsphere.datasource.names=master0,master1,master2,master0slave0,master1slave0,master2slave0

spring.shardingsphere.datasource.master0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master0.jdbc-url=jdbc:mysql://10.16.101.25:3306/ESC_D0?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.master0.username=
spring.shardingsphere.datasource.master0.password=
spring.shardingsphere.datasource.master0slave0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master0slave0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master0slave0.jdbc-url=jdbc:mysql://10.16.101.26:3306/ESC_D0?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.master0slave0.username=
spring.shardingsphere.datasource.master0slave0.password=

spring.shardingsphere.datasource.master1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master1.jdbc-url=jdbc:mysql://10.16.101.27:3306/ESC_D1?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.master1.username=
spring.shardingsphere.datasource.master1.password=
spring.shardingsphere.datasource.master1slave0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master1slave0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master1slave0.jdbc-url=jdbc:mysql://10.16.101.28:3306/ESC_D1?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.master1slave0.username=
spring.shardingsphere.datasource.master1slave0.password=

spring.shardingsphere.datasource.master2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master2.jdbc-url=jdbc:mysql://10.16.101.29:3306/ESC_D2?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.master2.username=
spring.shardingsphere.datasource.master2.password=
spring.shardingsphere.datasource.master2slave0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master2slave0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master2slave0.jdbc-url=jdbc:mysql://10.16.101.30:3306/ESC_D2?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.master2slave0.username=
spring.shardingsphere.datasource.master2slave0.password=

###############################配置用户端分片规则
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..2}.t_order$->{0..1}
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order$->{order_id % 2}
#t_order主键生成策略
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE

spring.shardingsphere.sharding.tables.t_order_item.actual-data-nodes=ds$->{0..2}.t_order_item$->{0..1}
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order_item.table-strategy.inline.algorithm-expression=t_order_item$->{order_id % 2}
#t_order_item主键生成策略
spring.shardingsphere.sharding.tables.t_order_item.key-generator.column=order_item_id
spring.shardingsphere.sharding.tables.t_order_item.key-generator.type=SNOWFLAKE


###############################配置店铺端分片规则
spring.shardingsphere.sharding.tables.s_order.actual-data-nodes=ds$->{0..2}.s_order$->{0..1}
spring.shardingsphere.sharding.tables.s_order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.s_order.table-strategy.inline.algorithm-expression=s_order$->{order_id % 2}

spring.shardingsphere.sharding.tables.s_order_item.actual-data-nodes=ds$->{0..2}.s_order_item$->{0..1}
spring.shardingsphere.sharding.tables.s_order_item.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.s_order_item.table-strategy.inline.algorithm-expression=s_order_item$->{order_id % 2}


#配置绑定表，以免产生迪卡尔积
spring.shardingsphere.sharding.binding-tables[0]=t_order,t_order_item
spring.shardingsphere.sharding.binding-tables[1]=s_order,s_order_item
#配置广播表
spring.shardingsphere.sharding.broadcast-tables=t_config

#配置用户端自定义分库规则
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.sharding-column=client_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.precise-algorithm-class-name=com.evian.boutiqueutils.shardstrategy.ClientDataBasePreciseShardingAlgorithm
spring.shardingsphere.sharding.tables.t_order_item.database-strategy.standard.sharding-column=client_id
spring.shardingsphere.sharding.tables.t_order_item.database-strategy.standard.precise-algorithm-class-name=com.evian.boutiqueutils.shardstrategy.ClientDataBasePreciseShardingAlgorithm

#配置店铺端自定义分库规则
spring.shardingsphere.sharding.tables.s_order.database-strategy.standard.sharding-column=shop_id
spring.shardingsphere.sharding.tables.s_order.database-strategy.standard.precise-algorithm-class-name=com.evian.boutiqueutils.shardstrategy.ShopDataBasePreciseShardingAlgorithm
spring.shardingsphere.sharding.tables.s_order_item.database-strategy.standard.sharding-column=shop_id
spring.shardingsphere.sharding.tables.s_order_item.database-strategy.standard.precise-algorithm-class-name=com.evian.boutiqueutils.shardstrategy.ShopDataBasePreciseShardingAlgorithm

#配置读写分离
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=master0
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=master0slave0
spring.shardingsphere.sharding.master-slave-rules.ds1.master-data-source-name=master1
spring.shardingsphere.sharding.master-slave-rules.ds1.slave-data-source-names=master1slave0
spring.shardingsphere.sharding.master-slave-rules.ds2.master-data-source-name=master2
spring.shardingsphere.sharding.master-slave-rules.ds2.slave-data-source-names=master2slave0

#开启SQl日志
spring.shardingsphere.props.sql.show=true

数据库设计

在这里插入图片描述

mysql创建数据库脚本

/*
 Navicat MySQL Data Transfer

 Source Server         : 10.16.101.25-M(0-1)
 Source Server Type    : MySQL
 Source Server Version : 80023
 Source Host           : 10.16.101.25:3306
 Source Schema         : ESC_D0
最低0.47元/天 解锁文章

200万优质内容无限畅学
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值