CN110851346B - Query statement boundary problem detection method, device, equipment and storage medium - Google Patents
Query statement boundary problem detection method, device, equipment and storage medium Download PDFInfo
- Publication number
- CN110851346B CN110851346B CN201910902759.1A CN201910902759A CN110851346B CN 110851346 B CN110851346 B CN 110851346B CN 201910902759 A CN201910902759 A CN 201910902759A CN 110851346 B CN110851346 B CN 110851346B
- Authority
- CN
- China
- Prior art keywords
- sql
- boundary
- statement
- file
- boundary problem
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
- 238000001514 detection method Methods 0.000 title claims abstract description 48
- 238000000034 method Methods 0.000 claims abstract description 54
- 238000012216 screening Methods 0.000 claims abstract description 21
- 230000002159 abnormal effect Effects 0.000 claims description 20
- 230000014509 gene expression Effects 0.000 claims description 12
- 238000004590 computer program Methods 0.000 claims description 7
- 238000000605 extraction Methods 0.000 claims description 4
- 238000011161 development Methods 0.000 abstract description 7
- 238000004891 communication Methods 0.000 description 4
- 238000010586 diagram Methods 0.000 description 4
- 230000008569 process Effects 0.000 description 4
- 230000003287 optical effect Effects 0.000 description 3
- 238000012545 processing Methods 0.000 description 3
- 238000004364 calculation method Methods 0.000 description 2
- 230000006835 compression Effects 0.000 description 2
- 238000007906 compression Methods 0.000 description 2
- 230000006870 function Effects 0.000 description 2
- 230000003993 interaction Effects 0.000 description 2
- 238000012986 modification Methods 0.000 description 2
- 230000004048 modification Effects 0.000 description 2
- 230000003068 static effect Effects 0.000 description 2
- 230000009286 beneficial effect Effects 0.000 description 1
- 230000005540 biological transmission Effects 0.000 description 1
- 239000000284 extract Substances 0.000 description 1
- 239000000835 fiber Substances 0.000 description 1
- 238000001914 filtration Methods 0.000 description 1
- 230000010365 information processing Effects 0.000 description 1
- 230000002085 persistent effect Effects 0.000 description 1
- 230000004044 response Effects 0.000 description 1
- 238000012552 review Methods 0.000 description 1
- 238000006467 substitution reaction Methods 0.000 description 1
- 230000000007 visual effect Effects 0.000 description 1
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F11/00—Error detection; Error correction; Monitoring
- G06F11/36—Preventing errors by testing or debugging software
- G06F11/362—Software debugging
- G06F11/366—Software debugging using diagnostics
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computer Hardware Design (AREA)
- Quality & Reliability (AREA)
- Mathematical Physics (AREA)
- Computational Linguistics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The embodiment of the application discloses a method, a device, equipment and a storage medium for detecting query statement boundary problems, and relates to the technical field of database statement detection. The method comprises the following steps: receiving a file storage path sql boundary detection instruction containing a specification; screening all sql files in the file storage path; extracting all sql sentences in the sql file, and extracting sql conditional sentences containing in operators from the sql sentences; detecting the number of parameters corresponding to each in operator in the sql conditional statement, and judging whether a boundary problem exists, wherein the number of parameters is larger than a boundary threshold value; and generating push content for prompting the boundary problem and sending the push content to the target user. According to the method, the contents of the sql file are subjected to targeted analysis through reasonable steps, and whether the sql statement in the sql file has boundary problems caused by in operators is judged, so that developers can conveniently know the boundary problems in the sql file and locate the problems, and the development quality and the treatment timeliness of the boundary problems are improved.
Description
Technical Field
The embodiment of the application relates to the technical field of database statement detection, in particular to a method, a device, equipment and a storage medium for detecting query statement boundary problems.
Background
In the development process of SQL (Structured Query Language ) of a database, a developer sometimes needs to query values of two states, if the normal query is used, the values of the two queries need to be queried twice, then the values of the two queries are put into a set and then are deduplicated, so that the query mode is very troublesome, and the in-condition query in the SQL is convenient.
However, when the in-condition query is used, if static data is input to the object of the query, the maximum receiving amount is limited by a boundary value represented by a certain value, and once the boundary value is exceeded, a boundary problem occurs. And when in conditions are used, boundary problems which are easy to occur when in use are easily ignored, so that program functions are abnormal and normal use is not possible. Therefore, when the written code file is reviewed, a developer hopes to automatically detect the boundary problem of the in condition and judge whether the boundary problem exists in the sql statement using the in condition.
Disclosure of Invention
The technical problem to be solved by the embodiment of the application is to provide a method, a device, equipment and a storage medium for detecting the boundary problem of an in condition in an sql statement and judging whether the boundary problem exists.
In order to solve the above technical problems, the method for detecting the query statement boundary problem according to the embodiments of the present application adopts the following technical scheme:
A detection method of query statement boundary problems includes:
Receiving an sql boundary detection instruction, wherein the sql boundary detection instruction comprises a designated file storage path;
responding to the sql boundary detection instruction, and screening out all sql files in the file storage path;
Extracting all sql statements in the sql file, and further extracting sql conditional statements containing in operators from the sql statements;
detecting the number of parameters corresponding to each in operator in the sql conditional statement, judging whether a boundary problem exists, wherein the number of parameters is larger than a boundary threshold value, and generating a corresponding judgment result;
and generating push content for prompting the boundary problem based on the judging result, and sending the push content to the target user.
According to the method for detecting the boundary problem of the query statement, disclosed by the embodiment of the application, the content of the sql file is subjected to targeted analysis through reasonable steps, and whether the sql statement in the sql file has the boundary problem caused by the in operator is judged, so that a developer can conveniently know the boundary problem in the sql file and locate the problem, and the development quality and the treatment timeliness of the boundary problem are improved.
Further, in the method for detecting a query statement boundary problem, the step of screening all sql files in the file storage path includes:
Detecting all files in a first-level directory under the file storage path, and screening out sql files in the files;
And accessing all folders under the first-level directory step by step until the detection of the files in all folders under the first-level directory is completed, and screening out all sql files in the files.
Further, in the method for detecting a query statement boundary problem, the step of extracting all sql statements in the sql file includes:
invoking a dom4j parse package or mybatis framework;
and executing analysis on the sql file through an API provided by dom4j or mybatis, and extracting all sql sentences in the sql file.
Further, in the method for detecting a query statement boundary problem, the step of extracting the sql conditional statement including the in operator from the sql statement includes:
configuring a regular expression;
And matching the sql conditional statement containing the in operator in the sql statement based on the regular expression, and extracting the matched sql conditional statement.
Further, in the method for detecting a query statement boundary problem, after the step of extracting all sql statements in the sql file and further extracting the sql conditional statement containing the in operator from the sql statements, the method further includes the steps of:
analyzing the sql conditional statement to obtain an id corresponding to each sql conditional statement;
and recursively inquiring in the sql statement according to the id to acquire classes and line numbers matched with the sql conditional statement.
Further, in the method for detecting a boundary problem of a query sentence, the step of generating push content for prompting the boundary problem based on the determination result and sending the push content to a target user includes:
When judging that the boundary problem with the parameter quantity larger than the boundary threshold exists, recording the corresponding sql conditional statement with the boundary problem as an abnormal conditional statement;
and incorporating the abnormal condition statement and the acquired class and line number matched with the abnormal condition statement into the push content.
Further, in the method for detecting a query statement boundary problem, after the step of detecting the number of parameters corresponding to each in operator in the sql conditional statement, the method further includes the steps of:
Counting the total number a of in operators and the number b of in operators, the parameter number of which does not exceed a preset performance boundary threshold value x, and calculating a value a/b of a condition proportion;
the step of generating push content for prompting boundary problems based on the judging result and sending the push content to the target user comprises the following steps:
And generating a prompt message based on the value of the condition proportion, and sending the prompt message to a target user so as to prompt the target user about the query efficiency of the sql under the file storage path.
In order to solve the above technical problems, the embodiment of the present application further provides a device for detecting a query statement boundary problem, which adopts the following technical scheme:
a query statement boundary problem detection apparatus, comprising:
The instruction receiving module is used for receiving an sql boundary detection instruction, wherein the sql boundary detection instruction comprises a designated file storage path;
the instruction execution module is used for responding to the sql boundary detection instruction and screening out all sql files in the file storage path;
The sentence extraction module is used for extracting all sql sentences in the sql file and further extracting sql conditional sentences containing in operators from the sql sentences;
the problem judging module is used for detecting the parameter number corresponding to each in operator in the sql conditional statement, judging whether a boundary problem with the parameter number larger than a boundary threshold exists or not, and generating a corresponding judging result;
And the result sending module is used for generating push content for prompting the boundary problem based on the judging result and sending the push content to the target user.
According to the detection device for the boundary problem of the query statement, disclosed by the embodiment of the application, the content of the sql file is subjected to targeted analysis through reasonable steps, and whether the boundary problem caused by the in operator exists in the sql statement in the sql file is judged, so that a developer can conveniently know the boundary problem in the sql file and locate the problem, and the development quality and the treatment timeliness of the boundary problem are improved.
In order to solve the above technical problems, the embodiment of the present application further provides a computer device, which adopts the following technical schemes:
A computer device comprising a memory and a processor, the memory having stored therein a computer program, the processor implementing the steps of the method for detecting a query statement boundary problem according to any one of the above-mentioned aspects when the computer program is executed.
In order to solve the above technical problems, an embodiment of the present application further provides a computer readable storage medium, which adopts the following technical schemes:
A computer-readable storage medium having stored thereon a computer program which, when executed by a processor, implements the steps of a method for detecting a query statement boundary problem according to any one of the above-described aspects.
Compared with the prior art, the embodiment of the application has the following main beneficial effects:
The embodiment of the application discloses a method, a device, equipment and a storage medium for detecting query statement boundary problems, wherein the method for detecting the query statement boundary problems receives a detection instruction containing a specified file storage path sql boundary; responding to the sql boundary detection instruction, and screening out all sql files in the file storage path; extracting all sql sentences in the sql file, and further extracting sql conditional sentences containing in operators from the sql sentences; detecting the number of parameters corresponding to each in operator in the sql conditional statement, judging whether a boundary problem exists, wherein the number of parameters is larger than a boundary threshold value, and generating a response judgment result; and generating push content for prompting the boundary problem based on the judgment result, and sending the push content to the target user. According to the method, the contents of the sql file are subjected to targeted analysis through reasonable steps, and whether the sql statement in the sql file has boundary problems caused by in operators is judged, so that developers can conveniently know the boundary problems in the sql file and locate the problems, and the development quality and the treatment timeliness of the boundary problems are improved.
Drawings
In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings that are needed in the embodiments will be briefly described below, and it is obvious that the drawings in the following description are only some embodiments of the present application, and other drawings may be obtained according to these drawings without inventive effort for a person skilled in the art.
FIG. 1 is a diagram of an exemplary system architecture in which embodiments of the present application may be applied;
FIG. 2 is a flow chart of one embodiment of a method for detecting query statement boundary problems in accordance with embodiments of the application;
FIG. 3 is a schematic structural diagram of an embodiment of a device for detecting a boundary problem of a query statement according to an embodiment of the application;
fig. 4 is a schematic structural view of an embodiment of a computer device according to an embodiment of the present application.
Detailed Description
Unless defined otherwise, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this application belongs. The terminology used herein in the description of the application is for the purpose of describing particular embodiments only and is not intended to be limiting of the application.
It is noted that the terms "comprising," "including," and "having," and any variations thereof, in the description and claims of the application and in the foregoing figures, are intended to cover non-exclusive inclusions. For example, a process, method, system, article, or apparatus that comprises a list of steps or elements is not limited to only those listed steps or elements but may include other steps or elements not listed or inherent to such process, method, article, or apparatus. In the claims, specification, and drawings of the present application, relational terms such as "first" and "second", and the like are used solely to distinguish one entity/operation/object from another entity/operation/object without necessarily requiring or implying any actual such relationship or order between such entities/operations/objects.
Reference herein to "an embodiment" means that a particular feature, structure, or characteristic described in connection with the embodiment may be included in at least one embodiment of the application. The appearances of such phrases in various places in the specification are not necessarily all referring to the same embodiment, nor are separate or alternative embodiments mutually exclusive of other embodiments. Those of skill in the art will explicitly and implicitly appreciate that the embodiments described herein may be combined with other embodiments.
In order to enable those skilled in the art to better understand the solution of the present application, the following description will clearly and completely describe the solution of the present application in connection with the accompanying drawings in the embodiments of the present application.
As shown in fig. 1, a system architecture 100 may include terminal devices 101, 102, 103, a network 104, and a server 105. The network 104 is used as a medium to provide communication links between the terminal devices 101, 102, 103 and the server 105. The network 104 may include various connection types, such as wired, wireless communication links, or fiber optic cables, among others.
The user may interact with the server 105 via the network 104 using the terminal devices 101, 102, 103 to receive or send messages or the like. Various communication client applications, such as a web browser application, a shopping class application, a search class application, an instant messaging tool, a mailbox client, social platform software, etc., may be installed on the terminal devices 101, 102, 103.
The terminal devices 101, 102, 103 may be various electronic devices having a display screen and supporting web browsing, including but not limited to smartphones, tablet computers, electronic book readers, MP3 players (Moving Picture Experts Group Audio Layer III, dynamic video expert compression standard audio plane 3), MP4 (Moving Picture Experts Group Audio Layer IV, dynamic video expert compression standard audio plane 4) players, laptop and desktop computers, and the like.
The server 105 may be a server providing various services, such as a background server providing support for pages displayed on the terminal devices 101, 102, 103.
It should be noted that, the method for detecting the query statement boundary problem provided by the embodiment of the present application is generally executed by a server/terminal device, and accordingly, the device for detecting the query statement boundary problem is generally disposed in the server/terminal device.
It should be understood that the number of terminal devices, networks and servers in fig. 1 is merely illustrative. There may be any number of terminal devices, networks, and servers, as desired for implementation.
With continued reference to FIG. 2, a flow chart of one embodiment of a method of detecting query statement boundary problems in accordance with embodiments of the application is shown. The method for detecting the query statement boundary problem comprises the following steps:
step 201: and receiving an sql boundary detection instruction, wherein the sql boundary detection instruction comprises a designated file storage path.
In the embodiment of the application, when target users such as developers need to know whether boundary problems related to in conditions exist in sql files in a database, the target users need to configure sql detection instructions according to actual requirements, and then the sql detection instructions are sent to a server to request to detect the boundary problems.
When configuring the sql detection instruction, the target user needs to specify the storage path of the sql file to be detected according to the actual requirement. In the embodiment of the application, the server can also provide a visual interaction interface for a target user to conveniently configure the file storage path.
In the embodiment of the present application, the electronic device (for example, the server/terminal device shown in fig. 1) on which the method for detecting the query statement boundary problem operates may receive the sql boundary detection instruction sent by the user in a wired connection manner or a wireless connection manner. It should be noted that the wireless connection may include, but is not limited to, 3G/4G connection, wiFi connection, bluetooth connection, wiMAX connection, zigbee connection, UWB (ultra wideband) connection, and other now known or later developed wireless connection.
Step 202: and responding to the sql boundary detection instruction, and screening out all sql files in the file storage path.
After receiving the sql detection instruction, the server accesses the file storage path based on the content indicated by the sql detection instruction, and screens out all sql files under the file storage path in a recursive traversal mode. The Sql file refers to a database script file with a suffix of Sql, and the contents in the Sql file are mainly Sql statements for execution in a database, and the Sql file can be opened by a database tool and the Sql statements therein are executed in the database.
In some embodiments of the present application, the step 202 includes:
Detecting all files in a first-level directory under the file storage path, and screening out sql files in the files;
And accessing all folders under the first-level directory step by step until the detection of the files in all folders under the first-level directory is completed, and screening out all sql files in the files.
When the file screening is performed under the file storage path, all files and folders under the file storage path need to be detected. Under the file storage path to be detected, firstly screening out the object (also called as a class) of the sql file under the first-level directory, and then further accessing the files and subfolders under the folder of the first directory until the content in each file and subfolder is detected step by step, thereby screening out all the sql files under the file storage path.
Step 203: extracting all sql statements in the sql file, and further extracting sql conditional statements containing in operators from the sql statements.
Based on the screened sql files, after all sql sentences in all sql files are extracted through further analysis and screening, recognition judgment is carried out in all sql sentences, whether the sql sentences contain in operators is confirmed, and if the sql sentences containing in operators are recognized, the corresponding sql sentences are extracted and marked as sql conditional sentences. Typically, each in operator corresponds to an sql conditional statement.
In a specific implementation manner of the embodiment of the present application, the step of extracting all sql statements in the sql file in step 203 includes:
invoking a dom4j parse package or mybatis framework;
and executing analysis on the sql file through an API provided by dom4j or mybatis, and extracting all sql sentences in the sql file.
Specifically, dom4j is an open source parsing package that can be used to evaluate the performance, functionality, and usability of the mainstream API (Application Programming Interface ). Mybatis is a persistent layer framework, which supports customizing sql, and can provide api for parsing the sql file to obtain related information of the sql file, such as sql statements in the sql file.
In a specific implementation manner of the embodiment of the present application, the step of extracting, in step 203, the sql conditional statement including the in operator from the sql statement includes:
configuring a regular expression;
And matching the sql conditional statement containing the in operator in the sql statement based on the regular expression, and extracting the matched sql conditional statement.
A regular expression is a logical formula for operating on a string, and a "regular string" is formed by using specific characters defined in advance and combinations of the specific characters, and is used for expressing a filtering logic for the string. Regular expressions are typically used to retrieve, replace, text that meets a certain pattern (rule).
In the embodiment of the application, after the regular expression configured for matching the in operator is completed, the part containing the in operator sentence is screened out from all sql sentences through the regular expression and is recorded as the sql conditional sentence.
Step 204: and detecting the parameter number corresponding to each in operator in the sql conditional statement, judging whether a boundary problem exists, wherein the parameter number of the boundary problem is larger than a boundary threshold value, and generating a corresponding judging result.
When querying in a database, the data amount when querying by using an in operator is limited by a certain amount range, the maximum value of the range is regarded as a boundary threshold, and generally, the value of the boundary threshold is 1000, that is, if the data amount when querying exceeds 1000, the query statement will report errors. The following exemplifies the application of the in operator in the sql statement: if the number of fields in brackets in sql sentence delete from student WHERE ID IN (1, 2, 3.) is only 1000 at most, if the number of fields exceeds 1000, the program cannot normally run, and the number of parameters corresponding to the in operator refers to the number of fields in brackets.
And detecting the parameter number of in operators in each sql conditional statement by a detection tool, marking the corresponding sql conditional statement if the detected parameter number is larger than a boundary threshold value, and carrying out statistics and summarization to generate a judgment result after all the sql conditional statements are detected. The judging result comprises the following steps: whether there is a boundary problem, and the number of sql conditional statements that there is a boundary problem.
Step 205: and generating push content for prompting the boundary problem based on the judging result, and sending the push content to the target user.
The pushing content can be displayed in a list form on a page of the server, and meanwhile, related information is sent to a developer in a mail mode and the like so as to remind the developer to review boundary problems and modify codes.
In one embodiment of the present disclosure, when a developer knows that there is a boundary problem and it is desired to avoid the occurrence of the boundary problem, a temporary table for storing data to be called may be pre-established, all the data that the in operator desires to call are inserted into the temporary table, and when the in operator is used for querying, the temporary table may be accessed to read the data to be called from the temporary table. If delete from student WHERE ID IN (1, 2, 3.) the number of data to be invoked in brackets is 2000, the boundary threshold value of 1000 is exceeded, and after the 2000 values are written into a temporary table temp, normal invoke query selection from T WHERE ID IN can be implemented by using the following sql conditional statement (select id from temp).
In some embodiments of the present application, after the step 203, the method for detecting a query statement boundary problem further includes the steps of:
analyzing the sql conditional statement to obtain an id corresponding to each sql conditional statement;
and recursively inquiring in the sql statement according to the id to acquire classes and line numbers matched with the sql conditional statement.
The class is called an object, in the present application, the class refers to an sql file where an sql conditional statement is located, and the line number refers to the line number where the sql conditional statement is located in the sql file.
And analyzing the matched sql conditional sentences to obtain ids (identification and labels) corresponding to each sql conditional sentence, and inquiring in the sql conditional sentences according to a plurality of ids so as to obtain classes and lines corresponding to each sql conditional sentence. Therefore, the target user can conveniently position the sql conditional statement to be checked, and particularly the sql conditional statement with the boundary problem.
In a further embodiment, the step 205 includes:
When judging that the boundary problem with the parameter quantity larger than the boundary threshold exists, recording the corresponding sql conditional statement with the boundary problem as an abnormal conditional statement;
and incorporating the abnormal condition statement and the acquired class and line number matched with the abnormal condition statement into the push content.
The generated pushing content for prompting the boundary problem also comprises the sql conditional statement with the boundary problem and related information thereof, and the sql conditional statement is sent to a developer, so that the developer can conveniently locate and analyze the problem.
In an embodiment of the present application, the information including the push content may further include: code submitters of the abnormal condition sentences, id of the abnormal condition sentences and other information.
In some embodiments of the present application, after the step of detecting the number of parameters corresponding to each in operator in the sql conditional statement in step 204, the method for detecting a boundary problem of the query statement further includes the steps of:
Counting the total number a of in operators and the number b of in operators with the parameter number not exceeding a preset performance boundary threshold value x, and calculating the value a/b of the condition proportion.
The step 205 includes: and generating a prompt message based on the value of the condition proportion, and sending the prompt message to a target user so as to prompt the target user about the query efficiency of the sql under the file storage path.
When the in operator is used for inquiring, after the number of parameter values exceeds a certain value in the range of the boundary threshold, the performance of the inquiry is rapidly reduced, wherein the threshold is marked as a performance boundary threshold x, and the value of x is generally 300 in practical application.
The condition proportion of the in operators in the range x can be obtained through statistics and calculation of the value of a/b, so that the query efficiency of sql under the file storage path can be judged to a certain extent.
Furthermore, in a mode similar to the method for marking abnormal condition sentences and incorporating push contents in the steps, sql condition sentences with the number of parameter values exceeding a performance boundary threshold can be obtained, and after the class and the number of lines of the corresponding sql condition sentences are obtained in the sql sentences through recursive query according to id, the push contents carrying the information are sent to a target user, so that the target user can conveniently locate and analyze.
According to the method for detecting the boundary problem of the query statement, disclosed by the embodiment of the application, the content of the sql file is subjected to targeted analysis through reasonable steps, and whether the sql statement in the sql file has the boundary problem caused by the in operator is judged, so that a developer can conveniently know the boundary problem in the sql file and locate the problem, and the development quality and the treatment timeliness of the boundary problem are improved.
Those skilled in the art will appreciate that implementing all or part of the above-described methods in accordance with the embodiments may be accomplished by way of a computer program stored in a computer-readable storage medium, which when executed, may comprise the steps of the embodiments of the methods described above. The storage medium may be a nonvolatile storage medium such as a magnetic disk, an optical disk, a Read-Only Memory (ROM), or a random access Memory (Random Access Memory, RAM).
It should be understood that, although the steps in the flowcharts of the figures are shown in order as indicated by the arrows, these steps are not necessarily performed in order as indicated by the arrows. The steps are not strictly limited in order and may be performed in other orders, unless explicitly stated herein. Moreover, at least some of the steps in the flowcharts of the figures may include a plurality of sub-steps or stages that are not necessarily performed at the same time, but may be performed at different times, the order of their execution not necessarily being sequential, but may be performed in turn or alternately with other steps or at least a portion of the other steps or stages.
With further reference to fig. 3, fig. 3 is a schematic structural diagram of an embodiment of a detection apparatus for a boundary problem of a query statement according to an embodiment of the present application. As an implementation of the method shown in fig. 2, the present application provides an embodiment of a device for detecting a query statement boundary problem, where an embodiment of the device corresponds to the embodiment of the method shown in fig. 2, and the device may be specifically applied to various electronic devices.
As shown in fig. 3, the device for detecting a query statement boundary problem according to the present embodiment includes:
An instruction receiving module 301; the method is used for receiving the sql boundary detection instruction, and the sql boundary detection instruction comprises a specified file storage path.
An instruction execution module 302; and the method is used for responding to the sql boundary detection instruction and screening out all sql files in the file storage path.
A sentence extraction module 303; and the sql conditional statement comprises an in operator and is used for extracting all sql statements in the sql file and further extracting the sql conditional statement containing the in operator from the sql statements.
A problem determination module 304; and the method is used for detecting the parameter number corresponding to each in operator in the sql conditional statement, judging whether the boundary problem that the parameter number is larger than a boundary threshold exists or not, and generating a corresponding judging result.
A result transmission module 305; and the pushing content used for generating and prompting the boundary problem is sent to the target user based on the judging result.
In some embodiments of the present application, the instruction execution module 302 is configured to detect all files in the first-level directory under the file storage path, and filter out sql files therein; and accessing all folders under the first-level directory step by step until the detection of the files in all folders under the first-level directory is completed, and screening out all sql files in the files.
In a specific implementation manner of the embodiment of the present application, the sentence extracting module 303 includes: and calling a sub-module. The calling submodule is used for calling a dom4j parsing package or a mybatis framework; and executing analysis on the sql file through an API provided by dom4j or mybatis, and extracting all sql sentences in the sql file.
Further, the sentence extraction module 303 further includes: the rule configures the sub-module. The rule configuration submodule is used for configuring a regular expression; and matching the sql conditional statement containing the in operator in the sql statement based on the regular expression, and extracting the matched sql conditional statement.
In some embodiments of the present application, the query statement boundary problem detection apparatus further includes: and a positioning information query module. After the sentence extracting module 303 extracts an sql conditional sentence containing an in operator from the sql sentence, the positioning information query module is configured to parse the sql conditional sentence, and obtain an id corresponding to each sql conditional sentence; and recursively inquiring in the sql statement according to the id to acquire classes and line numbers matched with the sql conditional statement.
In some embodiments of the present application, the result sending module 305 is configured to, when it is determined that there is a boundary problem with the number of parameters being greater than a boundary threshold, record a corresponding sql conditional statement with the boundary problem as an abnormal conditional statement; and incorporating the abnormal condition statement and the acquired class and line number matched with the abnormal condition statement into the push content.
In a further embodiment, the device for detecting a boundary problem of a query statement further includes: and a statistics operation module. The statistical operation module is used for counting the total number a of in operators and the number b of in operators with the parameter number not exceeding a preset performance boundary threshold value x, and calculating the value a/b of the condition proportion. The result sending module 305 is further configured to generate a prompt message based on the value of the condition proportion, and send the prompt message to the target user, so as to prompt the target user about the query efficiency of sql under the file storage path.
According to the detection device for the boundary problem of the query statement, disclosed by the embodiment of the application, the content of the sql file is subjected to targeted analysis through reasonable steps, and whether the boundary problem caused by the in operator exists in the sql statement in the sql file is judged, so that a developer can conveniently know the boundary problem in the sql file and locate the problem, and the development quality and the treatment timeliness of the boundary problem are improved.
In order to solve the technical problems, the embodiment of the application also provides computer equipment. Referring specifically to fig. 4, fig. 4 is a basic structural block diagram of a computer device according to the present embodiment.
The computer device 6 comprises a memory 61, a processor 62, a network interface 63 communicatively connected to each other via a system bus. It is noted that only computer device 6 having components 61-63 is shown in the figures, but it should be understood that not all of the illustrated components are required to be implemented and that more or fewer components may be implemented instead. It will be appreciated by those skilled in the art that the computer device herein is a device capable of automatically performing numerical calculation and/or information processing according to a preset or stored instruction, and its hardware includes, but is not limited to, a microprocessor, an Application SPECIFIC INTEGRATED Circuit (ASIC), a Programmable gate array (Field-Programmable GATE ARRAY, FPGA), a digital Processor (DIGITAL SIGNAL Processor, DSP), an embedded device, and the like.
The computer equipment can be a desktop computer, a notebook computer, a palm computer, a cloud server and other computing equipment. The computer equipment can perform man-machine interaction with a user through a keyboard, a mouse, a remote controller, a touch pad or voice control equipment and the like.
The memory 61 includes at least one type of readable storage media including flash memory, hard disk, multimedia card, card 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, magnetic disk, optical disk, etc. In some embodiments, the storage 61 may be an internal storage unit of the computer device 6, such as a hard disk or a memory of the computer device 6. In other embodiments, the memory 61 may also be an external storage device of the computer device 6, such as a plug-in hard disk, a smart memory card (SMART MEDIA CARD, SMC), a Secure Digital (SD) card, a flash memory card (FLASH CARD) or the like, which are provided on the computer device 6. Of course, the memory 61 may also comprise both an internal memory unit of the computer device 6 and an external memory device. In this embodiment, the memory 61 is generally used to store an operating system and various application software installed on the computer device 6, such as program codes of a method for detecting a boundary problem of a query statement. Further, the memory 61 may be used to temporarily store various types of data that have been output or are to be output.
The processor 62 may be a central processing unit (Central Processing Unit, CPU), controller, microcontroller, microprocessor, or other data processing chip in some embodiments. The processor 62 is typically used to control the overall operation of the computer device 6. In this embodiment, the processor 62 is configured to execute a program code stored in the memory 61 or process data, such as a program code for executing a method for detecting a boundary problem of the query statement.
The network interface 63 may comprise a wireless network interface or a wired network interface, which network interface 63 is typically used for establishing a communication connection between the computer device 6 and other electronic devices.
The present application also provides another embodiment, namely, a computer readable storage medium storing a detection program of a query statement boundary problem, where the detection program of the query statement boundary problem is executable by at least one processor, so that the at least one processor performs the steps of the detection method of the query statement boundary problem as described above.
From the above description of the embodiments, it will be clear to those skilled in the art that the above-described embodiment method may be implemented by means of software plus a necessary general hardware platform, but of course may also be implemented by means of hardware, but in many cases the former is a preferred embodiment. Based on such understanding, the technical solution of the present application may be embodied essentially or in a part contributing to the prior art in the form of a software product stored in a storage medium (e.g. ROM/RAM, magnetic disk, optical disk) comprising instructions for causing a terminal device (which may be a mobile phone, a computer, a server, an air conditioner, or a network device, etc.) to perform the method according to the embodiments of the present application.
In the foregoing embodiments of the present application, it should be understood that the disclosed apparatus and method may be implemented in other manners. For example, the apparatus embodiments described above are merely illustrative, e.g., the division of the modules is merely a logical function division, and there may be additional divisions when actually implemented, e.g., multiple modules or components may be combined or integrated into another system, or some features may be omitted or not performed.
The modules or components may or may not be physically separate, and components shown as modules or components may or may not be physical modules, may or may not be located in one place, or may be distributed over a plurality of network elements. Some or all of the modules or components thereof may be selected according to actual needs to achieve the purpose of the embodiment.
The present application is not limited to the above-described embodiments, but the above-described preferred embodiments of the present application are merely provided for illustrating the present application and not for limiting the scope of the present application, and it should be noted that it is possible for those skilled in the art to make several improvements and modifications to the technical solutions described in the foregoing detailed description or to make equivalent substitutions for some of the technical features thereof without departing from the principle of the present application. All equivalent structures made by the specification and the attached drawings are directly or indirectly applied to other related technical fields, and are equally considered to be included in the protection scope of the application.
It is apparent that the above-described embodiments are only some embodiments of the present application, but not all embodiments, and the preferred embodiments of the present application are shown in the drawings, which do not limit the scope of the patent claims. This application may be embodied in many different forms, but rather, embodiments are provided in order to provide a thorough and complete understanding of the present disclosure. Although the application has been described in detail with reference to the foregoing embodiments, it will be apparent to those skilled in the art that modifications may be made to the embodiments described in the foregoing description, or equivalents may be substituted for elements thereof. All other embodiments obtained by those skilled in the art without creative efforts based on the embodiments of the present application and all equivalent structures made by the descriptions and the attached drawings of the present application are directly or indirectly applied to other relevant technical fields and are equally within the protection scope of the present application.
Claims (7)
1. The method for detecting the query statement boundary problem is characterized by comprising the following steps:
Receiving an sql boundary detection instruction, wherein the sql boundary detection instruction comprises a designated file storage path;
responding to the sql boundary detection instruction, and screening out all sql files in the file storage path;
Extracting all sql statements in the sql file, and further extracting sql conditional statements containing in operators from the sql statements, wherein after the step of extracting all sql statements in the sql file, and further extracting sql conditional statements containing in operators from the sql statements, the method further comprises the steps of:
analyzing the sql conditional statement to obtain an id corresponding to each sql conditional statement;
recursively inquiring in the sql statement according to the id to acquire classes and line numbers matched with the sql conditional statement;
detecting the number of parameters corresponding to each in operator in the sql conditional statement, judging whether a boundary problem exists, wherein the number of parameters is larger than a boundary threshold value, and generating a corresponding judgment result;
and generating push content for prompting the boundary problem based on the judging result and sending the push content to the target user, wherein the step of generating the push content for prompting the boundary problem based on the judging result and sending the push content to the target user comprises the following steps:
When judging that the boundary problem with the parameter quantity larger than the boundary threshold exists, recording the corresponding sql conditional statement with the boundary problem as an abnormal conditional statement;
The abnormal condition statement and the acquired class and line number matched with the abnormal condition statement are incorporated into the push content together;
after performing the step of marking the corresponding sql conditional statement with boundary problems as an abnormal conditional statement, the method further comprises:
Inserting all parameter values corresponding to the in operator in the abnormal condition statement into a preset temporary table one by one and line by line, and respectively setting a distinguishing number for each parameter value;
Generating a full query statement for querying all parameter values according to the table field names corresponding to the distinguishing numbers;
And when an instruction for executing the abnormal condition statement is received, triggering the execution of the full-quantity query statement to query the temporary table, and obtaining all parameter values corresponding to the in operator in the abnormal condition statement.
2. The method for detecting a boundary problem of a query statement according to claim 1, wherein the step of screening out all sql files under the file storage path comprises:
Detecting all files in a first-level directory under the file storage path, and screening out sql files in the files;
And accessing all folders under the first-level directory step by step until the detection of the files in all folders under the first-level directory is completed, and screening out all sql files in the files.
3. The method for detecting a query statement boundary problem according to claim 1, wherein the step of extracting all sql statements in the sql file comprises:
invoking a dom4j parse package or mybatis framework;
and executing analysis on the sql file through an API provided by dom4j or mybatis, and extracting all sql sentences in the sql file.
4. The method for detecting a boundary problem of a query statement according to claim 1, wherein the step of extracting an sql conditional statement containing an in operator from the sql statement comprises:
configuring a regular expression;
And matching the sql conditional statement containing the in operator in the sql statement based on the regular expression, and extracting the matched sql conditional statement.
5. A query term boundary problem detection apparatus, wherein the query term boundary problem detection apparatus is configured to implement the steps of the query term boundary problem detection method according to any one of claims 1 to 4, and the query term boundary problem detection apparatus includes:
The instruction receiving module is used for receiving an sql boundary detection instruction, wherein the sql boundary detection instruction comprises a designated file storage path;
the instruction execution module is used for responding to the sql boundary detection instruction and screening out all sql files in the file storage path;
The sentence extraction module is used for extracting all sql sentences in the sql file and further extracting sql conditional sentences containing in operators from the sql sentences;
the problem judging module is used for detecting the parameter number corresponding to each in operator in the sql conditional statement, judging whether a boundary problem with the parameter number larger than a boundary threshold exists or not, and generating a corresponding judging result;
And the result sending module is used for generating push content for prompting the boundary problem based on the judging result and sending the push content to the target user.
6. A computer device comprising a memory and a processor, the memory having stored therein a computer program, the processor, when executing the computer program, implementing the steps of the query statement boundary problem detection method as claimed in any one of claims 1 to 4.
7. A computer readable storage medium, wherein a computer program is stored on the computer readable storage medium, which when executed by a processor, implements the steps of the method for detecting a query statement boundary problem as claimed in any one of claims 1 to 4.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910902759.1A CN110851346B (en) | 2019-09-24 | 2019-09-24 | Query statement boundary problem detection method, device, equipment and storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201910902759.1A CN110851346B (en) | 2019-09-24 | 2019-09-24 | Query statement boundary problem detection method, device, equipment and storage medium |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN110851346A CN110851346A (en) | 2020-02-28 |
| CN110851346B true CN110851346B (en) | 2024-05-31 |
Family
ID=69596014
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201910902759.1A Active CN110851346B (en) | 2019-09-24 | 2019-09-24 | Query statement boundary problem detection method, device, equipment and storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN110851346B (en) |
Families Citing this family (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN115516442A (en) * | 2020-04-27 | 2022-12-23 | 樊文飞 | Querying big data under limited resources |
Citations (8)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104765731A (en) * | 2014-01-02 | 2015-07-08 | 国际商业机器公司 | Database query optimization method and equipment |
| CN106919612A (en) * | 2015-12-25 | 2017-07-04 | ***通信集团浙江有限公司 | A kind of processing method and processing device of SQL script of reaching the standard grade |
| CN107748658A (en) * | 2017-09-30 | 2018-03-02 | 银联商务股份有限公司 | A kind of software development methodology, system, computer-readable recording medium and electronic equipment |
| CN107832055A (en) * | 2017-12-11 | 2018-03-23 | 安徽科大国创云网科技有限公司 | A kind of MQL language translations are the method for sql like language |
| CN108121542A (en) * | 2017-11-17 | 2018-06-05 | 广东睿江云计算股份有限公司 | A kind of collocation method of the SQL statement based on MyBatis, system |
| CN108984612A (en) * | 2018-06-12 | 2018-12-11 | 中国平安人寿保险股份有限公司 | Acquisition methods, device, computer equipment and the storage medium of target SQL statement |
| CN109271315A (en) * | 2018-08-23 | 2019-01-25 | 中国平安财产保险股份有限公司 | Scripted code detection method, device, computer equipment and storage medium |
| CN109325042A (en) * | 2018-08-14 | 2019-02-12 | 中国平安人寿保险股份有限公司 | Handle template acquisition methods, form processing method, device, equipment and medium |
Family Cites Families (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US8452754B2 (en) * | 2009-05-08 | 2013-05-28 | Microsoft Corporation | Static analysis framework for database applications |
-
2019
- 2019-09-24 CN CN201910902759.1A patent/CN110851346B/en active Active
Patent Citations (8)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104765731A (en) * | 2014-01-02 | 2015-07-08 | 国际商业机器公司 | Database query optimization method and equipment |
| CN106919612A (en) * | 2015-12-25 | 2017-07-04 | ***通信集团浙江有限公司 | A kind of processing method and processing device of SQL script of reaching the standard grade |
| CN107748658A (en) * | 2017-09-30 | 2018-03-02 | 银联商务股份有限公司 | A kind of software development methodology, system, computer-readable recording medium and electronic equipment |
| CN108121542A (en) * | 2017-11-17 | 2018-06-05 | 广东睿江云计算股份有限公司 | A kind of collocation method of the SQL statement based on MyBatis, system |
| CN107832055A (en) * | 2017-12-11 | 2018-03-23 | 安徽科大国创云网科技有限公司 | A kind of MQL language translations are the method for sql like language |
| CN108984612A (en) * | 2018-06-12 | 2018-12-11 | 中国平安人寿保险股份有限公司 | Acquisition methods, device, computer equipment and the storage medium of target SQL statement |
| CN109325042A (en) * | 2018-08-14 | 2019-02-12 | 中国平安人寿保险股份有限公司 | Handle template acquisition methods, form processing method, device, equipment and medium |
| CN109271315A (en) * | 2018-08-23 | 2019-01-25 | 中国平安财产保险股份有限公司 | Scripted code detection method, device, computer equipment and storage medium |
Also Published As
| Publication number | Publication date |
|---|---|
| CN110851346A (en) | 2020-02-28 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US11755387B1 (en) | Updating code of an app feature based on a value of a query feature | |
| CN110764682B (en) | Page control method, page control device and computer-readable storage medium | |
| US10699061B2 (en) | Application page quick access | |
| KR101748196B1 (en) | Determining message data to present | |
| US8756593B2 (en) | Map generator for representing interrelationships between app features forged by dynamic pointers | |
| US8589876B1 (en) | Detection of central-registry events influencing dynamic pointers and app feature dependencies | |
| CN110162408B (en) | Data processing method, device, equipment and machine-readable medium | |
| CN112394908A (en) | Method and device for automatically generating embedded point page, computer equipment and storage medium | |
| CN111651296A (en) | Interception backup method, device, equipment and storage medium for data deletion operation | |
| WO2021189766A1 (en) | Data visualization method and related device | |
| CN113282591B (en) | Authority filtering method, authority filtering device, computer equipment and storage medium | |
| CN110851346B (en) | Query statement boundary problem detection method, device, equipment and storage medium | |
| CN112363814A (en) | Task scheduling method and device, computer equipment and storage medium | |
| CN109240664A (en) | A kind of method and terminal acquiring user behavior information | |
| EP3594823B1 (en) | Information display method, terminal and server | |
| CN108268298A (en) | Generation method, device, storage medium and the electronic equipment of desktop icons | |
| CN110851133A (en) | Front-end component library, and webpage response method and device based on front-end component library | |
| US9372936B2 (en) | Partial data report generation with data costing notification | |
| CN111259259B (en) | University student news recommendation method, device, equipment and storage medium | |
| CN111104620B (en) | Webpage browsing method, server and system | |
| RU2715287C1 (en) | Method, terminal and server for displaying information | |
| CN116389409A (en) | Page path processing method and device, computer equipment and storage medium | |
| CN115185798A (en) | Data acquisition and analysis method and device, electronic equipment and storage medium | |
| CN117667668A (en) | Application analysis method, device, equipment and storage medium based on application upgrading | |
| CN117194342A (en) | File renaming method, system and computing device |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |