CN113590648A - Data query method, device, equipment and readable storage medium - Google Patents
Data query method, device, equipment and readable storage medium Download PDFInfo
- Publication number
- CN113590648A CN113590648A CN202110872979.1A CN202110872979A CN113590648A CN 113590648 A CN113590648 A CN 113590648A CN 202110872979 A CN202110872979 A CN 202110872979A CN 113590648 A CN113590648 A CN 113590648A
- Authority
- CN
- China
- Prior art keywords
- field
- interface
- sql template
- condition
- preset
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 238000000034 method Methods 0.000 title claims abstract description 46
- 238000012795 verification Methods 0.000 claims description 8
- 230000004048 modification Effects 0.000 claims description 2
- 238000012986 modification Methods 0.000 claims description 2
- 239000002699 waste material Substances 0.000 abstract description 7
- 238000012423 maintenance Methods 0.000 abstract description 6
- 238000010586 diagram Methods 0.000 description 12
- 230000006870 function Effects 0.000 description 7
- 238000004590 computer program Methods 0.000 description 4
- 230000009471 action Effects 0.000 description 3
- 239000008186 active pharmaceutical agent Substances 0.000 description 3
- 238000012217 deletion Methods 0.000 description 2
- 230000037430 deletion Effects 0.000 description 2
- 238000009795 derivation Methods 0.000 description 2
- 230000008569 process Effects 0.000 description 2
- 238000006467 substitution reaction Methods 0.000 description 2
- OYPRJOBELJOOCE-UHFFFAOYSA-N Calcium Chemical compound [Ca] OYPRJOBELJOOCE-UHFFFAOYSA-N 0.000 description 1
- 229910052791 calcium Inorganic materials 0.000 description 1
- 239000011575 calcium Substances 0.000 description 1
- 238000006243 chemical reaction Methods 0.000 description 1
- 238000004891 communication Methods 0.000 description 1
- 230000007246 mechanism Effects 0.000 description 1
- 230000003287 optical effect Effects 0.000 description 1
- 230000003068 static effect Effects 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2455—Query execution
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F9/00—Arrangements for program control, e.g. control units
- G06F9/06—Arrangements for program control, e.g. control units using stored programs, i.e. using an internal store of processing equipment to receive or retain programs
- G06F9/46—Multiprogramming arrangements
- G06F9/54—Interprogram communication
- G06F9/547—Remote procedure calls [RPC]; Web services
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- Software Systems (AREA)
- Mathematical Physics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The application provides a data query method, a device, equipment and a readable storage medium, comprising the following steps: receiving an Application Program Interface (API) calling request, wherein the API calling request comprises: at least one interface field and at least one interface field value corresponding to the at least one interface field; judging whether at least one interface field comprises a necessary condition field required by a preset SQL template; presetting an SQL template corresponding to the API; if yes, generating a target SQL statement according to a preset SQL template and at least one interface field value, and searching target data corresponding to the SQL statement from a database. The SQL sentences in the preset SQL template are dynamically modified according to the API call request, so that the problems of resource waste and high maintenance cost caused by creating a plurality of SQL templates are solved, and the flexibility of the SQL template is effectively improved.
Description
Technical Field
The present application relates to the field of computer software, and in particular, to a data query method, apparatus, device, and readable storage medium.
Background
An Application Programming Interface (API) is a predefined Interface (e.g. function, HTTP Interface) or a convention for linking different components of a software system. The API is used to provide a set of routines that applications and developers can access based on certain software or hardware without having to access source code or understand the details of the internal working mechanisms. Structured Query Language (SQL) is a special-purpose programming Language, a database Query and programming Language, used to access data and Query, update, and manage relational database systems.
The current API calling method is that each service API of multiple service APIs in the same (or multiple) data table in the database is written with an SQL template (for example, 5 service APIs require 5 SQL templates), and the condition field and the return field in the SQL template corresponding to each service API are fixed, so that the user terminal needs to transmit the preset parameters for the fixed condition field corresponding to each service API, thereby bringing a great deal of workload to the management and maintenance of the SQL template and the front-end service API. Therefore, neither the API parameters passed in by the user nor the return fields returned last are preset by the SQL template, and the flexibility is poor.
Disclosure of Invention
In view of the above, embodiments of the present application provide a data query method, apparatus, device and readable storage medium, so as to solve the technical problem of poor flexibility of an SQL template.
In order to achieve the above purpose, the technical solutions provided in the embodiments of the present application are as follows:
in a first aspect, an embodiment of the present application provides a data query method, including: receiving an Application Program Interface (API) calling request, wherein the API calling request comprises: at least one interface field and at least one interface field value corresponding to the at least one interface field; judging whether at least one interface field comprises a necessary condition field required by a preset SQL template; presetting an SQL template corresponding to the API; if yes, generating a target SQL statement according to a preset SQL template and at least one interface field value, and searching target data corresponding to the SQL statement from a database.
By receiving an application program interface API call request, if a plurality of interface fields of the API call request comprise all necessary condition fields in a preset SQL template, the SQL sentence in the preset SQL template is dynamically modified according to the API call request to generate a target SQL sentence, and then the target SQL sentence is sent to a user terminal according to a return result value corresponding to a return field in the target SQL sentence, so that the problems of resource waste and high maintenance cost caused by the creation of a plurality of SQL templates are avoided, and the flexibility of the SQL template is effectively improved.
Optionally, the preset SQL template includes all condition fields in the database; generating a target SQL statement according to a preset SQL template and at least one interface field value, wherein the generation comprises the following steps: judging whether any condition field of all condition fields corresponds to an interface field of the API call request or not; if not, converting the preset SQL template into a syntax tree, and then deleting syntax nodes in the syntax tree according to the condition field to obtain the target SQL statement.
Through the mode, if the condition fields of the preset SQL template comprise other condition fields besides the interface fields in the application program interface API calling request, the preset SQL template is converted into the syntax tree, and the other condition fields are deleted to form the target SQL statement, so that the problems of resource waste and high maintenance cost caused by the creation of a plurality of SQL templates are solved, and the flexibility of the SQL template is effectively improved.
Optionally, before deleting the syntax node in the syntax tree according to the condition field, the method further includes: replacing the placeholder corresponding to the condition field with a special value; deleting the grammar nodes in the grammar tree according to the condition field, which comprises the following steps: and deleting the syntax nodes with special values corresponding to the condition fields in the syntax tree.
Optionally, the preset SQL template comprises a requirement field in the database; generating a target SQL statement according to a preset SQL template and at least one interface field value, wherein the generation comprises the following steps: aiming at any interface field of all interface fields in the API call request, judging whether the interface field appears in a condition field of a preset SQL template; if not, adding the interface field in the condition field of the preset SQL template, and replacing the placeholder corresponding to the condition field with the interface field value corresponding to the interface field.
In the above manner, for an interface field that does not appear in a condition field in a preset SQL template, the interface field is added to the preset SQL template to form a target SQL statement, and then the target SQL statement is executed to obtain a return result value corresponding to a return field in the SQL statement. Therefore, the SQL template does not need to store all condition fields in the database, but only needs to store necessary condition fields, the waste of storage space is effectively reduced, meanwhile, the condition fields are dynamically added through presetting the SQL template, the target SQL statement is formed, the problems of resource waste and high maintenance cost caused by creating a plurality of SQL templates are avoided, and the flexibility of the SQL template is effectively improved.
Optionally, the preset SQL template includes all the return fields in the database; after the target field is obtained, aiming at any return field of all return fields in a preset SQL template, judging whether the return field appears in the target field; if not, deleting the return field in the preset SQL template.
Through the mode, the preset SQL template deletes the unnecessary return fields according to the return fields, namely the target fields, selected by the user terminal in a self-defined manner, so that the data return is more targeted, the query operation is not required to be executed on the result values of the unnecessary return fields of the user terminal, and the waste of network resources caused by the query of excessive fields is avoided.
Optionally, after receiving the application program interface API call request, before determining whether at least one interface field includes a necessary condition field required by the preset SQL template, the method further includes: judging whether signature verification and API interface authority authentication of the identity of the user pass or not; and if not, sending second prompt information, wherein the second prompt information is used for prompting signature verification and API interface authority authentication.
Signature verification and API interface authority authentication are carried out through the identity, calling of an illegal user to the API interface can be effectively prevented, and interface calling safety is improved.
In a second aspect, an embodiment of the present application provides a data query apparatus, including: a receiving module, configured to apply a program interface API call request, where the API call request includes: at least one interface field and at least one interface field value corresponding to the at least one interface field; the judging module is used for judging whether at least one interface field comprises a necessary condition field required by a preset SQL template; presetting an SQL template corresponding to the API; and the modification module is used for generating a target SQL statement according to the preset SQL template and at least one interface field value if at least one interface field comprises a necessary condition field required by the preset SQL template, and searching target data corresponding to the SQL statement from a database.
In a third aspect, an embodiment of the present application provides an electronic device, including: a processor, a memory, and a bus; the processor and the memory complete mutual communication through the bus; the memory stores program instructions executable by the processor, the processor calling the program instructions capable of performing the data query method as in the first aspect.
In a fourth aspect, embodiments of the present application provide a non-transitory computer-readable storage medium storing computer instructions for causing a computer to perform the data query method as in the first aspect.
In order to make the aforementioned objects, features and advantages of the present application more comprehensible, embodiments accompanied with figures are described in detail below.
Drawings
In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings that are required to be used in the embodiments of the present application will be briefly described below, it should be understood that the following drawings only illustrate some embodiments of the present application and therefore should not be considered as limiting the scope, and that those skilled in the art can also obtain other related drawings based on the drawings without inventive efforts.
Fig. 1 is a schematic flow chart of a data query method provided in an embodiment of the present application;
fig. 2 is a schematic flow chart of deleting a condition field according to an embodiment of the present application;
FIG. 3 illustrates an exemplary diagram of deleting syntax tree syntax nodes provided by an embodiment of the present application;
FIG. 4 is a flow chart illustrating adding a condition field according to an embodiment of the present application;
FIG. 5 is a flow chart illustrating deletion of a return field according to an embodiment of the present application;
FIG. 6 is a flow chart illustrating the addition of a return field according to an embodiment of the present application;
fig. 7 is a schematic structural diagram of a data query device according to an embodiment of the present application;
fig. 8 is a schematic structural diagram of an electronic device provided in an embodiment of the present application.
Detailed Description
Some terms in the embodiments of the present application will be explained below to facilitate understanding by those skilled in the art.
Syntax tree: the graphical representation of the sentence structure, which represents the derivation of the sentence, facilitates understanding the hierarchy of the sentence grammatical structure. In brief, a syntax tree is a tree formed when derivation is performed according to a certain rule.
antlr (other Tool for Language recognition), an open source parser which can automatically generate a syntax tree based on the input and display the syntax tree visually.
Apache calcium: an open source SQL analysis tool can analyze various SQL sentences into an abstract Syntax tree AST (abstract Syntax Tree), and the function of the open source SQL analysis tool is the same as that of antlr in the application.
It should be noted that the data query method provided in the embodiments of the present application may be executed by an electronic device, where the electronic device refers to a device terminal or a server having a function of executing a computer program, and the device terminal includes, for example: a smart phone, a Personal Computer (PC), a tablet computer, a Personal Digital Assistant (PDA), a Mobile Internet Device (MID), a network switch or a network router, and the like.
Before introducing the data query method provided by the embodiment of the present application, an application scenario applicable to the data query method is introduced, where the application scenario includes, but is not limited to: using the data query method to call a pre-developed API interface to enable the API interface to query field values in a database, where the database includes but is not limited to: mySQL database, oracle database, SQLserver database, hive database, spark-SQL database, presto database, clickhouse database and other databases for SQL query function; the interface calling method includes, but is not limited to, a calling method using HTTP/HTTPs, Remote Procedure Call (RPC).
The data query method is used in specific application scenarios, where the specific application scenarios are, for example: in online educational scenarios, the number of fields in the database is enormous, including: user account, user category, user name, user order number, reported course name, course time, teacher giving lessons, time giving lessons, etc. when the user needs to query data, the following situations may occur: in the first case, a user needs to input many information (i.e. an interface field and an interface field value corresponding to the interface field) to obtain information, such as an account number, an account name, an account category, and the like; in the second case, in order to avoid excessive information that needs to be input by the user, different SQL templates are created for various interface fields and combinations thereof, for example, an SQL template with the user name as a condition field, an SQL template with the account number as a condition field, an SQL template with the user name and the account number as condition fields, and the like are created, which causes expansion of the number of SQL templates and increases the difficulty in maintaining the API interface. Meanwhile, the information acquired by the user terminal has no pertinence, for example, the user wants to acquire the reported course name, but actually acquires the reported course name, the course time, the teacher giving lessons and the time giving lessons. The data query method can be used for creating a preset SQL template comprising necessary condition fields, adding the condition fields to modify the preset SQL template through the API call request of a user to obtain a target SQL statement each time the API interface is called, and then obtaining a corresponding return result value. Or, a preset SQL template including all the condition fields is created, and when the API interface is called, redundant condition fields in the preset SQL template are deleted according to the API calling request and the target fields of the user, and corresponding return result values are obtained.
Please refer to fig. 1, which is a schematic flow chart diagram of a data query method provided in the embodiment of the present application; the data query method mainly comprises the steps of receiving an Application Program Interface (API) calling request, if a plurality of interface fields of the API calling request comprise all necessary condition fields in a preset SQL template, dynamically modifying an SQL statement in the preset SQL template according to the API calling request to obtain a target SQL statement, and then sending a return result value corresponding to a return field in the target SQL statement to a user terminal. By the method, the problems of resource waste and high maintenance cost caused by the creation of a plurality of SQL (structured query language) templates are avoided, only one SQL template is needed for a plurality of (for example, 5) business APIs, the problem of expansion of the number of the SQL templates is solved, the flexibility of the SQL templates is effectively improved, and the data query method can comprise the following steps:
step S100: an Application Program Interface (API) call request is received.
In step S100, the API call request includes: the interface field and the interface field value corresponding to the interface field. The ways of receiving the API call request from the user terminal include, but are not limited to, the following: in the first way, the user enters an interface field and an interface field value corresponding to the interface field. For example, the user enters the user categories: student, account number: 001. in the second mode, a user inputs an interface field value according to the sequence of placeholders corresponding to specified condition fields in a preset SQL template, and the interface field value is converted into a form corresponding to the interface field and the interface field value corresponding to the interface field by the rear end. For example, the specified sequence of interface field values is user category, user account, the user only needs to input student, 001, and the backend will convert to "user category: student, account number: 001 ".
After step S100, step S200 is performed: and judging whether the at least one interface field comprises a necessary condition field required by the preset SQL template.
In the step S200, it should be noted that the preset SQL template corresponds to the API, and the required condition fields required by the preset SQL template may be one, multiple or none (otherwise, the where condition clause is deleted dynamically). For example, the condition fields in the data table of the database include user category, user account and account name. The preset SQL template can only set the user type as the necessary condition field, and can also set two condition fields of the user type and the user account as the necessary condition fields. Alternatively, the requirement field may not be set.
After step S200, if the determination result is yes, step S300 is executed to generate a target SQL statement according to the preset SQL template and the at least one interface field value.
In the step S300, the target SQL statement generating embodiments according to the preset SQL template and the at least one interface field value include, but are not limited to, the following:
in a first embodiment, for a case where the preset SQL template includes all condition fields in the database, if the determination result in the step S200 is yes, the preset SQL template is modified according to the interface field and the interface field value corresponding to the interface field to obtain the target SQL statement, including:
and S301, judging whether any condition field of all condition fields corresponds to an interface field of the API call request.
It should be noted that, in step S301, if the interface field of the API call request includes the interface fields except for all the condition fields in the database, the server may ignore the interface fields except for all the condition fields, or may send a prompt message to the API call end to prompt that the interface field of the API call request is out of the database.
After step S301, if the condition field does not correspond to the interface field of the API call request (i.e. the condition field is not included in the interface field of the API call request), step S302 is executed: and converting the preset SQL template into a syntax tree, and deleting syntax nodes in the syntax tree according to the condition field to obtain a target SQL statement.
The step S302 at least includes three ways:
in a first manner, please refer to a schematic flow chart of deleting a condition field provided in the embodiment of the present application shown in fig. 2; before the preset SQL template is converted into the syntax tree, replacing placeholders corresponding to condition fields in the preset SQL template with special values, and then deleting syntax nodes comprising the special values from the syntax tree after the conversion into the syntax tree; wherein the special value may be, for example, 4 consecutive special characters, etc. The embodiment includes:
step S303, if the condition field does not correspond to the interface field of the API call request (i.e. the condition field is not included in the interface field of the API call request), replacing the placeholder corresponding to the condition field in the preset SQL template with a special value.
And S304, if the condition field corresponds to the interface field of the API call request, directly replacing the placeholder corresponding to the condition field in the preset SQL template with the interface field value of the corresponding interface field.
The above step S303 or step S304 is repeatedly executed until all the condition fields in the data table (or data view) of the database are replaced, and the following step S305 can be executed. Since the last condition field in the loop in the data table may or may not correspond to the interface field of the API call request, step S305 may be after step S303 or after step S304.
After step S303 or step S304, step S305 is executed to convert the preset SQL template into a syntax tree, and delete the syntax node having the special value corresponding to the condition field in the syntax tree to obtain the target SQL statement.
In step S305, the syntax node includes: the interface field and the value corresponding to the interface field, the value corresponding to the interface field is a placeholder, a special character or an interface field value.
In the second mode, a preset SQL template is converted into a syntax tree, then, placeholders corresponding to condition fields in the syntax tree are replaced by special values, and then syntax nodes comprising the special values are deleted from the syntax tree. The embodiment includes:
step S306: and converting the preset SQL template into a syntax tree.
After step S306, step S307 is executed to replace the corresponding placeholder in the syntax tree with a special value if the condition field does not correspond to the interface field of the API call request (i.e. the condition field is not included in the interface field of the API call request).
And S307, if the condition field corresponds to the interface field of the API call request, directly replacing the placeholder corresponding to the condition field in the syntax tree with the interface field value of the corresponding interface field.
The above step S306 or step S307 is repeatedly performed until all the condition fields in the syntax tree are replaced, and the following step S308 can be performed. Since the last condition field in the loop in the data table may or may not correspond to the interface field of the API call request, step S308 may be after step S306 or after step S307.
After step S307 or step S306, step S308 is executed to delete the syntax node having the special value corresponding to the condition field in the syntax tree to obtain the target SQL statement.
In step S308, the syntax node includes: the interface field and the value corresponding to the interface field, the value corresponding to the interface field is a placeholder, a special character or an interface field value.
In a third mode, a condition field which cannot be found in an interface field of an API call request is directly deleted from a preset SQL template to obtain a target SQL statement, which specifically includes the following steps:
step S309, aiming at any condition field of all condition fields, judging whether the condition field corresponds to the interface field of the API call request.
After step S309, if the determination result is yes, step S310 is executed to replace the placeholder corresponding to the condition field with the interface field value corresponding to the interface field.
After step S310, if the determination result is negative, step S311 is executed to delete the condition field that cannot be found in the interface field of the API call request.
The above step S309 or step S310 is repeatedly executed until all condition fields in the data table (or data view) of the database are modified (including replacement or deletion), and then the following step S311 can be executed. Since the last condition field in the loop in the data table may or may not correspond to the interface field of the API call request, step S311 may be after step S309 or after step S310. After step S310 or step S309 is completed, step S311 is executed: if all the condition fields in the data table (or data view) are modified (including replaced or deleted), the target SQL statement is obtained.
The following describes how to generate the target SQL statement according to the preset SQL template and the at least one interface field value by a specific example in a first manner:
please refer to fig. 3, which illustrates an exemplary diagram of deleting syntax nodes of a syntax tree according to an embodiment of the present application; for example, in a case that the operational relationship between condition fields in the predetermined SQL template is sum, the condition fields in the SQL template include: user category, user name, user account. Among them, the user category is the requirement field. The interface field and the interface field value sent by the user terminal are as follows: user types are as follows: student, account number: 001. then, placeholders corresponding to the user categories in the preset SQL template are replaced by students, placeholders corresponding to the user accounts are replaced by 001, and placeholders corresponding to the user names are replaced by special values null. After the replacement, the SQL statements in the preset SQL template are parsed into a syntax tree, wherein the used parsing tool may be antlr or Apache call, and the like. Then, the syntax tree is traversed, and if a special value (null in the example) occurs, the syntax node is deleted from the syntax tree (corresponding to "get 2" in fig. 2). And then, acquiring the target SQL statement according to the reconstructed syntax tree.
In a second implementation manner, please refer to a flowchart of adding a condition field provided in an embodiment of the present application shown in fig. 4; aiming at the condition that the preset SQL template only comprises all necessary condition fields in the database, after judging whether a plurality of interface fields of the API call request comprise all necessary condition fields in the preset SQL template, modifying the preset SQL template according to the interface fields and the interface field values corresponding to the interface fields to obtain a target SQL statement, wherein the method comprises the following steps:
and step S320, judging whether the interface field appears in the condition field of the preset SQL template aiming at any interface field of all the interface fields in the API call request.
After step S320, if the determination result is yes, step S321 is executed: and replacing the placeholder corresponding to the condition field with the interface field value corresponding to the interface field.
After step S320, if the determination result is negative, step S322 is executed: adding the interface field into the condition field of the preset SQL template as the condition field, and replacing the placeholder corresponding to the condition field with the interface field value corresponding to the interface field.
And repeatedly executing the step S321 or the step S322 until all interface sections in all interface fields in the API call request are added as the condition fields of the preset SQL template.
The following illustrates the implementation process of step S320 to step S322: for example, the condition fields in the preset SQL template include: user category, user account. Wherein the user category is a requirement field. The interface field and the interface field value sent by the user terminal are as follows: user types are as follows: student, account number: 001. at this time, the account number in the interface field is added to the preset SQL template as a condition field. Then, placeholders corresponding to the user categories in the preset SQL template are replaced by students, and placeholders corresponding to the user accounts are replaced by 001.
After step S300, step S400 is performed: and searching a return result value corresponding to the return field in the target SQL statement in the database.
In the above step S400, the return result value may be sent to the calling end of the API according to the user requirement, or the return result value obtained by searching may be sent to another program for another operation. It should be noted that the field corresponding to the returned result value may be a returned field selected by the user.
The following describes how to modify the returned fields in the preset SQL template.
The method for modifying the returned field in the preset SQL template comprises the following two implementation modes:
in a first real-time manner, please refer to a schematic flow chart of deleting a return field provided in the embodiment of the present application shown in fig. 5; specifically, the method for determining the status of the preset SQL template that includes all the return fields in the database includes:
step S410: after the target field is obtained, whether the return field appears in the target field or not is judged for any return field of all the return fields in the preset SQL template.
It should be noted that, in the above step S410, the target field is used to limit some fields of the returned result value (instead of all fields of the data in the table being returned). In addition, the time for executing the "acquiring the target field" may be before or after executing the above step S100 (receiving the application program interface API call request), or may be receiving the target field and the API call request at the same time. Also, it should be noted that the target field is not user-specified, and the user can only select the target field within a specific range (i.e., all return fields in the database). Specific examples thereof include: the user terminal displays all selectable return fields, and the user selects a target field by checking the corresponding return field; or, the user inputs the target field according to the return field displayed by the user terminal.
After step S410, if the determination result is negative, step S411 is executed to delete the return field in the predetermined SQL template.
The above step S410 is repeatedly executed until only the return field corresponding to the target field remains in the preset SQL template.
In a second embodiment, the above steps S410 to S411 may also be implemented by adding a return field, such as a flow diagram of adding a return field provided in the embodiment of the present application shown in fig. 6; specifically, the method comprises the following steps:
step S420, after the target fields are obtained, whether the target fields are contained in the preset SQL template or not is judged according to any target field in the target fields.
It should be understood that, in the step S420, the manner and the sequence of acquiring the target field are the same as those of the step S410, and are not described herein again.
After step S420, if the determination result is no, step S421 is executed: and adding the target field into a preset SQL template as a return field.
The above step S410 is repeatedly executed until all the target fields are added as the return fields of the preset SQL template.
It should be understood that, like the above step S410, it should be noted that the target field is not arbitrarily specified by the user, and the user can only select the target field within a specified range (i.e., all the return fields in the database).
Referring to fig. 1, after step S200, if the determination result is negative, step S500 is executed to send the first prompt message.
In the above step S500, the first prompt message is used to prompt the user to enter the requirement field.
It should be noted that the step of custom-selecting the return field (corresponding to step S410 and step S411, or step S420 and step S421) may be performed before the condition field of the preset SQL template is modified (corresponding to step S300), so as to be performed after the condition field of the preset SQL template is modified (corresponding to step S300). In the specific implementation process, only the condition field in the preset SQL template may be modified (corresponding to step S300), only the return field in the preset SQL template may be modified, or both the condition field and the return field may be modified.
Please refer to fig. 7, which is a schematic structural diagram of a data query apparatus according to an embodiment of the present application; an embodiment of the present application provides a data query apparatus 700, including:
a receiving module 701, configured to receive an API call request, where the API call request includes: at least one interface field and at least one interface field value corresponding to the at least one interface field.
A determining module 702, configured to determine whether at least one interface field includes a necessary condition field required by a preset SQL template; the preset SQL template corresponds to the API.
The modifying module 703 is configured to generate a target SQL statement according to the preset SQL template and the at least one interface field value if the at least one interface field includes a necessary condition field required by the preset SQL template, and search for target data corresponding to the SQL statement from the database.
Optionally, in this embodiment of the present application, the determining module 702 is specifically configured to determine, for any condition field of all the condition fields, whether the condition field corresponds to an interface field of the API call request.
The modifying module 703 is specifically configured to, if the condition field does not correspond to the interface field of the API call request, convert the preset SQL template into a syntax tree, and then delete a syntax node in the syntax tree according to the condition field to obtain the target SQL.
Optionally, in this embodiment of the application, the modifying module 703 is specifically configured to replace the placeholder corresponding to the condition field with a special value.
The modifying module 703 is further specifically configured to delete a syntax node in the syntax tree having a special value corresponding to the condition field.
Optionally, in this embodiment of the present application, the determining module 702 is specifically configured to determine, for any interface field of all interface fields in the API call request, whether the interface field appears in a condition field of a preset SQL template.
The modifying module 703 is specifically configured to, if any interface field of all interface fields in the API call request does not appear in the condition field of the preset SQL template, add the interface field in the condition field of the preset SQL template, and replace the placeholder corresponding to the condition field with the interface field value corresponding to the interface field.
Optionally, in this embodiment of the present application, the receiving module 701 is further configured to obtain a target field.
The determining module 702 is further configured to determine, for any one of all return fields in the preset SQL template, whether the return field appears in the target field;
the modifying module 703 is further configured to delete any one of the return fields in the preset SQL template if the return field does not appear in the target field.
Optionally, in this embodiment of the present application, the data query apparatus further includes:
and the information sending module is used for sending first prompt information to the user terminal if the interface field of the API call request does not comprise any necessary condition field in a preset SQL template, wherein the first prompt information is used for prompting the user to transmit the necessary condition field.
Optionally, in this embodiment of the present application, the data query apparatus further includes:
and the verification module is used for judging whether the signature verification of the identity of the user and the API interface authority authentication pass or not.
And the information sending module is also used for sending second prompt information if the user does not receive the second prompt information, and the second prompt information is used for prompting the user to carry out signature verification and API interface authority authentication.
Please refer to fig. 8 for a schematic structural diagram of an electronic device according to an embodiment of the present application. An electronic device 800 provided in an embodiment of the present application includes: a processor 810 and a memory 820, the memory 820 storing machine readable instructions executable by the processor 810, the machine readable instructions when executed by the processor 810 performing the method as above.
The embodiment of the present application further provides a storage medium 830, where the storage medium 830 stores a computer program, and the computer program is executed by the processor 810 to perform the above method.
The storage medium 830 may be implemented by any type of volatile or nonvolatile storage device or combination thereof, such as a Static Random Access Memory (SRAM), an Electrically Erasable Programmable Read-Only Memory (EEPROM), an Erasable Programmable Read-Only Memory (EPROM), a Programmable Read-Only Memory (PROM), a Read-Only Memory (ROM), a magnetic Memory, a flash Memory, a magnetic disk, or an optical disk.
In the embodiments provided in the present application, it should be understood that the disclosed apparatus and method may be implemented in other ways. The apparatus embodiments described above are merely illustrative, and for example, the flowchart and block diagrams in the figures illustrate the architecture, functionality, and operation of possible implementations of apparatus, methods and computer program products according to various embodiments of the present application. In this regard, each block in the flowchart or block diagrams may represent a module, segment, or portion of code, which comprises one or more executable instructions for implementing the specified logical function(s). It should also be noted that, in some alternative implementations, the functions noted in the block may occur out of the order noted in the figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently, or the blocks may sometimes be executed in the reverse order, depending upon the functionality involved. It will also be noted that each block of the block diagrams and/or flowchart illustration, and combinations of blocks in the block diagrams and/or flowchart illustration, can be implemented by special purpose hardware-based systems which perform the specified functions or acts, or combinations of special purpose hardware and computer instructions.
In addition, functional modules of the embodiments in the present application may be integrated together to form an independent part, or each module may exist separately, or two or more modules may be integrated to form an independent part.
In this document, relational terms such as first and second, and the like may be used solely to distinguish one entity or action from another entity or action without necessarily requiring or implying any actual such relationship or order between such entities or actions.
The above description is only an alternative embodiment of the embodiments of the present application, but the scope of the embodiments of the present application is not limited thereto, and any person skilled in the art can easily conceive of changes or substitutions within the technical scope of the embodiments of the present application, and all the changes or substitutions should be covered by the scope of the embodiments of the present application.
Claims (10)
1. A method for querying data, comprising:
receiving an Application Program Interface (API) calling request, wherein the API calling request comprises: at least one interface field and at least one interface field value corresponding to the at least one interface field;
judging whether the at least one interface field comprises a necessary condition field required by a preset SQL template; the preset SQL template corresponds to the API;
if yes, generating a target SQL statement according to the preset SQL template and the at least one interface field value, and searching target data corresponding to the SQL statement from a database.
2. The method according to claim 1, wherein the predetermined SQL template comprises all condition fields in the database; generating a target SQL statement according to the preset SQL template and the at least one interface field value, wherein the generating includes:
judging whether any condition field of all the condition fields corresponds to an interface field of the API call request or not;
if not, converting the preset SQL template into a syntax tree, and then deleting syntax nodes in the syntax tree according to the condition field to obtain the target SQL statement.
3. The method according to claim 2, further comprising, before said deleting the syntax node in the syntax tree according to the condition field:
replacing the placeholder corresponding to the condition field with a special value;
deleting the grammar nodes in the grammar tree according to the condition field, wherein the deleting comprises the following steps:
and deleting the syntax nodes with the special values corresponding to the condition fields in the syntax tree.
4. The method according to claim 1, wherein the predetermined SQL template comprises a requirement field in the database; generating a target SQL statement according to the preset SQL template and the at least one interface field value, wherein the generating includes:
judging whether any interface field of all interface fields in the API call request appears in a condition field of the preset SQL template;
if not, adding the interface field in the condition field of the preset SQL template, and replacing the placeholder corresponding to the condition field with the interface field value corresponding to the interface field.
5. The method according to claim 1, wherein the predetermined SQL template comprises all the return fields in the database; the method further comprises the following steps:
after the target field is obtained, judging whether the return field appears in the target field or not aiming at any return field of all return fields in a preset SQL template;
if not, deleting the return field in the preset SQL template.
6. The method according to claim 1, wherein after said determining whether the at least one interface field includes a necessary condition field required by a preset SQL template, the method further comprises:
and if the interface field of the API call request does not comprise any necessary condition field in the preset SQL template, sending first prompt information, wherein the first prompt information is used for prompting to enter the necessary condition field.
7. The method according to claim 1, after said receiving the application program interface API call request, before determining whether the at least one interface field includes a requirement field required by a preset SQL template, further comprising:
judging whether signature verification and API interface authority authentication of the identity of the user pass or not;
and if not, sending second prompt information, wherein the second prompt information is used for prompting signature verification and API interface authority authentication.
8. A data query apparatus, comprising:
a receiving module, configured to apply a program interface API call request, where the API call request includes: at least one interface field and at least one interface field value corresponding to the at least one interface field;
the judging module is used for judging whether the at least one interface field comprises a necessary condition field required by a preset SQL template; the preset SQL template corresponds to the API;
and the modification module is used for generating a target SQL statement according to the preset SQL template and the at least one interface field value if the at least one interface field comprises a necessary condition field required by the preset SQL template, and searching target data corresponding to the SQL statement from a database.
9. An electronic device, comprising: a processor, a memory, and a bus;
the processor and the memory are communicated with each other through the bus;
the memory stores program instructions executable by the processor, the processor invoking the program instructions to perform the method of any one of claims 1-7.
10. A non-transitory computer-readable storage medium storing computer instructions which, when executed by a computer, cause the computer to perform the method of any one of claims 1-7.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110872979.1A CN113590648A (en) | 2021-07-30 | 2021-07-30 | Data query method, device, equipment and readable storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110872979.1A CN113590648A (en) | 2021-07-30 | 2021-07-30 | Data query method, device, equipment and readable storage medium |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN113590648A true CN113590648A (en) | 2021-11-02 |
Family
ID=78252837
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202110872979.1A Pending CN113590648A (en) | 2021-07-30 | 2021-07-30 | Data query method, device, equipment and readable storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN113590648A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN117971706A (en) * | 2024-04-01 | 2024-05-03 | 天津南大通用数据技术股份有限公司 | GBase database fuzzy test case generation method and device |
Citations (9)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101145162A (en) * | 2007-10-31 | 2008-03-19 | 金蝶软件（中国）有限公司 | Data base dynamic inquiry method and system |
| CN101799801A (en) * | 2010-01-26 | 2010-08-11 | 中兴通讯股份有限公司 | Realization method and device of self-definition statistical report form based on flow protocol |
| CN104021219A (en) * | 2014-06-23 | 2014-09-03 | 东莞市西奥计算机智能科技有限公司 | Method and device for generating data template |
| CN104252463A (en) * | 2013-06-26 | 2014-12-31 | 中国银联股份有限公司 | Db2 database management method based on web system |
| CN107480280A (en) * | 2017-08-22 | 2017-12-15 | 金蝶软件（中国）有限公司 | The method and relevant device of a kind of data processing |
| CN108959631A (en) * | 2018-07-24 | 2018-12-07 | 北京百度网讯科技有限公司 | Method and apparatus for query information |
| CN111460506A (en) * | 2020-04-03 | 2020-07-28 | 中国工商银行股份有限公司 | Data access control method and device |
| CN111611276A (en) * | 2020-06-05 | 2020-09-01 | 腾讯科技（深圳）有限公司 | Data query method, device and storage medium |
| CN113051287A (en) * | 2021-06-01 | 2021-06-29 | 北京达佳互联信息技术有限公司 | Query statement generation method, device, equipment and storage medium |
-
2021
- 2021-07-30 CN CN202110872979.1A patent/CN113590648A/en active Pending
Patent Citations (9)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101145162A (en) * | 2007-10-31 | 2008-03-19 | 金蝶软件（中国）有限公司 | Data base dynamic inquiry method and system |
| CN101799801A (en) * | 2010-01-26 | 2010-08-11 | 中兴通讯股份有限公司 | Realization method and device of self-definition statistical report form based on flow protocol |
| CN104252463A (en) * | 2013-06-26 | 2014-12-31 | 中国银联股份有限公司 | Db2 database management method based on web system |
| CN104021219A (en) * | 2014-06-23 | 2014-09-03 | 东莞市西奥计算机智能科技有限公司 | Method and device for generating data template |
| CN107480280A (en) * | 2017-08-22 | 2017-12-15 | 金蝶软件（中国）有限公司 | The method and relevant device of a kind of data processing |
| CN108959631A (en) * | 2018-07-24 | 2018-12-07 | 北京百度网讯科技有限公司 | Method and apparatus for query information |
| CN111460506A (en) * | 2020-04-03 | 2020-07-28 | 中国工商银行股份有限公司 | Data access control method and device |
| CN111611276A (en) * | 2020-06-05 | 2020-09-01 | 腾讯科技（深圳）有限公司 | Data query method, device and storage medium |
| CN113051287A (en) * | 2021-06-01 | 2021-06-29 | 北京达佳互联信息技术有限公司 | Query statement generation method, device, equipment and storage medium |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN117971706A (en) * | 2024-04-01 | 2024-05-03 | 天津南大通用数据技术股份有限公司 | GBase database fuzzy test case generation method and device |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN112149399B (en) | Table information extraction method, device, equipment and medium based on RPA and AI | |
| US20210201168A1 (en) | Method and Apparatus for Outputting Information, Device and Storage Medium | |
| CN112130830A (en) | Interface generation method and device and electronic equipment | |
| CN113778897A (en) | Automatic test method, device, equipment and storage medium of interface | |
| CN113590648A (en) | Data query method, device, equipment and readable storage medium | |
| CN113705816B (en) | Flow chart generation method, electronic device, device and readable storage medium | |
| CN113535749A (en) | Query statement generation method and device | |
| CN111046640A (en) | Dynamic generation method and device of certification document | |
| CN114443905A (en) | Interface document updating method and device, electronic equipment and readable storage medium | |
| CN113419719A (en) | Interface case generation method and system based on business scene | |
| CN115794872A (en) | Statement transformation method and device based on heterogeneous database, storage medium and equipment | |
| CN114996246A (en) | Data cleaning method for checking multiple fields of table based on NiFi assembly | |
| CN114637531A (en) | Method and device for dynamically generating application program interface | |
| CN114398152A (en) | Interface simulation service calling method and device | |
| CN113407598A (en) | Method and device for generating demand document, storage medium and electronic equipment | |
| CN113468258A (en) | Heterogeneous data conversion method and device and storage medium | |
| CN111949309A (en) | Method and device for pulling IDL file, computer equipment and storage medium | |
| KR100545348B1 (en) | An integrated development system for a SyncML server application development | |
| CN112799638A (en) | Non-invasive rapid development method, platform, terminal and storage medium | |
| CN112487170A (en) | Scene configuration-oriented human-computer interaction dialogue robot system | |
| CN111782678A (en) | Data storage method and device | |
| CN118071310B (en) | Business processing method and system based on flow engine | |
| CN110647314A (en) | Skill generation method and device and electronic equipment | |
| Корніловська et al. | PARSING INTERNET RESOURCES USING A CHAT BOT TO CREATE A CONSOLIDATED INFORMATION RESOURCES IN THE FIELD OF EMPLOYMENT IN THE FIELD OF INFORMATION TECHNOLOGY | |
| Kornilovska et al. | PARSING INTERNET RESOURCES USING A CHAT BOT TO CREATE A CONSOLIDATED INFORMATION RESOURCES IN THE FIELD OF EMPLOYMENT IN THE FIELD OF INFORMATION TECHNOLOGY |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |