CN118193567B - Method, device, equipment and medium for generating query statements and querying business data - Google Patents
Method, device, equipment and medium for generating query statements and querying business dataInfo
- Publication number
- CN118193567B CN118193567B CN202410262185.7A CN202410262185A CN118193567B CN 118193567 B CN118193567 B CN 118193567B CN 202410262185 A CN202410262185 A CN 202410262185A CN 118193567 B CN118193567 B CN 118193567B
- Authority
- CN
- China
- Prior art keywords
- query
- name
- view metadata
- condition
- target
- 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.)
- Active
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING OR CALCULATING; 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/2428—Query predicate definition using graphical user interfaces, including menus and forms
-
- G—PHYSICS
- G06—COMPUTING OR CALCULATING; 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
- G06F16/2448—Query languages for particular applications; for extensibility, e.g. user defined types
-
- G—PHYSICS
- G06—COMPUTING OR CALCULATING; 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
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Mathematical Physics (AREA)
- Human Computer Interaction (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention relates to the technical field of databases, and discloses a method, a device, equipment and a medium for generating query sentences and query service data; and generating a preliminary query statement according to the view metadata name, the at least one condition field and the condition configuration information of each condition field. The method comprises the steps of receiving a query request, analyzing the query request to obtain a first view metadata name, at least one query parameter, a condition value and a condition sign of each query parameter, obtaining a first prepared query statement according to the first view metadata name, generating a target query statement according to the first prepared query statement, the at least one query parameter and the condition value and the condition sign of each query parameter, and executing the target query statement to obtain a first query data set. The invention can improve the development efficiency of the related codes.
Description
Technical Field
The present invention relates to the field of database technologies, and in particular, to a method, an apparatus, a device, and a medium for generating query sentences and query service data.
Background
In recent years, the technological threshold of development work of Web programs is high, and in general, development of Web programs requires codes of three parts of a client, a server and a database.
In the related art, each time a query condition to a database table is newly added or modified, a user is required to write a corresponding structured language (Structured Query Language) script. After receiving the query parameters, splicing according to the structured language script and the query parameters to obtain query sentences, and obtaining query data after executing the query sentences.
However, when the query conditions are complex, or a large number of query conditions need to be set, the development efficiency of the relevant codes is low.
Disclosure of Invention
In view of the above, the present invention provides a method, apparatus, device and medium for generating query sentences and query service data, so as to solve the problem of low development efficiency of the code related to the query condition.
In a first aspect, the present invention provides a method of generating a query statement, the method being applied to a database management system, the method comprising:
receiving a preservation request;
extracting a view metadata name, at least one condition field and condition configuration information corresponding to each condition field from the storage request, wherein the view metadata name, the at least one condition field and the condition configuration information corresponding to each condition field are input by a user in a view metadata design interface;
And generating a preliminary query statement corresponding to the view metadata name according to the view metadata name, at least one condition field and condition configuration information corresponding to each condition field.
The method for generating the query statement has the following advantages:
Firstly, a developer can directly input view metadata names and corresponding condition fields and condition configuration information on a view metadata design interface according to actual requirements, so that the flexibility and the custom capability of the system are greatly enhanced, and the developer can customize a data view meeting own requirements according to business scenes. In addition, the scheme allows a developer to intuitively set query conditions through a graphical interface, does not need to write complex query sentences, reduces the use threshold, and can improve the development efficiency of related codes of a database. In addition, the server side can automatically generate a preliminary query statement according to the input of the developer, so that the error probability is reduced.
Secondly, when the query under different condition parameters is required to be performed based on the same view, only a preset preliminary query statement is required to be called, so that the query efficiency of the service data can be enhanced.
Third, because the user can dynamically add, modify or delete the condition field and the configuration information thereof according to the actual situation, the scheme can respond more quickly along with the service requirement, and the development efficiency of the related codes can be improved without re-writing the related codes of the database according to the service requirement.
In an alternative embodiment, the generating a preliminary query statement corresponding to the view metadata name according to the view metadata name, at least one condition field, and condition configuration information corresponding to each condition field includes:
Acquiring target view metadata corresponding to the view metadata names according to the view metadata names;
Extracting the table name of the target database from the target view metadata;
and generating the preliminary query statement according to the target database table name, at least one condition field and condition configuration information corresponding to each condition field.
Specifically, firstly, the scheme can automatically generate the preliminary query statement, greatly simplify the construction process of the query statement, further improve the query efficiency and reduce errors caused by manually writing the query. Secondly, view metadata is used as a middle layer, which not only encapsulates the specific details of the database table, but also provides rich query condition interfaces, this design model can enhance the modularity and multiplexing of code, and for frequently changing or similar query requirements, only the conditional configuration of view metadata needs to be adjusted to achieve a fast response.
In a second aspect, the present invention provides a method for querying service data, the method being applied to a database management system, the method comprising:
receiving a query request;
Analyzing the query request to obtain a first view metadata name, at least one query parameter, and a condition value and a condition symbol corresponding to each query parameter;
acquiring a first preparation query statement corresponding to the first view metadata name according to the first view metadata name;
generating a target query statement according to the first prepared query statement, at least one query parameter, and a condition value and a condition symbol corresponding to each query parameter;
And executing the target query statement to acquire a first query data set.
The method for inquiring the service data has the following advantages:
Firstly, the prepared query statement is directly obtained according to the view metadata name, so that the workload of repeatedly writing basic query can be reduced, and further, the query efficiency can be improved. Secondly, by taking the view metadata as middle layer data, when the database table structure changes, only the view metadata is required to be updated, so that related query codes do not need to be rewritten, and the development efficiency of the related codes of the database can be greatly improved.
In an alternative embodiment, the method further comprises:
Determining target view metadata corresponding to the first view metadata name according to the first view metadata name;
when the target view metadata is determined to comprise association relation information, extracting an association table name and at least one association field name from the association relation information;
Determining a target association table corresponding to the association table name according to the association table name;
Determining at least one piece of associated data corresponding to each piece of data in the first query data set in the target associated table according to each associated field name;
And splicing each piece of data and at least one piece of associated data corresponding to each piece of data to obtain a second query data set.
Specifically, the method and the device can automatically load the data in the association table according to the association relation information in the view metadata, simplify the query process and improve the development efficiency. And by splicing the associated data, the query result is ensured to contain all information related to the main data, and the data integrity is improved. The associated data are loaded at one time and spliced together, so that the cost of multiple database queries can be reduced, and the overall performance of the system can be improved particularly in a batch processing scene.
In an alternative embodiment, the method further comprises:
Extracting the type of the target view metadata from the target view metadata;
And packaging all data in the second query data set according to the type of the target view metadata to obtain a return result.
Specifically, the second query data set is packaged according to the type of the target view metadata, so that the data structure of the returned result is matched with the service requirement, and front-end display and back-end processing are facilitated.
In a third aspect, the present invention provides an apparatus for generating a query statement, the apparatus being applied to a database management system, the apparatus comprising:
the receiving module is used for receiving the storage request;
The extraction module is used for extracting the view metadata name, at least one condition field and condition configuration information corresponding to each condition field from the storage request, wherein the view metadata name, the at least one condition field and the condition configuration information corresponding to each condition field are input by a user in a view metadata design interface;
And the generating module is used for generating a preparation query statement corresponding to the view metadata name according to the view metadata name, at least one condition field and condition configuration information corresponding to each condition field.
In a fourth aspect, the present invention provides an apparatus for querying service data, the apparatus being applied to a database management system, the apparatus comprising:
The receiving module is used for receiving the query request;
the analysis module is used for analyzing the query request to obtain a first view metadata name, at least one query parameter, and a condition value and a condition symbol corresponding to each query parameter;
the acquisition module is used for acquiring a first preparation query statement corresponding to the first view metadata name according to the first view metadata name;
The generation module is used for generating a target query statement according to the first prepared query statement, at least one query parameter, and a condition value and a condition sign corresponding to each query parameter;
and the execution module is used for executing the target query statement and acquiring a first query data set.
In a fifth aspect, the present invention provides a computer device, including a memory and a processor, where the memory and the processor are communicatively connected to each other, and the memory stores computer instructions, and the processor executes the computer instructions, thereby executing the method for generating a query statement according to the first aspect or any implementation manner corresponding to the first aspect, or executing the method for querying service data according to the second aspect or any implementation manner corresponding to the second aspect.
In a sixth aspect, the present invention provides a computer readable storage medium having stored thereon computer instructions for causing a computer to perform the method of generating a query statement according to the first aspect or any of the embodiments corresponding thereto, or the method of querying service data according to the second aspect or any of the embodiments corresponding thereto.
In a seventh aspect, the present invention provides a computer program product comprising computer instructions for causing a computer to perform the method of generating a query statement of the first aspect or any of its corresponding embodiments, or to perform the method of querying business data of the second aspect or any of its corresponding embodiments.
Drawings
In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the related art, the drawings that are required to be used in the description of the embodiments or the related art will be briefly described, and it is apparent that the drawings in the description below are some embodiments of the present invention, and other drawings may be obtained according to the drawings without inventive effort for those skilled in the art.
FIG. 1 is a schematic diagram of a view metadata design interface according to an embodiment of the present invention;
FIG. 2 is a flow diagram of a method of generating a query statement according to an embodiment of the invention;
FIG. 3 is a flow diagram of another method of generating a query statement according to an embodiment of the invention;
FIG. 4 is a flow diagram of a method of querying business data in accordance with an embodiment of the present invention;
FIG. 5 is a flow chart of another method of querying business data in accordance with an embodiment of the present invention;
FIG. 6 is a block diagram of an apparatus for generating a query statement according to an embodiment of the invention;
Fig. 7 is a block diagram of an apparatus for querying service data according to an embodiment of the present invention;
Fig. 8 is a schematic diagram of a hardware structure of a computer device according to an embodiment of the present invention.
Detailed Description
For the purpose of making the objects, technical solutions and advantages of the embodiments of the present invention more apparent, the technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings in the embodiments of the present invention, and it is apparent that the described embodiments are some embodiments of the present invention, but not all embodiments of the present invention. All other embodiments, which can be made by those skilled in the art based on the embodiments of the invention without making any inventive effort, are intended to be within the scope of the invention.
In recent years, the software industry has developed rapidly, and most of business functions are realized based on a framework of combining a Web application program and a database, such as various shopping application programs, office application programs and the like. In conventional Web program development, a large amount of code is required to be written to add or modify any database-related function.
The embodiment of the invention provides a method for generating query sentences and query service data, which can greatly improve the development efficiency of related codes by generating the query sentences in advance through the metadata of intermediate variable views to query the service data.
The invention is applied to a database management system, which can comprise a client and a server. The client may be a terminal, such as a desktop computer, a notebook computer, a mobile phone, and the like. The server may be a server or a server cluster, etc. The client can be a terminal where a developer is located, or a terminal where a common user is located, and is provided with a Web end program. The server may be provided with a server program and a database in which a large number of database tables are stored.
The view metadata is described below. The view metadata corresponds to views in the database and is used for querying service data according to conditions. The view metadata may include a view metadata name, a database table name, a type of view metadata, a page template, a condition field, and condition configuration information of the condition field.
The view metadata name, the database table name, the type of the view metadata, the page template, the condition field and the field configuration information of the condition field can be configured in a view metadata design interface shown in fig. 1, wherein the design interface comprises three major modules, a view metadata setting module, a field list and a condition input module corresponding to the view type. The view metadata setting module comprises a view metadata name input box, a database table name input box, a view type input box, a page template input box and a field set input box.
The setting rules of the view metadata are described in detail below.
First, a view metadata name is input in a view metadata name input box. The view metadata name must be an entry, which is a unique identification of the view metadata. Entity metadata names or database table names may be selected in the database table name input box via a drop down list. If an entity metadata name is selected, the client may obtain a field configuration information set of the corresponding entity metadata from the server. If the database table name is selected, the client may obtain the table information and the field configuration information set of the corresponding database table from the server. The related information acquired from the server may be displayed in the view metadata setting module.
Second, the type of view metadata is selected through a drop down list at the view type input box. The type of view metadata may be an option type, a check type, a default value type, etc. (list type is not selected as default). The list type is used to present the list, can choose whether to add or modify the form view, delete, custom buttons, etc. The option type may specify an option display field and a value field, spliced into a drop-down option, and may specify a parent node field to compose a tree option. The check type can customize the check result and the hint message. The default value type can only return one value and is commonly used for automatically generating logic such as numbers.
Third, the page template may or may not be selected, and if not, a default page template is used, e.g., the default page module may be a list template. The page template is pre-created by a developer and is configured in the Web end program. A developer may create a page template, e.g., online, by page template editing software. The page template may be in a variety of page formats, such as native Htm, JQueryUI, vue, react, amis. The page template may include a form layout including the number of rows, columns, number of components per row or column, etc. of the form, and a definition of the individual components, which may include the display form of the components (e.g., drop down boxes, options, colors, etc.), and the data length that the components may populate.
Fourth, the condition field set and the condition configuration information related to the view metadata can be set through the field list and the field set input box.
1. A set of condition fields.
(1) View metadata defaults to listing all fields of the selected entity metadata or database table, unwanted fields may be deleted by themselves.
(2) For fields that do not need to be displayed, the field may be followed by a "|" hidden field.
(3) When the field is in the form of an option, a display field with escape as the option can be set, an alias displayed by the field can be set by using the 'to', and the database function can be used for calculation.
(4) "Xxx" may be used to refer to querying all fields of the "xxx" database table.
(5) The fields may be post-query calculated and processed using the full expression language (Expression Language, EL) expression of the "% xxx%" package.
2. Condition configuration information.
(1) The association relation of entity metadata and the data filtering condition are set through a conditional expression at the last "@ head of the field set input box.
Example 1, set the association of entity metadata "@ $xxx.id = $yyy.maid", indicating that there is a left-connected foreign key association for the "xxx" database table and the "yyy" database table.
Example 2, data filtering conditions "$xxx.NAME= #NAME" are set, indicating that the NAME field of the "xxx" database table is equal to the query parameter NAME value.
Example 3, set association and data filtering condition "@ $xxx.id = $yyy.maindid $xxx.name = #name #" at the same time, indicates that there is a left-connected foreign key association for the "xxx" database table and the "yyy" database table, and that the NAME field of the "xxx" database table is equal to the query parameter NAME value.
(2) The grouping inquiry of the group by is realized by the grouping condition designated by "/$$". For example, "/$xxx.zz" means that the traffic data in the "xxx" database tables is queried in groups according to the "zz" field.
(3) Positive ordering is specified by "+" and reverse ordering is specified by "-".
Example 1, "$$ xxx. Yyyy+" indicates that the data in the "xxx" database table is ordered in positive order according to the "yyy" field.
Example 2, "$xxx. Zz-" indicates that the data in the "xxx" database tables is ordered in reverse order according to the "zz" field.
(4) And forming an array by taking data in an association table corresponding to the metadata of the association entity as CHILD nodes, and placing the data in the CHILD attributes of each piece of data.
For example, the expression "yyy" field and "zzz" field in the association table "xxx" in CHILD [ $ xxx.yyy, $ xxx.zzz ] are concatenated into an array and placed in CHILD attributes of the corresponding data in the database table.
(5) Splicing the specified fields of the associated data into the value of the split is indicated by ($$).
For example, ($xxx. Yyy,) means that the values of the "yyy" fields of all data of the association table "xxx" are used "," concatenated together ".
(6) By { $ $ is meant stitching the association data behind the query result data.
For example, { $xxx. Yyy } means concatenating the value of the "yyy" field in the association table "xxx" behind the query result data.
After the developer has configured the set of condition fields, and the condition configuration information, a save operation may be performed. When the client detects a save operation by the developer, the identity of the page template (i.e., the user selected or default page template) may be obtained. In addition, the client converts the view metadata name, the database table name, the condition field, the condition configuration information and the like input by the developer into pinyin full-spellings, and suffix numbers 1,2,3 and the like are added after the pinyin full-spellings if the pinyin full-spellings are repeated. Then, according to the identification of the page template, JS (JavaScript) files, cascading style sheet (CASCADING STYLE SHEETS) files and the like corresponding to the target page template can be obtained from a page template library of the Web end program. Finally, the client can generate a condition input module according to the condition configuration information after the format conversion and the form layout and the definition of each component in the target page template, and the condition input module is displayed, as shown in the upper right of fig. 1.
The Web-side program also provides an EL expression editor. The developer can assemble the EL expression in the EL expression editor by pre-forming functions, custom functions, etc. The prefabricated functions can comprise a plurality of functions such as logic judgment, digital functions, time functions, character string functions, array functions, interface calling functions and the like. The custom function may be a JS (JavaScript) function.
In accordance with an embodiment of the present invention, there is provided a method embodiment of generating a query statement, it being noted that the steps shown in the flowchart of the figures may be performed in a computer system, such as a set of computer executable instructions, and, although a logical order is shown in the flowchart, in some cases, the steps shown or described may be performed in an order other than that shown or described herein.
In this embodiment, a method for generating a query sentence is provided, which is executed by the server, and fig. 2 is a flowchart of a method for generating a query sentence according to an embodiment of the present invention, as shown in fig. 2, where the flowchart includes the following steps:
Step S201, a save request is received, and a view metadata name, at least one condition field, and condition configuration information corresponding to each condition field are extracted from the save request.
The view metadata name, at least one condition field and condition configuration information corresponding to each condition field are all input by a user in the view metadata design interface.
In implementation, when a developer completes the design of view metadata in the design interface shown in FIG. 1, a save operation may be performed. After the client detects the developed save operation, a save request may be generated and sent to the server according to the name of the view metadata, the database table name, the condition field, the condition configuration information set, the type of the view metadata, and the like, which are input by the developer. After receiving the save request, the server may parse the save request, and obtain the view metadata name, the database table name, the condition field, the condition configuration information, the type of the view metadata, and so on.
Step S202, according to the view metadata name, at least one condition field and the condition configuration information corresponding to each condition field, a preparation query statement corresponding to the view metadata name is generated.
Wherein the type of the preliminary query statement may be a structured query language (Structured Query Language, SQL) statement.
In implementation, the server may generate the view metadata according to the view metadata name, the condition configuration information of each condition field and each condition field, and the type of the view metadata. In addition, the server side can also acquire a query statement template, and fill the view metadata name, the database table name, the condition field and the condition configuration information into the appointed position of the query statement template to obtain a prepared query statement and store the prepared query statement.
The method for generating the query statement provided by the embodiment comprises the following steps:
Firstly, a developer can directly input view metadata names and corresponding condition fields and condition configuration information on a view metadata design interface according to actual requirements, so that the flexibility and the custom capability of the system are greatly enhanced, and the developer can customize a data view meeting own requirements according to business scenes. In addition, the scheme allows a developer to intuitively set query conditions through a graphical interface, does not need to write complex SQL sentences, reduces the use threshold, and can improve the development efficiency of related codes of a database. In addition, the server side can automatically generate a preliminary query statement according to the input of the developer, so that the error probability is reduced.
Secondly, when the query under different condition parameters is required to be performed based on the same view, only a preset preliminary query statement is required to be called, so that the query efficiency of the service data can be enhanced.
Third, because the user can dynamically add, modify or delete the condition field and the configuration information thereof according to the actual situation, the scheme can respond more quickly along with the service requirement, and the development efficiency of the related codes can be improved without re-writing the related codes of the database according to the service requirement.
In this embodiment, a method for generating a query sentence is provided, which is executed by the server, and fig. 3 is a flowchart of a method for generating a query sentence according to an embodiment of the present invention, as shown in fig. 3, where the flowchart includes the following steps:
Step S301, a save request is received, and a view metadata name, at least one condition field, and condition configuration information corresponding to each condition field are extracted from the save request.
The view metadata name, at least one condition field and condition configuration information corresponding to each condition field are all input by a user in the view metadata design interface.
The specific process of step S301 is similar to that of step S201, and will not be described here.
Step S302, a preparation query statement corresponding to the view metadata name is generated according to the view metadata name, at least one condition field and condition configuration information corresponding to each condition field.
Specifically, the step S302 includes:
in step S3021, target view metadata corresponding to the view metadata name is acquired according to the view metadata name.
In implementation, the server may determine whether there is target view metadata corresponding to the view metadata name according to the view metadata name, if so, update the target view metadata according to the extracted condition field and the condition configuration information, and perform step S3022, if not, generate the target view metadata according to the view metadata name, the database table name, at least one condition field, the condition configuration information corresponding to each condition field, the type of the view metadata, and the like.
In step S3022, the target database table name is extracted from the target view metadata.
In implementation, the server may parse the target view metadata and extract the table name of the target database from the parsed target view metadata.
Step S3023, generating a preliminary query statement according to the target database table name, at least one condition field, and condition configuration information corresponding to each condition field.
In implementation, the server may first obtain the query statement template, fill the table name of the target database, the condition field, and the condition configuration information into the designated location of the query statement template, obtain the preliminary query statement, and store the preliminary query statement.
According to the method for generating the query statement, firstly, the scheme can automatically generate the preliminary query statement, so that the construction process of the query statement is greatly simplified, further, the query efficiency can be improved, and errors caused by manually writing the query can be reduced. Secondly, view metadata is used as a middle layer, which not only encapsulates the specific details of the database table, but also provides rich query condition interfaces, this design model can enhance the modularity and multiplexing of code, and for frequently changing or similar query requirements, only the conditional configuration of view metadata needs to be adjusted to achieve a fast response.
In accordance with an embodiment of the present invention, there is provided a method embodiment of querying business data, it being noted that the steps shown in the flowchart of the figures may be performed in a computer system, such as a set of computer-executable instructions, and, although a logical sequence is shown in the flowchart, in some cases, the steps shown or described may be performed in a different order than what is shown or described herein.
In this embodiment, a method for querying service data is provided, which is executed by the server, and fig. 4 is a flowchart of a method for querying service data according to an embodiment of the present invention, as shown in fig. 4, where the flowchart includes the following steps:
step S401, receiving a query request, and analyzing the query request to obtain a first view metadata name, at least one query parameter, and a condition value and a condition symbol corresponding to each query parameter.
In implementation, in the condition input module shown in the upper right of fig. 1, a general user may input a condition value in an input box corresponding to a condition field, click on a "search" control, and after detecting the operation, the client may generate a query request according to a first view element number name corresponding to the condition input module, a preset condition field (i.e., a query parameter), condition configuration information (including a conditional symbol), and a corresponding field value (i.e., a condition value), and send the query request to the server. After receiving the query request, the server may parse the query request to obtain a first view metadata name, at least one query parameter, and a condition value and a condition symbol corresponding to each query parameter. For example, in FIG. 1, the query parameters include "name" and "identification number".
Step S402, according to the first view metadata name, a first preliminary query sentence corresponding to the first view metadata name is acquired.
In an implementation, the server may determine, according to the first view metadata name, a first preliminary query statement corresponding to the first view metadata name.
Step S403, generating a target query statement according to the first preliminary query statement, at least one query parameter, and the condition value and the condition symbol corresponding to each query parameter.
Specifically, the server may determine, according to the query parameter, a target position where the query parameter is located in the first preliminary query statement, and fill the condition value and the condition symbol of the query parameter into the target position according to a predetermined rule, so as to obtain the target query statement.
For example, { "AGE" >: "5" }, where the query parameter is "AGE", the condition notation bits ">, the condition value" 5".
Step S404, executing a target query statement to acquire a first query data set.
Wherein the target query statement may be an SQL statement.
Specifically, the server may execute the target query statement, and obtain the first query data set from the database table stored in the database according to the query condition carried in the target query statement.
According to the method for inquiring the service data, firstly, the prepared inquiry statement is directly obtained according to the view metadata name, so that the workload of repeatedly writing basic inquiry can be reduced, and further, the inquiry efficiency can be improved. Secondly, by taking the view metadata as middle layer data, when the database table structure changes, only the view metadata is required to be updated, so that related query codes do not need to be rewritten, and the development efficiency of the related codes of the database can be greatly improved.
In this embodiment, a method for querying service data is provided, which is executed by the server, and fig. 5 is a flowchart of a method for querying service data according to an embodiment of the present invention, as shown in fig. 5, where the flowchart includes the following steps:
Step S501, a query request is received, and the query request is parsed to obtain a first view metadata name, at least one query parameter, and a condition value and a condition symbol corresponding to each query parameter.
Step S502, according to the first view metadata name, a first preliminary query statement corresponding to the first view metadata name is obtained.
Step S503, generating a target query statement according to the first prepared query statement, at least one query parameter, and the condition value and the condition symbol corresponding to each query parameter.
Step S504, executing the target query statement to acquire a first query data set.
The specific processing of step S501 to step S504 is similar to the specific processing of step S401 to step S404, and will not be described here.
In step S505, target view metadata corresponding to the first view metadata name is determined according to the first view metadata name.
In an implementation, the server stores multiple view metadata. The server may determine the target view metadata from the plurality of view metadata according to the first view metadata name.
Step S506, when it is determined that the target view metadata includes the association information, the association table name and at least one association field name are extracted from the association information.
In implementation, the server may determine whether the condition configuration information of the target view metadata includes association information, and if so, may extract an association table name and an association field name from the association information. For example, the association information is "@ $xxx.id+= $yyy.maiid", the database table name corresponding to the target view metadata is "xxx", the association table name is "yyy", and the association field name is "MAINID".
Step S507, determining a target association table corresponding to the association table name according to the association table name.
In an implementation, the server may determine the target association table from a plurality of database tables stored in the database according to the association table name.
Step S508, according to each associated field name, at least one piece of associated data corresponding to each piece of data in the first query data set is determined in the target associated table.
In an implementation, for each associated field name, the server may determine associated data corresponding to each piece of data according to the associated field name and a field value of a target field corresponding to the associated field name in each piece of data in the first query data set. The treatment can be specifically performed in the following two ways:
in one mode, the server may traverse each piece of data in the target association table, traverse each piece of data, determine whether a field value of an associated field corresponding to the associated field name in the piece of data is the same as a field value of a target field of the target data in the first query dataset, and if so, determine the traversed data as associated data of the target data. The target data is any piece of data in the first query data set. If not, the next piece of data in the next tag association table may be traversed. Similarly, associated data corresponding to each piece of data in the first set of query data may be determined.
In the second mode, the server may traverse each piece of data in the first query data set, traverse one piece of data each time, determine a field value of a target field corresponding to the associated field name in the traversed data, determine whether a field value of an associated field of at least one piece of data in the target association table is the same as a field value of the target field, and if so, determine the at least one piece of data as associated data corresponding to the traversed data. If not, the next piece of data of the first set of query data may be traversed. Similarly, associated data corresponding to each piece of data in the first set of query data may be determined.
For example, the association field name is "MAINID", the name of the target field is "ID", the "ID" of the target data in the first data set is "001", and the data of "MAINID" = "001" in the association table may be determined as the association data corresponding to the target data.
Step S509, splice each piece of data and at least one piece of associated data corresponding to each piece of data, to obtain a second query data set.
In implementation, for each piece of data, the server may splice at least one piece of associated data corresponding to the piece of data to the back of the piece of data, so as to obtain spliced data, where the spliced pieces of data form the second query data set.
Step S510, extracting the type of the target view metadata from the target view metadata.
The type of the target view metadata may be a data dictionary type, a list type, a tree shape, or the like.
In implementation, the server may parse the target entity metadata to extract the type of the target view metadata.
And S511, packaging all data in the second query data set according to the type of the target view metadata to obtain a return result.
In implementation, the server may obtain a conversion mode corresponding to the type of the target view metadata according to the type of the target view metadata, and perform format conversion on all data in the second query data set to obtain a final return result, and send the final return result to the client.
In some alternative embodiments, the server may convert the field value in each piece of data in the second query data set according to the conditional configuration information. For example, an EL expression is set in the condition configuration information, and a field value needs to be input into the EL expression to obtain a calculation result.
According to the method for inquiring the service data, firstly, the data in the association table can be automatically loaded according to the association relation information in the view metadata, so that the inquiring process is simplified, and the development efficiency is improved. Secondly, by splicing the associated data, the query result is ensured to contain all information related to the main data, and the integrity of the data is improved. And the associated data are loaded and spliced together at one time, so that the cost of multiple database queries can be reduced, and the overall performance of the system can be improved particularly in a batch processing scene. Thirdly, the second query data set is packaged according to the type of the target view metadata, so that the data structure of the returned result is matched with the service requirement, and front-end display and back-end processing are facilitated.
The embodiment also provides a device for generating a query sentence, which is used for implementing the foregoing embodiment and the preferred implementation manner, and is not described in detail. As used below, the term "module" may be a combination of software and/or hardware that implements a predetermined function. While the means described in the following embodiments are preferably implemented in software, implementation in hardware, or a combination of software and hardware, is also possible and contemplated.
The present embodiment provides an apparatus for generating a query sentence, as shown in fig. 6, including:
a receiving module 601, configured to receive a save request;
An extracting module 602, configured to extract, from the save request, a view metadata name, at least one condition field, and condition configuration information corresponding to each condition field, where the view metadata name, the at least one condition field, and the condition configuration information corresponding to each condition field are all input by a user in a view metadata design interface;
a generating module 603, configured to generate a preliminary query statement corresponding to the view metadata name according to the view metadata name, at least one condition field, and condition configuration information corresponding to each condition field.
In some alternative embodiments, the generating module 603 is configured to:
Acquiring target view metadata corresponding to the view metadata names according to the view metadata names;
Extracting the table name of the target database from the target view metadata;
and generating the preliminary query statement according to the target database table name, at least one condition field and condition configuration information corresponding to each condition field.
Further functional descriptions of the above respective modules and units are the same as those of the above corresponding embodiments, and are not repeated here.
The means for generating query statements in this embodiment are presented in the form of functional units, where the units are ASIC (Application SPECIFIC INTEGRATED Circuit) circuits, processors and memories executing one or more software or firmware programs, and/or other devices that can provide the above-described functionality.
The embodiment also provides a device for querying service data, which is used for implementing the foregoing embodiments and preferred embodiments, and is not described in detail. As used below, the term "module" may be a combination of software and/or hardware that implements a predetermined function. While the means described in the following embodiments are preferably implemented in software, implementation in hardware, or a combination of software and hardware, is also possible and contemplated.
The present embodiment provides an apparatus for querying service data, as shown in fig. 7, including:
a receiving module 701, configured to receive a query request;
The parsing module 702 is configured to parse the query request to obtain a first view metadata name, at least one query parameter, and a condition value and a condition symbol corresponding to each query parameter;
An obtaining module 703, configured to obtain a first preliminary query statement corresponding to the first view metadata name according to the first view metadata name;
a generating module 704, configured to generate a target query statement according to the first preliminary query statement, at least one query parameter, and a condition value and a condition symbol corresponding to each query parameter;
and the execution module 705 is configured to execute the target query statement to obtain a first query data set.
In some alternative embodiments, the execution module 705 is further configured to:
Determining target view metadata corresponding to the first view metadata name according to the first view metadata name;
when the target view metadata is determined to comprise association relation information, extracting an association table name and at least one association field name from the association relation information;
Determining a target association table corresponding to the association table name according to the association table name;
Determining at least one piece of associated data corresponding to each piece of data in the first query data set in the target associated table according to each associated field name;
And splicing each piece of data and at least one piece of associated data corresponding to each piece of data to obtain a second query data set.
In some alternative embodiments, the execution module 705 is further configured to:
Extracting the type of the target view metadata from the target view metadata;
And packaging all data in the second query data set according to the type of the target view metadata to obtain a return result.
The device for querying service data in this embodiment is presented as a functional unit, where the unit refers to an ASIC (Application SPECIFIC INTEGRATED Circuit) Circuit, a processor and a memory that execute one or more software or firmware programs, and/or other devices that can provide the above functions.
The embodiment of the invention also provides a computer device which is provided with the device for generating the query statement shown in the figure 6 or the device for querying the service data shown in the figure 7.
Referring to fig. 8, fig. 8 is a schematic structural diagram of a computer device according to an alternative embodiment of the present invention, and as shown in fig. 8, the computer device includes one or more processors 10, a memory 20, and interfaces for connecting components, including a high-speed interface and a low-speed interface. The various components are communicatively coupled to each other using different buses and may be mounted on a common motherboard or in other manners as desired. The processor may process instructions executing within the computer device, including instructions stored in or on memory to display graphical information of the GUI on an external input/output device, such as a display device coupled to the interface. In some alternative embodiments, multiple processors and/or multiple buses may be used, if desired, along with multiple memories and multiple memories. Also, multiple computer devices may be connected, each providing a portion of the necessary operations (e.g., as a server array, a set of blade servers, or a multiprocessor system). One processor 10 is illustrated in fig. 8.
The processor 10 may be a central processor, a network processor, or a combination thereof. The processor 10 may further include a hardware chip, among others. The hardware chip may be an application specific integrated circuit, a programmable logic device, or a combination thereof. The programmable logic device may be a complex programmable logic device, a field programmable gate array, a general-purpose array logic, or any combination thereof.
Wherein the memory 20 stores instructions executable by the at least one processor 10 to cause the at least one processor 10 to perform the methods shown in implementing the above embodiments.
The memory 20 may include a storage program area that may store an operating system, application programs required for at least one function, and a storage data area that may store data created according to the use of the computer device, etc. In addition, the memory 20 may include high-speed random access memory, and may also include non-transitory memory, such as at least one magnetic disk storage device, flash memory device, or other non-transitory solid-state storage device. In some alternative embodiments, memory 20 may optionally include memory located remotely from processor 10, which may be connected to the computer device via a network. Examples of such networks include, but are not limited to, the internet, intranets, local area networks, mobile communication networks, and combinations thereof.
The memory 20 may comprise volatile memory, such as random access memory, or nonvolatile memory, such as flash memory, hard disk or solid state disk, or the memory 20 may comprise a combination of the above types of memory.
The computer device also includes a communication interface 30 for the computer device to communicate with other devices or communication networks.
The embodiments of the present invention also provide a computer readable storage medium, and the method according to the embodiments of the present invention described above may be implemented in hardware, firmware, or as a computer code which may be recorded on a storage medium, or as original stored in a remote storage medium or a non-transitory machine readable storage medium downloaded through a network and to be stored in a local storage medium, so that the method described herein may be stored on such software process on a storage medium using a general purpose computer, a special purpose processor, or programmable or special purpose hardware. The storage medium may be a magnetic disk, an optical disk, a read-only memory, a random-access memory, a flash memory, a hard disk, a solid state disk, or the like, and further, the storage medium may further include a combination of the above types of memories. It will be appreciated that a computer, processor, microprocessor controller or programmable hardware includes a storage element that can store or receive software or computer code that, when accessed and executed by the computer, processor or hardware, implements the methods illustrated by the above embodiments.
Portions of the present invention may be implemented as a computer program product, such as computer program instructions, which when executed by a computer, may invoke or provide methods and/or aspects in accordance with the present invention by way of operation of the computer. Those skilled in the art will appreciate that the existence of computer program instructions in a computer-readable medium includes, but is not limited to, source files, executable files, installation package files, and the like, and accordingly, the manner in which computer program instructions are executed by a computer includes, but is not limited to, the computer directly executing the instructions, or the computer compiling the instructions and then executing the corresponding compiled programs, or the computer reading and executing the instructions, or the computer reading and installing the instructions and then executing the corresponding installed programs. Herein, a computer-readable medium may be any available computer-readable storage medium or communication medium that can be accessed by a computer.
Although embodiments of the present invention have been described in connection with the accompanying drawings, various modifications and variations may be made by those skilled in the art without departing from the spirit and scope of the invention, and such modifications and variations fall within the scope of the invention as defined by the appended claims.
Claims (6)
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202410262185.7A CN118193567B (en) | 2024-03-07 | 2024-03-07 | Method, device, equipment and medium for generating query statements and querying business data |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202410262185.7A CN118193567B (en) | 2024-03-07 | 2024-03-07 | Method, device, equipment and medium for generating query statements and querying business data |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN118193567A CN118193567A (en) | 2024-06-14 |
| CN118193567B true CN118193567B (en) | 2025-07-18 |
Family
ID=91413105
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202410262185.7A Active CN118193567B (en) | 2024-03-07 | 2024-03-07 | Method, device, equipment and medium for generating query statements and querying business data |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN118193567B (en) |
Families Citing this family (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN119149625A (en) * | 2024-11-15 | 2024-12-17 | 思创数码科技股份有限公司 | Metadata configuration-based data query method and system |
Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102541529A (en) * | 2010-12-30 | 2012-07-04 | 北京亿阳信通软件研究院有限公司 | Query page generating device and method |
| CN115422167A (en) * | 2022-09-27 | 2022-12-02 | 北京柏睿数据技术股份有限公司 | Cross-data source database view visualization construction method and system |
Family Cites Families (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US11334585B2 (en) * | 2019-12-19 | 2022-05-17 | Sap Se | Enterprise search using database views |
| CN111241123A (en) * | 2020-01-07 | 2020-06-05 | 深圳市华宇讯科技有限公司 | View data query method, device, server and storage medium |
| US11386053B2 (en) * | 2020-10-15 | 2022-07-12 | Google Llc | Automatic generation of a data model from a structured query language (SQL) statement |
| CN112416966B (en) * | 2020-12-11 | 2024-01-26 | 北京顺达同行科技有限公司 | Impromptu query method, impromptu query device, computer device and storage medium |
| CN113342821B (en) * | 2021-06-29 | 2023-04-07 | 深圳前海微众银行股份有限公司 | Report configuration method, device, equipment and computer storage medium |
| US12197440B2 (en) * | 2022-03-24 | 2025-01-14 | International Business Machines Corporation | Database query performance improvement |
-
2024
- 2024-03-07 CN CN202410262185.7A patent/CN118193567B/en active Active
Patent Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102541529A (en) * | 2010-12-30 | 2012-07-04 | 北京亿阳信通软件研究院有限公司 | Query page generating device and method |
| CN115422167A (en) * | 2022-09-27 | 2022-12-02 | 北京柏睿数据技术股份有限公司 | Cross-data source database view visualization construction method and system |
Also Published As
| Publication number | Publication date |
|---|---|
| CN118193567A (en) | 2024-06-14 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN109582909B (en) | Webpage automatic generation method and device, electronic equipment and storage medium | |
| US6470349B1 (en) | Server-side scripting language and programming tool | |
| CN111708805B (en) | Data query method, device, electronic device and storage medium | |
| CN111339041A (en) | File parsing and warehousing and file generating method and device | |
| US9967370B2 (en) | OData enabled mobile software applications | |
| CN108572819A (en) | Page updating method, device, terminal and computer-readable storage medium | |
| CN112527738A (en) | Project description file optimization method, system and storage medium | |
| CN110851136A (en) | Data acquisition method, device, electronic device and storage medium | |
| CN118193567B (en) | Method, device, equipment and medium for generating query statements and querying business data | |
| CN112650529A (en) | System and method capable of configuring generation of mobile terminal APP code | |
| CN114282284A (en) | Model data processing method, apparatus, device and storage medium | |
| CN114168122A (en) | Method and device for generating data script | |
| EP1777629A1 (en) | Method and apparatus for automatic form filling | |
| CN112130860A (en) | JSON object analysis method and device, electronic device and storage medium | |
| CN116028062A (en) | Object code generation method, NPU command display method and device | |
| CN115269607A (en) | Method and device for generating dynamic report and electronic equipment | |
| CN113177391B (en) | Method for redirecting operation cursor in streaming interface, computing equipment and storage medium | |
| CN110795915B (en) | XML file batch modification method, system, device and computer-readable storage medium | |
| CN118152522A (en) | Data query method, device, electronic device, storage medium and product | |
| CN116151162B (en) | Register code and document automated design method, device, equipment and medium | |
| CN116257673A (en) | Data query method, device, equipment and storage medium based on ElasticSearch | |
| CN116483344A (en) | Code generation method and device, terminal equipment and computer readable storage medium | |
| CN116644085A (en) | Data query method and device, storage medium and electronic equipment | |
| CN118193551B (en) | Method, device, equipment and medium for updating database table and querying business data | |
| CN118643007B (en) | Interface document parsing method, device and storage medium |
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 | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |