A data query method and system based on Mybatis to prevent server memory overflow

Abstract

The present invention relates to the field of computer technology, and provides a data query method and system for preventing server memory overflow based on Mybatis, including responding to a target query request; configuring a query restriction condition of a target query plug-in according to the target query request; triggering a limit plug-in interceptor when a business end initiates a database query; judging whether it is necessary to execute a limit splicing logic to optimize the maximum number of returned data of a target query statement in a query database according to the interception logic preset by the limit plug-in interceptor; in a non-statistical query mode, entering the limit splicing logic, initiating a query for a limited data amount to a database through a target query statement containing a limit condition, and returning a query result for a limited data amount. By dynamically adding a limit condition, server memory overflow caused by massive data loading is effectively prevented, real-time memory usage is monitored and query restrictions are adjusted accordingly, and query strategies are automatically adjusted to adapt to the current system resource status, ensuring the optimal performance of query operations.

Description

Technical Field

[0001] The present invention relates to the field of computer technology, and in particular to a data query method and system based on Mybatis for preventing server memory overflow. Background Art

[0002] As the business develops, the amount of database data also grows dramatically. As the existing query logic in the business system becomes inapplicable as the amount of data increases, sometimes the query statement has no query conditions due to bugs in the business logic that triggers the query, or the query request initiated by the external system has no query conditions, resulting in a massive amount of data being loaded from the database to the application server at one time. The massive amount of data occupies a large amount of memory on the application server, and even causes the server to overflow and crash. In the face of this problem, the existing technology has the following two problems:

[0003] (1) The shortcomings of traditional forward troubleshooting methods. Due to the large number and complexity of various data query scenarios in the business system and the uncontrollable external query requests, traditional troubleshooting and optimization methods are difficult to fully implement. In addition, the optimization effect is time-sensitive, resulting in the upper-level query entry always having some unexpected requests to query massive data. This makes it difficult to effectively cope with the growth of data volume in the long term and the problem cannot be eradicated.

[0004] (2) Database-level restrictions: Restrictions are set by the DBA at the database level, such as setting the maximum query data volume SQL_big_selects and the maximum execution time max_execution_time. This one-size-fits-all solution cannot meet the actual query quantity requirements of different databases or tables for various businesses. Different business servers have different memory configurations, and different databases and tables may not occupy the same amount of memory. At the same time, database-level restrictions cannot adjust the query data volume in real time according to the system resource status of the application server.

[0005] Therefore, a method is needed to intelligently adjust query limits to dynamically respond to changing business needs and server resource conditions, thereby protecting application servers from data query overload. Summary of the invention

[0006] This application proposes a data query method and system based on Mybatis to prevent server memory overflow, which overcomes the limitations of the prior art, including the shortcomings of traditional troubleshooting methods and inflexible restrictions at the database level. Through dynamic query restrictions and memory management, it adapts to different business and data table requirements and effectively prevents server memory overflow and performance problems caused by massive data queries:

[0007] The present invention provides a data query method for preventing server memory overflow based on Mybatis, comprising:

[0008] responding to a target query request;

[0009] According to the target query request, the target query plug-in mechanism is introduced into the application server and the query restriction conditions of the target query plug-in are configured. The target query plug-in is a limit plug-in based on the Mybatis architecture;

[0010] When the business end initiates a database query and enters the Executor stage, the limit plug-in interceptor is triggered. The Executor stage is the process of executing the query target query statement based on the Mybatis architecture and mapping the query results to Java objects;

[0011] According to the preset interception logic of the limit plug-in interceptor, determine whether it is necessary to execute the limit splicing logic to optimize the maximum number of returned data for the target query statement in the query database;

[0012] In non-statistical query mode, enter the limit splicing logic, initiate a limited data volume query to the database through a target query statement containing a limit condition, and return the query result of the limited data volume to ensure the normal operation of the application server.

[0013] Preferably, it also includes dynamically adjusting the limited data amount returned by the query based on the current memory usage of the JVM to limit the data query data amount for real-time adjustment and optimization;

[0014] Dynamically adjust the amount of data returned by the query based on the current JVM memory usage to limit the amount of data query data for real-time adjustment optimization, further including:

[0015] Get the current JVM memory usage in real time;

[0016] When the memory usage reaches or exceeds the memory usage threshold, the amount of available memory for the current JVM is calculated;

[0017] Get the space occupied by a piece of data in the current database table, and calculate the real-time limit data volume by dividing the available memory by the space occupied by a piece of data, so as to obtain the number of data that can be safely processed by SQL query under the current available memory status;

[0018] Determine whether the data volume limited by the original limit configuration is greater than the data volume limited by the real-time limit;

[0019] If the data volume specified by the existing limit condition in the target query statement is larger than the real-time limit data volume calculated based on the available memory optimization, the limit condition is updated to the calculated real-time limit data volume;

[0020] If the target query statement does not contain a limit condition and does not have a statistical query instruction, the limit condition is forced to be added to the target query statement to limit the data volume to no more than the calculated real-time limit data volume.

[0021] Preferably, the configuration of the limit plug-in interceptor is performed by adding query restriction conditions through the application server for file configuration, and the configuration items include the global switch of the limit plug-in, the number of limit plug-in query limit items, the memory usage of the forced limit optimization, and the number of query limit items in the relevant configuration item table.

[0022] Preferably, when the business end initiates a database query and enters the Executor stage, the limit plug-in interceptor is triggered. When the application server is running and the global switch is activated, the query target request is intercepted and processed through the Mybatis interception interface, including:

[0023] Call the limit plugin interceptor before or after the executor executes the target query statement;

[0024] Configure the target interception condition. The target interception condition specifies the target method and parameter type to be intercepted based on the signature matching mechanism of Mybatis to intercept the target method call containing any parameter type, and configure a signature for each parameter type combination;

[0025] Create a Java class to implement the Interceptor interface of Mybatis, and rewrite the intercept method to insert custom processing logic to extend or modify the default behavior of Mybatis by implementing custom interception logic;

[0026] Execute the target method of the Executor interface and intercept all target method calls that meet the target interception conditions;

[0027] The target method is a query method, and the parameter type list of the target method includes one or more combinations of MappedStatement, Object, RowBounds, ResultHandler, CacheKey, and BoundSql.

[0028] Preferably, judging whether it is necessary to execute the limit splicing logic to optimize the maximum number of returned data of the target query statement in the query database according to the interception logic preset by the limit plug-in interceptor includes:

[0029] Determine whether the global switch in the application server is enabled;

[0030] When the limit plug-in interceptor corresponding to the global switch is turned on, determine whether the plug-in annotation is closed in the target method link;

[0031] When the target method link closes the plug-in annotation, determine whether the current target query statement contains a statistical method;

[0032] When the current target query statement contains a statistical method, the limit concatenation logic is skipped and the query operation is executed directly;

[0033] When the current target query statement does not contain a statistical method, determine whether the current target query statement has a limit condition. If yes, execute the limit concatenation logic.

[0034] The current query restriction configuration is read to determine whether the memory usage of the current JVM exceeds the memory usage threshold. If the memory usage of the current JVM does not exceed the memory usage threshold, the query operation is performed under the current query restriction.

[0035] Preferably, when the current target query statement does not contain a statistical method, determining whether the current target query statement has a limit condition, and if so, executing the limit concatenation logic further includes:

[0036] Get the MappedStatement object in the current target query statement, and get the BoundSql object and SQL string. The BoundSql object contains the SQL statement and related description objects parsed by Mybatis. The content of the SQL string is the target query statement.

[0037] Determine whether the SQL string contains a limit clause or whether a statistical function is used;

[0038] If the SQL string already contains a limit clause or uses a statistical function, the limit plug-in interceptor does not automatically add a limit clause.

[0039] As preferably, it also includes:

[0040] When the plug-in annotation is added to the target method, the target method and all SQL query instructions in the target method link ignore the behavior of automatically adding the limit clause, so that the limit personalization switch of the current business is turned on and the query is based on the current target query statement.

[0041] Based on the same inventive concept, the present invention also provides a data query system for preventing server memory overflow based on Mybatis, comprising:

[0042] A receiving module, used for responding to a target query request;

[0043] The configuration management module is used to introduce the target query plug-in mechanism into the application server according to the target query request and configure the query restriction conditions of the target query plug-in. The target query plug-in is a limit plug-in based on the Mybatis architecture.

[0044] Query interception module: When the business end initiates a database query and enters the Executor stage, the limit plug-in interceptor is triggered. The Executor stage is the process of executing the query target query statement based on the Mybatis architecture and mapping the query results to Java objects;

[0045] The data processing module determines whether it is necessary to execute the limit splicing logic to optimize the maximum amount of data returned by the target query statement in the query database according to the preset interception logic of the limit plug-in interceptor; in the non-statistical query mode, it enters the limit splicing logic, initiates a query to limit the amount of data to the database through the target query statement containing the limit condition, and returns the query result of the limited amount of data to ensure the normal operation of the application server.

[0046] The present invention provides an electronic device, comprising:

[0047] A memory, the memory is used to store the processing program;

[0048] The processor implements the data query method for preventing server memory overflow based on Mybatis as described above when executing the processing program.

[0049] The present invention provides a readable storage medium, on which a processing program is stored. When the processing program is executed by a processor, the data query method for preventing server memory overflow based on Mybatis as described above is implemented.

[0050] Compared with the prior art, the present invention has at least one of the following beneficial effects:

[0051] (1) By dynamically adding limit conditions, the present invention can effectively prevent server memory overflow caused by massive data loading, and significantly improve the stability and reliability of the application server.

[0052] (2) The present invention allows specific queries to escape the automatic limit logic through custom plug-in annotations, providing flexibility and adaptability to different business needs, so that the system can be optimized more accurately for various scenarios.

[0053] (3) The automatic limit splicing logic insertion of the present invention reduces the need for manual optimization of query logic, and reduces system maintenance costs and technical debt caused by query efficiency issues.

[0054] (4) By monitoring real-time memory usage and adjusting query limits accordingly, the present invention can respond to server performance changes in real time, automatically adjust query strategies to adapt to the current system resource status, and ensure optimal performance of query operations.

[0055] (5) By preventing unnecessary large-scale data processing, the present invention enables the system to process query requests more efficiently and avoid performance bottlenecks caused by processing too much data. BRIEF DESCRIPTION OF THE DRAWINGS

[0056] Figure 1 The present invention is a flowchart of the steps of a data query method for preventing server memory overflow based on Mybatis;

[0057] Figure 2 A flowchart of a data query method for preventing server memory overflow based on Mybatis of the present invention;

[0058] Figure 3 A schematic diagram of a query process including a statistical method in an embodiment of the present invention;

[0059] Figure 4 A schematic diagram of a query process in an embodiment of the present invention without a statistical method;

[0060] Figure 5 A schematic diagram of a query flow for real-time optimization of limiting data volume in an embodiment of the present invention;

[0061] Figure 6 Schematic diagram of a system device in an embodiment of the present invention. DETAILED DESCRIPTION

[0062] In order to make the purpose, technical solution and advantages of the embodiments of the present application clearer, the technical solution 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. Obviously, the described embodiments are part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0063] Those skilled in the art will appreciate that, unless otherwise stated, the singular forms "a", "an", "said" and "the" used herein may also include plural forms. It should be further understood that the term "comprising" used in the specification of the present invention refers to the presence of the features, integers, steps, operations, elements and / or components, but does not exclude the presence or addition of one or more other features, integers, steps, operations, elements, components and / or groups thereof.

[0064] First embodiment

