当前位置:首页 > 数据库 > springboot+sharding-jdbc+mysql分库分表

springboot+sharding-jdbc+mysql分库分表

2022年11月08日 20:53:55数据库8

项目地址

github:github.com/apache/shar…

gitee:gitee.com/Sharding-Sp…最新版为5.1.2

实战

本位以5.1.1为例

1. 下载5.1.1-release的源码

修改pom.xml文件

//增加依赖
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-web</artifactId>
  <version>2.3.7.RELEASE</version>
</dependency>
<dependency>
  <groupId>com.baomidou</groupId>
  <artifactId>mybatis-plus-boot-starter</artifactId>
  <version>3.5.1</version>
</dependency>
<dependency>
  <groupId>com.baomidou</groupId>
  <artifactId>mybatis-plus-core</artifactId>
  <version>3.5.1</version>
</dependency>
复制代码

找到shardingsphere-example-generator 修改config.yaml文件如下

products:
  - jdbc

# supported: memory,cluster-zookeeper,cluster-etcd,standalone-file
modes: 
  - standalone-file

# supported: local
transactions: 
  - local

# supported: sharding,readwrite-splitting,encrypt,db-discovery
features:
    - sharding
#  - readwrite-splitting
#  - encrypt
#  - shadow
#  - db-discovery

# supported:jdbc,spring-boot-starter-jdbc,spring-boot-starter-jpa,spring-boot-starter-mybatis,spring-namespace-jdbc,spring-namespace-jpa,spring-namespace-mybatis
frameworks:
  - spring-boot-starter-mybatis
  #- jdbc
  #- spring-boot-starter-jdbc
  #- spring-boot-starter-jpa
  #- spring-namespace-jdbc
  #- spring-namespace-jpa
  #- spring-namespace-mybatis

props:
  host: localhost
  port: 3306
  username: root
  password: 123456
复制代码

org.apache.shardingsphere.example.generator.core.ExampleGenerator#OUTPUT_PATH 可修改生成代码的输出路径

2. 编译打包后

找到启动入口并运行:org.apache.shardingsphere.example.generator.ExampleGeneratorMain#main

会在指定目录生成项目

2.1 修改文件

根据需要修改application.properties 配置分库分表规则


spring.shardingsphere.datasource.ds-0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-0.jdbc-url=jdbc:mysql://localhost:3306/demo_ds_0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds-0.username=root
spring.shardingsphere.datasource.ds-0.password=123456
spring.shardingsphere.datasource.ds-0.max-active=16

spring.shardingsphere.datasource.ds-1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-1.jdbc-url=jdbc:mysql://localhost:3306/demo_ds_1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds-1.username=root
spring.shardingsphere.datasource.ds-1.password=123456
spring.shardingsphere.datasource.ds-1.max-active=16

spring.shardingsphere.datasource.ds-2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-2.jdbc-url=jdbc:mysql://localhost:3306/demo_ds_2?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds-2.username=root
spring.shardingsphere.datasource.ds-2.password=123456
spring.shardingsphere.datasource.ds-2.max-active=16

spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-column=user_id
spring.shardingsphere.rules.sharding.default-database-strategy.standard.sharding-algorithm-name=database-inline

spring.shardingsphere.rules.sharding.binding-tables=t_order,t_order_item
spring.shardingsphere.rules.sharding.broadcast-tables=t_address

spring.shardingsphere.rules.sharding.tables.t_order.actual-data-nodes=ds-$->{0..2}.t_order_$->{0..2}
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-column=order_id
spring.shardingsphere.rules.sharding.tables.t_order.table-strategy.standard.sharding-algorithm-name=t-order-inline

spring.shardingsphere.rules.sharding.tables.t_order.key-generate-strategy.column=order_id
spring.shardingsphere.rules.sharding.tables.t_order.key-generate-strategy.key-generator-name=snowflake

spring.shardingsphere.rules.sharding.tables.t_order_item.actual-data-nodes=ds-$->{0..2}.t_order_item_$->{0..2}
spring.shardingsphere.rules.sharding.tables.t_order_item.table-strategy.standard.sharding-column=order_id
spring.shardingsphere.rules.sharding.tables.t_order_item.table-strategy.standard.sharding-algorithm-name=t-order-item-inline

spring.shardingsphere.rules.sharding.tables.t_order_item.key-generate-strategy.column=order_item_id
spring.shardingsphere.rules.sharding.tables.t_order_item.key-generate-strategy.key-generator-name=snowflake

spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.props.algorithm-expression=ds-$->{user_id % 3}
spring.shardingsphere.rules.sharding.sharding-algorithms.t-order-inline.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.t-order-inline.props.algorithm-expression=t_order_$->{order_id % 3}
spring.shardingsphere.rules.sharding.sharding-algorithms.t-order-item-inline.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.t-order-item-inline.props.algorithm-expression=t_order_item_$->{order_id % 3}

spring.shardingsphere.rules.sharding.key-generators.snowflake.type=SNOWFLAKE

spring.shardingsphere.props.sql-show=true

# 引入了mybatis-plus
mybatis-plus.configuration.map-underscore-to-camel-case=true
mybatis-plus.configuration.auto-mapping-behavior=full
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
mybatis-plus.mapper-locations=classpath*:mappers/**/*Mapper.xml
复制代码

找到main方法入口 增加建库代码

        try {
            Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
            conexion = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/mysql?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true", "root", "123456");
            Statement st = (Statement) conexion.createStatement();
            st.executeUpdate("CREATE DATABASE if not exists demo_ds_0");
            st.executeUpdate("CREATE DATABASE if not exists demo_ds_1");
            st.executeUpdate("CREATE DATABASE if not exists demo_ds_2");

        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            if (conexion != null) {
                conexion.close();
            }
        }
复制代码

注释掉 org.apache.shardingsphere.example.sharding.spring.boot.starter.mybatis.service.ExampleService#run 方法56行 this.cleanEnvironment();

根据业务修改mapper.xml文件、 修改建表语句

2.2 运行main

运行后发现已经生成好指定配置的库、表

springboot+sharding-jdbc+mysql分库分表 _ JavaClub全栈架构师技术笔记

读写分离配置

# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements.  See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to You under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License.  You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#

# mybatis.mapper-locations=classpath*:mappers/*Mapper.xml

spring.shardingsphere.datasource.names=ds-0,ds-1,ds-2

spring.shardingsphere.datasource.ds-0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-0.jdbc-url=jdbc:mysql://localhost:3306/demo_ds_0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds-0.username=root
spring.shardingsphere.datasource.ds-0.password=123456
spring.shardingsphere.datasource.ds-0.max-active=16

spring.shardingsphere.datasource.ds-1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-1.jdbc-url=jdbc:mysql://localhost:3306/demo_ds_1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds-1.username=root
spring.shardingsphere.datasource.ds-1.password=123456
spring.shardingsphere.datasource.ds-1.max-active=16

spring.shardingsphere.datasource.ds-2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-2.jdbc-url=jdbc:mysql://localhost:3306/demo_ds_2?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.ds-2.username=root
spring.shardingsphere.datasource.ds-2.password=123456
spring.shardingsphere.datasource.ds-2.max-active=16

spring.shardingsphere.rules.readwrite-splitting.data-sources.readwrite_ds.type=Static
spring.shardingsphere.rules.readwrite-splitting.data-sources.readwrite_ds.props.write-data-source-name=ds-0
spring.shardingsphere.rules.readwrite-splitting.data-sources.readwrite_ds.props.read-data-source-names=ds-1,ds-2
spring.shardingsphere.rules.readwrite-splitting.data-sources.readwrite_ds.load-balancer-name=round_robin
spring.shardingsphere.rules.readwrite-splitting.load-balancers.round_robin.type=ROUND_ROBIN

spring.shardingsphere.props.sql-show=true

# 引入了mybatis-plus
mybatis-plus.configuration.map-underscore-to-camel-case=true
mybatis-plus.configuration.auto-mapping-behavior=full
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
mybatis-plus.mapper-locations=classpath*:mappers/**/*Mapper.xml

 

作者:yojhon
来源链接:https://blog.csdn.net/yojhon/article/details/103970076

