Method for converting different database query statements into standard data interfaces

Abstract

The invention discloses a method for converting different database query statements into standard data interfaces. Comprising the following steps: S1, configuring standard data interface configuration information, and sending the standard data interface configuration information to a server system; a user configures standard data interface configuration information through the front-end interface and sends the standard data interface configuration information to the server system. S2, verifying standard data interface configuration information and storing the standard data interface configuration information in a database; and the server system receives and verifies the configuration information, generates a unique ID to be bound with the configuration information after verification is passed, and stores the unique ID to a database. S3, calling a standard data interface; and responding to an interface calling request, processing a request parameter to create an execution parameter according to unique ID query configuration information in the request, safely executing a query statement, packaging a result set into a standard format, and returning the result set. According to the method, the heterogeneous database query statement is quickly and safely converted into the standard data interface through the configured and automatic process, and the development efficiency and the system safety are greatly improved.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of computer software, and particularly relates to a method for converting different database query statements into a standard data interface. BACKGROUND

[0002] With the rapid development of the Internet and enterprise informatization, the demand of users for enterprise reports is increasing. Developers are faced with a large number of report development tasks, and among these tasks, database query statement writing is one of the core work.

[0003] However, in actual development, these database query statements often come from a variety of heterogeneous databases, such as relational databases, non-relational databases and various data warehouses. The syntax specifications, data structures and access methods of these databases are different. In addition, each query statement has different parameters, and the formats of the parameters are also different. For example, the date parameter has the format yyyy-MM-dd and the format yyyyMMdd. Secondly, the developer needs to manually write a large amount of glue code every time a query statement is reused.

[0004] Therefore, several obvious disadvantages are also derived: first, the development efficiency is low, and a large amount of energy is wasted on repetitive underlying coding; second, the code is not universal, and each query statement needs to be matched with special code, which is difficult to reuse; third, the system maintenance is difficult, and any change in the underlying data source or query logic will trigger a chain reaction of code, which is not only prone to errors but also accumulates heavy technical debt. SUMMARY

[0005] The purpose of the present application is to solve the above problems and provide a method for converting different database query statements into a standard data interface.

[0006] In order to achieve the above purpose, the following technical solutions are adopted: A method for converting different database query statements into a standard data interface, comprising the following steps: S1: configuring standard data interface configuration information and sending it to a server system; including the following sub-steps: S11: writing a standard data interface name; S12: selecting a data source name; Selecting through a drop-down box in the front-end interface, wherein the data source name corresponds to a database connection.

[0007] S13: writing a database query statement; The parameters in the database query statement exist in the form of {parameter code}.

[0008] The database query statement includes but is not limited to a stored procedure, a select statement, a with...cte...select statement or a database supported query statement; S14: generating a parameter information list; Specifically, by analyzing the database query statement, the parameter placeholder contained therein is identified, the parameter placeholder is extracted as a parameter code, and corresponding parameter information is generated based on the parameter code. All parameter information is stored in a set to form a parameter information list.

[0009] The parameter information list is composed of multiple pieces of parameter information.

[0010] The parameter information includes a parameter code, a parameter name, a parameter format conversion function, a parameter value and a parameter description.

[0011] S15: configuring parameter information; Specifically, the user-configurable parameter information includes a parameter name and a parameter value, and at least one of a parameter format conversion function and a parameter description.

[0012] S16: selecting a result processing method; Specifically, the user can select to customize the configuration of the result processing method or default to no processing.

[0013] S17: saving standard data interface configuration information and sending it to the server system through an HTTP request; Specifically, the standard data interface configuration information is sent to the server system through an HTTP request created by the front-end interface.

[0014] S2: verifying standard data interface configuration information and saving it to the database; Including the following sub-steps S21: the server system receives the standard data interface configuration information; The server system receives the HTTP request and parses it to obtain the standard data interface configuration information.

[0015] S22: verifying the standard data interface configuration information; The syntax of the database query statement in the standard data interface configuration information is verified. If the syntax is correct, the verification is passed, step S23 is executed, and if the syntax is incorrect, a response data of verification failure is created and returned to the front-end interface.

[0016] S23: the server system generates a unique ID and establishes a JBDC connection; After the check passes, based on the standard data interface configuration information, the server system generates a unique ID through UUIDv7 or a snowflake algorithm, creates a JDBC connection, and establishes a session connection with the database.

[0017] The unique ID corresponds one-to-one to the standard data interface configuration information. The JDBC connection is a standard API specification for interacting with a relational database, used to establish a session connection with the database.

[0018] S24: Create a first execution parameter and traverse the elements of the parameter information list, and perform preliminary preprocessing on the first execution parameter. The server system creates a first execution parameter, then traverses each element, i.e., parameter information, in the parameter information list in the standard data interface, and performs preliminary preprocessing on the first execution parameter.

