SQL processing method, SQL processing system

Abstract

The present invention provides an SQL processing method and an SQL processing system, the method comprising: parsing the data type of SQL input parameters based on Druid components; modifying the input parameters to a precompiled format and retaining an ordered parameter list according to the parameter transmission, converting the syntax expression of the changed Druid component into SQL; and executing SQL through the precompiled mode. The present invention can parse the configured SQL to obtain input parameters, facilitate dynamic parameter transmission by users, and realize SQL execution under different conditions such as parameters and filtering conditions, and use Druid components to implement SQL parsing, which is more flexible in traversing the SQL syntax tree, can do special processing for the required SQL expression, and is more convenient to implement.

Description

Technical Field

[0001] The present invention relates to the field of big data technology and data processing technology, and in particular to a SQL (Structured Query Language) processing method and a SQL processing system. Background Art

[0002] The current mainstream SQL parsing tools are Calcite (a dynamic data management framework) and regular expressions.

[0003] The syntax tree parsed by Calcite requires a custom method to traverse the entire tree, which is not convenient and cannot modify some attributes in SQL in one traversal of the syntax tree. The regular expression method cannot accurately parse the required parameters based on the contextual semantics of SQL, and the implementation and subsequent maintenance are relatively difficult. Summary of the invention

[0004] To solve the above technical problems, the first objective of the present invention is to provide a SQL processing method.

[0005] The second objective of the present invention is to provide an SQL processing system.

[0006] The technical solution adopted by the present invention is as follows:

[0007] An embodiment of the first aspect of the present invention proposes an SQL processing method, comprising the following steps: parsing the data type of an SQL input parameter based on a Druid (a data storage designed for high-performance slicing and online analytical processing on large data sets) component; modifying the input parameter to a precompiled format based on the passed parameter and retaining an ordered passed parameter list, converting the changed grammatical expression of the Druid component into SQL; and executing SQL through the precompiled mode.

[0008] The SQL processing method proposed in the present invention may also have the following additional technical features:

[0009] According to one embodiment of the present invention, parsing SQL input parameters based on Druid components specifically includes: inheriting SQLASTVisitorAdapter in the Druid component to implement the TablesVisitor class, traversing and obtaining table information in the SQL from expression; querying the field information of the table through JDBC (Java Database Connectivity); traversing the expressions in the SQL where syntax through the WhereItemsVisitor class to obtain the input parameters that conform to the input parameter format; and judging the data type of the input parameter through the field corresponding to the input parameter.

[0010] According to one embodiment of the present invention, the input parameter is modified to a precompiled format and an ordered parameter list is retained according to the parameter transmission, and the grammatical expression of the Druid component is converted to SQL, including: traversing the where filter condition expression; judging whether the expression conforms to the input parameter format, if the input parameter exists in the parameter transmission, it is replaced with a precompiled format, and the parameter transmission is recorded in order; implementing the WhereSpaceHolderVisitor class, traversing the where filter condition expression, obtaining the filter condition that still holds the input parameter; replacing the filter condition that holds the input parameter with an empty string through a regular expression; cleaning the where statement whose filter condition is empty after the where keyword; converting the changed Druid grammatical expression into SQL.

[0011] According to one embodiment of the present invention, executing SQL through a precompiled mode includes: converting the parameter to a java (object-oriented programming language) data type according to the data type of the SQL input parameter; and obtaining the final result through JDBC supplemented by the parameter passing of the java data type according to the precompiled mode SQL.

[0012] An embodiment of the second aspect of the present invention proposes an SQL processing system, including: a parsing module, which is used to parse the data type of SQL input parameters based on the Druid component; a conversion module, which is used to modify the input parameters into a precompiled format according to the passed parameters and retain an ordered passed parameter list, and convert the changed grammatical expression of the Druid component into SQL; an execution module, which is used to execute SQL through a precompiled mode.

[0013] The SQL processing system proposed in the present invention may also have the following additional technical features:

[0014] According to one embodiment of the present invention, the parsing module is specifically used to: inherit the SQLASTVisitorAdapter in the Druid component to implement the TablesVisitor class, traverse and obtain the table information in the SQL from expression; query the field information of the table through JDBC; traverse the expression in the SQL where syntax through the WhereItemsVisitor class to obtain the input parameter that conforms to the input parameter format; and determine the data type of the input parameter through the field corresponding to the input parameter.

[0015] According to one embodiment of the present invention, the conversion module is specifically used to: traverse the where filter condition expression; determine whether the expression conforms to the input parameter format, if the input parameter exists in the passed parameter, replace it with a precompiled format, and record the passed parameter in sequence; implement the WhereSpaceHolderVisitor class, traverse the where filter condition expression, and obtain the filter condition that still holds the input parameter; replace the filter condition that holds the input parameter with an empty string through a regular expression; clean up the where statement whose filter condition is empty after the where keyword; convert the changed Druid syntax expression into SQL.

[0016] According to one embodiment of the present invention, the execution module is specifically used to: convert the parameter to a java data type according to the data type of the SQL input parameter; and obtain the final result through JDBC supplemented by the parameter of the java data type according to the precompiled mode SQL.

[0017] Beneficial effects of the present invention:

[0018] The present invention can parse the configured SQL to obtain input parameters, which is convenient for users to dynamically pass parameters. It can realize SQL execution under different conditions such as parameters and filtering conditions, and uses Druid components to parse SQL. It is more flexible in traversing the SQL syntax tree, and can perform special processing on the required SQL expressions, making the implementation more convenient. BRIEF DESCRIPTION OF THE DRAWINGS

[0019] Figure 1 is a flowchart of a SQL processing method according to an embodiment of the present invention;

[0020] Figure 2 is a schematic diagram of the Visitor class inheritance relationship according to an embodiment of the present invention;

[0021] Figure 3 4 is a block diagram of a SQL processing system according to an embodiment of the present invention. DETAILED DESCRIPTION

[0022] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0023] Figure 1 FIG. 1 is a flow chart of a SQL processing method according to an embodiment of the present invention. Figure 1As shown, the method comprises the following steps:

[0024] S1, parses the data type of SQL input parameters based on the Druid component.

[0025] Further, according to an embodiment of the present invention, parsing SQL input parameters based on the Druid component specifically includes: inheriting the SQLASTVisitorAdapter in the Druid component to implement the TablesVisitor class, traversing and obtaining the table information in the SQLfrom expression; querying the field information of the table through JDBC; traversing the expression in the SQLwhere syntax through the WhereItemsVisitor class, and obtaining the input parameters that conform to the input parameter format; and judging the data type of the input parameter through the field corresponding to the input parameter.

[0026] Specifically, the purpose of parsing the data type of the SQL input parameter based on the Druid component is to parse out the input parameters whose parameter values ​​in the initial SQL filter conditions are in the input parameter format.

[0027] Visitor class inheritance relationship can be referred to Figure 2 As shown in the figure, SQLASTVisitorAdapter is an adapter for traversing the syntax tree provided by the druid component. TablesVisitor and whereItemsVisitor are inherited from SQLASTVisitorAdapter and implement syntax parsers for different syntax expressions. TablesVisitor is a syntax parser for parsing table names in SQL, WhereItemsVisitor is a syntax parser for parsing filter conditions in SQL, and WhereSpaceHolderVisitor is a syntax parser for parsing filter conditions in SQL and completing parameter replacement.

[0028] S2, based on the passed parameters, modifies the input parameters to the precompiled format and retains the ordered passed parameter list, converting the syntax expression of the changed Druid component into SQL.

[0029] Further, according to an embodiment of the present invention, the input parameters are modified into a precompiled format and an ordered parameter list is retained according to the parameter transmission, and the syntax expression of the Druid component is converted into SQL, including: traversing the where filter condition expression; judging whether the expression conforms to the input parameter format, if the input parameter exists in the parameter transmission, it is replaced with the precompiled format, and the parameter transmission is recorded in order; implementing the WhereSpaceHolderVisitor class, traversing the where filter condition expression, and obtaining the filter condition that still holds the input parameter; replacing the filter condition that holds the input parameter with an empty string through a regular expression; cleaning the where statement whose filter condition is empty after the where keyword; converting the changed Druid syntax expression into SQL.