版权声明:
1、JavaClub(https://www.javaclub.cn)以学习交流为目的,由作者投稿、网友推荐和小编整理收藏优秀的IT技术及相关内容,包括但不限于文字、图片、音频、视频、软件、程序等,其均来自互联网,本站不享有版权,版权归原作者所有。

2、本站提供的内容仅用于个人学习、研究或欣赏,以及其他非商业性或非盈利性用途,但同时应遵守著作权法及其他相关法律的规定,不得侵犯相关权利人及本网站的合法权利。
3、本网站内容原作者如不愿意在本网站刊登内容,请及时通知本站(javaclubcn@163.com),我们将第一时间核实后及时予以删除。


本文链接:https://www.javaclub.cn/database/68543.html

分享给朋友:

“springboot+sharding-jdbc+mysql分库分表” 的相关文章

SpringBoot项目改为SpringCloud项目使用nacos作为注册中心

SpringBoot项目改为SpringCloud项目使用nacos作为注册中心

本章讲解的是在不改变原有业务的情况下将springboot改为springcloud项目使用nacos作为注册中心 首先在官网下载好nacos注册中心,在bin目录中startup.cmd文件为启动命令默认端口号是8888 接下来修改原有项目依赖...

SpringBoot之两种配置文件application.properties与application.yml文件的区别及读取方式

SpringBoot之两种配置文件application.properties与application.yml文件的区别及读取方式

为什么会使用properties文件 在软件开发的过程中,会经常遇到一些配置说是需要切换的,例如数据库的配置地址,用户名和密码,可以选择在常量中进行配置,但是当下次需要修改时,则需要将Java代码进行重新编译,再重启服务器。这样导致很繁琐。properties文件的出现使得后期项目的维护中,...

SpringBoot整合消息队列工具kafka

SpringBoot整合消息队列工具kafka

一、前言 之前整理了kafka在windows下的安装过程,也通过shell命令进行了消息产生者和消息消费者的创建及消息发送,所以想到把kafka与最流行的SpringBoot的框架进行整合,与项目结合,进行消息的发送。 二、整合开始 1.SpringBoot工程搭建,此处不多讲,可以...

SpringBoot+Mybatis框架整合Shiro权限管理

SpringBoot+Mybatis框架整合Shiro权限管理

一、前言 ​ 之前曾分享过一个关于shiro的认证原理的文章,分享了一下shiro的认证流程与shiro中的名词解释,其实shiro作为一款轻量级框架,被应用在各种中小型及大型企业的登录认证和用户授权的模块,有小伙伴称,在用SpringBoot框架,之前用过xml方式进行配置shiro框架,...

SpringCloud之断路器Hystrix

SpringCloud之断路器Hystrix

一、Hystrix是什么 之前文章讲过,SpringCloud中加入了断路器功能,当请求接口时,服务提供者出现宕机问题,没有一个好的容错机制的情况下,是有可能造成雪崩效应,造成整个服务瘫痪,从而导致系统挂掉。Hystrix是Netflix提供的一个延迟和容错库,可以在服务出现问题时,进行及时...

SpringBoot整合Redis缓存

SpringBoot整合Redis缓存

一、前言 之前分享过关于Redis集群如何搭建的,最近有小伙伴儿说分享一下Redis如何使用SpringBoot的整合,项目中经常使用的就是Redis,本次整合的是Redis单机版,后续会分享Redis集群版本,请多提出自己的意见。 二、开始整合 1.首先需要搭建Redis服务器,可参...

MySQL数据库(基础)

MySQL数据库(基础)

目录 1. 数据库概念 1.1 数据库是干嘛的? 1.2 数据库和数据结构是啥关系? ​1.3 两种类型的数据库 2. MySQL数据库 2.1 MySQL数据库概念 2.2 MySQL基本操作  2.2.1 建立...

JDBC连接时所犯错误1.字符集设置不合适2.连接MySQL8.0社区版时时区不一致3..包名不能以Java.命名4.驱动被弃用

Microsoft JDBC Driver 的主页为:https://msdn.microsoft.com/en-us/data/aa937724.aspx 下载所需驱动 今天连接时报了四次错,记录下来 1.java.sql.SQLException:...

Spring Cloud实战|3.SpringCloud 整合common模块

Spring Cloud实战|3.SpringCloud 整合common模块

手把手教你从0开始搭建spring cloud alibaba 脚手架,关注公众号“AI码师” 获取项目完整源码 视频地址:视频教程 创建新模块 ams-common 当前目录结构 基础包封装 引入必备依...

SpringCloud实战|8.SpringCloud 整合 seata1.3

SpringCloud实战|8.SpringCloud 整合 seata1.3

手把手教你从0开始搭建spring cloud alibaba 脚手架,关注公众号“AI码师” 获取项目完整源码 前言 本篇文章介绍了如何在Spring Cloud项目中引入seata,以及seata的安装过程 开整...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。