[0065] In traditional data query, under normal business operation, the database needs to be queried. At the beginning, the amount of data queried is small, and after a period of time, such as 1 or 2 years, the amount of data reaches tens of millions. During the query process, due to abnormal situations, the scenario conditions may be lost or directly discarded after the data is run. The data of the entire table in the database is directly taken to the application server. Generally, the memory of a database such as 8 cores and 16G is sufficient to store the scenario conditions, while the memory of the application server is relatively small compared to the memory of the database, such as 2 cores and 4G. In this case, the data of the entire table in the database is directly taken to the application server, and the application server directly reports an error due to insufficient memory, which can easily lead to slower system response speed, application crashes, and reduced data processing capabilities.

[0066] As the amount of data increases, traditional database query management methods face the following challenges. First, database query operations are often controlled by hard restrictions set by database administrators (DBAs), such as the maximum query data volume and the maximum execution time, to prevent queries from over-consuming system resources. However, this "one-size-fits-all" strategy does not meet the needs of all business scenarios, especially in environments involving multiple businesses and different server configurations. These restrictions may not be sufficient to meet the actual query requirements of different libraries or tables for various businesses, because the amount of data and processing requirements of each business vary greatly. In addition, these restrictions cannot be flexibly adjusted according to the real-time status of the application server, making the system unable to effectively respond when the amount of data increases sharply. Secondly, conventional solutions such as regular log review and code review can detect problems, but these methods are usually labor-intensive and time-consuming. For complex or large business systems, it is difficult to fully implement them, which not only increases labor costs, but also makes it difficult to effectively solve problems in the long run.

[0067] Since traditional methods rely on post-diagnosis and static restrictions, they show disadvantages when processing sudden large-scale data requests, especially in an environment with continuous growth in data and business. Existing technical solutions are difficult to adapt to changes in system resources in real time and cannot effectively prevent or solve problems such as memory overflow, resulting in system stability and performance being affected.

[0068] Based on the above considerations, the present invention proposes a data query method based on Mybatis to prevent server memory overflow, so as to effectively prevent the problem of application server memory overflow caused by querying a large amount of data. By dynamically adding limit conditions to SQL queries within the Mybatis framework, the data load is automatically adjusted according to the real-time system status and query requirements. By real-time monitoring of the server's memory usage, the present invention can automatically adjust the data volume limit in the query according to the current memory load, thereby effectively preventing memory overflow caused by large-scale data queries. In addition, the present invention also introduces custom annotations, namely plug-in annotations, which allow developers to skip automatic limit logic as needed, and provide flexibility for statistical or analytical queries that require a complete data set. This intelligent query management method not only improves the stability and scalability of the system, but also provides an efficient and secure solution strategy for query operations in large-scale data environments. The specific implementation method is as follows:

[0069] like Figure 1 , 2 As shown in Figure 6, this embodiment provides a data query method for preventing server memory overflow based on Mybatis, including:

[0070] Step S1: responding to a target query request, especially a query demand in a massive data environment.

[0071] It should be specifically pointed out that the target query request in this technical solution is mainly to deal with the query requirements in the massive data environment, although it is also applicable to processing large amounts of data. However, this solution is mainly used to design, optimize and manage massive data queries, effectively prevent system performance degradation or crashes caused by overloaded data queries, and ensure efficient and stable execution of data queries on extremely large data sets.

[0072] Since massive data queries involve processing TB-level or even PB-level data sets, the management and requirements of such data sets require higher-level data processing technologies and architectures, such as distributed databases and big data processing platforms. Therefore, this technical solution does not require a traditional stand-alone database system to effectively process queries for these massive data. By dynamically adjusting the limit conditions in SQL queries through intelligent plug-ins, the amount of data loaded is optimized according to the real-time system resource usage, thereby ensuring that the system's memory is not over-consumed when processing massive data, and preventing system crashes or performance bottlenecks caused by data overload. This method makes data queries more efficient and stable, and is suitable for complex business scenarios that require real-time analysis and processing of large-scale data sets, such as network security, large-scale e-commerce, social network data analysis, and other scenarios.

[0073] Step S2: According to the target query request, a target query plug-in mechanism is introduced into the application server and query restriction conditions of the target query plug-in are configured. The target query plug-in is a limit plug-in based on the Mybatis architecture.

[0074] Step S3: When the business end initiates a database query and enters the Executor stage, the limit plug-in interceptor is triggered. The Executor stage is a process of executing the query target query statement based on the Mybatis architecture and mapping the query results to Java objects;

[0075] Step S4: judging whether it is necessary to execute the limit splicing logic to optimize the maximum amount of returned data for the target query statement in the query database according to the interception logic preset by the limit plug-in interceptor;

[0076] Step S5: In non-statistical query mode, enter the limit splicing logic, initiate a limited data volume query to the database through a target query statement containing a limit condition, and return a query result of the limited data volume to ensure the normal operation of the application server.

[0077] In statistical query mode, the target query statements, i.e., SQL queries, containing statistical functions such as COUNT, SUM, AVG, MIN, MAX, etc. can be automatically identified. Since limiting the amount of data may interfere with the accuracy of the statistical results, the logic of adding limit conditions will be automatically skipped for statistical queries so that the query can be directly executed and return the full amount of data, supporting accurate data analysis and report production. This ensures that performance optimization measures do not affect the completeness and accuracy of the results in business scenarios that require a complete data set for detailed analysis and decision support. In addition, all calculations in the query logic are completed within the database, and statistical queries usually only return a single summary result such as the total number of data. Such query processing does not introduce additional data sets into the application server, thereby avoiding memory pressure that may be caused by large-scale data processing.

[0078] Furthermore, it also includes dynamically adjusting the amount of limited data returned by the query based on the current memory usage of the JVM to limit the amount of data query and perform real-time adjustment and optimization;

[0079] Dynamically adjust the amount of data returned by the query based on the current JVM memory usage to limit the amount of data query data for real-time adjustment optimization, further including:

