Database script verification and table index optimization method based on Mybatis

Abstract

The invention belongs to the technical field of databases, and relates to a Mybatis-based database script verification and table index optimization method and device, computer equipment and a storage medium, and the method comprises the steps that a Mybat i s-based database is configured, the configuration comprises a table, a table structure, a table index, a project code path, and code obtaining through the code path; obtaining an xml file in the code, and extracting an sql statement in the xml file; obtaining a regular expression according to the sql statement, and filtering out a complete se lect statement from the regular expression; judging whether the se lect statement can be executed or not, if not, determining that the xml file has a grammar error, if so, obtaining a query condition, and taking the query condition as a necessary condition for table index optimization; reading a table, a table structure and table index information according to database configuration; in the query condition, the white list field is ignored according to the existing table index, and the table index is optimized. And the project development efficiency can be improved.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a Mybatis-based database script verification and table index optimization method, device, computer equipment and storage medium. Background Art

[0002] In order to reduce quality defects and performance issues, we wrote automated detection and analysis software based on the code review and database table structure review in the work, tested the database scripts of the project, found problematic scripts, and optimized the creation of table indexes based on the current table structure design and code call conditions. We avoided syntax errors that could not be covered by the test, and the impact of the sudden increase in the amount of access data on system performance during the project operation.

[0003] Most of the database scripts based on the Spring project are in the MyBatis xml file. Abnormal scripts are generally eliminated in two ways: one is through identification and self-testing by developers; the other is through testing by testers based on test cases. Performance issues of database script execution are generally exposed through stress testing, indicator monitoring, user use, etc. The defects of this method are: although most problems can be found, there are still many blind spots in detection, and a few problems can easily lead to serious production accidents; this method is relatively passive, and generally can only be identified when problems occur. The discovery process relies more on people's level and energy, and cannot discover potential problems in the script. Summary of the invention

[0004] The purpose of the embodiments of the present invention is to propose a database script verification and table index optimization method, device, computer equipment and storage medium based on Mybatis, so as to detect the database script of the project, find potential script problems, and optimize the creation of table indexes according to the design of the current table structure and the code call situation; avoid syntax errors that cannot be covered by the test from the source, and the impact of the sudden increase in the amount of access data during the project operation on the system performance.

[0005] In order to solve the above technical problems, the present invention provides a database script verification and table index optimization method based on Mybatis, which adopts the following technical solution, including:

[0006] Configure the Mybatis-based database, where the database configuration includes tables, table structures, table indexes, and project code paths, and obtain the code through the code path;

[0007] According to the code, obtain the XML file in the code, and extract the SQL statement in the XML file;

[0008] According to the SQL statement, a regular expression is obtained, and a complete select statement is filtered out from the regular expression;

[0009] Determine whether the select statement can be executed, otherwise determine that there is a syntax error in the XML file, if yes, obtain the query condition, and use the query condition as a necessary condition for table index optimization;

[0010] According to the database configuration, read the table, table structure, and table index information;

[0011] From the query conditions, the existing table index is compared, the whitelist fields are ignored, and the table index is optimized according to different databases and tables.

[0012] Preferably, the Mybatis-based database is configured, and the database configuration includes tables, table structures, table indexes, and project code paths. Through the code path, the step of obtaining the code specifically includes:

[0013] For Mybatis-based databases, configure tables, table structures, table indexes, project code path information, and generate project configuration files;

[0014] Read the project configuration file and scan all the codes under the project code path according to the project code path.

[0015] Preferably, the step of obtaining an XML file in the code according to the code and extracting SQL statements in the XML file specifically includes:

[0016] Read the xml file with ".xml" suffix from the code;

[0017] Get file content from an XML file, where the file content includes XML tags and comment characters;

[0018] Remove the XML tags and comment characters to get the SQL statements in the XML file.

[0019] Preferably, the step of obtaining a regular expression according to the SQL statement and filtering out a complete select statement from the regular expression specifically includes:

[0020] Use the Pattern and Matcher regular expression tool classes in the java.util.regex package of Java to identify the data content in the specified format in the XML file;

[0021] Use the replace function of the String class in the java.lang package to delete or replace the data content in the specified format to obtain a complete select statement.

[0022] Preferably, the step of judging whether the select statement can be executed, and if so, determining that there is a syntax error in the XML file, and obtaining a query condition, and using the query condition as a necessary condition for table index optimization specifically includes:

[0023] Use CCJSqlParserUtil tool class in com.github.jsqlparser dependency package to parse SQL;

[0024] Use recursive method to extract table name, field name, query condition and subquery condition in SQL statement.

[0025] Preferably, the step of reading the table, table structure, and table index information according to the database configuration specifically includes:

