CN114896273A

CN114896273A - SQL statement configuration method and equipment compatible with storage process and storage medium

Info

Publication number: CN114896273A
Application number: CN202210813757.7A
Authority: CN
Inventors: 封飞; 宋凡
Original assignee: Suzhou Qingying Feifan Software Technology Co ltd
Current assignee: Suzhou Qingying Feifan Software Technology Co ltd
Priority date: 2022-07-12
Filing date: 2022-07-12
Publication date: 2022-08-12
Anticipated expiration: 2042-07-12
Also published as: CN114896273B

Abstract

A SQL statement configuration method, equipment and storage medium compatible with storage procedures are provided, the method comprises: establishing a configuration file, wherein the configuration file comprises a plurality of SQL statement bodies written by preset writing rules, and statement body identifications are configured in the SQL statement bodies; analyzing the configuration file, analyzing the SQL statement bodies in the configuration file through a syntax analysis class, generating syntax tree objects corresponding to the SQL statement bodies, and further generating a syntax tree object library; and after calling a syntax tree object matched with the statement body identifier in a syntax tree object library through the statement body identifier, generating a preprocessed SQL statement which can be recognized by JDBC based on the input parameters. According to the SQL sentence configuration method compatible with the storage process, the problems of poor performance and difficult maintenance of the SQL sentences in the storage process are solved, and the performance of the SQL sentences in the use process is improved.

Description

SQL statement configuration method and equipment compatible with storage process and storage medium

Technical Field

The embodiment of the application relates to a method, equipment and a storage medium for configuring SQL (structured query language) statements compatible with a storage process.

Background

The stored procedure is stored in a large database system, a group of SQL statement sets for completing specific functions are stored in the database, the SQL statement sets are called again after first compiling without compiling again, and a user calls the stored procedure by specifying the name of the stored procedure and giving parameters (if the stored procedure has the parameters).

With the development of internet technology, the existing storage process has the following defects: 1. for complex dynamic SQL statements, the SQL statements are often dynamically spliced when a storage process is used, and the performance is poor and the SQL statements are difficult to maintain due to dynamic splicing. 2. The tight coupling of the application and the database has high maintenance difficulty; 3. the portability is poor.

Therefore, nowadays the object relational mapping framework is used in the development of application systems instead of the storage process. The object relational mapping framework is a technology for mapping structured data (table/record) into an object of a high-level language, and is characterized in that a database is read and written through entity classes, flow processing is carried out through for/while/if statements of JAVA, and structured data calculation is carried out through HQL or JAVA syntax, so that complete service logic is realized under a JAVA system. The object relational mapping framework technology avoids manually completing the data conversion between the object-oriented Java language and the relational database in the storage process, improves the development efficiency of the application system and is easy to maintain in the later period.

However, for some application systems already using a storage process, if a company developing the application system directly uses an object relational mapping framework to replace the storage process to improve the performance of the SQL statement when in use, the problems of high migration cost, high requirement on a technical stack, and time and labor consumption in the modification process of the technical scheme may exist.

Disclosure of Invention

In order to solve the defects in the prior art, the application aims to provide a storage process-compatible SQL statement configuration method, equipment and a storage medium, which can improve the performance of SQL statements in an application system and reduce the development cost while being compatible with a storage process.

In order to achieve the above object, the present application provides a method for configuring an SQL statement compatible with a stored procedure, including:

establishing a configuration file, wherein the configuration file comprises a plurality of SQL statement bodies written by preset writing rules, and statement body identifications are configured in the SQL statement bodies;

analyzing the configuration file, analyzing the SQL statement bodies in the configuration file through a syntax analysis class, generating syntax tree objects corresponding to the SQL statement bodies, and further generating a syntax tree object library;

and after calling a syntax tree object matched with the statement body identifier in a syntax tree object library through the statement body identifier, generating a preprocessed SQL statement which can be recognized by JDBC based on the input parameters.

Further, the configuration file is provided in plurality.

Furthermore, a plurality of SQL statement bodies are configured in an SQL statement body set, and the SQL statement body set is configured with a data set identifier.

And further, acquiring a syntax tree object matched with the statement body identification in a syntax tree object library through the data set identification and the statement body identification.

Further, the data set is identified as an access level class name.

Furthermore, the SQL statement body comprises a plurality of SQL statement nodes; the SQL statement node comprises one or more of a text statement node, a where statement node and an if statement node; the node content of the text statement node is a standard SQL statement, and the node content of the if statement node comprises an if statement mark, a judgment expression and a text statement node; the judgment expression is used for judging the validity of the if statement node; the content of the where statement node comprises a where statement mark and a text statement node.

Further, the where statement node and/or if statement node are nested and provided with multiple layers.

Further, the node content of the text statement node or the SQL statement of the supporting variable.

Further, the categories of the variables include a Java base type and a set of any base types thereof.

To achieve the above object, the present application also provides an electronic device, including:

a processor;

a memory including one or more computer program modules;

wherein the one or more computer program modules are stored in the memory and configured to be executed by the processor, the one or more computer program modules comprising SQL statement configuration methods for implementing the compatible stored procedures described above.

To achieve the above object, the present application also provides a computer storage medium including a computer program carried on a non-transitory computer readable medium, the computer program including program code for executing the SQL statement configuration method compatible with the storage procedure.

Compare in prior art, this application has following beneficial effect:

according to the method and the device, the configuration of the SQL statement is directly completed in the configuration file, the tight coupling with the database is avoided, the subsequent maintenance and the code updating are convenient, and the performance of the SQL statement in use is improved.

The method can be compatible with the storage process, namely when some application systems using the storage process need to improve the performance of a part of complex SQL sentences, the part of SQL sentences can be configured by using the method without influencing other SQL sentences in the storage process.

Compared with the mode of directly replacing the storage process by the object relational mapping framework, the method does not change the technical architecture of the original storage process and does not increase a new technical stack, namely, the method does not need to modify the whole technical architecture on a large scale while improving the performance of the SQL statement of the storage process, and has low development and modification cost.

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 the practice of the application.

Drawings

The above and other features, advantages and aspects of various embodiments of the present application will become more apparent from the following detailed description when taken in conjunction with the accompanying drawings. Like reference symbols in the various drawings indicate like elements. It should be understood that the drawings are schematic and that elements and features are not necessarily drawn to scale.

Fig. 1 is a schematic flowchart of a storage procedure compatible SQL statement configuration method according to an embodiment of the present application;

FIG. 2 is a schematic illustration of the profile contents of the present application;

FIG. 3 is a diagram illustrating the syntax parsing contents of the SQL statement corpus according to the present application;

FIG. 4 is a schematic diagram of preprocessing SQL statement content of the present application;

FIG. 5 is a schematic block diagram of an electronic device provided by an embodiment of the present application;

fig. 6 is a schematic diagram of a storage medium according to an embodiment of the present application.

Detailed Description

Embodiments of the present application will be described in more detail below with reference to the accompanying drawings. While certain embodiments of the present application are shown in the drawings, it should be understood that the present application may be embodied in various forms and should not be construed as limited to the embodiments set forth herein, but rather are provided for a more thorough and complete understanding of the present application. It should be understood that the drawings and embodiments of the present application are for illustration purposes only and are not intended to limit the scope of the present application.

It should be understood that the various steps recited in the method embodiments of the present application may be performed in a different order and/or in parallel. Moreover, method embodiments may include additional steps and/or omit performing the illustrated steps. The scope of the present application is not limited in this respect.

The term "include" and variations thereof as used herein are open-ended, i.e., "including but not limited to". The term "based on" is "based, at least in part, on". The term "one embodiment" means "at least one embodiment"; the term "another embodiment" means "at least one additional embodiment"; the term "some embodiments" means "at least some embodiments". Relevant definitions for other terms will be given in the following description.

It is noted that references to "a", "an", and "the" modifications in this application are intended to be illustrative rather than limiting, and that those skilled in the art will recognize that reference to "one or more" unless the context clearly dictates otherwise. "plurality" is to be understood as two or more.

Hereinafter, embodiments of the present application will be described in detail with reference to the accompanying drawings.

Fig. 1 is a schematic flowchart of a method for configuring an SQL statement compatible with a stored procedure according to an embodiment of the present application. As shown in fig. 1, in at least one embodiment, the method includes at least step S101, step S102, and step S103.

Step S101: establishing a configuration file, wherein the configuration file comprises a plurality of SQL (structured query language) statement bodies written by preset writing rules, and each SQL statement body is internally provided with a statement body mark;

step S102: analyzing the configuration file, analyzing the SQL statement bodies in the configuration file through a syntax analysis class, generating syntax tree objects corresponding to the SQL statement bodies, and further generating a syntax tree object library;

step S103: and after calling a syntax tree object matched with the statement body identifier in a syntax tree object library through the statement body identifier, generating a preprocessed SQL statement which can be recognized by JDBC based on the input parameters.

Fig. 2 is a schematic diagram of the content of the configuration file of the present application, as shown in fig. 2, the content of the configuration file of the present application includes a pair of tags < descriptions > and </descriptions >, the pair of tags and the content in the tag represent an SQL corpus, the < descriptions > tag is configured with a class attribute, the class attribute is used to identify the SQL corpus represented by the tag, that is, a data combination identifier, and an exemplary value of the class is a data access layer class name com. The tags of < statements > and </statements > are provided with a plurality of pairs of < stmt > and </stmt > tags, wherein the pair of < stmt > and </stmt > tags represents an SQL statement body, and an id attribute is configured in the < stmt > tag and is used for identifying the SQL statement body represented by the tags.

The SQL statement body comprises a plurality of SQL statement nodes, and the SQL statement nodes comprise one or more of text statement nodes, where statement nodes and if statement nodes; the node content of the text statement node is a standard SQL statement or an SQL statement supporting a variable; the node content of the if statement node comprises an if statement mark, a judgment expression and a text statement node; the judgment expression is used for judging the validity of the if statement node; the content of the where statement node comprises a where statement mark and a text statement node.

Illustratively, the < where > and </where > pair of tags and contents in the tag represent a where statement node, the < if > and </if > pair of tags and contents in the tag represent an if statement node, and the < if > tag is configured with a test attribute, the value of which is a judgment expression, for determining whether the if statement node is valid.

