基于SpringBoot+Mybatis实现Mysql分表_Java

基本思路

1.根据创建时间字段按年进行分表，比如日志表log可以分为log_2024、log_2025

2.在需要进行插入、更新操作的地方利用threadlocal将数据表对应的entity和创建时间放入当前的线程中，利用mybatis提供的拦截器在sql执行前进行拦截，将threadlocal中的entity类取出，根据类上标注的注解获取要操作的表名，再利用创建时间获得最终要操作的实际表名，最后更换sql中的表名让拦截器继续执行

定义注解

定义注解@shardedtable，将该注解标注在数据表对应的entity类上，比如user类上

/**
 * 分表注解
 */
@retention(retentionpolicy.runtime)
@target(elementtype.type)
public @interface shardedtable {

    // 表名前缀
    string prefix();

}

@shardedtable(prefix = "user")
@tablename("user")
public class user {

    @tableid(type = idtype.auto)
    private integer id;

    private string name;

    private integer age;

    public user(string name, integer age) {
        this.name = name;
        this.age = age;
    }
}

创建threadlocal

public class shardingcontext{
    private static final threadlocal<shardingcontext> context = new threadlocal<>();

    private class<?> entityclass; // 数据表对应的实体类
    private date date;

    public static void setcontext(class<?> entityclass, date date) {
        shardingcontext context = new shardingcontext();
        context.entityclass = entityclass;
        context.date = date;
        context.set(context);
    }

    public static shardingcontext getcontext() {
        return context.get();
    }

    public static void clearcontext() {
        context.remove();
    }

    public class<?> getentityclass() {
        return entityclass;
    }

    public date getdate() {
        return date;
    }
}

创建拦截器

@component
@intercepts({@signature(type = statementhandler.class, method = "prepare", args = {connection.class,integer.class})})
public class shardinginterceptor implements interceptor {

    @autowired
    private shardingstrategy shardingstrategy;

    @override
    public object intercept(invocation invocation) throws throwable {
        // 获取原始sql
        statementhandler statementhandler = (statementhandler) invocation.gettarget();
        boundsql boundsql = statementhandler.getboundsql();
        string originalsql =  boundsql.getsql();

        // 获取当前操作的实体类
        shardingcontext context = shardingcontext.getcontext();
        if (context != null){
            class<?> entityclass = context.getentityclass();
            date date = context.getdate();
            shardedtable annotation = entityclass.getannotation(shardedtable.class);
            if (annotation != null) {
                // 设置新的sql,替换表名
                string basetablename = annotation.prefix();
                string actualtablename = shardingstrategy.gettablename(user.class, date);
                string modifiedsql = originalsql.replace(basetablename, actualtablename);
                setsql(boundsql, modifiedsql);

                // 将数据保存到原表，作为备份
                executebackupinsert(statementhandler,originalsql);
            }
        }

        return invocation.proceed();
    }

    private void setsql(boundsql boundsql, string sql) throws exception {
        field field = boundsql.class.getdeclaredfield("sql");
        field.setaccessible(true);
        field.set(boundsql, sql);
    }

    // 同时将数据保存到原表，作为备份
    private void executebackupinsert(statementhandler statementhandler, string backupsql) throws sqlexception {
        connection connection = null;
        preparedstatement preparedstatement = null;
        try {
            // 通过反射获取 mappedstatement
            metaobject metaobject = systemmetaobject.forobject(statementhandler);
            mappedstatement mappedstatement = (mappedstatement) metaobject.getvalue("delegate.mappedstatement");

            connection = mappedstatement.getconfiguration().getenvironment().getdatasource().getconnection();
            preparedstatement = connection.preparestatement(backupsql);

            // 设置参数
            parameterhandler parameterhandler = statementhandler.getparameterhandler();
            parameterhandler.setparameters(preparedstatement);

            preparedstatement.executeupdate();
        } finally {
            if (preparedstatement != null) {
                preparedstatement.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
    }

    @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) {
    }
}

获取表名

@component
public class shardingstrategy {

    public string gettablename(class<?> entityclass, date date) {
        shardedtable annotation = entityclass.getannotation(shardedtable.class);
        if (annotation == null) {
            throw new runtimeexception("实体类必须使用@shardedtable注解");
        }
        // 获取分表前缀
        string tableprefix = annotation.prefix();
        if (tableprefix == null || tableprefix.isempty()) {
            throw new runtimeexception("分表前缀不能为空");
        }
        // 获取当前日期所在的年份
        int year = dateutil.year(date);
        return tableprefix + "_" + year;
    }
}

业务处理

在需要进行业务处理的地方，将数据表对应的entity.class和创建时间通过threadlocal放入当前线程中，后面要根据这些信息获取实际要操作的表名

public void insert(serviceorderlogentity serviceorderlogentity) {
    shardingcontext.setcontext(serviceorderlogentity.class, serviceorderlogentity.gettime() == null ? new date() : serviceorderlogentity.gettime());
    
    int result = serviceorderlogmapper.insert(serviceorderlogentity);
    
    shardingcontext.clearcontext();
}

到此这篇关于基于springboot+mybatis实现mysql分表的文章就介绍到这了,更多相关springboot mysql分表内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！