[0026] Use the DriverManager class in the java.sql package to obtain the connection;

[0027] When the Connection is successfully established, the database metadata DatabaseMetaData is obtained. The metadata includes the basic information of the database, table structure, fields, and index information.

[0028] Preferably, the step of comparing the query conditions with the existing table indexes, ignoring the whitelist fields, and optimizing the table indexes according to different databases and tables specifically includes:

[0029] According to the select query statement in the XML file, get the fields used in the query condition and ignore the whitelist fields;

[0030] Combine dictionary data to optimize table indexes and generate index optimization suggestions;

[0031] Generate index optimization suggestions into files for storage.

[0032] In order to solve the above technical problems, the present invention also provides a database script verification and table index optimization device based on Mybatis, which adopts the following technical solution, including:

[0033] The acquisition module is used to configure the Mybatis-based database. The database configuration includes tables, table structures, table indexes, and project code paths. The code is obtained through the code path.

[0034] An extraction module, used to obtain an XML file in the code according to the code, and extract SQL statements in the XML file;

[0035] A filtering module, used to obtain a regular expression according to the SQL statement, and filter out a complete select statement from the regular expression;

[0036] A judgment module is used to judge whether the select statement can be executed, otherwise it is determined that there is a syntax error in the XML file, and if so, a query condition is obtained, and the query condition is used as a necessary condition for table index optimization;

[0037] A reading module, used to read the table, table structure, and table index information according to the database configuration;

[0038] The optimization module is used to compare the existing table indexes from the query conditions, ignore the whitelist fields, and optimize the table indexes according to different databases and tables.

[0039] In order to solve the above technical problems, the present invention also provides a computer device, which adopts the technical solution described below, including a memory and a processor, wherein the memory stores computer-readable instructions, and when the processor executes the computer-readable instructions, it implements the steps of the above-mentioned Mybatis-based database script verification and table index optimization method.

[0040] In order to solve the above technical problems, the present invention also provides a computer-readable storage medium, which adopts the technical solution described below, and stores computer-readable instructions on the computer-readable storage medium. When the computer-readable instructions are executed by the processor, the steps of the above-mentioned Mybatis-based database script verification and table index optimization method are implemented.

[0041] Compared with the prior art, the present invention has the following beneficial effects: on the one hand, it can identify code errors by verifying the database scripts, and on the other hand, it can analyze the optimization plan of the database index; in project management, it can effectively control the quality and efficiency improvement process, and turn the code verification work that originally required a lot of manpower and had unsatisfactory results into a configurable and automated process, which can greatly reduce labor costs and avoid production accidents caused by hidden problems after the system is put into operation, thereby improving project development efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0042] In order to more clearly illustrate the scheme in the present invention, a brief introduction is given below to the drawings required for use in the description of the embodiments of the present invention. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0043] Figure 1It is a flowchart of an embodiment of the database script verification and table index optimization method based on Mybatis of the present invention;

[0044] Figure 2 It is a schematic diagram of the contents of a software configuration file used in the Mybatis-based database script verification and table index optimization method of the present invention;

[0045] Figure 3 It is a schematic diagram of the contents of a database configuration file used in the Mybatis-based database script verification and table index optimization method of the present invention;

[0046] Figure 4 It is a schematic diagram of the optimization result using the Mybatis-based database script verification and table index optimization method of the present invention;

[0047] Figure 5 It is a structural schematic diagram of an embodiment of a database script verification and table index optimization device based on Mybatis of the present invention;

[0048] Figure 6 It is a schematic structural diagram of an embodiment of a computer device of the present invention. DETAILED DESCRIPTION

[0049] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as those commonly understood by technicians in the technical field of the present invention; the terms used in the specification of the application herein are only for the purpose of describing specific embodiments and are not intended to limit the present invention; the terms "including" and "having" and any variations thereof in the specification and claims of the present invention and the above-mentioned drawings are intended to cover non-exclusive inclusions. The terms "first", "second", etc. in the specification and claims of the present invention or the above-mentioned drawings are used to distinguish different objects, not to describe a specific order.

[0050] Reference to "embodiments" herein means that a particular feature, structure, or characteristic described in conjunction with the embodiments may be included in at least one embodiment of the present invention. The appearance of the phrase in various places in the specification does not necessarily refer to the same embodiment, nor is it an independent or alternative embodiment that is mutually exclusive with other embodiments. It is explicitly and implicitly understood by those skilled in the art that the embodiments described herein may be combined with other embodiments.

[0051] In order to enable those skilled in the art to better understand the solutions of the present invention, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the accompanying drawings.

[0052] It should be noted that the Mybatis-based database script verification and table index optimization method provided in the embodiment of the present invention is generally executed by a server / terminal device, and accordingly, the Mybatis-based database script verification and table index optimization device is generally arranged in a server / terminal device.

[0053] It should be understood that the numbers of the above terminal devices, networks and servers are only illustrative. Any number of terminal devices, networks and servers may be provided according to implementation requirements.

[0054] Embodiment 1

[0055] Continue to refer Figure 1 , shows a flowchart of an embodiment of the Mybatis-based database script verification and table index optimization method of the present invention. The Mybatis-based database script verification and table index optimization method comprises the following steps:

[0056] Step S1, configure the Mybatis-based database, the database configuration includes tables, table structures, table indexes, and project code paths, and obtain the code through the code path.

[0057] The project in this embodiment refers to an information engineering project that needs to be verified and optimized.

[0058] MyBatis is a persistence layer framework that supports customized sql, stored procedures, and advanced mapping. The features of Mybatis are: easy to learn, no third-party dependencies, the simplest installation only requires two jar files and the configuration of several sql mapping files; flexible to use, Mybatis will not impose any impact on the existing design of the application or database, sql is written in xml, which is convenient for unified management and optimization, and all requirements for operating the database can be met through sql statements; decoupling sql from program code: by providing a DAO layer, business logic and data access logic are separated, making the system design clearer, easier to maintain, and easier to unit test. The separation of sql and code improves maintainability; xml tags are provided to support the writing of dynamic sql, etc.

[0059] Mybatis's functional architecture is divided into three layers:

[0060] (1) API layer: APIs are provided for external use. Developers use these local APIs to manipulate the database. Once the interface layer receives a call request, it calls the data processing layer to complete specific data processing.

[0061] (2) Data processing layer: responsible for specific SQL search, SQL parsing, SQL execution and execution result mapping processing. Its main purpose is to complete a database operation according to the call request.

[0062] (3) Basic support layer: responsible for the most basic functional support, including connection management, transaction management, configuration loading and cache processing. These are common things, and they are extracted as the most basic components. Provide the most basic support for the upper data processing layer.

[0063] In this embodiment, the table refers to a database table. The table structure refers to the attributes and types of information stored in the database table. The table index refers to a sorted data structure used to quickly query specified data.

[0064] In this embodiment, the electronic device (e.g., server / terminal device) on which the Mybatis-based database script verification and table index optimization method runs can receive the Mybatis-based database script verification and table index optimization request through a wired connection or a wireless connection. It should be noted that the above-mentioned wireless connection method may include but is not limited to 3G / 4G / 5G connection, WiFi connection, Bluetooth connection, WiMAXX connection, Zigbee connection, UWB (ultrawideband) connection, and other wireless connection methods currently known or to be developed in the future.

[0065] In this embodiment, step S1 configures a Mybatis-based database, wherein the database configuration includes tables, table structures, table indexes, and project code paths. Obtaining the code through the code path may also include the following steps:

[0066] S11. For a Mybatis-based database, configure the table, table structure, table index, and project code path information to generate a project configuration file. For example, the name of the generated project configuration file may be config.properties.

[0067] S12. Read the project configuration file, and scan all codes under the project code path according to the project code path.

[0068] The configuration of the Mybatis-based database also includes configuring the access address, port, database name, user name, password, etc. The database configuration needs to be maintained according to the database configuration information of the scanned project. Taking the MySQL database as an example, the database configuration information can be placed in the {root directory of this tool project} / mysql.properties file. After the configuration of the Mybatis-based database is completed, the database configuration can be generated into configuration files in txt, cfg, xml and other formats. Figure 2Schematic diagram of the contents of the software configuration file used in the Mybatis-based database script verification and table index optimization method of the present invention, such as Figure 2 As shown, in this embodiment, the software configuration file is named config.properties, codeRootPath refers to the project code path, and columnWhitelist refers to the whitelist field. codeRootPath can refer to the root path of the project code. Through columnWhitelist, multiple whitelist fields can be configured, and multiple whitelist fields are separated by English commas.

[0069] For the convenience of maintaining the paths of different projects locally, the software configuration file can be placed in the {root directory of this tool project} / config.properties file. For the same project, there is only one software configuration, including the root path for scanning and the field whitelist formed by the fields that are agreed in the project that do not need to create indexes.

[0070] Database configuration means that there may be multiple databases in a project, and one database may have multiple data sources. The field names of the connection information of each database may be slightly different, so there will be multiple database configuration files. For example, the MySQL database is named mysql.properties, and the Oracle data is named oracle.properties.

[0071] Separate the software configuration from the database configuration, so that the goal is clearer and reading and maintenance are more convenient. The two are independent of each other and are both necessary configuration items for the operation of the tool.

