CN110083650B

CN110083650B - Metadata self-discovery-based automatic generation method for data query interface

Info

Publication number: CN110083650B
Application number: CN201910337144.9A
Authority: CN
Inventors: 陈昊; 王鹏; 于会游; 姜玉峰; 滕姿; 李栋; 杜浩; 唐丽娜; 饶定远; 陈燕秋; 闵圣捷; 杨飞; 丁晓宇; 董一舟
Original assignee: Cetc Kehuayun Information Technology Co ltd; Zhongdianke Jiaxing Novel Wisdom City Technology Development Co ltd
Current assignee: Cetc Kehuayun Information Technology Co ltd; Zhongdianke Jiaxing Novel Wisdom City Technology Development Co ltd
Priority date: 2019-04-25
Filing date: 2019-04-25
Publication date: 2023-06-30
Anticipated expiration: 2039-04-25
Also published as: CN110083650A

Abstract

The automatic generation method of the data query interface based on metadata self-discovery comprises the following steps: the metadata management system automatically discovers the table and the table structure in the database and stores the table and the table structure in the service database; the user selects a data table, configures inquiry fields and parameters, and stores related parameters in a data inquiry system; the system generates an SQL sentence template according to the data query parameter list selected by the user, and the template can accept parameters and operators appointed by the user as parameters; the system generates a corresponding URL address and adds a route forwarding rule and the API mapping; when the interface is used for inquiring data, the application calls the API through the appointed URL; checking the legitimacy of the input parameters according to the parameter list corresponding to the URL; if the parameters are legal, generating SQL sentences by using the parameters to the stored SQL sentence templates; and calling the database to inquire the data, serializing the result data according to the JSON format, and returning the data in a data stream mode.

Description

Metadata self-discovery-based automatic generation method for data query interface

Technical Field

The invention belongs to the technical field of databases, and particularly relates to a metadata self-discovery-based automatic generation method and system for a data query interface.

Background

Along with the continuous development of informatization construction, data are continuously increased, and the demand for data query is more and more increased. The prior art can generate a query interface by writing sql statements according to a fixed data structure. However, when the data structure is changed and different query parameters are needed, only the sql regeneration interface can be rewritten, so that great repeated development workload is brought.

Disclosure of Invention

The invention provides a metadata self-discovery-based automatic generation method and system for a data query interface, which are used for solving the problem that the existing data query interface needs to be repeatedly developed.

The embodiment of the invention discloses a data query interface automatic generation method based on metadata self-discovery, which comprises the following steps:

step 1, a metadata management system automatically discovers a table and a table structure in a database and stores the table and the table structure in a service database;

step 2, the user selects a data table, configures inquiry fields and parameters, and stores related parameters in a data inquiry system;

step 3, the system generates an SQL sentence template according to the data query parameter list selected by the user, and the template can accept parameters and operators designated by the user as parameters;

step 4, the system generates a corresponding URL address and adds a route forwarding rule and the API mapping;

step 5, repeating the steps 2 to 4, and repeatedly creating a plurality of APIs for different tables and different query parameters;

step 6, when the interface is used for inquiring data, the application calls the API through the appointed URL;

step 7, checking the legality of the input parameters according to the parameter list corresponding to the URL;

step 8, if the parameters are legal, using the parameters to generate SQL sentences with the saved SQL sentence templates;

and 9, calling database query data, serializing the result data according to the JSON format, and returning the data in a data stream mode.

The beneficial effects of the invention include:

the workload of manually and repeatedly writing SQL sentences is reduced;

adapting to the scene of data structure change, supporting the dynamic generation of a query interface;

the method adapts to the scene of the change of the query parameters and supports the dynamic assembly of sql statements.

Drawings

The above, as well as additional purposes, features, and advantages of exemplary embodiments of the present invention will become readily apparent from the following detailed description when read in conjunction with the accompanying drawings. Several embodiments of the present invention are illustrated by way of example, and not by way of limitation, in the figures of the accompanying drawings and in which:

FIG. 1 is a schematic diagram of an automatic generation system for a data query interface based on metadata self-discovery according to one embodiment of the present invention.

Description of the embodiments

According to one or more embodiments, as shown in fig. 1, a method for automatically generating a data query interface based on metadata self-discovery includes the following steps:

step 1, the metadata management system automatically discovers the table and the table structure in the database and stores the table and the table structure in the service database,

step 1.1, registering data source link information of data to be queried in a metadata management system;

step 1.2, the metadata management system automatically scans the database at fixed time by configuring a timing task;

step 1.3, automatically discovering a table and a table structure of a data source, wherein the table and the table structure comprise a table name, a field type and a field remark;

step 1.4 the data found in step 1.3 are saved in a business database,

step 2, the user selects the data table, configures the query field and the parameters, stores the related parameters in the data query system,

step 2.1, obtaining data table information stored by a metadata management system, and presenting the existing tables and table structures to a data user in a list form by the system;

step 2.2, the user selects a table which needs to automatically generate a data query API, and selects a field and a parameter rule which run in the API for transmitting query parameters;

the step 2.3 system saves the relevant parameters to a database,

step 3, the system generates an SQL sentence template according to the data query parameter list selected by the user, and the template can accept parameters and operators appointed by the user as parameters;

step 7, the system checks the legality of the input parameters according to the parameter list corresponding to the URL;

step 8, if the parameters are legal, generating SQL sentences by using the parameters on the stored SQL sentence templates;

and 9, invoking database query data, serializing the result data according to the JSON format, and returning the data in a data stream mode.

The embodiment provides a method for automatically generating a query interface without re-writing SQL when the data structure is changed and different query parameters are needed.

In accordance with one or more embodiments, as shown in FIG. 1, a system for automatically generating a data query interface based on metadata self-discovery, the system comprising: a metadata management system, a data interface system and a data query system,

the functions of the metadata management system include:

registering a data source, regularly scanning the data source, acquiring all tables and fields of the acquired tables, and generating data source information, tables and table structures;

the functions of the data interface system include:

acquiring all tables according to the data source information, the tables and the table structures, acquiring the table structures through selecting the tables, generating SOL templates after configuring data query parameters, generating URLs, and establishing URL-API mapping by route forwarding;

the data query system functions include:

checking the validity of the URL, generating SQL sentences according to parameters if the parameter rule is valid, calling the database to inquire the data, and returning the data through the data stream.

In the several embodiments provided in this application, it should be understood that the disclosed systems, devices, and methods may be implemented in other manners. For example, the apparatus embodiments described above are merely illustrative, e.g., the division of the units is merely a logical function division, and there may be additional divisions when actually implemented, e.g., multiple units or components may be combined or integrated into another system, or some features may be omitted or not performed. In addition, the coupling or direct coupling or communication connection shown or discussed with each other may be an indirect coupling or communication connection via some interfaces, devices, or elements, or may be an electrical, mechanical, or other form of connection.

The integrated units, if implemented in the form of software functional units and sold or used as stand-alone products, may be stored in a computer readable storage medium. Based on such understanding, the technical solution of the present invention is essentially or a part contributing to the prior art, or all or part of the technical solution may be embodied in the form of a software product stored in a storage medium, comprising several instructions for causing a computer device (which may be a personal computer, a server, or a network device, etc.) to perform all or part of the steps of the method according to the embodiments of the present invention. And the aforementioned storage medium includes: a U-disk, a removable hard disk, a Read-Only Memory (ROM), a random access Memory (RAM, random Access Memory), a magnetic disk, or an optical disk, or other various media capable of storing program codes.

While the invention has been described with reference to certain preferred embodiments, it will be understood by those skilled in the art that various changes and substitutions of equivalents may be made and equivalents will be apparent to those skilled in the art without departing from the scope of the invention. Therefore, the protection scope of the invention is subject to the protection scope of the claims.

Claims

1. The automatic generation method of the data query interface based on the metadata self-discovery is characterized by comprising the following steps:

step 1, a metadata management system automatically discovers a table and a table structure in a database and stores the table and the table structure in a service database, wherein the step 1 comprises the following steps:

step 1.2, the metadata management system automatically scans the database at regular time by configuring a timing task;

step 1.4, the data found in the step 1.3 are stored in a service database;

step 2, a user selects a data table, configures query fields and parameters, and stores related parameters in a data query system, wherein the step 2 comprises:

step 2.1, acquiring data table information stored by a metadata management system, and presenting the existing tables and table structures to a data user in a list form by the system;

step 2.3, storing the relevant parameters into a database;

step 3, the system generates an SQL sentence template according to the data query parameter list selected by the user, and the template receives parameters and operators designated by the user as parameters;