A dynamic query method and system

Abstract

The present application discloses a dynamic query method and system, which relates to the field of information query technology, including: obtaining a local query data source, a query table, and a first mapping between a data type and a query type according to a query data source ID sent by a business party; determining the query type corresponding to each field in the query table according to a mapping model between the data type and the query type and the first mapping, and returning the query type and its corresponding query method to the business party; receiving query conditions set by the business party, and converting the query conditions into a query statement of a target database according to the query data source; inserting the query data source and the query table into the query statement, executing the new query statement to obtain the query result, and returning the query result to the business party. The present application provides a query system located between a business and a database, which can shield the differences between different databases from the business party, so that when the business is changed or the query field is changed, there is no need to modify the code or redevelop.

Description

Technical Field

[0001] The present application relates to the field of information query technology, and in particular to a dynamic query method and system. Background Art

[0002] Currently, query solutions are developed separately for specific businesses to implement specific query logic. Every change in the business requires changing the query code or redevelopment.

[0003] When the business fields are few and relatively fixed, there is no major problem with targeted development implementation, and the maintainability is relatively high. However, the queries for the same type of data in different businesses or different presentation methods of the same business are basically the same, so there will be a lot of logical duplication of code.

[0004] If business fields change frequently or need to be added or changed dynamically, the targeted development implementation will frequently change the code and be unstable. Summary of the invention

[0005] The present application provides a dynamic query method, which aims to solve the problem of frequent changes in query codes due to business changes in the prior art.

[0006] To achieve the above objectives, this application adopts the following technical solutions:

[0007] A dynamic query method of the present application comprises the following steps:

[0008] According to the query data source ID sent by the business party, obtain the query data source, query table and first mapping between the data type and the query type stored locally;

[0009] Determine the query type corresponding to each field in the query table according to a pre-established mapping model between data types and query types and the first mapping, and return the query type and its corresponding query method to the business party;

[0010] Receiving the query conditions set by the business party according to the query type and query mode, and converting the query conditions into query statements of the target database according to the query data source;

[0011] After inserting the query data source and the query table into the query statement, the new query statement is executed to obtain the query result, and the query result is returned to the business party.

[0012] Preferably, the step of obtaining the locally corresponding stored query data source, query table, and first mapping between data type and query type according to the query data source ID sent by the business party includes:

[0013] Verify the query data source, query table, and first mapping between data type and query type configured by the business party. If the verification passes, save the query data source, query table, and first mapping, and return the ID corresponding to the query data source to the business party.

[0014] Preferably, the determining the query type corresponding to each field in the query table according to the pre-established mapping model between the data type and the query type and the first mapping includes:

[0015] Mapping each field in the query table to a corresponding query type according to a pre-established mapping model between data types and query types;

[0016] The first mapping is loaded to determine whether the query type of each field in the query table is specified in the first mapping, and if so, the query type specified in the first mapping is used as the primary one.

[0017] Preferably, the if then the first mapping is mainly specified also includes:

[0018] When the first mapping specifies that a certain field of the query table is not supported, the query type obtained according to the mapping model for the field is deleted.

[0019] Preferably, converting the query condition into a query statement of a target database according to the query data source includes:

[0020] Parsing the query condition to obtain a target field name, a target query type, a target query method, and a target matching value;

[0021] Determine the target database type according to the query data source, and call the query converter corresponding to the target database;

[0022] The target field name, target query type, target query method and target matching value are input into the query converter to output the query statement corresponding to the target database.

[0023] Preferably, inserting the query data source and the query table into the query statement, executing the new query statement to obtain the query result, and returning the query result to the business party includes:

[0024] Assembling the query table and the query data source into the query statement to obtain a new query statement, and executing the new query statement in the target database to obtain a query result;

[0025] The query result is converted into a field name according to the first mapping and mapping model, and the field name and its corresponding field value are returned to the business party.

[0026] A dynamic query device, comprising:

[0027] An acquisition module, used to acquire a locally corresponding stored query data source, a query table, and a first mapping between a data type and a query type according to a query data source ID sent by a business party;

[0028] An interaction module, configured to determine the query type corresponding to each field in the query table according to a pre-established mapping model between data types and query types and the first mapping, and return the query type and its corresponding query method to the business party;

[0029] A conversion module, used to receive the query conditions set by the business party according to the query type and query mode, and convert the query conditions into query statements of the target database according to the query data source;

[0030] The query module is used to insert the query data source and query table into the query statement, execute the new query statement to obtain the query result, and return the query result to the business party.

[0031] Preferably, the system also includes a verification module, which is used to verify the query data source, query table and the first mapping between data type and query type configured by the business party. If the verification passes, the query data source, query table and first mapping are saved, and the ID corresponding to the query data source is returned to the business party.

[0032] An electronic device comprises a memory and a processor, wherein the memory is used to store one or more computer instructions, wherein the one or more computer instructions are executed by the processor to implement a dynamic query method as described in any one of the above.

[0033] A computer-readable storage medium storing a computer program, wherein the computer program enables a computer to implement a dynamic query method as described in any one of the above when executed.

[0034] The present invention has the following beneficial effects:

[0035] The present application provides a stable query system located between the business and the database, which can shield the differences between different databases from the business side, and does not require code modification or redevelopment when the business changes or the query field changes. When necessary, only the relevant configuration needs to be changed, which is more stable. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative labor.

[0037] Figure 1 This is a first flow chart of a dynamic query method implemented in Example 1 of the present application;

[0038] Figure 2 This is the second flow chart of a dynamic query method implemented in Example 2 of the present application

[0039] Figure 3 is a schematic diagram of a dynamic query system implemented in Example 3 of the present application;

[0040] Figure 4 It is a schematic diagram of an electronic device for implementing a dynamic query method according to Example 4 of the present application. DETAILED DESCRIPTION

[0041] The following will be combined with the accompanying drawings to clearly and completely describe the technical solutions in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by those skilled in the art without creative work are within the scope of protection of the present application.

[0042] The terms "first", "second", etc. in the claims and specification of the present application are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that the terms used in this way are interchangeable under appropriate circumstances. This is merely a way of distinguishing objects with the same properties in the embodiments of the present application. In addition, the terms "including" and "having" and any variations thereof are intended to cover non-exclusive inclusions, so that a process, method, system, product or apparatus that includes a series of units is not necessarily limited to those units, but may include other units not expressly listed or inherent to these processes, methods, products or apparatus.

[0043] Example 1

[0044] like Figure 1 As shown, a dynamic query method comprises the following steps:

[0045] S110, acquiring the query data source, query table, and first mapping between data type and query type stored locally according to the query data source ID sent by the business party;

[0046] S120, determining the query type corresponding to each field in the query table according to a pre-established mapping model between data types and query types and the first mapping, and returning the query type and its corresponding query method to the business party;

[0047] S130, receiving the query conditions set by the business party according to the query type and query mode, and converting the query conditions into a query statement of the target database according to the query data source;

[0048] S140: After inserting the query data source and the query table into the query statement, execute the new query statement to obtain a query result, and return the query result to the business party.

[0049] This embodiment mainly involves the interaction between the business party, the dynamic query system and the database, wherein the dynamic query system is the subject described in the above method and is also the medium between the business party and the database. Its function is to shield the differences between various databases from the business party, so that business changes do not require rewriting code. The business party first configures the query data source, query table, and the first mapping between the data type and the query type in the dynamic query system. The first mapping is manually configured. After the business party completes the configuration, the dynamic query system will verify the configured information, mainly to check whether the data source information is correct, that is, whether the dynamic query system can connect to the query data source, determine whether the query table exists, and whether the configuration format of the first mapping is correct. After the verification, the query data source, query table, first mapping and other information will be saved locally, and the ID corresponding to the query data source will be returned to the business party. The business party obtains the corresponding query data source, query table and first mapping in the dynamic query system according to the query data source ID. If the acquisition fails, an error is returned. If the acquisition is successful, all queryable field information is determined according to the query table, and then the query type and query method corresponding to each field are determined according to the pre-established mapping model between the data type and the query type and the first mapping. The mapping model is automatic mapping, and the obtained query type and corresponding query method are returned to the business party. The business party sets the query conditions according to the query type and query method, and goes to the dynamic query system to query after the query conditions are set. The dynamic query system determines the type of database to be queried according to the query data source, and then calls the corresponding query converter according to the database type. The principle of the query converter is to develop the corresponding language conversion logic according to the database type to be supported. For example, the query is for data named Zhang San, the data source is configured as MySQL database and user data table, and the query condition is set as [data source ID = 123, name = Zhang San, type = string, operator = eq]. When querying, it can be found through the data source that the database to be queried is MySQL and the table to be queried is user. Based on this, a converter that is equal to the string of the MySQL database, that is, (type = string, operator = eq), is found. When (name = Zhang San) is passed in, it will be converted into the corresponding MySQL query statement [name = 'Zhang San']. Therefore, the query condition is entered into the corresponding query converter for conversion, and the query statement of the target database is obtained. Finally, the query data source and the query table are assembled into the query statement to obtain a new query statement. The new query statement is executed to obtain the query result, and then the query result is returned to the business party. Through the dynamic query system, you only need to configure the data source information in the early stage, and all subsequent queries can be implemented using the dynamic query system. Even if the business changes, query fields change, or query fields are dynamically added, there is no need to change the business party's query code.

[0050] Example 2

[0051] like Figure 2 As shown, a dynamic query method comprises the following steps:

[0052] S210, verifying the query data source, query table, and first mapping between data type and query type configured by the business party, and if the verification passes, saving the query data source, query table, and first mapping, and returning an ID corresponding to the query data source to the business party;

[0053] S220, acquiring the query data source, query table, and first mapping between data type and query type stored locally according to the query data source ID sent by the business party;

[0054] S230, mapping each field in the query table to a corresponding query type according to a pre-established mapping model between data types and query types;

[0055] S240, loading the first mapping, determining whether the first mapping specifies the query type of each field in the query table, and if so, taking the query type specified by the first mapping as the primary one;

[0056] S250, receiving the query condition set by the business party according to the query type and query method, parsing the query condition, and obtaining a target field name, a target query type, a target query method, and a target matching value;

[0057] S260, determining a target database type according to the query data source, and calling a query converter corresponding to the target database;

[0058] S270, input the target field name, target query type, target query method and target matching value into the query converter to output the query statement corresponding to the target database;

[0059] S280: After inserting the query data source and the query table into the query statement, execute the new query statement to obtain the query result, and return the query result to the business party.

[0060] According to Example 2, the mapping relationship between data types and query types is divided into two categories. One is the manually configured first mapping, which mainly includes three mapping relationships. The first is the mapping relationship of special data, such as 0 and 1 stored in the database, which should be rendered as male and female drop-down boxes or radio buttons in the page query condition rendering; the database stores JSON of personnel information, but only needs to query according to the name of the person in the query; the second is field information that does not need to be displayed, such as the creation time and modification time in the data, which do not need to be rendered in the page query conditions; the information of the product in the database (name, unit price, picture, etc.) does not need to be displayed when querying; the third is the query information that is not supported in the data table, such as the product in the order The quantities are not calculated and saved in the data table in advance. These can be assigned mapping relationships through manual configuration. The other type is the pre-established basic data mapping model, which belongs to automatic mapping. Through automatic mapping and manual mapping, the corresponding data, such as the fields in the query table, are mapped according to the data type (number, character, time, set, etc.) combined with the application scenario (address, organizational personnel structure, etc.) and the query display method (drop-down box, search box, time selector, etc.) to the basic query type (value, text, time, address, etc.), and each query type can correspond to one or more query methods (such as exact and fuzzy search of text; greater than, equal to, less than, greater than or equal to, less than or equal to, and range of values; contains one, contains all, does not contain, etc. of the set).