[0072] The whitelist fields here refer to fields that can be ignored when scanning fields and are considered not to need to be indexed. Because in some scenarios, it is more efficient to query without an index than to create an index. For example, the gender field, gender, stores fields including "male, female, unknown". Creating an index for this field will reduce query efficiency. Therefore, gender does not need to be indexed. When scanning, if you find that the whitelist field has also been indexed, you need to recommend deleting it.

[0073] The purpose of step S1 is to scan all code files according to the acquired code path, so as to acquire the code.

[0074] Figure 3 Schematic diagram of the contents of the database configuration file used in the Mybatis-based database script verification and table index optimization method of the present invention. Figure 3As shown, the database configuration file mysql.properties includes host information host[i], database access port port[i] information, database login username username[i], database username and password password[i], database name database[i], etc.

[0075] Step S2, according to the code, obtain the XML file in the code, and extract the SQL statement in the XML file.

[0076] The file content obtained from the XML file will contain many XML tags and comment characters such as "#", "—", "<!---->", etc. These tags and unnecessary characters need to be effectively removed.

[0077] In this embodiment, step S2, according to the code, obtaining the XML file in the code, extracting the SQL statement in the XML file may also include the steps of:

[0078] S21. Read an XML file with a ".xml" suffix from the code.

[0079] Remove non-xml files.

[0080] S22. Obtain file content from the XML file, where the file content includes XML tags and comment characters.

[0081] Remove comment characters from XML files with the ".xml" suffix, such as <! ---->, #, --, / ** / .

[0082] S23. Remove the XML tags and comment characters to obtain the SQL statements in the XML file.

[0083] Remove the mybatis tags in the XML files with the ".xml" suffix, such as <? xml>, <! DOCUMENT>..., <mapper> ， <update> ， <delete> ， <insert> , <if> ， <choose> ， <otherwise>,<![cdata[]]>, <when> ， <foreach>wait.

[0084] The purpose of step S2 is to parse out the SQL statements in the scanned series of XML files.

[0085] Step S3, obtaining a regular expression according to the SQL statement, and filtering out a complete select statement from the regular expression.

[0086] In this embodiment, step S3 may further include the following steps:

[0087] S31. Use the Pattern and Matcher regular expression tool classes in the java.util.regex package of Java to identify the data content in the specified format in the XML file.

[0088] S32. Use the replace function of the String class in the java.lang package to delete or replace the data to obtain a complete select statement. After using the replace function of the String class in the java.lang package to delete or replace the data, character conversion can also be performed. <where>Convert to where, convert escaped strings such as ">;" to a greater than sign ">", convert "<;" to a less than sign "<", etc.

[0089] After converting the characters, you can also assign values ​​to variables to get a pure select statement that can be executed. For example, assign the variable #{field name} to an empty string """ and then get a pure select statement that can be executed. Use regular expressions to delete and convert the xml tags to extract the select query statement. You need to delete comments such as <! ---->, #, --, / ** / ; delete the mybatis tags <? xml>, <! DOCUMENT>, <mapper> ， <update> ， <delete> ， <insert> , <if> ， <choose> ， <otherwise>,<![cdata[]]>, <when> ， <foreach>;Will <where>Convert it into where, convert escape strings such as ">;" into a greater than sign ">", convert "<;" into a less than sign "<"; assign the variable #{field name} to an empty string ""... finally get a pure database sql query script.

[0090] Step S4, judging whether the select statement can be executed, otherwise it is determined that there is a syntax error in the XML file, and if so, obtaining the query condition, and using the query condition as a necessary condition for table index optimization.

[0091] In this embodiment, the query condition refers to the condition part in the database query script. According to the above example: select * from user where id = #{id}, the content after the where keyword "id = #{id}" is the query condition.

[0092] In this embodiment, step S4 may further include the steps of:

[0093] S41. Use the CCJSqlParserUtil tool class in the com.github.jsqlparser dependency package to parse SQL.

[0094] S42. Use a recursive method to extract the table name, field name, query condition, and subquery condition in the SQL statement.

[0095] Recursion refers to the function calling itself until all the layers of the SQL statement structure are parsed, which means the recursive process ends. If the SQL script can be parsed successfully, it is considered that there is no syntax error; otherwise, it will be recorded in the generated error-{timestamp}.log file to save the error SQL and the cause of the error.

[0096] Step S5, reading the table, table structure, and table index information according to the database configuration.

[0097] In this embodiment, step S5 may further include the steps of:

[0098] S51. Use the DriverManager class in the java.sql package to obtain the connection.

