A browser-based data retrieval method and system

Abstract

The application relates to the technical field of data retrieval, in particular to a browser-based data retrieval method and system, which comprises the following steps: configuring all fields of a main table of a database, and configuring fields of an associated subtable of the main table; obtaining inputted search fields and a connector to form a search condition; analyzing the search condition in a browser front end to generate corresponding JSON array data; analyzing the JSON array data to output a to-be-executed sql statement query condition; and based on the sql statement query condition, obtaining target data from the database table and the associated subtable. The application generates JSON array data by analyzing the search condition in the browser front end, and analyzes the JSON array data in the back end, so that the complexity and flexibility of the search condition and the accuracy of the search result are ensured; the application has strong expansibility in the later stage, and only needs to configure the properties of a data model in the back end, and the front-end and back-end analysis functions can automatically support new search conditions.

Description

Technical Field

[0001] This invention relates to the field of data retrieval technology, and in particular to a browser-based data retrieval method and system. Background Technology

[0002] Currently, most common office management systems rely on simple queries based on a few pre-set frequently used attributes, lacking the ability to perform complex, freely combinable queries. Complex combinations of conditions cannot be achieved using traditional browser search methods, and inappropriate search criteria will prevent the retrieval of desired data files.

[0003] For example, when retrieving a desired data file from a vast amount of data files, users must set search criteria before the search is conducted, and new search criteria are generated based on the search results. Existing data retrieval devices lack effective means for exploring appropriate search criteria, relying heavily on user search experience and familiarity with office materials, making it difficult to quickly find the required data file based on suitable search criteria. Summary of the Invention

[0004] This invention provides a browser-based data retrieval method and system to overcome the shortcomings of the prior art, facilitate users in customizing various search conditions, and greatly improve search accuracy.

[0005] This invention provides a browser-based data retrieval method, comprising:

[0006] Configure all fields of the main table in the database, and configure the fields of the related sub-tables of the main table;

[0007] Obtain the input search fields and search criteria composed of connectors;

[0008] The browser frontend parses the search criteria to generate the corresponding JSON array data;

[0009] The JSON array data is parsed to output the SQL query conditions to be executed;

[0010] Based on the query conditions of the SQL statement, the target data is retrieved from the database table and the associated sub-table.

[0011] The data retrieval method provided by the present invention further includes:

[0012] The database contains data for creating alternative search criteria, and the table fields for the alternative search criteria include the criterion attribute name, the parent criterion attribute name, and the criterion attribute description.

[0013] Generate the alternative search conditions for the main table and the sub-table respectively;

[0014] Construct an alternative condition tree based on the alternative search conditions of the main table and sub-tables.

[0015] According to the data retrieval method provided by the present invention, the retrieval conditions are parsed in the browser frontend to generate corresponding JSON array data, including...

[0016] Select any condition from the candidate condition tree, obtain the input search field, and obtain the operator type;

[0017] Obtain the conditions, operator types, and condition attribute names of the search fields from the candidate condition tree, and add them sequentially to the JSON;

[0018] After adding the corresponding operators and parentheses to the JSON, output JSON data or JSON array data.

[0019] According to the data retrieval method provided by the present invention, the JSON array data is parsed to output the query conditions of the SQL statement to be executed, including:

[0020] Based on the browser front-end parsing of the search conditions, a corresponding JSON array data is generated. The value of the left parenthesis in the JSON array data is extracted, and the number of left parentheses is accumulated.

[0021] Extract the condition attribute names of the conditions in the JSON array data, extract the condition attribute names of the corresponding search fields, and extract the condition attribute names of the corresponding operators;

[0022] Extract the value of the right parenthesis from the JSON array data, and count the number of right parentheses;

[0023] The SQL query conditions are output by sequentially concatenating the condition attribute name of the left parenthesis, the condition attribute name of the stated condition, the condition attribute name of the search field, the condition attribute name of the operator, and the condition attribute name of the right parenthesis.

[0024] According to the data retrieval method provided by the present invention, if the condition attribute name of the condition is the condition of the sub-table, then the corresponding SQL statement query condition for the subquery is generated.

[0025] On the other hand, the present invention also provides a browser-based data retrieval system, comprising:

[0026] The configuration module is used to configure all fields of the main table of the database and the fields of the related sub-tables of the main table;

[0027] The input module is used to obtain the input search fields and search conditions composed of connectors;

[0028] The generation module is used to parse the search criteria in the browser frontend and generate corresponding JSON array data;

[0029] The parsing module is used to parse the JSON array data and output the query conditions of the SQL statement to be executed;

[0030] The query module retrieves target data from the database table and the associated sub-table based on the query conditions of the SQL statement.

[0031] The present invention also provides an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the steps of any of the data retrieval methods described above.

[0032] The present invention also provides a non-transitory computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of any of the data retrieval methods described above.

[0033] This invention provides a browser-based data retrieval method and system. By parsing search conditions to generate a JSON array on the browser front-end and parsing the JSON array on the back-end, the complexity and flexibility of search conditions and the accuracy of search results are ensured. Furthermore, based on this method, the system has strong scalability. Only the attributes of the data model need to be configured on the back-end, and both the front-end and back-end parsing functions can automatically support new search conditions. Attached Figure Description

[0034] To more clearly illustrate the technical solutions in this invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of this invention. For those skilled in the art, other drawings can be obtained from these drawings without creative effort.

[0035] Figure 1 This is a flowchart illustrating the data retrieval method provided by the present invention. Detailed Implementation

[0036] To make the objectives, technical solutions, and advantages of this invention clearer, the technical solutions of this invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some, not all, of the embodiments of this invention. All other embodiments obtained by those skilled in the art based on the embodiments of this invention without creative effort are within the scope of protection of this invention.

[0037] The terms "comprising" and "having," and any variations thereof, in the specification, claims, and accompanying drawings of this application are intended to cover non-exclusive inclusion. For example, a process, method, system, product, or apparatus that includes a series of steps or modules is not limited to the steps or modules listed, but may optionally include steps or modules not listed, or may optionally include other steps or modules inherent to such process, method, product, or apparatus.

[0038] It should be noted that the terms "first" and "second" used in this invention merely distinguish similar objects and do not represent a specific ordering of objects. It is understood that "first" and "second" can be interchanged in a specific order or sequence where permissible. It should be understood that the objects distinguished by "first" and "second" can be interchanged where appropriate so that the embodiments of the invention described herein can be implemented in orders other than those described or illustrated herein.

[0039] It should be noted that this invention can be applied to application scenarios that frequently require data analysis and statistics, such as office management systems. For example, human resource management systems often need to statistically analyze personnel data across various dimensions, and using this invention for data retrieval and filtering can effectively meet user needs.

[0040] In one embodiment, such as Figure 1 As shown, the present invention provides a browser-based data retrieval method, including:

[0041] Configure all fields of the main table in the database, and configure the fields of the related sub-tables of the main table;

[0042] Obtain the input search fields and search criteria composed of connectors;

[0043] The browser frontend parses the search criteria to generate the corresponding JSON array data;

[0044] The JSON array data is parsed to output the SQL query conditions to be executed;

[0045] Based on the query conditions of the SQL statement, the target data is retrieved from the database table and the associated sub-table.

[0046] The data retrieval method provided by the present invention further includes:

[0047] The database contains data for creating alternative search criteria. The table fields for the alternative search criteria include the condition attribute name id, the parent condition attribute name parent id, and the condition attribute description name.

[0048] You can determine whether a condition is a main table condition or a sub-table condition based on the condition attribute name id.

[0049] Generate the alternative search conditions for the main table and the sub-table respectively;

[0050] Construct an alternative condition tree based on the alternative search conditions of the main table and sub-tables.

[0051] According to the data retrieval method provided by the present invention, parsing the search criteria in the browser frontend to generate corresponding JSON array data includes:

[0052] Select any condition from the candidate condition tree, obtain the input search field, and obtain the operator type;

[0053] Obtain the conditions, operator types, and condition attribute names of the search fields from the candidate condition tree, and add them sequentially to the JSON;

[0054] After adding the corresponding operators and parentheses to the JSON, output the JSON data or JSON array data;

[0055] In one specific implementation, when a condition is added, a corresponding JSON is generated in the browser's front end;

[0056] Furthermore, the JSON data structure is explained as follows: key represents the condition attribute value, operate represents the operator (equal to, contain, not equal to, not contain, front contain, back contain, etc.), value represents the search field of the condition (hereinafter referred to as the query value), link represents the concatenation operator of the condition (AND, OR), not_operate represents the operator "NOT", left_bracket represents the left bracket "(", and right_bracket represents the right bracket ")".