[0019] Specifically, the specific process of preliminary preprocessing is as follows: when the server traverses the parameter information, it synchronously judges whether the parameter format conversion function of the current parameter information is empty. If it is empty, it calls the function executor, inputs the parameter value of the current parameter, obtains the converted parameter value, and inserts it into the first execution parameter with the parameter code as the key and the converted parameter value as the value. Otherwise, it inserts it into the first execution parameter with the parameter code as the key and the parameter value of the current parameter as the value.

[0020] The first execution parameter is a key-value pair parameter, where the key is the parameter name and the value is the parameter value, and both the key and the value are empty by default.

[0021] S25: Perform security processing on the first execution parameter after preliminary processing and the database query statement to generate a safe, precompiled parameter set and database query statement. Specifically, the specific content of security processing is as follows: Parse all {parameter code} form parameter codes in the database query statement through a regular expression and store them in a parameter code set. Traverse each parameter code in the parameter code set and query the corresponding value from the second execution parameter according to the current parameter code, and store the value in the parameter set.

[0022] Replace all {parameter code} forms in the database query statement with parameterized placeholders through a regular expression to obtain a precompiled database query statement.

[0023] S26: According to the data source name in the standard data interface configuration information, obtain the corresponding data source from the global data source set, and obtain the JDBC connection from the data source, receive the parameter set, execute the database query statement, and obtain the result set. The global data source set is a key-value pair set of Map type created when the server system is started, with a data source name as a key and a data source as a value.

[0024] S27: Extract column type information in the result set and convert the column type information into a mark string; Specifically, the extraction method is to obtain metadata from the result set, traverse each row of the metadata to obtain column name and data type information, and store the column type information.

[0025] The column type set is a key-value pair set, with a column name as a key and a data type of the column as a value.

[0026] S28: The server system saves the unique ID, the corresponding standard data interface configuration information and the mark string into the database, and returns the response data of successful saving to the front-end interface; The server system associates the generated unique ID, the corresponding standard data interface configuration information and the mark string, and saves them in the database through JDBC connection, and creates the response data of successful saving and returns it to the front-end interface.

[0027] S3: Call the standard data interface; Including the following sub-steps: S31: The server system parses the call request to extract the unique ID, request parameters and temporary result processing mode therefrom; Specifically, the user issues a call request, and the server system receives and parses the call request to extract the unique ID, request parameters and temporary result processing mode therefrom.

[0028] The request parameters are parameters input by the user when calling the standard data interface.

[0029] The request parameters are key-value pair parameters of Map data type, with a parameter name as a key and a parameter value as a value.

[0030] The temporary result processing mode is a string input by the user when calling the standard data interface.

[0031] S32: Query the corresponding standard data interface configuration information from the database according to the unique ID; Specifically, the unique ID parsed in step S31 is used to query the corresponding standard data interface configuration information from the database.

[0032] S33: Create a second execution parameter, traverse the parameter information list, and pre-process the second execution parameter; Specifically, the server system creates a second execution parameter, and traverses each element of a parameter information list in the standard data interface configuration information, and in the process of traversal, the second execution parameter is preprocessed synchronously.

[0033] The second execution parameter is a necessary parameter used when executing the query statement, and is a key-value pair parameter of Map type, with the key being a parameter name and the value being a parameter value, and both the key and the value are initially empty by default.

[0034] Specifically, the preprocessing specifically includes the following steps: S331: Key matching judgment is performed on the request parameter; If the key of the request parameter is the parameter code of the current parameter information, the value of the request parameter is obtained according to the parameter code, and the value of the request parameter is taken as an intermediate parameter value; If the key of the request parameter is not the parameter code of the current parameter information, the default parameter value of the current parameter information is taken as the intermediate parameter value; S332: Judgment is performed on the parameter format conversion function of the parameter information; If the parameter format conversion function of the parameter information is not empty, a function executor is called and the intermediate parameter value is input, to obtain a converted parameter value, and the parameter code is taken as the key and the converted parameter value is taken as the value, which are inserted into the second execution parameter; If the parameter format conversion function of the parameter information is empty, the parameter code is taken as the key and the value of the request parameter is taken as the value, which are inserted into the second execution parameter.

[0035] S34: The same security processing steps as in step S25 are adopted to perform security processing on the second execution parameter and the database query statement, to generate a safe, precompiled parameter set and a database query statement; S35: According to the data source name in the standard data interface configuration information, a data source with the specified data source name is obtained from a global data source set, a JDBC connection is obtained from the data source, a parameter set is received, a database query statement is executed, and a dynamic result set is obtained; S36: The dynamic result set is encapsulated to obtain a standard result set; Specifically, the encapsulation specifically includes the following steps: The corresponding mark string is queried from the database according to the unique ID, and the mark string is deserialized into a key-value pair set, i.e., column type information.

[0036] The data of each row in the dynamic result set and the data of each column in the current row are traversed, then the column name is obtained from the column type set, and based on the obtained column name, the column value is obtained from the currently traversed column.

