A method for configuring SQL to support dynamic queries

Abstract

The present application relates to the technical field of computer, provide a kind of method for configuring SQL supports dynamic query, user inputs the SQL statement required by business, query method name and corresponding data source information in configuration page and sends to the system, the system is parsed out and returned to page by AST syntax analysis engine with the query condition in SQL and return data structure, user modifies, reduces, whether the query condition is limited to fill in, the data structure of return result is sorted, reduced and renamed and saved to database;When the interface of the system is called by the calling party of system, by passing in query method name and query parameter, the system will first query whether there is result data from cache, if yes, then directly return the result, if not, then the SQL of query will be reloaded by AST syntax analysis engine, the SQL required by business to be executed is assembled into SQL by filling in query parameter, finally, the query result is returned to calling party, and the result is cached to cache system.

Description

Technical Field

[0001] This invention relates to the field of computer technology, specifically a method for configuring SQL to support dynamic queries. Background Technology

[0002] In the development of data dashboards, dashboards, and other similar applications, it's common for server-side developers to create interfaces that only receive parameters, execute SQL queries written by DBAs, and then assemble the returned results according to business logic. This process is repetitive, lacks business logic, and wastes developers' time. This invention allows DBAs to configure SQL themselves, achieving a single interface compatible with all business applications. Summary of the Invention

[0003] This invention provides a method for supporting dynamic queries based on configured SQL statements. Users can input the required SQL statements, query method names, and corresponding data source information on a configuration page and send them to the application system. The system uses its built-in AST syntax analysis engine to parse the query conditions and return data structures in the SQL and return them to the page. Users can modify, delete, or restrict whether the query condition data structures are required, and sort, delete, and rename the returned result data structures before saving them to the database. When a caller calls the system's interface, by passing the query method name and query parameters, the system first checks the cache for existing result data. If it exists, the system returns the result directly. If not, the system reloads the query SQL using the AST syntax analysis engine, fills the query parameters into the SQL statement, assembles it into the SQL that the business actually needs to execute, and finally returns the result to the caller according to the configured information and caches the result in the cache system.

[0004] To achieve the above objectives, the present invention provides the following technical solution:

[0005] A method for supporting dynamic queries by configuring SQL includes the following steps:

[0006] A. The application system receives the startup command and starts up;

[0007] B. After the application system starts, the user configures the data source, SQL statement and query method name in the application system's configuration interface;

[0008] C. After receiving the SQL statement, data source, and query method name from the configuration interface, the application system connects to the database through the data source, verifies the validity of the SQL statement, and then parses the SQL statement in the AST syntax analysis engine. After parsing, the query conditions and return data structure contained in the SQL statement are obtained, and the query conditions and return data structure are returned to the configuration interface.

[0009] D. On the configuration interface, users can modify whether the returned query conditions are required, and users can also modify the names of the returned result fields;

[0010] E. The caller of the application system requests the query interface of the application system through the query method name and query parameters. The application system formats the query method name and query SQL conditions into a string using MD5, and then retrieves the result from the cache system. If the cache system has obtained the result, it is directly returned to the caller, and the process ends; if there is no data in the cache system, it continues to step F.

[0011] F. The application system retrieves the configured query SQL from the database by querying the method name, reassembles the query SQL and input parameters into a business query statement through the AST syntax analysis engine, and then sends the business query SQL to the database to request the query results;

[0012] G. After the application system obtains the results returned by the database, it formats the results according to the configured result data type and returns the result data. Then, it combines the returned result and the MD5 formatted result into a key-value pair, saves it to the cache system, and returns the result to the caller.

[0013] In step D, the ways for the user to modify the returned query conditions include one or more of the following: supporting the deletion of query conditions and modifying whether the query conditions are required.

[0014] In step D, the user can modify the name of the returned result field in one or more ways, including deleting the returned result field, performing sorting operations, and submitting and saving the result to the application system after completing the operation.

[0015] The beneficial effects of this invention are:

[0016] Compared to existing technologies, this invention eliminates the need to write dedicated code for any specific data interface. During development, data developers only need to configure the interface on the configuration side, and can customize the input parameters and return results. Once the application system is started, the interface caller only needs to pass the required query parameters and the configured query method name to the backend interface. The interface will first query the cache; if the data is not found, it will use an AST syntax analysis engine to parse and assemble the business SQL, and finally format the returned result according to the configured return result data structure and store it in the cache.