[0099] S52. When the Connection is successfully established, the database metadata DatabaseMetaData is obtained. The metadata includes the basic information, table structure, field, and index information of the database. The basic information of the database includes the database access address host[i], the database access port port[i], the database login user name username[i], the database user name and password password[i], and the database name database[i]. The database configuration is read, including the database access address host[i], the database access port port[i], the database login user name username[i], the database user name and password password[i], and the database name database[i]. [i] represents that multiple databases can be configured, and the same i represents the same database. Multiple databases may use different database types due to different construction years. For example, the common MySQL, Oracle, SQLServer, PostgreSQL, etc. What is stored is the business data of the information system, and the data types are similar. For example, host[0], port[0] are the address and port of the first database, host[1], port[1] are the address and port of the second database, and so on.

[0100] According to the configuration of the database, you can use the classes of the java.sql package to obtain all tables, fields, and indexes, and create dictionary data of <table name, field index> for reading data in the analysis phase.

[0101] In this embodiment, the dictionary (Map) can store the index field information corresponding to the table and obtain it from the table when needed, that is, read the data. Dictionary data refers to a storage structure, which can be a one-to-one data consisting of key and value.<key,value> . You can use the key, such as "user table", to locate its value for easy query.

[0102]

[0103] Step S6, from the query conditions, compare the existing table indexes, ignore the whitelist fields, and optimize the table indexes according to different databases and tables.

[0104] According to the above configuration of the database, the database metadata can be read, including table structure, fields, table indexes, etc. The table index read is the existing table index.

[0105] In this embodiment, step S6 may further include the following steps:

[0106] S61. According to the select query statement in the XML file, the fields used in the query condition are obtained, and the whitelist fields are ignored.

[0107] S62. Optimize the table index based on the dictionary data and generate index optimization suggestions.

[0108] Index optimization suggestions include: if the field is used as a query condition in the code and meets the index creation conditions but the index does not exist in the database, it is recommended to add it; if the index is not used in the code but exists in the database, it is recommended to delete it.

[0109] S63: Generate a file of the index optimization suggestion and store it.

[0110] For example, the index optimization suggestions can be generated into an index-suggest-{timestamp}.txt file for storage.

[0111] For example, the contents recorded in the index-suggest-{timestamp}.txt file may include:

[0112] Table sale_order

[0113] It is recommended to add field indexes: order_no, created_date.

[0114] It is recommended to remove the field indexes: updated_date, is_enable.

[0115] Figure 4 This is a schematic diagram of the optimization results using the Mybatis-based database script verification and table index optimization method of the present invention. Figure 4 As shown, for a large system with 2204 XML files and 4916 tables, the scanning and analysis time can be completed in about 3 minutes, which can replace 15 days of code review workload. It can be seen that by adopting this embodiment, the XML files in the code can be quickly analyzed, potential incorrect syntax can be identified, and production accidents caused by hidden conditions being triggered can be avoided; the use of indexes can also be analyzed, optimization suggestions can be provided, and optimization suggestion output files can be formed, which can further improve the performance of the system.

[0116] The implementation of this embodiment has the following beneficial effects: on the one hand, by extracting SQL from the XML file, if the syntax of the XML is incorrect, the extracted SQL cannot be loaded by jsqlparser to obtain the table name, field, query condition and other information; the XML script verification occurs after the SQL is extracted from the XML, and the query field is further obtained; if the loading fails, the verification fails, that is, by verifying the database script to identify code errors, on the other hand, the optimization plan for the database index can be analyzed; in project management, the quality and efficiency improvement process can be effectively controlled, and the code verification work that originally required more manpower and had unsatisfactory results can be turned into a configurable and automated process, which can greatly reduce labor costs, avoid production accidents caused by hidden problems after the system is put into operation, and improve project development efficiency.

[0117] The present invention can be used in many general or special computer system environments or configurations. For example: personal computers, server computers, handheld or portable devices, tablet devices, multiprocessor systems, microprocessor-based systems, set-top boxes, programmable consumer electronic devices, network PCs, minicomputers, mainframe computers, distributed computing environments including any of the above systems or devices, etc. The present invention can be described in the general context of computer-executable instructions executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, etc. that perform specific tasks or implement specific abstract data types. The present invention can also be practiced in distributed computing environments, in which tasks are performed by remote processing devices connected through a communication network. In a distributed computing environment, program modules can be located in local and remote computer storage media including storage devices.

[0118] Those skilled in the art can understand that all or part of the processes in the above-mentioned embodiments can be implemented by instructing the relevant hardware through computer-readable instructions, and the computer-readable instructions can be stored in a computer-readable storage medium. When the program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, the aforementioned storage medium can be a non-volatile storage medium such as a disk, an optical disk, a read-only memory (ROM), or a random access memory (RAM).

[0119] It should be understood that, although the steps in the flowchart of the accompanying drawings are displayed in sequence as indicated by the arrows, these steps are not necessarily executed in sequence in the order indicated by the arrows. Unless otherwise specified herein, there is no strict order restriction on the execution of these steps, and they can be executed in other orders. Moreover, at least a part of the steps in the flowchart of the accompanying drawings may include multiple sub-steps or multiple stages, and these sub-steps or stages are not necessarily executed at the same time, but can be executed at different times, and their execution order is not necessarily sequential, but can be executed in turn or alternately with other steps or at least a part of the sub-steps or stages of other steps.

[0120] Embodiment 2

[0121] Further references Figure 4 , as a response to the above Figure 2 The present invention provides an embodiment of a database script verification and table index optimization device based on Mybatis, and the device embodiment is similar to Figure 2 Corresponding to the method embodiment shown, the device can be specifically applied to various electronic devices.

[0122] like Figure 4 As shown, the Mybatis-based database script verification and table index optimization device 70 of this embodiment includes: an acquisition module 71, an extraction module 72, a filtering module 73, a judgment module 74, a reading module 75 and an optimization module 76. Among them:

[0123] The acquisition module 71 is used to configure the database based on Mybatis. The database configuration includes tables, table structures, table indexes, and project code paths. Codes are acquired through the code paths.

[0124] The extraction module 72 is used to obtain the XML file in the code according to the code, and extract the SQL statement in the XML file;

[0125] The filtering module 73 is used to obtain a regular expression according to the SQL statement and filter out a complete select statement from the regular expression;

[0126] The judgment module 74 is used to judge whether the select statement can be executed. If not, it is determined that there is a syntax error in the XML file. If yes, the query condition is obtained and the query condition is used as a necessary condition for table index optimization.

[0127] The reading module 75 is used to read the table, table structure, and table index information according to the database configuration;

[0128] The optimization module 76 is used to compare the existing table indexes from the query conditions, ignore the whitelist fields, and optimize the table indexes according to different databases and tables.

[0129] In some optional implementations of this embodiment, the acquisition module 71 further includes: a configuration module 711, which is used to configure tables, table structures, table indexes, and project code path information for a Mybatis-based database to generate a project configuration file; a scanning module 712, which is used to read the project configuration file and scan all the codes under the project code path according to the project code path.

[0130] The extraction module 72 further includes: an XML file filtering module 721, which is used to read an XML file containing a ".xml" suffix from the code; a content acquisition module 722, which is used to obtain file content from the XML file, wherein the file content includes XML tags and comment characters; a deletion module 723, which is used to remove XML tags and comment characters to obtain SQL statements in the XML file.

[0131] The filtering module 73 further includes: a regular tool module 731, which is used to use the regular tool classes of Pattern and Matcher in the java.util.regex package of Java to identify the data content in a specified format in the xml file; a replacement module 732, which is used to use the replace function of the String class in the java.lang package to delete or replace the data content in the specified format to obtain a complete select statement.

[0132] The judgment module 74 further includes: a tool parsing module 741, which is used to parse SQL using the CCJSqlParserUtil tool class in the com.github.jsqlparser dependency package; a recursive module 742, which is used to extract the table name, field name, query condition, and subquery condition in the SQL statement using a recursive method.

[0133] The reading module 75 further includes: a connection module 751, which is used to use the DriverManager class in the java.sql package to obtain the connection Connection; a metadata acquisition module 752, which is used to obtain the database metadata DatabaseMetaData after the Connection is successfully connected. The metadata includes the basic information, table structure, field, and index information of the database.

[0134] The optimization module 76 further includes: a field module 761, which is used to obtain the fields used in the query conditions according to the select query statement in the XML file, ignoring the whitelist fields; an optimization suggestion module 762, which is used to optimize the table index in combination with the dictionary data and generate index optimization suggestions; a storage module 763, which is used to generate a file with the index optimization suggestions for storage.

[0135] The beneficial effects of implementing this embodiment are: on the one hand, code errors can be identified by verifying the database script, and on the other hand, optimization solutions for database indexes can be analyzed; in project management, the quality and efficiency improvement process can be effectively controlled, and the code verification work that originally required a lot of manpower and had unsatisfactory results can be turned into a configurable and automated process, which can greatly reduce labor costs and avoid production accidents caused by hidden problems after the system is put into operation, thereby improving project development efficiency.

[0136] Embodiment 3

[0137] To solve the above technical problems, the embodiment of the present invention also provides a computer device. Figure 6 , Figure 6 This is a basic structural block diagram of the computer device in this embodiment.

[0138] The above-mentioned computer device 8 includes a memory 81, a processor 82, and a network interface 83 that are interconnected and communicated through a system bus. It should be noted that the figure only shows a computer device 8 having components of a memory 81, a processor 82, and a network interface 83, but it should be understood that it is not required to implement all the components shown, and more or fewer components can be implemented instead. Among them, those skilled in the art can understand that the computer device here is a device that can automatically perform numerical calculations and / or information processing according to pre-set or stored instructions, and its hardware includes but is not limited to a microprocessor, an application specific integrated circuit (ASIC), a programmable gate array (FPGA), a digital processor (DSP), an embedded device, etc.

[0139] The computer device may be a desktop computer, a notebook computer, a PDA, a cloud server, etc. The computer device may interact with the user through a keyboard, a mouse, a remote control, a touch pad, or a voice control device.

[0140] The memory 81 includes at least one type of readable storage medium, including flash memory, hard disk, multimedia card, card-type memory (e.g., SD or DX memory, etc.), random access memory (RAM), static random access memory (SRAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), programmable read-only memory (PROM), magnetic memory, disk, optical disk, etc. In some embodiments, the memory 81 may be an internal storage unit of the computer device 8, such as a hard disk or memory of the computer device 8. In other embodiments, the memory 81 may also be an external storage device of the computer device 8, such as a plug-in hard disk, a smart memory card (Smart Media Card, SMC), a secure digital (Secure Digital, SD) card, a flash card (FlashCard), etc. equipped on the computer device 8. Of course, the memory 81 may also include both the internal storage unit of the computer device 8 and its external storage device. In this embodiment, the memory 81 is generally used to store the operating system and various application software installed on the computer device 8, such as computer-readable instructions of the database script verification and table index optimization method based on Mybatis. In addition, the memory 81 can also be used to temporarily store various types of data that have been output or are to be output.

[0141] The processor 82 may be a central processing unit (CPU), a controller, a microcontroller, a microprocessor, or other data processing chips in some embodiments. The processor 82 is generally used to control the overall operation of the computer device 8. In this embodiment, the processor 82 is used to run computer-readable instructions stored in the memory 81 or process data, such as running computer-readable instructions of the Mybatis-based database script verification and table index optimization method.

[0142] The network interface 83 may include a wireless network interface or a wired network interface. The network interface 83 is generally used to establish a communication connection between the computer device 8 and other electronic devices.

[0143] The beneficial effects of implementing this embodiment are: on the one hand, code errors can be identified by verifying the database script, and on the other hand, optimization solutions for database indexes can be analyzed; in project management, the quality and efficiency improvement process can be effectively controlled, and the code verification work that originally required a lot of manpower and had unsatisfactory results can be turned into a configurable and automated process, which can greatly reduce labor costs and avoid production accidents caused by hidden problems after the system is put into operation, thereby improving project development efficiency.

[0144] Embodiment 4

[0145] The present invention also provides another embodiment, namely, providing a computer-readable storage medium, which stores computer-readable instructions. The computer-readable instructions can be executed by at least one processor to enable the at least one processor to perform the steps of the Mybatis-based database script verification and table index optimization method as described above.

[0146] The beneficial effects of implementing this embodiment are: on the one hand, code errors can be identified by verifying the database script, and on the other hand, optimization solutions for database indexes can be analyzed; in project management, the quality and efficiency improvement process can be effectively controlled, and the code verification work that originally required a lot of manpower and had unsatisfactory results can be turned into a configurable and automated process, which can greatly reduce labor costs and avoid production accidents caused by hidden problems after the system is put into operation, thereby improving project development efficiency.

[0147] Through the description of the above implementation methods, those skilled in the art can clearly understand that the above-mentioned embodiment methods can be implemented by means of software plus a necessary general hardware platform, and of course can also be implemented by hardware, but in many cases the former is a better implementation method. Based on such an understanding, the technical solution of the present invention is essentially or the part that contributes to the prior art can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, a magnetic disk, or an optical disk), and includes a number of instructions for a terminal device (which can be a mobile phone, a computer, a server, an air conditioner, or a network device, etc.) to execute the various embodiment methods of the present invention.

[0148] Obviously, the embodiments described above are only some embodiments of the present invention, rather than all embodiments. The preferred embodiments of the present invention are given in the accompanying drawings, but they do not limit the patent scope of the present invention. The present invention can be implemented in many different forms. On the contrary, the purpose of providing these embodiments is to make the understanding of the disclosure of the present invention more thorough and comprehensive. Although the present invention has been described in detail with reference to the aforementioned embodiments, for those skilled in the art, it is still possible to modify the technical solutions recorded in the aforementioned specific embodiments, or to perform equivalent replacements for some of the technical features therein. Any equivalent structure made using the contents of the specification and drawings of the present invention, directly or indirectly used in other related technical fields, is also within the scope of patent protection of the present invention.< / where> < / foreach> < / when> < / otherwise> < / choose> < / if> < / insert> < / delete> < / update> < / mapper> < / where> < / foreach> < / when> < / otherwise> < / choose> < / if> < / insert> < / delete> < / update> < / mapper>

