1.POM依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.30</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.3</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.3.11.RELEASE</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.2.1</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.2.3</version>
</dependency>
<!-- 分表插件 -->
<dependency>
<groupId>org.shardbatis</groupId>
<artifactId>shardbatis</artifactId>
<version>2.0.0B</version>
</dependency>
<!-- sql解析插件 -->
<dependency>
<groupId>net.sf.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>0.8.0</version>
</dependency>
<!-- mybatis pager -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>4.1.0</version>
</dependency>
这里需要注意,如果使用 pagehelper 分页插件和 shardbatis 插件,一定要把pagehelper放在下面!!!!(踩坑)
2.配置文件/配置类
2.1配置类
package com.ils.intelab.video.config;
import com.google.code.shardbatis.builder.ShardConfigHolder;
import com.google.code.shardbatis.builder.ShardConfigParser;
import com.google.code.shardbatis.converter.SqlConverterFactory;
import com.google.code.shardbatis.util.ReflectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.executor.statement.RoutingStatementHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.logging.Log;
import org.apache.ibatis.logging.LogFactory;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
import java.util.concurrent.ConcurrentHashMap;
@Intercepts({@Signature(
type = StatementHandler.class,
method = "prepare",
args = {Connection.class,Integer.class}
)})
public class ShardPluginExtend implements Interceptor {
private static final Log log = LogFactory.getLog(ShardPluginExtend.class);
public static final String SHARDING_CONFIG = "shardingConfig";
private static final ConcurrentHashMap<String, Boolean> cache = new ConcurrentHashMap();
public ShardPluginExtend(){
}
@Override
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler statementHandler = (StatementHandler)invocation.getTarget();
MappedStatement mappedStatement = null;
if (statementHandler instanceof RoutingStatementHandler) {
StatementHandler delegate = (StatementHandler) ReflectionUtils.getFieldValue(statementHandler, "delegate");
mappedStatement = (MappedStatement)ReflectionUtils.getFieldValue(delegate, "mappedStatement");
} else {
mappedStatement = (MappedStatement)ReflectionUtils.getFieldValue(statementHandler, "mappedStatement");
}
String mapperId = mappedStatement.getId();
if (this.isShouldParse(mapperId)) {
String sql = statementHandler.getBoundSql().getSql();
if (log.isDebugEnabled()) {
log.debug("Original Sql [" + mapperId + "]:" + sql);
}
Object params = statementHandler.getBoundSql().getParameterObject();
SqlConverterFactory cf = SqlConverterFactory.getInstance();
sql = cf.convert(sql, params, mapperId);
if (log.isDebugEnabled()) {
log.debug("Converted Sql [" + mapperId + "]:" + sql);
}
ReflectionUtils.setFieldValue(statementHandler.getBoundSql(), "sql", sql);
}
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
String config = properties.getProperty("shardingConfig", (String)null);
if (config != null && config.trim().length() != 0) {
ShardConfigParser parser = new ShardConfigParser();
InputStream input = null;
try {
input = Resources.getResourceAsStream(config);
parser.parse(input);
} catch (IOException var14) {
log.error("Get sharding config file failed.", var14);
throw new IllegalArgumentException(var14);
} catch (Exception var15) {
log.error("Parse sharding config file failed.", var15);
throw new IllegalArgumentException(var15);
} finally {
if (input != null) {
try {
input.close();
} catch (IOException var13) {
log.error(var13.getMessage(), var13);
}
}
}
} else {
throw new IllegalArgumentException("property 'shardingConfig' is requested.");
}
}
private boolean isShouldParse(String mapperId) {
if(StringUtils.isNotBlank(mapperId)){
mapperId = mapperId.substring(mapperId.lastIndexOf('.', (mapperId.lastIndexOf(".") - 1)) + 1);
}
Boolean parse = (Boolean)cache.get(mapperId);
if (parse != null) {
return parse;
} else {
if (!mapperId.endsWith("!selectKey")) {
ShardConfigHolder configHolder = ShardConfigHolder.getInstance();
if (!configHolder.isIgnoreId(mapperId) && (!configHolder.isConfigParseId() || configHolder.isParseId(mapperId))) {
parse = true;
}
}
if (parse == null) {
parse = false;
}
cache.put(mapperId, parse);
return parse;
}
}
}
package com.ils.intelab.video.config;
import com.baomidou.mybatisplus.core.injector.ISqlInjector;
import com.baomidou.mybatisplus.core.parser.ISqlParser;
import com.baomidou.mybatisplus.extension.injector.LogicSqlInjector;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
@Configuration
@MapperScan({"com.ils.intelab.video.dao"})
public class MybatisPlusConfig {
@Bean(name = "shardPlugin")
public ShardPluginExtend shardPlugin() {
ShardPluginExtend shardPlugin = new ShardPluginExtend();
Properties properties = new Properties();
//文件加载--键值必须为shardingConfig,这是类的内部要求,否则加载失败
properties.put("shardingConfig", "shard/shard_config.xml");
shardPlugin.setProperties(properties);
return shardPlugin;
}
/**
* 分页插件
*
* @return PaginationInterceptor
*/
@Bean
@ConditionalOnMissingBean
public PaginationInterceptor paginationInterceptor() {
PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
List<ISqlParser> sqlParserList = new ArrayList<>();
paginationInterceptor.setSqlParserList(sqlParserList);
return paginationInterceptor;
}
/**
* 逻辑删除插件
*
* @return LogicSqlInjector
*/
@Bean
@ConditionalOnMissingBean
public ISqlInjector sqlInjector() {
return new LogicSqlInjector();
}
}
2.2配置文件(测试)
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE shardingConfig PUBLIC "-//shardbatis.googlecode.com//DTD Shardbatis 2.0//EN"
"http://shardbatis.googlecode.com/dtd/shardbatis-config.dtd">
<shardingConfig>
<!-- 忽略不进行分表策略的mapperid-即对应的方法 -->
<ignoreList>
<value></value>
</ignoreList>
<!-- 进行分表策略的mapperid-即对应的方法;一定要精确到方法,否则不会被拦截 -->
<parseList>
<value>VideoDataMotionMapper.getALLMotionVideoDate</value>
</parseList>
<!-- 指定表使用对应的策略 -->
<strategy tableName="video_data_motion" strategyClass="com.ils.intelab.video.shard.VideoDataShardStrategyImpl" />
</shardingConfig>
3.ShardStrategy接口实现(测试)
package com.ils.intelab.video.shard;
import com.alibaba.fastjson.JSONObject;
import com.google.code.shardbatis.strategy.ShardStrategy;
import com.ils.intelab.common.utils.JsonMapperUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
/**
* @Classname VideoDataShardStrategyImpl
* @Description TODO
* @Date 2020/5/6 14:48
* @Created zzf
*/
@Component
@Slf4j
public class VideoDataShardStrategyImpl implements ShardStrategy {
@Override
public String getTargetTableName(String baseTableName, Object params, String mapperId) {
String jsonResult = JsonMapperUtil.obj2String(params);
JSONObject jsonObject = JSONObject.parseObject(jsonResult);
int deviceSerial = jsonObject.get("deviceSerial").toString().hashCode();
Integer tableNum = deviceSerial % 4;
Integer databaseNum = deviceSerial % 2;
baseTableName = baseTableName.split("_")[0] + "_" + databaseNum + "." + baseTableName + "_" + tableNum;
return baseTableName;
}
}
这里我根据它的hashcode去做!
测试如下: