A method for converting different database query statements into a standard data interface

Abstract

The application discloses a method for converting different database query statements into a standard data interface. The method comprises the following steps: S1: configuring standard data interface configuration information and sending the same to a server system; a user configures the standard data interface configuration information through a front-end interface and sends the same to the server system; S2: checking the standard data interface configuration information and saving the same to a database; the server system receives and checks the configuration information, generates a unique ID after the checking is passed, binds the unique ID, and saves the same to the database; S3: calling the standard data interface; in response to an interface calling request, the configuration information is queried according to the unique ID in the request, request parameters are processed to create execution parameters, a query statement is safely executed, and a result set is encapsulated into a standard format and then returned. Through the configuration and automation process, the application realizes the quick and safe conversion of heterogeneous database query statements into a standard data interface, and greatly improves the development efficiency and system security.

Description

Technical Field

[0001] This invention relates to the field of computer software technology, and in particular to a method for converting different database query statements into a standard data interface. Background Technology

[0002] With the rapid development of the internet and enterprise informatization, users' demand for enterprise reports is constantly increasing. Developers face a large number of report development tasks, and among these tasks, writing database query statements is one of the core tasks.

[0003] However, in actual development, these database query statements often come from various heterogeneous databases, such as relational databases, non-relational databases, and various data warehouses. These databases have different syntax specifications, data structures, and access methods. In addition, each query statement has different parameters, and the format of the parameters is not the same. For example, for date parameters, some are in the format yyyy-MM-dd, while others are in the format yyyyMMdd. Secondly, developers need to manually write a lot of glue code every time they reuse a query statement.

[0004] Therefore, several obvious drawbacks have emerged: First, development efficiency is low, with a lot of energy being wasted on repetitive low-level coding; second, code versatility is poor, with each query requiring dedicated code, making it difficult to reuse; third, system maintenance is difficult, as any change to the underlying data source or query logic will trigger a chain reaction in the code, which is not only prone to errors but also accumulates a heavy technical debt. Summary of the Invention

[0005] The purpose of this invention is to solve the above-mentioned problems by proposing a method for converting different database query statements into a standard data interface.

[0006] To achieve the above objectives, the following technical solution was adopted:

[0007] A method for converting different database query statements into a standard data interface includes the following steps:

[0008] S1: Configure the standard data interface configuration information and send it to the server system;

[0009] Includes the following sub-steps:

[0010] S11: Specify the standard data interface name;

[0011] S12: Select the data source name;

[0012] Select from the dropdown menu in the front-end interface, where the data source name corresponds to the database connection.

[0013] S13: Write the database query statement;

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

[0015] The database query statements include, but are not limited to, stored procedures, select statements, with...cte...select statements, or query statements supported by the database.

[0016] S14: Generate a list of parameter information;

[0017] Specifically, by parsing the database query statement, the parameter placeholders contained therein are identified, the parameter placeholders are extracted as parameter codes, and corresponding parameter information is generated based on the parameter codes. All the parameter information is stored in a set to form a parameter information list.

[0018] The parameter information list consists of multiple parameter information entries.

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

[0020] S15: Configuration parameter information;

[0021] Specifically, the user-configurable parameter information includes parameter name, parameter value, and at least one of the following: parameter format conversion function and parameter description.

[0022] S16: Select the result processing method;

[0023] Specifically, users can choose to customize the result processing method or default to no processing.

[0024] S17: Save the standard data interface configuration information and send it to the server system via an HTTP request;

[0025] Specifically, the standard data interface configuration information is sent to the server system via an HTTP request created by the front-end interface.

[0026] S2: Verify the standard data interface configuration information and save it to the database;

[0027] Includes the following sub-steps

[0028] S21: The server system receives standard data interface configuration information;

[0029] The server system receives HTTP requests and parses them to obtain standard data interface configuration information.

[0030] S22: Verify the standard data interface configuration information;

[0031] 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.

[0032] S23: The server system generates a unique ID and establishes a JBDC connection;

[0033] 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.

[0034] The unique ID corresponds one-to-one with the standard data interface configuration information;

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

[0036] S24: Create the first execution parameter, iterate through the elements of the parameter information list, and perform preliminary preprocessing on the first execution parameter;

[0037] The server system creates the first execution parameter, then iterates through each element in the parameter information list of the standard data interface, i.e., the parameter information, and performs preliminary preprocessing on the first execution parameter.

[0038] Specifically, the preliminary preprocessing process is as follows: when the server traverses the parameter information, 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 to insert it 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 to insert it into the first execution parameter.

[0039] 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.

[0040] 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;

[0041] Specifically, the details of the safety procedures are as follows:

[0042] The system uses regular expressions to parse all parameter codes in the form of {parameter code} in the database query statement and stores them in a parameter code set. It then iterates through each parameter code in the set and retrieves the corresponding value from the second execution parameter based on the current parameter code, storing that value in the parameter set.

[0043] By using regular expressions, all {parameter code} forms in a database query statement are replaced with parameterized placeholders, resulting in a pre-compiled database query statement.

[0044] 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;

[0045] The global data source set is a key-value pair collection of type Map, created when the server system starts, with the data source name as the key and the data source as the value.

[0046] S27: Extract column type information from the result set and convert the column type information into a tag string;

[0047] Specifically, the extraction method is as follows: obtain metadata from the result set, traverse each row of the metadata to obtain column name and data type information, and store them in the column type information.

[0048] 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.

[0049] 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;

[0050] The server system associates the generated unique ID with the corresponding standard data interface configuration information and tag string, saves it in the database via JDBC connection, and creates a successful save response to return to the front-end interface.

[0051] S3: Call the standard data interface;

[0052] Includes the following sub-steps:

[0053] S31: The server system parses the call request and extracts the unique ID, request parameters, and temporary result processing method from it;

[0054] Specifically, when a user sends a call request, the server system receives and parses the call request, extracting a unique ID, request parameters, and temporary result processing method.

[0055] The request parameters are the parameters passed in by the user when calling the standard data interface.

[0056] The request parameters are key-value pairs of Map data type, where the key is the parameter name and the value is the parameter value.

[0057] The temporary result processing method is a string passed in by the user when calling the standard data interface.

[0058] S32: Retrieve the corresponding standard data interface configuration information from the database based on the unique ID;

[0059] Specifically, based on the unique ID obtained in step S31, the corresponding standard data interface configuration information is queried from the database.

[0060] S33: Create a second execution parameter, traverse the parameter information list, and preprocess the second execution parameter;

[0061] Specifically, the server system creates a second execution parameter and iterates 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.

[0062] The second execution parameter is a required parameter used when executing the query statement. It is a key-value pair parameter of type Map, where the key is the parameter name and the value is the parameter value. Both the key and the value are initially empty.

[0063] Specifically, the preprocessing includes the following steps:

[0064] S331: Perform key matching judgment on the request parameters;

[0065] 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;

[0066] 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.

[0067] S332: Determine the parameter format conversion function for parameter information;

[0068] 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.

[0069] 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.

[0070] S34: Using the same security processing steps as step S25, 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;

[0071] 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;

[0072] S36: Encapsulate the dynamic result set to obtain the standard result set;

[0073] Specifically, the encapsulation method is as follows:

[0074] The database is used to retrieve the corresponding tag string based on the unique ID, and the tag string is deserialized into a set of key-value pairs, i.e., column type information.

[0075] Iterate through the data in each row of the dynamic result set and the data in each column of the current row. Then, retrieve the column name from the column type set and, based on the retrieved column name, retrieve the column value from the currently iterated column.

[0076] 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.

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

[0078] S37: Determine the processing method for temporary results and return a standard result set or a custom result set based on the determination result.

[0079] Specifically, the determination of the temporary result processing method is as follows:

[0080] If the temporary result processing method does not exist, the result processing method will be obtained from the standard data interface configuration information;

[0081] 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;

[0082] If a temporary result processing method exists and is set to not process, then the standard result set is returned directly.

[0083] Compared with the prior art, the beneficial effects of the present invention are as follows:

[0084] This invention transforms data interface development from a traditional hard-coding model to a configuration-based model. Users no longer need to write complex business logic code, database connection code, or HTTP interface code; they only need to write core query statements and complete parameter configuration to quickly generate a usable interface. This significantly shortens the development, testing, and deployment cycle, reduces the technical requirements for developers, and enables agile development and rapid delivery of data interfaces.

[0085] This invention adopts a fully configuration-driven architecture, allowing users to customize interface information, write arbitrary query statements, finely define the processing rules for each parameter, and flexibly choose the result processing method according to specific business needs. New data interfaces can be extended without modifying the core system code, thus improving the adaptability and practicality of this invention in practical applications.

[0086] This invention allows users to write configuration information and then convert the database query statements according to the configuration information, effectively avoiding the differences in connection methods and syntax details between different underlying databases. Through a unified configuration process, multiple data sources can be easily accessed and managed, and standard, consistent data service interfaces are provided to the outside world, simplifying the system architecture and operation and maintenance difficulty in complex data environments.

[0087] 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

[0088] 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.

[0089] 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.

[0090] 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.

[0091] 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

[0092] 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.

[0093] like Figure 1 As shown, a method for converting different database query statements into a standard data interface includes the following steps:

[0094] S1: Configure the standard data interface configuration information and send it to the server system;

[0095] 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.

[0096] like Figure 2 As shown, the specific process includes the following sub-steps:

[0097] S11: Specify the standard data interface name;

[0098] 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.

[0099] S12: Select the data source name;

[0100] Users select a data source name, such as "mysql-production-db", from a dropdown menu in the data source configuration area of ​​the front-end interface. Each data source name corresponds to a database connection; the data source name is a pre-configured abstract identifier containing all the necessary information for the database connection. By selecting a data source name, the server system can quickly locate and establish a connection to the target database when subsequently calling standard data interface configuration information.

[0101] S13: Write the database query statement;

[0102] Users can write database query statements in the query statement editing area. By writing different query statements, they can create data interfaces with different functions to meet the needs of various business scenarios, from simple data retrieval to complex statistical analysis.

[0103] Parameters in a database query statement are represented in the form of {parameter code}.

[0104] Database query statements include, but are not limited to, stored procedures, select statements, complex query statements with general expressions (such as with...cte...select), or query statements supported by the database.

[0105] S14: Generate a list of parameter information;

[0106] Specifically, after the user clicks the "Generate with one click" 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 therein, that is, the parameters in the database query statement, extracts the parameter placeholders as parameter codes, generates the corresponding parameter information based on the parameter codes, and stores all the parameter information into a collection to form a parameter information list.

[0107] The parameter information list consists of multiple parameter information entries.

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

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

[0110] The parameter name is the Chinese name of the parameter code, which is to help developers understand the actual name of the parameter code.

[0111] The formatting convention for parameter conversion functions is: function name ($this, params), where $this is a keyword representing the parameter value whose format is to be converted.

[0112] The purpose of parameter format conversion functions is to convert non-standard parameter formats into standard ones, such as converting a date parameter in the format yyyyMMdd into the standard yyyy-MM-dd format. By standardizing parameter formats, developers do not need to worry about parameter format handling issues, reducing usage costs.

[0113] The parameter value uses the default value of the parameter code, and can also receive external requests to modify the default value, thus realizing dynamic parameters.

[0114] The parameter description provides detailed information about the parameter code and its specific usage, making it easy for users to understand.

[0115] By generating a parameter information list following the steps described above, a standardized and unified specification can be formed, laying the foundation for subsequent automated processing and reducing the need for manual intervention. Users only need to fill in the configuration according to the information dimensions in the list, which lowers the threshold for parameter configuration, avoids the error-prone problems of manual creation, and improves configuration efficiency and accuracy.

[0116] S15: Configuration parameter information;