[0061] After obtaining the corresponding query data source, query table and the first mapping between data type and query type according to the query data source ID, first determine to obtain the query table structure information according to the query table information, then obtain all the queryable field information in the query table, including the data type corresponding to each field, and then input the corresponding data type into the pre-established data type and query type mapping model to obtain the query type supported by each field, and at the same time search the first mapping to determine whether it has configured each field. If not, skip this step directly. If the mapping relationship of a field is specified in the first mapping, the one specified in the first mapping is used as the main one. If the first mapping clearly indicates that the query of the field is not supported, the query type obtained for the field according to the mapping model is also deleted, and no query is performed.

[0062] The final query type and its corresponding query method are returned to the business party. The business party sets query conditions according to the query type and query method, and sends the query conditions to the dynamic query system. The dynamic query system parses the query conditions to obtain the target field name, target query type, target query method and target matching value, wherein the matching value refers to the specific value to be queried. For example, if the query name is Zhang San's data, the query condition includes: the field name of the name (name indicates name), the query type code (string indicates string), the query method code (eq indicates equality), and the matching value (Zhang San). Then, the database type to be queried, that is, the target database type, is obtained according to the query data source. The corresponding query converter is called according to the target database type. The query converter is as described in Example 1. The query field, matching value, query type, and query method to be queried are passed into the corresponding query converter for conversion to obtain the query statement corresponding to the target database, and then The query statement is assembled according to the query data source and the query table to obtain a complete query statement. The query result can be obtained by executing the complete query statement in the target database. The dynamic query system converts the query result into the format of (field name: field value) and then returns it to the business party. The return results of queries in different databases may be inconsistent. The dynamic query system unifies the format of all query results into (field name: field value). The conversion here is mainly the conversion of data types. For example, the database stores 0 and 1, and the results returned during the query are male and female. When converting, it is necessary to first determine whether to convert. If converted (if converted, the identifier [transform=true] that needs to be converted will be added to the query condition), the corresponding mapping relationship will be obtained through the query data source ID passed during the query, and the data type and conversion value corresponding to the query result will be determined according to the mapping relationship (for example, according to a special mapping relationship (male 0 / female 1) the corresponding value is converted into its specific meaning).

[0063] Through the dynamic query system, as long as the data source information is configured in the early stage, all subsequent queries can be implemented through the dynamic query system. Even if business changes, query field changes, or query fields are dynamically added, there is no need to change the business query code. Only the mapping configuration needs to be changed. If there is a query requirement for special data not included in the dynamic query, you only need to add a query type and corresponding query method in the dynamic form in the specified way, and specify this data as a new query type in the mapping configuration, which has better stability.

[0064] Example 3

[0065] like Figure 3 As shown, a dynamic query system includes a verification module, an acquisition module, an interaction module, a conversion module and a query module:

[0066] The verification module is used to verify the query data source, query table and the first mapping between the data type and the query type configured by the business party. If the verification passes, the query data source, query table and the first mapping are saved, and the ID corresponding to the query data source is returned to the business party.

[0067] The acquisition module is used to acquire the query data source, query table and first mapping between data type and query type stored locally according to the query data source ID sent by the business party;

[0068] The interaction module is used to determine the query type corresponding to each field in the query table according to a pre-established mapping model between data types and query types and the first mapping, and return the query type and its corresponding query method to the business party;

[0069] The conversion module is used to receive the query conditions set by the business party according to the query type and query mode, and convert the query conditions into query statements of the target database according to the query data source;

[0070] The query module is used to insert the query data source and the query table into the query statement, execute the new query statement to obtain the query result, and return the query result to the business party.

