Data query method and device based on dynamic rule combination and electronic equipment

Abstract

The invention provides a dynamic rule combination-based data query method and apparatus, and an electronic device.The method comprises the steps of firstly obtaining query parameter information input by a user, and then analyzing fields, operators and values in each query parameter group; based on the analyzed fields, operators and values, a corresponding target processing rule is matched for each query parameter group from a preset processing rule set, and then the query parameter group is processed according to the corresponding target processing rule for each query parameter group; and generating an SQL query statement based on a preset logic operator set and a processing result of each query parameter group, and finally executing the SQL query statement to query the database to obtain a database query result. By adopting the method and the device, the query parameter information can be automatically converted into the SQL query statement, the operation complexity of multi-condition complex query can be reduced, and meanwhile, the universality of the multi-condition complex query in diversified query scenes is improved.

Description

Technical Field

[0001] The present invention relates to the technical field of data query, and in particular to a data query method, device and electronic device based on dynamic rule combination. Background Art

[0002] In the data query scenario based on Spring Data JPA, the prior art mainly adopts the following several solutions to achieve complex multi-condition queries:

[0003] (1) Static Method Name Convention: By defining methods that conform to the naming rules (such as findByNameAndPriceLessThan) in the Repository interface, the corresponding SQL query statements are parsed and generated by Spring Data JPA. This solution only supports predefined field combinations and cannot dynamically expand conditions; when the number of conditions increases, the method name becomes long (such as findByXAndYAndZOrA...), resulting in poor code readability and violation of coding specifications; this solution only supports simple operators and cannot support complex operations (such as BETWEEN, JOIN queries or subqueries).

[0004] (2) Manually write Specification implementation classes: By implementing the toPredicate method of the Specification interface, manually write the combination logic of each query condition. In this solution, a separate Specification class needs to be implemented for each query scenario, resulting in a large amount of duplicate code and high code redundancy. When the business logic changes (such as adjusting the AND / OR relationship between conditions), the code needs to be modified class by class, which is prone to introducing human errors. The logic of manually splicing Predicate is cumbersome, especially when dealing with nested logical relationships, and the code readability drops sharply.

[0005] (3) Annotation-based dynamic query framework: Dynamically generate SQL statements by combining custom annotations (such as @Query) with AOP. When embedding SQL strings in the annotation, runtime exceptions are easily caused by spelling mistakes or type mismatches. This solution uses a static binding method for annotation parameters and is difficult to implement dynamic condition combination (such as dynamically adding WHERE clauses according to user input), lacking flexibility. The business logic of this solution is scattered in the annotation and the code, and when the requirements change, multiple annotations and codes need to be modified synchronously, violating the single responsibility principle and making it difficult to maintain the business logic.

[0006] (4) Other solutions in the prior art: Directly concatenating strings to generate SQL statements (such as String sql = "SELECT... WHERE name = '" + param + "'") can easily lead to SQL injection attacks. When frequently parsing query conditions (such as in high-concurrency scenarios), the unoptimized dynamic condition generation logic may cause CPU and memory resource bottlenecks. Existing solutions are mostly designed for specific scenarios (such as REST API parameter parsing) and are difficult to seamlessly migrate to heterogeneous systems such as message queues and batch processing. Summary of the Invention

[0007] In view of this, an object of the present invention is to provide a data query method, device, and electronic device based on dynamic rule combination to alleviate the above problems existing in the related art.

[0008] In a first aspect, an embodiment of the present invention provides a data query method based on dynamic rule combination, including: obtaining query parameter information input by a user; wherein, the query parameter information includes at least one query parameter group with a preset data structure, and each query parameter group contains corresponding fields, operators, and values; parsing out the fields, operators, and values in each query parameter group, and matching a corresponding target processing rule for each query parameter group from a preset processing rule set based on the parsed fields, operators, and values; processing each query parameter group according to the corresponding target processing rule for the query parameter group, and generating an SQL query statement based on a preset logical operator set and the processing result of each query parameter group; executing the SQL query statement to query the database to obtain a database query result.

[0009] In a second aspect, an embodiment of the present invention further provides a data query device based on dynamic rule combination, including: an obtaining module, configured to obtain query parameter information input by a user; wherein, the query parameter information includes at least one query parameter group with a preset data structure, and each query parameter group contains corresponding fields, operators, and values; a parsing and matching module, configured to parse out the fields, operators, and values in each query parameter group, and match a corresponding target processing rule for each query parameter group from a preset processing rule set based on the parsed fields, operators, and values; a generating module, configured to process each query parameter group according to the corresponding target processing rule for the query parameter group, and generate an SQL query statement based on a preset logical operator set and the processing result of each query parameter group; an executing module, configured to execute the SQL query statement to query the database to obtain a database query result.

[0010] In a third aspect, an embodiment of the present invention further provides an electronic device, including a processor and a memory. The memory stores computer-executable instructions that can be executed by the processor. The processor executes the computer-executable instructions to implement the data query method based on dynamic rule combination described in the first aspect above.

[0011] A data query method, apparatus, and electronic device based on dynamic rule combination provided by an embodiment of the present invention first obtain query parameter information input by a user. The query parameter information includes at least one query parameter group having a preset data structure. Each query parameter group contains corresponding fields, operators, and values. Then, the fields, operators, and values in each query parameter group are parsed, and a corresponding target processing rule is matched for each query parameter group from a preset set of processing rules based on the parsed fields, operators, and values. After that, each query parameter group is processed according to its corresponding target processing rule, and an SQL query statement is generated based on a preset set of logical operators and the processing results of each query parameter group. Finally, the SQL query statement is executed to query the database to obtain a database query result. By adopting the above technology, corresponding processing rules can be automatically matched for the query parameter information input by the user for processing, and then the query parameter information can be converted into an SQL query statement. It is not necessary to manually write an SQL query statement or conditional judgment code to implement a multi-condition complex query, which can reduce the operation complexity of the multi-condition complex query and improve the universality of the multi-condition complex query in diverse query scenarios.

[0012] Other features and advantages of the present invention will be described in the following specification, and, in part, will be obvious from the specification, or will be understood by implementing the present invention. The objectives and other advantages of the present invention are realized and obtained by the structures specifically pointed out in the specification, claims, and drawings.

[0013] To make the above objectives, features, and advantages of the present invention more obvious and understandable, the following specifically enumerates preferred embodiments and, in conjunction with the accompanying drawings, makes a detailed description as follows. BRIEF DESCRIPTION OF THE DRAWINGS

[0014] In order to more clearly illustrate the specific embodiments of the present invention or the technical solutions in the prior art, the following will briefly introduce the drawings required for the description of the specific embodiments or the prior art. Obviously, the following drawings are some embodiments of the present invention. For those of ordinary skill in the art, other drawings can be obtained based on these drawings without creative efforts.

[0015] Figure 1 It is a flowchart of a data query method based on dynamic rule combination in an embodiment of the present invention;

[0016] Figure 2 This is a flow example diagram of data query based on dynamic rule combination in an embodiment of the present invention;

[0017] Figure 3 This is a node transfer diagram of data query based on dynamic rule combination in an embodiment of the present invention;

[0018] Figure 4 This is a schematic structural diagram of a data query device based on dynamic rule combination in an embodiment of the present invention;

[0019] Figure 5 This is a schematic structural diagram of an electronic device in an embodiment of the present invention. Detailed implementation manners

[0020] To make the objectives, technical solutions and advantages of the embodiments of the present invention clearer, the technical solutions of the present invention will be clearly and completely described below in conjunction with the embodiments. Apparently, the described embodiments are some but not all of the embodiments of the present invention. All other embodiments obtained by those of ordinary skill in the art based on the embodiments of the present invention without creative efforts shall fall within the protection scope of the present invention.

[0021] For the convenience of understanding this embodiment, first, a data query method based on dynamic rule combination disclosed in the embodiments of the present invention will be introduced in detail. Refer to Figure 1 As shown, the data query method based on dynamic rule combination may include the following steps:

[0022] Step S102, obtain the query parameter information input by the user.

[0023] Among them, the query parameter information may include at least one query parameter group with a preset data structure, and each query parameter group may contain corresponding fields, operators and values respectively.

[0024] For example, the query parameter group can be represented in a unified parameter format of "field name_operator=value". The field name can refer to the attribute name of the entity class, such as name, price, category, etc. The operator can be like (fuzzy matching), between (range), in (inclusion), etc. The value can refer to the corresponding query value, which can be a single value or multiple values. Multiple values need to be separated by commas.

[0025] For the case where the query parameter information includes multiple query parameter groups, the multiple query parameter groups can be connected by a connection symbol (such as &, etc.) to make the query parameter information conform to the HTTP request parameter standard format.

[0026] Step S104: Parse the fields, operators, and values in each query parameter group, and match the corresponding target processing rule for each query parameter group from the preset processing rule set based on the parsed fields, operators, and values.

[0027] Step S106: Process each query parameter group according to the corresponding target processing rule, and generate an SQL query statement based on the preset logical operator set and the processing result of each query parameter group.

[0028] Step S108: Execute the SQL query statement to query the database and obtain the database query result.

[0029] A data query method based on dynamic rule combination provided by an embodiment of the present invention first obtains query parameter information input by a user. The query parameter information includes at least one query parameter group with a preset data structure. Each query parameter group contains corresponding fields, operators, and values. Then, parse the fields, operators, and values in each query parameter group, and match the corresponding target processing rule for each query parameter group from the preset processing rule set based on the parsed fields, operators, and values. After that, process each query parameter group according to the corresponding target processing rule, and generate an SQL query statement based on the preset logical operator set and the processing result of each query parameter group. Finally, execute the SQL query statement to query the database and obtain the database query result. By adopting the above technology, the corresponding processing rule can be automatically matched for the query parameter information input by the user for processing, and then the query parameter information can be converted into an SQL query statement. It is not necessary to manually write an SQL query statement or conditional judgment code to implement a multi-condition complex query, which can reduce the operation complexity of the multi-condition complex query and improve the universality of the multi-condition complex query in diverse query scenarios.

[0030] As a possible implementation manner, parsing the fields, operators, and values in each query parameter group in step S104 above may include: using a regular expression to match the fields, operators, and values in each parameter group, and extracting the matched fields, operators, and values in the same parameter group in an associated manner.

[0031] For example, a parameter parser can use the regular expression (\w+)_(\w+)=(\d+)$ to match the fields, operators, and values in the query parameter group price_gt=1000, and obtain the field name "price" (commodity price), operator "gt" (Greater Than, greater than), and value "1000" (1000 yuan), so as to split "price_gt=1000" into field="price", operator="bt", and value="1000".

[0032] As a possible implementation, each processing rule in the above preset processing rule set may respectively correspond to a corresponding operator; based on this, in step S104 above, matching a corresponding target processing rule from the preset processing rule set based on the parsed fields, operators, and values for each query parameter group may include: for each query parameter group, using the processing rule in the preset processing rule set that corresponds to the operator included in this query parameter group as the target processing rule corresponding to this query parameter group.

[0033] Exemplarily, each predefined operator (such as gt or like or between) has a corresponding processing rule; when the operator "gt" in the query parameter group price_gt = 1000 is known, then generate the processing logic of price > 1000 (i.e., the target processing rule) for matching this query parameter group. By using this method, since different operators have fixed processing logics, the standardized processing logics corresponding to the operators can be matched, thus avoiding the situation of chaotic processing logics during the process of processing query parameter groups.

[0034] As a possible implementation, the above processing result may include the query conditions corresponding to the respective query parameter groups, and the query parameter groups and the query conditions are in one-to-one correspondence; based on this, in step S106 above, processing each query parameter group according to the target processing rule corresponding to it may include: for each query parameter group, converting the fields, operators, and values in this query parameter group into corresponding query conditions according to the target processing rule corresponding to it.

[0035] Exemplarily, each processing rule in the above preset processing rule set may respectively correspond to a corresponding processor; for each query parameter group, the target processor corresponding to this query parameter group may be determined based on the operator included in this query parameter group, and the fields, operators, and values in this query parameter group may be converted into corresponding query conditions by the target processor corresponding to this query parameter group according to the target processing rule corresponding to it.

[0036] Continuing with the previous example, after the parameter parser parses out the field name "price" (commodity price), operator "gt" (Greater Than, greater than), and value "1000" (1000 yuan) from the query parameter group price_gt = 1000, the processing logic of "price > 1000" can be matched based on the operator "gt", and then the rule processor with this processing logic can be matched, so as to convert the query parameter group price_gt = 1000 into the query condition price > 1000 through this rule processor.

[0037] As a possible implementation, each processing rule in the above set of preset processing rules may correspond to a corresponding priority; based on this, for each query parameter group, the operation mode of the target processor corresponding to the query parameter group determined based on the operators included in the query parameter group may be as follows:

[0038] Step a1, if the operator included in the query parameter group is a non-empty operator, then use the processor corresponding to the operator included in the query parameter group as the target processor corresponding to the query parameter group.

[0039] Continuing with the previous example, the operator "gt" included in the query parameter group price_gt = 1000 is a non-empty operator, so the rule processor with the processing logic that matches the operator "gt" to generate price > 1000 can be used as the target processor corresponding to the query parameter group price_gt = 1000.

[0040] Step a2, if the operator included in the query parameter group is an empty operator, then use the processor corresponding to the processing rule with the highest priority as the target processor corresponding to the query parameter group.

[0041] For example, the operator included in the query parameter group status = 1 is actually an empty operator, and the processing logic with the highest priority corresponds to the operator "eq" (equal), so the rule processor with the processing logic of the highest priority can be used as the target processor corresponding to the query parameter group status = 1. At this time, it can be considered that the processing logic with the highest priority is used by default to process the query parameter group with an empty operator.

[0042] As a possible implementation, the above query conditions may include the fields and values in the corresponding query parameter group; based on this, the generation of the SQL query statement based on the preset logical operator set and the processing results of each query parameter group in step S106 above may include:

[0043] Step A1, match the corresponding target logical operator for each query parameter group from the preset logical operator set, and splice the obtained query conditions into a logical expression based on the target logical symbol corresponding to each query parameter group.

[0044] For the case where there are multiple query parameter groups, after converting each obtained query parameter group into a corresponding query condition, a corresponding target logical operator (such as AND, OR, or NOT) can be configured for each query parameter group, and the obtained multiple query conditions can be concatenated into a logical expression using the configured target logical operator. Continuing with the previous example, after processing the query parameter groups name_like=flagship, price_bt=2000,5000, and status=1 into the query conditions name LIKE '%flagship%', price BETWEEN 2000 AND 5000, and status=1 respectively, the logical operator "AND" can be configured for these query conditions, and the logical operator "AND" is used to connect these query conditions to generate the logical expression "(name LIKE '%flagship%') AND (price BETWEEN 2000 AND 5000) AND (status=1)".

[0045] Step A2, generate an SQL query statement based on the logical expression and the pre-compiled SQL query statement structure.

[0046] Exemplarily, the logical expression may include the query conditions corresponding to each query parameter group and the logical relation symbols used to connect the query conditions; the above step A2 (i.e., generating an SQL query statement based on the logical expression and the pre-compiled SQL query statement structure) may include the following steps A21 to A23:

[0047] Step A21, construct an abstract syntax tree corresponding to the logical expression based on the logical relation symbols, so as to represent the logical expression in a tree structure through the abstract syntax tree.

[0048] For example, after obtaining the logical expression (name LIKE '%mobile phone%' AND brand='Apple') OR (price>5000 AND status=1), the logical expression can be represented as a tree structure through the JPA Criteria API. This tree structure uses "OR" as the root group (i.e., the root node), and the root group includes two sub-groups "AND". One of the sub-groups includes two leaf nodes (i.e., name LIKE '%mobile phone%' and brand='Apple'), and the other sub-group includes two leaf nodes (i.e., price>5000 and status=1).

[0049] Step A22, recursively traverse the nodes of the abstract syntax tree in the order from the leaf nodes to the root node, so as to generate a pre-compiled SQL query statement with placeholders using the traversal result and the pre-compiled SQL query statement structure; wherein, the placeholders in the pre-compiled SQL query statement correspond one by one to the values in the obtained query conditions.

[0050] Continuing with the previous example, the nodes of the tree structure can be recursively traversed in the order from leaf nodes to the root node through the JPA Criteria API, and a pre-compiled SQL query statement with a pre-compiled SQL query statement structure and placeholders can be created through the JPA Criteria API.

[0051] Step A23, replace the placeholders in the pre-compiled SQL query statement one by one with the values in the obtained query conditions to obtain an SQL query statement.

[0052] Continuing with the previous example, the nodes of the tree structure can be recursively traversed in the order from leaf nodes to the root node through the JPA Criteria API to replace the placeholders in the pre-compiled SQL query statement one by one with the values in the corresponding query conditions, so as to obtain the required SQL query statement.

[0053] As a possible implementation, before parsing the fields, operators, and values in each query parameter group, the above data query method based on dynamic rule combination may further include:

[0054] Step b1, determine whether the fields in each query parameter group are in the preset field whitelist. If there are fields in the query parameter group that are not in the preset field whitelist, determine the fields that are not in the preset field whitelist in the corresponding query parameter group as illegal fields, and generate the first alarm information corresponding to each illegal field; where the preset field whitelist includes predefined legal fields and / or fields in the database.

[0055] Step b2, determine whether there is a corresponding processing rule for each operator in the query parameter group in the preset processing rule set. If there is an operator in the query parameter group that does not have a corresponding processing rule in the preset processing rule set, determine the operator that does not have a corresponding processing rule in the preset processing rule set in the corresponding query parameter group as an illegal operator, and generate the second alarm information corresponding to each illegal operator.

[0056] For the sake of easy understanding, the implementation manner of the above data query method based on dynamic rule combination is described exemplarily as follows with a specific application as an example.

[0057] The technical terms involved in the present invention are as follows:

[0058] PA: The official Java persistence specification, which realizes the structured interaction between Java objects and relational database tables through object-relational mapping (ORM) technology. The core functions include entity management, query language (JPQL), transaction control, etc.

[0059] Specification (can be abbreviated as Spec): A programming model in Spring Data JPA for dynamically constructing query conditions. By implementing the `toPredicate` method and combining it with the JPA Criteria API, it dynamically generates the conditional combination logic (such as AND / OR nested structures) of the WHERE clause.

[0060] Predicate: An object in the JPA Criteria API representing specific SQL conditions (such as =, >, LIKE). It is created through the CriteriaBuilder and supports combination and nesting (such as `and()`, `or()`) to achieve complex queries.

[0061] H2 Database: An open-source lightweight in-memory database that supports standard SQL syntax and the JDBC protocol. It is commonly used in unit tests, rapid prototype verification, or demonstration scenarios without relying on external database services (such as MySQL, Oracle).

[0062] Condition Parsing: Parses the parameters passed from the front end (such as `{"name_like":"mobile","price_gte":"1000"}`) into a structured condition object, including the field name, operator (such as `like`, `gte`), and matching value.

[0063] CriteriaBuilder (Condition Builder): The core utility class of the JPA Criteria API for dynamically constructing query conditions, providing methods such as `equal()`, `like()`, `greaterThanOrEqualTo()` to generate Predicate objects.

[0064] Predicate Composition: The process of combining multiple Predicate conditions into a composite query condition through logical operators (AND / OR). For example: `cb.and(predicate1,cb.or(predicate2,predicate3))`.

[0065] To implement the above data query method based on dynamic rule combination, a three-layer decoupled design (parameter parsing layer - rule mapping layer - condition generation layer) hierarchical architecture can be adopted. This hierarchical architecture supports users to define arbitrary combinations of query conditions through a unified parameter format (such as field name_operator=value), solving the problems of rigid condition combination and high code duplication rate in traditional hard-coded query methods.

[0066] Parameter parsing layer: Regular expressions are used to separate fields, operators, and values from query parameter information in a unified parameter format (e.g., parsing "price_gt=1000" into field name "price", operator "gt", and value "1000"), and custom rule extension is supported (no need to modify the core logic when adding new operators). The parameter parsing layer can classify and organize the diverse filtering conditions of users, and clarify the meaning of each filtering condition.

[0067] Rule mapping layer: A mapping relationship between operators and processing rules is established in advance. The dynamic rule engine can use this mapping relationship to match corresponding processing rules for each operator (such as gt / like / between) parsed by the parameter parsing layer, so that the fields, operators, and values parsed by the parameter parsing layer can be converted into query conditions through the corresponding rule processors according to the matched processing rules. The rule mapping layer can decouple the operator and Predicate generation in the strategy mode, realize the dynamic registration of operator logic, and greatly improve the scalability.

[0068] Condition generation layer: The single query conditions (such as "price > 1000", "name contains mobile phone") obtained from the rule mapping layer are connected by connectors (such as AND or OR) through the JPA Criteria API to finally generate a complete SQL query statement. When a new query condition needs to be added temporarily, the new query condition can be automatically spliced without the participation of programmers. The condition generation layer can automatically generate a multi-condition AND / OR logic tree based on the nested combination ability of the JPA Criteria API, and support the accurate expression of complex query semantics.

[0069] See Figure 2 As shown, the process of performing data query based on dynamic rule combination using the above data query method based on dynamic rule combination mainly includes:

[0070] Step 1, the user submits a filtering form.

[0071] The filtering form includes query parameter information, and the query parameter information includes multiple groups of query parameters. Each group of query parameters includes fields, operators, and values in a unified parameter format (such as "field name_operator=value").

[0072] Step 2, parse the filtering form into field name + operator + value.

[0073] Each group of query parameters in the filtering form is parsed into the corresponding three parts (i.e., field name, operator, and value) by the parameter parser.

[0074] Step 3, match the rule strategy.

[0075] Match the parsed operator with the corresponding rule handler (i.e., RuleHandler) to process the parsed field name, operator, and value according to the corresponding rule strategy, so as to obtain the query conditions corresponding to each group of query parameters.

[0076] Step 4, concatenate into a legal SQL.

[0077] Use the SQL generator through the JPA Criteria API to connect the obtained query conditions with a connector (AND or OR) to generate a logical expression, and then convert the logical expression into SQL.

[0078] Step 5, execute the SQL to obtain the database query result.

[0079] The executable SQL can perform H2 database query operations, and return the database query result to the user in the form of serialized information. The database queried by executing the SQL can use the H2 database.

[0080] Taking product search (query parameters: name_like=flagship&price_bt=2000,5000&status=1) as an example, Figure 3 It shows the key node transfer process of query parameters from HTTP request to SQL execution. See Figure 3 As shown, the node transfer process of data query based on dynamic rule combination through the controller is as follows:

[0081] Step 1, receive the HTTP request.

[0082] The @RequestParam or @ModelAttribute annotation of Spring MVC automatically captures the query parameters input by the user on the client side into the Map<String, String> structure.

[0083] To standardize the query parameters, UTF-8 is used by default to decode the query parameters to avoid Chinese character garbling.

[0084] For example, the data structure of the query parameters can be expressed as:

[0085]

[0086] The client includes the query parameters of this data structure in the HTTP request and sends them to the controller, and the controller receives this HTTP request.

[0087] Step 2, parameter parsing.

[0088] The controller parses the query parameters included in the HTTP request through a parameter parser to match the field, operator, and value in the query parameters using the regular expression (\w+)_(\w+)=(\d+)$: splits name_like=flagship into field=name, operator=like, and value=flagship, splits price_bt=2000,5000 into field=price, operator=bt (abbreviation of custom Between), and value=2000,5000, and splits status=1 into field=status, operator=eq (i.e., equal, default operator here), and value=1.

[0089] For the obtained values, the parameter parser retains the original string without any processing and delays the process of converting the query parameters into query conditions until the subsequent RuleHandler is matched. By adopting this deferred processing operation method, it is possible to avoid the failure of immediately attempting to convert the query parameters into the target type after receiving them.

[0090] After the parameter parser parses the field, operator, and value in the query parameters, it generates a condition list (i.e., List <condition>) Each Condition included in the condition list is the corresponding field, operator, and value, and then the condition list is sent to the controller.

[0091] List <condition>It can be expressed as:

[0092]

[0093] The controller can also perform exception interception to trigger an alarm when an illegal field name and / or illegal operator appears. For example, if it is found that "drop" in drop_table is an illegal field name, the controller triggers an exception alarm to send an alarm message to the client. Another example, if it is found that an illegal operator in status_nn cannot be processed by any rule processor (rule processors need to be defined separately for different operators), the controller triggers an exception alarm to send an alarm message to the client.

[0094] Step 3: Rule processor matching.

[0095] A rule processor repository can be established to automatically inject a list of classes that implement a specific interface of all RuleHandlers through Spring. Specifically, the Spring container will automatically scan and identify all RuleHandler implementation classes with annotations such as @Component and @Service, and inject all the found implementation class instances into the rule processor list (i.e., List <rulehandler>) in.

[0096] Traversable List <condition>Match the corresponding RuleHandler for each operator included in the Condition in this condition list.

[0097] A priority control mechanism can also be adopted for the List <condition>Match a RuleHandler for each Condition in the condition list. Specifically, the matching priority of the RuleHandler can be set through the @Order annotation (the smaller the value, the higher the priority), so that when the operator included in the Condition is a null operator, the RuleHandler with the highest priority is matched for the Condition. For example, when the controller receives a query parameter status = 1 (status has no _eq suffix) without explicitly specifying a non-null operator, since the EqualsHandler has the highest priority (@Order(1)) and the EqualsHandler supports the null operator null, the controller will select the EqualsHandler to process the query parameter instead of other RuleHandlers that may be matched.

[0098] Specifically, for the query parameters name_like = flagship & price_bt = 2000,5000 & status = 1, the LikeHandler can be selected to process name_like = flagship, and the code can be expressed as:

[0099] Path <string>namePath = root.get("name");

[0100] Predicate p = cb.like(namePath, "% flagship %");

[0101] If the BetweenHandler is selected to handle price_bt = 2000, 5000, the code can be expressed as:

[0102] String[] parts = condition.getValue().split(",");

[0103] Long low = Long.parseLong(parts[0]);

[0104] Long high = Long.parseLong(parts[1]);

[0105] Predicate p = cb.between(root.get("price"), low, high);

[0106] If the EqualsHandler is selected to handle status = 1, the code can be expressed as:

[0107] Integer statusValue = Integer.parseInt(condition.getValue());

[0108] Predicate p = cb.equal(root.get("status"), statusValue);

[0109] After each selected rule handler processes its corresponding field, operator, and value to obtain the corresponding Predicate, a Predicate list (i.e., List <predicate>) The Predicate list contains all the Predicates obtained after being processed by the rule processor, and then this Predicate list is sent to the controller.

[0110] Step 4, Predicate combination (dynamically assemble the WHERE clause).

[0111] The controller combines the Predicates (i.e., all the obtained Predicates) into a Spec through the SQL generator. Specifically, the operation of combining the Predicates into a Spec can be performed through the CriteriaBuilder of the JPA Criteria API.

[0112] When combining the Predicates into a Spec, it can be default that all Predicates are connected by AND to generate a logical expression. Specifically, the logical expression generated from name_like = flagship & price_bt = 2000,5000 & status = 1 is (name LIKE '%flagship%') AND (price BETWEEN 2000 AND 5000) AND (status = 1).

[0113] Combining the Predicates into a Spec can also support extended logical operators (OR / NOT). It is necessary to add a logicType field in the Condition and splice the Predicate by passing different values to the logicType field. The code can be expressed as:

[0114]

[0115] For example, name_like = mobile & brand = apple & price_gt = 5000 & price_gt_logic = OR specifies price_gt_logic = OR. Three conditions can be parsed as name_like = mobile (AND logic, default), brand = apple (AND logic, default), price_gt = 5000 (OR logic, specified by price_gt_logic = OR). Then the logical expression (name LIKE '%mobile%' AND brand = 'apple') OR (price > 5000) is generated, and further the SQL statement WHERE (name LIKE '%mobile%' AND brand = 'apple') OR (price > 5000) is generated.

[0116] The working principle of the CriteriaBuilder mainly includes: creating a CriteriaQuery <t>Define the return type, obtain the field path through root.get("fieldName") (automatically mapped to database columns), and use the method chain of CriteriaBuilder to generate comparison expressions (i.e., conditional expressions or relational expressions).

[0117] Hibernate, which implements the JPA interface, can generate pre-compiled SQL with placeholders. The code can be expressed as:

[0118]

[0119]

[0120] Among them, "?" is a placeholder;

[0121] After that, Hibernate replaces the placeholders in the pre-compiled SQL with corresponding values to obtain the final SQL used to query the database.

[0122] Step Five, execute the database query.

[0123] The controller can execute the SQL obtained in Step Four to query the database. Specifically, the database query can be executed by JPA. The process of JPA executing the database query mainly includes: JpaRepository.findAll(Specification) triggers the query, Hibernate translates Specification into HQL (and then into native SQL), and executes it securely through JDBCPreparedStatement (preventing SQL injection). The database queried by the controller when executing the SQL can use the H2 database.

[0124] The database will return the result obtained after the controller executes the SQL as a list (i.e., List <product>) is sent to the controller in the form of List <product>That is the database query result.

[0125] Step Six, serialization and return of the database query result.

[0126] The controller will process the received List <product>Perform serialization to convert the database query results into JSON results and return the JSON results to the client.

[0127] The above data query method based on dynamic rule combination mainly involves the following two aspects of innovation:

[0128] (1) Fully automatic SQL generation based on parameter description.

[0129] By parsing the query parameters passed from the front end into corresponding Condition objects and automatically selecting the corresponding RuleHandler according to the operator (such as gt, like), and then generating the WHERE statement fragment, developers only need to focus on the parameter structure without manually writing SQL or conditional judgment code. The JPA Criteria query statement is dynamically generated through structured rule parameters, and the generated SQL has the same performance as the manually written one, enabling the complete decoupling of the query logic and business code.

[0130] The query parameters adopt the structure of field name_operator=value; among them, the field name must be the attribute name existing in the entity class, case-sensitive; the operator (i.e., the operation symbol) is a member of the predefined operator set (such as eq, gt, like, etc.); the value provides the corresponding formatted value according to the requirements of the operator and field type. Example: name=iPhone is equivalent to name_eq=iPhone, indicating that name is iPhone; status_ne=0 indicates that status is not equal to 0; price_gt=1000 indicates that price is greater than 1000; price_lt=5000 indicates that price is less than 5000; price_gte=1000 indicates that price is greater than or equal to 1000; price_lte=5000 indicates that price is less than or equal to 5000.

[0131] (2) Security defense system for dynamic parameters.

[0132] Field legality verification: Before parsing the query parameters, verify whether the field in the query parameters (i.e., the field) is a legal field predefined in the field whitelist, and it is also possible to judge whether the field in the query parameters is a legal field in the database data table, so as to ensure that only legal fields will participate in the database query.