[0030] That is to say, traverse the SQL expression and replace the input parameters with the precompiled format according to the passed parameters. When the passed parameters do not cover all the input parameters in the SQL, it is necessary to delete the unfilled filter items in the SQL.

[0031] S3, execute SQL through precompiled mode.

[0032] According to one embodiment of the present invention, executing SQL through a precompiled mode includes: converting the data type of the SQL input parameter into a java data type; and executing the precompiled mode SQL through JDBC supplemented by the java data type parameter to obtain the final result.

[0033] Specifically, according to the data type of the input parameter parsed in step S1, the parameter is converted into a specific java data type. The pre-compiled SQL processed in step S2 is executed through JDBC with the parameter of java data type to obtain the final result.

[0034] As can be seen from the above, the present invention can parse the configured SQL to obtain input parameters, which is convenient for users to dynamically pass parameters and can implement SQL execution under different conditions such as parameters and filter conditions. Moreover, the Druid component is used technically to implement SQL parsing, which is more flexible in traversing the SQL syntax tree and can perform special processing on the required SQL expressions, which is more convenient than other methods.

[0035] The present invention can be applied to related data service suites, where users configure data sources and SQL as API (Application Programming Interface) interfaces, and provide interface call services to dynamically splice SQL execution to obtain results based on user parameters.

[0036] In summary, according to the SQL processing method of the embodiment of the present invention, the data type of the SQL input parameter is parsed based on the Druid component, the input parameter is modified to a precompiled format according to the parameter transmission and the ordered parameter transmission list is retained, the syntax expression of the changed Druid component is converted into SQL, and SQL is executed through the precompiled mode. Therefore, the method can parse the configured SQL to obtain the input parameters, which is convenient for users to dynamically transmit parameters, and can realize SQL execution under different conditions such as parameters and filtering conditions, and use the Druid component to implement SQL parsing, which is more flexible in traversing the SQL syntax tree, and can do special processing for the required SQL expression, and the implementation method is more convenient.

[0037] Corresponding to the above SQL processing method, the present invention also provides an SQL processing system. Since the system embodiment of the present invention corresponds to the above method embodiment, details not disclosed in the system embodiment can be referred to the above method embodiment, and will not be repeated in the present invention.

[0038] Figure 3 is a block diagram of a SQL processing system according to an embodiment of the present invention. Figure 3 As shown, the device includes: a parsing module 1, a conversion module 2 and an execution module 3.

[0039] Among them, parsing module 1 is used to parse the data type of SQL input parameters based on the Druid component; conversion module 2 is used to modify the input parameters to a precompiled format according to the passed parameters and retain the ordered passed parameter list, and convert the changed syntax expression of the Druid component into SQL; execution module 3 is used to execute SQL through the precompiled mode.

[0040] According to one embodiment of the present invention, the parsing module 1 is specifically used for: inheriting the SQLASTVisitorAdapter in the Druid component to implement the TablesVisitor class, traversing and obtaining the table information in the SQL from expression; querying the field information of the table through JDBC; traversing the expressions in the SQL where syntax through the WhereItemsVisitor class, and obtaining the input parameters that conform to the input parameter format; and judging the data type of the input parameter through the field corresponding to the input parameter.

[0041] According to one embodiment of the present invention, conversion module 2 is specifically used for: traversing the where filter condition expression; determining whether the expression conforms to the input parameter format, replacing the input parameter with a precompiled format if it exists in the passed parameter, and recording the passed parameter in sequence; implementing the WhereSpaceHolderVisitor class, traversing the where filter condition expression, and obtaining the filter condition that still holds the input parameter; replacing the filter condition that holds the input parameter with an empty string through a regular expression; cleaning the where statement whose filter condition is empty after the where keyword; and converting the changed Druid syntax expression into SQL.

[0042] According to an embodiment of the present invention, the execution module 3 is specifically used to: convert the data type of the SQL input parameter into a java data type; and execute the final result through JDBC supplemented by the java data type parameter according to the precompiled mode SQL.

[0043] According to the SQL processing system of the embodiment of the present invention, the data type of the SQL input parameter is parsed by the parsing module based on the Druid component, the conversion module modifies the input parameter to a precompiled format according to the parameter transmission and retains the ordered parameter transmission list, the syntax expression of the changed Druid component is converted into SQL, and the execution module executes SQL through the precompiled mode. In this way, the configured SQL can be parsed to obtain the input parameters, which is convenient for users to dynamically transmit parameters, and SQL execution under different conditions such as parameters and filtering conditions can be realized. In addition, the use of the Druid component to implement SQL parsing is more flexible in traversing the SQL syntax tree, and can perform special processing on the required SQL expressions, and the implementation method is more convenient.

[0044] In the description of the present invention, the terms "first" and "second" are used for descriptive purposes only and should not be understood as indicating or implying relative importance or implicitly indicating the number of the indicated technical features. Therefore, the features defined as "first" and "second" may explicitly or implicitly include one or more of the features. "Multiple" means two or more, unless otherwise clearly and specifically defined.

[0045] In the description of this specification, the description with reference to the terms "one embodiment", "some embodiments", "examples", "specific examples", or "some examples" etc. means that the specific features, structures, materials or characteristics described in conjunction with the embodiment or example are included in at least one embodiment or example of the present invention. In this specification, the schematic representation of the above terms does not necessarily target the same embodiment or example. Moreover, the specific features, structures, materials or characteristics described can be combined in a suitable manner in any one or more embodiments or examples. In addition, the technical personnel in this field can combine and combine the different embodiments or examples described in this specification and the features of different embodiments or examples without contradiction. In the description of this specification, the description with reference to the terms "one embodiment", "some embodiments", "examples", "specific examples", or "some examples" etc. means that the specific features, structures, materials or characteristics described in conjunction with the embodiment or example are included in at least one embodiment or example of the present invention. In this specification, the schematic representation of the above terms does not necessarily target the same embodiment or example. Moreover, the specific features, structures, materials or characteristics described can be combined in a suitable manner in any one or more embodiments or examples. Furthermore, those skilled in the art may combine and associate different embodiments or examples and features of different embodiments or examples described in this specification without mutual contradiction.

[0046] Any process or method description in a flowchart or otherwise described herein may be understood to represent a module, segment or portion of code comprising one or more executable instructions for implementing the steps of a custom logical function or process, and the scope of the preferred embodiments of the present invention includes alternative implementations in which functions may not be performed in the order shown or discussed, including performing functions in a substantially simultaneous manner or in reverse order depending on the functions involved, which should be understood by technicians in the technical field to which the embodiments of the present invention belong.

[0047] The logic and / or steps represented in the flowchart or otherwise described herein, for example, can be considered as an ordered list of executable instructions for implementing logical functions, and can be embodied in any computer-readable medium for use by an instruction execution system, device or apparatus (such as a computer-based system, a system including a processor, or other system that can fetch instructions from an instruction execution system, device or apparatus and execute the instructions), or in combination with these instruction execution systems, devices or apparatuses. For the purpose of this specification, "computer-readable medium" can be any device that can contain, store, communicate, propagate or transmit a program for use by an instruction execution system, device or apparatus, or in combination with these instruction execution systems, devices or apparatuses. More specific examples of computer-readable media (a non-exhaustive list) include the following: an electrical connection with one or more wires (electronic device), a portable computer disk box (magnetic device), a random access memory (RAM), a read-only memory (ROM), an erasable and programmable read-only memory (EPROM or flash memory), a fiber optic device, and a portable compact disk read-only memory (CDROM). In addition, the computer-readable medium may even be paper or other suitable medium on which the program is printed, since the program may be obtained electronically, for example, by optically scanning the paper or other medium and then editing, interpreting or processing in other suitable ways if necessary, and then stored in a computer memory.

[0048] It should be understood that the various parts of the present invention can be implemented by hardware, software, firmware or a combination thereof. In the above-mentioned embodiments, a plurality of steps or methods can be implemented by software or firmware stored in a memory and executed by a suitable instruction execution system. For example, if implemented by hardware, as in another embodiment, it can be implemented by any one of the following technologies known in the art or their combination: a discrete logic circuit having a logic gate circuit for implementing a logic function for a data signal, a dedicated integrated circuit having a suitable combination of logic gate circuits, a programmable gate array (PGA), a field programmable gate array (FPGA), etc.