[0071] Example 4

[0072] like Figure 4 As shown, an electronic device includes a memory 401 and a processor 402, wherein the memory 401 is used to store one or more computer instructions, wherein the one or more computer instructions are executed by the processor 402 to implement the above-mentioned dynamic query method.

[0073] Those skilled in the art can clearly understand that, for the convenience and brevity of description, the specific working process of the electronic device described above can refer to the corresponding process in the aforementioned method embodiment, and will not be repeated here.

[0074] A computer-readable storage medium storing a computer program, wherein the computer program enables a computer to implement a dynamic query method as described above when executed.

[0075] Exemplarily, the computer program may be divided into one or more modules / units, one or more modules / units are stored in the memory 401 and executed by the processor 402, and the I / O interface transmission of data is completed by the input interface 405 and the output interface 406 to complete the present invention. The one or more modules / units may be a series of computer program instruction segments that can complete specific functions, and the instruction segments are used to describe the execution process of the computer program in the computer device.

[0076] The computer device may be a computing device such as a desktop computer, a notebook, a PDA, a cloud server, etc. The computer device may include, but is not limited to, a memory 401 and a processor 402. Those skilled in the art may understand that this embodiment is only an example of a computer device and does not constitute a limitation on the computer device. The computer device may include more or fewer components, or a combination of certain components, or different components. For example, the computer device may also include an input device 407, a network access device, a bus, etc.

[0077] The processor 402 may be a central processing unit (CPU), or other general-purpose processors 402, digital signal processors 402 (DSP), application-specific integrated circuits (ASIC), field-programmable gate arrays (FPGA), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. The general-purpose processor 402 may be a microprocessor 402 or the processor 402 may also be any conventional processor 402, etc.

[0078] The memory 401 may be an internal storage unit of a computer device, such as a hard disk or memory of the computer device. The memory 401 may also be an external storage device of the computer device, such as a plug-in hard disk, a smart media card (SMC), a secure digital (SD) card, a flash card, etc. equipped on the computer device. Furthermore, the memory 401 may include both an internal storage unit of the computer device and an external storage device. The memory 401 is used to store computer programs and other programs and data required by the computer device. The memory 401 may also be used to temporarily store in the output device 408. The aforementioned storage media include various media that can store program codes, such as a USB flash drive, a mobile hard disk, a read-only memory ROM403, a random access memory RAM404, a disk or an optical disk.

[0079] The above is only a specific embodiment of the present invention, but the protection scope of the present invention is not limited thereto. Any changes or substitutions within the technical scope disclosed by the present invention should be included in the protection scope of the present invention. Therefore, the protection scope of the present invention should be based on the protection scope of the claims.

Claims

1. A dynamic query method, characterized in that: The following steps are involved: According to the query data source ID sent by the business party, the locally corresponding stored query data source, query table, and the first mapping between the manually configured data type and the query type are obtained; Determine the query type corresponding to each field in the query table according to a pre-established mapping model between data types and query types and the first mapping, and return the query type and its corresponding query method to the business party; Receiving the query conditions set by the business party according to the query type and query mode, and converting the query conditions into query statements of the target database according to the query data source; After inserting the query data source and the query table into the query statement, executing the new query statement to obtain the query result, and returning the query result to the business party; Wherein, determining the query type corresponding to each field in the query table according to a pre-established mapping model between data types and query types and the first mapping includes: Through the pre-established mapping model between data types and query types and the first mapping, the fields in the query table are mapped to query types including values, text, time and address according to data types including numbers, characters, time and sets, application scenarios including addresses and organizational personnel structures, and query display methods including drop-down boxes, search boxes and time selectors, and each query type corresponds to one or more query methods, including precise and fuzzy searches for texts, greater than, equal to, less than, greater than or equal to, less than or equal to and ranges for values, and one-included, all-included and none-included for sets; The converting the query condition into a query statement of a target database according to the query data source includes: Parsing the query condition to obtain a target field name, a target query type, a target query method, and a target matching value; Determine the target database type according to the query data source, and call the query converter corresponding to the target database; The target field name, target query type, target query method and target matching value are input into the query converter to output the query statement corresponding to the target database.

