springboot2.1.8+sharding-jdbc3.x自定义分片

最新推荐文章于 2024-07-29 06:30:00 发布

longfulong

最新推荐文章于 2024-07-29 06:30:00 发布

阅读量945

点赞数

分类专栏：框架使用文章标签： springboot mybatis-plus sharding-jdbc

本文链接：https://blog.csdn.net/longfulong/article/details/102505871

版权

该博客介绍了如何在SpringBoot2.1.8项目中集成Sharding-JDBC进行数据库分片，详细讲解了从引入依赖、配置application.properties，到自定义分片策略、创建实体类和数据库表，最后进行测试的全过程。

摘要由CSDN通过智能技术生成

springboot2.1.8+sharding-jdbc3.x自定义分片

引入jar

引入jar

https://start.spring.io/

<parent>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-parent</artifactId>
	<version>2.1.8.RELEASE</version>
	<relativePath/> 
</parent>
  
  <dependencies>
  
    <dependency>
	<groupId>com.baomidou</groupId>
	<artifactId>mybatis-plus-boot-starter</artifactId>
	<version>3.2.0</version>
   </dependency>
  
   <dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
        <version>8.0.17</version>
  </dependency>
  <dependency>
	<groupId>io.shardingsphere</groupId>
	<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
	<version>3.0.0</version>
  </dependency>
  <dependency>
	<groupId>org.projectlombok</groupId>
	<artifactId>lombok</artifactId>
	<version>1.18.10</version>
	<optional>true</optional>
  </dependency>
  
  </dependencies>

添加application.properties配置

#mybatis-plus.mapper-locations=mybatis/mapper/*.xml
mybatis-plus.type-aliases-package=com.example.entity
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
spring.main.allow-bean-definition-overriding=true

#连接池为druid时的配置
#sharding.jdbc.datasource.slave1.type=com.alibaba.druid.pool.DruidDataSource
#sharding.jdbc.datasource.slave1.url=jdbc:mysql://localhost:3306/slave1?useUnicode=true&characterEncoding=utf8&useSSL=true&allowMultiQueries=true&verifyServerCertificate=false&serverTimezone=Asia/Shanghai
#https://shardingsphere.apache.org/document/legacy/3.x/document/cn/manual/sharding-jdbc/configuration/config-spring-boot/

#数据分片 + 读写分离
sharding.jdbc.datasource.names=master0,master1,master0slave0,master0slave1,master1slave0,master1slave1
sharding.jdbc.config.sharding.props.sql.show=true

sharding.jdbc.datasource.master0.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.master0.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.master0.jdbcUrl=jdbc:mysql://localhost:3306/master0?useUnicode=true&characterEncoding=utf8&useSSL=true&allowMultiQueries=true&verifyServerCertificate=false&serverTimezone=Asia/Shanghai
sharding.jdbc.datasource.master0.username=root
sharding.jdbc.datasource.master0.password=aaaa

sharding.jdbc.datasource.master0slave0.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.master0slave0.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.master0slave0.jdbcUrl=jdbc:mysql://localhost:3306/master0slave0?useUnicode=true&characterEncoding=utf8&useSSL=true&allowMultiQueries=true&verifyServerCertificate=false&serverTimezone=Asia/Shanghai
sharding.jdbc.datasource.master0slave0.username=root
sharding.jdbc.datasource.master0slave0.password=aaaa
sharding.jdbc.datasource.master0slave1.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.master0slave1.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.master0slave1.jdbcUrl=jdbc:mysql://localhost:3306/master0slave1?useUnicode=true&characterEncoding=utf8&useSSL=true&allowMultiQueries=true&verifyServerCertificate=false&serverTimezone=Asia/Shanghai
sharding.jdbc.datasource.master0slave1.username=root
sharding.jdbc.datasource.master0slave1.password=aaaa

sharding.jdbc.datasource.master1.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.master1.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.master1.jdbcUrl=jdbc:mysql://localhost:3306/master1?useUnicode=true&characterEncoding=utf8&useSSL=true&allowMultiQueries=true&verifyServerCertificate=false&serverTimezone=Asia/Shanghai
sharding.jdbc.datasource.master1.username=root
sharding.jdbc.datasource.master1.password=aaaa