[0057] Explanation of front-end JSON constructor methods:

[0058] S1, select a condition from the candidate condition tree, select the operator type, and enter the query value;

[0059] S2, when the "Add" button is clicked, a JSON object is initialized, the id of the selected condition in the condition tree is the key, the id of the operator type is the operator, and the query value is the value, and these are added to the JSON object to get {"key":key,"operate":operate,"value":value};

[0060] S3, select the connector (AND, OR) according to the actual situation, and add the link attribute to the JSON;

[0061] S4, select the operator ("NOT") according to the actual situation, and add the not_operate attribute to the JSON;

[0062] S5. Add a left bracket as needed, and add a left_bracket attribute to the JSON. Clicking once will give the left_bracket value "(", clicking twice will give the value "((", and so on).

[0063] S6. Add a left bracket as needed, and add a right_bracket attribute to the JSON. Click once and the right_bracket value is ")", click twice and the value is "))", and so on.

[0064] The final complete JOSN is {"key":key,"operate":operate,"value":value,"link":link,"not_operate":not_operate,"right_bracket":right_bracket,

[0065] "left_bracket":left_bracket};

[0066] If multiple conditions are specified, the final output will be a JSON array object.

[0067] Operator rules: bef_contain means front containment, aft_contain means back containment, contain means containment, not_contain means not containment, not_null means not null, is_null means null, eq means equal to, not_eq means not equal to;

[0068] The not_operate operator rules: not means negation, and the meaning of the condition within its scope is reversed. The default value is empty.

[0069] Furthermore, according to the data retrieval method provided by the present invention, the JSON array data is parsed to output the query conditions of the SQL statement to be executed, including:

[0070] Based on the browser front-end parsing of the search conditions, a corresponding JSON array data is generated. The value of the left parenthesis in the JSON array data is extracted, and the number of left parentheses is accumulated.

[0071] Extract the condition attribute names of the conditions in the JSON array data, extract the condition attribute names of the corresponding search fields, and extract the condition attribute names of the corresponding operators;

[0072] Extract the value of the right parenthesis from the JSON array data, and count the number of right parentheses;

[0073] The SQL query conditions are output by sequentially concatenating the condition attribute name of the left parenthesis, the condition attribute name of the stated condition, the condition attribute name of the search field, the condition attribute name of the operator, and the condition attribute name of the right parenthesis.

[0074] Explanation of the method for parsing JOSN in the background:

[0075] S1, extract the value of left_bracket (left bracket) and count the number of left brackets;

[0076] S2, extract the value of key (the attribute value of the condition). If the key is in a format like e.e1, it is a condition of the sub-table. Based on the value of e, match the corresponding subquery: select e.parentId from ee;

[0077] S3 extracts the values ​​of value (the query value of the condition), operate (the operator), not_operate (the notion operator), and link (the connector). If key is a subtable query condition, the complete subquery is link id not_operate in(select e.parentId from ee where key operate value);

[0078] S4, extract the value of `right_bracket` (right bracket) and count the number of right brackets. Concatenate and combine them to obtain the corresponding JOSN condition SQL. The SQL for querying the main table is: `left_bracket link key operate value right_bracket`, and the SQL for querying the child table is: `left_bracket link id not_operate in(select e.parentIdfrom ee where key operate value)right_bracket`. Note that `operate` and `not_operate` need to be converted according to their actual meanings; for example, "not like 'key%'" is used for "not preceding containment".

[0079] S5 parses the next JSON in the same way as the first three steps, and finally compares the cumulative number of left parentheses with the cumulative number of right parentheses. It then adds up the missing right parentheses to get the complete SQL statement for the query conditions.

[0080] It should be noted that the actual search conditions can be freely combined, and there are no restrictions on the number of conditions or the logical complexity.

[0081] As an example, add the conditions shown in the table below:

[0082] Ethnicity is not the same as Han ethnicity And (job information.job status equals "in office") Or job information. (Job status is equal to "temporarily appointed") And the name does not include Zhang

[0083] The corresponding JSON for its first condition is as follows:

[0084] {"key": "nation", "operate": "not_eq", "value": "Han nationality", "link": ""}, each condition corresponds to a JSON, and all retrieval conditions form a JSON array:

[0085] [{"key": "nation", "operate": "not_eq", "value": "Han nationality", "link": ""};

[0086] {"key": "duty.jobStatus", "operate": "eq", "value": "in office",

[0087] "right_bracket": "", "left_bracket": "(", "link": "AND"};

[0088] {"key": "duty.jobStatus", "operate": "eq", "value": "acting", "link": "OR", "left_bracket": "", "right_bracket": ")"};

[0089] {"key": "name", "operate": "bef_contain", "value": "Zhang", "link": "AND", "not_operate": "NOT", "right_bracket": "", "left_bracket": ""}].

[0090] After constructing the conditions on the browser page, submit them to the background for parsing. The finally parsed retrieval conditions are as follows:

[0091] t.nation!='Han nationality' AND (t.id in (select duty.parentId from Duty duty where duty.jobStatus = 'in office') or t.id in (select duty.parentId from Duty duty where duty.jobStatus = 'acting')) AND t.name not like '%Zhang'.

[0092] In practical applications, the data for alternative search conditions and the correspondence between sub-table conditions and sub-tables are configured according to the actual situation.

[0093] The above embodiments are merely examples of embodiments of the present invention and should not be construed as further limiting the present invention.

[0094] According to the data retrieval method provided by the present invention, if the condition attribute name of the condition is the condition of the sub-table, then the corresponding SQL statement query condition for the subquery is generated.

[0095] On the other hand, the present invention also provides a browser-based data retrieval system. The provided data retrieval system and the data retrieval method described above can be referred to in correspondence, including a configuration module, an input module, a generation module, a parsing module, and a query module:

[0096] The configuration module is used to configure all fields of the main table of the database and to configure the fields of the related sub-tables of the main table.

[0097] The input module is used to obtain the input search fields and search conditions composed of connectors;

[0098] The generation module is used to parse the search criteria in the browser frontend and generate corresponding JSON array data;

[0099] The parsing module is used to parse the JSON array data and output the query conditions of the SQL statement to be executed;

[0100] The query module retrieves target data from the database table and the associated sub-table based on the query conditions of the SQL statement.

[0101] This invention also provides an electronic device, which may include: a processor, a communication interface, a memory, and a communication bus, wherein the processor, the communication interface, and the memory communicate with each other via the communication bus. The processor can invoke logical instructions stored in the memory to execute the steps of a browser-based data retrieval method provided by the above methods, including:

[0102] Configure all fields of the main table in the database, and configure the fields of the related sub-tables of the main table;

[0103] Obtain the input search fields and search criteria composed of connectors;

[0104] The browser frontend parses the search criteria to generate the corresponding JSON array data;

[0105] The JSON array data is parsed to output the SQL query conditions to be executed;

[0106] Based on the query conditions of the SQL statement, the target data is retrieved from the database table and the associated sub-table.

[0107] Furthermore, the logical instructions in the aforementioned memory can be implemented as software functional units and sold or used as independent products, and can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, or the part that contributes to the prior art, or a part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of the present invention. The aforementioned storage medium includes various media capable of storing program code, such as USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.

[0108] On the other hand, the present invention also provides a computer program product, the computer program product comprising a computer program stored on a non-transitory computer-readable storage medium, the computer program comprising program instructions, wherein when the program instructions are executed by a computer, the computer is able to perform the steps of a browser-based data retrieval method provided by the above methods, including:

[0109] Configure all fields of the main table in the database, and configure the fields of the related sub-tables of the main table;

[0110] Obtain the input search fields and search criteria composed of connectors;

[0111] The browser frontend parses the search criteria to generate the corresponding JSON array data;

[0112] The JSON array data is parsed to output the SQL query conditions to be executed;

[0113] Based on the query conditions of the SQL statement, the target data is retrieved from the database table and the associated sub-table.

[0114] In another aspect, the present invention also provides a non-transitory computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of a browser-based data retrieval method provided by the methods described above, including:

[0115] Configure all fields of the main table in the database, and configure the fields of the related sub-tables of the main table;

[0116] Obtain the input search fields and search criteria composed of connectors;

[0117] The browser frontend parses the search criteria to generate the corresponding JSON array data;

[0118] The JSON array data is parsed to output the SQL query conditions to be executed;

[0119] Based on the query conditions of the SQL statement, the target data is retrieved from the database table and the associated sub-table.