2. A dynamic query method according to claim 1, characterized in that: The method of obtaining the query data source, query table, and first mapping between the data type and the query type stored locally according to the query data source ID sent by the business party includes: Verify the query data source, query table, and first mapping between data type and query type configured by the business party. If the verification passes, save the query data source, query table, and first mapping, and return the ID corresponding to the query data source to the business party.

3. A dynamic query method according to claim 1, characterized in that: The determining the query type corresponding to each field in the query table according to the pre-established mapping model between the data type and the query type and the first mapping includes: Mapping each field in the query table to a corresponding query type according to a pre-established mapping model between data types and query types; The first mapping is loaded to determine whether the query type of each field in the query table is specified in the first mapping, and if so, the query type specified in the first mapping is used as the primary one.

4. A dynamic query method according to claim 3, characterized in that: The if so, taking the first mapping as the main one also includes: When the first mapping specifies that a certain field of the query table is not supported, the query type obtained according to the mapping model for the field is deleted.

5. A dynamic query method according to claim 1, characterized in that: The inserting the query data source and the query table into the query statement and then executing the new query statement to obtain the query result, and returning the query result to the business party, comprises: Assembling the query table and the query data source into the query statement to obtain a new query statement, and executing the new query statement in the target database to obtain a query result; The query result is converted into a field name according to the first mapping and mapping model, and the field name and its corresponding field value are returned to the business party.

6. A dynamic query system, characterized in that: include: An acquisition module, used to acquire a locally corresponding stored query data source, a query table, and a first mapping between a data type and a query type according to a query data source ID sent by a business party; An interaction module, configured to determine the query type corresponding to each field in the query table according to a pre-established mapping model between data types and query types and the first mapping, and return the query type and its corresponding query method to the business party; A conversion module, used to receive the query conditions set by the business party according to the query type and query mode, and convert the query conditions into query statements of the target database according to the query data source; A query module, used to insert the query data source and query table into the query statement, execute the new query statement to obtain the query result, and return the query result to the business party; The interactive module determines the query type corresponding to each field in the query table according to the pre-established mapping model between the data type and the query type and the first mapping, including: Through the pre-established mapping model between data types and query types and the first mapping, the fields in the query table are mapped to query types including values, text, time and address according to data types including numbers, characters, time and sets, application scenarios including addresses and organizational personnel structures, and query display methods including drop-down boxes, search boxes and time selectors, and each query type corresponds to one or more query methods, including precise and fuzzy searches for texts, greater than, equal to, less than, greater than or equal to, less than or equal to and ranges for values, and one-included, all-included and none-included for sets; The conversion module converts the query condition into a query statement of the target database according to the query data source, including: Parsing the query condition to obtain a target field name, a target query type, a target query method, and a target matching value; Determine the target database type according to the query data source, and call the query converter corresponding to the target database; The target field name, target query type, target query method and target matching value are input into the query converter to output the query statement corresponding to the target database.

7. A dynamic query system according to claim 6, characterized in that: The system also includes a verification module, which is used to verify the query data source, query table and the first mapping between the data type and the query type configured by the business party. If the verification passes, the query data source, query table and the first mapping are saved, and the ID corresponding to the query data source is returned to the business party.

8. An electronic device, characterized in that: It comprises a memory and a processor, wherein the memory is used to store one or more computer instructions, wherein the one or more computer instructions are executed by the processor to implement a dynamic query method as claimed in any one of claims 1 to 5.

9. A computer-readable storage medium storing a computer program, characterized in that: The computer program enables a computer to implement a dynamic query method according to any one of claims 1 to 5 when executed.

Images