mybatis自动分表和JdbcTemplate自动建表
需求背景:由于业务的增长,原有的表存放的太多的数据,每次去查询的数据耗时很久,严重影响了查询效率,所以才有了今天的博客文章,在处理问题的过程中,有考虑mysql的分区方案,我的分区是按照月份的,但是对于定时任务来说,时间存在重复的,所以mysql分区方案被剔除,因为项目xxl-job的方案,主要针对的表是xxl_job_log,但是改变涉及的位置比较多,手动去修改,一个是比较麻烦,二是容易出现测试测漏掉的情况发生,所以引入了mybatis的拦截器Interceptor,整体方案比较简单,但是因为存在同一个时间,很多任务都会同时执行的问题,所以会有并发比较高的问题
使用的数据库:mysql
使用的java的持久层框架: mybatis
拦截器部分的全部代码
package com.java.mybatisservice.interceptor; import lombok.extern.slf4j.Slf4j; import org.apache.ibatis.executor.Executor; import org.apache.ibatis.mapping.BoundSql; import org.apache.ibatis.mapping.MappedStatement; import org.apache.ibatis.mapping.SqlCommandType; import org.apache.ibatis.mapping.SqlSource; import org.apache.ibatis.plugin.Interceptor; import org.apache.ibatis.plugin.Intercepts; import org.apache.ibatis.plugin.Invocation; import org.apache.ibatis.plugin.Signature; import org.apache.ibatis.session.ResultHandler; import org.apache.ibatis.session.RowBounds; import org.springframework.stereotype.Component; import java.time.LocalDate; import java.time.format.DateTimeFormatter; import java.util.concurrent.ConcurrentHashMap; import java.util.concurrent.locks.ReentrantLock; import java.util.regex.Matcher; import java.util.regex.Pattern; /** * @Description: * @Author: 喵星人 * @Create: 2023/11/10 11:02 */ /** * method = "query"拦截select方法、而method = "update"则能拦截insert、update、delete的方法 * * @author 喵星人 */ @Intercepts({ @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}), @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}) }) @Component @Slf4j public class ReplaceTableInterceptor implements Interceptor { private final String tableName = "recy_bank_resume"; private final ReentrantLock lock = new ReentrantLock(); private final Pattern tablePattern; private final ConcurrentHashMap<String, String> tableNameCache = new ConcurrentHashMap<>(); /** * 保存当前月份的成员变量 */ private String currentMonth; public ReplaceTableInterceptor() { // 在构造方法中创建正则表达式 String regex = "\\b" + Pattern.quote(tableName) + "\\b"; tablePattern = Pattern.compile(regex, Pattern.CASE_INSENSITIVE); // 初始化当前月份 currentMonth = LocalDate.now().format(DateTimeFormatter.ofPattern("yyyyMMdd")); } @Override public Object intercept(Invocation invocation) throws Throwable { updateTableNameCache(); // 更新表名缓存 Object[] args = invocation.getArgs(); MappedStatement ms = (MappedStatement) args[0]; Object parameterObject = args[1]; BoundSql boundSql = ms.getBoundSql(parameterObject); String originalSql = boundSql.getSql(); // log.info("【原始sql】:{}", originalSql); if (isSelectStatement(ms) && isTargetTable(originalSql, tableName)) { String newTableName = tableNameCache.get("current"); String newSql = originalSql.replace(tableName, newTableName); log.info("【修改后的sql】:{}", newSql); // 确保替换表名是原子操作 lock.lock(); try { BoundSql newBoundSql = new BoundSql(ms.getConfiguration(), newSql, boundSql.getParameterMappings(), parameterObject); MappedStatement newMs = copyMappedStatement(ms, new BoundSqlSqlSource(newBoundSql)); args[0] = newMs; } finally { lock.unlock(); } } return invocation.proceed(); } private void updateTableNameCache() { String currentMonthNow = LocalDate.now().format(DateTimeFormatter.ofPattern("yyyyMMdd")); String newTableName = tableName + currentMonthNow; tableNameCache.computeIfAbsent("current", k -> newTableName); } private String getNewTableName(String tableName) { // 根据需求生成新的表名,可以使用当前日期等方式 String currentMonthNow = LocalDate.now().format(DateTimeFormatter.ofPattern("yyyyMMdd")); if (!currentMonth.equals(currentMonthNow)) { // 如果月份变化了,更新当前月份和需要的表名 currentMonth = currentMonthNow; return tableName + currentMonthNow; } // 月份没变化,直接返回原表名 return tableName + currentMonth; } private boolean isTargetTable(String sql, String tableName) { // 使用预先创建的正则表达式 Matcher matcher = tablePattern.matcher(sql); return matcher.find(); } private boolean isSelectStatement(MappedStatement mappedStatement) { return mappedStatement.getSqlCommandType() == SqlCommandType.SELECT; } private MappedStatement copyMappedStatement(MappedStatement ms, SqlSource newSqlSource) { MappedStatement.Builder builder = new MappedStatement.Builder(ms.getConfiguration(), ms.getId(), newSqlSource, ms.getSqlCommandType()); builder.resource(ms.getResource()); builder.fetchSize(ms.getFetchSize()); builder.statementType(ms.getStatementType()); builder.keyGenerator(ms.getKeyGenerator()); if (ms.getKeyProperties() != null && ms.getKeyProperties().length > 0) { builder.keyProperty(String.join(",", ms.getKeyProperties())); } builder.timeout(ms.getTimeout()); builder.parameterMap(ms.getParameterMap()); builder.resultMaps(ms.getResultMaps()); builder.resultSetType(ms.getResultSetType()); builder.cache(ms.getCache()); builder.flushCacheRequired(ms.isFlushCacheRequired()); builder.useCache(ms.isUseCache()); return builder.build(); } /*** * MappedStatement构造器接受的是SqlSource * 实现SqlSource接口,将BoundSql封装进去 */ public static class BoundSqlSqlSource implements SqlSource { private BoundSql boundSql; public BoundSqlSqlSource(BoundSql boundSql) { this.boundSql = boundSql; } @Override public BoundSql getBoundSql(Object parameterObject) { return boundSql; } } }
pom文件
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.4.2</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.mybatis</groupId> <artifactId>mybatis-service</artifactId> <version>0.0.1-SNAPSHOT</version> <name>mybatis-service</name> <description>Demo project for Spring Boot</description> <properties> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.4</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <!--实体类字段校验依赖--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-validation</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.27</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> <version>3.9</version> </dependency> <!--引入junit单元测试依赖--> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> </dependency> <!--引入thymeleaf依赖--> <!-- <dependency>--> <!-- <groupId>org.springframework.boot</groupId>--> <!-- <artifactId>spring-boot-starter-thymeleaf</artifactId>--> <!-- </dependency>--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-redis</artifactId> </dependency> <!--添加fastjson依赖--> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.83</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>
配置文件application.properties
server.port=80 logging.level.com.java=debug logging.level.web=debug log4j.logger.org.mybatis=DEBUG spring.devtools.add-properties=false ip.port=127.0.0.1:3306 spring.datasource.driver-class-name=com.mysql.jdbc.Driver spring.datasource.url=jdbc:mysql://${ip.port}/yourheart?useUnicode=true&characterEncoding=utf-8&useSSL=false spring.datasource.username=root spring.datasource.password=root spring.datasource.hikari.connection-timeout=20000 spring.datasource.hikari.maximum-pool-size=10 spring.datasource.hikari.minimum-idle=10 mybatis.mapper-locations=classpath:mapping/*.xml mybatis.configuration.map-underscore-to-camel-case=true
启动类
package com.java.mybatisservice; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; @SpringBootApplication public class MybatisServiceApplication { public static void main(String[] args) { SpringApplication.run(MybatisServiceApplication.class, args); } }
<?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.java.mybatisservice.mapper.RecyBankResumeMapper"> <select id="getAll" resultType="com.java.mybatisservice.bank.controller.front.RecyBankResume"> SELECT * FROM recy_bank_resume WHERE bank_resume_status='2' ORDER BY update_resume_times DESC </select> <insert id="adds" parameterType="com.java.mybatisservice.bank.controller.front.BankResume"> INSERT INTO recy_bank_resume(bank_end,bank_name,bank_funds,bank_use,update_resume_times,resume_user,bank_resume_status) VALUES(#{bankEnd},#{bankName},#{bankFunds},#{bankUse},NOW(),#{resumeUser},#{bankResumeStatus}) </insert> </mapper>
测试类相关代码
package com.java.mybatisservice; import com.java.mybatisservice.mapper.RecyResumerMapper; import org.junit.jupiter.api.Test; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; @SpringBootTest public class MybatisServiceApplicationTests { @Autowired private RecyResumerMapper recyResumerMapper; @Test public void test(){ } }
以上代码解决高并发环境数据处理错误的场景,保证了原子性,避免了线程不安全的问题发生
下面列出自动建表相关,以下项目其实就是分布式定时任务xxl-job中的代码
package com.xxl.job.executor.service.jobhandler; import com.xxl.job.core.handler.annotation.XxlJob; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Component; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.ResultSet; import java.sql.SQLException; import java.time.LocalDate; import java.time.format.DateTimeFormatter; /** * XxlJob开发示例(Bean模式) * * 开发步骤: * 1、任务开发:在Spring Bean实例中,开发Job方法; * 2、注解配置:为Job方法添加注解 "@XxlJob(value="自定义jobhandler名称", init = "JobHandler初始化方法", destroy = "JobHandler销毁方法")",注解value值对应的是调度中心新建任务的JobHandler属性的值。 * 3、执行日志:需要通过 "XxlJobHelper.log" 打印执行日志; * 4、任务结果:默认任务结果为 "成功" 状态,不需要主动设置;如有诉求,比如设置任务结果为失败,可以通过 "XxlJobHelper.handleFail/handleSuccess" 自主设置任务结果; * * @author xuxueli 2019-12-11 21:52:51 */ @Component public class SampleXxlJob { private static Logger logger = LoggerFactory.getLogger(SampleXxlJob.class); @Autowired private JdbcTemplate jdbcTemplate; @XxlJob("demoJobHandler11") public void demoJobHandle11() throws Exception { String tableName = "xxl_job_log" + LocalDate.now().format(DateTimeFormatter.ofPattern("yyyyMM")); createTableIfNotExists(tableName); } private void createTableIfNotExists(String tableName) { if (!isTableExists(tableName)) { String createTableSql = "CREATE TABLE " + tableName + " (\n" + " `id` bigint(20) NOT NULL AUTO_INCREMENT,\n" + " `job_group` int(11) NOT NULL COMMENT '执行器主键ID',\n" + " `job_id` int(11) NOT NULL COMMENT '任务,主键ID',\n" + " `executor_address` varchar(255) DEFAULT NULL COMMENT '执行器地址,本次执行的地址',\n" + " `executor_handler` varchar(255) DEFAULT NULL COMMENT '执行器任务handler',\n" + " `executor_param` varchar(512) DEFAULT NULL COMMENT '执行器任务参数',\n" + " `executor_sharding_param` varchar(20) DEFAULT NULL COMMENT '执行器任务分片参数,格式如 1/2',\n" + " `executor_fail_retry_count` int(11) NOT NULL DEFAULT '0' COMMENT '失败重试次数',\n" + " `trigger_time` datetime DEFAULT NULL COMMENT '调度-时间',\n" + " `trigger_code` int(11) NOT NULL COMMENT '调度-结果',\n" + " `trigger_msg` text COMMENT '调度-日志',\n" + " `handle_time` datetime DEFAULT NULL COMMENT '执行-时间',\n" + " `handle_code` int(11) NOT NULL COMMENT '执行-状态',\n" + " `handle_msg` text COMMENT '执行-日志',\n" + " `alarm_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '告警状态:0-默认、1-无需告警、2-告警成功、3-告警失败',\n" + " PRIMARY KEY (`id`),\n" + " KEY `I_trigger_time` (`trigger_time`),\n" + " KEY `I_handle_code` (`handle_code`),\n" + " KEY `trigger_handle_alarm_retry` (`trigger_code`,`handle_code`,`alarm_status`,`executor_fail_retry_count`)\n" + ") ENGINE=InnoDB AUTO_INCREMENT=47953569 DEFAULT CHARSET=utf8mb4;"; try { jdbcTemplate.execute(createTableSql); logger.info("Double-check if the table is created successfully"); if (!isTableExists(tableName)) { throw new RuntimeException("Failed to create table: " + tableName); } } catch (Exception e) { // Log error and re-try table creation logger.error("Error creating table:{}",e.getMessage()); createTableIfNotExists(tableName); } }else { logger.info("表:{} 已经存在",tableName); } } private boolean isTableExists(String tableName) { try (Connection connection = jdbcTemplate.getDataSource().getConnection()) { DatabaseMetaData metaData = connection.getMetaData(); try (ResultSet tables = metaData.getTables(null, null, tableName, new String[] { "TABLE" })) { return tables.next(); } } catch (SQLException e) { throw new RuntimeException("Failed to check table existence: " + tableName, e); } } }
我使用的mysql数据库的版本是5.7.43
对应的配置文件的数据库信息
#2023年10月30日添加 spring.datasource.url=jdbc:mysql://127.0.0.1:3306/xxl_job?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai spring.datasource.username=root spring.datasource.password=root spring.datasource.driver-class-name=com.mysql.jdbc.Driver
pom文件
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>com.xuxueli</groupId> <artifactId>xxl-job-executor-samples</artifactId> <version>2.3.0</version> </parent> <artifactId>xxl-job-executor-sample-springboot</artifactId> <packaging>jar</packaging> <name>${project.artifactId}</name> <description>Example executor project for spring boot.</description> <url>https://www.xuxueli.com/</url> <properties> </properties> <dependencyManagement> <dependencies> <dependency> <!-- Import dependency management from Spring Boot (依赖管理:继承一些默认的依赖,工程需要依赖的jar包的管理,申明其他dependency的时候就不需要version) --> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>${spring-boot.version}</version> <type>pom</type> <scope>import</scope> </dependency> </dependencies> </dependencyManagement> <dependencies> <!-- spring-boot-starter-web (spring-webmvc + tomcat) --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <!-- xxl-job-core --> <dependency> <groupId>com.xuxueli</groupId> <artifactId>xxl-job-core</artifactId> <version>${project.parent.version}</version> </dependency> <!-- <dependency>--> <!-- <groupId>org.springframework.boot</groupId>--> <!-- <artifactId>spring-boot-starter-data-jpa</artifactId>--> <!-- </dependency>--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> </dependencies> <build> <plugins> <!-- spring-boot-maven-plugin (提供了直接运行项目的插件:如果是通过parent方式继承spring-boot-starter-parent则不用此插件) --> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> <version>${spring-boot.version}</version> <executions> <execution> <goals> <goal>repackage</goal> </goals> </execution> </executions> </plugin> </plugins> </build> </project>
xxl-job的主pom文件
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.xuxueli</groupId> <artifactId>xxl-job</artifactId> <version>2.3.0</version> <packaging>pom</packaging> <name>${project.artifactId}</name> <description>A distributed task scheduling framework.</description> <url>https://www.xuxueli.com/</url> <modules> <module>xxl-job-core</module> <module>xxl-job-admin</module> <module>xxl-job-executor-samples</module> </modules> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> <maven.compiler.encoding>UTF-8</maven.compiler.encoding> <maven.compiler.source>1.8</maven.compiler.source> <maven.compiler.target>1.8</maven.compiler.target> <maven.test.skip>true</maven.test.skip> <netty-all.version>4.1.58.Final</netty-all.version> <gson.version>2.8.6</gson.version> <spring.version>5.3.26</spring.version> <spring-boot.version>2.4.2</spring-boot.version> <mybatis-spring-boot-starter.version>2.1.4</mybatis-spring-boot-starter.version> <mysql-connector-java.version>5.1.6</mysql-connector-java.version> <slf4j-api.version>1.7.30</slf4j-api.version> <junit.version>5.7.1</junit.version> <javax.annotation-api.version>1.3.2</javax.annotation-api.version> <groovy.version>3.0.7</groovy.version> <maven-source-plugin.version>3.2.1</maven-source-plugin.version> <maven-javadoc-plugin.version>3.2.0</maven-javadoc-plugin.version> <maven-gpg-plugin.version>1.6</maven-gpg-plugin.version> <maven-war-plugin.version>3.3.1</maven-war-plugin.version> <logback.version>1.2.10</logback.version> <log4j.version>2.17.1</log4j.version> <tomcat.version>9.0.73</tomcat.version> </properties> <build> <plugins> </plugins> </build> <licenses> <license> <name>GNU General Public License version 3</name> <url>https://opensource.org/licenses/GPL-3.0</url> </license> </licenses> <scm> <tag>master</tag> <url>https://github.com/xuxueli/xxl-job.git</url> <connection>scm:git:https://github.com/xuxueli/xxl-job.git</connection> <developerConnection>scm:git:git@github.com:xuxueli/xxl-job.git</developerConnection> </scm> <developers> <developer> <id>XXL</id> <name>xuxueli</name> <email>931591021@qq.com</email> <url>https://github.com/xuxueli</url> </developer> </developers> <dependencyManagement> <dependencies> <!-- 显式指定Tomcat版本 --> <dependency> <groupId>org.apache.tomcat.embed</groupId> <artifactId>tomcat-embed-core</artifactId> <version>${tomcat.version}</version> </dependency> <dependency> <groupId>org.apache.tomcat.embed</groupId> <artifactId>tomcat-embed-el</artifactId> <version>${tomcat.version}</version> </dependency> <dependency> <groupId>org.apache.tomcat.embed</groupId> <artifactId>tomcat-embed-websocket</artifactId> <version>${tomcat.version}</version> </dependency> <dependency> <groupId>ch.qos.logback</groupId> <artifactId>logback-classic</artifactId> <version>${logback.version}</version> </dependency> <dependency> <groupId>ch.qos.logback</groupId> <artifactId>logback-core</artifactId> <version>${logback.version}</version> </dependency> </dependencies> </dependencyManagement> <profiles> <profile> <id>release</id> <build> <plugins> <!-- Source --> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-source-plugin</artifactId> <version>${maven-source-plugin.version}</version> <executions> <execution> <phase>package</phase> <goals> <goal>jar-no-fork</goal> </goals> </execution> </executions> </plugin> <!-- Javadoc --> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-javadoc-plugin</artifactId> <version>${maven-javadoc-plugin.version}</version> <executions> <execution> <phase>package</phase> <goals> <goal>jar</goal> </goals> <configuration> <doclint>none</doclint> </configuration> </execution> </executions> </plugin> <!-- GPG --> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-gpg-plugin</artifactId> <version>${maven-gpg-plugin.version}</version> <configuration> <useAgent>false</useAgent> </configuration> <executions> <execution> <phase>verify</phase> <goals> <goal>sign</goal> </goals> </execution> </executions> </plugin> </plugins> </build> <distributionManagement> <snapshotRepository> <id>oss</id> <url>https://oss.sonatype.org/content/repositories/snapshots/</url> </snapshotRepository> <repository> <id>oss</id> <url>https://oss.sonatype.org/service/local/staging/deploy/maven2/</url> </repository> </distributionManagement> </profile> </profiles> </project>
如果文字存在不好理解的情况,对应的也会录制对应的视频发布到b站,希望可以对您有所帮忙