本文主要是介绍基于SpringBoot+Mybatis实现Mysql分表,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
《基于SpringBoot+Mybatis实现Mysql分表》这篇文章主要为大家详细介绍了基于SpringBoot+Mybatis实现Mysql分表的相关知识,文中的示例代码讲解详细,感兴趣的小伙伴可...
基本思路
1.根据创建时间字段按年进行分表,比如日志表log可以分为log_2024、log_2025
2.在需要进行插入、android更新操作的地方利用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 = pythonentityClass; 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<?> epythonntityClass = 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.getParameterHHYHMOandler(); parameterHandler.setParameters(preparedStatement); preparedStatement.executeUpdate(); } finally { if (preparedStatement != null) { preparedStatement.close()China编程; } 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分表内容请搜索China编程(www.chinasem.cn)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程China编程(www.chinasem.cn)!
这篇关于基于SpringBoot+Mybatis实现Mysql分表的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!