A method and apparatus for querying data using a converted SQL statement

Abstract

A method and device for querying data using a converted SQL statement, the method comprising: obtaining a basic SQL statement comprising a query filtering condition and converting the basic SQL statement into an SQL abstract syntax tree; traversing the SQL abstract syntax tree and determining whether a preset condition is met; if the preset condition is met, obtaining a modified SQL abstract syntax tree according to a predefined rule and outputting the modified SQL abstract syntax tree as a converted SQL statement; and querying data satisfying the query filtering condition in a database using the converted SQL statement.

Description

Technical Field

[0001] This application relates to the field of databases, and more particularly to a method and apparatus for querying data using transformed SQL statements. Background Technology

[0002] In web applications, it is a very common requirement to dynamically query the database and return data based on multiple query conditions entered by the user.

[0003] In the field of Java Web backend development, under the MyBatis technology framework, the common solution for this requirement is to use dynamic SQL templates; however, if there are more complex query filtering conditions, they need to be written out in detail one by one in the SQL query template file.

[0004] In the Java web backend Hibernate framework, a common solution is the HQL Criteria query technique. However, HQL Criteria requires converting each query condition into a Criteria statement, and each query condition necessitates writing one or more Java statements manually.

[0005] Both of the above-mentioned general technical solutions require manual maintenance of SQL query templates or Java Criteria statements, which is labor-intensive, meticulous, error-prone, and not universally applicable. Summary of the Invention

[0006] This application provides a method and apparatus for querying data using transformed SQL statements, which can be modified based on basic SQL query statements and dynamically variable query filtering conditions, and the modified SQL statements can be used to query data in different databases.

[0007] This application provides a method for querying data using transformed SQL statements, the method including:

[0008] Obtain the basic SQL statement, including query filtering conditions, and convert it into an SQL abstract syntax tree;

[0009] Traverse the SQL abstract syntax tree and determine whether the preset conditions are met;

[0010] If the preset conditions are met, the modified SQL abstract syntax tree is obtained according to the predefined rules, and the modified SQL abstract syntax tree is output as the converted SQL statement.

[0011] Use the transformed SQL statement to query the database for data that meets the query filtering conditions.

[0012] In one exemplary embodiment, after obtaining the basic SQL statement including query filtering conditions, the method further includes:

[0013] Extract the four elements of SQL variable replacement based on the query filtering conditions;

[0014] The four elements are: SQL variable name, SQL variable data type, query filtering operation, and constant value.

[0015] In one exemplary embodiment, traversing the SQL abstract syntax tree and determining whether a preset condition is met includes:

[0016] Traverse the SQL abstract syntax tree;

[0017] Determine whether the SQL variable name in the SQL abstract syntax tree matches the SQL variable name of the four-element tuple corresponding to the query filter condition;

[0018] If a match is found, then the preset conditions are met.

[0019] In one exemplary embodiment, determining whether the SQL variable name in the SQL abstract syntax tree matches the SQL variable name in the four elements corresponding to the query filter condition includes:

[0020] Compare the SQL variable names in the SQL abstract syntax tree with the SQL variable name strings of the four-element tuple;

[0021] If the strings of two variable names are equal, then the two variable names are considered to match.

[0022] In one exemplary embodiment, the predefined rules include:

[0023] Based on the data type of the SQL variable in the four-element tuple, convert the SQL variable into the SQL constant expression corresponding to the SQL constant value of the four-element tuple.

[0024] In one exemplary embodiment, the predefined rules further include:

[0025] If an SQL variable is a subexpression in an SQL binary expression, then the binary operators in the SQL binary expression are modified to the binary operators corresponding to the query filtering operations in the four-element tuple.

[0026] In one exemplary embodiment, modifying the binary operators of the SQL binary expression to the binary operators corresponding to the query filtering operations in the four-element tuple includes:

[0027] Based on the value of the query filtering operation and the preset correspondence in the mapping table of SQL binary operators, the query filtering operation of the four-element tuple is replaced with a binary operator; wherein, the preset correspondence includes the binary operators corresponding to the values ​​of different query filtering operations.

[0028] In one exemplary embodiment, the values ​​of query filtering operations in the mapping table of SQL binary operators correspond one-to-one with the binary operators.

