在做基于mybatis持久层开发时,由于单表数据量增大,可能会考虑分表进行处理,可以采用sharding-jdbc实现,这里基于mybatis自定义实现分表组件进行实现,重点思考其实现逻辑和设计模式,学习底层原理。
具体涉及到的多表联查,可进一步思考如何实现。
分表需求:自定义分表策略,可以为id取模,日期取月;可以通过注解进行引用;
前提:需提取建好表结构;
核心逻辑:通过拦截器,根据分表策略得到分表名,再把分表名动态替换到sql中;
实例:
分表策略及实现
public interface ITableShardStrategy {
/**
* 生成分表名
* @param tableNamePrefix 表前缀名
* @param value 值
* @return: String
*/
String generateTableName(String tableNamePrefix,Object value);
/**
* 验证tableNamePrefix
* @param tableNamePrefix 表前缀名
* @return:
*/
default void verificationTableNamePrefix(String tableNamePrefix){
if (ObjectUtils.isEmpty(tableNamePrefix)) {
throw new RuntimeException("tableNamePrefix is null");
}
}
}
id取模策略
@Component
public class IdTableShardStrategy implements ITableShardStrategy {
@Override
public String generateTableName(String tableNamePrefix, Object value) {
verificationTableNamePrefix(tableNamePrefix);
if (value == null || ObjectUtils.isEmpty(value.toString())) {
throw new RuntimeException("value is null");
}
long id = Long.parseLong(value.toString());
//此处可以缓存优化
return tableNamePrefix + "_" + (id % 2);
}
}
日期取月策略
@Component
public class DateTableShardStrategy implements ITableShardStrategy {
private static final String DATE_PATTERN = "yyyyMM";
@Override
public String generateTableName(String tableNamePrefix, Object value) {
verificationTableNamePrefix(tableNamePrefix);
if (value == null || ObjectUtils.isEmpty(value.toString())) {
return tableNamePrefix + "_" + DateUtil.formatDateByPattern(new Date(), DATE_PATTERN);
} else {
return tableNamePrefix + "_" + DateUtil.formatDateByPattern(new Date(), value.toString());
}
}
}
通过注解引入,灵活应用,注解的作用范围是类、接口、函数,运行时生效。
@Target(value = {ElementType.TYPE,ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface TableShard {
// 表前缀名
String tableNamePrefix();
// 值
String value() default "";
// 是否是字段名,如果是需要解析请求参数改字段名的值(默认否)
boolean fieldFlag() default false;
// 对应的分表策略类
Class<? extends ITableShardStrategy> shardStrategy();
}
抽象分表策略与分表注解都搞定了,最后一步就是根据分表注解信息,去执行分表策略得到分表名,再把分表名动态替换到sql中,同时具有通用性。
Mybatis框架中,有拦截器机制做扩展,我们只需要拦截StatementHandler#prepare函数,即StatementHandle创建Statement之前,先把sql里面的表名动态替换成分表名。
@Intercepts({
@Signature(
type = StatementHandler.class,
method = "prepare",
args = {Connection.class, Integer.class}
)
})
public class TableShardInterceptor implements Interceptor {
private static final ReflectorFactory defaultReflectorFactory = new DefaultReflectorFactory();
@Override
public Object intercept(Invocation invocation) throws Throwable {
// MetaObject是mybatis里面提供的一个工具类,类似反射的效果
MetaObject metaObject = getMetaObject(invocation);
BoundSql boundSql = (BoundSql) metaObject.getValue("delegate.boundSql");
MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
// 获取Mapper执行方法
Method method = invocation.getMethod();
// 获取分表注解
TableShard tableShard = getTableShard(method,mappedStatement);
// 如果method与class都没有TableShard注解或执行方法不存在,执行下一个插件逻辑
if (tableShard == null) {
return invocation.proceed();
}
//获取值
String value = tableShard.value();
//value是否字段名,如果是,需要解析请求参数字段名的值
boolean fieldFlag = tableShard.fieldFlag();
if (fieldFlag) {
//获取请求参数
Object parameterObject = boundSql.getParameterObject();
if (parameterObject instanceof MapperMethod.ParamMap) {
// ParamMap类型逻辑处理
MapperMethod.ParamMap parameterMap = (MapperMethod.ParamMap) parameterObject;
// 根据字段名获取参数值
Object valueObject = parameterMap.get(value);
if (valueObject == null) {
throw new RuntimeException(String.format("入参字段%s无匹配", value));
}
//替换sql
replaceSql(tableShard, valueObject, metaObject, boundSql);
} else {
// 单参数逻辑
//如果是基础类型抛出异常
if (isBaseType(parameterObject)) {
throw new RuntimeException("单参数非法,请使用@Param注解");
}
if (parameterObject instanceof Map){
Map<String,Object> parameterMap = (Map<String,Object>)parameterObject;
Object valueObject = parameterMap.get(value);
//替换sql
replaceSql(tableShard, valueObject, metaObject, boundSql);
} else {
//非基础类型对象
Class<?> parameterObjectClass = parameterObject.getClass();
Field declaredField = parameterObjectClass.getDeclaredField(value);
declaredField.setAccessible(true);
Object valueObject = declaredField.get(parameterObject);
//替换sql
replaceSql(tableShard, valueObject, metaObject, boundSql);
}
}
} else {//无需处理parameterField
//替换sql
replaceSql(tableShard, value, metaObject, boundSql);
}
//执行下一个插件逻辑
return invocation.proceed();
}
/**
* @description:
* @param target
* @return: Object
*/
@Override
public Object plugin(Object target) {
// 当目标类是StatementHandler类型时,才包装目标类,否者直接返回目标本身, 减少目标被代理的次数
if (target instanceof StatementHandler) {
return Plugin.wrap(target, this);
} else {
return target;
}
}
/**
* @description: 基本数据类型验证,true是,false否
* @param object
* @return: boolean
*/
private boolean isBaseType(Object object) {
if (object.getClass().isPrimitive()
|| object instanceof String
|| object instanceof Integer
|| object instanceof Double
|| object instanceof Float
|| object instanceof Long
|| object instanceof Boolean
|| object instanceof Byte
|| object instanceof Short) {
return true;
} else {
return false;
}
}
/**
* @description: 替换sql
* @param tableShard 分表注解
* @param value 值
* @param metaObject mybatis反射对象
* @param boundSql sql信息对象
* @return: void
*/
private void replaceSql(TableShard tableShard, Object value, MetaObject metaObject, BoundSql boundSql) {
String tableNamePrefix = tableShard.tableNamePrefix();
// 获取策略class
Class<? extends ITableShardStrategy> strategyClazz = tableShard.shardStrategy();
// 从spring ioc容器获取策略类
ITableShardStrategy tableShardStrategy = SpringBeanUtil.getBean(strategyClazz);
// 生成分表名
String shardTableName = tableShardStrategy.generateTableName(tableNamePrefix, value);
// 获取sql
String sql = boundSql.getSql();
// 完成表名替换
metaObject.setValue("delegate.boundSql.sql", sql.replaceAll(tableNamePrefix, shardTableName));
}
/**
* @description: 获取MetaObject对象-mybatis里面提供的一个工具类,类似反射的效果
* @param invocation
* @return: MetaObject
*/
private MetaObject getMetaObject(Invocation invocation) {
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
// MetaObject是mybatis里面提供的一个工具类,类似反射的效果
MetaObject metaObject = MetaObject.forObject(statementHandler,
SystemMetaObject.DEFAULT_OBJECT_FACTORY,
SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY,
defaultReflectorFactory
);
return metaObject;
}
/**
* @description: 获取分表注解
* @param method
* @param mappedStatement
* @return: TableShard
*/
private TableShard getTableShard(Method method, MappedStatement mappedStatement) throws ClassNotFoundException {
String id = mappedStatement.getId();
// 获取Class
final String className = id.substring(0, id.lastIndexOf("."));
// 分表注解
TableShard tableShard = null;
// 获取Mapper执行方法的TableShard注解
tableShard = method.getAnnotation(TableShard.class);
// 如果方法没有设置注解,从Mapper接口上面获取TableShard注解
if (tableShard == null) {
// 获取TableShard注解
tableShard = Class.forName(className).getAnnotation(TableShard.class);
}
return tableShard;
}
}
注册分表拦截器
@Configuration
public class TableShardConfig {
/**
* 注册插件
*/
@Bean
public Interceptor tableShardInterceptor(){
return new TableShardInterceptor();
}
}
可以针对上面实现进行封装打包成jar,需要的项目进行引入使用。使用时在对应的mapper类上或类中的方法上加上分表注解即可。
使用实例:
在此不再描述,常用的集成mybatis的配置及引用方式,在此基础上,对mapper类加上自定义分表注解即可,若不使用,则保持不动。
@TableShard(tableNamePrefix = "tb_log_date",shardStrategy = DateTableShardStrategy.class)
public interface LogDateMapper {
/**
* 查询列表-根据日期分表
*/
List<LogDate> queryList();
/**
* 单插入-根据日期分表
*/
void save(LogDate logDate);
}
@TableShard(tableNamePrefix = "tb_log_id",value = "id",fieldFlag = true,shardStrategy = IdTableShardStrategy.class)
public interface LogIdMapper {
/**
* 根据id查询-根据id分片
*/
LogId queryOne(@Param("id") long id);
/**
* 单插入-根据id分片
*/
void save(LogId logId);
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ldc.springboot_sharding_table.mapper.LogDateMapper">
<select id="queryList" resultType="com.ldc.springboot_sharding_table.model.LogDate">
select id as id, comment as comment, create_date as createDate from tb_log_date
</select>
<insert id="save" >
insert into tb_log_date(id, comment, create_date)
values (#{id}, #{comment}, #{createDate})
</insert>
</mapper>
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ldc.springboot_sharding_table.mapper.LogIdMapper">
<select id="queryOne" resultType="com.ldc.springboot_sharding_table.model.LogId">
select id as id, comment as comment, create_date as createDate from tb_log_id where id = #{id}
</select>
<insert id="save" >
insert into tb_log_id(id, comment, create_date)
values (#{id}, #{comment}, #{createDate})
</insert>
</mapper>
实体自行创建,不再赘述。
单元测试
@Test
void test() {
LogDate logDate = new LogDate();
long id = new Random().nextInt(100) + 1;
logDate.setId(id);
logDate.setComment("测试date");
logDate.setCreateDate(new Date());
//插入
logDateMapper.save(logDate);
//查询
List<LogDate> logDates = logDateMapper.queryList();
System.out.println(JSONObject.toJSONString(logDates));
}
@Test
void test2() {
LogId logId = new LogId();
long id = new Random().nextInt(100) + 1;
logId.setId(id);
logId.setComment("测试id");
logId.setCreateDate(new Date());
//插入
logIdMapper.save(logId);
//查询
LogId logIds = logIdMapper.queryOne(id);
System.out.println(JSONObject.toJSONString(logIds));
}
测试结果
{“comment”:“测试id”,“createDate”:1640599685000,“id”:85}
[{“comment”:“测试date”,“createDate”:1640594605000,“id”:52},{“comment”:“测试date”,“createDate”:1640593732000,“id”:56},{“comment”:“测试date”,“createDate”:1640599716000,“id”:83}]