记录springBoot、MybatisPlus框架,
应用场景:
项目数据库test_db和app_db使用同一个mysql实例,分不同的库(表模型),为了降低复杂度,给db_data_link的用户赋予了db_ds数据库的只读权限,可以跨库查询
而注解@TableName里有属性schema,可以设置表空间,但是因为是写死,不够灵活,当测试环境和生产环境的表空间不一致,而对应的表结构一致,或者表空间修改时,那么就需要改动大批量的实体类代码,后期维护太过繁琐,增加工作量。
以下就是解决以上问题的方法:
当我们需要动态表模式名称获取在@TableName注解里的schema属性里使用的时候,
需要注意的是:我的mybatis-plus版本的是:3.4.2
<!-- mybatis-plus -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.2</version>
</dependency>
因为该版本的表名生成拦截器不符合我们动态表空间动态替换,去获取.properties里配置的属性值。所以,我们需要重写DynamicTableNameInnerInterceptor类,根据实际需求,来动态获取配置文件的属性替换表空间。
DynamicTableNameInnerInterceptor类
package com.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.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;
/**
* 重写实现动态表空间的方法
* @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 (name.getValue().indexOf("$") >= 0){
int dNum = name.getValue().lastIndexOf(".");
value = name.getValue().substring(0,dNum);
if (dNum >= 0 && 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();
}
}
接下来就是配置MybatisPlusConfig.java
package com.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.business.**.dao"})
public class MybatisPlusConfig {
@Autowired
HikariProperties hikariProperties;
/**
* 默认的数据源
*/
private HikariDataSource dataSourceOral() {
HikariDataSource dataSource = new HikariDataSource();
hikariProperties.config(dataSource);
return dataSource;
}
/**
* 单数据源连接池配置
*/
@Bean
public HikariDataSource singleDatasource() {
return dataSourceOral();
}
@Value("${test_db_schema}")
private String test_db_schema;
@Value("${app_db_schema}")
private String app_db_schema;
@Value("${sys_db_schema}")
private String sys_db_schema;
@Value("${log_db_schema}")
private String log_db_schema;
// 最新版
@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("${test_db_schema}", new TableNameHandler() {
@Override
public String dynamicTableName(String sql, String tableName) {
return test_db_schema;
}
});
map.put("${app_db_schema}",new TableNameHandler() {
@Override
public String dynamicTableName(String sql, String tableName) {
return app_db_schema;
}
});
map.put("${sys_db_schema}",new TableNameHandler() {
@Override
public String dynamicTableName(String sql, String tableName) {
return sys_db_schema;
}
});
map.put("${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;
}
}
那么.properties文件的配置如下:
test_db_schema=test_db
app_db_schema=app_db
sys_db_schema=sys_db
log_db_schema=log_db
如上配置完成后,那么实体类的使用方式如下:
在注解@TableName中,配置schema的属性值为:"${test_db_schema}"
package com.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 = "${test_db_schema}")
@ApiModel(value = "com.business.demo.domain.Demo", description = "名称参数")
public class Demo {
/** 主键 */
@TableId(value = "obj_id",type = IdType.ASSIGN_UUID)
@ApiModelProperty(hidden = true)
private String objId;
@TableField(value = "texas_id")
@ApiModelProperty(value = "texasId")
private String texasId;
/** 名称 */
@TableField(value = "name")
@ApiModelProperty(value = "名称")
private String name;
@TableField(exist = false)
@ApiModelProperty(value = "详情数据")
private List<DemoDetail> data;
}