[0133] Parameter value pre - encoding: By using the PreparedStatement interface (i.e., the pre - compilation interface), a pre - compiled SQL structure is provided to bind parameter values (specifically, binding placeholders to the values of corresponding query parameters), and special characters are automatically escaped. Parameter value pre - encoding has an impact on the SQL query execution phase, mainly including: ensuring that the database can correctly perform queries with special characters; ensuring that the database can cache query execution plans with the same structure; ensuring that the returned database query results conform to the actual query intent. Through parameter value pre - encoding, since special characters (such as quotation marks and semicolons) entered by users do not damage the SQL structure, SQL injection can be prevented, enhancing security; and SQL queries with the same structure can reuse the database query cache, achieving optimization of database query performance: Since special characters are correctly escaped, query parameters are passed to the database according to the correct data types, thus ensuring the data integrity of the query parameters used in data queries.

[0134] Logical symbol restriction: Only AND / OR / NOT are allowed, and dangerous symbols (such as ;, --, etc.) are filtered.

[0135] Through field legality verification, parameter value pre - encoding, and logical symbol restriction, SQL injection and illegal field access can be completely avoided during the process of automatically generating SQL. Through the pre - compilation mechanism and dangerous symbol filtering, all known injection types (such as Union injection and blind injection) can be covered. Illegal fields are directly rejected to avoid unauthorized queries of sensitive data (such as password and email).

[0136] The beneficial effects of the above - mentioned data query method based on dynamic rule combination can be mainly reflected in the following four aspects:

[0137] 1) The code reuse rate is increased, and the extension cost is reduced.

[0138] Adopting the RuleHandler plug - in design, the processing logic of each query condition can be isolated through the abstract interface + specific implementation class.

[0139] Using Spring to automatically assemble RuleHandler, the @Autowired List can be utilized <rulehandler>Dynamically load all rule processors. Specifically, Spring can automatically discover, instantiate, and inject all classes that implement a specific interface, for List <rulehandler>The automatic assembly process is as follows:

[0140] Component scanning: When Spring starts, it scans all classes under the specified package path.

[0141] Component identification: Identify classes with annotations such as @Component and @Service.

[0142] Type matching: Find all classes that implement the RuleHandler interface.

[0143] Instantiation: Create instances of these classes.

[0144] Collection injection: Collect all instances into a List and inject them.

[0145] With the above data query method based on dynamic rule combination, since the same operators (such as eq, gt) do not need to be implemented repeatedly, the code reuse rate can be improved; and when a new condition type needs to be added, only the corresponding Handler class needs to be added, avoiding invasive modifications and reducing the extension cost.

[0146] 2) Enhance the defense ability against injection attacks and improve security.

[0147] The traditional method of manually splicing SQL has an injection risk and poor security. Through the JPA Criteria API parameterized query method, precompiled SQL can be dynamically generated and parameter values can be bound using placeholders, which can reduce the injection risk and enhance the defense ability against injection attacks.

[0148] When the JPA Criteria API generates SQL, it will create a precompiled statement with placeholders and automatically bind the parameter values. This process can be divided into the following steps:

[0149] Build a Criteria query: Use the Criteria API to build the query structure.

[0150] Generate parameterized JPQL / HQL: JPA converts the Criteria into JPQL / HQL with parameter placeholders.

[0151] Convert to precompiled SQL: Hibernate converts the JPQL into SQL with a placeholder "?".

[0152] Parameter binding: Bind the actual parameter values through the PreparedStatement.

[0153] Verify whether the field is a legal field before parsing the query parameters. Illegal fields (such as password) can be filtered through a field whitelist, and abnormal requests can be recorded through system logs, enabling the interception of illegal fields and thus improving security.

[0154] 3) Support complex logic and improve code readability.

[0155] Use CriteriaBuilder to nest Predicates and construct an abstract syntax tree, and then generate SQL by recursively traversing the nodes of the abstract syntax tree. It can generate AND / OR / NOT combination conditions at infinite levels, improving the support for complex logic. Moreover, the logical structure of the SQL is clear, enhancing code readability and also improving the efficiency of SQL statement generation, thus facilitating the improvement of data query efficiency.

[0156] Based on the above data query method based on dynamic rule combination, an embodiment of the present invention further provides a data query device based on dynamic rule combination. Refer to Figure 4 As shown, the device may include the following modules:

[0157] An acquisition module 402, configured to acquire query parameter information input by a user; wherein, the query parameter information includes at least one query parameter group with a preset data structure, and each query parameter group contains corresponding fields, operators, and values.

[0158] A parsing and matching module 404, configured to parse out the fields, operators, and values in each query parameter group, and match a corresponding target processing rule for each query parameter group from a preset set of processing rules based on the parsed fields, operators, and values.

[0159] A generation module 406, configured to process each query parameter group according to the corresponding target processing rule for the query parameter group, and generate an SQL query statement based on a preset set of logical operators and the processing results of each query parameter group.

[0160] An execution module 408, configured to execute the SQL query statement to query a database and obtain a database query result.

[0161] Adopting the above data query device based on dynamic rule combination can automatically match corresponding processing rules for the query parameter information input by the user for processing, and then convert the query parameter information into an SQL query statement. It can achieve multi-condition complex queries without manually writing SQL query statements or conditional judgment codes, which can reduce the operation complexity of multi-condition complex queries and improve the universality of multi-condition complex queries in diverse query scenarios.

[0162] The above processing result may include the query conditions corresponding to the corresponding query parameter groups; based on this, the above generating module 406 may also be used to: for each query parameter group, convert the fields, operators, and values in the query parameter group into corresponding query conditions according to the target processing rule corresponding thereto.

[0163] The above query conditions may include the fields and values in the corresponding query parameter groups; based on this, the above generating module 406 may also be used to: match a corresponding target logical operator for each query parameter group from the preset set of logical operators, and splice the obtained query conditions into a logical expression based on the target logical operator corresponding to each query parameter group; generate the SQL query statement based on the logical expression and the precompiled SQL query statement structure.

[0164] Each processing rule in the above preset processing rule set may respectively correspond to a corresponding operator and a processor; based on this, the above parsing and matching module 404 may also be used to: for each query parameter group, use the processing rule in the preset processing rule set that corresponds to the operator included in the query parameter group as the target processing rule corresponding to the query parameter group.