[0080] Get the current JVM memory usage in real time;

[0081] When the memory usage reaches or exceeds the memory usage threshold, the amount of available memory for the current JVM is calculated;

[0082] Get the space occupied by a piece of data in the current database table, and calculate the real-time limit data volume by dividing the available memory by the space occupied by a piece of data, so as to obtain the number of data that can be safely processed by SQL query under the current available memory status;

[0083] Determine whether the data volume limited by the original limit configuration is greater than the data volume limited by the real-time limit;

[0084] If the data volume specified by the existing limit condition in the target query statement is larger than the real-time limit data volume calculated based on the available memory optimization, the limit condition is updated to the calculated real-time limit data volume;

[0085] If the target query statement does not contain a limit condition and does not have a statistical query instruction, the limit condition is forced to be added to the target query statement to limit the data volume to no more than the calculated real-time limit data volume.

[0086] Specifically, the key to this step is to monitor and adjust query limits in real time to optimize memory usage and prevent overflow. First, the system obtains the memory usage of the current Java virtual machine JVM in real time. When it is detected that the memory usage exceeds the memory usage threshold of 70%, this memory usage threshold can be adjusted according to the actual server performance requirements to calculate the available memory space of the current JVM. Next, based on the average data size of each record in the database, the personalized data size setting configured by the user is first used. If not configured, the default value is used to estimate the maximum number of records that the system can safely process under the current memory conditions. This calculation result will determine the dynamically generated limit value. If the existing limit value in the query exceeds this calculation result, or the original SQL statement, that is, the original target query statement, does not set a limit and the query does not belong to the aggregation type, such as the target query statement contains count, sum, avg, min, max or distinct queries, the system will automatically adjust or add limit conditions to ensure that data loading does not exceed the memory capacity limit, thereby effectively preventing memory overflow caused by large amounts of data loading. This automatic adjustment strategy not only improves the stability of the system, but also enhances the ability to cope with sudden high load situations.

[0087] Among them, based on the average data size of each record in the database, the average data size first uses the personalized data size setting configured by the user. If not configured, the default value is used to estimate the maximum number of records that the system can safely process under the current memory conditions. The specific calculation process is as follows:

[0088] (1) Get the current memory usage and available memory. Use JVM monitoring tools such as JMX and Java Management Extensions to get the total memory and used memory of the current JVM. The formula for calculating available memory is: Available memory = JVM total memory - used memory

[0089] (2) Determine the size of each piece of data. If a specific data size has been configured for a table, use that configuration value. If no specific data size has been configured, use a preset default value.

[0090] (3) Calculate the maximum number of records based on the available memory and the size of each data item: maximum number of records = available memory / size of each data item. Ensure that under the current memory conditions, even if the system loads the maximum number of records, it will not cause memory overflow.

[0091] (4) Adjust the limit value of the SQL query. If the original SQL query already contains a limit value and the value is less than the calculated maximum number of records, keep the original limit unchanged. If the limit value of the original SQL query is greater than the calculated maximum number of records or there is no limit value in the SQL query, set the limit value to the calculated maximum number of records. For non-aggregate queries, which do not use aggregate functions such as count and sum, always make sure to add or adjust the limit value to avoid loading a large amount of data at one time.

[0092] Step S5: The database executes the modified SQL query and sends the query data results that meet the query restrictions to the application server. Specifically, the application service initiates a data query to the database with the query SQL containing the limit condition; the database executes the received modified SQL query to ensure data retrieval in accordance with the preset restriction conditions; the application server receives and processes the data results from the database query to complete the processing of the query request.

[0093] Therefore, this step can save the cost of manual troubleshooting and automatically limit all existing and future database queries, greatly reducing the cost of manual troubleshooting and optimization. In addition, once set, it is permanently valid for all queries, greatly improving the efficiency of problem solving and the stability of the system. At the same time, it is transparent and easy to maintain. The operation process is completely transparent to the user, and there is no need to understand the internal implementation details. This lowers the technical threshold and makes maintenance and expansion easier. By analyzing the SQL query, determine whether the limit conditions need to be spliced. Dynamically adjust the query limit data volume according to the real-time memory usage and server performance to avoid server memory overflow caused by querying too much data at one time, while ensuring that statistical queries that require a large amount of data will not be improperly affected, ensuring the diversity of the business and the accuracy of the query.

[0094] Finally, through the customized release annotation, the present invention allows specific business logic to skip limit splicing, ensuring that query requirements in special scenarios are not affected. This feature not only increases the applicability of the query method, but also provides additional flexible configuration options, allowing the business to adjust the query strategy according to specific needs.

[0095] Further, in step S2, the configuration of the limit plug-in interceptor is configured by adding query restriction conditions through the application server. The configuration items include the global switch of the limit plug-in, the number of limit plug-in query restrictions, the memory usage rate of the mandatory limit optimization, and the number of query restrictions in the relevant configuration item table. In addition, the relevant configuration item table also includes the size of a single data item and the default size of a single data item, which is used to dynamically add limit restriction conditions to the SQL query in the query request according to the performance status of the application server. The following is an example of the query restriction condition configuration in step S2, and the specific data is set according to the system performance:

[0096] (1) Enable the global switch and set the data query method to prevent server memory overflow in the file configuration of the application server to ensure that all query requests are processed through this filtering layer.

[0097] Sample configuration: mybatis.limit.enable=true;

[0098] The mybatis.limit.enable global switch is used to control whether the data query method is enabled. When set to true, the data query method that prevents server memory overflow is automatically activated and a limit condition is added to the SQL statement when necessary.

[0099] (2) Set the limit plug-in query limit number, and add the query limit number, that is, the query limit data volume configuration in the data query method to prevent server memory overflow in the file configuration of the application service. The query limit data volume specifies the maximum number of data returned for each query request to prevent excessive data loading from causing server memory overflow.

