CN111241130A - SQL language-based grammar statement extension method and system - Google Patents
SQL language-based grammar statement extension method and system Download PDFInfo
- Publication number
- CN111241130A CN111241130A CN201911385937.4A CN201911385937A CN111241130A CN 111241130 A CN111241130 A CN 111241130A CN 201911385937 A CN201911385937 A CN 201911385937A CN 111241130 A CN111241130 A CN 111241130A
- Authority
- CN
- China
- Prior art keywords
- statement
- grammar
- parameter
- syntax
- value
- 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.)
- Pending
Links
- 238000000034 method Methods 0.000 title claims abstract description 28
- 238000012546 transfer Methods 0.000 claims abstract description 83
- 230000005540 biological transmission Effects 0.000 claims abstract description 47
- 238000004458 analytical method Methods 0.000 claims abstract description 12
- 230000006870 function Effects 0.000 claims description 28
- 238000013519 translation Methods 0.000 claims description 7
- 239000003550 marker Substances 0.000 claims description 6
- 238000010586 diagram Methods 0.000 description 11
- 238000004590 computer program Methods 0.000 description 7
- 238000013461 design Methods 0.000 description 6
- 238000012545 processing Methods 0.000 description 6
- 238000011161 development Methods 0.000 description 2
- 238000005516 engineering process Methods 0.000 description 1
- 238000002347 injection Methods 0.000 description 1
- 239000007924 injection Substances 0.000 description 1
- 238000004519 manufacturing process Methods 0.000 description 1
- 238000012986 modification Methods 0.000 description 1
- 230000004048 modification Effects 0.000 description 1
- 230000003287 optical effect Effects 0.000 description 1
- 239000000243 solution Substances 0.000 description 1
Images
Classifications
-
- 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/245—Query processing
- G06F16/2453—Query optimisation
- G06F16/24534—Query rewriting; Transformation
-
- 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
Abstract
The invention discloses a method and a system for expanding syntax sentences based on SQL language, comprising the following steps: acquiring SQL query statements written based on preset WHERE condition grammar writing rules, and analyzing a start mark of each grammar statement in the SQL query statements and a value of a transfer parameter in each grammar statement; for any grammar statement, when the grammar statement has a value of a transmission parameter, analyzing and translating the grammar statement based on a preset WHERE grammar statement analysis rule according to the value of the transmission parameter in the grammar statement to determine an execution statement corresponding to the grammar statement; for any grammar statement, when the grammar statement does not have the value of the transmission parameter, analyzing and translating the grammar statement based on a preset WHERE grammar statement analysis rule according to the start mark of the grammar statement so as to determine an execution statement corresponding to the grammar statement; and replacing the syntax statement corresponding to the execution statement by using the determined execution statement, and determining the SQL execution statement corresponding to the SQL query statement.
Description
Technical Field
The present invention relates to the field of computer technologies, and in particular, to a method and a system for expanding a syntax statement based on an SQL language.
Background
When operations such as query are performed on a database through an SQL statement in software, data needed by a user needs to be filtered according to different query conditions. When writing the SQL statement, the conditions are assembled into the WHERE conditional statement of the SQL statement.
A generally written SQL statement will write each condition, the relationship between conditions, etc. into the SQL statement. In order to avoid injection risk of SQL statements, the WHERE conditional statement is generally written using the following method: where cCode? orcName like? Then, SQL statement query is carried out by encoding the transmission parameters. When in use, all conditions must be transmitted to complete the query. However, in normal program development, not all conditions are used each time, but some conditions are used for query, so that SQL statements may need to be dynamically generated or query after replacement, which greatly increases the number of codes and the difficulty of codes.
Disclosure of Invention
The invention provides an extension method and system of syntax sentences based on SQL language, which aims to solve the problem of how to extend SQL sentences and automatically generate variable transmission parameters for conditional query.
In order to solve the above problem, according to an aspect of the present invention, there is provided a method for extending a syntax statement based on SQL language, the method comprising:
acquiring SQL query statements written based on preset WHERE condition grammar writing rules, and analyzing a start mark of each grammar statement in the SQL query statements and a value of a transfer parameter in each grammar statement;
for any grammar statement, when the grammar statement has a value of a transmission parameter, analyzing and translating the grammar statement based on a preset WHERE grammar statement analysis rule according to the value of the transmission parameter in the grammar statement to determine an execution statement corresponding to the grammar statement;
for any grammar statement, when the grammar statement does not have the value of the transmission parameter, analyzing and translating the grammar statement based on a preset WHERE grammar statement analysis rule according to the start mark of the grammar statement so as to determine an execution statement corresponding to the grammar statement;
and replacing the syntax statement corresponding to the execution statement by using the determined execution statement, and determining the SQL execution statement corresponding to the SQL query statement.
Preferably, the preset WHERE condition grammar writing rule includes:
grammar writing rules of operators in, notin, equal, unequal, between2, like and unlike; and
determining a starting mark according to whether data corresponding to the key value in the transmission parameter is used or not; wherein the start marker comprises: a first preset character and a second preset character; if the data corresponding to the key required in the transmission parameter is not transmitted during query, taking a first preset character as a starting mark; otherwise, the second preset character is used as the starting mark.
Preferably, when there is a value of the transfer parameter in the syntax sentence, parsing and translating the syntax sentence according to a preset WHERE syntax sentence parsing rule according to the value of the transfer parameter in the syntax sentence to determine an execution sentence corresponding to the syntax sentence, includes:
analyzing the equial and unequal grammar sentences, when the value of a transfer function exists, acquiring data with a second parameter or a third parameter as key from the transfer parameters, and translating according to the data with the second parameter or the third parameter as key to determine an execution sentence corresponding to the grammar sentence;
analyzing in and continue grammar sentences, when a value of a transfer function exists, acquiring data with a second parameter of key from the transfer parameters, and when the data is in an array format, translating according to the data with the second parameter of key and the array length to determine an execution sentence corresponding to the grammar sentence;
analyzing the between and between2 grammar statements, when there is a value of the transfer function, acquiring data with the second and third parameters as key corresponding from the transfer parameters, and translating according to the data with the second parameter or the third parameter as key to determine the execution statement corresponding to the grammar statement;
and analyzing the like and unlike grammar sentences, when the value of the transfer function exists, acquiring data and a third parameter which correspond to the second parameter as the key from the transfer parameters, and translating the data and the third parameter which correspond to the key according to the second parameter to determine the execution sentences corresponding to the grammar sentences.
Preferably, when there is no value of the transmission parameter in the syntax sentence, according to the start flag of the syntax sentence, parsing and translating based on a preset WHERE syntax sentence parsing rule to determine an execution sentence corresponding to the syntax sentence, includes:
when the value of the transmission parameter does not exist in the grammar statement, determining a starting mark of the grammar statement, and if the starting mark is a first preset character, directly translating the grammar statement into '1-1', which means that the current condition is ignored; if the start mark is the second preset character, the grammar statement is directly translated into '1-2', which means that no data meets the condition.
Preferably, wherein the first preset character is '$'; the second predetermined character is '#'.
According to another aspect of the present invention, there is provided a system for extending a syntax statement based on SQL language, the system comprising:
the statement parsing unit is used for acquiring SQL query statements compiled based on preset WHERE conditional grammar compiling rules and parsing a start mark of each grammar statement in the SQL query statements and a value of a transmission parameter in each grammar statement;
the first execution statement determination unit is used for analyzing and translating any grammar statement according to the value of the transfer parameter in the grammar statement and based on a preset WHERE grammar statement analysis rule when the value of the transfer parameter exists in the grammar statement so as to determine an execution statement corresponding to the grammar statement;
a second execution statement determination unit, configured to, for any syntax statement, perform parsing and translation based on a preset WHERE syntax statement parsing rule according to a start flag of the syntax statement when there is no value of a transfer parameter in the syntax statement, so as to determine an execution statement corresponding to the syntax statement;
and the execution statement determining unit is used for replacing the corresponding syntax statement with the determined execution statement and determining the SQL execution statement corresponding to the SQL query statement.
Preferably, the preset WHERE condition grammar writing rule includes:
grammar writing rules of operators in, notin, equal, unequal, between2, like and unlike; and
determining a starting mark according to whether data corresponding to the key value in the transmission parameter is used or not; wherein the start marker comprises: a first preset character and a second preset character; if the data corresponding to the key required in the transmission parameter is not transmitted during query, taking a first preset character as a starting mark; otherwise, the second preset character is used as the starting mark.
Preferably, when there is a value of a transfer parameter in the syntax sentence, the first execution sentence determination unit parses and translates the syntax sentence based on a preset WHERE syntax sentence parsing rule according to the value of the transfer parameter in the syntax sentence to determine an execution sentence corresponding to the syntax sentence, including:
analyzing the equial and unequal grammar sentences, when the value of a transfer function exists, acquiring data with a second parameter or a third parameter as key from the transfer parameters, and translating according to the data with the second parameter or the third parameter as key to determine an execution sentence corresponding to the grammar sentence;
analyzing in and continue grammar sentences, when a value of a transfer function exists, acquiring data with a second parameter of key from the transfer parameters, and when the data is in an array format, translating according to the data with the second parameter of key and the array length to determine an execution sentence corresponding to the grammar sentence;
analyzing the between and between2 grammar statements, when there is a value of the transfer function, acquiring data with the second and third parameters as key corresponding from the transfer parameters, and translating according to the data with the second parameter or the third parameter as key to determine the execution statement corresponding to the grammar statement;
and analyzing the like and unlike grammar sentences, when the value of the transfer function exists, acquiring data and a third parameter which correspond to the second parameter as the key from the transfer parameters, and translating the data and the third parameter which correspond to the key according to the second parameter to determine the execution sentences corresponding to the grammar sentences.
Preferably, when there is no value of the transfer parameter in the syntax sentence, the second execution sentence determination unit, according to the start flag of the syntax sentence, performs parsing and translation based on a preset WHERE syntax sentence parsing rule to determine the execution sentence corresponding to the syntax sentence, includes:
when the value of the transmission parameter does not exist in the grammar statement, determining a starting mark of the grammar statement, and if the starting mark is a first preset character, directly translating the grammar statement into '1-1', which means that the current condition is ignored; if the start mark is the second preset character, the grammar statement is directly translated into '1-2', which means that no data meets the condition.
Preferably, wherein the first preset character is '$'; the second predetermined character is '#'.
The invention provides an SQL language-based grammar statement extension method and system, which are characterized in that all query conditions are written into an SQL statement in advance according to a defined writing rule, when in real query, transmission parameters are dynamically generated according to required conditions for query, before query, SQL statement dynamic generation is carried out according to a defined grammar parsing rule of extended query conditions, variable transmission parameters are automatically generated for conditional query, and a final result is obtained according to the generated query SQL and the query parameters. Therefore, the SQL statement can be expanded only by transmitting the parameters without modifying the SQL statement, the program code is not modified, the program expansibility is improved, the development efficiency is improved, and the code is easier to read and maintain.
Drawings
A more complete understanding of exemplary embodiments of the present invention may be had by reference to the following drawings in which:
FIG. 1 is a flow diagram of a method 100 for expanding a syntax statement in the SQL language according to an embodiment of the invention; and
fig. 2 is a schematic structural diagram of an extension system 200 for SQL-based syntax statements according to an embodiment of the present invention.
Detailed Description
The exemplary embodiments of the present invention will now be described with reference to the accompanying drawings, however, the present invention may be embodied in many different forms and is not limited to the embodiments described herein, which are provided for complete and complete disclosure of the present invention and to fully convey the scope of the present invention to those skilled in the art. The terminology used in the exemplary embodiments illustrated in the accompanying drawings is not intended to be limiting of the invention. In the drawings, the same units/elements are denoted by the same reference numerals.
Unless otherwise defined, terms (including technical and scientific terms) used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this invention belongs. Further, it will be understood that terms, such as those defined in commonly used dictionaries, should be interpreted as having a meaning that is consistent with their meaning in the context of the relevant art and will not be interpreted in an idealized or overly formal sense.
Fig. 1 is a flow chart of a method 100 for expanding a syntax statement based on SQL language according to an embodiment of the present invention. As shown in fig. 1, according to the method for expanding a syntax statement based on SQL language provided by the embodiment of the present invention, all query conditions are written into an SQL statement in advance according to a defined writing rule, when a real query is performed, a transfer parameter is dynamically generated according to a required condition to perform a query, before the query, a dynamic generation of the SQL statement is performed according to a defined syntax parsing rule for expanding the query conditions, and a variable transfer parameter is automatically generated to perform a conditional query, so as to obtain a final result according to the generated SQL and the query parameters. Therefore, the SQL statement can be expanded only by transmitting the parameters without modifying the SQL statement, the program code is not modified, the program expansibility is improved, the development efficiency is improved, and the code is easier to read and maintain. The method 100 for expanding the syntax statements based on the SQL language provided by the embodiment of the present invention starts with step 101, obtains the SQL query statements written based on the preset WHERE conditional syntax writing rule in step 101, and parses the start flag of each syntax statement in the SQL query statement and the value of the transfer parameter in each syntax statement.
Preferably, the preset WHERE condition grammar writing rule includes:
grammar writing rules of operators in, notin, equal, unequal, between2, like and unlike; and
determining a starting mark according to whether data corresponding to the key value in the transmission parameter is used or not; wherein the start marker comprises: a first preset character and a second preset character; if the data corresponding to the key required in the transmission parameter is not transmitted during query, taking a first preset character as a starting mark; otherwise, the second preset character is used as the starting mark.
Preferably, wherein the first preset character is '$'; the second predetermined character is '#'.
In the embodiment of the invention, the corresponding grammar rule is customized according to the common WHERE conditional grammar, which specifically comprises: rules are written in, notin, equal, unequal, between2, like, unlike, etc. grammars.
For the equal and unequal grammars, there are three (colName, param, default) grammar design parameters, the first parameter is a column of an SQL query statement, the second parameter is a key for transmitting data in the query process, the third parameter is a selectable item, and if the key does not exist in the transmission parameters, the data is used for data replacement.
For in and non grammars, the grammar design parameters are two (colName, paramList), the first parameter is the column of SQL query statement, the second parameter is the key for transmitting the parameters in the query process, and the value corresponding to the key is an array grammar to be normally executed.
For the grammars of between and between2, there are three grammar design parameters (colName, fromParam, toParam), the first parameter is a column of an SQL query statement, the second parameter is a key corresponding to an interval start value in query data, and the third parameter is a key corresponding to an interval end value in query data. between and between2 differ in that it contains no span start value and no span end value.
For the like and unlike syntax, the syntax designs three parameters, (field, paramSet, _ paramSet%), the first parameter is the column of the SQL query statement, the second parameter is the key of the transfer parameter in the query process, the third parameter is an optional fixed format, which is used to distinguish left matching, right matching, left and right matching, if the third parameter is not written, the left and right matching is translated.
In addition, each rule is configured with corresponding parameters, and is matched with two prefixes, namely $and #', for distinguishing whether the parameters are transmitted or not and corresponding to different query results. All design grammars start with "$", "#", and are used for processing and inquiring whether data corresponding to keys in the transfer parameters are used or not. If key corresponding data required in the grammar is not transmitted when inquiring, when "$" starts, the grammar is analyzed to be 1 to 1 ', which means that the current condition is ignored, and when "#" starts, the grammar is analyzed to be 1 to 2', which means that no data meets the condition.
For example, the SQL query example written using the writing rule of the embodiment of the present invention is:
in step 102, for any grammar statement, when the value of the transfer parameter exists in the grammar statement, the grammar statement is analyzed and translated based on a preset WHERE grammar statement analysis rule according to the value of the transfer parameter in the grammar statement so as to determine an execution statement corresponding to the grammar statement.
Preferably, when there is a value of the transfer parameter in the syntax sentence, parsing and translating the syntax sentence according to a preset WHERE syntax sentence parsing rule according to the value of the transfer parameter in the syntax sentence to determine an execution sentence corresponding to the syntax sentence, includes:
analyzing the equial and unequal grammar sentences, when the value of a transfer function exists, acquiring data with a second parameter or a third parameter as key from the transfer parameters, and translating according to the data with the second parameter or the third parameter as key to determine an execution sentence corresponding to the grammar sentence;
analyzing in and continue grammar sentences, when a value of a transfer function exists, acquiring data with a second parameter of key from the transfer parameters, and when the data is in an array format, translating according to the data with the second parameter of key and the array length to determine an execution sentence corresponding to the grammar sentence;
analyzing the between and between2 grammar statements, when there is a value of the transfer function, acquiring data with the second and third parameters as key corresponding from the transfer parameters, and translating according to the data with the second parameter or the third parameter as key to determine the execution statement corresponding to the grammar statement;
and analyzing the like and unlike grammar sentences, when the value of the transfer function exists, acquiring data and a third parameter which correspond to the second parameter as the key from the transfer parameters, and translating the data and the third parameter which correspond to the key according to the second parameter to determine the execution sentences corresponding to the grammar sentences.
In step 103, for any grammar statement, when the grammar statement does not have the value of the transmission parameter, according to the start mark of the grammar statement, the grammar statement is analyzed and translated based on the preset WHERE grammar statement analysis rule, so as to determine the execution statement corresponding to the grammar statement.
Preferably, when there is no value of the transmission parameter in the syntax sentence, according to the start flag of the syntax sentence, parsing and translating based on a preset WHERE syntax sentence parsing rule to determine an execution sentence corresponding to the syntax sentence, includes:
when the value of the transmission parameter does not exist in the grammar statement, determining a starting mark of the grammar statement, and if the starting mark is a first preset character, directly translating the grammar statement into '1-1', which means that the current condition is ignored; if the start mark is the second preset character, the grammar statement is directly translated into '1-2', which means that no data meets the condition.
After writing the SQL statements, when the SQL statements are called in the program for query, key value data query needs to be transmitted according to a predetermined rule, for example, HashMap data types are transmitted in Java as query parameters, keys corresponding to HashMap are names of parameters written in the SQL statements, and values corresponding to keys are parameters required by the replacement of SQL by us.
All grammars first parse the start tag of the grammar: "$", "#", then the corresponding parameter in the grammar is key to inquire the corresponding value in the transfer parameter, if not, the default value in the grammar is analyzed as the transfer parameter. If the transmission parameters exist, the corresponding SQL statement is continuously analyzed according to the grammar rule, if the transmission parameters do not exist, the SQL statement is directly translated into 1-1 or 1-2 to carry out SQL statement query, and parameter transmission processing is not carried out.
And for the parsing of the equial grammar and the unequal grammar, acquiring data with a second parameter of key from the transmission parameters according to grammar rules, if the data is null, using the configuration of a third parameter, and directly parsing according to the grammar rules of "$" and "#" under the condition that the data is null. Translate the equal syntax into "first parameter? ", the unequal grammar is translated into" first parameter! ? "replaces the query SQL statement and writes the retrieved data into the parameters of the query.
And for in and retin syntax analysis, acquiring data with a second parameter of key from the transmission parameters according to a syntax rule, and judging whether the data is in an array format. Data does not exist or the formats are not matched, and the data is directly analyzed according to the syntax rules of "$" and "#". Judging the length of the array, translating the in grammar into' in (; the notin syntax is translated into "in (. And writing the data in the array into the query parameters one by one.
And for parsing between the between and 2 grammars, acquiring data corresponding to keys of the second and third parameters from the transfer parameters according to grammar rules. If the two acquired parameters are all null, directly analyzing according to the syntax rules of "$" and "#"; only the data corresponding to the second parameter is obtained, and the between syntax is translated into "the first parameter >? ", between2 translates to" first parameter >? "; only data corresponding to the third parameter is acquired, and the between syntax is translated into "the first parameter? ", between2 syntax translates to" the first parameter <? "; two parameters were acquired, the between syntax translation being "(first parameter >. And replacing the former grammar with the translated SQL statement, and writing the correspondingly acquired data into the query parameters in sequence.
And for the parsing of the like and unlike grammars, acquiring data corresponding to the second parameter as the key from the transmission parameters according to the grammar rule. If not, the syntax rules of "$" and "#" are directly analyzed. Upon acquisition, the like syntax translates to "first parameter like? "the unlike grammar translates to" the first parameter not like? ". Checking whether a third parameter exists, and if the third parameter does not exist, writing the query parameter into "% acquired data"; the third parameter is "_ the second parameter%", and the inquiry parameter is written in "% of acquired data"; the third parameter is the "% _ second parameter", and the query parameter is written in "% acquired data".
And step 104, replacing the syntax statement corresponding to the execution statement with the determined execution statement, and determining the SQL execution statement corresponding to the SQL query statement.
In the embodiment of the invention, the SQL statement is queried according to the query SQL analysis example. Wherein, the program transmission parameters are: { param1: Condition 1, param41: Condition 41, param51: Condition 51, param52: Condition 52, param6: Condition 6, param8: [ Condition 81, Condition 82, Condition 83] }.
According to the SQL query example written above, the concrete query conditions are resolved as follows:
field1, param1, get key "param 1" corresponds to data "condition 1", syntax replacement is "t.field 1? ", the parameter writes" condition 1 ".
And $ equal (t. field2, param2), no data corresponding to "param 2" key is found, and "1 ═ 1" is replaced according to "$" and "#" replacement rules.
$ equal (t. field3, param3, 'default'), find no data with key "param 3", have default values in parameters, and replace grammar with "t. field1 ═? ", the parameter is written to" default ";
$ between (t.field4, param41, param42), obtain the value corresponding to key "param 41" as "condition 41", find the value corresponding to parameter 42 not, according to the syntax parsing rule, replace the syntax with "t.field4 >? ", the parameter writes" condition 41 ".
$ between2(t.field5, param51, param52) obtains a value "condition 51" corresponding to "param 51" as a key, obtains a value "condition 52" corresponding to "param 52" as a key, and replaces the syntax with "(t.field5 >.
$ like (t. field6, param6, _ param 6%), a value of "condition 6" corresponding to a key of "param 6", a third parameter format of "_ second parameter%", and a syntax rule of "t. field6 like? ", the parameter is written as" condition 6% ".
Field7, param7, data corresponding to key "param 7" is not obtained, and "1 ═ 2" is replaced according to "$" and "#" replacement rules.
$ in (t.field8, paramList8), a value corresponding to "param 6" obtained key is "[ condition 81, condition 82, condition 83 ]", and the obtained value is 3 in length, and the syntax is replaced by "t.field8 in (.
Therefore, the final executed SQL query statement can be obtained as:
the specific parameters of the corresponding SQL query are as follows: [ "condition 1", "default", "condition 41", "condition 51", "condition 52", "condition 6%", "condition 81", "condition 82", "condition 83" ].
The method of the embodiment of the invention designs grammar rules such as equal, unequal, in, notin, between2, like, unlike, and the like, and almost covers all conditions in a WHERE query statement; when the SQL statement is executed, the transmitted parameters are received, the SQL statement is analyzed and translated by combining the syntax rules, and finally the query is executed, so that the SQL statement can be expanded only by transmitting the parameters without modifying the SQL statement, and the expansibility of the program is improved while the program code is not modified.
Fig. 2 is a schematic structural diagram of an extension system 200 for SQL-based syntax statements according to an embodiment of the present invention. As shown in fig. 2, an embodiment of the present invention provides an extension system 200 for a syntax statement based on SQL, including: a sentence parsing unit 201, a first execution sentence determination unit 202, a second execution sentence determination unit 203, and an execution sentence determination unit 204.
Preferably, the statement parsing unit 201 is configured to obtain an SQL query statement written based on a preset WHERE conditional syntax writing rule, and parse a start flag of each syntax statement in the SQL query statement and a value of a transfer parameter in each syntax statement.
Preferably, the preset WHERE condition grammar writing rule includes:
grammar writing rules of operators in, notin, equal, unequal, between2, like and unlike; and
determining a starting mark according to whether data corresponding to the key value in the transmission parameter is used or not; wherein the start marker comprises: a first preset character and a second preset character; if the data corresponding to the key required in the transmission parameter is not transmitted during query, taking a first preset character as a starting mark; otherwise, the second preset character is used as the starting mark.
Preferably, wherein the first preset character is '$'; the second predetermined character is '#'.
Preferably, for any syntax sentence, when there is a value of a transfer parameter in the syntax sentence, the first execution sentence determination unit 202 is configured to parse and translate the syntax sentence based on a preset WHERE syntax sentence parsing rule according to the value of the transfer parameter in the syntax sentence, so as to determine an execution sentence corresponding to the syntax sentence.
Preferably, when there is a value of a transfer parameter in the syntax sentence, the first execution sentence determining unit 202, according to the value of the transfer parameter in the syntax sentence, parsing and translating the syntax sentence based on a preset WHERE syntax sentence parsing rule to determine an execution sentence corresponding to the syntax sentence, includes:
analyzing the equial and unequal grammar sentences, when the value of a transfer function exists, acquiring data with a second parameter or a third parameter as key from the transfer parameters, and translating according to the data with the second parameter or the third parameter as key to determine an execution sentence corresponding to the grammar sentence;
analyzing in and continue grammar sentences, when a value of a transfer function exists, acquiring data with a second parameter of key from the transfer parameters, and when the data is in an array format, translating according to the data with the second parameter of key and the array length to determine an execution sentence corresponding to the grammar sentence;
analyzing the between and between2 grammar statements, when there is a value of the transfer function, acquiring data with the second and third parameters as key corresponding from the transfer parameters, and translating according to the data with the second parameter or the third parameter as key to determine the execution statement corresponding to the grammar statement;
and analyzing the like and unlike grammar sentences, when the value of the transfer function exists, acquiring data and a third parameter which correspond to the second parameter as the key from the transfer parameters, and translating the data and the third parameter which correspond to the key according to the second parameter to determine the execution sentences corresponding to the grammar sentences.
Preferably, the second executed statement determining unit 203 is configured to, for any syntax statement, when there is no value of the transfer parameter in the syntax statement, perform parsing and translation based on a preset WHERE syntax statement parsing rule according to a start flag of the syntax statement to determine an executed statement corresponding to the syntax statement.
Preferably, when there is no value of the delivery parameter in the syntax sentence, the second execution sentence determination unit 203, according to the start flag of the syntax sentence, performs parsing and translation based on a preset WHERE syntax sentence parsing rule to determine the execution sentence corresponding to the syntax sentence, includes:
when the value of the transmission parameter does not exist in the grammar statement, determining a starting mark of the grammar statement, and if the starting mark is a first preset character, directly translating the grammar statement into '1-1', which means that the current condition is ignored; if the start mark is the second preset character, the grammar statement is directly translated into '1-2', which means that no data meets the condition.
Preferably, the execution statement determining unit 204 is configured to replace the syntax statement corresponding to the determined execution statement with the determined execution statement, and determine the SQL execution statement corresponding to the SQL query statement.
The extension system 200200 of the SQL-based syntax sentence according to the embodiment of the present invention corresponds to the extension method 100 of the SQL-based syntax sentence according to another embodiment of the present invention, and is not described herein again.
The invention has been described with reference to a few embodiments. However, other embodiments of the invention than the one disclosed above are equally possible within the scope of the invention, as would be apparent to a person skilled in the art from the appended patent claims.
Generally, all terms used in the claims are to be interpreted according to their ordinary meaning in the technical field, unless explicitly defined otherwise herein. All references to "a/an/the [ device, component, etc ]" are to be interpreted openly as referring to at least one instance of said device, component, etc., unless explicitly stated otherwise. The steps of any method disclosed herein do not have to be performed in the exact order disclosed, unless explicitly stated.
As will be appreciated by one skilled in the art, embodiments of the present application may be provided as a method, system, or computer program product. Accordingly, the present application may take the form of an entirely hardware embodiment, an entirely software embodiment or an embodiment combining software and hardware aspects. Furthermore, the present application may take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, and the like) having computer-usable program code embodied therein.
The present application is described with reference to flowchart illustrations and/or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the application. It will be understood that each flow and/or block of the flow diagrams and/or block diagrams, and combinations of flows and/or blocks in the flow diagrams and/or block diagrams, can be implemented by computer program instructions. These computer program instructions may be provided to a processor of a general purpose computer, special purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.
These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer-readable memory produce an article of manufacture including instruction means which implement the function specified in the flowchart flow or flows and/or block diagram block or blocks.
These computer program instructions may also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.
Finally, it should be noted that: the above embodiments are only for illustrating the technical solutions of the present invention and not for limiting the same, and although the present invention is described in detail with reference to the above embodiments, those of ordinary skill in the art should understand that: modifications and equivalents may be made to the embodiments of the invention without departing from the spirit and scope of the invention, which is to be covered by the claims.
Claims (10)
1. A method for expanding syntax statements based on SQL language is characterized in that the method comprises the following steps:
acquiring SQL query statements written based on preset WHERE condition grammar writing rules, and analyzing a start mark of each grammar statement in the SQL query statements and a value of a transfer parameter in each grammar statement;
for any grammar statement, when the grammar statement has a value of a transmission parameter, analyzing and translating the grammar statement based on a preset WHERE grammar statement analysis rule according to the value of the transmission parameter in the grammar statement to determine an execution statement corresponding to the grammar statement;
for any grammar statement, when the grammar statement does not have the value of the transmission parameter, analyzing and translating the grammar statement based on a preset WHERE grammar statement analysis rule according to the start mark of the grammar statement so as to determine an execution statement corresponding to the grammar statement;
and replacing the syntax statement corresponding to the execution statement by using the determined execution statement, and determining the SQL execution statement corresponding to the SQL query statement.
2. The method of claim 1, wherein the preset WHERE conditional grammar writing rules comprise:
grammar writing rules of operators in, notin, equal, unequal, between2, like and unlike; and
determining a starting mark according to whether data corresponding to the key value in the transmission parameter is used or not; wherein the start marker comprises: a first preset character and a second preset character; if the data corresponding to the key required in the transmission parameter is not transmitted during query, taking a first preset character as a starting mark; otherwise, the second preset character is used as the starting mark.
3. The method of claim 1, wherein when the value of the transmission parameter exists in the syntax sentence, parsing and translating the syntax sentence according to the value of the transmission parameter in the syntax sentence based on a preset WHERE syntax sentence parsing rule to determine an execution sentence corresponding to the syntax sentence comprises:
analyzing the equial and unequal grammar sentences, when the value of a transfer function exists, acquiring data with a second parameter or a third parameter as key from the transfer parameters, and translating according to the data with the second parameter or the third parameter as key to determine an execution sentence corresponding to the grammar sentence;
analyzing in and continue grammar sentences, when a value of a transfer function exists, acquiring data with a second parameter of key from the transfer parameters, and when the data is in an array format, translating according to the data with the second parameter of key and the array length to determine an execution sentence corresponding to the grammar sentence;
analyzing the between and between2 grammar statements, when there is a value of the transfer function, acquiring data with the second and third parameters as key corresponding from the transfer parameters, and translating according to the data with the second parameter or the third parameter as key to determine the execution statement corresponding to the grammar statement;
and analyzing the like and unlike grammar sentences, when the value of the transfer function exists, acquiring data and a third parameter which correspond to the second parameter as the key from the transfer parameters, and translating the data and the third parameter which correspond to the key according to the second parameter to determine the execution sentences corresponding to the grammar sentences.
4. The method of claim 1, wherein when there is no value of the transmission parameter in the syntax sentence, parsing and translating based on a preset WHERE syntax sentence parsing rule according to the start flag of the syntax sentence to determine an execution sentence corresponding to the syntax sentence comprises:
when the value of the transmission parameter does not exist in the grammar statement, determining a starting mark of the grammar statement, and if the starting mark is a first preset character, directly translating the grammar statement into '1-1', which means that the current condition is ignored; if the start mark is the second preset character, the grammar statement is directly translated into '1-2', which means that no data meets the condition.
5. The method of claim 2 or 4, wherein the first predetermined character is '$'; the second predetermined character is '#'.
6. An extension system of syntax statements based on SQL language, the system comprising:
the statement parsing unit is used for acquiring SQL query statements compiled based on preset WHERE conditional grammar compiling rules and parsing a start mark of each grammar statement in the SQL query statements and a value of a transmission parameter in each grammar statement;
the first execution statement determination unit is used for analyzing and translating any grammar statement according to the value of the transfer parameter in the grammar statement and based on a preset WHERE grammar statement analysis rule when the value of the transfer parameter exists in the grammar statement so as to determine an execution statement corresponding to the grammar statement;
a second execution statement determination unit, configured to, for any syntax statement, perform parsing and translation based on a preset WHERE syntax statement parsing rule according to a start flag of the syntax statement when there is no value of a transfer parameter in the syntax statement, so as to determine an execution statement corresponding to the syntax statement;
and the execution statement determining unit is used for replacing the corresponding syntax statement with the determined execution statement and determining the SQL execution statement corresponding to the SQL query statement.
7. The system of claim 6, wherein the preset WHERE conditional grammar writing rules comprise:
grammar writing rules of operators in, notin, equal, unequal, between2, like and unlike; and
determining a starting mark according to whether data corresponding to the key value in the transmission parameter is used or not; wherein the start marker comprises: a first preset character and a second preset character; if the data corresponding to the key required in the transmission parameter is not transmitted during query, taking a first preset character as a starting mark; otherwise, the second preset character is used as the starting mark.
8. The system according to claim 6, wherein the first executed statement determining unit, when the value of the transfer parameter exists in the syntax statement, parses and translates the syntax statement according to the value of the transfer parameter in the syntax statement based on a preset WHERE syntax statement parsing rule to determine the executed statement corresponding to the syntax statement, includes:
analyzing the equial and unequal grammar sentences, when the value of a transfer function exists, acquiring data with a second parameter or a third parameter as key from the transfer parameters, and translating according to the data with the second parameter or the third parameter as key to determine an execution sentence corresponding to the grammar sentence;
analyzing in and continue grammar sentences, when a value of a transfer function exists, acquiring data with a second parameter of key from the transfer parameters, and when the data is in an array format, translating according to the data with the second parameter of key and the array length to determine an execution sentence corresponding to the grammar sentence;
analyzing the between and between2 grammar statements, when there is a value of the transfer function, acquiring data with the second and third parameters as key corresponding from the transfer parameters, and translating according to the data with the second parameter or the third parameter as key to determine the execution statement corresponding to the grammar statement;
and analyzing the like and unlike grammar sentences, when the value of the transfer function exists, acquiring data and a third parameter which correspond to the second parameter as the key from the transfer parameters, and translating the data and the third parameter which correspond to the key according to the second parameter to determine the execution sentences corresponding to the grammar sentences.
9. The system according to claim 6, wherein the second execution statement determination unit, when there is no value of the transfer parameter in the syntax statement, performs parsing and translation based on a preset WHERE syntax statement parsing rule according to the start flag of the syntax statement to determine the execution statement corresponding to the syntax statement, includes:
when the value of the transmission parameter does not exist in the grammar statement, determining a starting mark of the grammar statement, and if the starting mark is a first preset character, directly translating the grammar statement into '1-1', which means that the current condition is ignored; if the start mark is the second preset character, the grammar statement is directly translated into '1-2', which means that no data meets the condition.
10. The system of claim 7 or 9, wherein the first predetermined character is '$'; the second predetermined character is '#'.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201911385937.4A CN111241130A (en) | 2019-12-29 | 2019-12-29 | SQL language-based grammar statement extension method and system |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201911385937.4A CN111241130A (en) | 2019-12-29 | 2019-12-29 | SQL language-based grammar statement extension method and system |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN111241130A true CN111241130A (en) | 2020-06-05 |
Family
ID=70865859
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201911385937.4A Pending CN111241130A (en) | 2019-12-29 | 2019-12-29 | SQL language-based grammar statement extension method and system |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN111241130A (en) |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112765158A (en) * | 2020-12-30 | 2021-05-07 | 中国人寿保险股份有限公司上海数据中心 | Data modification method and device of database and storage medium |
| WO2023060878A1 (en) * | 2021-10-13 | 2023-04-20 | 苏州浪潮智能科技有限公司 | Data query method and system, heterogeneous acceleration platform, and storage medium |
Citations (12)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US6735594B1 (en) * | 2000-06-23 | 2004-05-11 | International Business Machines Corporation | Transparent parameter marker support for a relational database over a network |
| US20130006609A1 (en) * | 2011-06-28 | 2013-01-03 | International Business Machines Corporation | Method, system and program storage device for automatic incremental learning of programming language grammar |
| US20140156696A1 (en) * | 2012-12-04 | 2014-06-05 | Oracle International Corporation | Dynamic generation of database queries in query builders |
| CN103870555A (en) * | 2014-03-04 | 2014-06-18 | 赛特斯信息科技股份有限公司 | Spring data JPA dynamic query realization method |
| CN104331295A (en) * | 2014-11-05 | 2015-02-04 | 北京中科辅龙信息技术有限公司 | Page generation method and system |
| CN104361040A (en) * | 2014-10-29 | 2015-02-18 | 中国建设银行股份有限公司 | Method and device for customized query |
| WO2016011904A1 (en) * | 2014-07-23 | 2016-01-28 | 华为技术有限公司 | Database access method and apparatus and database system |
| CN107463662A (en) * | 2017-07-31 | 2017-12-12 | 拉卡拉支付股份有限公司 | A kind of dynamic Sql querying methods, device |
| US20180218032A1 (en) * | 2017-01-31 | 2018-08-02 | Salesforce.Com, Inc. | Systems, methods, and apparatuses for implementing conditional statement execution within a multi-tenant aware structured query language |
| CN110019335A (en) * | 2017-10-17 | 2019-07-16 | 航天信息股份有限公司 | A kind of method and system carrying out dynamic expansion to SQL query statement based on note |
| CN110083605A (en) * | 2019-04-24 | 2019-08-02 | 天津中新智冠信息技术有限公司 | Traffic table querying method, device, server and computer readable storage medium |
| CN110362595A (en) * | 2019-07-19 | 2019-10-22 | 北京首汽智行科技有限公司 | A kind of SQL statement dynamic analysis method |
-
2019
- 2019-12-29 CN CN201911385937.4A patent/CN111241130A/en active Pending
Patent Citations (12)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US6735594B1 (en) * | 2000-06-23 | 2004-05-11 | International Business Machines Corporation | Transparent parameter marker support for a relational database over a network |
| US20130006609A1 (en) * | 2011-06-28 | 2013-01-03 | International Business Machines Corporation | Method, system and program storage device for automatic incremental learning of programming language grammar |
| US20140156696A1 (en) * | 2012-12-04 | 2014-06-05 | Oracle International Corporation | Dynamic generation of database queries in query builders |
| CN103870555A (en) * | 2014-03-04 | 2014-06-18 | 赛特斯信息科技股份有限公司 | Spring data JPA dynamic query realization method |
| WO2016011904A1 (en) * | 2014-07-23 | 2016-01-28 | 华为技术有限公司 | Database access method and apparatus and database system |
| CN104361040A (en) * | 2014-10-29 | 2015-02-18 | 中国建设银行股份有限公司 | Method and device for customized query |
| CN104331295A (en) * | 2014-11-05 | 2015-02-04 | 北京中科辅龙信息技术有限公司 | Page generation method and system |
| US20180218032A1 (en) * | 2017-01-31 | 2018-08-02 | Salesforce.Com, Inc. | Systems, methods, and apparatuses for implementing conditional statement execution within a multi-tenant aware structured query language |
| CN107463662A (en) * | 2017-07-31 | 2017-12-12 | 拉卡拉支付股份有限公司 | A kind of dynamic Sql querying methods, device |
| CN110019335A (en) * | 2017-10-17 | 2019-07-16 | 航天信息股份有限公司 | A kind of method and system carrying out dynamic expansion to SQL query statement based on note |
| CN110083605A (en) * | 2019-04-24 | 2019-08-02 | 天津中新智冠信息技术有限公司 | Traffic table querying method, device, server and computer readable storage medium |
| CN110362595A (en) * | 2019-07-19 | 2019-10-22 | 北京首汽智行科技有限公司 | A kind of SQL statement dynamic analysis method |
Cited By (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN112765158A (en) * | 2020-12-30 | 2021-05-07 | 中国人寿保险股份有限公司上海数据中心 | Data modification method and device of database and storage medium |
| WO2023060878A1 (en) * | 2021-10-13 | 2023-04-20 | 苏州浪潮智能科技有限公司 | Data query method and system, heterogeneous acceleration platform, and storage medium |
| US11893011B1 (en) | 2021-10-13 | 2024-02-06 | Inspur Suzhou Intelligent Technology Co., Ltd. | Data query method and system, heterogeneous acceleration platform, and storage medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US7458022B2 (en) | Hardware/software partition for high performance structured data transformation | |
| CN106951228B (en) | Method and device for processing differences in version iteration | |
| DK2778914T3 (en) | PROCEDURE AND SYSTEM FOR GENERATING A PARSER AND PARISING COMPLEX DATA | |
| US7437666B2 (en) | Expression grouping and evaluation | |
| US7328403B2 (en) | Device for structured data transformation | |
| CN101788992A (en) | Method and system for converting query sentence of database | |
| WO2004079571A2 (en) | Hardware accelerator state table compiler | |
| CN111930615B (en) | Code quality evaluation method and device | |
| CN107291907B (en) | Business data multi-language storage and query method and device | |
| CN111241130A (en) | SQL language-based grammar statement extension method and system | |
| CN115016793A (en) | Code generation method and device based on syntax tree, electronic equipment and storage medium | |
| CN116089542A (en) | JDBC-based database adaptation method and device | |
| CN101201750B (en) | Method of providing data for encoding/decoding using syntax lexical analysis tool | |
| CN114265772A (en) | Test case generation method and test method | |
| CN112000690A (en) | Method and device for analyzing structured operation statement | |
| CN114090017B (en) | Method and device for analyzing programming language and nonvolatile storage medium | |
| CN102426550B (en) | Source code analysis method and system | |
| CN115525671A (en) | Data query method, device, equipment and storage medium | |
| CN114064601B (en) | Storage process conversion method, device, equipment and storage medium | |
| CN111597202B (en) | Battlefield situation information on-demand extraction method based on fractal theory | |
| CN110058858B (en) | JSON data processing method and device | |
| CN108008947B (en) | Intelligent prompting method and device for programming statement, server and storage medium | |
| Longley et al. | Json-ld 1.0 processing algorithms and api | |
| CN100591070C (en) | Protocol message analytic method and protocol message analytic system | |
| CN113688131A (en) | Method, system, device and computer program product for field level impact analysis |
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 |