[0029] To address the aforementioned problems, the present invention also provides an apparatus for querying data using a transformed SQL statement. The apparatus includes a memory and a processor. The memory stores a program for querying data using a transformed SQL statement, and the processor reads and executes the program for querying data using a transformed SQL statement, performing the method described in any of the above embodiments.

[0030] To address the aforementioned problems, the present invention also provides a computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, perform the operations in any of the above embodiments.

[0031] Compared with related technologies, this application provides a method and apparatus for querying data using a transformed SQL statement. The method includes: obtaining a basic SQL statement including query filtering conditions and converting it into an SQL abstract syntax tree; traversing the SQL abstract syntax tree and determining whether it meets preset conditions; if the preset conditions are met, obtaining a modified SQL abstract syntax tree according to predefined rules and outputting the modified SQL abstract syntax tree as a transformed SQL statement; and using the transformed SQL statement to query data that meets the query filtering conditions in a database. This application can modify the basic SQL query statement and dynamically variable query filtering conditions, and use the modified SQL statement to query data in different databases.

[0032] Other features and advantages of this application will be set forth in the following description, and will be apparent in part from the description, or may be learned by practicing the application. Other advantages of this application can be realized and obtained by means of the solutions described in the description and the accompanying drawings. Attached Figure Description

[0033] The accompanying drawings are used to provide an understanding of the technical solutions of this application and constitute a part of the specification. They are used together with the embodiments of this application to explain the technical solutions of this application and do not constitute a limitation on the technical solutions of this application.

[0034] Figure 1 This is a flowchart illustrating a method for querying data using transformed SQL statements according to an embodiment of this application.

[0035] Figure 2 This is a schematic diagram of an apparatus for querying data using transformed SQL statements according to an embodiment of this application. Detailed Implementation

[0036] This application describes several embodiments, but these descriptions are exemplary and not restrictive, and it will be apparent to those skilled in the art that many more embodiments and implementations are possible within the scope of the embodiments described herein. Although many possible combinations of features are shown in the drawings and discussed in the detailed description, many other combinations of the disclosed features are also possible. Unless specifically limited, any feature or element of any embodiment may be used in combination with, or may replace, any feature or element of any other embodiment.

[0037] This application includes and contemplates combinations of features and elements known to those skilled in the art. The embodiments, features, and elements disclosed in this application may also be combined with any conventional features or elements to form a unique inventive scheme as defined by the claims. Any feature or element of any embodiment may also be combined with features or elements from other inventive schemes to form another unique inventive scheme as defined by the claims. Therefore, it should be understood that any feature shown and / or discussed in this application may be implemented individually or in any suitable combination. Therefore, the embodiments are not limited except by the limitations imposed by the appended claims and their equivalents. Furthermore, various modifications and changes may be made within the scope of the appended claims.

[0038] Furthermore, in describing representative embodiments, the specification may have presented methods and / or processes as a specific sequence of steps. However, the method or process should not be limited to the specific order of steps described herein, to the extent that it does not depend on such a specific order. As will be understood by those skilled in the art, other sequences of steps are also possible. Therefore, the specific order of steps set forth in the specification should not be construed as a limitation of the claims. Moreover, the claims concerning the method and / or process should not be limited to the steps performed in the written order, and those skilled in the art will readily understand that these orders can be varied and still remain within the spirit and scope of the embodiments of this application.

[0039] In some technologies, particularly within the MyBatis framework for Java Web backend development, a common solution to this requirement is to use dynamic SQL templates, for example:

[0040] The query returns data for currently active blogs. It has two optional query criteria: title and author.

[0041]

[0042] As you can see, the XML element `if` contains all the dynamic condition matching conditions and the corresponding SQL filter statements. If you have more complex query filtering requirements, you need to write them out in detail one by one in the SQL query template file.

[0043] In the Java web backend Hibernate framework, the most common solution is the HQL Criteria query technique. The HQL Criteria technique requires converting each query condition into a Criteria statement, as shown in the example below:

[0044] Retrieve all Student objects whose age is 22 or null.

[0045] Criteria cr=session.createCriteria(Student.class);