[0100] Sample configuration: mybatis.limit.size=10000;

[0101] mybatis.limit.size is used to specify the maximum number of records that can be returned by each SQL query. This parameter directly controls the amount of data loaded, which helps prevent overflow caused by a large amount of data being loaded into memory at once.

[0102] (3) Add a mandatory limit to optimize the memory usage in the application service configuration, that is, set a threshold for memory usage. When the actual usage exceeds this value, the amount of data in the query result is automatically reduced.

[0103] Sample configuration: mybatis.limit.force.opt.memo.rate=70;

[0104] mybatis.limit.force.opt.memo.rate is used to set the threshold of server memory usage. When the server's memory usage exceeds this value, such as 70% in the example, the data query system that prevents server memory overflow will automatically adjust the data loading limit in the SQL query to avoid memory overflow.

[0105] (4) Add a data size configuration for the relevant table to the configuration of the application service, that is, set the size of data records for different tables. This helps to more accurately calculate memory requirements and adjust query limits.

[0106] Sample configuration: mybatis.limit.table.1.size=1;

[0107] Sample configuration: mybatis.limit.table.2.size=2;

[0108] mybatis.limit.table.1.size and mybatis.limit.table.2.size are used to set the size estimate of a single data entry for a specific database table. This setting can accurately calculate the amount of data that can be safely queried in the current memory state.

[0109] (5) Add the default size configuration for the relevant table data in the configuration of the application service. That is, when the table-specific data size is not configured, the default value will be used to calculate the query limit.

[0110] Example configuration: mybatis.limit.default.size=10

[0111] mybatis.limit.default.size is used to set the default single data size. This parameter is used to provide a fallback data size estimate when the size is not configured for a specific table.

[0112] In summary, the mybatis.limit series of configuration keys are used to control multiple behaviors related to the Mybatis plug-in. Each key is followed by a specific sub-configuration item to refine the behavior of the plug-in. It not only protects the application server from memory pressure, but also optimizes query response time and stability, which is particularly suitable for data-intensive application environments.

[0113] Further, in step S3, when the business end initiates a database query and enters the Executor stage, the limit plug-in interceptor is triggered. When the application server is running and the global switch is activated, the query target request is intercepted and processed through the Mybatis interception interface, including:

[0114] Call the limit plugin interceptor before or after the executor executes the target query statement;

[0115] Configure the target interception condition. The target interception condition specifies the target method and parameter type to be intercepted based on the signature matching mechanism of Mybatis to intercept the target method call containing any parameter type, and configure a signature for each parameter type combination;

[0116] Create a Java class to implement the Interceptor interface of Mybatis, and rewrite the intercept method to insert custom processing logic to extend or modify the default behavior of Mybatis by implementing custom interception logic;

[0117] Execute the target method of the Executor interface and intercept all target method calls that meet the target interception conditions; when the query or update method of the Executor interface is called, the Interceptor interception interface will determine whether to execute the interception based on the set interception logic.

[0118] The target method is a query method, and the parameter type list of the target method includes one or more combinations of MappedStatement, Object, RowBounds, ResultHandler, CacheKey, and BoundSql.

[0119] Specifically, in the Mybatis framework, when the business initiates a database query and assembles an SQL statement, a custom interceptor based on org.apache.ibatis.plugin.Interceptor, namely the Interceptor interception interface, will be triggered. This interceptor intervenes in the query process by overriding the intercept method. The specific triggering time is when calling the query method of the Executor interface. The parameters of this method include MappedStatement, Object, RowBounds, ResultHandler, CacheKey, and BoundSql, or a combination of MappedStatement, Object, RowBounds, and ResultHandler. This allows intervention before the SQL query is executed, such as dynamically inserting limit conditions to control the amount of data loaded, thereby preventing memory overflow problems caused by large amounts of data queries.

[0120] In Mybatis, the query method of the Executor interface is the core method for executing database queries. This method involves multiple parameters. The following is a detailed explanation of these parameters:

[0121] MappedStatement contains the mapping information of the SQL statement, i.e. the target query statement, such as the SQL command type, such as SELECT, INSERT, UPDATE, DELETE, etc., the SQL statement itself, and the configuration information required to execute SQL;

[0122] Object is the parameter object passed in when executing SQL. This can be a simple object, a Map, or any user-defined type.

[0123] RowBounds is used to support physical paging. It contains two important properties: offset starting row index and limit maximum number of returned rows. In this method, limit can be adjusted dynamically based on page size.

[0124] ResultHandler is a custom way to process database query results, allowing you to process the results immediately after the query returns, rather than waiting for all result sets to be processed. This can optimize memory usage when processing large amounts of data.

[0125] CacheKey, a key component of Mybatis for cache management, determines when cached results can be used instead of executing SQL again. In the memory management strategy, reasonable use of cache can reduce the number of database queries, thereby reducing memory pressure.

[0126] BoundSql is the parsed SQL statement to be executed, including the actual parameters bound. This is where the interceptor directly modifies the SQL statement, such as adding a limit statement.

[0127] Determine whether the global switch in the application server is enabled. If the global switch is enabled, use the custom release annotation as a personalized switch to control whether the SQL query applies the automatically added query restriction condition.

[0128] The SQL query contains a custom release annotation, and the SQL query is not modified;

[0129] The SQL query does not contain a custom release annotation. The BoundSql and corresponding SQL keywords are obtained from the MappedStatement, and the SQL query is dynamically modified based on whether the SQL keyword contains a statistical function or a limit preset condition.

[0130] Further, in step S4, judging whether it is necessary to execute the limit concatenation logic to optimize the maximum amount of returned data of the target query statement in the query database according to the interception logic preset by the limit plug-in interceptor includes:

[0131] (1) Determine whether the global switch in the application server is enabled;

[0132] (2) When the limit plug-in interceptor corresponding to the global switch is turned on, determine whether the plug-in annotation is turned off for the target method link;