[0037] The column name is taken as a key, the column value is taken as a value, a key-value pair is constructed, the constructed key-value pair is stored in a key-value pair set, and finally the key-value pair set of each row is stored in a List set to form a data result set, that is, data information.

[0038] The column type information and the data information are combined to form a standard result set.

[0039] S37: judging the temporary result processing mode, and returning a standard result set or a custom result set according to the judgment result.

[0040] Specifically, the judgment of the temporary result processing mode is as follows: If the temporary result processing mode does not exist, the result processing mode is obtained from the standard data interface configuration information; If the temporary result processing mode exists and is not processing, a corresponding result processor is selected and called according to the result processing mode, the standard result set is input into the result processor, and a processed custom result set is returned; If the temporary result processing mode exists and is processing, the standard result set is directly returned.

[0041] Compared with the prior art, the beneficial effects of the present application are as follows: The present application changes the development of the data interface from the traditional hard coding mode to the configuration mode. The user does not need to write complex business logic code, database connection code and HTTP interface code, but only needs to write a core query statement and complete parameter configuration to quickly generate a usable interface. The development, testing and deployment cycle is greatly shortened, the technical requirements for the developer are reduced, and the agile development and rapid delivery of the data interface are realized.

[0042] The present application adopts a full configuration driven architecture. The user can customize interface information, write any query statement, finely define the processing rules of each parameter and flexibly select the result processing mode according to the specific business requirements, without modifying the system core code to extend a new data interface, thereby improving the adaptability and practicality of the present application in practical application.

[0043] The present application converts the database query statement according to the configuration information written by the user, effectively avoids the difference in connection mode and syntax details of different underlying databases, easily accesses and manages various data sources through a unified configuration process, and provides a standard and consistent data service interface to the outside, thereby simplifying the system architecture and operation and maintenance difficulty in a complex data environment.

[0044] Since all interface logic is defined and managed through configuration information, changes in business requirements can be implemented instantly simply by modifying the configuration content through the interface, avoiding the cumbersome process of modifying code, recompiling, and deploying in the traditional way. This makes system maintenance simple and efficient, greatly reduces long-term operation and maintenance costs and technical debt, and ensures the system's ability to respond quickly to business needs. Attached Figure Description

[0045] Figure 1 This is a flowchart illustrating the steps of a method for converting different database query statements into a standard data interface according to the present invention. Figure 2 This is a schematic diagram illustrating step S1 of a method for converting different database query statements into a standard data interface according to the present invention. Figure 3 This is a schematic diagram illustrating step S2 of a method for converting different database query statements into a standard data interface according to the present invention. Figure 4 This is a schematic diagram illustrating step S3 of a method for converting different database query statements into a standard data interface according to the present invention. Detailed Implementation

[0046] To make the above-mentioned objectives, features and advantages of this application more apparent and understandable, the specific embodiments of this application will be described in detail below with reference to the accompanying drawings.

[0047] like Figure 1 As shown, a method for converting different database query statements into a standard data interface includes the following steps: S1: Configure the standard data interface configuration information and send it to the server system; Users can configure standard data interfaces through a graphical configuration interface provided by the front-end. The front-end interface is usually a web page, which is divided into six functional areas: 'Basic Information Configuration Area', 'Data Source Configuration Area', 'Query Statement Editing Area', 'Parameter Configuration Area', 'Result Processing Configuration Area', and 'Operation Button Area'. Users can complete the configuration through mouse clicks, keyboard input, drop-down selections, and other operations.

[0048] like Figure 2 As shown, the specific process includes the following sub-steps: S11: Specify the standard data interface name; Users enter the name of the standard data interface in the basic information configuration area, such as "Query Department User List". The standard data interface name is a globally unique identifier, providing a precise index for subsequent interface calls, management, and retrieval.

[0049] S12: Select a data source name; The user selects a data source name, such as "mysql-production-db", through a drop-down box in the data source configuration area in the front-end interface. One data source name corresponds to one database connection, and the data source name is a pre-configured abstract identifier containing all necessary information of the database connection. By selecting a data source name, the server system can quickly locate and establish a connection with the target database according to the identifier when calling the standard data interface configuration information in the subsequent.

[0050] S13: Write a database query statement; The user writes a database query statement in the query statement editing area. By writing different query statements, the user can create data interfaces with different functions to meet the needs of various business scenarios, from simple data retrieval to complex statistical analysis.

[0051] The parameters in the database query statement exist in the form of {parameter code}.

[0052] The database query statement includes but is not limited to stored procedures, select statements, complex query statements with general expressions (such as with...cte...select), or database-supported query statements. S14: Generate a parameter information list; Specifically, after the user clicks the "one-click generation" button, the front-end interface uses a regular expression to parse the database query statement through an embedded JavaScript program, automatically identifies the parameter placeholders contained in the database query statement, extracts the parameter placeholders as parameter codes, and generates corresponding parameter information based on the parameter codes. All parameter information is stored in a collection to form a parameter information list.

[0053] The parameter information list is composed of multiple parameter information.

[0054] The parameter information includes parameter code, parameter name, parameter format conversion function, parameter value, and parameter description.

[0055] The parameter code is the parameter code in the database query statement.

[0056] The parameter name is the Chinese name of the parameter code, which is convenient for developers to understand the actual name of the parameter code.

[0057] The parameter format conversion function is filled in the specification: function name ($this, params), $this is a keyword, indicating the parameter value to be converted in format.

[0058] The parameter format conversion function is used to convert the non-standard parameter format into the standard parameter format, such as converting the date parameter in the format of yyyyMMdd into the standard format of yyyy-MM-dd. By unifying the parameter format, the developer does not need to consider the parameter format processing problem, and the use cost is reduced.

[0059] The parameter value adopts the default value of the parameter code, and the external request can also modify the default value to realize dynamic parameters.

[0060] The parameter description is the detailed information and specific use of the parameter code, which facilitates the user to clearly understand.

[0061] According to the above steps, the parameter information list is generated, a standardized unified specification is formed, and the foundation for subsequent automation processing is laid, the manual intervention is reduced, the user only needs to fill in and configure according to the information dimension in the list, the threshold of parameter configuration is reduced, the problem of easy error in manual creation is avoided, and the configuration efficiency and accuracy are improved.

[0062] S15: Configure parameter information; The parameter information list generated in step S14 is a page automatically generated by the front-end interface, and the specific information inside needs to be configured by the user. Therefore, the user configures the parameter information in the generated parameter information list in the parameter configuration area, allows the user to supplement the differentiated information of the parameter according to the actual business demand, and avoids the contradiction that the "system forced logic cannot meet the business scene".

[0063] Specifically, the parameter information that can be configured by the user includes at least one of the parameter name, the parameter value, the parameter format conversion function, and the parameter description.

[0064] S16: Select result processing mode; The user selects the result processing mode from the result processing configuration area according to the own demand.

[0065] Specifically, the user can select to customize the result processing mode or default to not process.

[0066] The result returned by the database query interface is usually in the original format, such as directly mapping the database field name and data type, while the downstream systems, such as the front-end page and the report tool, have different requirements for the result format. If the interface does not support the selection of the result processing mode, the downstream system needs to write a large amount of secondary processing code for the unified interface result, which further leads to the reduction of the connection efficiency, the deterioration of the compatibility, and the risk of inconsistent data caused by multiple conversions.

[0067] Therefore, in the present application, when the downstream system has special result processing requirements, the user customizes the result processing mode according to the requirements; when the downstream system has no special requirements, the result processing mode is defaulted to no processing.

[0068] S17: Save the standard data interface configuration information and send it to the server system through an HTTP request; Specifically, the user clicks the "Save" button, and the front-end interface sends all the standard data interface configuration information to the API endpoint specified by the server system through an HTTP request created by the front-end interface.

[0069] S2: Verify the standard data interface configuration information and save it to the database; As shown in Figure 3 The specific process includes the following sub-steps S21: The server system receives the standard data interface configuration information; The server system receives the HTTP request and parses the request body to obtain the standard data interface configuration information. The server system is usually a backend application built with Java Web frameworks such as Spring Boot.

[0070] S22: Verify the standard data interface configuration information; The server system performs syntax verification on the database query statements in the standard data interface configuration information through the functions provided by the database driver. If the syntax is correct, the verification is passed, and step S23 is continued. If the syntax is incorrect, a verification failure response data is created and returned to the front-end interface.

[0071] S23: The server system generates a unique ID and establishes a JBDC connection; After verification, based on the standard data interface configuration information, the server system uses the snowflake algorithm to generate a globally unique ID, which is bound to this configuration information. A JDBC connection is created to establish a session connection with the database.

[0072] The unique ID corresponds to the standard data interface configuration information one by one, ensuring that each interface's configuration information has a unique identifier, providing a core search basis for subsequent operations.

[0073] JDBC connection is a standard API specification for interacting with relational databases, used to establish a session connection with the database.

[0074] S24: Create the first execution parameter and traverse the elements of the parameter information list while performing preliminary preprocessing on the first execution parameter; The server system creates a first execution parameter, which is a key-value pair parameter, wherein the key is a parameter name and the value is a parameter value, and both the key and the value are empty by default. Then each element in the parameter information list in the standard data interface is traversed, i.e., parameter information, and the first execution parameter is preliminarily preprocessed.

[0075] Specifically, the specific process of the preliminary preprocessing is as follows: when the server system traverses the parameter information, it synchronously judges whether the parameter format conversion function of the current parameter information is empty. If it is empty, the function executor is called, the parameter value of the current parameter is input, the converted parameter value is obtained, and the parameter code is taken as the key and the converted parameter value is taken as the value, which are inserted into the first execution parameter. Otherwise, the parameter code is taken as the key and the parameter value of the current parameter is taken as the value, which are inserted into the first execution parameter.

