Mybatis 分表处理
项目到后期遇到分表查询处理，但是项目已经快要开发完了而且使用通用mapper不想在改，想了一个办法在mybatis与mysql数据库连接时处理sql语句 替换相关表。
第一步，springboot采用注解开发，添加相关配置监听mybatis.
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
public class MybatisInterceptorConfig {
@Bean
public String myInterceptor(SqlSessionFactory sqlSessionFactory) {
sqlSessionFactory.getConfiguration().addInterceptor(new MybatisSqlInterceptor());
return "interceptor";
}
}
第二步，mybatis在执行sql语句前处理代码
/***
* mybatis 执行前 sql处理
*
*/
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import javax.annotation.PostConstruct;
import org.apache.commons.lang3.StringUtils;
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.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.factory.DefaultObjectFactory;
import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
@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})
})
@Component
public class MybatisSqlInterceptor implements Interceptor {
@Autowired
private RedisService redisServic2;
private static RedisService redisService;
@PostConstruct
public void beforeInit() {
//需要注入的类
redisService=redisServic2;
}
@Override
public Object intercept(Invocation invocation) throws Throwable {
// 获取sql
String sql = getSqlByInvocation(invocation);
if (StringUtils.isBlank(sql)) {
return invocation.proceed();
}
// sql交由处理类处理 对sql语句进行处理 此处是范例 不做任何处理
String sql2Reset = sql;
// 包装sql后，重置到invocation中
resetSql2Invocation(invocation, sql2Reset);
// 返回，继续执行
return invocation.proceed();
}
@Override
public Object plugin(Object obj) {
return Plugin.wrap(obj, this);
}
@Override
public void setProperties(Properties arg0) {
// doSomething
}
/**
* 获取sql语句
* @param invocation
* @return
*/
private String getSqlByInvocation(Invocation invocation) {
final Object[] args = invocation.getArgs();
MappedStatement ms = (MappedStatement) args[0];
Object parameterObject = args[1];
BoundSql boundSql = ms.getBoundSql(parameterObject);
return boundSql.getSql();
}
/**
* 包装sql后，重置到invocation中
* @param invocation
* @param sql
* @throws SQLException
*/
private void resetSql2Invocation(Invocation invocation, String sql) throws SQLException {
final Object[] args = invocation.getArgs();
MappedStatement statement = (MappedStatement) args[0];
Object parameterObject = args[1];
BoundSql boundSql = statement.getBoundSql(parameterObject);
MappedStatement newStatement = newMappedStatement(statement, new BoundSqlSqlSource(boundSql));
MetaObject msObject = MetaObject.forObject(newStatement, new DefaultObjectFactory(), new DefaultObjectWrapperFactory(),new DefaultReflectorFactory());
//========================================================分表处理关键位置 start===============================================================
//根据需求 替换表
String tabName="";//表名
int tabNameLength=0;//查询表的长度
String suffix="";//后缀
tabNameLength=tabName.length();
if(sql.indexOf(tabName)!=-1){
if(sql.length()>(sql.indexOf(tabName)+tabNameLength+1)){
if(sql.substring((sql.indexOf(tabName)+tabNameLength),(sql.indexOf(tabName)+tabNameLength+1)).indexOf("_")==-1){
sf=new StringBuffer(sql);
sf.replace(sql.indexOf(tabName), sql.indexOf(tabName)+tabNameLength,tabName+suffix);
sql=sf.toString();
}else if(sql.indexOf(tabName+" ")!=-1){
sf=new StringBuffer(sql);
sf.replace(sql.indexOf(tabName+" "), sql.indexOf(tabName+" ")+tabNameLength,tabName+suffix);
sql=sf.toString();
}
}else{
sql=sql.replace(tabName,tabName+suffix);
}
}
//========================================================分表处理关键位置 end===============================================================
msObject.setValue("sqlSource.boundSql.sql", sql);
args[0] = newStatement;
}
private MappedStatement newMappedStatement(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) {
StringBuilder keyProperties = new StringBuilder();
for (String keyProperty : ms.getKeyProperties()) {
keyProperties.append(keyProperty).append(",");
}
keyProperties.delete(keyProperties.length() - 1, keyProperties.length());
builder.keyProperty(keyProperties.toString());
}
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();
}
private String getOperateType(Invocation invocation) {
final Object[] args = invocation.getArgs();
MappedStatement ms = (MappedStatement) args[0];
SqlCommandType commondType = ms.getSqlCommandType();
if (commondType.compareTo(SqlCommandType.SELECT) == 0) {
return "select";
}
if (commondType.compareTo(SqlCommandType.INSERT) == 0) {
return "insert";
}
if (commondType.compareTo(SqlCommandType.UPDATE) == 0) {
return "update";
}
if (commondType.compareTo(SqlCommandType.DELETE) == 0) {
return "delete";
}
return null;
}
// 定义一个内部辅助类，作用是包装sq
class BoundSqlSqlSource implements SqlSource {
private BoundSql boundSql;
public BoundSqlSqlSource(BoundSql boundSql) {
this.boundSql = boundSql;
}
@Override
public BoundSql getBoundSql(Object parameterObject) {
return boundSql;
}
}
}
以上为 监听 mybatis与sql连接时处理sql的相关处理代码，具体需要根据具体需求自行修改相关逻辑。
主要位置：
//========================================================分表处理关键位置 start===============================================================
//根据需求 替换表
String tabName="";//表名
int tabNameLength=0;//查询表的长度
String suffix="";//后缀
tabNameLength=tabName.length();
if(sql.indexOf(tabName)!=-1){
if(sql.length()>(sql.indexOf(tabName)+tabNameLength+1)){
if(sql.substring((sql.indexOf(tabName)+tabNameLength),(sql.indexOf(tabName)+tabNameLength+1)).indexOf("_")==-1){
sf=new StringBuffer(sql);
sf.replace(sql.indexOf(tabName), sql.indexOf(tabName)+tabNameLength,tabName+suffix);
sql=sf.toString();
}else if(sql.indexOf(tabName+" ")!=-1){
sf=new StringBuffer(sql);
sf.replace(sql.indexOf(tabName+" "), sql.indexOf(tabName+" ")+tabNameLength,tabName+suffix);
sql=sf.toString();
}
}else{
sql=sql.replace(tabName,tabName+suffix);
}
}
//========================================================分表处理关键位置 end===============================================================
