Data interface generation method and readable storage medium

Abstract

The invention provides a data interface generation method and a readable storage medium, and the data interface generation method comprises the steps: 1, connecting a database, and loading and displaying tables and fields in the database; 2, filling in basic information of the service, and selecting tables and fields in a database to obtain request input parameters and response output parameters of the service; generating an executable SQL script in combination with the service type in the definition of the service basic information; 3, based on the selection of the data type, the index, the remark and the input and output parameter field of the selected field, combining whether the index is determined and whether the selected field is in the request input parameter or the response output parameter, perfecting the executable SQL script and generating a corresponding annotation; 4, according to the input parameter list and the output parameter list, the executable SQL script and the annotation are converted into a dynamic SQL and persistent in a memory; and 5, generating a new service according to the service basic information, the request input parameter, the response output parameter and the dynamic SQL, and synchronously generating an interface document.

Description

Technical Field

[0001] The present invention relates to the technical field of data sharing, and in particular to a data interface generating method and a readable storage medium. Background Art

[0002] Some enterprises and institutions (such as hospitals) purchase and use multiple digital products from multiple vendors. Data interoperability between these products is difficult. Existing technology often involves negotiating interface specifications, agreeing on interface names and parameters, developing various interfaces on demand, and then invoking these interfaces to connect data. This approach is labor-intensive, time-consuming, and incurs additional modification costs. Interface operation and maintenance is also cumbersome and lacks unified management. Summary of the Invention

[0003] The purpose of the present invention is to provide a data interface generation method and a readable storage medium to solve the problems of complexity and high cost in the existing customized development and operation and maintenance of data interfaces between products.

[0004] To solve the above technical problems, the present invention provides a data interface generation method, which includes:

[0005] Step 1: Connect to the database and load and display the tables and fields in the database;

[0006] Step 2: Fill in the basic service information, select the tables and fields in the database, and obtain the service request input parameters and response output parameters; generate an executable SQL script based on the service type defined in the basic service information; when multiple heterogeneous databases are selected, the generated executable SQL script includes multiple intermediate SQL scripts and a merge query SQL script, and generates temporary views and materialized views based on the scenario;

[0007] Step 3: Based on the data type, index, remark, and input and output parameter field selection of the selected field, combined with whether the index is used and whether the selected field is included in the request input parameter or the response output parameter, the executable SQL script is improved and corresponding comments are generated;

[0008] Step 4: According to the input parameter list and the output parameter list, the executable SQL script and the comment are converted into dynamic SQL and persisted in memory;

[0009] Step 5: Generate a new service based on the basic service information, the request input parameters, the response output parameters and the dynamic SQL, and simultaneously generate an interface document.

[0010] Optionally, the data interface generating method further includes:

[0011] Step 6: Use the built-in testing framework to generate default test parameters and predefined data structures based on service input parameters, and perform functional and performance testing on the generated interfaces.

[0012] Step 7: Automatically identify query statements involving sensitive fields through dynamic SQL runtime parsing and generate a desensitized execution path audit report;

[0013] Step 8: Circuit breaker protection: By analyzing the query logs, a single user's high-frequency table scan operations on a single table or multiple tables are marked as high-risk, the user account with continuous high risk is locked, and subsequent requests from the user account with continuous high risk are rejected.

[0014] Optionally, the step 4 includes:

[0015] Generate a request body according to the input parameter list and the output parameter list;

[0016] The request body is converted into dynamic SQL that can be recognized by the database through a template engine.

[0017] Optionally, the content of the request input parameter includes the field name, Chinese comment and type of the request input parameter; the content of the response output parameter includes the field name, Chinese comment and type of the response output parameter.

[0018] Optionally, during the process of filling in the basic service information in step 2, context-aware field completion technology is used to dynamically recommend options based on the relationships between tables and columns in the library and between tables during the selection process.

[0019] Optionally, the basic information of the service includes a service number and a service name.

[0020] Optionally, after step 4, the data interface generation method further includes: adjusting the generated dynamic SQL; and supporting full join matching or left join matching based on other tables or views.

[0021] Optionally, after step three, the data interface generation method further includes: loading and displaying the input parameter list and the output parameter list, allowing the parameters of the input parameter list and the output parameter list to be maintained individually or in batches.

[0022] Optionally, after generating the dynamic SQL in step 4, the data interface generation method further includes: verifying the generated dynamic SQL through an SQL syntax verification function.

[0023] In order to solve the above technical problems, the present invention also provides a readable storage medium having a program stored thereon, characterized in that when the program is executed, the steps of the data interface generation method described above are implemented.

[0024] In summary, in the data interface generation method and readable storage medium provided by the present invention, the data interface generation method includes: step 1: connecting to the database, loading and displaying the tables and fields in the database; step 2: filling in the basic information of the service, selecting the tables and fields in the database, and obtaining the request input parameters and response output parameters of the service; generating an executable SQL script in combination with the service type in the definition of the basic information of the service; wherein when there are multiple source heterogeneous databases selected, the generated executable SQL script includes multiple intermediate SQL scripts and a merge query SQL script, and generates temporary views and materialized views according to the scenario; step 3: based on the data type, index, remark and input and output parameter field selection of the selected field, combined with whether to index and whether the selected field is in the request input parameter or in the response output parameter, improve the executable SQL script and generate corresponding comments; step 4: according to the input parameter list and the output parameter list, convert the executable SQL script and the comments into dynamic SQL and persist them in memory; step 5: generate a new service based on the basic information of the service, the request input parameter, the response output parameter and the dynamic SQL, and simultaneously generate an interface document.

[0025] With this configuration, operators can view and select data for interoperability through a visual display, completing the definition and release of interfaces. No programming experience is required for the entire interface release process. The platform automatically generates services based on the selected data and generates interface documentation to facilitate data user integration. Data interfaces are quickly generated, cost-effective, and easy to maintain, facilitating unified management. BRIEF DESCRIPTION OF THE DRAWINGS

[0026] Those skilled in the art will appreciate that the drawings are provided for a better understanding of the present invention, but do not constitute any limitation on the scope of the present invention.

[0027] Figure 1 It is a flowchart of a data interface generation method according to an embodiment of the present invention.

[0028] Figure 2 Schematic diagram of a loading display table and field attributes contained in the table according to an embodiment of the present invention.

[0029] Figure 3 It is a schematic diagram of loading and displaying a view and field attributes contained in the view according to an embodiment of the present invention.

[0030] Figure 4 This is a schematic diagram of obtaining request input parameters and response output parameters according to an embodiment of the present invention.

[0031] Figure 5 It is a schematic diagram of generating an input parameter list and an output parameter list according to an embodiment of the present invention.

[0032] Figure 6 This is a schematic diagram of automatically generating dynamic SQL according to an embodiment of the present invention.

[0033] Figure 7 It is a schematic diagram of loading and displaying an interface document according to an embodiment of the present invention.

[0034] Figure 8 It is a schematic diagram of exporting an interface document according to an embodiment of the present invention. DETAILED DESCRIPTION

[0035] To make the objects, advantages, and features of the present invention more clearly apparent, the present invention is further described below in conjunction with the accompanying drawings and specific embodiments. It should be noted that the drawings are all in a very simplified form and are not drawn to scale. They are only used to conveniently and clearly assist in illustrating the purposes of the embodiments of the present invention. In addition, the structures shown in the drawings are often part of the actual structure. In particular, different drawings may need to illustrate different focuses and sometimes use different scales.

[0036] As used in the present invention, the singular forms "a", "an", "one" and "the" include plural objects, the term "or" is generally used in a sense including "and / or", the term "several" is generally used in a sense including "at least one", and the term "at least two" is generally used in a sense including "two or more". In addition, the terms "first", "second" and "third" are used for descriptive purposes only and are not to be understood as indicating or implying relative importance or implicitly indicating the number of the indicated technical features. Therefore, the features defined as "first", "second" and "third" may explicitly or implicitly include one or at least two of the features, and for those of ordinary skill in the art, the specific meanings of the above terms in the present invention can be understood according to the specific circumstances. In addition, directional terms such as above, below, up, down, upward, downward, left, right, etc. are used relative to the exemplary embodiments as they are shown in the figures, with the upward or upper direction being toward the top of the corresponding figure and the downward or lower direction being toward the bottom of the corresponding figure.

[0037] The present invention aims to provide a data interface generation method and a readable storage medium to solve the problems of complexity and high cost in the custom development and operation and maintenance of existing inter-product data interfaces.

[0038] Please refer to Figure 1 , an embodiment of the present invention provides a data interface generation method, which includes:

[0039] Step 1 S1: Connect to the database and load and display the tables and fields in the database;

[0040] Step 2 S2: Fill in basic service information, select tables and fields in the database, and obtain service request input parameters and response output parameters; generate an executable SQL script based on the service type defined in the basic service information; when multiple heterogeneous databases are selected, the generated executable SQL script includes multiple intermediate SQL scripts and a merge query SQL script, and generates temporary views and materialized views based on the scenario;

[0041] Step 3 S3: Generate an executable SQL script and corresponding comments based on the data type, index, remark, and input and output parameter field selection of the selected field, combined with whether the selected field is indexed and whether the selected field is in the request input parameter or the response output parameter;

[0042] Step 4 S4: According to the input parameter list and the output parameter list, the executable SQL script and the annotation are converted into dynamic SQL and persisted in memory;

[0043] Step 5 S5: Generate a new service based on the basic service information, the request input parameters, the response output parameters and the dynamic SQL, and simultaneously generate an interface document.

[0044] The data interface generation method provided in this embodiment can be implemented based on a platform device. The following description uses a medical service middle platform as an example of a platform device.

[0045] Step 1 S1 can connect to the database through the platform device and load and display the data in the database. Optionally, in one embodiment, the data in the database includes a table and field attributes in the table. After the table is selected, the field attributes contained in the table can be further loaded and displayed, such as Figure 2 In another embodiment, the data in the database includes views and field attributes in the views. After a view is selected, the field attributes contained in the view can be further loaded and displayed, such as Figure 3 shown.

[0046] The database in step 1 S1 can manage its data source, which can be data from open business libraries, data lakes, and data center libraries. It supports connecting to mainstream databases, domestic databases, large databases, and other types, such as Oracle, Mysql, SqlServer, Cache, Oceanbase, and Hive. By selecting the database type and utilizing the intelligent configuration parsing module, it automatically identifies and adapts the connection parameters and drivers of different databases to ensure the efficiency and accuracy of the connection process.

[0047] In step 2 S2, firstly, the interface service to be created can be set according to the actual business needs, and the basic service information can be filled in, such as Figure 4As shown, it shows that the name of the interface service to be created is "Query Patient Information Service". Then you can select the corresponding table and field in the database, for example, select the "Patient Basic Information Table" in the database, and the platform can display the field attributes in the table. Preferably, the field attributes can be divided into a request body and a response body, which respectively correspond to the request input parameters and response output parameters of the interface service to be created. Furthermore, combined with the service type in the basic information definition of the service (such as query, add, delete, modify, and increase and change), an executable SQL script is automatically generated for the subsequent step four S4. At the same time, users are supported to customize and modify SQL scripts to meet personalized needs. Optionally, the interface service can be automatically created after clicking Save or Save and Publish.

[0048] A temporary view is a logical view in the database. Essentially, it serves as a filter condition for a SQL query statement and does not actually store data. A materialized view, similar to a physical table, is also based on a SQL query definition but stores the query results persistently. This approach, based on multiple intermediate SQL scripts and combined query SQL scripts, may not offer a significant speed advantage for simple searches. However, when selecting multiple heterogeneous databases, the efficiency advantage of step two is significant.

[0049] Preferably, the step 3 S3, the step 4 S4, and the step 5 S5 are automatically executed based on the platform device. With this configuration, the user only needs to view and select the data for intercommunication in step 1 S1 and step 2 S2, and the platform device can automatically generate a callable interface based on the data requirements and automatically generate the interface document to complete the definition and release of the interface. The entire interface release process does not require programming experience, which facilitates the connection of data users.

[0050] In step 3 S3, if Figure 5 As shown, the platform device reads the content of the request input parameter of the interface service to be created in step 2 S2 to generate an input parameter list, and reads the content of the response output parameter to generate an output parameter list. Based on the data type, index, and remarks of the selected field and the selection of the input and output parameter fields, combined with whether the index is used and whether the selected field is in the request input parameter or the response output parameter, an executable SQL script and corresponding comments are generated.