[0076] By creating the first execution parameter and preliminarily preprocessing it, a parameter set that is as close as possible to the real calling scene and has been preliminarily preprocessed is generated, which can ensure that even the default value needs to be processed by the format conversion function, so that the subsequent steps can be performed under the parameter condition closest to the real environment, thereby ensuring the high accuracy of the obtained column type information.

[0077] S25: The first execution parameter after the preliminary preprocessing and the database query statement are safely processed to generate a safe and precompiled parameter set and a database query statement; Specifically, the specific content of the safe processing is as follows: All parameter codes in the form of {parameter code} in the database query statement are parsed by a regular expression and stored in a parameter code set. Each parameter code in the parameter code set is traversed, and the corresponding value is queried from the second execution parameter according to the current parameter code, and the value is stored in the parameter set.

[0078] All {parameter code} forms in the database query statement are replaced by parameterized placeholders by a regular expression to obtain a precompiled database query statement.

[0079] The parameter set and the database query statement obtained after the safe processing can prevent SQL injection, which is a high-risk risk and also solves the syntax error problem caused by special characters, and is a key technical link to ensure the security of the interface call and the stability of the database. Especially in the public interface scenario for external users, the processing mechanism can significantly reduce the risk of data leakage and damage.

[0080] S26: According to the data source name in the standard data interface configuration information, the corresponding data source is obtained from the global data source set, and the JDBC connection is obtained from the data source, the parameter set is received, the database query statement is executed, and the result set is obtained; Wherein, the global data source set is a key-value pair set of Map type with data source name as key and data source as value, which is created when the server system starts.

[0081] S27: Extract column type information in the result set, and convert the column type information into a mark string; Specifically, the metadata is obtained from the result set, each row of the metadata is traversed to obtain column name and data type information, which is stored in the column type information, and the column type information is converted into a mark string such as a JSON string.

[0082] The column type set is a key-value pair set, with column name as key and data type of the column as value, such as int, varchar(20), decimal(10, 2), etc.

[0083] Through steps S24, S25, S26 and S27, the preposition and transparency of the data structure are realized, the first execution parameter of the preliminary preprocessing is used to simulate a real query execution, and the data structure that will be finally returned by the query, i.e. which fields and types of each field, can be known in advance and accurately. The user knows the return structure of the interface immediately after the configuration is completed, without the need for "blind" first call or document review, solving the "information asymmetry" problem and enhancing the predictability and ease of use of the system.

[0084] S28: The server system saves the unique ID and the corresponding standard data interface configuration information and the mark string to the database, and returns the response data of successful saving to the front-end interface; The server system associates the generated unique ID with the corresponding standard data interface configuration information and the mark string, saves them in the database through JDBC connection, and then creates the response data of successful saving to return to the front-end interface.

[0085] S3: Call the standard data interface; The user issues a call request, i.e. calls the standard data interface through HTTP request, and transmits the unique ID, request parameter and temporary result processing mode to the server system.

[0086] As shown in the following figure: Figure 4 Specifically, the following sub-steps are included: S31: The server system parses the call request to extract the unique ID, request parameter and temporary result processing mode therefrom; Specifically, the server system receives and parses the call request to extract the unique ID, request parameter and temporary result processing mode from the request body.

[0087] The request parameter is the parameter transmitted by the user when calling the standard data interface.

[0088] The request parameter is a key-value pair parameter of a Map data type, wherein the key is a parameter name and the value is a parameter value.

[0089] The temporary result processing mode is a string passed in when the user calls the standard data interface. Passing in the temporary result processing mode when calling the standard data interface can dynamically adapt to temporary and personalized result format requirements without modifying the default configuration of the interface. Since there may be a large number of short-term and special temporary requirements in actual business, the passing in of the temporary result processing mode allows the user to directly specify the result processing logic through the parameter without modifying the default configuration in the database, thereby avoiding the risk of modifying the default configuration and affecting the global.

[0090] S32: Query the corresponding standard data interface configuration information from the database according to the unique ID; Specifically, the server system queries the corresponding standard data interface configuration information from the database according to the unique ID obtained by parsing.

[0091] S33: Create a second execution parameter, iterate through the parameter information list, and pre-process the second execution parameter; Specifically, the server system creates a second execution parameter, wherein the second execution parameter is a necessary parameter used when executing a query statement and is a key-value pair parameter of a Map type, the key is a parameter name, the value is a parameter value, and both the key and the value are initially empty by default. Then, each element of the parameter information list in the standard data interface configuration information is iterated, and the second execution parameter is pre-processed synchronously during the iteration.

[0092] Specifically, the pre-processing includes the following steps: S331: Key matching judgment is performed on the request parameter; The server system checks the key of the request parameter to determine whether the request parameter contains a key that is the same as the parameter code of the current parameter information; If so, the corresponding value is found from the request parameter, and the value is used as an intermediate parameter value.