[0046] cr.add(Restrictions.eq(“age”,new Integer(22));

[0047] cr.add(Restrictions.isNull(“age”));

[0048] As you can see, each query condition requires writing one or more Java statements by hand.

[0049] Both of the above-mentioned general technical solutions require manual maintenance of SQL query templates or Java Criteria statements, which is labor-intensive, meticulous, error-prone, and not universally applicable.

[0050] To address the aforementioned problems, the inventors proposed a method for querying data using transformed SQL statements.

[0051] This disclosure provides a method for querying data using transformed SQL statements, such as... Figure 1 As shown, the method includes steps S100-S130;

[0052] S100. Obtain the basic SQL statement including query filtering conditions and convert it into an SQL abstract syntax tree;

[0053] S110. Traverse the SQL abstract syntax tree and determine whether the preset conditions are met;

[0054] S120. If the preset conditions are met, the modified SQL abstract syntax tree is obtained according to the predefined rules, and the modified SQL abstract syntax tree is output as the converted SQL statement.

[0055] S130. Use the transformed SQL statement to query the database for data that meets the query filtering conditions.

[0056] In this embodiment, the basic SQL statement can be a general query statement, and there are no specific restrictions on the format of the statement.

[0057] In one exemplary embodiment, after obtaining the basic SQL statement including query filtering conditions, the method further includes: extracting the four elements of SQL variable replacement based on the query filtering conditions; wherein the four elements are SQL variable name, SQL variable data type, query filtering operation, and constant value. In this embodiment, the dynamically passed query filtering conditions can be organized into a list of four-element tuples, where each tuple contains the four elements: SQL variable name (field), SQL variable data type (format), query filtering operation (op), and constant value (value).

[0058] In one exemplary embodiment, traversing the SQL abstract syntax tree and determining whether a preset condition is met includes: first, traversing the SQL abstract syntax tree; second, determining whether the SQL variable names in the SQL abstract syntax tree match the SQL variable names of the four-element tuples corresponding to the query filter conditions; third, if they match, determining that the preset condition is met; fourth, if they do not match, the method flow ends.

[0059] In one exemplary embodiment, determining whether the SQL variable name in the SQL abstract syntax tree matches the SQL variable name of the four-element tuple corresponding to the query filter condition includes:

[0060] Compare the SQL variable names in the SQL abstract syntax tree with the SQL variable name strings of the four-element tuple;

[0061] If the strings of two variable names are equal, then the two variable names are considered to match.

[0062] In S120, if the preset conditions are met, the modified SQL abstract syntax tree is obtained according to the predefined rules, and the modified SQL abstract syntax tree is output as the converted SQL statement.

[0063] In one exemplary embodiment, the predefined rules include: converting the SQL variable into an SQL constant expression corresponding to the SQL constant value of the four-element tuple, based on the SQL variable data type of the four-element tuple. In this embodiment, if the SQL variable (SQL VariantRef) exists elsewhere, such as as a function parameter, then the SQL variable is replaced with the SQL constant expression corresponding to the constant value of the four-element tuple, based on the SQL variable data type (format) of the four-element tuple. In this embodiment, there is another case: if the SQL variable (SQL VariantRef) is a subexpression of a SQL binary expression (SQL BinaryOpExpr), then: based on the SQL variable data type (format) of the four-element tuple, the constant value of the tuple is converted into a suitable SQL constant expression, replacing the SQL variable.

[0064] In one exemplary embodiment, the predefined rule further includes: if the SQL variable is a sub-expression in an SQL binary expression, then modifying the binary operator of the SQL binary expression to the binary operator corresponding to the query filtering operation in the four-element tuple. In this embodiment, if the SQL variable (SQL VariantRef) is a sub-expression in a SQL binary expression (SQL BinaryOpExpr), then: based on the data type (format) of the SQL variable in the four-element tuple, converting the constant value of the tuple to a suitable SQL constant expression and replacing the SQL variable; simultaneously, dynamically modifying the binary operator (SQLBinaryOperator) of the SQL binary expression based on the value of the query filtering operation (op) in the four-element tuple.

[0065] For example: The basic SQL query statement is:

[0066] SELECT id,name FROM user WHERE(ISNULL(:userId)OR id=:userId)

[0067] The query filter condition quadruple is:

[0068] SQL variable name SQL variable format Query filtering operations constant value userId integer greaterThan 1

[0069] Transformed SQL statement:

[0070] SELECT id,name FROM user WHERE(ISNULL(1)OR id>1)

[0071] As can be seen, in the transformed SQL statement, the function parameters in the basic SQL statement were correctly converted, and the original binary expression id =:userId has been converted into the new expression id > 1.

[0072] In one exemplary embodiment, modifying the binary operators of the SQL binary expression to the binary operators corresponding to the query filtering operations in the four-element tuple includes: replacing the query filtering operations of the four-element tuple with binary operators according to the value of the query filtering operations and a preset correspondence in the mapping table of SQL binary operators; wherein, the preset correspondence includes the binary operators corresponding to the values ​​of different query filtering operations.

[0073] In one exemplary embodiment, the values ​​of query filtering operations in the mapping table of SQL binary operators correspond one-to-one with the binary operators. The mapping table is shown in Table 1 below:

[0074] Table 1. Mapping Relationship of SQL Binary Operators

[0075] Query filtering operation (op) SQL Binary Operators equals ＝ not Equal <> like LIKE not like NOT LIKE greater Than > greater than Equal >＝ lesser Than < lesser Than Or Equal <＝

[0076] In one exemplary embodiment, for certain types of databases, some specific query filtering operations (ops) require simultaneous modification of the left and right sub-expressions of the SQL binary expression to represent more complex operations. In this embodiment, simultaneously modifying the left and right sub-expressions of the SQL binary expression means replacing the left and right sub-expressions (the SQL variables to be replaced and their corresponding SQL fields) in the original SQL expression with nested SQL expressions to achieve more complex logic. For example, in SQL statements of a MySQL database,

[0077] Original expression: Name! = :Name;

[0078] This will be replaced with: Name! = 'abc' OR ISNULL(Name) = 1;

[0079] From the above substitutions, we can see that the left subexpression `Name` of the original expression is replaced with `Name! = 'abc'`; the right subexpression `:Name` is replaced with `ISNULL(Name) = 1`; and the SQL operator `! =` is replaced with `OR`. The conversion rules in this embodiment depend on the actual database. Different databases have slightly different conversion rules, but the common point is that the original expression needs to be converted into a nested, multi-level expression.

[0080] Example 1

[0081] For SQL query statement transformation, the equivalent transformation of the NOTEqual operation in MySQL database is as follows:

[0082] In this example, since the SQL inequality comparison operator <> cannot match columns with NULL values ​​in MySQL, but can in Oracle, the rules in this example are used for equivalent conversion to ensure consistent query filtering across databases.

[0083] The basic SQL query statement is:

[0084] SELECT id,name FROM user WHERE(ISNULL(:userName)OR name=:userName)

[0085] Query filter condition quadruple:

[0086] SQL variable name SQL variable format Query filtering operations constant value userName string notEqual a

[0087] The converted statement is:

[0088] SELECT id,name FROM user WHERE(ISNULL(1)OR(name<>'a'OR ISNULL(name)=1))

[0089] As can be seen, in the transformed SQL statement, the binary expression name = :userName in the basic SQL statement is converted into a new expression according to the conversion rules: name<>'a' OR ISNULL(name) = 1.

[0090] This embodiment provides a method for generating a final SQL statement by modifying SQL variable placeholders and nearby SQL binary expressions in a basic SQL query statement and dynamically variable query filtering conditions. In other words, only a simple basic SQL statement needs to be prepared, eliminating the need for the meticulous and tedious preparation of various IF condition paragraphs by operating on each field individually in the original SQL template. Furthermore, based on the SQL transformation rules, cross-database queries can be easily achieved, making the operation simple and convenient.

[0091] This application also provides an apparatus for querying data using transformed SQL statements, such as... Figure 2 As shown, the device includes a memory 210 and a processor 220; the memory is used to store a program for querying data using transformed SQL statements, and the processor is used to read and execute the program for querying data using transformed SQL statements, and to execute the method described in any of the above embodiments.

[0092] This application also provides a computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, perform the operations in any of the above embodiments.

[0093] It will be understood by those skilled in the art that all or some of the steps, systems, or apparatuses disclosed above, and their functional modules / units, can be implemented as software, firmware, hardware, or suitable combinations thereof. In hardware implementations, the division between functional modules / units mentioned above does not necessarily correspond to the division of physical components; for example, a physical component may have multiple functions, or a function or step may be performed collaboratively by several physical components. Some or all components may be implemented as software executed by a processor, such as a digital signal processor or microprocessor, or as hardware, or as an integrated circuit, such as an application-specific integrated circuit (ASIC). Such software may be distributed on a computer-readable medium, which may include computer storage media (or non-transitory media) and communication media (or transient media). As is known to those skilled in the art, the term computer storage media includes volatile and non-volatile, removable and non-removable media implemented in any method or technology for storing information (such as computer-readable instructions, data structures, program modules, or other data). Computer storage media include, but are not limited to, RAM, ROM, EEPROM, flash memory or other memory technologies, CD-ROM, digital versatile disc (DVD) or other optical disc storage, magnetic cartridges, magnetic tape, disk storage or other magnetic storage devices, or any other medium that can be used to store desired information and can be accessed by a computer. Furthermore, it is well known to those skilled in the art that communication media typically contain computer-readable instructions, data structures, program modules, or other data in modulated data signals such as carrier waves or other transmission mechanisms, and may include any information delivery medium.

Claims

1. A method for querying data using transformed SQL statements, characterized in that, The methods include: Obtain the basic SQL statement, including query filtering conditions, and convert it into an SQL abstract syntax tree; Traverse the SQL abstract syntax tree and determine whether the preset conditions are met; If the preset conditions are met, the modified SQL abstract syntax tree is obtained according to the predefined rules, and the modified SQL abstract syntax tree is output as the converted SQL statement. Use the transformed SQL statement to query the database for data that meets the query filtering conditions; The process of obtaining the basic SQL statement including query filtering conditions further includes: Based on the query filtering conditions, four elements for SQL variable replacement are extracted, namely, SQL variable name, SQL variable data type, query filtering operation, and constant value. Furthermore, the predefined rules include: converting SQL variables into SQL constant expressions corresponding to the SQL constant values ​​of the four-element tuples, based on the SQL variable data type of the four-element tuples.

2. The method for querying data using transformed SQL statements according to claim 1, characterized in that, The step of traversing the SQL abstract syntax tree and determining whether preset conditions are met includes: Traverse the SQL abstract syntax tree; Determine whether the SQL variable name in the SQL abstract syntax tree matches the SQL variable name of the four-element tuple corresponding to the query filter condition; If a match is found, then the preset conditions are met.

3. The method for querying data using transformed SQL statements according to claim 2, characterized in that, The step of determining whether the SQL variable name in the SQL abstract syntax tree matches the SQL variable name of the four-element tuple corresponding to the query filter condition includes: Compare the SQL variable names in the SQL abstract syntax tree with the SQL variable name strings of the four-element tuple; If the strings of two variable names are equal, then the two variable names are considered to match.

4. The method for querying data using transformed SQL statements according to claim 1, characterized in that, The predefined rules also include: If an SQL variable is a subexpression in an SQL binary expression, then the binary operators in the SQL binary expression are modified to the binary operators corresponding to the query filtering operations in the four-element tuple.

5. The method for querying data using transformed SQL statements according to claim 4, characterized in that, The modification of the binary operators in the SQL binary expression to the binary operators corresponding to the query filtering operations in the four-element tuple includes: Based on the value of the query filtering operation and the preset correspondence in the mapping table of SQL binary operators, the query filtering operation of the four-element tuple is replaced with a binary operator; wherein, the preset correspondence includes the binary operators corresponding to the values ​​of different query filtering operations.

6. The method for querying data using transformed SQL statements according to claim 5, characterized in that, The value of the query filtering operation in the mapping table of the SQL binary operators corresponds one-to-one with the binary operator.

7. An apparatus for querying data using a transformed SQL statement, the apparatus comprising: A memory and a processor; characterized in that the memory is used to store a program for querying data using a transformed SQL statement, and the processor is used to read and execute the program for querying data using a transformed SQL statement, and to perform the method according to any one of claims 1-6.

8. A computer-readable storage medium storing computer-executable instructions that, when executed by a processor, perform the operations described in any one of claims 1-6.

Images