[0165] The above generating module 406 may also be used to: for each query parameter group, determine the target processor corresponding to the query parameter group based on the operator included in the query parameter group, and convert the fields, operators, and values in the query parameter group into corresponding query conditions through the target processor corresponding to the query parameter group according to the target processing rule corresponding to the query parameter group.

[0166] Each processing rule in the above preset processing rule set may respectively correspond to a corresponding priority; based on this, the above generating module 406 may also be used to perform the following operations for each query parameter group: if the operator included in the query parameter group is a non-empty operator, use the processor corresponding to the operator included in the query parameter group as the target processor corresponding to the query parameter group; if the operator included in the query parameter group is an empty operator, use the processor corresponding to the processing rule with the highest priority as the target processor corresponding to the query parameter group.

[0167] The above parsing and matching module 404 may also be used to: use regular expressions to match the fields, operators, and values in each parameter group, and extract the matched fields, operators, and values in the same parameter group in an associated manner.

[0168] The above logical expression may include query conditions corresponding to each query parameter group and logical relation operators for connecting the query conditions; based on this, the above generating module 406 may further be configured to: construct an abstract syntax tree corresponding to the logical expression based on the logical relation operators, so as to represent the logical expression in a tree structure through the abstract syntax tree; recursively traverse the nodes of the abstract syntax tree in the order from the leaf nodes to the root node, so as to generate a pre-compiled SQL query statement with placeholders by using the traversal result and the pre-compiled SQL query statement structure; wherein, the placeholders in the pre-compiled SQL query statement correspond one by one to the values in the obtained query conditions; and replace the placeholders in the pre-compiled SQL query statement with the values in the obtained query conditions one by one to obtain the SQL query statement.

[0169] The above parsing and matching module 404 may further be configured to perform the following operations before parsing out the fields, operators, and values in each query parameter group: determine whether the fields in each query parameter group are in a preset field whitelist, and if there are fields in a query parameter group that are not in the preset field whitelist, determine the fields that are not in the preset field whitelist in the corresponding query parameter group as illegal fields, and generate first alarm information corresponding to each illegal field; wherein the preset field whitelist includes predefined legal fields and / or fields in the database; determine whether there are corresponding processing rules for the operators in each query parameter group in a preset processing rule set, and if there are operators in a query parameter group that do not have corresponding processing rules in the preset processing rule set, determine the operators that do not have corresponding processing rules in the corresponding query parameter group as illegal operators, and generate second alarm information corresponding to each illegal operator.

[0170] The data query device based on dynamic rule combination provided by the embodiments of the present invention has the same implementation principle and technical effects as those of the foregoing embodiments of the data query method based on dynamic rule combination. For the sake of brief description, for the parts not mentioned in the embodiments of the data query device based on dynamic rule combination, reference may be made to the corresponding content in the foregoing embodiments of the data query method based on dynamic rule combination.

[0171] Embodiments of the present invention further provide an electronic device, as Figure 5 shown, which is a schematic structural diagram of the electronic device. Wherein, the electronic device includes a processor 51 and a memory 50. The memory 50 stores computer-executable instructions that can be executed by the processor 51, and the processor 51 executes the computer-executable instructions to implement the above data query method based on dynamic rule combination.

[0172] In Figure 5 In the illustrated embodiment, the electronic device further includes a bus 52 and a communication interface 53, wherein the processor 51, the communication interface 53, and the memory 50 are connected through the bus 52.

[0173] Among them, the memory 50 may include a high-speed random access memory (RAM), and may also include a non-volatile memory, such as at least one disk memory. The communication connection between the system network element and at least one other network element is realized through at least one communication interface 53 (which can be wired or wireless), and the Internet, wide area network, local area network, metropolitan area network, etc. can be used. The bus 52 may be an ISA (Industry Standard Architecture) bus, a PCI (Peripheral Component Interconnect) bus, an EISA (Extended Industry Standard Architecture) bus, etc. The bus 52 can be divided into an address bus, a data bus, a control bus, etc. For the sake of simplicity of representation, Figure 5 only a single bidirectional arrow is used in the figure, but it does not mean that there is only one bus or one type of bus.

[0174] The processor 51 may be an integrated circuit chip with the ability to process signals. In the implementation process, each step of the above method can be completed by the integrated logic circuit in the hardware of the processor 51 or instructions in the form of software. The above-mentioned processor 51 may be a general-purpose processor, including a central processing unit (CPU for short), a network processor (NP for short), etc.; it may also be a digital signal processor (DSP for short), an application specific integrated circuit (ASIC for short), a field-programmable gate array (FPGA for short), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components. The general-purpose processor may be a microprocessor or the processor may also be any conventional processor, etc. The steps of the method disclosed in the embodiments of the present invention can be directly implemented by a hardware decoding processor, or implemented by a combination of hardware and software modules in the decoding processor. The software module may be located in a mature storage medium in the art such as a random access memory, a flash memory, a read-only memory, a programmable read-only memory, or an electrically erasable programmable memory, a register, etc. This storage medium is located in the memory, and the processor 51 reads the information in the memory and combines its hardware to complete the steps of the data query method based on dynamic rule combination in the foregoing embodiments.

[0175] Unless otherwise specifically stated, the relative steps, numerical expressions, and numerical values of the components and steps set forth in these embodiments do not limit the scope of the present invention.