[0017] In addition, compared with traditional development methods, this invention can publish business interfaces to the outside world without the intervention of server-side developers. Furthermore, the interfaces can be dynamically updated during system execution without reinstalling the system. Attached Figure Description

[0018] Figure 1 This is a schematic diagram of the UML process of the present invention. Detailed Implementation

[0019] The present invention will be further described below with reference to the accompanying drawings:

[0020] Please refer to Figure 1 A method for configuring SQL to support dynamic queries includes the following steps:

[0021] A. The application system receives the startup command and starts up;

[0022] B. After the application system starts, the user configures the data source, SQL statement and query method name in the application system's configuration interface;

[0023] C. After receiving the SQL statement, data source, and query method name from the configuration interface, the application system connects to the database through the data source, verifies the validity of the SQL statement, and then parses the SQL statement in the AST syntax analysis engine. After parsing, the query conditions and return data structure contained in the SQL statement are obtained, and the query conditions and return data structure are returned to the configuration interface.

[0024] D. On the configuration interface, users can modify whether the returned query conditions are required, and users can also modify the names of the returned result fields;

[0025] E. The caller of the application system requests the query interface of the application system through the query method name and query parameters. The application system formats the query method name and query SQL conditions into a string using MD5, and then retrieves the result from the cache system. If the cache system has obtained the result, it is directly returned to the caller, and the process ends; if there is no data in the cache system, it continues to step F.

[0026] F. The application system retrieves the configured query SQL from the database by querying the method name, reassembles the query SQL and input parameters into a business query statement through the AST syntax analysis engine, and then sends the business query SQL to the database to request the query results;

[0027] G. After the application system obtains the results returned by the database, it formats the results according to the configured result data type and returns the result data. Then, it combines the returned result and the MD5 formatted result into a key-value pair, saves it to the cache system, and returns the result to the caller.

[0028] In step D, users can modify the returned query conditions in one or more ways, including deleting query conditions and changing whether query conditions are required. In addition, users can also modify the names of the returned result fields in one or more ways, including deleting returned result fields, performing sorting operations, and submitting and saving the results to the application system after completing the operation.

[0029] Data developers can complete the development of a data interface through four steps (A, B, C, D), while front-end developers can complete the interface call through three steps (E, F, G). This method can greatly improve the team's development efficiency, eliminate the need for back-end developers, and allow modification of the interface content without system downtime.

[0030] The above does not limit the technical scope of the present invention in any way. Any modifications, equivalent changes and alterations made to the above embodiments based on the technical essence of the present invention shall still fall within the technical scope of the present invention.

Claims

1. A method for configuring SQL to support dynamic queries, characterized in that: Includes the following steps: A. The application system receives the startup command and starts up; B. After the application system starts, the user configures the data source, SQL statement, and query method name in the application system's configuration interface; C. After receiving the SQL statement, data source, and query method name from the configuration interface, the application system connects to the database through the data source, verifies the validity of the SQL statement, and after verification, parses the SQL statement in the AST syntax analysis engine. After parsing, the system obtains the query conditions and return data structure contained in the SQL statement and returns the query conditions and return data structure to the configuration interface. D. On the configuration interface, users can modify whether the returned query conditions are required, and users can also modify the names of the returned result fields; E. The caller of the application system requests the query interface of the application system through the query method name and query parameters. The application system formats the query method name and query SQL conditions into a string using MD5, and then retrieves the results from the cache system. If the cache system has obtained the results, it directly returns them to the caller, and the process ends; if there is no data in the cache system, it continues to step F. F. The application system retrieves the configured query SQL from the database by querying the method name, reassembles the query SQL and input parameters into a business query statement through the AST syntax analysis engine, and then sends the business query SQL to the database to request the query results; G. After the application system obtains the results returned by the database, it formats the results according to the configured result data type and returns the result data. Then, it combines the returned result and the MD5 formatted result into a key-value pair, saves it to the cache system, and returns the result to the caller.

2. The method for configuring SQL to support dynamic queries according to claim 1, characterized in that: In step D, the user can modify the returned query conditions in one or more ways, including deleting query conditions and modifying whether query conditions are required.

3. The method for configuring SQL to support dynamic queries according to claim 1, characterized in that: In step D, the user can modify the name of the returned result field in one or more ways, including deleting the returned result field, performing sorting operations, and submitting and saving the result to the application system after completing the operation.

Images