Claims

1. A database script verification and table index optimization method based on Mybatis, characterized in that: The steps include: Configure the Mybatis-based database, where the database configuration includes tables, table structures, table indexes, and project code paths, and obtain the code through the code path; According to the code, obtain the XML file in the code, and extract the SQL statement in the XML file; According to the SQL statement, a regular expression is obtained, and a complete select statement is filtered out from the regular expression; Determine whether the select statement can be executed, otherwise determine that there is a syntax error in the XML file, if yes, obtain the query condition, and use the query condition as a necessary condition for table index optimization; According to the database configuration, read the table, table structure, and table index information; From the query conditions, the existing table index is compared, the whitelist fields are ignored, and the table index is optimized according to different databases and tables.

2. The Mybatis-based database script verification and table index optimization method according to claim 1, characterized in that: The Mybatis-based database is configured, and the database configuration includes tables, table structures, table indexes, and project code paths. Through the code path, the steps of obtaining the code specifically include: For Mybatis-based databases, configure tables, table structures, table indexes, project code path information, and generate project configuration files; Read the project configuration file and scan all the codes under the project code path according to the project code path.

3. The Mybatis-based database script verification and table index optimization method according to claim 1, characterized in that: The step of obtaining an XML file in the code according to the code and extracting SQL statements in the XML file specifically includes: Read the xml file with ".xml" suffix from the code; Get file content from an XML file, where the file content includes XML tags and comment characters; Remove the XML tags and comment characters to get the SQL statements in the XML file.

4. The Mybatis-based database script verification and table index optimization method according to claim 1, characterized in that: The step of obtaining a regular expression according to the SQL statement and filtering out a complete select statement from the regular expression specifically includes: Use the Pattern and Matcher regular expression tool classes in the java.uti l.regex package of Java to identify the data content in the specified format in the XML file; Use the replace function of the String class in the java.lang package to delete or replace the data content in the specified format to obtain a complete select statement.

5. The Mybatis-based database script verification and table index optimization method according to claim 1, characterized in that: The step of judging whether the select statement can be executed, otherwise determining that there is a syntax error in the XML file, and obtaining a query condition, and using the query condition as a necessary condition for table index optimization specifically includes: Use CCJSqlParserUtil tool class in com.github.jsqlparser dependency package to parse SQL; Use recursive method to extract table name, field name, query condition and subquery condition in SQL statement.

6. The Mybatis-based database script verification and table index optimization method according to claim 1, characterized in that: The step of reading the table, table structure, and table index information according to the database configuration specifically includes: Use the DriverManager class in the java.sql package to obtain the connection; When the Connection is successfully established, the database metadata DatabaseMetaData is obtained. The metadata includes the basic information of the database, table structure, fields, and index information.

7. The Mybatis-based database script verification and table index optimization method according to any one of claims 1 to 6, characterized in that: The step of optimizing the table index from the query condition, comparing the existing table index, ignoring the whitelist field, and according to different databases and tables specifically includes: According to the select query statement in the XML file, get the fields used in the query condition and ignore the whitelist fields; Combine dictionary data to optimize table indexes and generate index optimization suggestions; Generate index optimization suggestions into files for storage.

8. A database script verification and table index optimization device based on Mybatis, characterized in that: include: The acquisition module is used to configure the Mybatis-based database. The database configuration includes tables, table structures, table indexes, and project code paths. The code is obtained through the code path. An extraction module, used to obtain an XML file in the code according to the code, and extract SQL statements in the XML file; A filtering module, used to obtain a regular expression according to the SQL statement, and filter out a complete select statement from the regular expression; A judgment module is used to judge whether the select statement can be executed, otherwise it is determined that there is a syntax error in the XML file, and if so, a query condition is obtained, and the query condition is used as a necessary condition for table index optimization; A reading module, used to read the table, table structure, and table index information according to the database configuration; The optimization module is used to compare the existing table indexes from the query conditions, ignore the whitelist fields, and optimize the table indexes according to different databases and tables.

9. A computer device comprising a memory and a processor, wherein the memory stores computer-readable instructions, and when the processor executes the computer-readable instructions, the steps of the Mybatis-based database script verification and table index optimization method are implemented as described in any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-readable instructions, and when the computer-readable instructions are executed by the processor, the steps of the Mybatis-based database script verification and table index optimization method are implemented as described in any one of claims 1 to 7.

Images