sharding.jdbc.datasource.master1slave0.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.master1slave0.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.master1slave0.jdbcUrl=jdbc:mysql://localhost:3306/master1slave0?useUnicode=true&characterEncoding=utf8&useSSL=true&allowMultiQueries=true&verifyServerCertificate=false&serverTimezone=Asia/Shanghai
sharding.jdbc.datasource.master1slave0.username=root
sharding.jdbc.datasource.master1slave0.password=aaaa
sharding.jdbc.datasource.master1slave1.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.master1slave1.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.master1slave1.jdbcUrl=jdbc:mysql://localhost:3306/master1slave1?useUnicode=true&characterEncoding=utf8&useSSL=true&allowMultiQueries=true&verifyServerCertificate=false&serverTimezone=Asia/Shanghai
sharding.jdbc.datasource.master1slave1.username=root
sharding.jdbc.datasource.master1slave1.password=aaaa

sharding.jdbc.config.sharding.tables.t_order.actual-data-nodes=ds$->{
   0..1}.t_order$->{
   0..1}
# 自定义分库分表算法
sharding.jdbc.config.sharding.tables.t_order.databaseStrategy.complex.shardingColumns=order_id,user_id
sharding.jdbc.config.sharding.tables.t_order.databaseStrategy.complex.algorithmClassName=com.example.sharding.algorithm.DbShardingAlgorithm

## 自定义分表算法
sharding.jdbc.config.sharding.tables.t_order.tableStrategy.complex.shardingColumns=order_id,user_id
sharding.jdbc.config.sharding.tables.t_order.tableStrategy.complex.algorithmClassName=com.example.sharding.algorithm.TableShardingAlgorithm
#自增列名称，缺省表示不使用自增主键生成器
sharding.jdbc.config.sharding.tables.t_order.key-generator-column-name=id

#自增列值生成器类名称，缺省表示使用默认自增列值生成器。该类需提供无参数的构造器
#sharding.jdbc.config.sharding.tables.<logic-table-name>.key-generator-class-name=
sharding.jdbc.config.sharding.tables.t_order_item.actual-data-nodes=ds$->{
   0..1}.t_order_item$->{
   0..1}

sharding.jdbc.config.sharding.tables.t_order_item.databaseStrategy.complex.shardingColumns=order_id,user_id
sharding.jdbc.config.sharding.tables.t_order_item.databaseStrategy.complex.algorithmClassName=com.example.sharding.algorithm.DbShardingAlgorithm

## 自定义分表算法
sharding.jdbc.config.sharding.tables.t_order_item.tableStrategy.complex.shardingColumns=order_id,user_id
sharding.jdbc.config.sharding.tables.t_order_item.tableStrategy.complex.algorithmClassName=com.example.sharding.algorithm.TableShardingAlgorithm
sharding.jdbc.config.sharding.tables.t_order_item.key-generator-column-name=id

#绑定表指分片规则一致的主表和子表
sharding.jdbc.config.sharding.binding-tables[0]=t_order,t_order_item

#sharding.jdbc.config.sharding.binding-tables[1]=t_order,t_order_log
#指所有的分片数据源中都存在的表，表结构和表中的数据在每个数据库中均完全一致。适用于数据量不大且需要与海量数据的表进行关联查询的场景，例如：字典表。
#sharding.jdbc.config.sharding.broadcast-tables=t_config
sharding.jdbc.config.sharding.master-slave-rules.ds0.master-data-source-name=master0
sharding.jdbc.config.sharding.master-slave-rules.ds0.slave-data-source-names=master0slave0, master0slave1
sharding.jdbc.config.sharding.master-slave-rules.ds1.master-data-source-name=master1
sharding.jdbc.config.sharding.master-slave-rules.ds1.slave-data-source-names=master1slave0, master1slave1

#https://shardingsphere.apache.org/document/legacy/3.x/document/cn/features/sharding/concept/sql/
#https://shardingsphere.apache.org/document/legacy/3.x/document/cn/manual/sharding-jdbc/configuration/config-spring-boot/
#https://www.cnblogs.com/sky-chen/p/11393659.html

自定义分片及分表算法

package com.example.sharding.algorithm;

import io.shardingsphere.api.algorithm.sharding.ListShardingValue;
import io.shardingsphere.api.algorithm.sharding.ShardingValue;
import io.shardingsphere.api.algorithm.sharding