Paging and table division integrated query method based on MyBatis interceptor
The MyBatis interceptor mechanism realizes integrated pagination and sub-table query, which solves the problems of low paging performance, intrusion of sub-table logic and insufficient support for multi-table joint query, improves query efficiency and development efficiency, and is suitable for a variety of sharding strategies and complex SQL scenarios.
Patent Information
- Application Number
- CN202510542005.5
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-04-28
- Publication Date
- 2025-08-19
AI Technical Summary
In the prior art, the performance of paging query is low, the logic of sub-tables is strong invasive, the support for joint query of multiple tables is insufficient, and the lack of unified management leads to low development efficiency and high system complexity.
The integrated query method of paging and subtable based on MyBatis interceptor is adopted. Through custom annotation and interceptor mechanism, COUNT(*) queries are generated dynamically, table names are replaced, and non-invasive integration of paging and subtable logic is achieved. It supports multiple sharding strategies and complex SQL scenarios.
It improves the efficiency of paging query by 50%, reduces the response time of subtable query by 30%, shortens the development cycle by 70%, supports a variety of sharding strategies and complex SQL, and improves development efficiency and system compatibility.
Smart Images
Figure CN120508574A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the technical field of data sharding tables, and in particular to a paging and sharding integrated query method based on a MyBatis interceptor. Background Art
[0002] With the rapid growth of business data, traditional single-table queries are facing performance bottlenecks, and sharding technology has become the mainstream solution. At the same time, paging queries are indispensable in data display scenarios. Existing technologies have the following problems: Poor paging performance: Traditional paging requires executing two queries (COUNT(*) and LIMIT), and the statistical logic of complex SQL (such as JOIN and UNION) is difficult to optimize. The sharding logic is highly invasive: the table name replacement logic needs to be hard-coded, the code is redundant and difficult to maintain. Insufficient support for multi-table joint queries: The existing sharding solution has difficulty handling joint queries across sharded tables. Lack of unified management: The separation of paging and sharding logic leads to low development efficiency and high system complexity. Summary of the Invention
[0003] The present invention aims to provide a paging and table-splitting integrated query method based on MyBatis interceptor.
[0004] The technical solution adopted in the present invention is: The integrated query method of paging and table partitioning based on MyBatis interceptor includes the following steps: Step 1: Get the form data to be queried and divide it into pages and tables; Step 2: Create custom paging annotations and custom table sharding annotations. The custom paging annotations include the paging parameters of the current thread, and the custom table sharding annotations declare the table sharding strategy. Step 3: Add custom paging annotations and custom table annotations to the mapping file of the paging and table of the form data to be queried; Step 4: Create a paging interceptor and a table interceptor respectively; Step 5: Get the query service list and determine the current query type. If it is a paging query, execute step 6; if it is a table query, execute step 7. Step 6: intercept the form data query SQL through the paging interceptor, execute the SQL after executing the paging algorithm and return the data; Step 7: intercept the form data query SQL through the table sharding interceptor, execute the SQL after executing the table sharding algorithm and return the data.
[0005] Furthermore, the paging parameters in step 2 include the page number and page size. These parameters are stored in thread-local variables (ThreadLocal) to ensure thread isolation and avoid parameter crosstalk in a multi-threaded environment. It should be noted that the paging parameters must be read by the interceptor before SQL execution and automatically cleared after execution to prevent memory leaks.
[0006] Furthermore, in step 2, a table sharding strategy is added to the Mapper interface method. The table sharding strategy includes preset strategies (such as hash sharding and time sharding) and user-defined strategies, and the specific implementation class is associated with the strategy name.
[0007] Furthermore, the specific steps of the paging query in step 6 are as follows: Step 6-1, intercept all query requests through the paging interceptor to obtain the global operation object; Specifically, all query requests are intercepted through the Executor.query() method of MyBatis.
[0008] Step 6-2: Check whether the global operation object has a custom paging annotation; if so, extract the paging parameters (e.g. pageNum=2, pageSize=10) from the local thread variable and execute step 6-3; otherwise, skip the paging operation and directly execute the original query SQL Step 6-3: Parse the original SQL into a syntax tree (AST) and identify clauses. Remove clauses irrelevant to statistics and rewrite to generate a new COUNT SQL statement with the original query as a subquery. Specifically, the original query is wrapped into a subquery, generating SELECT COUNT(*) FROM (original query) TOTAL.
[0009] Step 6-4: Dynamically append paging conditions based on the database dialect (such as LIMIT in MySQL and ROWNUM in Oracle) to generate new paging SQL statements. Step 6-5: Get the total number of records using the COUNT SQL statement, and get the current page data using the paging SQL statement with paging conditions added. Specifically, the rewritten COUNT statement is executed through JDBC to obtain the total number of records.
[0010] Step 6-6: Build the current paging object information, bind it to the query result data, and finally return it to the caller. The paging object information includes total, pageNum, and pageSize.
[0011] Specifically, when constructing the paging object information, total, pageNum, and pageSize are encapsulated as a PageInfo object.
[0012] Furthermore, the clauses in step 6-3 include query body, JOIN, WHERE, ORDER BY and other clauses.
[0013] Furthermore, in step 6-4, a paging condition is dynamically added to the new SQL statement based on the database dialect (such as LIMIT in MySQL or ROWNUM in Oracle). The offset is calculated as (pageNum-1)*pageSize to generate the LIMIT offset and pageSize.
[0014] Furthermore, in step 6-5, it is determined whether the total number of records is zero; if so, an empty result set is directly returned.
[0015] Furthermore, in step 6-6, the paging parameters in ThreadLocal are cleared to ensure that subsequent requests are not affected.
[0016] Furthermore, the specific steps for querying the sharded tables in step 7 are as follows: Step 7-1: intercept all query requests through the table interceptor to obtain the global operation object; Specifically, the StatementHandler.prepare() method is intercepted before SQL precompilation.
[0017] Step 7-2: Determine whether the global operation object has a custom sharding annotation; if so, obtain the currently used sharding strategy and go to step 7-3; otherwise, skip the sharding operation and directly execute the original query SQL Specifically, the @ShardingRule annotation on the Mapper method is parsed to determine the currently used sharding strategy.
[0018] In step 7-3, the sharding key value is extracted from the method call parameter, and the sharding strategy (such as HashShardingStrategy) is called to calculate the actual table name and replace the logical table name in the SQL.
[0019] Specifically, when extracting shard key values, for example, hash sharding requires obtaining the userId field, and time sharding requires obtaining the createTime field. Recursive extraction of shard key values from simple types, POJO objects, or Maps is supported, adapting to different parameter structures.
[0020] In step 7-4, submit the SQL statement after replacing the table name (such as JDBC) for execution to operate the actual physical table.
[0021] Specifically, if the sharded tables are in the same database, consistency is guaranteed by relying on database transactions; if the databases are sharded, distributed transactions need to be introduced.
[0022] Furthermore, replacing the logical table name in SQL in step 7-3 specifically includes the following steps: Step 7-3-1, use JSqlParser to parse the original SQL and identify all logical table references; In step 7-3-2, replace the logical table name with the actual table name based on the mapping relationship, retaining the alias (for example, FROM user u → FROM user_123 u). Avoid accidentally replacing strings with the same name (such as table names in comments or field values).
[0023] In step 7-3-3, replace multiple tables in the JOIN query one by one to ensure that the association relationship is correct.
[0024] This invention adopts the above technical solutions and, through the MyBatis interceptor mechanism, completes paging statistics and table name replacement without modifying business code, achieving non-invasive paging and table sharding. It dynamically generates efficient COUNT(*) queries, avoiding full table scans and implementing intelligent SQL optimization. It supports multiple sharding rules, such as hash and time, and allows user-defined extensions for flexible table sharding strategies. It adapts to complex SQL scenarios (such as JOIN and UNION) through a table name mapping mechanism, enabling support for multi-table joint queries. BRIEF DESCRIPTION OF THE DRAWINGS
[0025] The present invention will be further described in detail below with reference to the accompanying drawings and specific embodiments; Figure 1 This is a structural diagram of the integrated query method for paging and table partitioning based on the MyBatis interceptor of the present invention; Figure 2 This is a schematic diagram of the implementation process of the paging interceptor of the present invention; Figure 3 This is a schematic diagram of the implementation process of the table interceptor of the present invention;. DETAILED DESCRIPTION
[0026] In order to make the purpose, technical solutions and advantages of the embodiments of the present application clearer, the technical solutions in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application.
[0027] like Figures 1 to 3 As shown in FIG1 , the present invention discloses a paging and table integrated query method based on MyBatis interceptor, which includes the following steps: Step 1: Get the form data to be queried and divide it into pages and tables; Step 2: Create custom paging annotations and custom table sharding annotations. The custom paging annotations include the paging parameters of the current thread, and the custom table sharding annotations declare the table sharding strategy. Step 3: Add custom paging annotations and custom table annotations to the mapping file of the paging and table of the form data to be queried; Step 4: Create a paging interceptor and a table interceptor respectively; Step 5: Get the query service list and determine the current query type. If it is a paging query, execute step 6; if it is a table query, execute step 7. Step 6: intercept the form data query SQL through the paging interceptor, execute the SQL after executing the paging algorithm and return the data; Step 7: intercept the form data query SQL through the table sharding interceptor, execute the SQL after executing the table sharding algorithm and return the data.
[0028] Furthermore, the paging parameters in step 2 include the page number and page size. These parameters are stored in thread-local variables (ThreadLocal) to ensure thread isolation and avoid parameter crosstalk in a multi-threaded environment. It should be noted that the paging parameters must be read by the interceptor before SQL execution and automatically cleared after execution to prevent memory leaks.
[0029] Furthermore, in step 2, a table sharding strategy is added to the Mapper interface method. The table sharding strategy includes preset strategies (such as hash sharding and time sharding) and user-defined strategies, and the specific implementation class is associated with the strategy name.
[0030] Furthermore, the specific steps of the paging query in step 6 are as follows: Step 6-1, intercept all query requests through the paging interceptor to obtain the global operation object; Specifically, all query requests are intercepted through the Executor.query() method of MyBatis.
[0031] Step 6-2: Check whether the global operation object has a custom paging annotation; if so, extract the paging parameters (e.g. pageNum=2, pageSize=10) from the local thread variable and execute step 6-3; otherwise, skip the paging operation and directly execute the original query SQL Step 6-3: Parse the original SQL into a syntax tree (AST) and identify clauses. Remove clauses irrelevant to statistics and rewrite to generate a new COUNT SQL statement with the original query as a subquery. Specifically, the original query is wrapped into a subquery, generating SELECT COUNT(*) FROM (original query) TOTAL.
[0032] Step 6-4: Dynamically append paging conditions based on the database dialect (such as LIMIT in MySQL and ROWNUM in Oracle) to generate new paging SQL statements. Step 6-5: Get the total number of records using the COUNT SQL statement, and get the current page data using the paging SQL statement with paging conditions added. Specifically, the rewritten COUNT statement is executed through JDBC to obtain the total number of records.
[0033] Step 6-6: Build the current paging object information, bind it to the query result data, and finally return it to the caller. The paging object information includes total, pageNum, and pageSize.
[0034] Specifically, when constructing the paging object information, total, pageNum, and pageSize are encapsulated as a PageInfo object.
[0035] Furthermore, the clauses in step 6-3 include query body, JOIN, WHERE, ORDER BY and other clauses.
[0036] Furthermore, in step 6-4, a paging condition is dynamically added to the new SQL statement based on the database dialect (such as LIMIT in MySQL or ROWNUM in Oracle). The offset is calculated as (pageNum-1)*pageSize to generate the LIMIT offset and pageSize.
[0037] Furthermore, in step 6-5, it is determined whether the total number of records is zero; if so, an empty result set is directly returned.
[0038] Furthermore, in step 6-6, the paging parameters in ThreadLocal are cleared to ensure that subsequent requests are not affected.
[0039] Furthermore, the specific steps for querying the sharded tables in step 7 are as follows: Step 7-1: intercept all query requests through the table interceptor to obtain the global operation object; Specifically, the StatementHandler.prepare() method is intercepted before SQL precompilation.
[0040] Step 7-2: Determine whether the global operation object has a custom sharding annotation; if so, obtain the currently used sharding strategy and go to step 7-3; otherwise, skip the sharding operation and directly execute the original query SQL Specifically, the @ShardingRule annotation on the Mapper method is parsed to determine the currently used sharding strategy.
[0041] In step 7-3, the sharding key value is extracted from the method call parameter, and the sharding strategy (such as HashShardingStrategy) is called to calculate the actual table name and replace the logical table name in the SQL.
[0042] Specifically, when extracting shard key values, for example, hash sharding requires obtaining the userId field, and time sharding requires obtaining the createTime field. Supports recursive extraction of shard key values from simple types, POJO objects, or Maps, adapting to different parameter structures. Hash sharding: tableName=logicTable+"_"+(hash(userId)%128). Time sharding: tableName=logicTable+"_" + yyyyMM.format(createTime). Multi-table association support: If the SQL involves multiple logical tables (such as user and order), the actual table name must be calculated independently for each table, forming a mapping relationship {user: "user_123", order: "order_123"}.
[0043] In step 7-4, submit the SQL statement after replacing the table name (such as JDBC) for execution to operate the actual physical table.
[0044] Specifically, if the sharded tables are in the same database, consistency is guaranteed by relying on database transactions; if the databases are sharded, distributed transactions need to be introduced.
[0045] Furthermore, replacing the logical table name in SQL in step 7-3 specifically includes the following steps: Step 7-3-1, use JSqlParser to parse the original SQL and identify all logical table references; In step 7-3-2, replace the logical table name with the actual table name based on the mapping relationship, retaining the alias (for example, FROM user u → FROM user_123 u). Avoid accidentally replacing strings with the same name (such as table names in comments or field values).
[0046] In step 7-3-3, replace multiple tables in the JOIN query one by one to ensure that the association relationship is correct.
[0047] The principle of the present invention is described in detail below: Definition: Sharding is a technique for partitioning large datasets into multiple subsets of fixed size, used to optimize query performance and enhance user experience. In this paper, paging logic is implemented by dynamically generating COUNT(*) statistical queries and LIMIT clauses.
[0048] Table sharding is a technique that splits a single table's data into multiple physical tables based on rules (such as hashing or time). It is used to address performance issues caused by excessive data volume in a single table. The table sharding strategy of this invention is implemented by dynamically replacing table names.
[0049] MyBatis is a Java persistence layer framework that simplifies database operations by configuring SQL mappings through XML or annotations. The present invention implements non-invasive integration of paging and table sharding logic based on the MyBatis interceptor mechanism. JsqlParser, a SQL statement parsing tool, is used to analyze SQL structures and dynamically rewrite them (such as generating COUNT(*) queries). An interceptor is a mechanism that dynamically intercepts specific operations in the software execution process and allows the insertion of custom processing logic. In the Java MyBatis framework, interceptors are implemented through a plug-in mechanism to extend or modify the framework's default behavior without modifying the original code.
[0050] This paper uses JSqlParser to dynamically generate efficient COUNT(*) queries, implementing intelligent SQL parsing and rewriting to avoid full table scans. It also dynamically replaces table names by modifying BoundSql objects through reflection, supporting multi-table join queries. It also uses the PageMethod class to pass paging parameters, enabling thread-local variable management and ensuring thread safety.
[0051] This invention utilizes the above technical solution, offering the following advantages: 1) Performance Improvement: Paging and statistical query efficiency increases by over 50%, and sharded table query response time decreases by 30%. 2) Development Efficiency Improvement: Paging and sharding logic is non-invasive, shortening development cycles by 70%. 3) Flexible Expansion: Supports customizable sharding strategies, adapting to various scenarios such as hashing, time, and geolocation. 4) High Compatibility: Compatible with mainstream databases such as MySQL and PostgreSQL, supporting complex SQL statements (such as subqueries and JOINs).
[0052] Obviously, the described embodiments are part of the embodiments of the present application, rather than all of the embodiments. In the absence of conflict, the embodiments in the present application and the features in the embodiments can be combined with each other. The components of the embodiments of the present application generally described and shown in the drawings here can be arranged and designed in various different configurations. Therefore, the detailed description of the embodiments of the present application is not intended to limit the scope of the application for protection, but merely represents the selected embodiments of the present application. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of this application.
Claims
1. The integrated query method of paging and table partitioning based on MyBatis interceptor is characterized by: It includes the following steps: Step 1: Get the form data to be queried and divide it into pages and tables; Step 2: Create custom paging annotations and custom table sharding annotations. The custom paging annotations include the paging parameters of the current thread, and the custom table sharding annotations declare the table sharding strategy. Step 3: Add custom paging annotations and custom table annotations to the mapping file of the paging and table of the form data to be queried; Step 4: Create a paging interceptor and a table sharding interceptor respectively. The paging interceptor and table sharding interceptor are used to intercept the data table query SQL and execute the SQL and return the data after executing the corresponding paging or table sharding algorithm. Step 5: Get the query service list and determine the current query type. If it is a paging query, execute step 6; if it is a table query, execute step 7. Step 6: intercept the form data query SQL through the paging interceptor, execute the SQL after executing the paging algorithm and return the data; Step 7: intercept the form data query SQL through the table sharding interceptor, execute the SQL after executing the table sharding algorithm and return the data.
2. The paging and table-splitting integrated query method based on MyBatis interceptor according to claim 1 is characterized in that: In step 2, the paging parameters include the page number and page size. The paging parameters are stored in local thread variables to ensure thread isolation. The paging parameters must be read by the interceptor before SQL execution and automatically cleared after execution.
3. The paging and table-splitting integrated query method based on MyBatis interceptor according to claim 1 is characterized in that: In step 2, add a table sharding strategy to the Mapper interface method. The table sharding strategy includes preset strategies and user-defined strategies, and the specific implementation class is associated with the strategy name; the preset strategies include hash sharding and time sharding.
4. The paging and table-splitting integrated query method based on MyBatis interceptor according to claim 1 is characterized in that: The specific steps for paging query in step 6 are as follows: Step 6-1, intercept all query requests through the paging interceptor to obtain the global operation object; Step 6-2: Check whether the global operation object has a custom paging annotation; if so, extract the paging parameters from the local thread variable and execute step 6-3; otherwise, skip the paging operation and directly execute the original query SQL Step 6-3: Parse the original SQL into a syntax tree and identify clauses. Remove clauses irrelevant to statistics and rewrite to generate a new COUNT SQL statement with the original query as a subquery. Step 6-4: Dynamically add paging conditions based on the database dialect to generate new paging SQL statements; Step 6-5: Get the total number of records using the COUNT SQL statement, and get the current page data using the paging SQL statement with paging conditions added. Step 6-6, build the current paging object information, bind the paging object information with the query result data, and finally return it to the caller.
5. The paging and table-splitting integrated query method based on MyBatis interceptor according to claim 4 is characterized in that: In step 6-4, offset = (pageNum-1) * pageSize is calculated based on the paging parameters to generate LIMIT offset and pageSize.
6. The paging and table-splitting integrated query method based on MyBatis interceptor according to claim 4 is characterized in that: In step 6-5, determine whether the total number of records is zero; if so, return an empty result set directly.
7. The paging and table-splitting integrated query method based on MyBatis interceptor according to claim 1 is characterized in that: The specific steps for table sharding query in step 7 are as follows: Step 7-1: intercept all query requests through the table interceptor to obtain the global operation object; Step 7-2: Determine whether the global operation object has a custom sharding annotation. If so, obtain the currently used sharding strategy and proceed to step 7-3. Otherwise, skip the sharding operation and directly execute the original query SQL. Step 7-3: Extract the shard key value from the method call parameter, call the sharding strategy to calculate the actual table name, and replace the logical table name in the SQL; In step 7-4, submit the SQL statement after replacing the table name to execute it to operate the actual physical table.
8. The paging and table-splitting integrated query method based on MyBatis interceptor according to claim 7 is characterized in that: Replacing the logical table name in SQL in step 7-3 specifically includes the following steps: Step 7-3-1, use JSqlParser to parse the original SQL and identify all logical table references; Step 7-3-2: Replace the logical table name with the actual table name based on the mapping relationship, and retain the alias; In step 7-3-3, replace multiple tables in the JOIN query one by one to ensure that the association relationship is correct.