ibatis分表处理

当某一个表的数据太庞大的时候，查询就会变得很困难，这个时候需要进行分表操作，就是把一个总表分成几个分表，这样查询某些数据的时候，就会变得快一些：

分表操作：

1  在sql中加一个占位符，代表分表，如下T_HTLPRO_DAYINCREASE_TBL_SHD ：

SELECT thd.increasetype,

                         thd.increaseamount,

                         thd.increasecurrency,

                         thd.commodityid,

                         thd.saledate

                    FROM T_HTLPRO_DAYINCREASE_TBL_SHD thd, t_htlpro_salechannel channel

                   where thd.salechannelid = channel.salechannelid

             and thd.commodityid = #commodityId#

                     and thd.salechannelcd = #saleChannelCd#

                     <![CDATA[

                     and thd.saledate >= #saleStartDate#

                     and thd.saledate <= #saleEndDate#

                     ]]>

2  这个分表关联merchantCode，不同的merchantCode，数据保存到不同的分表中，即根据merchantCode找到对应的分表：

 select * from htl_org.t_shard_map t where t.merchant_code = 'M10000001' ;

 

3  ibatis执行sql操作会调用SqlExecutor里的方法，因此需要继承这个类，并重写相关方法：

在执行sql之前，用分表名替换占位符，

package com.fangcang.dao.shard.executor;

 

import java.sql.Connection;

import java.sql.SQLException;

import java.util.List;

 

import org.apache.commons.lang.StringUtils;

import org.apache.commons.logging.Log;

import org.apache.commons.logging.LogFactory;

 

import com.fangcang.dao.shard.config.ShardMappingInfo;

import com.fangcang.dao.shard.util.AnnotationUtils;

import com.ibatis.sqlmap.engine.execution.SqlExecutor;

import com.ibatis.sqlmap.engine.mapping.statement.RowHandlerCallback;

import com.ibatis.sqlmap.engine.scope.StatementScope;

 

/**

 * 继承ibatis的SqlExecutor 处理分表

 * @author wangjun

 * @date 2016-3-7

 */

public class ShardedSqlExecutor extends SqlExecutor {

 

private static final Log log = LogFactory.getLog(ShardedSqlExecutor.class);

private static final ThreadLocal<String> shardParam = new ThreadLocal<String>();

 

public void setMerchantCode(String merchantCode){

shardParam.set(merchantCode);

}

 

public String wrapperSql(String sql){

if(AnnotationUtils.isCallerShardablePresent()){

String merchantCode = shardParam.get();

if(StringUtils.isEmpty(merchantCode)){

throw new RuntimeException("使用分表功能商家编码不能为空！");

}

if(log.isDebugEnabled()){

log.debug("\r\n");

log.debug("==========对以下sql执行分表替换==========");

log.debug("==========替换前==========");

log.debug("=========="+sql+"==========");

log.debug("==========使用映射配置==========");

log.debug("\r\n");

}

List<ShardMappingInfo> mappings = ShardMappingInfo.getShardMappingInfo(merchantCode);

if(null == mappings || mappings.isEmpty()){

throw new RuntimeException("使用分表功能分表配置不能为空！");

}

if(log.isDebugEnabled()){

log.debug("\r\n");

log.debug("==========商家编码："+merchantCode+"，映射配置"+mappings+"==========");

log.debug("\r\n");

}

for (ShardMappingInfo mapping : mappings) {

if(!StringUtils.isEmpty(mapping.getMainTableName()) && !StringUtils.isEmpty(mapping.getShardTableName())){

sql = sql.replaceAll(mapping.getMainTableName()+"_TBL_SHD", mapping.getShardTableName());

}

}

if(log.isDebugEnabled()){

log.debug("\r\n");

log.debug("==========替换后==========");

log.debug("=========="+sql+"==========");

log.debug("\r\n");

}

return sql; 

}

return sql;

};

 

@Override

public int executeUpdate(StatementScope statementScope, Connection conn,

String sql, Object[] parameters) throws SQLException {

if(log.isDebugEnabled()){

log.debug("\r\n");

log.debug("==========正在执行sql==========");

log.debug("=========="+sql+"==========");

log.debug("==========sql参数"+resolveParamters(parameters)+"==========");

log.debug("\r\n");

}

return super.executeUpdate(statementScope, conn, wrapperSql(sql), parameters);

}

 

@Override

public void addBatch(StatementScope statementScope, Connection conn,

String sql, Object[] parameters) throws SQLException {

if(log.isDebugEnabled()){

log.debug("\r\n");

log.debug("==========正在执行sql==========");

log.debug("=========="+sql+"==========");

log.debug("==========sql参数"+resolveParamters(parameters)+"==========");

log.debug("\r\n");

}

super.addBatch(statementScope, conn, wrapperSql(sql), parameters);

}

 

 

@Override

public void executeQuery(StatementScope statementScope, Connection conn,

String sql, Object[] parameters, int skipResults, int maxResults,

RowHandlerCallback callback) throws SQLException {

if(log.isDebugEnabled()){

log.debug("\r\n");

log.debug("==========正在执行sql==========");

log.debug("=========="+sql+"==========");

log.debug("==========sql参数"+resolveParamters(parameters)+"==========");

log.debug("\r\n");

}

super.executeQuery(statementScope, conn, wrapperSql(sql), parameters, skipResults,

maxResults, callback);

}

 

@Override

public int executeUpdateProcedure(StatementScope statementScope,

Connection conn, String sql, Object[] parameters)

throws SQLException {

if(log.isDebugEnabled()){

log.debug("\r\n");

log.debug("==========正在执行sql==========");

log.debug("=========="+sql+"==========");

log.debug("==========sql参数"+resolveParamters(parameters)+"==========");

log.debug("\r\n");

}

return super.executeUpdateProcedure(statementScope, conn, wrapperSql(sql), parameters);

}

 

@Override

public void executeQueryProcedure(StatementScope statementScope,

Connection conn, String sql, Object[] parameters, int skipResults,

int maxResults, RowHandlerCallback callback) throws SQLException {

if(log.isDebugEnabled()){

log.debug("\r\n");

log.debug("==========正在执行sql==========");

log.debug("=========="+sql+"==========");

log.debug("==========sql参数："+resolveParamters(parameters)+"==========");

log.debug("\r\n");

}

super.executeQueryProcedure(statementScope, conn, wrapperSql(sql), parameters, skipResults,

maxResults, callback);

}

 

private String resolveParamters(Object[] parameters){

if(null == parameters || parameters.length < 1){

return "";

}

 

StringBuilder sb = new StringBuilder();

sb.append("[ ");

for (Object object : parameters) {

if(null != object){

sb.append(object.toString()).append(" ");

}

}

sb.append("]");

return sb.toString();

}

}