[0051] Optionally, the content of the request input parameter includes the field name, Chinese annotation and type of the request input parameter; the content of the response output parameter includes the field name, Chinese annotation and type of the response output parameter. In step 3 S3, the content of the request input parameter and the response output parameter supports Chinese and English input.

[0052] Optionally, during the process of filling in the basic service information in step 2 S2, context-aware field completion technology is used to dynamically recommend options based on the associations between tables and columns in the library and between tables during the selection process.

[0053] After step three S3, the data interface generation method further includes: loading and displaying the input parameter list and the output parameter list, allowing the parameters of the input parameter list and the output parameter list to be maintained individually or in batches. The message structure of the input parameter list and the output parameter list can be loaded and displayed for user preview. The parameter content of the input parameter list and the output parameter list supports individual or batch maintenance.

[0054] In step 4 S4, if Figure 6 As shown, the platform device can automatically generate dynamic SQL based on the input parameter list and the output parameter list. In an exemplary embodiment, the step 4 S4 includes:

[0055] Step S41: Generate a request body according to the input parameter list and the output parameter list;

[0056] Step S42: Convert the request body into dynamic SQL recognizable by the database through a template engine.

[0057] In one example, step 4 (S4) can be implemented using a template engine (such as FreeMarker). FreeMarker's TPL template is a data structure conversion tool that follows FreeMarker rules during configuration. It is essentially a text string. When the program starts, the service's primary key (the request body) and template content are retrieved from the database and persisted in the execution engine's memory. FreeMarker syntax conversion is performed on the execution text, where the syntax is essentially extracted from the request body and converted into SQL syntax statements recognizable by the database.

[0058] For example, when the json in the request body (i.e. the input parameter list and the output parameter list) is:

[0059] {

[0060] "id":"1001",

[0061] "dictKey":"A001",

[0062] "dictValue":"test value 001"

[0063] }

[0064] The template extracted in memory is (at this time, SQL is in a database-unrecognizable state):