[0093] If not, the default parameter value of the current parameter information is used as the intermediate parameter value.

[0094] Since the request parameter is created by the user and passed into the server system, it has a higher priority than the default parameter information, so it is necessary to determine whether the request parameter exists. If it exists, the request parameter is used to overwrite the parameter information, and if it does not exist, the default parameter value of the parameter information is used. The user can choose whether to create a request parameter according to the needs, and if the key of the request parameter is an invalid key, it will also be ignored in this judgment logic. Therefore, this not only improves the flexibility of the code, but also avoids errors in subsequent step processes.

[0095] S332: judging the parameter format conversion function of the parameter information; The server system checks whether the parameter format conversion function in the current parameter information is empty.

[0096] If not, the function executor registered in the server system is called, and the intermediate parameter value obtained in the previous step is passed into the function executor to obtain the converted parameter value. The parameter code is used as the key, and the converted parameter value is used as the value, which is inserted into the second execution parameter.

[0097] If empty, the parameter code of the current parameter is used as the key, and the value of the request parameter is used as the value, which is inserted into the second execution parameter.

[0098] The core purpose of this judgment is to solve the problem of whether the parameter value needs to be processed, that is, to determine whether the original parameter value obtained needs to be customized. By first judging whether conversion is needed, and then deciding whether to perform conversion, the original input from different sources and with inconsistent formats is converted into a unified and standardized format required by the database query, which embodies the ability of "user-defined extension".

[0099] Step S33 creates a second execution parameter and pre-processes the second execution parameter, so that the external incoming, possibly non-standard, incomplete original request parameter can be automatically and intelligently converted into a fully prepared, safe and reliable "second execution parameter" set that can be directly used for database operation.

[0100] S34: The same security processing steps as in step S25 are used to process the second execution parameter and the database query statement to generate a safe, pre-compiled parameter set and a database query statement; The user-oriented configuration format is converted into the execution format required by the database underlying driver, so as to build a query structure completely separated from the parameter value, ensuring that user input will only be treated as data processing, and never be parsed as SQL code. At the same time, the pre-compilation mechanism usually also brings performance improvement.

[0101] S35: According to the data source name in the standard data interface configuration information, the data source with the specified data source name is obtained from the global data source set, and the JDBC connection is obtained from the data source. The parameter set is received, the database query statement is executed, and the dynamic result set is obtained; Specifically, the server system acquires a data source corresponding to the data source name in the configuration information from the set of data sources, and acquires a new JDBC connection from the data source. Based on the connection and the precompiled database query statement, a PreparedStatement object is created. The values in the precompiled parameter set are sequentially set to the object, and finally the database query statement is executed to obtain a dynamic result set. Thus, the final database interaction is completed, the original data is obtained, and the conversion from configuration information to real data is realized.

[0102] Step S35, as the core of executing the database query, can convert the preprocessed query logic into actual data results in an efficient, secure and reliable manner. The performance is optimized by connection pool reuse, the result quality is guaranteed by the verification mechanism, and a data basis is provided for subsequent packaging and processing.

[0103] S36: Packaging the dynamic result set to obtain a standard result set; The server system uses the column type information set pre-acquired and saved in the database in step S26 to package the dynamic result set.

[0104] Specifically, the specific method of packaging is as follows: The corresponding mark string is queried from the database according to the unique ID, and the mark string is deserialized into a key-value pair set, i.e., column type information.

[0105] The data of each row in the dynamic result set and the data of each column of the current row are traversed, and then the column name is acquired from the known column type information set, and based on the acquired column name, the column value is acquired from the currently traversed column.

[0106] The column name is taken as the key and the column value is taken as the value to construct a Map type key-value pair, and the constructed key-value pair is stored in a key-value pair set. Finally, the key-value pair set of each row is stored in a List set to form a data result set, i.e., data information.

[0107] The column type information and the data information are combined to form a standard result set.

[0108] S37: Judging the temporary result processing mode and returning the standard result set or the custom result set according to the judgment result.

[0109] Specifically, the judgment of the temporary result processing mode is as follows: If the temporary result processing mode does not exist, the result processing mode is acquired from the standard data interface configuration information.

[0110] If the temporary result processing mode exists and is not not processing, the user inputs a custom configured temporary result processing mode, then a corresponding result processor is invoked according to the selected result processing mode, the standard result set is input into the result processor, and the custom result set returned by the result processor is taken as the final corresponding result.

[0111] If the temporary result processing mode exists and is not processing, the standard result set is directly serialized into a JSON format, and then the standard result set is returned through an HTTP response.

[0112] Since the temporary result processing mode is created and input into the server system by the user, the priority is higher than the result processing mode in the standard data interface configuration information, so it is necessary to determine whether the temporary result processing mode exists, if not, the result processing mode in the configuration information is found from the database, if yes, the temporary result processing mode is used. When the temporary result processing mode is input, the user can select the temporary result processing mode according to the demand, or the default is not processing.

[0113] If the temporary result processing mode is inconsistent with the preset mode in the configuration information, the temporary result processing mode is used.

[0114] Through different configurations of the result processing mode by the user in the configuration information, the application can adapt to the data format requirements of various downstream systems. Whether it is a JSON expected by the front-end page, or an XML required for integration with a legacy system, or even a specific encryption format, it can be easily realized through configuration. This reflects the strong adaptation ability of the application as a data service intermediate layer. This mechanism allows the user to implant any complex post-processing logic without modifying the core code.

[0115] The above only describes the preferred examples of the application and does not limit the application. For those skilled in the art, the application can have other optimization schemes and additional functions. Any modification, equivalent replacement, improvement, etc. within the spirit and principles of the application shall be included in the protection scope of the application.

Claims

1. A method for converting different database query statements into a standard data interface, characterized in that: Includes the following steps: S1: Configure the standard data interface configuration information and send it to the server system; Includes the following sub-steps: S11: Specify the standard data interface name; S12: Select the data source name; S13: Write the database query statement; The parameters in the database query statement exist in the form of {parameter code}; S14: Generate a list of parameter information; The parameter information list consists of multiple parameter information items; The parameter information includes parameter code, parameter name, parameter format conversion function, parameter value, and parameter description; S15: Configuration parameter information; S16: Select the result processing method; S17: Save the standard data interface configuration information and send it to the server system via an HTTP request; S2: Verify the standard data interface configuration information and save it to the database; Includes the following sub-steps S21: The server system receives standard data interface configuration information; S22: Verify the standard data interface configuration information; S23: The server system generates a unique ID and establishes a JBDC connection; The unique ID corresponds one-to-one with the standard data interface configuration information; S24: Create the first execution parameter, iterate through the elements of the parameter information list, and perform preliminary preprocessing on the first execution parameter; S25: Perform security processing on the first execution parameters and the database query statement after preliminary preprocessing to generate a secure, pre-compiled parameter set and database query statement; S26: Based on the data source name in the standard data interface configuration information, obtain the corresponding data source from the global data source collection, obtain a JDBC connection from the data source, receive the parameter set, execute the database query statement, and obtain the result set; S27: Extract column type information from the result set and convert the column type information into a tag string; S28: The server system saves the unique ID, the corresponding standard data interface configuration information, and the tag string to the database, and returns the successful saving response data to the front-end interface; S3: Call the standard data interface; Includes the following sub-steps: S31: The server system parses the call request and extracts the unique ID, request parameters, and temporary result processing method from it; The request parameters are the parameters passed in by the user when calling the standard data interface; The request parameter is a key-value pair parameter, where the key is the parameter name and the value is the parameter value; The temporary result processing method is a string passed in by the user when calling the standard data interface; S32: Retrieve the corresponding standard data interface configuration information from the database based on the unique ID; S33: Create a second execution parameter, traverse the parameter information list, and preprocess the second execution parameter; The second execution parameter is a required parameter used when executing the query statement; S34: Perform security processing on the second execution parameters and the database query statement to generate a secure, pre-compiled set of parameters and a database query statement; S35: Based on the data source name in the standard data interface configuration information, retrieve the data source with the specified data source name from the global data source collection, obtain a JDBC connection from the data source, receive the parameter set, execute the database query statement, and obtain a dynamic result set; The global data source collection is a key-value pair collection created when the server system starts, with the data source name as the key and the data source as the value. S36: Encapsulate the dynamic result set to obtain the standard result set; The standard result set consists of two parts: column type information and data information. S37: Determine the processing method for temporary results and return a standard result set or a custom result set based on the determination result.

2. The method for converting different database query statements into a standard data interface as described in claim 1, characterized in that: Step S14 specifically involves parsing the database query statement, identifying the parameter placeholders contained therein, extracting the parameter placeholders as parameter codes, generating corresponding parameter information based on the parameter codes, and storing all the parameter information into a set to form a parameter information list.

3. The method for converting different database query statements into a standard data interface as described in claim 1, characterized in that: In step S15, the user-configurable parameter information includes parameter name, parameter value, and at least one of parameter format conversion function and parameter description.

4. The method for converting different database query statements into a standard data interface as described in claim 1, characterized in that: In step S16, the user can choose to customize the result processing method or default to no processing.