[0176] If the above functions are implemented in the form of software function units and sold or used as independent products, they can be stored in a non-volatile computer-readable storage medium executable by a processor. Based on such an understanding, the technical solution of the present invention, in essence, or the part that contributes to the prior art, or a part of this 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 for causing a computer device (which may be a personal computer, a server, or a network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of the present invention. The foregoing storage medium includes: various media such as a USB flash drive, a mobile hard disk, a read-only memory (ROM), a random access memory (RAM), a magnetic disk, or an optical disc that can store program codes.

[0177] In the description of the present invention, it should be noted that the orientation or positional relationship indicated by the terms "center", "upper", "lower", "left", "right", "vertical", "horizontal", "inner", "outer", etc. is based on the orientation or positional relationship shown in the drawings. It is only for the convenience of describing the present invention and simplifying the description, rather than indicating or implying that the device or element referred to must have a specific orientation, be constructed and operated in a specific orientation. Therefore, it should not be construed as a limitation to the present invention. In addition, the terms "first", "second", "third" are only used for descriptive purposes and cannot be construed as indicating or implying relative importance.

[0178] Finally, it should be noted that the above-described embodiments are only specific embodiments of the present invention, which are used to illustrate the technical solutions of the present invention, rather than limiting them. The protection scope of the present invention is not limited thereto. Although the present invention has been described in detail with reference to the foregoing embodiments, those of ordinary skill in the art should understand that any person skilled in the art within the technical scope disclosed by the present invention can still modify the technical solutions described in the foregoing embodiments or easily conceive of changes, or perform equivalent replacements for some of the technical features; and these modifications, changes or replacements do not make the essence of the corresponding technical solutions deviate from the spirit and scope of the technical solutions of the embodiments of the present invention, and should all be covered by the protection scope of the present invention. Therefore, the protection scope of the present invention should be determined by the protection scope of the claims.< / rulehandler> < / rulehandler> < / product> < / product> < / product> < / t> < / predicate> < / string> < / condition> < / condition> < / rulehandler> < / condition> < / condition>

Claims

1. A data query method based on dynamic rule combination, characterized in that, including: obtaining query parameter information input by a user; wherein, the query parameter information includes at least one query parameter group with a preset data structure, and each query parameter group contains corresponding fields, operators, and values; parsing out the fields, operators, and values in each query parameter group, and matching a corresponding target processing rule for each query parameter group from a preset set of processing rules based on the parsed fields, operators, and values; processing each query parameter group according to the target processing rule corresponding thereto, and generating an SQL query statement based on a preset set of logical operators and the processing result of each query parameter group; executing the SQL query statement to query a database to obtain a database query result.

2. The data query method based on dynamic rule combination according to claim 1, wherein The processing result includes a query condition corresponding to the corresponding query parameter group; processing each query parameter group according to the target processing rule corresponding thereto includes: for each query parameter group, converting the fields, operators, and values in the query parameter group into corresponding query conditions according to the target processing rule corresponding thereto.

3. The data query method based on dynamic rule combination according to claim 2, wherein The query condition includes the fields and values in the corresponding query parameter group; generating an SQL query statement based on a preset set of logical operators and the processing result of each query parameter group includes: matching a corresponding target logical operator for each query parameter group from the preset set of logical operators, and concatenating the obtained query conditions into a logical expression based on the target logical operator corresponding to each query parameter group; generating the SQL query statement based on the logical expression and a precompiled SQL query statement structure.

4. The data query method based on dynamic rule combination according to claim 2, wherein Each processing rule in the preset set of processing rules corresponds to a corresponding operator and a processor; matching a corresponding target processing rule for each query parameter group from the preset set of processing rules based on the parsed fields, operators, and values includes: for each query parameter group, using the processing rule in the preset set of processing rules that corresponds to the operator included in the query parameter group as the target processing rule corresponding to the query parameter group; processing each query parameter group according to the target processing rule corresponding thereto includes: for each query parameter group, determining the target processor corresponding to the query parameter group based on the operator included in the query parameter group, and converting the fields, operators, and values in the query parameter group into corresponding query conditions according to the target processing rule corresponding to the query parameter group through the target processor corresponding to the query parameter group.

5. The data query method based on dynamic rule combination according to claim 4, wherein Each processing rule in the preset set of processing rules corresponds to a corresponding priority; determining the target processor corresponding to the query parameter group based on the operator included in the query parameter group includes: if the operator included in the query parameter group is a non-empty operator, using the processor corresponding to the operator included in the query parameter group as the target processor corresponding to the query parameter group; if the operator included in the query parameter group is an empty operator, using the processor corresponding to the processing rule with the highest priority as the target processor corresponding to the query parameter group.

6. The data query method based on dynamic rule combination according to claim 1, wherein parsing out the fields, operators, and values in each query parameter group includes: Use regular expressions to match the fields, operators, and values in each parameter group, and extract the matched fields, operators, and values in the same parameter group in an associated manner.

7. The data query method based on dynamic rule combination according to claim 3, characterized in that The logical expression includes query conditions corresponding to each query parameter group and logical relation operators for connecting the query conditions; Generate the SQL query statement based on the logical expression and the pre-compiled SQL query statement structure, including: Construct an abstract syntax tree corresponding to the logical expression based on the logical relation operators, so as to represent the logical expression in a tree structure through the abstract syntax tree; Recursively traverse the nodes of the abstract syntax tree in the order from leaf nodes to the root node, so as to generate a pre-compiled SQL query statement with placeholders by using the traversal result and the pre-compiled SQL query statement structure; wherein, the placeholders in the pre-compiled SQL query statement correspond one-to-one to the values in the obtained query conditions; Replace the placeholders in the pre-compiled SQL query statement with the values in the obtained query conditions one by one to obtain the SQL query statement.

8. The data query method based on dynamic rule combination according to claim 4, wherein Before parsing out the fields, operators, and values in each query parameter group, it further includes: Judge whether the fields in each query parameter group are in the preset field white list. If there are fields in the query parameter group that are not in the preset field white list, determine the fields that are not in the preset field white list in the corresponding query parameter group as illegal fields, and generate a first alarm message corresponding to each illegal field; wherein, the preset field white list includes pre-defined legal fields and / or the fields in the database; Judge whether there is a corresponding processing rule in the preset processing rule set for each operator in each query parameter group. If there is an operator in the query parameter group that has no corresponding processing rule in the preset processing rule set, determine the operator that has no corresponding processing rule in the preset processing rule set in the corresponding query parameter group as an illegal operator, and generate a second alarm message corresponding to each illegal operator.

9. A data query device based on dynamic rule combination, characterized in that, It includes: An acquisition module for acquiring query parameter information input by a user; wherein, the query parameter information includes at least one query parameter group with a preset data structure, and each query parameter group contains corresponding fields, operators, and values; A parsing and matching module for parsing out the fields, operators, and values in each query parameter group, and matching a corresponding target processing rule for each query parameter group from the preset processing rule set based on the parsed fields, operators, and values; A generation module for processing each query parameter group according to the corresponding target processing rule for each query parameter group, and generating an SQL query statement based on the preset logical operator set and the processing results of each query parameter group; An execution module for executing the SQL query statement to query the database to obtain a database query result.

10. An electronic device, characterized in that, It includes a processor and a memory. The memory stores computer-executable instructions that can be executed by the processor, and the processor executes the computer-executable instructions to implement the data query method based on dynamic rule combination according to any one of claims 1 to 8.

Images