Mybatis按日期分表

利用自定义拦截器实现分表 ，可以根据年份，日期，等进行分表 

自定义@TableSplit注解

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

//自定义注解 
@Retention(RetentionPolicy.RUNTIME)
@Target({ ElementType.TYPE })
public @interface TableSplit {
    //是否分表
     boolean split() default true;
    //表名
     String value();

    //获取分表策略,如YYYY or yyyyMM
     String strategy();

}

public interface Strategy {
    public String convert(String tableName);
}

 

import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * 按年份分表
 */
public class YYYYStrategy implements Strategy {

    @Override
    public String convert(String tableName) {
        SimpleDateFormat sdf = new SimpleDateFormat("YYYY");
        StringBuilder sb=new StringBuilder(tableName);
        sb.append("_");
        sb.append(sdf.format(new Date()));
        return sb.toString();
    }

}

 

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

public class StrategyManager{
    private Log log= LogFactory.getLog(StrategyManager.class);
    private Map<String,Strategy> strategies = new ConcurrentHashMap<String,Strategy>(10);

    public  Strategy getStrategy(String key){
        return strategies.get(key);
    }

    public   Map<String, Strategy> getStrategies() {
        return strategies;
    }

    public  void setStrategies(Map<String, String> strategies) {
        for(Map.Entry<String, String> entry : strategies.entrySet()){
            try {
                this.strategies.put(entry.getKey(),(Strategy)Class.forName(entry.getValue()).newInstance());
            } catch (Exception e) {
                log.error("实例化策略出错", e);
            }
        }
        printDebugInfo();
    }
    private void printDebugInfo(){
        StringBuffer msg= new StringBuffer("初始化了"+strategies.size()+"策略");
        for(String key: strategies.keySet()){
            msg.append("\n");
            msg.append(key);
            msg.append("  --->  ");
            msg.append(strategies.get(key));
        }
        log.debug(msg.toString());
    }
}

自定义拦截器 

import com.mayday.common.split.ContextHelper;
import com.mayday.common.split.Strategy;
import com.mayday.common.split.StrategyManager;
import com.mayday.common.split.TableSplit;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
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.ReflectorFactory;
import org.apache.ibatis.reflection.factory.DefaultObjectFactory;
import org.apache.ibatis.reflection.factory.ObjectFactory;
import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory;
import org.apache.ibatis.reflection.wrapper.ObjectWrapperFactory;

import java.sql.Connection;
import java.util.Properties;

@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class }) })
public class TableSplitInterceptor implements Interceptor {
    private Log log = LogFactory.getLog(getClass());
    private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory();
    private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory();
    private static final ReflectorFactory DEFAULT_OBJECT_REFLECTOR_FACTORY=new DefaultReflectorFactory();

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        MetaObject metaStatementHandler = MetaObject.forObject(statementHandler, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY,DEFAULT_OBJECT_REFLECTOR_FACTORY);

        BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql");
        // Configuration configuration = (Configuration) metaStatementHandler
        // .getValue("delegate.configuration");
        Object parameterObject = metaStatementHandler.getValue("delegate.boundSql.parameterObject");
        doSplitTable(metaStatementHandler);
        // 传递给下一个拦截器处理
        return invocation.proceed();

    }

    @Override
    public Object plugin(Object target) {
        // 当目标类是StatementHandler类型时，才包装目标类，否者直接返回目标本身,减少目标被代理的次数
        if (target instanceof StatementHandler) {
            return Plugin.wrap(target, this);
        } else {
            return target;
        }
    }

    @Override
    public void setProperties(Properties properties) {

    }

    private void doSplitTable(MetaObject metaStatementHandler) throws ClassNotFoundException{
        String originalSql = (String) metaStatementHandler.getValue("delegate.boundSql.sql");
        if (originalSql != null && !originalSql.equals("")) {
            log.info("分表前的SQL："+originalSql);
            MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement");
            String id = mappedStatement.getId();
            String className = id.substring(0, id.lastIndexOf("."));
            Class<?> classObj = Class.forName(className);
            // 根据配置自动生成分表SQL
            TableSplit tableSplit = classObj.getAnnotation(TableSplit.class);
            if (tableSplit != null && tableSplit.split()) {
                StrategyManager strategyManager = ContextHelper.getStrategyManager();
                Strategy strategy=strategyManager.getStrategy(tableSplit.strategy());//获取分表策略来处理分表
                String convertedSql=originalSql.replaceAll(tableSplit.value(), strategy.convert(tableSplit.value()));
                metaStatementHandler.setValue("delegate.boundSql.sql",convertedSql);
                log.info("分表后的SQL："+convertedSql);
            }
        }
    }
}

在dao层使用@TableSplit注解

@TableSplit(value="operation_record", strategy="YYYY")
public interface ReportDao {

    //查询后台操作记录
    List<OperationRecord> queryOperationRecord(OperationRecord operationRecord);

    //查询后台操作记录总数
    Long queryOperationRecordTotal(OperationRecord operationRecord);
}

 

最后还需要在spring-mybatis.xml文件中配置 分表策略 

<bean id="strategyManager" class="com.mayday.common.split.StrategyManager">
    <property name="strategies">
        <map>
            <entry key="YYYY" value="com.mayday.common.split.YYYYStrategy" />
            <entry key="yyyyMM" value="com.mayday.common.split.MMStrategy" />
        </map>
    </property>
</bean>

配置自定义拦截器 

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <!-- 注入数据库连接池 -->
    <property name="dataSource" ref="dataSource" />
    <!-- 配置MyBaties全局配置文件:mybatis-config.xml -->
    <property name="configLocation" value="classpath:mybatis-config.xml" />
    <!-- 扫描entity包 使用别名 -->
    <property name="typeAliasesPackage" value="com.mayday.model" />
    <!-- 扫描sql配置文件:mapper需要的xml文件 -->
    <property name="mapperLocations" value="classpath:mapper/*.xml" />
    <property name="plugins">
        <array>
            <bean class="com.mayday.interceptor.TableSplitInterceptor"/>
        </array>
    </property>
</bean>

 

转载于:https://my.oschina.net/u/3524345/blog/1543488