[0133] (3) When the target method link closes the plug-in annotation, determine whether the current target query statement contains a statistical method;

[0134] (4) When the current target query statement contains a statistical method, the limit concatenation logic is skipped and the query operation is executed directly;

[0135] (5) When the current target query statement does not contain a statistical method, determine whether the current target query statement has a limit condition. If so, execute the limit concatenation logic. The specific process is as follows:

[0136] Get the MappedStatement object in the current target query statement, and get the BoundSql object and SQL string. The BoundSql object contains the SQL statement and related description objects parsed by Mybatis. The content of the SQL string is the target query statement.

[0137] Determine whether the SQL string contains a limit clause or whether a statistical function is used;

[0138] If the SQL string already contains a limit clause or uses a statistical function, the limit plug-in interceptor does not automatically add a limit clause.

[0139] (6) Read the current query restriction configuration and determine whether the memory usage of the current JVM exceeds the memory usage threshold. If the memory usage of the current JVM does not exceed the memory usage threshold, execute the query operation under the current query restriction.

[0140] Specifically, when the global switch of the application server is turned on, the specific contents of judging whether the target query statement meets the splicing limit condition are as follows:

[0141] ① Check whether the current business method has the NoMybatisAutoLimit annotation applied. This annotation allows specific business scenarios to avoid automatic limit insertion, ensuring that queries that require a complete data set are not affected. If this annotation exists, it means that the method and its call chain should not execute the automatic limit splicing logic.

[0142] ② Get BoundSql and SQL strings from MappedStatement. The original SQL keywords do not contain limit conditions, count methods, sum methods, avg methods, min methods, max methods, or distinct and other statistical methods.

[0143] Specifically, in Mybatis, MappedStatement is a core configuration object that contains the type of SQL command, the SQL statement itself, and comprehensive information about the operation on the SQL statement. BoundSql is extracted from MappedStatement, which specifically contains the SQL statement to be executed and the parameter information bound to it. This makes BoundSql a key part of understanding and manipulating dynamic SQL statements, especially when SQL queries need to be dynamically modified based on runtime conditions. The interceptor uses the intercept method to access MappedStatement and extract BoundSql from it. It then checks the SQL statement in BoundSql to confirm whether it already contains limit conditions or aggregate queries that are not suitable for adding limits. This step ensures that limit conditions can be applied intelligently on appropriate queries, thereby avoiding unnecessary interference with complex statistical queries and protecting the system from memory overflows caused by excessive data loads.

[0144] Furthermore, it also includes: when the plug-in annotation is added to the target method, the target method and all SQL query instructions in the target method link ignore the behavior of automatically adding the limit clause, so that the limit personalization switch of the current business is turned on, and the query is made according to the current target query statement. In specific scenarios, adding the plug-in annotation to the target method allows all SQL query instructions in the method and its call link to ignore the standard operation of automatically adding the limit clause. It enables the business to control specific queries through the limit personalization switch, allowing SQL statements to be executed completely according to business needs without being constrained by automatic restrictions. This approach is mainly used in special cases where the business logic requires access to a complete data set or to perform a comprehensive analysis.

[0145] The purpose of this step is to give greater flexibility and control, especially when the expected amount of data returned and the corresponding processing capacity are known. Since technicians in this field have a direct understanding of the data requirements and system capacity of their applications when using them, they can make reasonable decisions to ensure that the system will not overflow the memory due to excessive data loading. Although this leaves a way for operation, in most cases, through reasonable business planning and strict implementation monitoring, potential performance problems can be effectively avoided to ensure the stable operation of the system and the efficiency of data processing.

[0146] Second embodiment

[0147] Based on the same inventive concept, the present invention also provides a data query system for preventing server memory overflow based on Mybatis, comprising:

[0148] A receiving module, used for responding to a target query request;

[0149] The configuration management module is used to introduce the target query plug-in mechanism into the application server according to the target query request and configure the query restriction conditions of the target query plug-in. The target query plug-in is a limit plug-in based on the Mybatis architecture.

[0150] Query interception module: When the business end initiates a database query and enters the Executor stage, the limit plug-in interceptor is triggered. The Executor stage is the process of executing the query target query statement based on the Mybatis architecture and mapping the query results to Java objects;

[0151] The data processing module determines whether it is necessary to execute the limit splicing logic to optimize the maximum amount of data returned by the target query statement in the query database according to the preset interception logic of the limit plug-in interceptor; in the non-statistical query mode, it enters the limit splicing logic, initiates a query to limit the amount of data to the database through the target query statement containing the limit condition, and returns the query result of the limited amount of data to ensure the normal operation of the application server.

[0152] Third embodiment

[0153] In order to more fully understand the technical implementation and application effects of the present invention, this section Figure 2 , Figure 3 and Figure 4 The operation flow in different query processing scenarios is shown. These flow charts clearly illustrate how the system responds and processes these requests when faced with queries that contain or do not contain statistical methods and how to dynamically adjust query restrictions based on real-time memory status. The following is a detailed introduction and step analysis of each process, aiming to provide a deep understanding of the core functions and advantages of the invention.