Illustratively, as shown in the second SQL statement in fig. 2, the value of the id attribute of the SQL statement is global _ user _ search, that is, the SQL statement is identified as global _ user _ search, the SQL statement includes a text statement node and a where statement node, the content of the text node is a standard SQL statement SELECT FROM global _ user, and 3 if statement nodes with test attributes in _ fuzzy = true, in _ fuzzy | = true, and in _ generator | = null are provided in the where statement node; wherein, an if statement node with a test attribute of in _ fuzzy = = true is provided with an if statement node with a test attribute of in _ user _ name | = = null, the if statement node is provided with a text statement node, and the content of the text node is an SQL statement supporting variables: AND user _ name LIKE CONCAT (# { in _ user _ name }, "%"); an if statement node with a test attribute of in _ user _ name | = null is arranged in the if statement node with a test attribute of in _ fuzzy | = true, a text statement node is arranged in the if statement node, and the content of the text node is an SQL statement supporting variables: AND user _ name = # { in _ user _ name }; a text statement node is arranged in an if statement node with the test attribute of in _ generator! = null, and the content of the text node is an SQL statement supporting variables: AND generator = # { in _ generator }.

It is understood that the variable is placed within # { }.

Further, the configuration file is provided in plurality. In actual system application, for convenience in maintenance and development, each configuration file corresponds to one business entity table, that is, the SQL statement bodies in the configuration file are set for the business entity table.

It should be noted that the configuration file is an XML configuration file. The XML can be an extensible markup language, is a source language allowing a user to define own markup language, uses a configuration file in an XML format to configure SQL sentences, and is high in code readability and convenient for later maintenance.

And analyzing the configuration file, reading the configuration file when the application system is initialized, analyzing the configuration file through a preset syntax analysis class, generating a java syntax tree object, and storing the java syntax tree object into a syntax tree object library.

It can be understood that the syntax parsing class includes a syntax parsing class of the SQL statement body set, a syntax parsing class of the SQL statement body, a syntax parsing class of the text statement node, a syntax parsing class of the if statement node, and a syntax parsing class of the where statement node. Illustratively, fig. 3 is a schematic diagram of the syntax parsing class content of the SQL statement body set of the present application.

It will be appreciated that the SQL statement body and its corresponding syntax tree object have the same identity.

When the initialization of the application system is completed, the corresponding syntax tree object can be called from the syntax tree object library through the identifier of the syntax tree object, and the preprocessed SQL statement which can be identified by JDBC is generated based on the input parameters. Illustratively, a syntax tree object corresponding to a syntax tree object library is called through a statement identifier "global _ user _ get _ by _ ids", it can be understood that the syntax tree object is parsed by a first SQL statement body whose id is "global _ user _ get _ by _ ids" in fig. 2, and a preprocessed SQL statement that can be recognized by JDBC is generated based on an input parameter args, fig. 4 is a schematic diagram of contents of the preprocessed SQL statement of the present application, as shown in fig. 4, the preprocessed SQL statement includes a string type SQL statement and a parameter args in an array form, and it can be understood that an execution result of the preprocessed SQL statement can be obtained by accessing a database through a jdbccwrapper interface in the JDBC.

The SQL sentence configuration method compatible with the storage process can be used for improving some application systems using the storage process on the premise of not directly replacing the storage process by using an object relational mapping framework, so that the performance of the SQL sentences in use is improved. Fig. 5 is a schematic block diagram of an electronic device according to an embodiment of the present application. As shown in fig. 5, the electronic device 130 includes a processor 131, a memory 132, and a display device 133. The display device 133 is configured as a live room. The memory 132 is used to store non-transitory computer readable instructions (e.g., one or more computer program modules). The processor 131 is configured to execute non-transitory computer readable instructions that, when executed by the processor 131, may perform one or more of the steps of the above-described stored procedure-compatible SQL statement configuration method. The memory 132 and the processor 131 may be interconnected by a bus system and/or other form of connection mechanism (not shown).

For example, the processor 131 may be a Central Processing Unit (CPU), a Digital Signal Processor (DSP), or other form of processing unit having data processing capabilities and/or program execution capabilities, such as a Field Programmable Gate Array (FPGA), or the like; for example, the Central Processing Unit (CPU) may be an X86 or ARM architecture or the like. The processor 131 may be a general-purpose processor or a special-purpose processor that may control other components in the electronic device 130 to perform desired functions.

For example, memory 132 may include any combination of one or more computer program products that may include various forms of computer-readable storage media, such as volatile memory and/or non-volatile memory. Volatile memory can include, for example, Random Access Memory (RAM), cache memory (or the like). The non-volatile memory may include, for example, Read Only Memory (ROM), a hard disk, an Erasable Programmable Read Only Memory (EPROM), a portable compact disc read only memory (CD-ROM), USB memory, flash memory, and the like. One or more computer program modules may be stored on the computer-readable storage medium and executed by processor 131 to implement various functions of electronic device 130. Various applications and various data, as well as various data used and/or generated by the applications, and the like, may also be stored in the computer-readable storage medium.

It should be noted that, in the embodiment of the present application, reference may be made to the description about the method above for specific functions and technical effects of the electronic device 130, and details are not described here.

Fig. 6 is a schematic diagram of a storage medium according to an embodiment of the present application. As shown in fig. 6, storage medium 150 is used to store non-transitory computer readable instructions 151. For example, the non-transitory computer readable instructions 151, when executed by a computer, may perform one or more steps in a SQL statement configuration method according to a compatible stored procedure described above.

For example, the storage medium 150 may be applied to the electronic device 130 described above. For example, the storage medium 150 may be the memory 132 in the electronic device 130 shown in fig. 5. For example, the related description about the storage medium 150 may refer to the corresponding description of the memory 132 in the electronic device 130 shown in fig. 5, and is not repeated here.

It should be noted that the storage medium (computer-readable medium) described above in the present application may be a computer-readable signal medium or a non-transitory computer-readable storage medium or any combination of the two. The non-transitory computer readable storage medium may be, for example, but not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or a combination of any of the above. More specific examples of the non-transitory computer-readable storage medium may include, but are not limited to: an electrical connection having one or more wires, a portable computer diskette, a hard disk, a Random Access Memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing. In the present application, a non-transitory computer readable storage medium may be any tangible medium that can contain, or store a program for use by or in connection with an instruction execution system, apparatus, or device.

In this application, however, a computer readable signal medium may include a propagated data signal with computer readable program code embodied therein, for example, in baseband or as part of a carrier wave. Such a propagated data signal may take many forms, including, but not limited to, electro-magnetic, optical, or any suitable combination thereof. A computer readable signal medium may also be any computer readable medium that is not a non-transitory computer readable storage medium and that can communicate, propagate, or transport a program for use by or in connection with an instruction execution system, apparatus, or device. Program code embodied on a computer readable medium may be transmitted using any appropriate medium, including but not limited to: electrical wires, optical cables, RF (radio frequency), etc., or any suitable combination of the foregoing.

The computer readable medium may be embodied in the electronic device; or may exist separately without being assembled into the electronic device.

The flowchart and block diagrams in the figures illustrate the architecture, functionality, and operation of possible implementations of systems, methods and computer program products according to various embodiments of the present application. In this regard, each block in the flowchart or block diagrams may represent a module, segment, or portion of code, which comprises one or more executable instructions for implementing the specified logical function(s). It should also be noted that, in some alternative implementations, the functions noted in the block may occur out of the order noted in the figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently, or the blocks may sometimes be executed in the reverse order, depending upon the functionality involved. It will also be noted that each block of the block diagrams and/or flowchart illustration, and combinations of blocks in the block diagrams and/or flowchart illustration, can be implemented by special purpose hardware-based systems which perform the specified functions or acts, or combinations of special purpose hardware and computer instructions.

In the foregoing, the SQL statement configuration method, the electronic device, and the storage medium compatible with the stored procedures provided in the embodiment of the present application are described with reference to fig. 1 to 6. The SQL statement configuration method compatible with the storage process, provided by the embodiment of the application, avoids the problems of poor performance and difficult maintenance of the SQL statement when the storage process is used, and improves the performance of the SQL statement when the storage process is used. The method can be compatible with the storage process, and can be used for carrying out SQL statement configuration on some complex dynamic SQL statements originally using the storage process, thereby avoiding the technical scheme that the whole storage process is replaced by object relational mapping from being migrated, and reducing the development cost.

The above description is only a few embodiments of the present application and is intended to be illustrative of the principles of the technology employed. It will be appreciated by those skilled in the art that the scope of the disclosure herein is not limited to the particular combination of features described above, but also encompasses other arrangements formed by any combination of the above features or their equivalents without departing from the spirit of the disclosure. For example, the above features may be replaced with (but not limited to) features having similar functions disclosed in the present application.

Further, while operations are depicted in a particular order, this should not be understood as requiring that such operations be performed in the particular order shown or in sequential order. Under certain circumstances, multitasking and parallel processing may be advantageous. Likewise, while several specific implementation details are included in the above discussion, these should not be construed as limitations on the scope of the application. Certain features that are described in the context of separate embodiments can also be implemented in combination in a single embodiment. Conversely, various features that are described in the context of a single embodiment can also be implemented in multiple embodiments separately or in any suitable subcombination.

Although the subject matter has been described in language specific to structural features and/or methodological acts, it is to be understood that the subject matter defined in the appended claims is not necessarily limited to the specific features or acts described above. Rather, the specific features and acts described above are disclosed as example forms of implementing the claims.

Claims

1. A SQL statement configuration method compatible with stored procedures comprises the following steps:

2. The method according to claim 1, wherein a plurality of SQL statements are configured in an SQL statement set, and the SQL statement set is configured with a data set identifier.

3. The method according to claim 2, wherein the syntax tree objects matching with the statement body identifier are obtained from a syntax tree object library through data set identifier and statement body identifier.

4. The stored procedure compatible SQL statement configuration method according to claim 2, wherein the data set identifier is an access level class name.

5. The SQL statement configuration method compatible with stored procedures of claim 1, wherein the SQL statement body comprises a plurality of SQL statement nodes; the SQL statement node comprises one or more of a text statement node, a where statement node and an if statement node; the node content of the text statement node is a standard SQL statement, and the node content of the if statement node comprises an if statement mark, a judgment expression and a text statement node; the judgment expression is used for judging the validity of the if statement node; the content of the where statement node comprises a where statement mark and a text statement node.

6. The SQL statement configuration method compatible with the stored procedures of claim 5, wherein the where statement node and/or if statement node are nested and provided with multiple layers.

7. The method according to claim 6, wherein the node contents of the text statement nodes or the SQL statements of the supporting variables are compatible with stored procedures.

8. The stored procedure compatible SQL statement configuration method according to claim 7, wherein the categories of variables include Java base types and any set of base types thereof.

9. An electronic device, comprising:

a processor;

a memory including one or more computer program modules;

wherein the one or more computer program modules are stored in the memory and configured to be executed by the processor, the one or more computer program modules comprising SQL statement configuration methods for implementing the compatible stored procedures of any of claims 1-8.

10. A computer storage medium comprising a computer program embodied on a non-transitory computer readable medium, the computer program comprising program code for performing the SQL statement configuration method for compatible stored procedures of any of claims 1-8.