PostgreSql分库分表
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
import java.lang.reflect.Field;
import java.util.Properties;
/**
* MybatisSubmeterInterceptor
* @Description: 分表拦截器
* @Author:
* @Date 2022/7/29 9:43
* @Version 1.0.0
*/
@ConditionalOnClass(value = org.apache.ibatis.plugin.Interceptor.class)
@Component("mybatisSubmeterInterceptor")
@Intercepts({
@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class})
})
@Order(0)
@Slf4j
public class MybatisSubmeterInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
Object[] args = invocation.getArgs();
Executor executor = (Executor) invocation.getTarget();
// 获取原始的sql语句
MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
Object parameter = invocation.getArgs()[1];
BoundSql boundSql = mappedStatement.getBoundSql(parameter);
String oldsql = boundSql.getSql();
// 判断是否需要分表
if (isReplaceTableName(oldsql)) {
log.error("\n分表前的sql：{}", oldsql);
// 查询单独处理
if (mappedStatement.getSqlCommandType().compareTo(SqlCommandType.SELECT) == 0) {
RowBounds rowBounds = (RowBounds) args[2];
ResultHandler resultHandler = (ResultHandler) args[3];
CacheKey cacheKey;
BoundSql boundSqlnew;
if (args.length == 4) {
boundSqlnew = mappedStatement.getBoundSql(parameter);
cacheKey = executor.createCacheKey(mappedStatement, parameter, rowBounds, boundSqlnew);
} else {
boundSqlnew = (BoundSql) args[5];
cacheKey = (CacheKey) args[4];
}
String sql = boundSqlnew.getSql();
String newsql = sql.replace("db_father", "db_father_" + "2400");
String newsql2 = sql.replace("DB_FATHER", "DB_FATHER_" + "2400");
newsql = StringUtils.equals(sql,newsql) ? newsql2 : newsql;
//通过反射修改sql语句
Field field = boundSqlnew.getClass().getDeclaredField("sql");
field.setAccessible(true);
field.set(boundSqlnew, newsql);
log.error("\n分表后的sql：{}", boundSqlnew.getSql());
// 直接query可能会应该其他拦截器
return executor.query(mappedStatement, parameter, rowBounds, resultHandler, cacheKey, boundSqlnew);
} else {
// 分表策略，这个地方可以使用注解，配置类，配置数据库等方式
String newsql = oldsql.replace("db_father", "db_father_" + "2400");
String newsql2 = oldsql.replace("DB_FATHER", "DB_FATHER_" + "2400");
newsql = StringUtils.equals(oldsql,newsql) ? newsql2 : newsql;
//重新生成一个BoundSql对象
BoundSql bs = new BoundSql(mappedStatement.getConfiguration(),newsql,boundSql.getParameterMappings(),parameter);
//重新生成一个MappedStatement对象
MappedStatement newMappedStatement = copyMappedStatement(mappedStatement, new BoundSqlSqlSource(bs));
//赋回给实际执行方法所需的参数中
args[0] = newMappedStatement;
}
log.error("\n分表后的sql：{}", ((MappedStatement)args[0]).getBoundSql(parameter).getSql());
}
return invocation.proceed();
}
@Override
public Object plugin(Object o) {
return Plugin.wrap(o, this);
}
@Override
public void setProperties(Properties properties) {
log.info("setProperties " + properties.toString());
}
/**
*
*
* @Description: 判断是否需要分表
* @Author:
* @Date 2022/7/29 11:53
* @param sql sql
* @return boolean
* @Version 1.0.0
**/
private boolean isReplaceTableName(String sql) {
// 测试数据，将ddb_father 重新定位到db_father_2400,
// 这个地方可以使用注解，配置类，配置数据库等方式判断
if (sql.contains("db_father") || sql.contains("DB_FATHER")) {
return true;
}
return false;
}
/**
*
*
* @Description: 生成一个新的mappedStatement
* @Date 2022/7/29 13:49
* @param ms
* @param newSqlSource
* @return
* @Version 1.0.0
**/
private MappedStatement copyMappedStatement (MappedStatement ms, SqlSource newSqlSource) {
MappedStatement.Builder builder = new MappedStatement.Builder(ms.getConfiguration(), ms.getId(), newSqlSource, ms.getSqlCommandType());
builder.resource(ms.getResource());
builder.fetchSize(ms.getFetchSize());
builder.statementType(ms.getStatementType());
builder.keyGenerator(ms.getKeyGenerator());
if (ms.getKeyProperties() != null && ms.getKeyProperties().length > 0) {
builder.keyProperty(String.join(",",ms.getKeyProperties()));
}
builder.timeout(ms.getTimeout());
builder.parameterMap(ms.getParameterMap());
builder.resultMaps(ms.getResultMaps());
builder.resultSetType(ms.getResultSetType());
builder.cache(ms.getCache());
builder.flushCacheRequired(ms.isFlushCacheRequired());
builder.useCache(ms.isUseCache());
return builder.build();
}
/***
* MappedStatement构造器接受的是SqlSource
* 实现SqlSource接口，将BoundSql封装进去
*/
public static class BoundSqlSqlSource implements SqlSource {
private BoundSql boundSql;
public BoundSqlSqlSource(BoundSql boundSql) {
this.boundSql = boundSql;
}
@Override
public BoundSql getBoundSql(Object parameterObject) {
return boundSql;
}
}
}