[0154] like Figure 3 As shown, Figure 3 The following is a flowchart of a query containing statistical methods. In this process, if the query request contains statistical methods such as COUNT, SUM, AVG, MIN, MAX, and DISTINCT, the system will adopt a specific processing strategy. For example, if the SQL to be executed is select count (*) from user, first determine whether the SQL is of the query type, and enter the custom limit interception in the Executor stage. When the global switch plug-in is turned on and the method link has no plug-in annotation, check whether the query statement contains statistical functions (that is, whether it contains statistical methods. If it does, the query will continue without automatically adding any limit conditions to ensure that the accuracy of statistical calculations is not affected. During this process.

[0155] like Figure 4 As shown, Figure 4 This is a diagram of the query process without statistical methods. In this process,

[0156] For queries that do not include statistical methods, such as the SQL to be executed is select name, age from user, in Figure 3 After the step of judging, if it is judged that the SQL has no statistical method, it will continue to judge whether there is a limit condition. If not, it will configure according to the preset global configuration parameters such as the query limit number and memory usage threshold, and execute the query. At the same time, the system will dynamically calculate and add appropriate limit conditions. This step is to limit the amount of data in the query results, prevent memory overflow due to data overload, and maintain query efficiency.

[0157] like Figure 5 As shown, Figure 5 A diagram of the query process that is optimized for real-time adjustment to limit the amount of data. This flowchart shows how the system adjusts the query limit conditions in real time when the actual memory usage exceeds the set threshold. For example, if the SQL statement to be executed is select name, age from user, during this process, the system will monitor the memory usage of the JVM in real time. Once it is detected that the memory usage exceeds the predetermined threshold, such as 70%, the system will recalculate and optimize the limit value based on the current available memory and the size of each data in the target database. In this way, even when memory is tight, the system can automatically adjust the query load to ensure that it will not crash due to trying to load too much data.

[0158] These flowcharts provide a visual representation of different scenarios of query processing and how the system intelligently responds to each situation to optimize performance and stability.

[0159] In addition, in actual application scenarios, the foreign exchange service system faces the risk of memory overflow due to configuration or logical errors. The following cases describe in detail the specific problems encountered in foreign exchange user services and exchange record queries and their solutions, demonstrating the key role of this method in preventing potential system crashes. Through these examples, it is clearer to see how the system can cause serious performance problems due to improper query operations when there are no appropriate query restrictions, and further verify the importance of introducing query restriction methods for maintaining system stability.

[0160] Application Example 1, regarding the query problem of foreign exchange user service: In this example, the user service of foreign exchange is connected to the method provided by the present invention, and the default limit number is configured to be 10,000. One of the user rpc remote procedure call services: query user information according to the user id, and the bottom layer uses the selectOne method of mybatis. However, the rpc caller did not pass in the user id when calling the interface, resulting in an error message when selectingOne "expected to query one piece of data but queried 10,000". In fact, the bottom layer of this scenario has been optimized for limit queries, and the default limit of 10,000 conditions have been spliced. Otherwise, in this scenario, mybatis will directly query the database to obtain all the data. This causes the server memory to be abnormally occupied, or even memory overflow.

[0161] Application example 2, foreign exchange exchange record query problem: When the customer used the foreign exchange exchange record function, the transaction ID was lost when querying a foreign exchange transaction due to a system bug. Then mybatis used an unconditional SQL statement to query the database to obtain the full data. At that time, the database transaction data was about 10 million, and when all the transaction data was loaded into the exchange service, it directly caused the system memory overflow. Subsequently, a data query system was urgently connected to prevent server memory overflow to ensure the smooth operation of the exchange service.

[0162] Therefore, when using this method, MyBatis may perform a full table query in the absence of specific query conditions, such as user ID. This query will attempt to load all the data in the database into memory, which will cause a large amount of server memory to be occupied when the amount of data is large, and may even cause memory overflow. Therefore, the introduction of a data query system to prevent server memory overflow is to prevent this risk, and to protect the system from excessive memory pressure by limiting the number of data returned.

[0163] Fourth embodiment

[0164] In some embodiments of the present application, an electronic device is also provided. The electronic device includes a memory and a processor, wherein the memory is used to store a processing program; when the processor executes the processing program, the data query method for preventing server memory overflow based on Mybatis as described in the embodiment of the present invention is implemented. When the processor executes the processing program, the data query method for preventing server memory overflow based on Mybatis in the aforementioned embodiment is implemented.

[0165] Fifth embodiment

[0166] The present invention provides a readable storage medium, on which a processing program is stored, and when the processing program is executed by a processor, a data query method for preventing server memory overflow based on Mybatis as described in an embodiment of the present invention is implemented. A computer-readable storage medium may be a tangible device that can hold and store instructions used by an instruction execution device. The computer-readable storage medium may be, for example, - but not limited to - an electrical storage device, a magnetic storage device, an optical storage device, an electromagnetic storage device, a semiconductor storage device, or any suitable combination of the above. More specific examples (a non-exhaustive list) of computer-readable storage media include: a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), a static random access memory (SRAM), a portable compact disk read-only memory (CD-ROM), a digital versatile disk (DVD), a memory stick, a floppy disk, a mechanical encoding device, such as a punch card or a protruding structure in a groove on which instructions are stored, and any suitable combination of the above. The computer-readable storage medium used herein is not to be construed as a transient signal per se, such as a radio wave or other freely propagating electromagnetic wave, an electromagnetic wave propagating through a waveguide or other transmission medium (e.g., light pulses through a fiber optic cable), or an electrical signal transmitted through wires.

[0167] The embodiments of the present disclosure have been described above, and the above description is exemplary, not exhaustive, and is not limited to the disclosed embodiments. Many modifications and changes will be apparent to those of ordinary skill in the art without departing from the scope and spirit of the described embodiments. The selection of terms used herein is intended to best explain the principles of the embodiments, practical applications, or technical improvements in the market, or to enable other persons of ordinary skill in the art to understand the embodiments disclosed herein.

Claims

1. A data query method based on Mybatis to prevent server memory overflow, characterized in that: include: responding to a target query request; According to the target query request, a target query plug-in mechanism is introduced into the application server and a query restriction condition of the target query plug-in is configured, wherein the target query plug-in is a limit plug-in based on the Mybatis architecture; When the business end initiates a database query and enters the Executor stage, the limit plug-in interceptor is triggered. The Executor stage is a process of executing the query target query statement based on the Mybatis architecture and mapping the query results to Java objects; According to the interception logic preset by the limit plug-in interceptor, it is determined whether it is necessary to execute the limit splicing logic to optimize the maximum amount of returned data of the target query statement in the query database; In the non-statistical query mode, the limit splicing logic is entered, and a limited data volume query is initiated to the database through a target query statement containing a limit condition and the query result of the limited data volume is returned to ensure the normal operation of the application server.

