首先你要知道在哪些sql上面要处理分表?你可能需要一个注解:
- package com.dusk.domyself.stock.common.split;
- 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 {
- //是否分表
- public boolean split() default true;
- //表名
- public String value();
- //获取分表策略
- public String strategy();
- }
然后你可能需要一个拦截器处理类:
- package com.dusk.domyself.stock.common.split;
- import java.sql.Connection;
- import java.util.Properties;
- 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.MetaObject;
- 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 com.dusk.domyself.stock.common.ContextHelper;
- @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();
- @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);
- 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);
- }
- }
- }
- }
- package com.dusk.domyself.stock.mapper;
- import java.util.List;
- import com.dusk.domyself.stock.common.split.TableSplit;
- import com.dusk.domyself.stock.entity.StockDay;
- @TableSplit(value="stock_day", strategy="YYYY")
- public interface StockDayMapper {
- int deleteByPrimaryKey(Long id);
- int insert(StockDay record);
- int insertSelective(StockDay record);
- List<StockDay> selectBystockId(String stockId);
- int updateByPrimaryKeySelective(StockDay record);
- int updateByPrimaryKey(StockDay record);
- }
- <!-- 配置分表策略 -->
- <bean id="strategyManager" class="com.dusk.domyself.stock.common.split.StrategyManager">
- <property name="strategies">
- <map>
- <entry key="YYYY" value="com.dusk.domyself.stock.common.split.YYYYStrategy" />
- </map>
- </property>
- </bean>
- package com.dusk.domyself.stock.common.split;
- import java.text.SimpleDateFormat;
- import java.util.Date;
- /**
- * 按年分表
- * @author dushangkui
- *
- */
- 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();
- }
- }
通用策略接口是:
- package com.dusk.domyself.stock.common.split;
- public interface Strategy {
- /**
- * 传入一个需要分表的表名,返回一个处理后的表名
- * Strategy必须包含一个无参构造器
- * @param tableName
- * @return
- */
- public String convert(String tableName);
- }
- package com.dusk.domyself.stock.common;
- import org.springframework.beans.BeansException;
- import org.springframework.context.ApplicationContext;
- import org.springframework.context.ApplicationContextAware;
- import org.springframework.stereotype.Component;
- import com.dusk.domyself.stock.common.split.StrategyManager;
- @Component
- public class ContextHelper implements ApplicationContextAware {
- private static ApplicationContext context;
- @Override
- public void setApplicationContext(ApplicationContext context)
- throws BeansException {
- ContextHelper.context=context;
- }
- public static StrategyManager getStrategyManager(){
- return context.getBean(StrategyManager.class);
- }
- }
跑起来看结果:
- [2016-08-30 22:32:30,170] [http-apr-8080-exec-7] (TableSplitInterceptor.java:63) INFO com.dusk.domyself.stock.common.split.TableSplitInterceptor - 分表前的SQL:select
- id, stock_id, data_date, top_price, bottom_price, open_price, close_price, main_funds,
- retail_funds, funds, volume
- from stock_day
- where stock_id = ?
- [2016-08-30 22:32:30,170] [http-apr-8080-exec-7] (AbstractBeanFactory.java:249) DEBUG org.springframework.beans.factory.support.DefaultListableBeanFactory - Returning cached instance of singleton bean 'strategyManager'
- [2016-08-30 22:32:30,171] [http-apr-8080-exec-7] (TableSplitInterceptor.java:75) INFO com.dusk.domyself.stock.common.split.TableSplitInterceptor - 分表后的SQL:select
- id, stock_id, data_date, top_price, bottom_price, open_price, close_price, main_funds,
- retail_funds, funds, volume
- from stock_day_2016
- where stock_id = ?
需要其他的分表策略可以自己实现然后注册就好了
里面设计的类已经github共享:https://github.com/dushangkui/common-util
https://blog.csdn.net/jiaotuwoaini/article/details/52373883