5. The method for converting different database query statements into a standard data interface as described in claim 1, characterized in that: The specific content of step S2 includes: S21: The server system receives standard data interface configuration information; The server system receives HTTP requests and parses them to obtain standard data interface configuration information; S22: Verify the standard data interface configuration information; The syntax of the database query statement in the standard data interface configuration information is validated. If the syntax is correct, the validation passes and step S23 is executed. If the syntax is incorrect, a validation failure response is created and returned to the front-end interface. S23: The server system generates a unique ID and establishes a JDBC connection; After successful verification, based on the standard data interface configuration information, the server system generates a unique ID using UUIDv7 or the Snowflake algorithm, and creates a JDBC connection to establish a session connection with the database. S24: Create the first execution parameter, iterate through the elements of the parameter information list, and perform preliminary preprocessing on the first execution parameter; Preliminary preprocessing: When the server system traverses each element in the parameter information list of the standard data interface, it synchronously checks whether the parameter format conversion function of the current parameter information is empty. If it is empty, the function executor is called, the parameter value of the current parameter is passed in, the converted parameter value is obtained, and the parameter code is used as the key and the converted parameter value is used as the value, and inserted into the first execution parameter. Otherwise, the parameter code is used as the key and the parameter value of the current parameter is used as the value, and inserted into the first execution parameter. The first execution parameter is a key-value pair parameter, where the key is the parameter name and the value is the parameter value, and both the key and the value are empty by default; S25: Perform security processing on the first execution parameters and the database query statement after preliminary preprocessing to generate a secure, pre-compiled parameter set and database query statement; S26: Based on the data source name in the standard data interface configuration information, obtain the corresponding data source from the global data source collection, obtain a JDBC connection from the data source, receive the parameter set, execute the database query statement, and obtain the result set; S27: Extract column type information from the result set and convert the column type information into a tag string; Specifically, the process involves: retrieving metadata from the result set, iterating through each row of the metadata to obtain column names and data type information, and storing this information in the column type information; The column type set is a key-value pair set, where the key is the column name and the value is the data type of the column. S28: The server system saves the unique ID, the corresponding standard data interface configuration information, and the tag string to the database, and returns the successful saving response data to the front-end interface; The server system associates the generated unique ID with the corresponding standard data interface configuration information and saves it in the database via a JDBC connection, then creates a successful save response and returns it to the front-end interface.

6. The method for converting different database query statements into a standard data interface as described in claim 1, characterized in that: Step S33 specifically involves: the server system creating a second execution parameter and iterating through each element of the parameter information list in the standard data interface configuration information. During the iteration process, the second execution parameter is preprocessed synchronously. The second execution parameter is a key-value pair parameter, where the key is the parameter name and the value is the parameter value, and both the key and the value are empty by default.

7. The method for converting different database query statements into a standard data interface as described in claim 6, characterized in that: The preprocessing specifically includes the following steps: S331: Perform key matching judgment on the request parameters; If the key of the request parameter is the parameter code of the current parameter information, then the value of the request parameter is obtained according to the parameter code, and the value of the request parameter is used as the intermediate parameter value; If the key of the request parameter is not the parameter code of the current parameter information, then the default parameter value of the current parameter information will be used as the intermediate parameter value. S332: Determine the parameter format conversion function for parameter information; If the parameter format conversion function is not empty, the function executor is called, and the intermediate parameter value is passed in to obtain the converted parameter value. The parameter code is used as the key and the converted parameter value is used as the value to insert it into the second execution parameter. If the parameter format conversion function for the parameter information is empty, then the parameter code is used as the key and the value of the request parameter is used as the value, and inserted into the second execution parameter.

8. The method for converting different database query statements into a standard data interface as described in claim 1, characterized in that: The specific details of the security procedures in steps S25 and S34 are as follows: Use regular expressions to parse all parameter codes in the form of {parameter code} in the database query statement and store them in the parameter code set. Iterate through each parameter code in the parameter code set and retrieve the corresponding value from the second execution parameter based on the current parameter code, and store the value in the parameter set. Use regular expressions to replace all {parameter code} forms in the database query statement with parameterized placeholders to obtain a pre-compiled database query statement.

9. The method for converting different database query statements into a standard data interface as described in claim 1, characterized in that: In step S36, the specific method of encapsulation is as follows: The database is retrieved based on the unique ID, and the corresponding tag string is deserialized into a set of key-value pairs, i.e., column type information. Iterate through the data in each row of the dynamic result set and the data in each column of the current row, then get the column names from the above key-value pair set, and get the column values ​​from the currently iterated column based on the obtained column names; Key-value pairs are constructed using column names as keys and column values ​​as values. These key-value pairs are then stored in a key-value pair collection. Finally, the key-value pair collections for each row are stored in a List collection to form a data result set, i.e., the data information. The column type information and data information are combined to form a standard result set.

10. The method for converting different database query statements into a standard data interface as described in claim 1, characterized in that: In step S37, the determination of the temporary result processing method is as follows: If the temporary result processing method does not exist, the result processing method will be obtained from the standard data interface configuration information; If a temporary result processing method exists and is not "no processing", then select and call the corresponding result processor according to the result processing method, pass the standard result set to the result processor, and return the processed custom result set; If a temporary result processing method exists and is set to not process, then the standard result set is returned directly.

Images