[0065] insertinto test4(my_id,my_key,my_value)values(

[0066] ′${EntryBody.obj().value("$.id")!}′,

[0067] ′${EntryBody.obj().value("$.dictKey")!}′,

[0068] ′${EntryBody.obj().value("$.dictValue")!}' )

[0070] The result after conversion: (At this time, sql is in a state that can be recognized by the database)

[0071] insertinto test4(my_id,my_key,my_value)values(

[0072] '1001',

[0073] 'A001',

[0074] 'Test value 001' )

[0076] The entrybody is the service input request header. Subsequent paths must conform to JSON syntax. XML input request headers must conform to XPath. Freemarker syntax supports defining constants and writing logic such as if-else statements, fully meeting low-code requirements. This logic is used to dynamically generate SQL statements.

[0077] Furthermore, after generating dynamic SQL in step 4 S4, SQL statements can be edited and improved. Freemarker templates can generate a variety of versatile SQL statements on demand, similar to low-code platforms. For example, when a certain input parameter is passed, another complex SQL statement is executed. For example, in a paginated query scenario, when the page number and page number are passed, the backend SQL statement will splice the offset calculation statement to complete the paginated query SQL execution. If the page number and page number are not passed, the SQL statement will execute normally.

[0078] Optionally, after step 4 S4, the data interface generation method further includes: adjusting the generated dynamic SQL to support full join matching (inner join) or left join matching (left join) based on other tables or views. The user can adjust the dynamic SQL according to actual conditions, including but not limited to inner join, left join, and other tables or views.

[0079] Optionally, after the dynamic SQL is generated in step 4, the data interface generation method further includes: verifying the generated dynamic SQL through the SQL syntax verification function. In an exemplary embodiment, for example, the generated dynamic SQL can be verified through the SQL syntax verification function (using code to implement the data source connection information, execute with the executable SQL into the server and capture the test execution results), check the grammatical correctness and performance optimization of the SQL statement, mainly to automatically check the integrity of the statement structure, the correctness of punctuation marks, automatically check whether the data types operated in the SQL statement are consistent, check whether the references to tables and columns comply with business logic and data relationships, confirm whether the user executing the SQL statement has the corresponding authority, check the SQL statement, prevent SQL injection attacks, etc. SQL injection is a common security vulnerability. Attackers obtain or modify data in the database by constructing malicious SQL statements. SQL injection can be prevented by filtering and escaping user input, using parameterized queries, etc.

[0080] Optionally, the data interface generation method supports advanced configurations such as cache management. For scenarios with extremely high response time requirements, such as high-frequency user query operations, redis is used to store data in the server's memory. The high-speed response capability of the memory cache enables the system to process more requests per unit time, thereby improving the overall throughput. The memory cache intercepts repeated requests, reduces the number of database accesses, and reduces the database's CPU, disk IO and network overhead, significantly improving system performance, reducing costs, and enhancing the system's stability and fault tolerance to cope with complex scenarios.

[0081] In step five S5, the basic service information of the interface service to be created includes the service number and service name, and these basic information can be obtained through user input. Then the platform device can generate a new service based on the basic information of the service, the request input parameter, the response output parameter and the dynamic SQL, and simultaneously generate the interface document. In an exemplary embodiment, step five S5 can automatically generate a detailed PDF interface document through a built-in tpl template based on the basic service information such as the service code and parameter information. The interface document includes the functional description of the interface, request parameters, response parameters, return examples and other contents, which are convenient for data users to connect. Establish an association mechanism between the service and the interface document, and automatically and synchronously update the interface document when the service code or parameter information changes to ensure the real-time and accuracy of the document.

[0082] Furthermore, the generated interface documentation can be loaded for viewing, such as Figure 7 It can also be exported, such as Figure 8 shown.

[0083] Optionally, after step 5 S5, the data interface generation method further includes:

[0084] Step 6 S6: Generate default test parameters and predefined data structures based on service input parameters through the built-in test framework, and perform functional and performance tests on the generated interface;

[0085] Step 7 S7: Automatically identify query statements involving sensitive fields through dynamic SQL runtime parsing and generate a desensitized execution path audit report;

[0086] Step eight S8: fuse protection; by analyzing the query log, a single user's high-frequency table scan operation for a single table or multiple tables is marked as high-risk, the user account with continuous high risk is locked, and subsequent requests from the user account with continuous high risk are rejected.

[0087] In step 6 (S6), the platform device can optionally use a built-in automated testing framework and the default values ​​in the service parameter design and the input parameters generated by the structure to conduct comprehensive functional and performance testing on the generated interface. The automated testing framework supports multiple test scenarios and assertion methods, enabling rapid identification of defects and issues in the interface. By analyzing the interface's input, output, and business logic, it automatically generates high-coverage test cases, improving testing efficiency and quality.

[0088] Furthermore, when exporting the interface document, an offline interface description document is dynamically generated through a template engine. The platform device can, for example, dynamically generate the offline interface description document through Freemarker, and the offline interface description document can be in PDF format, which is convenient for users to view.

[0089] In step seven S7, the sensitive field identification technology is used to identify sensitive columns in the database and the sensitive keyword dictionary, combined with regular constraints, to desensitize sensitive data in large texts when output.

[0090] In step 8 (S8), compared to real-time frequency detection and calculation for each request, this step does not affect the efficiency of the original interface. Furthermore, the analysis of high-risk accounts can refer to a more refined high-risk knowledge base and run independently, ensuring efficient processing and minimizing the program's impact on normal business requests. This high-risk knowledge base can be built, for example, by analyzing and integrating query logs to identify user accounts that frequently scan a single or multiple tables, such as by creating a whitelist or blacklist.

[0091] An embodiment of the present invention further provides a readable storage medium having a program stored thereon, and when the program is executed, the steps of the data interface generating method described above are implemented.

[0092] In summary, in the data interface generation method and readable storage medium provided by the present invention, the data interface generation method includes: step 1: connecting to the database, loading and displaying the tables and fields in the database; step 2: filling in the basic information of the service, selecting the tables and fields in the database, and obtaining the request input parameters and response output parameters of the service; generating an executable SQL script in combination with the service type in the definition of the basic information of the service; wherein when there are multiple source heterogeneous databases selected, the generated executable SQL script includes multiple intermediate SQL scripts and a merge query SQL script, and generates temporary views and materialized views according to the scenario; step 3: based on the data type, index, remark and input and output parameter field selection of the selected field, combined with whether to index and whether the selected field is in the request input parameter or in the response output parameter, improve the executable SQL script and generate corresponding comments; step 4: according to the input parameter list and the output parameter list, convert the executable SQL script and the comments into dynamic SQL and persist them in memory; step 5: generate a new service based on the basic information of the service, the request input parameter, the response output parameter and the dynamic SQL, and simultaneously generate an interface document. With this configuration, operators can view and select data for interoperability through a visual display, completing the definition and release of interfaces. No programming experience is required for the entire interface release process. The platform automatically generates services based on the selected data and generates interface documentation to facilitate data user integration. Data interfaces are quickly generated, cost-effective, and easy to maintain, facilitating unified management.

[0093] It should be noted that the above embodiments can be combined with each other. The above description is only a description of the preferred embodiments of the present invention and does not limit the scope of the present invention. Any changes and modifications made by ordinary technicians in the field of the present invention based on the above disclosure are within the scope of protection of the present invention.

Claims

1. A data interface generation method, characterized in that: include: Step 1: Connect to the database and load and display the tables and fields in the database; Step 2: Fill in the basic service information, select the table and field in the database, obtain the service request input parameters and response output parameters; generate an executable SQL script based on the service type defined in the basic service information; When multiple heterogeneous databases are selected, the generated executable SQL script includes multiple intermediate SQL scripts and a merged query SQL script, and generates temporary views and materialized views according to the scenario; Step 3: Based on the data type, index, remark, and input and output parameter field selection of the selected field, combined with whether the index is used and whether the selected field is included in the request input parameter or the response output parameter, the executable SQL script is improved and corresponding comments are generated; Step 4: According to the input parameter list and the output parameter list, the executable SQL script and the comment are converted into dynamic SQL and persisted in memory; Step 5: Generate a new service based on the basic service information, the request input parameters, the response output parameters and the dynamic SQL, and simultaneously generate an interface document.

2. The data interface generation method according to claim 1, characterized in that: The data interface generation method further includes: Step 6: Use the built-in testing framework to generate default test parameters and predefined data structures based on service input parameters, and perform functional and performance testing on the generated interfaces. Step 7: Automatically identify query statements involving sensitive fields through dynamic SQL runtime parsing and generate a desensitized execution path audit report; Step 8: Circuit breaker protection: By analyzing the query logs, a single user's high-frequency table scan operations on a single table or multiple tables are marked as high-risk, the user account with continuous high risk is locked, and subsequent requests from the user account with continuous high risk are rejected.

3. The data interface generation method according to claim 1, characterized in that: The step 4 includes: generating a request body according to the input parameter list and the output parameter list; The request body is converted into dynamic SQL that can be recognized by the database through a template engine.

4. The data interface generation method according to claim 1, characterized in that: The content of the request input parameter includes the field name, Chinese comment and type of the request input parameter; the content of the response output parameter includes the field name, Chinese comment and type of the response output parameter.

5. The data interface generation method according to claim 1, characterized in that: In the process of filling in the basic service information in step 2, through the context-aware field completion technology, options are dynamically recommended during the selection process based on the relationships between tables in the library, columns in the tables, and between tables.

6. The data interface generation method according to claim 1, characterized in that: The basic information of the service includes the service number and service name.

7. The data interface generation method according to claim 1, characterized in that: After step 4, the data interface generation method further includes: adjusting the generated dynamic SQL; and supporting full join matching or left join matching based on other tables or views.

8. The data interface generation method according to claim 1, characterized in that: After step three, the data interface generation method further includes: loading and displaying the input parameter list and the output parameter list, allowing the parameters of the input parameter list and the output parameter list to be maintained individually or in batches.

9. The data interface generation method according to claim 1, characterized in that: After generating the dynamic SQL in step 4, the data interface generation method further includes: verifying the generated dynamic SQL through an SQL syntax verification function.

10. A readable storage medium having a program stored thereon, characterized in that: When the program is executed, the steps of the data interface generation method according to any one of claims 1 to 9 are implemented.

Images