2. The data query method for preventing server memory overflow based on Mybatis as claimed in claim 1, characterized in that: It also includes dynamically adjusting the limited data amount returned by the query based on the current memory usage of the JVM to limit the data query data amount for real-time adjustment and optimization; Dynamically adjusting the limited data amount returned by the query based on the current JVM memory usage to limit the data query data amount for real-time adjustment and optimization further includes: Obtain the memory usage of the current JVM in real time; When the memory usage reaches or exceeds the memory usage threshold, calculating the available memory of the current JVM; Obtain the space occupied by a piece of data in the current database table, and calculate the real-time limit data volume by dividing the available memory by the space occupied by the piece of data, thereby obtaining the number of data that can be safely processed by the SQL query under the current available memory state; Determine whether the data volume limit of the original limit configuration is greater than the data volume limit of the real-time limit; If the limited data volume specified by the existing limit condition in the target query statement is greater than the real-time limit data volume calculated based on the available memory optimization, then update the limit condition to the calculated real-time limit data volume; If the limit condition does not exist in the target query statement and there is no statistical query instruction, the limit condition is forcibly spliced ​​in the target query statement, and the limited data volume does not exceed the calculated real-time limit data volume.

3. The data query method for preventing server memory overflow based on Mybatis as claimed in claim 1, characterized in that: The configuration of the limit plug-in interceptor is performed by adding the query restriction conditions to the application server for file configuration. The configuration items include the global switch of the limit plug-in, the number of limit plug-in query restrictions, the memory usage rate of the forced limit optimization, and the number of query restrictions in the relevant configuration item table.

4. The data query method for preventing server memory overflow based on Mybatis according to claim 1, characterized in that: When the business end initiates a database query and enters the Executor phase, the limit plug-in interceptor is triggered, including: Calling the limit plug-in before or after the executor executes the target query statement; Configure the target interception condition, which specifies the target method and parameter type to be intercepted based on the signature matching mechanism of Mybatis to intercept the target method call containing any parameter type, and configure a signature for each combination of the parameter type; Create a java class to implement the Interceptor interface of Mybatis, and rewrite the intercept method to insert custom processing logic, so as to expand or modify the default behavior of Mybatis by implementing custom interception logic; Execute the target method of the Executor interface and intercept all calls to the target method that meet the target interception conditions; The target method is a query method, and the parameter type list of the target method includes one or more combinations of MappedStatement, Object, RowBounds, ResultHandler, CacheKey and BoundSql.

5. The data query method for preventing server memory overflow based on Mybatis according to claim 3 is characterized in that: Judging whether it is necessary to execute the limit splicing logic to optimize the maximum amount of returned data of the target query statement in the query database according to the interception logic preset by the limit plug-in interceptor includes: Determining whether the global switch in the application server is enabled; When the limit plug-in corresponding to the global switch is turned on, determine whether the plug-in annotation is turned off for the target method link; In the case where the plug-in annotation is closed in the target method link, determining whether the current target query statement includes a statistical method; When the current target query statement contains the statistical method, the limit concatenation logic is skipped and the query operation is directly executed; When the current target query statement does not contain the statistical method, determining whether the current target query statement has the limit condition, and if so, executing the limit splicing logic; The current query restriction configuration is read to determine whether the memory usage of the current JVM exceeds the memory usage threshold, and if the memory usage of the current JVM does not exceed the memory usage threshold, the query operation is performed under the current query restriction.

6. The data query method for preventing server memory overflow based on Mybatis according to claim 5, characterized in that: When the current target query statement does not include the statistical method, determining whether the current target query statement has the limit condition, and if so, executing the limit concatenation logic further includes: Get the MappedStatement object in the current target query statement, and obtain the BoundSql object and the SQL string, wherein the BoundSql object contains the SQL statement and related description objects parsed by the Mybatis, and the content of the SQL string is the target query statement; Determine whether the SQL string contains a limit clause or whether a statistical function is used; If the SQL string already contains the limit clause or uses the statistical function, the limit plug-in does not automatically add the limit clause.

7. The data query method for preventing server memory overflow based on Mybatis according to claim 4 is characterized in that: Also includes: When the plug-in annotation is added to the target method, the target method and all SQL query instructions in the target method link ignore the behavior of automatically adding the limit clause, so that the limit personalization switch of the current business is turned on and the query is performed according to the current target query statement.

8. A data query system based on Mybatis to prevent server memory overflow, characterized in that: include: A receiving module, used for responding to a target query request; A configuration management module, used for introducing a target query plug-in mechanism into the application server according to the target query request and configuring the query restriction condition of the target query plug-in, wherein the target query plug-in is a limit plug-in based on the Mybatis architecture; The query interception module triggers the limit plug-in interceptor when the business end initiates a database query and enters the Executor stage. The Executor stage is a process of executing the query target query statement based on the Mybatis architecture and mapping the query results to Java objects; The data processing module determines whether it is necessary to execute the limit splicing logic to optimize the maximum amount of data returned by the target query statement in the query database according to the interception logic preset by the limit plug-in interceptor; in the non-statistical query mode, enters the limit splicing logic, initiates a limited data amount query to the database through a target query statement containing a limit condition, and returns the query result of the limited data amount to ensure the normal operation of the application server.

9. An electronic device, characterized in that: include: A memory, the memory being used to store a processing program; A processor, wherein when executing the processing program, the processor implements the data query method for preventing server memory overflow based on Mybatis as described in any one of claims 1 to 7.

10. A readable storage medium, characterized in that: The readable storage medium stores a processing program, and when the processing program is executed by the processor, the data query method for preventing server memory overflow based on Mybatis as described in any one of claims 1 to 7 is implemented.

Images