CN116361328A

CN116361328A - Method and device for querying data by using converted SQL statement

Info

Publication number: CN116361328A
Application number: CN202310343237.9A
Authority: CN
Inventors: 朱国峰
Original assignee: Beijing Lanyun Technologies Co ltd
Current assignee: Beijing Lanyun Technologies Co ltd
Priority date: 2023-03-31
Filing date: 2023-03-31
Publication date: 2023-06-30

Abstract

A method and apparatus for querying data using a converted SQL statement, the method comprising: acquiring a basic SQL sentence comprising query filtering conditions, and converting the basic SQL sentence into an SQL abstract syntax tree; traversing the SQL abstract syntax tree, and judging whether a preset condition is met; if the preset condition is met, a modified SQL abstract syntax tree is obtained according to a predefined rule, and the modified SQL abstract syntax tree is output as a converted SQL sentence; and querying the data meeting the query filtering condition in the database by using the converted SQL sentence.

Description

Method and device for querying data by using converted SQL statement

Technical Field

The present invention relates to the field of databases, and in particular, to a method and apparatus for querying data using a converted SQL statement.

Background

In a WEB application, it is very common to dynamically query a database and return data according to various query conditions input by a user.

Under the framework of mybatis technology in the Java Web back-end development field, a general solution to the requirement is to adopt a dynamic SQL template; but if there are more and more complex requirements for query filtering conditions, the requirements need to be written in the SQL query template file in detail one by one.

Under the Java WEB back-end Hibernate technical framework, a general solution is an HQL criterion query technology. The HQL criterion technique requires one or more Java statements to be handwritten for each query condition to be converted to criterion statements.

The two general technical solutions need to manually maintain SQL query templates or Java criterion sentences, and have the advantages of large workload, elaboration, complexity, error and incapability of being general.

Disclosure of Invention

The application provides a method and a device for querying data by using a converted SQL statement, which can be modified according to a basic SQL query statement and a dynamically variable query filtering condition, and query data is performed in different databases by using the modified SQL statement.

The application provides a method for querying data by using a converted SQL statement, which comprises the following steps:

acquiring a basic SQL sentence comprising query filtering conditions, and converting the basic SQL sentence into an SQL abstract syntax tree;

traversing the SQL abstract syntax tree, and judging whether a preset condition is met;

if the preset condition is met, a modified SQL abstract syntax tree is obtained according to a predefined rule, and the modified SQL abstract syntax tree is output as a converted SQL sentence;

and querying the data meeting the query filtering condition in the database by using the converted SQL sentence.

In an exemplary embodiment, after the obtaining the basic SQL statement including the query filtering condition, the method further includes:

extracting four elements replaced by SQL variables according to the query filtering conditions;

the four elements are SQL variable names, SQL variable data types, query filtering operations and constant values.

In an exemplary embodiment, the traversing the SQL abstract syntax tree and determining whether a preset condition is met comprises:

traversing the SQL abstract syntax tree;

judging whether SQL variable names in the SQL abstract syntax tree are matched with SQL variable names of four-element tuples corresponding to the query filtering conditions or not;

if so, determining that the preset condition is met.

In an exemplary embodiment, the 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 filtering condition includes:

comparing the SQL variable names in the SQL abstract syntax tree with the SQL variable name character strings of the four-element tuples;

and if the character strings of the two variable names are equal, determining that the two variable names are matched.

In an exemplary embodiment, the predefined rule includes:

and according to the SQL variable data type of the four-element tuple, converting the SQL variable into an SQL constant expression corresponding to the SQL constant value of the four-element tuple.

In an exemplary embodiment, the predefined rule further includes:

if the SQL variable is a sub-expression in the SQL binary expression, modifying the binary operator of the SQL binary expression into a binary operator corresponding to the query filtering operation in the four-element tuple.

In an exemplary embodiment, the modifying the binary operator of the SQL binary expression into the binary operator corresponding to the query filtering operation in the four-element tuple comprises:

according to the value of the query filtering operation and the preset corresponding relation in the mapping relation table of the SQL binary operator, replacing the query filtering operation of the four-element tuple with the binary operator; the preset corresponding relation comprises binary operators corresponding to different values of query filtering operation.

In an exemplary embodiment, the values of the query filtering operations in the mapping relation table of the SQL binary operators are in one-to-one correspondence with the binary operators.

In order to solve the above problems, the present invention also provides an apparatus for querying data using a converted SQL statement, the apparatus comprising: a memory and a processor; the method is characterized in that the memory is used for storing a program for inquiring data by using a converted SQL statement, and the processor is used for reading and executing the program for inquiring data by using the converted SQL statement and executing the method in any of the above embodiments.

In order to solve the above-described 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 the method of any one of the above-described embodiments.

Compared with the related art, the application provides a method and a device for querying data by using a converted SQL statement, wherein the method comprises the following steps: acquiring a basic SQL sentence comprising query filtering conditions, and converting the basic SQL sentence into an SQL abstract syntax tree; traversing the SQL abstract syntax tree, and judging whether a preset condition is met; if the preset condition is met, a modified SQL abstract syntax tree is obtained according to a predefined rule, and the modified SQL abstract syntax tree is output as a converted SQL sentence; and querying the data meeting the query filtering condition in the database by using the converted SQL sentence. The method and the device can be modified according to basic SQL query sentences and dynamically variable query filtering conditions, and query data is carried out in different databases by using the modified SQL sentences.

Additional features and advantages of the application will be set forth in the description which follows, and in part will be obvious from the description, or may be learned by practice of the application. Other advantages of the present application may be realized and attained by the structure particularly pointed out in the written description and drawings.

Drawings

The accompanying drawings are included to provide an understanding of the technical aspects of the present application, and are incorporated in and constitute a part of this specification, illustrate the technical aspects of the present application and together with the examples of the present application, and not constitute a limitation of the technical aspects of the present application.

FIG. 1 is a flow chart of a method for querying data using a converted SQL statement in an embodiment of the application;

fig. 2 is a schematic diagram of an apparatus for querying data using a converted SQL statement according to an embodiment of the present application.

Detailed Description

The present application describes a number of embodiments, but the description is illustrative and not limiting and it will be apparent to those of ordinary skill 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 possible. Any feature or element of any embodiment may be used in combination with or in place of any other feature or element of any other embodiment unless specifically limited.

The present application includes and contemplates combinations of features and elements known to those of ordinary skill in the art. The embodiments, features and elements of the present disclosure may also be combined with any conventional features or elements to form a unique inventive arrangement as defined in the claims. Any feature or element of any embodiment may also be combined with features or elements from other inventive arrangements to form another unique inventive arrangement as defined in the claims. Thus, it should be understood that any of the features shown and/or discussed in this application may be implemented alone or in any suitable combination. Accordingly, the embodiments are not to be restricted except in light of the attached claims and their equivalents. Further, various modifications and changes may be made within the scope of the appended claims.

Furthermore, in describing representative embodiments, the specification may have presented the method and/or process as a particular sequence of steps. However, to the extent that the method or process does not rely on the particular order of steps set forth herein, the method or process should not be limited to the particular sequence of steps described. Other sequences of steps are possible as will be appreciated by those of ordinary skill in the art. Accordingly, the particular order of the steps set forth in the specification should not be construed as limitations on the claims. Furthermore, the claims directed to the method and/or process should not be limited to the performance of their steps in the order written, and one skilled in the art can readily appreciate that the sequences may be varied and still remain within the spirit and scope of the embodiments of the present application.

In some technologies, under the framework of mybatis technology in the Java Web backend development domain, a common solution to this need is to use dynamic SQL templates, for example:

the query returns BLOG data for the current active state, which has two optional query conditions title, autther.

It can be seen that the XML element if contains all dynamic condition matching conditions and corresponding SQL filter statements. If there are more and more complex query filtering condition requirements, the requirements are written in the SQL query template file in detail one by one.

Under the Java WEB back-end Hibernate technical framework, the most common solution is the HQL criterion query technique.

All Student objects whose age, is equal to 22 or whose age is null (null) are queried.

Criteria cr＝session.createCriteria(Student.class)；