[0049] A person skilled in the art may understand that all or part of the steps in the method for implementing the above-mentioned embodiment may be completed by instructing related hardware through a program, and the program may be stored in a computer-readable storage medium, which, when executed, includes one or a combination of the steps of the method embodiment.

[0050] In addition, each functional unit in each embodiment of the present invention may be integrated into a processing module, or each unit may exist physically separately, or two or more units may be integrated into one module. The above-mentioned integrated module may be implemented in the form of hardware or in the form of a software functional module. If the integrated module is implemented in the form of a software functional module and sold or used as an independent product, it may also be stored in a computer-readable storage medium.

[0051] The storage medium mentioned above may be a read-only memory, a magnetic disk or an optical disk, etc. Although the embodiments of the present invention have been shown and described above, it can be understood that the above embodiments are exemplary and cannot be understood as limiting the present invention. A person of ordinary skill in the art may change, modify, replace and modify the above embodiments within the scope of the present invention.

[0052] Although embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that various changes, modifications, substitutions and variations may be made to the embodiments without departing from the principles and spirit of the present invention, and that the scope of the present invention is defined by the appended claims and their equivalents.

Claims

1. A SQL processing method, characterized in that: The following steps are involved: Parse the data type of SQL input parameters based on Druid components; Modify the input parameters to a precompiled format and retain the ordered parameter list according to the parameter transmission, and convert the syntax expression of the changed Druid component into SQL; Execute SQL in precompiled mode, where SQL input parameters are parsed based on Druid components, including: Inherit the SQLASTVisitorAdapter in the Druid component to implement the TablesVisitor class, traverse and obtain the table information in the SQLfrom expression; Query the field information of the table through JDBC; Through the WhereItemsVisitor class, traverse the expressions in the SQL where syntax and obtain the input parameters that conform to the input parameter format; Determine the data type of the input parameter by the field corresponding to the input parameter; modify the input parameter to a precompiled format and retain the ordered parameter list according to the parameter, and convert the syntax expression of the Druid component into SQL, including: Traverse the where filter condition expression; Determine whether the expression conforms to the input parameter format, if the input parameter exists in the passed parameter, replace it with the precompiled format, and record the passed parameter in order; Implement the WhereSpaceHolderVisitor class, traverse the where filter condition expression, and obtain the filter conditions that still hold the input parameters; Replace the filter condition holding the input parameter with an empty string through a regular expression; After cleaning the where keyword, filter the where statements with empty conditions; Convert the changed Druid syntax expression into SQL.

2. The SQL processing method according to claim 1, characterized in that: Execute SQL in precompiled mode, including: Convert the parameter to a Java data type according to the data type of the SQL input parameter; According to the pre-compiled SQL mode, the final result is obtained through JDBC with the help of Java data type parameter passing.

3. A SQL processing system, characterized in that: include: A parsing module, which is used to parse the data type of SQL input parameters based on the Druid component; A conversion module, which is used to modify the input parameters into a precompiled format and retain an ordered parameter list according to the passed parameters, and convert the syntax expression of the changed Druid component into SQL; An execution module is used to execute SQL in a precompiled mode; the parsing module is specifically used to: inherit the SQLASTVisitorAdapter in the Druid component to implement the TablesVisitor class, traverse and obtain the table information in the SQL from expression; Query the field information of the table through JDBC; Through the WhereItemsVisitor class, traverse the expressions in the SQL where syntax and obtain the input parameters that conform to the input parameter format; The data type of the input parameter is determined by the field corresponding to the input parameter; the conversion module is specifically used for: Traverse the where filter condition expression; Determine whether the expression conforms to the input parameter format, if the input parameter exists in the passed parameter, replace it with the precompiled format, and record the passed parameter in order; Implement the WhereSpaceHolderVisitor class, traverse the where filter condition expression, and obtain the filter conditions that still hold the input parameters; Replace the filter condition holding the input parameter with an empty string through a regular expression; After cleaning the where keyword, filter the where statements with empty conditions; Convert the changed Druid syntax expression into SQL.

4. The SQL processing system according to claim 3, characterized in that: The execution module is specifically used for: Convert the parameter to a Java data type according to the data type of the SQL input parameter; According to the pre-compiled SQL mode, the final result is obtained through JDBC with the help of Java data type parameter passing.

Images