[0117] The parameter information list generated in step S14 is an automatically generated page from 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, allowing the user to supplement the parameter differentiation information according to actual business needs, thus avoiding the contradiction that "the system's forced logic cannot meet the business scenario".

[0118] Specifically, the user-configurable parameter information includes parameter name, parameter value, and at least one of the following: parameter format conversion function and parameter description.

[0119] S16: Select the result processing method;

[0120] Users can select the result processing method according to their own needs from the result processing configuration area.

[0121] Specifically, users can choose to customize the result processing method or default to no processing.

[0122] Database query interfaces typically return results in their native format, such as directly mapping database field names and data types. However, downstream systems, such as front-end pages and reporting tools, have different requirements for the result format. If the interface does not support the selection of result processing methods, downstream systems need to write a lot of secondary processing code for the unified interface results, which leads to reduced integration efficiency and poor compatibility. This also avoids the risk of data inconsistency caused by multiple conversions.

[0123] Therefore, in this invention, when the downstream system has special result processing requirements, the user can customize the result processing method according to the requirements; when the downstream system does not have special requirements, the result processing method is set to no processing by default.

[0124] S17: Save the standard data interface configuration information and send it to the server system via an HTTP request;

[0125] Specifically, when the user clicks the "Save" button, the front-end interface sends all standard data interface configuration information to the API endpoint specified by the server system through an HTTP request created by the front-end interface.

[0126] S2: Verify the standard data interface configuration information and save it to the database;

[0127] like Figure 3 As shown, the specific process includes the following sub-steps.

[0128] S21: The server system receives standard data interface configuration information;

[0129] The server system receives HTTP requests and parses the request body to obtain standard data interface configuration information. The server system is typically a backend application built using Java Web frameworks such as Spring Boot.

[0130] S22: Verify the standard data interface configuration information;

[0131] The server system performs syntax validation on the database query statements in the standard data interface configuration information using the functions provided by the database driver. If the syntax is correct, the validation passes and the process continues to step S23. If the syntax is incorrect, a validation failure response is created and returned to the front-end interface.

[0132] S23: The server system generates a unique ID and establishes a JBDC connection;

[0133] After successful verification, based on the standard data interface configuration information, the server system uses the Snowflake algorithm to generate a globally unique ID, which is then bound to this configuration information. A JDBC connection is then created to establish a session connection with the database.

[0134] Each unique ID corresponds one-to-one with the standard data interface configuration information, ensuring that the configuration information of each interface has a unique identifier, providing a core retrieval basis for subsequent operations.

[0135] JDBC connections are a standard API specification for interacting with relational databases and for establishing session connections with the database.

[0136] S24: Create the first execution parameter, iterate through the elements of the parameter information list, and perform preliminary preprocessing on the first execution parameter;

[0137] The server system creates a first execution parameter, which is a key-value pair parameter where the key is the parameter name and the value is the parameter value. Both the key and value are empty by default. Then, it iterates through each element in the parameter information list of the standard data interface, i.e., the parameter information, and performs preliminary preprocessing on the first execution parameter.

[0138] Specifically, the preliminary preprocessing process is as follows: When the server system traverses the parameter information, 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 to insert it 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 to insert it into the first execution parameter.

[0139] By creating the first execution parameter and performing preliminary preprocessing on it, a parameter set that simulates the real calling scenario as closely as possible and has undergone preliminary preprocessing is generated. This ensures that even default values ​​need to be processed by the format conversion function, so that subsequent steps can be performed under parameter conditions that are closest to the real environment, thereby guaranteeing the high accuracy of the obtained column type information.

[0140] S25: Perform security processing on the first execution parameters and database query statement after preliminary preprocessing to generate a secure, pre-compiled set of parameters and database query statement;

[0141] Specifically, the details of the safety procedures are as follows:

[0142] The system uses regular expressions to parse all parameter codes in the form of {parameter code} in the database query statement and stores them in a parameter code set. It then iterates through each parameter code in the set and retrieves the corresponding value from the second execution parameter based on the current parameter code, storing that value in the parameter set.

[0143] By using regular expressions, all {parameter code} forms in a database query statement are replaced with parameterized placeholders, resulting in a pre-compiled database query statement.

[0144] The parameter set and database query statement obtained through security processing can prevent SQL injection, a high-risk vulnerability, and also solve the problem of syntax errors caused by special characters. It is a key technical link to ensure the security of interface calls and the stability of the database. Especially in the scenario of public interface facing external users, this processing mechanism can significantly reduce the risk of data leakage and damage.

[0145] 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;

[0146] The global data source collection is a key-value pair collection of type Map, created when the server system starts, with the data source name as the key and the data source as the value.

[0147] S27: Extract column type information from the result set and convert the column type information into a tag string;

[0148] Specifically, the metadata is retrieved from the result set, each row of the metadata is traversed to obtain the column name and data type information, which is stored in the column type information and then converted into a tag string, such as a JSON string.

[0149] A column type collection is a collection of key-value pairs, where the key is the column name and the value is the data type of the column, such as int, varchar(20), decimal(10,2), etc.

[0150] Steps S24, S25, S26, and S27 achieve the pre-processing and transparency of the data structure. By utilizing the first execution parameters from the preliminary preprocessing, a real query execution is simulated, allowing for an accurate and prior understanding of the final data structure returned by the query, including the fields and their types. Users immediately know the interface's return structure after configuration, eliminating the need for a "blind" initial call or documentation review. This solves the "information asymmetry" problem and enhances the system's predictability and usability.

[0151] 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;

[0152] The server system associates the generated unique ID with the corresponding standard data interface configuration information and tag string, and saves it in the database via JDBC connection. Then, it creates a successful save response and returns it to the front-end interface.

[0153] S3: Call the standard data interface;

[0154] When a user issues a request, that is, to call a standard database interface via an HTTP request, the user also sends a unique ID, request parameters, and temporary result processing method to the server system.

[0155] like Figure 4 As shown, the specific steps include the following:

[0156] S31: The server system parses the call request and extracts the unique ID, request parameters, and temporary result processing method from it;

[0157] Specifically, the server system receives and parses the call request, extracting the unique ID, request parameters, and temporary result processing method from the request body.

[0158] Request parameters are the parameters passed in by the user when calling the standard data interface.

[0159] The request parameters are key-value pairs of Map data type, where the key is the parameter name and the value is the parameter value.

[0160] The temporary result processing method is a string passed in by the user when calling the standard data interface. Passing a temporary result processing method when calling the standard data interface allows for dynamic adaptation to temporary and customized result format requirements without modifying the interface's default configuration. Since real-world business scenarios may involve numerous short-term and special temporary needs, passing in the temporary result processing method allows users to directly specify the result processing logic through parameters, without modifying the default configuration in the database, thus avoiding the risk of affecting the overall system by modifying the default configuration.

[0161] S32: Retrieve the corresponding standard data interface configuration information from the database based on the unique ID;

[0162] Specifically, the server system queries the database for the corresponding standard data interface configuration information based on the unique ID obtained from the parsing.

[0163] S33: Create a second execution parameter, traverse the parameter information list, and preprocess the second execution parameter;

[0164] Specifically, the server system creates a second execution parameter, which is a required parameter used when executing the query statement. This second execution parameter is a key-value pair parameter of type Map, where the key is the parameter name and the value is the parameter value, and both the key and value are initially empty. Then, each element of the parameter information list in the standard data interface configuration information is traversed, and during this traversal, the second execution parameter is preprocessed synchronously.

[0165] Specifically, the preprocessing includes the following steps:

[0166] S331: Perform key matching judgment on the request parameters;

[0167] The server system checks the key of the request parameters to determine whether the request parameters contain a key that is the same as the parameter code in the current parameter information;

[0168] If it exists, find the corresponding value from the request parameters and use that value as the intermediate parameter value.

[0169] If it does not exist, the default parameter value of the current parameter information will be used as the intermediate parameter value.

[0170] Since request parameters are created by the user and passed to the server system, they have higher priority than default parameter information. Therefore, it is necessary to check whether the request parameter exists. If it exists, the request parameter will be used to overwrite the parameter information. If it does not exist, the default parameter value will continue to be used. Users can choose whether to create request parameters according to their needs. If the key of the request parameter is invalid, 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 steps.

[0171] S332: Determine the parameter format conversion function for parameter information;

[0172] The server system checks whether the parameter format conversion function in the current parameter information is empty.

[0173] If not empty, 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, and then inserted into the second execution parameter.

[0174] 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, and then inserted into the second execution parameter.

[0175] The core purpose of this judgment is to address the question of "whether parameter values ​​need processing," that is, to determine whether the obtained raw parameter values ​​need to be customized. By first determining whether there is a transformation requirement, and then deciding whether to perform the transformation, raw inputs from different sources and with inconsistent formats are converted into a unified and standardized format required by database queries, thus fully demonstrating the capability of "user-defined extensions."

[0176] Step S33 creates a second execution parameter and preprocesses it, thereby automatically and intelligently transforming the externally input, potentially non-standard or incomplete, original request parameters into a fully ready, secure, reliable set of "second execution parameters" that can be directly used for database operations.

[0177] S34: Using the same security processing steps as step S25, perform security processing on the second execution parameters and database query statements to generate a secure, pre-compiled set of parameters and database query statements;

[0178] The user-facing configuration format is converted into the execution format required by the underlying database driver. This ensures that user input is treated as data and never parsed as SQL code by completely separating the query structure from parameter values. Furthermore, pre-compilation mechanisms often lead to performance improvements.

[0179] 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;

[0180] Specifically, the server system retrieves the corresponding data source from the Qunar data source collection based on the data source name in the configuration information, and obtains a new JDBC connection from that data source. Based on this connection and the pre-compiled database query statement, a PreparedStatement object is created. The values ​​from the pre-compiled parameter set are set into this object in sequence, and finally, the database query statement is executed to obtain a dynamic result set. This completes the final database interaction, retrieves the raw data, and realizes the transformation from configuration information to real data.

[0181] Step S35, as the core of executing the database query, can transform the preprocessed query logic into actual data results in an efficient, safe, and reliable manner. It optimizes performance through connection pool reuse, ensures result quality through a verification mechanism, and provides a data foundation for subsequent encapsulation and processing.

[0182] S36: Encapsulate the dynamic result set to obtain the standard result set;

[0183] The server system encapsulates the dynamic result set using the set of column type information pre-acquired and stored in the database in step S26.

[0184] Specifically, the encapsulation method is as follows:

[0185] The database is used to retrieve the corresponding tag string based on the unique ID, and the tag string is deserialized into a set of key-value pairs, i.e., column type information.

[0186] Iterate through the data in each row of the dynamic result set and the data in each column of the current row. Then, retrieve the column name from the known column type information set, and based on the retrieved column name, retrieve the column value from the currently traversed column.

[0187] Construct key-value pairs of type Map using column names as keys and column values ​​as values, store the constructed key-value pairs in a key-value pair collection, and finally store the key-value pair collection of each row into a List collection to form a data result set, i.e., data information.

[0188] The column type information and data information are combined into a standard result set.

[0189] S37: Determine the processing method for temporary results and return a standard result set or a custom result set based on the determination result.

[0190] Specifically, the determination of the temporary result processing method is as follows:

[0191] If the temporary result processing method does not exist, the result processing method will be obtained from the standard data interface configuration information.

[0192] If a temporary result processing method exists and is not set to "not processed", and the user provides a custom configuration for the temporary result processing method, then the corresponding result processor will be called according to the selected result processing method. The standard result set will be passed to the result processor, and the custom result set returned by it will be used as the final response result.

[0193] If a temporary result processing method exists and is not processed, the standard result set is directly serialized into JSON format and then returned via an HTTP response.

[0194] Since the temporary result processing method is created by the user and passed to the server system, it has higher priority than the result processing method in the standard data interface configuration information. Therefore, it is necessary to check whether the temporary result processing method exists. If it does not exist, the result processing method is searched for in the configuration information in the database; if it exists, the temporary result processing method is used. When passing in the temporary result processing method, the user can choose the temporary result processing method according to their needs, or the default is not to process it.

[0195] If the temporary result processing method is inconsistent with the preset method in the configuration information, the temporary result processing method shall prevail.

[0196] By allowing users to configure different result processing methods in the configuration information, this invention can adapt to the data format requirements of various downstream systems. Whether it's JSON expected by the front-end page, XML required for integration with legacy systems, or even specific encrypted formats, all can be easily achieved through configuration. This demonstrates the powerful adaptability of this invention as a data service middleware layer. This mechanism allows users to embed any complex post-processing logic without modifying the core code.

[0197] The above description is merely a preferred embodiment of this application and is not intended to limit the scope of this application. Those skilled in the art may find other optimizations and additional functions in this application. Any modifications, equivalent substitutions, or improvements made within the spirit and principles of this application should be included within the scope of protection of this application.

Claims

1. A method of converting different database query statements into a standard data interface, characterized by: The method comprises the following steps: S1: configuring standard data interface configuration information and sending it to a server system; The method comprises the following sub-steps: S11: writing a standard data interface name; S12: selecting a data source name; S13: writing a database query statement; The parameters in the database query statement exist in the form of {parameter code}; S14: generating a parameter information list; The parameter information list is composed of multiple pieces of parameter information; The parameter information comprises parameter code, parameter name, parameter format conversion function, parameter value and parameter description; S15: configuring parameter information; S16: selecting a result processing mode; S17: saving the standard data interface configuration information and sending it to the server system through an HTTP request; S2: checking the standard data interface configuration information and saving it to a database; The method comprises the following sub-steps S21: the server system receives the standard data interface configuration information; S22: checking the standard data interface configuration information; S23: the server system generates a unique ID and establishes a JDBC connection; The unique ID corresponds to the standard data interface configuration information one by one; S24: creating a first execution parameter and traversing the elements of the parameter information list to preliminarily preprocess the first execution parameter; S25: performing security processing on the first execution parameter after preliminary processing and the database query statement to generate a safe, pre-compiled parameter set and a database query statement; S26: according to the data source name in the standard data interface configuration information, obtaining the corresponding data source from a global data source set, obtaining a JDBC connection from the data source, receiving the parameter set, executing the database query statement, and obtaining a result set; S27: extracting column type information in the result set and converting the column type information into a mark string; S28: the server system saves the unique ID, the corresponding standard data interface configuration information and the mark string into the database, and returns response data of successful saving to the front-end interface; S3: calling a standard data interface; The method comprises the following sub-steps: S31: the server system parses a calling request to extract a unique ID, request parameters and a temporary result processing mode therefrom; The request parameters are parameters input by a user when calling the standard data interface; The request parameters are a kind of key-value pair parameters, wherein the key is a parameter name and the value is a parameter value; The temporary result processing mode is a string input by the user when calling the standard data interface; S32: according to the unique ID, the corresponding standard data interface configuration information is queried from the database; S33: creating a second execution parameter, traversing the parameter information list, and preprocessing the second execution parameter; The second execution parameter is a necessary parameter used when executing a query statement; S34: performing security processing on the second execution parameter and the database query statement to generate a safe, pre-compiled parameter set and a database query statement; 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, 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; The global data source set is a key-value pair set created when the server system starts, with the data source name as the key and the data source as the value; S36: The dynamic result set is encapsulated to obtain the standard result set; The standard result set is composed of column type information and data information; S37: The temporary result processing mode is judged, and the standard result set or the custom result set is returned according to the judgment result; 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 not processed, the corresponding result processor is selected and called according to the result processing mode, the standard result set is input into the result processor, and the processed custom result set is returned; If the temporary result processing mode exists and is not processed, the standard result set is directly returned.

2. The method for converting different database query statements into a standard data interface as claimed in claim 1, wherein: Step S14 is specifically to parse the database query statement, identify the parameter placeholder contained therein, extract the parameter placeholder as a parameter code, and generate corresponding parameter information based on the parameter code, store all parameter information into a set, and form a parameter information list.

3. The method for converting different database query statements into a standard data interface as claimed in claim 1, wherein: 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 claimed in claim 1, wherein: In step S16, the user can select to customize the result processing mode or default to not process.

5. The method for converting different database query statements into a standard data interface as claimed in claim 1, wherein: The specific content of step S2 includes: S21: The server system receives the standard data interface configuration information; The server system receives and parses the HTTP request to obtain the 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 verified, if the syntax is correct, the verification is passed, step S23 is executed, if the syntax is incorrect, a verification failure response data is created and returned to the front-end interface; S23: The server system generates a unique ID and establishes a JDBC connection; After verification, based on the standard data interface configuration information, the server system generates a unique ID through UUIDv7 or snowflake algorithm, and creates a JDBC connection to establish a session connection with the database; 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 preliminary preprocessing includes the following steps: S21: The server system traverses each element in the parameter information list in the standard data interface, and synchronously judges whether the parameter format conversion function of the current parameter information is empty. If the parameter format conversion function is empty, the function executor is called, the parameter value of the current parameter is transmitted, 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; The first execution parameter is a key-value pair parameter, wherein the key is a parameter name and the value is a parameter value, and the key and the value are both empty by default; S25: The first execution parameter after the preliminary preprocessing and the database query statement are subjected to security processing to generate a safe and precompiled parameter set and a database query statement; 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, 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; S27: The column type information in the result set is extracted and converted into a mark string; Specifically, the metadata is obtained from the result set, each row of the metadata is traversed to obtain the column name and the data type information, and the column type information is stored; The column type information is a key-value pair set, the key is a column name, and the value is a data type of the column; 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 the successful saving to the front-end interface; The server system associates the generated unique ID with the corresponding standard data interface configuration information, saves them in the database through the JDBC connection, and returns the response data of the successful saving to the front-end interface.

6. The method for converting different database query statements into a standard data interface according to claim 1, wherein: S33: The server system creates a second execution parameter, and traverses each element in the parameter information list in the standard data interface configuration information, and synchronously preprocesses the second execution parameter during the traversal. The second execution parameter is a key-value pair parameter, wherein the key is a parameter name and the value is a parameter value, and the key and the value are both empty by default.

7. The method for converting different database query statements into a standard data interface according to claim 6, wherein: 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 the 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: The parameter format conversion function of the parameter information is judged; If the parameter format conversion function of the parameter information is not empty, a function executor is invoked and the intermediate parameter value is passed in to obtain the converted parameter value, the parameter code is taken as the key, the converted parameter value is taken as the value, and the converted parameter value is 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, the value of the request parameter is taken as the value, and the value is inserted into the second execution parameter.

8. The method for converting different database query statements into a standard data interface as claimed in claim 1, wherein: The specific content of the security processing in steps S25 and S34 is: All parameter codes in the form of {parameter code} in the database query statement are parsed using a regular expression and stored in a parameter code set, each parameter code in the parameter code set is traversed, the corresponding value is queried from the second execution parameter according to the current parameter code, and the value is stored in a parameter set; All {parameter code} forms in the database query statement are replaced with parameterized placeholders using a regular expression to obtain a precompiled database query statement.

9. The method for converting different database query statements into a standard data interface as claimed in claim 1, wherein: In step S36, the specific method of the packaging is: 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; The data of each row in the dynamic result set and the data of each column of the current row are traversed, the column name is obtained from the above key-value pair set, and the column value is obtained from the currently traversed column based on the obtained column name; The key-value pair is constructed with the column name as the key and the column value as the value, the constructed key-value pair is stored in the 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; The column type information and the data information are combined to form a standard result set.

Images