[0120] The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs. Those skilled in the art can understand and implement this without any creative effort.

[0121] Through the above description of the embodiments, those skilled in the art can clearly understand that each embodiment can be implemented by means of software plus necessary general-purpose hardware platforms, and of course, it can also be implemented by hardware. Based on this understanding, the above technical solutions, in essence or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute the methods described in the various embodiments or some parts of the embodiments.

[0122] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, and not to limit them; although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features; and these modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A browser-based data retrieval method, characterized in that, include: Configure all fields of the main table in the database, and configure the fields of the related sub-tables of the main table; Obtain the input search fields and search criteria composed of connectors; The browser frontend parses the search criteria to generate the corresponding JSON array data; The JSON array data is parsed to output the SQL query conditions to be executed; Based on the SQL query conditions, the target data is retrieved from the database table and the associated sub-table; The process of parsing the JSON array data and outputting the SQL query conditions to be executed includes: Step 1: Extract the value of the left parenthesis of any JSON string in the JSON array data, and count the number of left parentheses; Step 2: Extract the conditional attribute names of the JSON, extract the conditional attribute names of the corresponding search fields, and extract the conditional attribute names of the corresponding operators. Step 3: Extract the value of the right parenthesis in the JSON and count the number of right parentheses; then concatenate the condition attribute name of the left parenthesis, the condition attribute name of the condition, the condition attribute name of the search field, the condition attribute name of the operator, and the condition attribute name of the right parenthesis in sequence, and output the SQL query condition corresponding to the JSON. Step 4: Parse the next JSON in sequence according to Steps 1 to 3 until all JSON in the JSON array data has been parsed; Step 5: Compare the cumulative number of left parentheses with the cumulative number of right parentheses, and supplement the missing right parentheses to obtain the SQL query conditions for the JSON array data.

2. The data retrieval method according to claim 1, characterized in that, include: The database contains data for creating alternative search criteria, and the table fields for the alternative search criteria include the criterion attribute name, the parent criterion attribute name, and the criterion attribute description. Generate the alternative search conditions for the main table and the sub-table respectively; Construct an alternative condition tree based on the alternative search conditions of the main table and sub-tables.

3. The data retrieval method according to claim 2, characterized in that, The browser frontend parses the search criteria to generate the corresponding JSON array data, including Select any condition from the candidate condition tree, obtain the input search field, and obtain the operator type; Obtain the conditions, operator types, and condition attribute names of the search fields from the candidate condition tree, and add them sequentially to the JSON; After adding the corresponding operators and parentheses to the JSON, output JSON data or JSON array data.

4. The data retrieval method according to claim 3, characterized in that, If the condition attribute name of the condition is the condition of the sub-table, then the corresponding SQL statement query condition for the subquery is generated.

5. A browser-based data retrieval system, characterized in that, include: The configuration module is used to configure all fields of the main table of the database and the fields of the related sub-tables of the main table; The input module is used to obtain the input search fields and search conditions composed of connectors; The generation module is used to parse the search criteria in the browser frontend and generate corresponding JSON array data; The parsing module is used to parse the JSON array data and output the query conditions of the SQL statement to be executed; The query module retrieves target data from the database table and the associated sub-table based on the query conditions of the SQL statement; The parsing module is further configured to: Step 1, extract the value of the left parenthesis of any JSON in the JSON array data and accumulate the number of left parentheses; Step 2, extract the conditional attribute name of the JSON, extract the conditional attribute name of the corresponding search field, and extract the conditional attribute name of the corresponding operator; Step 3, extract the value of the right parenthesis of the JSON and accumulate the number of right parentheses. Step 4: Concatenate the conditional attribute name of the left parenthesis, the conditional attribute name of the condition, the conditional attribute name of the search field, the conditional attribute name of the operator, and the conditional attribute name of the right parenthesis in sequence to output the SQL query conditions corresponding to the JSON; Step 5: Parse the next JSON in sequence according to Steps 1 to 3 until all JSON in the JSON array data has been parsed; Step 6: Compare the cumulative number of left parentheses with the cumulative number of right parentheses, and supplement the missing right parentheses to obtain the SQL query conditions of the JSON array data.

6. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the program, it implements the steps of the method as described in any one of claims 1 to 4.

7. A non-transitory computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the steps of the method as described in any one of claims 1 to 4.

Images