Why is there such a need
Recently, we have an online project. Due to the large amount of read-write data, the pressure on the database increases, so now we plan to adopt the scheme of read-write separation.
How to solve it
The framework we use is springboot+mybatisplus, and it is an online project. It can not be transformed in large quantities, and the cost is too high, which is the prerequisite.
At present, there are two schemes,
1. The method of directly introducing multiple data sources of mybatisplus uses the annotation @ DS("xxx") at the method or class level. This scheme is more flexible, but many of our online codes use the single table addition, deletion, modification and query interfaces encapsulated by mybatisplus. These interfaces cannot use annotations. If they are modified alone, it will lead to a large workload of changes.
2. Customize the data source, obtain the sql type through the interceptor of mybatis, follow the database data source for query statements, add, delete and change to the main database data source.
After comparing our needs, we finally decided to do it in the second way.
Start test
Create demo
Create 2 library demos_ master,demo_ slave
Create a table user
Insert some data
CREATE TABLE user ( id BIGINT(20) NOT NULL COMMENT 'Primary key ID', name VARCHAR(30) NULL DEFAULT NULL COMMENT 'full name', age INT(11) NULL DEFAULT NULL COMMENT 'Age', email VARCHAR(50) NULL DEFAULT NULL COMMENT 'mailbox', PRIMARY KEY (id) ); INSERT INTO user (id, name, age, email) VALUES (1, 'Jone', 18, 'test1@baomidou.com'), (2, 'Jack', 20, 'test2@baomidou.com'), (3, 'Tom', 28, 'test3@baomidou.com'), (4, 'Sandy', 21, 'test4@baomidou.com'), (5, 'Billie', 24, 'test5@baomidou.com');
Initialize a maven project and introduce related dependencies
<parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.5.0</version> <relativePath /> </parent> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.4.3</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <scope>provided</scope> </dependency> </dependencies>
Configure data source
spring:
datasource:
hikari:
master:
jdbc-url: jdbc:mysql://localhost:3306/demo01_master
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
slave:
jdbc-url: jdbc:mysql://localhost:3306/demo01_slave
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
Generate entity, mapper,service, etc
Next, create a tool class DynamicDataSourceHolder for switching data sources
public class DynamicDataSourceHolder {
private static ThreadLocal<String> contextHolder = new ThreadLocal<>();
public static final String DB_MASTER = "master";
public static final String DB_SLAVE = "slave";
public static String getDbType() {
String db = contextHolder.get();
if (db == null) {
db = DB_MASTER;
}
return db;
}
public static void setDBType(String str) {
log.info("Currently set data source as" + str);
contextHolder.set(str);
}
public static void clearDbType() {
contextHolder.remove();
}
}
Implement a dynamic data source that inherits AbstractRoutingDataSource. This class runs and we select the current data source according to the defined rules
public class DynamicDataSource extends AbstractRoutingDataSource {
@Nullable
@Override
protected Object determineCurrentLookupKey() {
return DynamicDataSourceHolder.getDbType();
}
}
Add an interceptor to intercept the execution of sql
@Component
@Slf4j
//Specify which methods to intercept. update includes addition, deletion and modification
@Intercepts({ @Signature(type = Executor.class, method = "update", args = { MappedStatement.class, Object.class }),
@Signature(type = Executor.class, method = "query", args = { MappedStatement.class, Object.class,
RowBounds.class, ResultHandler.class }) })
public class DynamicDataSourceInterceptor implements Interceptor {
private static final String REGEX = ".*insert\\u0020.*|.*delete\\u0020.*|.*update\\u0020.*";
@Override
public Object intercept(Invocation invocation) throws Throwable {
boolean synchronizationActive = TransactionSynchronizationManager.isActualTransactionActive();
log.info("Whether the current execution statement has a transaction:{}",synchronizationActive);
String lookupKey = DynamicDataSourceHolder.DB_MASTER;
if (!synchronizationActive) {
Object[] objects = invocation.getArgs();
MappedStatement ms = (MappedStatement) objects[0];
if (ms.getSqlCommandType().equals(SqlCommandType.SELECT)) {
// If the selectKey is a self incrementing id query primary key, the primary database is used
if (ms.getId().contains(SelectKeyGenerator.SELECT_KEY_SUFFIX)) {
lookupKey = DynamicDataSourceHolder.DB_MASTER;
} else {
BoundSql boundSql = ms.getSqlSource().getBoundSql(objects[1]);
String sql = boundSql.getSql().toLowerCase(Locale.CHINA).replaceAll("[\\t\\n\\r]", " ");
if (sql.matches(REGEX)) {
lookupKey = DynamicDataSourceHolder.DB_MASTER;
} else {
// Here, if there are multiple slave databases, add the selection process
lookupKey = DynamicDataSourceHolder.DB_SLAVE;
}
}
}
} else {
lookupKey = DynamicDataSourceHolder.DB_MASTER;
}
DynamicDataSourceHolder.setDBType(lookupKey);
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
// The interception of additions, deletions and modifications is then handled by intercept
if (target instanceof Executor) {
return Plugin.wrap(target, this);
} else {
return target;
}
}
@Override
public void setProperties(Properties properties) {
}
Then create a configuration class to create data sources and configure transaction managers
@Configuration
@MapperScan(basePackages = "demo01.mapper")
public class MyBatisPlusConfig {
/**
* Configure data source
* @return
*/
@Bean(name = "master")
@ConfigurationProperties(prefix = "spring.datasource.hikari.master")
public DataSource master() {
return DataSourceBuilder.create().build();
}
@Bean(name = "slave")
@ConfigurationProperties(prefix = "spring.datasource.hikari.slave")
public DataSource slave() {
return DataSourceBuilder.create().build();
}
@Primary
@Bean(name = "dynamicDataSource")
public DynamicDataSource dataSource(@Qualifier("master") DataSource master,
@Qualifier("slave") DataSource slave) {
Map<Object, Object> targetDataSource = new HashMap<>();
targetDataSource.put(DynamicDataSourceHolder.DB_MASTER, master);
targetDataSource.put(DynamicDataSourceHolder.DB_SLAVE, slave);
DynamicDataSource dataSource = new DynamicDataSource();
dataSource.setTargetDataSources(targetDataSource);
return dataSource;
}
/**
* Configure transaction manager
*/
@Bean
public DataSourceTransactionManager transactionManager(DynamicDataSource dataSource) throws Exception {
return new DataSourceTransactionManager(dataSource);
}
}
Start testing
We need a few simple test cases
| Use case | expect | actual |
|---|---|---|
| Call query interface | The query is from the library | √ |
| Call the new data interface once, and then call the query interface again | When the master database is added, the slave database is queried, and there is no new data in the slave database | √ |
| In an interface, add first and then query, without transaction annotation | When the master database is added, the slave database is queried, and there is no new data in the slave database | √ |
| In an interface, add first and then query, with transaction annotations | When adding a primary database, you can query the primary database with corresponding data | √ |