实现动态表名是个很常见的需求,网上也有很多解决方法,这边总结了三种实现方式。
一、手动给每个方法加个表名的变量
缺点很明显,侵入性大,不方便,不推荐
二、mybatis插件机制拦截sql替换表名实现动态表名
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Properties;
/**
* mybatis插件实现动态表名,可以拦截器新增、编辑、删除、查询等
*/
@Component
@Intercepts({
@Signature(type = StatementHandler.class, method = "prepare", args = {
Connection.class, Integer.class})})
public class ReplaceTablePlugin implements Interceptor {
private static final Logger LOG = LoggerFactory.getLogger(ReplaceTablePlugin.class);
private final static Map TABLE_MAP = new LinkedHashMap();
/**
* 需要替换的表(替换前的表名和替换后的表名)
*/
static {
TABLE_MAP.put("t_user", "t_user_20220101");
TABLE_MAP.put("t_org", "t_org_20220202");
}
@Override
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler statementHandler = realTarget(invocation.getTarget());
MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
doTable(statementHandler, metaObject);
return invocation.proceed();
}
private void doTable(StatementHandler handler, MetaObject metaStatementHandler) throws ClassNotFoundException {
BoundSql boundSql = handler.getBoundSql();
String originalSql = boundSql.getSql();
if (originalSql != null && !originalSql.equals("")) {
LOG.info("拦截前的sql:{}", originalSql);
if (isReplaceTableName(originalSql)) {
for (Map.Entry entry : TABLE_MAP.entrySet()) {
originalSql = originalSql.replace(entry.getKey(), entry.getValue());
}
LOG.info("拦截后的sql:{}", originalSql);
metaStatementHandler.setValue("delegate.boundSql.sql", originalSql);
}
}
}
private boolean isReplaceTableName(String sql) {
for (String tableName : TABLE_MAP.keySet()) {
if (sql.contains(tableName)) {
return true;
}
}
return false;
}
@Override
public Object plugin(Object target) {
if (target instanceof StatementHandler) {
return Plugin.wrap(target, this);
}
return target;
}
@Override
public void setProperties(Properties properties) {
}
/**
* Obtain real processing objects, possibly multi-layer agents
*
* @param target
* @param
* @return
*/
public static T realTarget(Object target) {
if (Proxy.isProxyClass(target.getClass())) {
MetaObject metaObject = SystemMetaObject.forObject(target);
return realTarget(metaObject.getValue("h.target"));
}
return (T) target;
}
}
三、mybatis-plus的DynamicTableNameInnerInterceptor实现
3.1引入mybatis-plus的pom jar包依赖
com.baomidou
mybatis-plus-extension
3.5.2
com.baomidou
服务器托管网mybatis-plus-annotation
3.5.2
com.baomidou
mybatis-plus-core
3.5.2
3.2 实现的配置类
import java.util.Map;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.json.JSONUtil;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.DynamicTableNameInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.OptimisticLockerInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
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.ComponentScan;
import org.springframework.data.redis.core.RedisTemplate;
/**
*
* mybatis-plus实现动态替换表名
*/
@MapperScan("com.xiaweiyi8080.dal.mapper")
@ComponentScan({"com.xiaweiyi8080.dal.manager"})
@Slf4j
public class MyBatisPlusConfiguration {
@Autowired
private RedisTemplate redisTemplate;
// 配置文件里配置的哪些表需要动态表名
@Value("${xxxxx.tableName:}")
private String dynamicTableName;
@Bean
public MybatisPlusInterceptor dynamicTableNameInterceptor() {
log.info("------拿到的dynamicTableName={}", dynamicTableName);
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor = new DynamicTableNameInnerInterceptor();
dynamicTableNameInnerInterceptor.setTableNameHandler((sql, tableName) -> {
// 获取参数方法
Map paramMap = RequestDataHelper.getRequestData();
if (CollectionUtil.isNotEmpty(paramMap)) {
paramMap.forEach((k, v) -> log.info(k + "----" + v));
}
if (StringUtils.isNotBlank(dynamicTableName) && dynamicTableName.contains(tableName)) {
log.info("------替换前表名={}", tableName);
String suffix = "_20220101";
tableName += suffix;
log.info("------替换后表名={}", tableName);
}
return tableName;
});
服务器托管网 interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);
return interceptor;
}
}
服务器托管,北京服务器托管,服务器租用 http://www.fwqtg.net
机房租用,北京机房租用,IDC机房托管, http://www.fwqtg.net
服务器托管网 这种问题一般来说就是缺少驱动,就要下载驱动。 问题: 解决办法: 1.进入联想官网下载驱动 网站:https://newsupport.lenovo.com.cn/driveDownloads_index.html?v=9d9bc7ad5023e…