介绍
学习sharding-jdbc的分库分表功能,以分库,分表,分库分表三种方式来实现,此文章只展示分库分表,为简洁其余两种分篇写
1.环境搭建
代码结构
数据库搭建(2个mysql数据库),并在2个库下都添加多个相同结构的表
![数据库](https://img-blog.csdnimg.cn/6e7f526bfc2d4712810fc22aea8d3bcc.png
pom.xml依赖
<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>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.20</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.0.5</version>
</dependency>
</dependencies>
application.yml (配置文件分离,结构更清晰)
spring:
main:
allow-bean-definition-overriding: true # 需要配置否则加载数据源报错 是否允许定义重名的bean对象覆盖原有的bean
profiles:
active: databasetable
mybatis-plus:
mapperLocations: classpath*:mapper/*Mapper.xml
application-databasetable.yml
# 数据源命名
spring.shardingsphere.datasource.names=book2021,book2022
# datasource1
spring.shardingsphere.datasource.book2021.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.book2021.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.book2021.url=jdbc:mysql://192.168.31.212:3306/book2021?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8&allowMultiQueries=true
spring.shardingsphere.datasource.book2021.username=root
spring.shardingsphere.datasource.book2021.password=123
# datasource2
spring.shardingsphere.datasource.book2022.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.book2022.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.book2022.url=jdbc:mysql://192.168.31.212:3307/book2022?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8&allowMultiQueries=true
spring.shardingsphere.datasource.book2022.username=root
spring.shardingsphere.datasource.book2022.password=123
#库名.表名
spring.shardingsphere.sharding.tables.book.actual-data-nodes=book202${1..2}.book_info_202${1..2}0${1..9},book202${1..2}.book_info_202${1..2}1${0..2}
# 数据库分片字段
spring.shardingsphere.sharding.tables.book.database-strategy.standard.sharding-column=create_time
# 数据库分片策略
spring.shardingsphere.sharding.tables.book.database-strategy.standard.preciseAlgorithmClassName=cn.xwl.xshardingjdbc.config.database.PreciseDatabaseShardingAlgorithm
spring.shardingsphere.sharding.tables.book.database-strategy.standard.rangeAlgorithmClassName=cn.xwl.xshardingjdbc.config.database.RangeDatabaseShardingAlgorithm
# 数据表分片字段
spring.shardingsphere.sharding.tables.book.table-strategy.standard.sharding-column=create_time
# 数据表分表策略
spring.shardingsphere.sharding.tables.book.table-strategy.standard.preciseAlgorithmClassName=cn.xwl.xshardingjdbc.config.table.PreciseTableShardingAlgorithm
spring.shardingsphere.sharding.tables.book.table-strategy.standard.rangeAlgorithmClassName=cn.xwl.xshardingjdbc.config.table.RangeTableShardingAlgorithm
# 打印sql
spring.shardingsphere.props.sql.show=true
2.代码编写
同分库,分表两篇文中的一致,这里不粘贴了,项目地址 本测试Demo的Gitee地址