Horizontal table sharding is to split the data of the same table into multiple tables according to certain rules in the same database. Multiple data sources use the dynamic-datasource of mybatis-plus. The sharding-jdbc database and table sharding are used. The database connection pool management is alibaba’s druid-spring-boot-starter
Tables in the same database
Table of Contents
1. Database table
2.Configuration
3.Introduced jar package
4. Database operations
5.The following is a runnable example
6.Reference
1. Database table
database:
Table Structure
CREATE TABLE `t_order_4` ( `order_id` bigint NOT NULL COMMENT 'order id', `price` decimal(10,2) NOT NULL COMMENT 'order price', `user_id` bigint NOT NULL COMMENT 'Order user id', `status` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT 'Order Status', `create_time` datetime DEFAULT NULL, PRIMARY KEY (`order_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC;
Split tables when configuring multiple data sources:
2. Configuration
application.yaml configuration:
--- #################### Database related configuration ####################
spring:
#Data source configuration items
autoconfigure:
exclude:
- com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure # Exclude Druid’s automatic configuration and use dynamic-datasource-spring-boot-starter to configure multiple data sources
#main:
# allow-bean-definition-overriding: true
datasource:
druid: # Druid [monitoring] related global configuration
web-stat-filter:
enabled: true
stat-view-servlet:
enabled: true
allow: #Set a whitelist, if left blank, all access will be allowed.
url-pattern: /druid/*
login-username: #Console management username and password
login-password:
filter:
stat:
enabled: true
log-slow-sql: true # Slow SQL logging
slow-sql-millis: 100
merge-sql: true
wall:
config:
multi-statement-allow: true
dynamic: #Multiple data source configuration
druid: # Druid [Connection Pool] related global configuration
initial-size: 5 # Initial number of connections
min-idle: 10 # Minimum number of connection pools
max-active: 20 #Maximum number of connection pools
max-wait: 600000 # Configure the waiting timeout for obtaining a connection, unit: milliseconds
time-between-eviction-runs-millis: 60000 # Configure how often to detect idle connections that need to be closed, unit: milliseconds
min-evictable-idle-time-millis: 300000 # Configure the minimum survival time of a connection in the pool, unit: milliseconds
max-evictable-idle-time-millis: 900000 # Configure the maximum survival time of a connection in the pool, unit: milliseconds
validation-query: SELECT 1 FROM DUAL # Configure to check whether the connection is valid
test-while-idle: true
test-on-borrow: false
test-on-return: false
primary: master
datasource:
master:
name: ruoyi-vue-pro
url: jdbc:mysql://127.0.0.1:3306/${spring.datasource.dynamic.datasource.master.name}?allowMultiQueries=true & amp;useUnicode=true & amp;useSSL=false & amp;characterEncoding=UTF -8 & amp;serverTimezone=Asia/Shanghai & amp;autoReconnect=true & amp;nullCatalogMeansCurrent=true # MySQL Connector/J 8.X connection example
# url: jdbc:mysql://127.0.0.1:3306/${spring.datasource.dynamic.datasource.master.name}?useSSL=false & amp;allowPublicKeyRetrieval=true & amp;useUnicode=true & amp;characterEncoding= UTF-8 & amp;serverTimezone=CTT # MySQL Connector/J 5.X connection example
# url: jdbc:postgresql://127.0.0.1:5432/${spring.datasource.dynamic.datasource.slave.name} # Example of PostgreSQL connection
# url: jdbc:oracle:thin:@127.0.0.1:1521:xe # Example of Oracle connection
# url: jdbc:sqlserver://127.0.0.1:1433;DatabaseName=${spring.datasource.dynamic.datasource.master.name} # SQLServer connection example
username: root
password: root
# username: sa
# password: JSm:g(*%lU4ZAkz06cd52KqT3)i1?H7W
slave: # Simulate slave library, which can be modified according to your own needs
name: ruoyi-vue-pro
url: jdbc:mysql://127.0.0.1:3306/${spring.datasource.dynamic.datasource.slave.name}?allowMultiQueries=true & amp;useUnicode=true & amp;useSSL=false & amp;characterEncoding=UTF -8 & amp;serverTimezone=Asia/Shanghai & amp;autoReconnect=true & amp;nullCatalogMeansCurrent=true # MySQL Connector/J 8.X connection example
# url: jdbc:mysql://127.0.0.1:3306/${spring.datasource.dynamic.datasource.slave.name}?useSSL=false & amp;allowPublicKeyRetrieval=true & amp;useUnicode=true & amp;characterEncoding= UTF-8 & amp;serverTimezone=CTT # MySQL Connector/J 5.X connection example
# url: jdbc:postgresql://127.0.0.1:5432/${spring.datasource.dynamic.datasource.slave.name} # Example of PostgreSQL connection
# url: jdbc:oracle:thin:@127.0.0.1:1521:xe # Example of Oracle connection
# url: jdbc:sqlserver://127.0.0.1:1433;DatabaseName=${spring.datasource.dynamic.datasource.slave.name} # SQLServer connection example
username: root
password: root
# username: sa
# password: JSm:g(*%lU4ZAkz06cd52KqT3)i1?H7W
#shardingsphere related configurations
# Sub-database and sub-table configuration
#shardingsphere related configurations
shardingsphere:
datasource:
names: m1 #The name of the configuration library, optional
m1: #Configure the data source information of the current m1 library
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/ruoyi-vue-pro?useUnicode=true & amp;characterEncoding=UTF-8 & amp;useSSL=false
username: root
password: root
sharding:
tables:
system_dept: # Specify the data distribution of the t_order table and configure the data node
actualDataNodes: m1.system_dept_$->{1..8}
tableStrategy: #Table splitting strategy, options are inline, standard, complex, hint, none
inline: # Specify the sharding strategy of the t_order table. The sharding strategy includes the sharding key and the sharding algorithm. #inline (row expression sharding strategy) - precise sharding based on a single sharding key.
shardingColumn: id
algorithmExpression: system_dept_$->{id % 8 + 1}
# keyGenerator: # Specify the primary key generation strategy of the t_order table as SNOWFLAKE
# column: order_id #Specify the primary key
# type: SNOWFLAKE #The primary key generation strategy is SNOWFLAKE
default-data-source-name: m1 #Data source that does not use the sub-table and sub-database strategy
props:
sql:
show: true
3.Introduced jar package
<-- Put this jar in front of the mybatisjar package -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-typehandlers-jsr310</artifactId>
<version>1.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
4. Database Operation
Normal operation is enough, no need to add @DS to specify the data source
5. The following is a runnable example
springboot2.7.17, mybatisplus3.5.3.1, dynamic-datasource3.5.1
Source code:
https://download.csdn.net/download/xiaobijia/88516542
-
6. Reference
[Sharding-JDBC] (1) Integrate mybatis-plus horizontal table_mybatisplus horizontal table-CSDN blog
Multiple data sources + database sub-database and sub-table_Multiple data sources, sub-database and sub-table_Amarone’s blog-CSDN blog
Cause: java.sql.SQLFeatureNotSupportedException: getObject with type-CSDN Blog
The new version of SpringBoot integrates sharding-jdbc and reports shardingsphere.shardingjdbc.jdbc.unsupported.AbstractUnsupportedOperationConnection.isValid solution – Laeni – Blog Park (cnblogs.com)