Record the springBoot and MybatisPlus frameworks,
Application scenarios:
The project databases test_db and app_db use the same mysql instance and are divided into different libraries (table models). In order to reduce complexity, the user of db_data_link is given read-only permissions for the db_ds database, and can query across libraries.
The annotation @TableName has the attribute schema, which can set the table space, but because it is hard-coded, it is not flexible enough. When the table spaces of the test environment and the production environment are inconsistent, but the corresponding table structures are consistent, or the table space is modified, then it is necessary Changing a large number of entity class codes will make subsequent maintenance too cumbersome and increase the workload.
Here’s how to solve the above problems:
When we need to obtain the dynamic table schema name and use it in the schema attribute in the @TableName annotation,
It should be noted that my mybatis-plus version is: 3.4.2
<!-- mybatis-plus -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.2</version>
</dependency>
Because this version of the table name generation interceptor does not comply with our dynamic table space dynamic replacement, we need to obtain the property values configured in .properties. Therefore, we need to rewrite the DynamicTableNameInnerInterceptor class to dynamically obtain the attributes of the configuration file and replace the table space according to actual needs.
DynamicTableNameInnerInterceptor class
package com.epgis.business.common.config;
import com.baomidou.mybatisplus.core.plugins.InterceptorIgnoreHelper;
import com.baomidou.mybatisplus.core.toolkit.PluginUtils;
import com.baomidou.mybatisplus.core.toolkit.TableNameParser;
import com.baomidou.mybatisplus.extension.plugins.handler.TableNameHandler;
import com.baomidou.mybatisplus.extension.plugins.inner.DynamicTableNameInnerInterceptor;
import com.epgis.business.common.util.StringHelper;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* Rewrite the method to implement dynamic table space
* @author XYP
* @version 1.0
* @date 2023/10/17
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
@SuppressWarnings({<!-- -->"rawtypes"})
public class DynamicTableNameInterceptor extends DynamicTableNameInnerInterceptor {<!-- -->
private Map<String, TableNameHandler> tableNameHandlerMap;
@Override
public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {<!-- -->
PluginUtils.MPBoundSql mpBs = PluginUtils.mpBoundSql(boundSql);
if (InterceptorIgnoreHelper.willIgnoreDynamicTableName(ms.getId())) return;
mpBs.sql(this.changeTable(mpBs.sql()));
}
@Override
public void beforePrepare(StatementHandler sh, Connection connection, Integer transactionTimeout) {<!-- -->
PluginUtils.MPStatementHandler mpSh = PluginUtils.mpStatementHandler(sh);
MappedStatement ms = mpSh.mappedStatement();
SqlCommandType sct = ms.getSqlCommandType();
if (sct == SqlCommandType.INSERT || sct == SqlCommandType.UPDATE || sct == SqlCommandType.DELETE) {<!-- -->
if (InterceptorIgnoreHelper.willIgnoreDynamicTableName(ms.getId())) return;
PluginUtils.MPBoundSql mpBs = mpSh.mPBoundSql();
mpBs.sql(this.changeTable(mpBs.sql()));
}
}
protected String changeTable(String sql) {<!-- -->
TableNameParser parser = new TableNameParser(sql);
List<TableNameParser.SqlToken> names = new ArrayList<>();
parser.accept(names::add);
StringBuilder builder = new StringBuilder();
int last = 0;
for (TableNameParser.SqlToken name : names) {<!-- -->
int start = name.getStart();
if (start != last) {<!-- -->
builder.append(sql, last, start);
String value = name.getValue();
String value2 = "";
//If it contains the "$" symbol, replace it
if (name.getValue().indexOf("$") >= 0){<!-- -->
int dNum = name.getValue().lastIndexOf(".");
value = name.getValue().substring(0,dNum);
if (dNum >= 0 & amp; & amp; name.getValue().length() > dNum){<!-- -->
value2 = name.getValue().substring(dNum + 1);
}
}
TableNameHandler handler = tableNameHandlerMap.get(value);
if (handler != null) {<!-- -->
builder.append(handler.dynamicTableName(sql, value) + (StringHelper.isBlank(value2)?"":("." + value2)));
} else {<!-- -->
builder.append(value);
}
}
last = name.getEnd();
}
if (last != sql.length()) {<!-- -->
builder.append(sql.substring(last));
}
return builder.toString();
}
}
The next step is to configure MybatisPlusConfig.java
package com.epgis.business.common.config;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.handler.TableNameHandler;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.zaxxer.hikari.HikariDataSource;
import lombok.Data;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import java.util.HashMap;
import java.util.Map;
@Data
@Configuration
@MapperScan(basePackages = {<!-- -->"com.epgis.business.**.dao"})
public class MybatisPlusConfig {<!-- -->
@Autowired
HikariProperties hikariProperties;
/**
*Default data source
*/
private HikariDataSource dataSourceOral() {<!-- -->
HikariDataSource dataSource = new HikariDataSource();
hikariProperties.config(dataSource);
return dataSource;
}
/**
* Single data source connection pool configuration
*/
@Bean
public HikariDataSource singleDatasource() {<!-- -->
return dataSourceOral();
}
@Value("${epgis.test_db_schema}")
private String test_db_schema;
@Value("${epgis.app_db_schema}")
private String app_db_schema;
@Value("${epgis.sys_db_schema}")
private String sys_db_schema;
@Value("${epgis.log_db_schema}")
private String log_db_schema;
// new
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {<!-- -->
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.POSTGRE_SQL));
DynamicTableNameInterceptor dynamicTableNameInnerInterceptor = new DynamicTableNameInterceptor();
Map<String,TableNameHandler> map = new HashMap<>();
map.put("${epgis.test_db_schema}", new TableNameHandler() {<!-- -->
@Override
public String dynamicTableName(String sql, String tableName) {<!-- -->
return test_db_schema;
}
});
map.put("${epgis.app_db_schema}",new TableNameHandler() {<!-- -->
@Override
public String dynamicTableName(String sql, String tableName) {<!-- -->
return app_db_schema;
}
});
map.put("${epgis.sys_db_schema}",new TableNameHandler() {<!-- -->
@Override
public String dynamicTableName(String sql, String tableName) {<!-- -->
return sys_db_schema;
}
});
map.put("${epgis.log_db_schema}",new TableNameHandler() {<!-- -->
@Override
public String dynamicTableName(String sql, String tableName) {<!-- -->
return log_db_schema;
}
});
dynamicTableNameInnerInterceptor.setTableNameHandlerMap(map);
interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);
return interceptor;
}
}
Then the configuration of the .properties file is as follows:
epgis.test_db_schema=test_db epgis.app_db_schema=app_db epgis.sys_db_schema=sys_db epgis.log_db_schema=log_db
After the above configuration is completed, the entity class is used as follows:
In the annotation @TableName, the attribute value of the configuration schema is: “${epgis.test_db_schema}”
package com.epgis.business.demo.domain;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.util.List;
/**
* @Author: xyp
* @CreateTime: 2023/8/26
*/
@Data
@TableName(value = "t_demo",schema = "${epgis.test_db_schema}")
@ApiModel(value = "com.epgis.business.demo.domain.Demo", description = "Name Parameter")
public class Demo {<!-- -->
/** Primary key */
@TableId(value = "obj_id",type = IdType.ASSIGN_UUID)
@ApiModelProperty(hidden = true)
private String objId;
@TableField(value = "texas_id")
@ApiModelProperty(value = "texasId")
private String texasId;
/** name */
@TableField(value = "name")
@ApiModelProperty(value = "name")
private String name;
@TableField(exist = false)
@ApiModelProperty(value = "Details Data")
private List<DemoDetail> data;
}