Background:The previous project to do read-write separation when using MybatisPlus dynamic data to do, a lot of places to use the @DS directly specify the read or write library to achieve the business; with the table data volume is getting bigger and bigger, and now intends to be a relatively large table for the level of splitting, ready to use the ShardingJDBC to achieve, but found that the two together is not so smooth, the Internet! Most of the articles are directly to the entire Sharding data source as a data source MybatisPlus, then the original @DS above the specified data source can not be directly used Sharding library and other logic, so I studied the source code, the realization of this logic, to the back of the need for friends to provide a case, to avoid wasting unnecessary time!
I. Version Selection
There are currently two main versions of ShardingJDBC, an early version of ShardingJDBC and ShardingSphere-JDBC from the ShardingSphere project
- Sharding-JDBC: Sharding-JDBC was originally released in 2016 by the then project sponsors. It was first introduced as a lightweight JDBC tier solution designed to solve the problem of database sharding and read/write separation.
- ShardingSphere : The ShardingSphere project evolved from the Sharding-JDBC project and was officially released in 2018.Apache ShardingSphere is dedicated to building a more complete distributed database management ecosystem and includes Sharding-JDBC, Sharding-Proxy, and Sharding-Sidecar.
The standalone ShardingJDBC has been discontinued, and the most used version is 4.1.1.
<dependency>
<groupId></groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
ShardingSphere project has been in the update iteration , ShardingSphere-JDBC is through the ShardingJDBC update iteration over , in the original code based on some optimization and new features to join , for the developer , mainly the configuration of the parameters have some adjustments . But the role of the parameters and the configuration of the same way as before;
Here I am using ShardingSphere-JDBC 5.2.1 directly for the convenience of new features that will be used later.
Official Help Documentation：/read/shardingsphere-5.1.0-zh/
<dependency>
<groupId></groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.2.1</version>
</dependency>
II. Project dependencies
The full Maven dependencies for the case are listed below:
<dependencies>
<dependency>
<groupId></groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId></groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId></groupId>
<artifactId>HikariCP</artifactId>
<version>3.4.5</version>
</dependency>
<dependency>
<groupId></groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.0</version>
</dependency>
<!-- read-write separation -->
<dependency>
<groupId></groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.3.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.33</version>
</dependency>
<!--Shardingjdbc-->
<dependency>
<groupId></groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.2.1</version>
<exclusions>
<exclusion>
<groupId></groupId>
<artifactId>snakeyaml</artifactId>
</exclusion>
</exclusions>
</dependency>
<!-- Add the correct version of the SnakeYAML shardingsphere-jdbcThere's a problem with the dependency version in there，will report an error-->
<dependency>
<groupId></groupId>
<artifactId>snakeyaml</artifactId>
<version>1.33</version>
</dependency>
<dependency>
<groupId></groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
III. Parameter Configuration
configure
server:
port: 8080
mybatis-plus:
mapper-locations: classpath*:mybatis/*.xml
type-aliases-package:
configuration:
map-underscore-to-camel-case: false
log-impl:
spring:
application:
name: sharding-jdbc-test
sharding-sphere:
datasource:
names: master,write,read,read2
master:
type:
driver-class-name:
jdbc-url: jdbc:mysql://127.0.0.1:3306/game_dev?characterEncoding=utf-8&useSSL=false&allowMultiQueries=true&autoReconnect=true&serverTimezone=Asia/Shanghai
username: root
password: 123456
write:
type:
driver-class-name:
jdbc-url: jdbc:mysql://127.0.0.1:3306/game_dev?characterEncoding=utf-8&useSSL=false&allowMultiQueries=true&autoReconnect=true&serverTimezone=Asia/Shanghai
username: root
password: 123456
read:
type:
driver-class-name:
jdbc-url: jdbc:mysql://127.0.0.1:3306/game_dev_read?characterEncoding=utf-8&useSSL=false&allowMultiQueries=true&autoReconnect=true&serverTimezone=Asia/Shanghai
username: root
password: 123456
read2:
type:
driver-class-name:
jdbc-url: jdbc:mysql://127.0.0.1:3306/game_dev_read?characterEncoding=utf-8&useSSL=false&allowMultiQueries=true&autoReconnect=true&serverTimezone=Asia/Shanghai
username: root
password: 123456
rules:
sharding:
tables:
team_msg:
## Here.customer-dsis the name of the data source for the read/write separation configured below
actual-data-nodes: customer-ds.team_msg_${0..1}
table-strategy:
standard:
sharding-column: id
sharding-algorithm-name: msg-id # corresponds to the followingsharding-algorithms
sharding-algorithms:
## Note the name here(for examplemsg-id)No underlining，The following parameters will not be loaded, resulting in a startup error
msg-id:
type: INLINE
props:
## utilizationidmodulus extraction algorithm
algorithm-expression: team_msg_${id % 2}
## Read/Write Separation Related
readwrite-splitting:
data-sources:
customer-ds:
load-balancer-name: customer-lb
static-strategy:
write-data-source-name: master
read-data-source-names: read,read2,write
load-balancers:
customer-lb:
## utilization自定义的复杂均衡算法
type: CUSTOM
props:
# Show real after processingsql
sql-show: true
Code Configuration
The most critical configuration is the need to register the MybatisPlus data source as a data source using shardingsphere-jdbc, and to ensure that the name of the data source and the original MybatisPlus data source is consistent, shardingSphereDataSource in fact, there is a Map to save all the configurations in the data source, here is mainly to facilitate the subsequent use of @DS to do dynamic data source switching, so the same ShardingSphere database is registered as a dynamic data source for four, to avoid the use of @DS can not find the corresponding data source;
import ;
import ;
import ;
import ;
import ;
import .;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
/**
* <p>
* <p>
* <p/>
*
* @author luowj
* @version 1.0
* @since 2024/7/22 18:41
*/
@Configuration
@AutoConfigureBefore({, })
public class MyDataSourceConfiguration {
/**
* mybatisplus Dynamic Data Source Configuration Items
*/
@Autowired
private DynamicDataSourceProperties properties;
/**
* shardingjdbcdata source
*/
@Lazy
@Resource(name = "shardingSphereDataSource")
private AbstractDataSourceAdapter shardingSphereDataSource;
@Value("${}")
private String shardingDataSourceNames;
/**
* Registering Dynamic Data Sources It's very critical here.，Because we need to use the@DSAnnotation Configuration Dynamic Selection of Data Sources，同上又要让选择data source使用shardingjdbcdata source
* the reason why，这里需要动态的把所有data source都注册为 shardingjdbcdata source
*/
@Bean
public DynamicDataSourceProvider dynamicDataSourceProvider() {
if ((shardingDataSourceNames)) {
throw new RuntimeException("configure Cannot be empty");
}
String[] names = (",");
return new AbstractDataSourceProvider() {
@Override
public Map<String, DataSource> loadDataSources() {
Map<String, DataSource> dataSourceMap = new HashMap<>();
(names).forEach(name -> (name, shardingSphereDataSource));
return dataSourceMap;
}
};
}
/**
* Setting a dynamic data source as the preferred data source
*/
@Primary
@Bean
public DataSource dataSource(DynamicDataSourceProvider dynamicDataSourceProvider) {
DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
(());
(());
(());
(dynamicDataSourceProvider);
dataSource.setP6spy(properties.getP6spy());
(());
return dataSource;
}
}
Customizing Complex Equalization Algorithms in ShardingSphere
Load balancing in shardingsphere requires the implementation of the ReadQueryLoadBalanceAlgorithm interface and the return of a customized algorithm name in the getType method, which officially comes with RoundRobinReadQueryLoadBalanceAlgorithm, RandomReadQueryLoadBalanceAlgorithm and so on. RandomReadQueryLoadBalanceAlgorithm, etc. Here we must customize the algorithm to be compatible with the @DS annotation to achieve the freedom to switch the data source;
ShardingSphere is loaded using the SPI mechanism, and the corresponding loading source code section is as follows:
So if we want our custom ReadQueryLoadBalanceAlgorithm class to take effect, we need to create a file in the services folder of our project's META-INF and populate it with the custom class
The configuration in the source code is as follows:
Then we follow the configuration of the source code to create it directly in our own project
Final custom implementation of CustomLoadBalanceAlgorithm
public class CustomLoadBalanceAlgorithm implements ReadQueryLoadBalanceAlgorithm {
private Properties props;
public CustomLoadBalanceAlgorithm() {
}
@Override
public void init(Properties props) {
= props;
}
/**
* Getting the data source
*
* @param name Data source name（ShardingJDBCapplied）
* @param writeDataSourceName 写Data source name
* @param readDataSourceNames 所有配置的复杂均衡center读Data source name
* @param context Transaction Context Object，Accessible() Determining whether a transaction is required，This can be used to determine if the Write Data Source
* @return
*/
@Override
public String getDataSource(String name, String writeDataSourceName, List<String> readDataSourceNames, TransactionConnectionContext context) {
// Get currentMybatisPlusSpecified data source
String dsKey = ();
if ((dsKey)) {
if ((dsKey)) {
return dsKey;
}
if ((dsKey)) {
return dsKey;
}
throw new RuntimeException("@DS misconfiguration，Current Data Source[" + dsKey + "](euphemism) pass awaySharingJDBCList of data sources[" + readDataSourceNames + "]center");
}
return writeDataSourceName;
}
@Override
public String getType() {
return "CUSTOM";
}
@Override
public boolean isDefault() {
return true;
}
@Override
@Generated
public Properties getProps() {
return ;
}
}
V. Source Code
Gitee: /luowenjie98/sharing-sphere-mybatisplus-demo
Please give me a STAR if you find it helpful, thank you very much !