mybatis mysql 分表_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===============================================================