cr.add(Restrictions.eq(“age”,new Integer(22))；

cr.add(Restrictions.isNull(“age”))；

It can be seen that each query condition requires one or more Java statements to be handwritten.

The inventor proposes a method for querying data using a converted SQL statement aiming at the problems.

The embodiment of the disclosure provides a method for querying data by using a converted SQL statement, as shown in FIG. 1, the method comprises steps S100-S130;

s100, acquiring a basic SQL sentence comprising query filtering conditions, and converting the basic SQL sentence into an SQL abstract syntax tree;

s110, traversing the SQL abstract syntax tree, and judging whether a preset condition is met or not;

s120, if the preset condition is met, a modified SQL abstract syntax tree is obtained according to a predefined rule, and the modified SQL abstract syntax tree is output as a converted SQL sentence;

s130, querying the data meeting the query filtering condition in the database by utilizing the converted SQL statement.

In this embodiment, the basic SQL statement may be a general query statement, and the writing format of the statement is not particularly limited.

In an exemplary embodiment, after the obtaining the basic SQL statement including the query filtering condition, the method further includes: extracting four elements replaced by SQL variables according to the query filtering conditions; the four elements are SQL variable names, SQL variable data types, query filtering operations and constant values. In this embodiment, the query filtering condition dynamically transmitted can be organized into a list of four-element tuples, where a single tuple contains four elements, namely, SQL variable name (field), SQL variable data type (format), query filtering operation (op), and constant value (value).

In an exemplary embodiment, the traversing the SQL abstract syntax tree and determining whether a preset condition is met comprises: traversing the SQL abstract syntax tree; judging whether the SQL variable names in the SQL abstract syntax tree are matched with the SQL variable names of the four-element tuples corresponding to the query filtering conditions or not; and thirdly, if the matching is carried out, determining that the preset condition is met. And step four, if the two are not matched, ending the flow of the method.

In an exemplary embodiment, the 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 filtering condition includes:

In S120, if the preset condition is satisfied, a modified SQL abstract syntax tree is obtained according to a predefined rule, and the modified SQL abstract syntax tree is output as a converted SQL statement.

In an exemplary embodiment, the predefined rule includes: and according to the SQL variable data type of the four-element tuple, converting the SQL variable into an SQL constant expression corresponding to the SQL constant value of the four-element tuple. In this embodiment, if the SQL variable (SQL VariantRef) exists at another location, for example, exists as a function parameter, the SQL variable is replaced with the SQL constant expression corresponding to the constant value of the four-element tuple according to the SQL variable data type (format) of the four-element tuple. In this embodiment there is also the case that if the SQL variable (SQL Variant Ref) is a sub-expression in a certain SQL binary expression (SQL BinaryOpExpr), the constant values of the tuples are converted into the appropriate SQL constant expression according to the SQL variable data type (format) of the four-element tuple, replacing the SQL variable.

In an exemplary embodiment, the predefined rule further includes: if the SQL variable is a sub-expression in the SQL binary expression, modifying the binary operator of the SQL binary expression into a binary operator corresponding to the query filtering operation in the four-element tuple. In the embodiment, if the SQL variable (SQL Variant Ref) is a sub-expression in a certain SQL binary expression (SQL BinaryOpExpr), according to the SQL variable data type (format) of the four-element tuple, converting the constant value of the tuple into a proper SQL constant expression to replace the SQL variable; while dynamically modifying binary operators (SQL BinaryOperator) of the SQL binary expression according to values of query filtering operations (ops) of the four-element tuples.

For example: the SQL basic query statement is:

SELECT id,name FROM user WHERE(ISNULL(:userId)OR id＝:userId)

the query filtering condition quadruple is as follows:

SQL variable name	SQL variable format	Query filtering operations	Constant value
				userId	integer	greaterThan	1

Converted SQL statement:

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

it can be seen that in the converted SQL statement, the function parameters in the underlying SQL statement are correctly converted, and the original binary expression id=: userId has been converted to a new expression id >1.

In an exemplary embodiment, the modifying the binary operator of the SQL binary expression into the binary operator corresponding to the query filtering operation in the four-element tuple comprises: according to the value of the query filtering operation and the preset corresponding relation in the mapping relation table of the SQL binary operator, replacing the query filtering operation of the four-element tuple with the binary operator; the preset corresponding relation comprises binary operators corresponding to different values of query filtering operation.

In an exemplary embodiment, the values of the query filtering operations in the mapping relation table of the SQL binary operators are in one-to-one correspondence with the binary operators. Wherein the mapping table is shown in table 1 below:

table 1 mapping relationship table of SQL binary operators

Query filtering operations (op)	SQL binary operator (SQL Binary Operator)
		equals	＝
not Equal	<>
		like	LIKE
not Like	NOT LIKE
		greater Than	>
greater Than Or Equal	>＝
		lesser Than	<
lesser Than Or Equal	<＝

In an exemplary embodiment, for some particular types of databases, some special query filtering operations (ops) require modifying both the left and right sub-expressions of the SQL binary expression to represent more complex operations. In this embodiment, modifying the left and right sub-expressions of the SQL binary expression simultaneously replaces the left and right sub-expressions (the SQL variable to be replaced and the corresponding SQL field) in the original SQL expression with nested SQL expressions to achieve more complex logic. For example: in the SQL statement of the MySQL database,

the original expression: name-! Name;

will be replaced with: name-! = 'abc' OR ISNULL (Name) =1;

from the above substitution, the left sub-expression Name of the original expression is replaced with Name-! = 'abc'; right sub-expression Name is replaced with ISNULL (Name) =1; SQL operator-! =replaced with OR. The conversion rules in this embodiment are dependent on the actual database, and the conversion rules of different databases are not identical, but have in common that the original expression needs to be converted into a nested multi-level expression.

Example one

For SQL query statement transformations, a specific example of an equivalent transformation of a notEquat operation under the mysql database is as follows:

in this example, SQL is not equal to the comparator < > cannot match a column with a value of NULL in the mysql database, but ORACLE can, in order to ensure that the query filtering effect across databases is consistent, therefore, the rule in this embodiment needs to be used for equivalent conversion;

the SQL basic query statement is:

SELECT id,name FROM user WHERE(ISNULL(:userName)OR name＝:userName)

query filter condition quadruples:

SQL variable name	SQL variable format	Query filtering operations	Constant value
				userName	string	notEqual	a

The converted sentence is:

SELECT id,name FROM user WHERE(ISNULL(1)OR(name<>'a'OR ISNULL(name)＝1))

it can be seen that, in the converted SQL statement, the binary expression name=: userName in the basic SQL statement is converted into a new expression according to the conversion rule: name < 'a' OR ISNULL (name) =1.

In this embodiment, a method for modifying an SQL variable placeholder and a nearby SQL binary expression in a basic SQL statement to generate a final SQL statement according to the basic SQL query statement and a dynamically variable query filtering condition is provided in this embodiment. That is, only a simple basic SQL statement is needed to be prepared, and the detailed and complicated field-by-field operation preparation of various IF condition paragraphs in the original SQL template is omitted; and the cross-database query can be easily realized based on SQL conversion rules, and the operation is simple and convenient.

The present application also provides an apparatus for querying data using a converted SQL statement, as shown in fig. 2, the apparatus comprising: a memory 210 and a processor 220; the memory is used for storing a program for inquiring data by using the converted SQL statement, and the processor is used for reading and executing the program for inquiring data by using the converted SQL statement and executing the method in any of the above embodiments.

The present application also provides a computer-readable storage medium storing computer-executable instructions that, when executed by a processor, perform the operations of the method of any of the above embodiments.

Those of ordinary skill in the art will appreciate that all or some of the steps, systems, functional modules/units in the apparatus, and methods disclosed above may be implemented as software, firmware, hardware, and suitable combinations thereof. In a hardware implementation, the division between the functional modules/units mentioned in the above description does not necessarily correspond to the division of physical components; for example, one physical component may have multiple functions, or one function or step may be performed cooperatively by several physical components. Some or all of the 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. Such software may be distributed on computer readable media, which may include computer storage media (or non-transitory media) and communication media (or transitory media). The term computer storage media includes both volatile and nonvolatile, removable and non-removable media implemented in any method or technology for storage of information such as computer readable instructions, data structures, program modules or other data, as known to those skilled in the art. Computer storage media includes, but is not limited to, RAM, ROM, EEPROM, flash memory or other memory technology, CD-ROM, digital Versatile Disks (DVD) or other optical disk storage, magnetic cassettes, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other medium which can be used to store the desired information and which can be accessed by a computer. Furthermore, as is well known to those of ordinary skill in the art, communication media typically embodies computer readable instructions, data structures, program modules or other data in a modulated data signal such as a carrier wave or other transport mechanism and includes any information delivery media.

Claims

1. A method of querying data using a converted SQL statement, the method comprising:

2. The method of claim 1, wherein after retrieving the basic SQL statement including the query filtering conditions, the method further comprises:

3. The method of claim 2, wherein traversing the SQL abstract syntax tree and determining whether a preset condition is met comprises:

traversing the SQL abstract syntax tree;

if so, determining that the preset condition is met.

4. The method for querying data using a converted SQL statement according to claim 3, wherein the 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 filtering condition comprises:

5. A method of querying data using a converted SQL statement as claimed in claim 3, wherein the predefined rules comprise:

6. The method of claim 5, wherein the predefined rules further comprise:

7. The method of claim 6, wherein modifying the binary operators of the SQL binary expression into binary operators corresponding to the query filtering operation in the four-element tuple comprises:

8. The method of claim 7, wherein the values of the query filtering operations in the mapping table of the SQL binary operators are in one-to-one correspondence with the binary operators.

9. An apparatus for querying data using a converted SQL statement, the apparatus comprising: a memory and a processor; wherein the memory is used for storing a program for inquiring data by using a converted SQL sentence, and the processor is used for reading and executing the program for inquiring data by using the converted SQL sentence and executing the method of any one of claims 1-8.

10. A computer readable storage medium storing computer executable instructions which when executed by a processor perform operations in the method of any of claims 1-8.