A database query method, device, electronic device and medium
By defining configuration arrays and using whereComp and whereJoin functions to generate SQL query conditions, the problem of insufficient SQL development efficiency and performance in the existing technology is solved, and efficient construction of complex queries and flexible reuse of SQL fragments is realized.
Patent Information
- Application Number
- CN202410985364.3
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2024-07-23
- Publication Date
- 2024-11-08
- Estimated Expiration
- 2044-07-23
AI Technical Summary
The existing SQL development methods have shortcomings in complex queries and SQL fragment reuse, especially in handling large-scale and complex data queries and multi-person collaborative development scenarios, it is difficult to improve SQL development efficiency and query performance.
By defining the configuration array of query conditions and the associative query configuration array, use the whereComp function and whereJoin function to generate SQL query conditions, dynamically generate the associative query conditions, and execute the query to achieve flexible construction and associative query conditions.
It improves SQL development efficiency and query performance, simplifies the writing of complex query conditions, supports efficient reuse and sharing of SQL fragments, and reduces code complexity and maintenance difficulty.
Smart Images

Figure CN118520013B_ABST
Abstract
Description
Technical Field
[0001] The present application relates to the field of database technology, and in particular to a database query method, device, electronic device and medium. Background Art
[0002] For the code snippet sharing mechanism developed based on Structured Query Language (SQL), the basic content is dynamic processing of condition assembly, multi-table joins, and associated subqueries. The important content is that under the SQL snippet sharing mechanism, development engineers can form query statements with business characteristics during the business coding process, and achieve rapid sharing and efficient reuse through the atomization and configuration of SQL snippets.
[0003] The related technology provides a variety of SQL development methods, such as Object Relational Mapping (ORM), query builder (QueryBuilder), stored procedure method, SQL template engine method, etc. These methods have made positive explorations in improving development efficiency and simplifying coding, but they still have deficiencies in supporting complex queries and reusing SQL fragments, especially when dealing with large and complex data query and analysis tasks, in the scenario of multi-person collaborative development of SQL sharing and reuse. Therefore, there is an urgent need for a new SQL development method that can improve the efficiency and performance of queries while further improving SQL development efficiency. It should be noted that the information disclosed in the above background technology section is only used to enhance the understanding of the background of the present disclosure, and therefore may include information that does not constitute prior art known to ordinary technicians in the field. Summary of the invention
[0004] In view of this, the embodiments of the present application at least provide a database query method, device, electronic device and medium, which can improve the efficiency and performance of queries while further improving SQL development efficiency.
[0005] This application mainly includes the following aspects:
[0006] In a first aspect, an embodiment of the present application provides a method for querying a database, the method for querying a database comprising:
[0007] Define a first configuration array of query conditions and obtain request parameters; the first configuration array includes at least one first configuration rule, each first configuration rule includes a field name, an SQL condition instruction and a parameter name;
[0008] Generate an SQL query condition array based on the whereComp function, the first configuration array, and the request parameters;
[0009] Define a second configuration array for the associated query and obtain a query object of the database; the second configuration array includes at least one second configuration rule, each second configuration rule includes a primary field associated with the primary table, an associated condition instruction and a parameter name;
[0010] Generate an associated query condition array based on the whereJoin function, the second configuration array, the query object, the request parameters, and the SQL query condition array;
[0011] Execute a query based on the associated query condition array to obtain data query results.
[0012] In a second aspect, an embodiment of the present application further provides a database query device, the database query device comprising a first acquisition module, a first generation module, a second acquisition module, a second generation module and a query module; wherein,
[0013] The first acquisition module is used to define a first configuration array of query conditions and obtain request parameters; the first configuration array includes at least one first configuration rule, each first configuration rule includes a field name, an SQL condition instruction and a parameter name;
[0014] The first generating module is used to generate an SQL query condition array based on the whereComp function, the first configuration array and the request parameter;
[0015] The second acquisition module is used to define a second configuration array of the associated query and acquire a query object of the database; the second configuration array includes at least one second configuration rule, each second configuration rule includes a primary field associated with the primary table, an associated condition instruction and a parameter name;
[0016] The second generating module is used to generate an associated query condition array based on the whereJoin function, the second configuration array, the query object, the request parameter and the SQL query condition array;
[0017] The query module is used to execute a query based on the associated query condition array to obtain a data query result.
[0018] In a third aspect, an embodiment of the present application further provides an electronic device, comprising: a processor, a memory and a bus, wherein the memory stores machine-readable instructions executable by the processor, and when the electronic device is running, the processor and the memory communicate through the bus, and the machine-readable instructions are executed by the processor to execute the steps of the database query method described in the first aspect above.
[0019] In a fourth aspect, an embodiment of the present application further provides a computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor, the steps of the database query method described in the first aspect or the first aspect are executed.
[0020] The embodiments of the present application provide a method, device, electronic device and medium for querying a database, which defines a first configuration array of query conditions and obtains request parameters; generates an SQL query condition array based on the whereComp function, the first configuration array and the request parameters; defines a second configuration array of associated queries and obtains a query object of the database; generates an associated query condition array based on the whereJoin function, the second configuration array, the query object, the request parameters and the SQL query condition array; executes a query based on the associated query condition array to obtain a data query result. The present application generates query conditions by calling the whereComp function and the whereJoin function, and executes a query, which can improve the efficiency and performance of queries while further improving the efficiency of SQL development. BRIEF DESCRIPTION OF THE DRAWINGS
[0021] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings required for use in the embodiments will be briefly introduced below. It should be understood that the following drawings only show certain embodiments of the present application and therefore should not be regarded as limiting the scope. For ordinary technicians in this field, other related drawings can be obtained based on these drawings without paying creative work.
[0022] Figure 1 A flowchart of a database query method provided by an embodiment of the present application is shown;
[0023] Figure 2 One of the functional module diagrams of a database query device provided in an embodiment of the present application is shown;
[0024] Figure 3 A second functional module diagram of a database query device provided in an embodiment of the present application is shown;
[0025] Figure 4 A schematic structural diagram of an electronic device provided in an embodiment of the present application is shown. DETAILED DESCRIPTION
[0026] To make the purpose, technical scheme and advantages of the embodiments of the present application clearer, the technical scheme in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. It should be understood that the drawings in the present application only serve the purpose of explanation and description and are not used to limit the scope of protection of the present application. In addition, it should be understood that the schematic drawings are not drawn in real proportion. The flowchart used in this application shows the operations implemented according to some embodiments of the present application. It should be understood that the operations of the flowchart can be implemented out of sequence, and the steps without logical context can be reversed in order or implemented simultaneously. In addition, those skilled in the art, under the guidance of the content of the present application, can add one or more other operations to the flowchart, or remove one or more operations from the flowchart.
[0027] In addition, the described embodiments are only a part of the embodiments of the present application, rather than all the embodiments. The components of the embodiments of the present application described and shown in the drawings here can be arranged and designed in various configurations. Therefore, the following detailed description of the embodiments of the present application provided in the drawings is not intended to limit the scope of the application claimed for protection, but merely represents the selected embodiments of the present application. Based on the embodiments of the present application, all other embodiments obtained by those skilled in the art without making creative work belong to the scope of protection of the present application.
[0028] In order to enable those skilled in the art to use the contents of this application, the following implementation methods are provided in combination with the specific application scenario "database query". For those skilled in the art, the general principles defined here can be applied to other embodiments and application scenarios without departing from the spirit and scope of this application.
[0029] The following methods, devices, electronic devices or computer-readable storage media of the embodiments of the present application can be applied to any scenario where a database query is required. The embodiments of the present application are not limited to specific application scenarios. Any solution using the database query method, device, electronic device and medium provided by the embodiments of the present application is within the scope of protection of the present application.
[0030] It is worth noting that for the code snippet sharing mechanism developed based on the Structured Query Language (SQL), the basic content is dynamic processing of condition assembly, multi-table joins, and associated subqueries. The important content is that under the SQL snippet sharing mechanism, development engineers can form query statements with business characteristics during the business coding process, and achieve rapid sharing and efficient reuse through the atomization and configuration of SQL fragments.
[0031] Before this application was filed, the related art provided a variety of SQL development methods, such as Object Relational Mapping (ORM), QueryBuilder, stored procedure method, SQL template engine method, etc. The following introduces and explains various SQL development methods respectively:
[0032] ORM is a programming technique that uses object-oriented thinking to map objects to relational database tables and provide object operation methods to achieve data persistence and joint query. Developers can use common object programming methods (object creation / update / delete / query) to operate data on different types of databases, and automatically generate and execute corresponding SQL statements at the bottom of ORM, greatly simplifying the database operation code.
[0033] The query builder provides a set of stream-based application programming interfaces (APIs) for building SQL query statements. Many web frameworks have built-in query builders, such as Laravel, Ruby on Rails, and Thinkphp. The query builder allows you to customize a set of complex conditions, can handle unlimited nested conditions, and use "and" and "or" operators for grouping. It is characterized by simple use, clear syntax, and good readability.
[0034] Stored procedure method, a collection of SQL statements pre-compiled in the database, can complete specific database operations, such as query, insert, update, etc. Stored procedures are executed on the database service, can also accept specified parameters, and can also perform logical control and error handling. Different databases (such as Oracle, SQL Server, MySQL, etc.) support stored procedures, and the concepts are basically the same.
[0035] SQL template engine method, a tool library for generating dynamic SQL statements, allows parameters and control structures to be defined in SQL statements, and dynamically generates SQL statements.
[0036] The above SQL development methods have made positive explorations in improving development efficiency and simplifying coding, but they are still insufficient in supporting complex queries and reusing SQL fragments. Specifically, the ORM method sacrifices the execution efficiency of the program, lacks support for complex queries, has cumbersome coding, and lacks support for SQL sharing; in terms of performance, the ORM method may load all result data sets into memory, instantiate objects, assign attributes, and load related objects, which is very unfriendly to multi-field data tables, especially data sets with more than 400,000 rows. If such large data sets are loaded, too much memory will be occupied, and too many object instantiations will also seriously affect system performance; in terms of coding, the ORM needs to convert the object operation into the underlying database operation based on the mapping relationship between the object and the database table. This mapping relationship may be defined through configuration files, annotations, or naming conventions, which increases the cumbersomeness of code coding; the ORM focuses on abstracting database operations, improving code portability and code-level reuse, but does not provide sufficient support for complex queries, query performance, coding efficiency, and SQL fragment sharing. The query builder has limited support for complex queries (coding is easily confusing) and insufficient SQL encapsulation capabilities; the query builder methods usually correspond to basic SQL conditions, such as like, order, and group; for multi-layer nested subqueries, AND and OR conditional logical combination queries, UNION and UNION ALL operation queries, recursive queries, grouping and aggregation, etc., the code expression may be difficult and verbose, and may even be confusing; there is insufficient support for complex, performance-sensitive query statistics scenarios; for SQL fragment sharing, it is basically only code-level sharing, because the query builder is generally one-time, if you need to execute the same or similar queries in multiple places, you need to repeat the query builder code; to solve the sharing problem, it is often considered to encapsulate commonly used queries into reusable methods or classes, but this increases the coding difficulty of the caller. For example, when the user selects a specified time range condition, the encapsulated method can be called, and a large number of if-else statements will appear in the code call layer, which is very unfavorable for later maintenance. After all, the query builder is not as well-regulated as the ORM method. The stored procedure method has poor portability, is coupled with the application, has difficulty in version management (upgrade deployment), and is not suitable for multiple interactions with the database; stored procedures are usually written in a database-specific language, which is very inconvenient for heterogeneous database service architectures. For example, vehicle trajectory data in the Internet of Vehicles often uses a time series database, and user and vehicle data often use a relational database.The SQL template engine method is suitable for scenarios that require dynamic generation of complex SQL. It solves the problem of mixing SQL and code to a certain extent and improves the maintainability of SQL. However, it also introduces some additional complexity and learning costs. The encapsulation granularity of SQL is relatively large (generally a complete SQL statement). Most usage scenarios are one-time coding and one-time query, and the ability to share SQL fragments is relatively weak.
[0037] The above SQL development method is especially useful when dealing with large and complex data query and analysis tasks, and in scenarios where multiple people collaborate to develop SQL for shared reuse. Therefore, there is an urgent need for a new SQL development method that can improve query efficiency and performance while further improving SQL development efficiency.
[0038] In view of the above problems, the embodiment of the present application defines a first configuration array of query conditions and obtains request parameters; generates an SQL query condition array based on the whereComp function, the first configuration array and the request parameters; defines a second configuration array of associated queries and obtains the query object of the database; generates an associated query condition array based on the whereJoin function, the second configuration array, the query object, the request parameters and the SQL query condition array; executes the query based on the associated query condition array to obtain the data query result. The present application generates query conditions by calling the whereComp function and the whereJoin function, and executes the query, which can improve the efficiency and performance of the query while further improving the efficiency of SQL development.
[0039] To facilitate the understanding of the present application, the technical solution provided by the present application is described in detail below in conjunction with specific embodiments.
[0040] Figure 1 The following is a flow chart of a database query method provided in an embodiment of the present application. Figure 1 As shown, the database query method provided in the embodiment of the present application includes the following steps:
[0041] S101: Define a first configuration array of query conditions and obtain request parameters.
[0042] The first configuration array includes at least one first configuration rule, and each first configuration rule includes a field name, an SQL condition instruction, and a parameter name.
[0043] In a specific implementation, the first configuration array includes at least one first configuration rule, each first configuration rule defines the structure and logic of the query condition, and each first configuration rule includes a field name, an SQL condition instruction, and a parameter name.
[0044] It should be noted that by using configuration rules, the structure and logic of query conditions (i.e., SQL code snippets) can be easily defined and modified without modifying the code, which improves the flexibility and configurability of query condition construction and can adapt to different business needs.
[0045] Exemplarily, the code of the first configuration array defining the query condition is:
[0046] configRules = [
[0047] "field1 operator1 param1",
[0048] "field2 operator2 param2", ... ]
[0051] Wherein, configRules represents a first configuration array, and configRules includes at least one first configuration rule of the query condition. Each first configuration rule is defined in the format of "field name (field) SQL condition instruction (operator) parameter name (param)".
[0052] For example, the code for the request parameter is:
[0053] requestParams = {
[0054] "param1": "value1",
[0055] "param2": "value2", ...
[0057] }
[0058] Among them, requestParams represents the request parameters, which is a key-value pair object or mapping, including the request parameters (param) and their corresponding values (value).
[0059] S102: Generate an SQL query condition array based on the whereComp function, the first configuration array and the request parameters.
[0060] In a specific implementation, the embodiment of the present application can dynamically generate database query conditions by calling the whereComp function. Specifically, the input of the whereComp function is a set of configuration rules (the first configuration array configRules) and request parameters (requestParams).
[0061] It should be noted that the traditional method requires manual splicing of complex SQL statements in the code, which is prone to errors and difficult to maintain. However, using the database query method provided in the embodiment of the present application, complex query conditions can be described by configuration rules, and the code only needs to call the whereComp function to automatically generate the corresponding SQL conditions, which greatly simplifies the coding process, that is, simplifies the writing of complex query conditions.
[0062] In addition, the embodiment of the present application separates the definition of query conditions from the code logic, making the code clearer and easier to understand. The configuration rules describe the query conditions in a declarative way, which is more intuitive and easier to maintain than splicing SQL statements in the code, which improves the readability and maintainability of the code.
[0063] S103: Define a second configuration array for the associated query and obtain a query object from the database.
[0064] The second configuration array includes at least one second configuration rule, and each second configuration rule includes a main field associated with the main table, an associated condition instruction and a parameter name.
[0065] In a specific implementation, the second configuration array includes at least one second configuration rule, and each second configuration rule includes a main field associated with the main table, an associated condition instruction, and a parameter name.
[0066] Among them, corresponding SQL code snippets, such as query conditions, association conditions, etc., can be dynamically generated according to the configuration rules.
[0067] Exemplarily, the code for defining the second configuration array of the associated query may be:
[0068] joinConfigRules = [
[0069] "mainTable.field1 joinOperator1 param1",
[0070] "mainTable.field2 joinOperator2 param2", ... ]
[0073] Among them, joinConfigRules represents the second configuration array, the second configuration array contains at least one second configuration rule of the association query, and each second configuration rule is defined in the format of "main field associated with the main table (mainTable.field) association condition instruction (joinOperator) parameter name (param)".
[0074] It should be noted that the two numerical items mainTable.field1 and joinOperator1 form the join table and on condition; param1 and joinOperator1 form the where condition required by the SQL statement. Specifically, the working principle is the same as whereComp, which is also a dynamic association of data tables. The benefit of this is that tables can be dynamically joined, data query is very efficient, and SQL fragments can be easily shared.
[0075] Exemplarily, the code for obtaining a database query object may be:
[0076] queryObject = getQueryObject()
[0077] Among them, queryObject represents the current query object or query builder, which is used to build and execute database queries.
[0078] S104: Generate an associated query condition array based on the whereJoin function, the second configuration array, the query object, the request parameters, and the SQL query condition array.
[0079] In a specific implementation, the embodiment of the present application can dynamically generate associated query conditions by calling the whereJoin function. Specifically, the input of the whereJoin function is the query object (queryObject), the SQL query condition array (whereConditions) generated by the whereComp function, the second configuration array (joinConfigRules) of the associated query, and the request parameters (requestParams).
[0080] It should be noted that the whereJoin function allows the dynamic generation of associated query conditions according to the configuration rules, making the construction of associated queries more flexible. By passing request parameters, the conditions of associated queries can be dynamically controlled to achieve more personalized query requirements, that is, to support dynamic associated queries.
[0081] S105: Execute a query based on the associated query condition array to obtain a data query result.
[0082] For example, the code to execute the query and obtain the result may be:
[0083] result = queryObject.where(whereConditions).select()
[0084] Here, the embodiment of the present application can reduce unnecessary database queries and data transmissions by dynamically generating optimized SQL query conditions and associated query conditions, thereby improving query efficiency and performance.
[0085] In an embodiment of the present application, a first configuration array of query conditions is defined and request parameters are obtained; an SQL query condition array is generated based on the whereComp function, the first configuration array and the request parameters; a second configuration array of associated queries is defined and a query object of the database is obtained; an associated query condition array is generated based on the whereJoin function, the second configuration array, the query object, the request parameters and the SQL query condition array; a query is executed based on the associated query condition array to obtain data query results. The present application generates query conditions by calling the whereComp function and the whereJoin function, and executes queries, which can improve the efficiency and performance of queries while further improving the efficiency of SQL development.
[0086] In an optional implementation, generating an SQL query condition array based on the whereComp function, the first configuration array, and the request parameter in S102 includes the following steps:
[0087] Step 1021: Parse each first configuration rule in the first configuration array through the whereComp function, and dynamically generate corresponding where clause conditions according to the field name, SQL condition instruction, parameter name and the value of the request parameter parsed from each first configuration rule to obtain the SQL query condition array; wherein the SQL query condition array includes at least one where clause condition.
[0088] In a specific implementation, the whereComp function dynamically generates corresponding SQL query conditions (where clause conditions) by parsing each first configuration rule in the first configuration array and according to the value of the request parameter. The query generated conditions may include equal value conditions, fuzzy matching conditions, range conditions, etc., depending on the SQL condition instructions used in the first configuration rule. The output of the whereComp function is an array containing all generated SQL query conditions, that is, an SQL query condition array.
[0089] For example, the code for generating the where clause condition using the whereComp function may be:
[0090] whereConditions = whereComp(configRules, requestParams)
[0091] Wherein, whereConditions represents an array of SQL query conditions containing at least one where clause condition; whereComp's input is the first configuration array (configRules) and request parameters (requestParams).
[0092] In an optional implementation, the step 1021 dynamically generates corresponding where clause conditions according to the field name, SQL condition instruction, parameter name and the value of the request parameter parsed from each of the first configuration rules to obtain the SQL query condition array, including the following steps: initializing a first empty array; the first empty array is used to store each generated where clause condition; traversing each of the first configuration rules in the first configuration array, checking whether there is a value corresponding to the parameter name in the first configuration rule in the request parameter; if the value corresponding to the parameter name in the first configuration rule exists and is valid, generating a corresponding where clause condition according to the operator; if a valid where clause condition is generated, adding the valid where clause condition to the first empty array to obtain the SQL query condition array.
[0093] In the specific implementation, the workflow of the whereComp function includes the following steps:
[0094] (1). Receive two parameters:
[0095] configRules: the first configuration array, including at least one first configuration rule of the query condition, each first configuration rule is defined in the format of "field name SQL condition instruction parameter name".
[0096] requestParams: request parameters, a key-value pair object or map containing request parameters and their corresponding values.
[0097] (2) Initialize the first empty array whereConditions to store the generated where clause conditions.
[0098] (3). Traverse each first configuration rule in the configRules array:
[0099] Split each first configuration rule into three parts by spaces: field name, SQL condition instruction and parameter name; check whether there is a value corresponding to the parameter name in requestParams, and the value is not empty or undefined; if the parameter value exists and is valid, generate the corresponding SQL query condition according to the operator; if a valid SQL query condition (where clause condition) is generated, add it to the whereConditions array.
[0100] Exemplarily, for the = conditional instruction (such as =), a condition of the form field = 'value' is generated; for the like_both conditional instruction (such as like), a condition of the form field LIKE '%value%' is generated; for the between conditional instruction (such as between), two values corresponding to the parameter name (such as startValue and endValue) are obtained from requestParams, and a condition of the form field BETWEEN 'startValue' AND 'endValue' is generated; for the in_array conditional instruction (such as in), an array of values corresponding to the parameter name (such as [value1, value2, ...]) is obtained from requestParams, and a condition of the form field IN ('value1', 'value2', ...) is generated; other conditional instructions can be expanded and processed as needed, such as the in_comma conditional instruction, which means obtaining the number of values corresponding to the parameter name from requestParams (a comma-separated string, such as "1, 1921, 912"), and generating a condition of the form field IN ('value1', 'value2', ...).
[0101] (4). Returns the generated whereConditions array, which contains all the where clause conditions generated according to each first configuration rule and request parameters.
[0102] (5) The caller can use the whereConditions array to build a complete SQL query statement, or pass it to other functions or methods for further processing.
[0103] In an optional implementation, before the first configuration array defining the query condition in S101, for any of the first configuration rules, the SQL condition instruction in the first configuration rule is generated according to the following steps: the preset query condition type is abstracted and encapsulated to generate the SQL condition instruction matching the preset query condition type; the SQL condition instructions corresponding to different preset query condition types have corresponding configuration formats and processing logic; wherein the preset query condition type is any one of the following types: an equal query condition type, a fuzzy matching condition type, and a range query condition type.
[0104] The idea of calling the whereComp function is to automatically generate the where clause of the SQL query in a configured way. Specific content:
[0105] A) Abstraction of condition types: Common preset query condition types, such as equal value query condition type, fuzzy matching condition type, range query condition type, etc., are abstracted and encapsulated to form specific "SQL condition instructions". Each SQL condition instruction has a corresponding configuration format and processing logic, similar to the instruction set definition of computer software and hardware interfaces. This abstract method allows complex query conditions to be expressed with simple configuration items, reducing the complexity of SQL writing.
[0106] B) Parameterization of condition types: Supports dynamic binding of condition parameters. In the configuration of each SQL condition instruction, the condition parameters exist in the form of placeholders, and the actual parameter values are dynamically provided at runtime. Through the whereComp function, a where clause that conforms to SQL syntax is constructed based on the SQL condition instructions, field names, parameter placeholders and other information in the configuration, so that the same configuration can be reused for different queries, improving the reusability of SQL.
[0107] C) Custom SQL conditional instructions: Based on common business functions (especially heterogeneous databases), R&D engineers can use custom SQL fragments. For example, for the same channel SQL conditional instruction, different where clauses can be constructed for the data model of Mysql objects / Didb objects, which reduces the complexity of SQL writing for heterogeneous databases. Custom SQL conditional instructions are more closely aligned with business query requirements, making it easier to generate building block-style SQL where clauses.
[0108] The generated SQL code snippet (such as a where clause) may include an equal value condition, a fuzzy matching condition, a range condition, etc., depending on the SQL condition instructions used in the configuration rule.
[0109] The generated SQL code snippet can be encapsulated in an array or string and returned for subsequent use.
[0110] In an optional implementation, generating an associated query condition array based on the whereJoin function, the second configuration array, the query object, the request parameters, and the SQL query condition array in S104 includes the following steps: inputting the SQL query condition array, the second configuration array, the query object, and the request parameters into the whereJoin function, the whereJoin function dynamically generates an updated query object by parsing each second configuration rule in the second configuration array and according to the value of the request parameter, the SQL query condition array, and the query object; the updated query object includes multiple associated query conditions.
[0111] In the specific implementation, the input of the whereJoin function is the query object, the SQL query condition array generated by the whereComp function, the second configuration array of the associated query, and the request parameters. Each second configuration rule of the associated query configuration contains the primary field associated with the primary table, the associated condition instruction, and the parameter. The whereJoin function dynamically generates the SQL conditions for the associated query by parsing the associated query configuration rules and according to the values of the request parameters. The generated conditions will be added to the whereConditions array, and the output of the whereJoin function is the updated query object, which contains all the associated query conditions.
[0112] For example, the code for performing an association query using the whereJoin function may be:
[0113] try {
[0114] whereJoin(queryObject, whereConditions, joinConfigRules, requestParams)
[0115] The input of the whereJoin function includes the query object (queryObject), the SQL query condition array (whereConditions) generated by the whereComp function, the second configuration array of the associated query (joinConfigRules), and the request parameters (requestParams).
[0116] In an optional implementation, the whereJoin function dynamically generates an updated query object by parsing each second configuration rule in the second configuration array and according to the value of the request parameter, the SQL query condition array and the query object, including the following steps: traversing each second configuration rule in the second configuration array, splitting each second configuration rule into a primary field associated with the primary table, an associated condition instruction and a parameter name according to spaces; performing corresponding associated operations according to the associated condition instructions defined by the business, and encapsulating the associated operations in the associated condition instructions; writing the associated query conditions generated by the associated condition instructions into a second empty array; the second empty array is used to store the generated associated query conditions; returning the updated query object; the updated query object has applied the associated query and where clause conditions.
[0117] In the specific implementation, the workflow of the whereJoin function includes the following steps:
[0118] (1). Receive four parameters:
[0119] queryObject: Represents the current query object or query builder, which is used to build and execute database queries.
[0120] whereConditions: SQL query condition array, including the generated where clause conditions.
[0121] joinConfigRules: The second configuration array contains the second configuration rules of the associated query. Each second configuration rule is defined in the format of "main table associated field associated condition instruction parameter name".
[0122] requestParams: request parameters, a key-value pair object or map containing request parameters and their corresponding values.
[0123] (2) Traverse the second configuration rule of each associated query in the joinConfigRules array:
[0124] Each second configuration rule is divided into three parts according to spaces: main table association field, association condition instruction, parameter name; according to the association condition instruction defined by the business, the corresponding association operation is performed;
[0125] For example, the conditional instruction vehicle_name_like_both queries the license plate name, and its instruction format is: objectid vehicle_name_like_both vehicleName; format description: the device ID of the main table (used for joint tables), fixed conditional instructions, and parameter license plate name; the generated SQL statement is to connect the main table to the license plate device relationship table, then connect the license plate main table, and then generate the license plate name condition of the license plate main table. All these connection operations are encapsulated in the conditional instructions, and the external business logic only needs to use the conditional instructions, which greatly improves the sharing and reuse of SQL fragments; such as the conditional instruction hold_coon queries the sub-channel, querying a channel (including the equipment of the sub-channel), and its instruction format is mainTableHoldid hold_coonreqHoldId; format description: the device ID of the main table (used for joint tables), fixed conditional instructions, and parameter channel ID; the generated SQL statement is to query the network parameter value of the channel relationship once through the parameter channel ID, and then connect the main table to the "network relationship table of equipment and channels", and then generate the channel ID condition of the network relationship table.
[0126] (3) Write the associated query conditions generated by the associated condition instruction into whereConditions.
[0127] (4) Returns the query object queryObject, to which the associated query and where clause conditions have been applied.
[0128] (5) The caller can continue to perform other operations on the returned query object, such as sorting, paging, aggregation, etc., and finally execute the query to obtain the results.
[0129] Here, the idea of calling the whereJoin function is to automatically connect the primary table and the foreign table as needed through configuration, according to the actual runtime parameter values, and generate the corresponding where clause to automatically generate the join condition clause of the SQL query. R&D engineers only need to configure the connection relationship between tables in the agreed format, without writing complex join statements, to quickly build efficient association queries. The specific content is as follows:
[0130] A) Configuration of connection relationships. The associated fields, connection relationships between tables, parameter conditions, etc. are described in a configuration manner, so that complex table association logic can be expressed in a concise form, improving the readability and maintainability of SQL.
[0131] B) Parameterization of join conditions. Similar to the whereComp function, in the configuration, the join conditions contain parameter placeholders, and the actual parameter values are dynamically provided at runtime, so that the same join configuration can be reused for different queries, improving the reusability of SQL. The internal logic of the whereJoin function can realize multi-table cascading based on the configuration items and actual parameters, which greatly reduces the usage threshold for R&D personnel, simplifies the repeated table join code segments distributed in different business logics, and connects to the outer table on demand based on the actual parameters, greatly reducing the number of table joins and improving query speed.
[0132] In actual projects, good results have been achieved. For example, in the vehicle management platform of the Internet of Vehicles, the channel hierarchy tree is a unified management directory. Through the channel SQL conditional instruction (hold_conn), the vehicle equipment of the sub-channel is automatically associated to complete the data query. The business logic code for screening queries is concise and semantically clear, and it is highly reused in various business code logic modules. Another example is device group query. The statistical report needs to query the device information within the group by device group. At this time, the group SQL conditional instruction groupdevice_conn can be used to automatically associate the device group and other data tables to complete the report query. The group query is also highly reused in the logic of each business code.
[0133] It should be noted that, for the sharing and reuse mechanism of SQL code snippets (SQL query conditions, associated query conditions) in the embodiments of the present application, the generated SQL code snippets can be stored in a shared code snippet library so that multiple developers or projects can access and reuse them; a standardized way can be provided to describe and manage SQL code snippets, such as adding comments, classifications, etc.; developers can quickly find the required SQL code snippets through searching, filtering, etc., and apply them to their own projects.
[0134] In addition, for the method of dynamically generating SQL statements in the embodiment of the present application, a complete SQL statement is dynamically generated according to the value of the request parameter (requestParams). The generated SQL code snippet can be spliced with other necessary SQL statement parts (such as table name, field name, etc.) to form a complete SQL statement. It supports combining multiple SQL code snippets to achieve complex SQL statement generation. By passing request parameters, the dynamic customizability of SQL statements is achieved to meet different query requirements.
[0135] In addition, for the performance optimization and suggestions of the SQL code snippets in the embodiments of the present application, the shared SQL code snippets are performance analyzed and optimized, and suggestions and best practices for performance improvement are provided. Common SQL performance problems, such as missing indexes, redundant query conditions, etc., are identified, and optimization suggestions are given. Execution statistics of SQL code snippets, such as execution time, resource consumption, etc., are collected and analyzed to provide data support for performance optimization. A mechanism is provided to allow developers to provide feedback and discuss the performance of SQL code snippets, and to promote the sharing of performance optimization experience. By configuring rule-driven SQL code snippet generation, combined with the sharing and reuse mechanism of code snippets, as well as dynamic SQL statement generation and performance optimization suggestions, the present invention can significantly improve the efficiency and quality of SQL development.
[0136] In an optional implementation, after executing the query based on the associated query condition array in S105 and before obtaining the data query result, the database query method further includes: handling an exception.
[0137] For example, the code for handling exceptions may be:
[0138] catch (Exception e) {
[0139] handleException(e)
[0140] return false
[0141] It should be noted that the SQL development technology methods such as ORM methods, query builder methods, stored procedure methods, SQL template engines, etc. in the related technologies have deficiencies in complex query support, SQL fragment reuse, etc. The embodiment of the present application provides a new SQL fragment sharing mechanism, the core idea of which is: in the complex business development coding process, the business SQL query conditions are disassembled into reusable atomic fragments, and shareable SQL logical conditions (with business characteristics) are generated through configuration and parameterization, providing a reuse and sharing mechanism at the SQL fragment level in multi-person development collaboration between the business modules of the project.
[0142] Based on the same application concept, the embodiments of the present application also provide a database query device corresponding to the database query method provided in the above embodiments. Since the principle of solving the problem by the device in the embodiments of the present application is similar to the database query method in the above embodiments of the present application, the implementation of the device can refer to the implementation of the method, and the repeated parts will not be repeated.
[0143] like Figure 2 , 3 As shown, Figure 2 This is one of the functional module diagrams of a database query device 200 provided in an embodiment of the present application. Figure 3 This is a second functional module diagram of a database query device 200 provided in an embodiment of the present application. Figure 2 As shown, the database query device 200 includes a first acquisition module 210, a first generation module 220, a second acquisition module 230, a second generation module 240 and a query module 250; wherein,
[0144] The first acquisition module 210 is used to define a first configuration array of query conditions and obtain request parameters; the first configuration array includes at least one first configuration rule, each first configuration rule includes a field name, an SQL condition instruction and a parameter name;
[0145] The first generating module 220 is used to generate an SQL query condition array based on the whereComp function, the first configuration array and the request parameter;
[0146] The second acquisition module 230 is used to define a second configuration array of the associated query and acquire a query object of the database; the second configuration array includes at least one second configuration rule, each second configuration rule includes a primary field associated with the primary table, an associated condition instruction and a parameter name;
[0147] The second generating module 240 is used to generate an associated query condition array based on the whereJoin function, the second configuration array, the query object, the request parameter and the SQL query condition array;
[0148] The query module 250 is used to execute a query based on the associated query condition array to obtain a data query result.
[0149] In an optional embodiment, if Figure 3 As shown, the first generating module 220 includes a parsing unit 221; the parsing unit 221 is used to:
[0150] Parsing each first configuration rule in the first configuration array by using the whereComp function, and dynamically generating a corresponding where clause condition according to the field name, SQL condition instruction, parameter name and the value of the request parameter parsed from each first configuration rule, so as to obtain the SQL query condition array;
[0151] The SQL query condition array includes at least one where clause condition.
[0152] In an optional embodiment, if Figure 3 As shown, the parsing unit 221 is specifically used to obtain the SQL query condition array according to the following steps:
[0153] Initialize a first empty array; the first empty array is used to store each generated where clause condition;
[0154] Traversing each of the first configuration rules in the first configuration array, and checking whether there is a value corresponding to the parameter name in the first configuration rule in the request parameter;
[0155] If the value corresponding to the parameter name in the first configuration rule exists and is valid, generate a corresponding where clause condition according to the operator;
[0156] If a valid where clause condition is generated, the valid where clause condition is added to the first empty array to obtain the SQL query condition array.
[0157] In an optional embodiment, if Figure 3 As shown, before the first configuration array defining the query condition, for any of the first configuration rules, the first acquisition module 210 is further used to generate the SQL condition instruction in the first configuration rule according to the following steps:
[0158] Abstracting and encapsulating the preset query condition type, generating the SQL condition instruction matching the preset query condition type; the SQL condition instructions corresponding to different preset query condition types have corresponding configuration formats and processing logics;
[0159] The preset query condition type is any one of the following types: an equal value query condition type, a fuzzy matching condition type, and a range query condition type.
[0160] In an optional embodiment, if Figure 3 As shown, the second generating module 240 is used to generate the associated query condition array according to the following steps:
[0161] The SQL query condition array, the second configuration array, the query object and the request parameter are input into the whereJoin function. The whereJoin function dynamically generates an updated query object by parsing each second configuration rule in the second configuration array and according to the value of the request parameter, the SQL query condition array and the query object; the updated query object includes multiple associated query conditions.
[0162] In an optional embodiment, if Figure 3 As shown, the second generation module 240 is specifically used to dynamically generate an updated query object according to the following steps:
[0163] Traversing each second configuration rule in the second configuration array, and splitting each second configuration rule into a main field associated with the main table, an associated condition instruction, and a parameter name according to spaces;
[0164] According to the associated condition instruction defined by the business, a corresponding associated operation is executed, and the associated operation is encapsulated in the associated condition instruction;
[0165] The associated query conditions generated by the associated condition instruction are written into a second empty array; the second empty array is used to store the generated associated query conditions;
[0166] Returns the updated query object; the updated query object has the associated query and where clause conditions applied.
[0167] In an optional embodiment, if Figure 3 As shown, after executing the query based on the associated query condition array and before obtaining the data query result, the query module 250 is also used to handle exceptions.
[0168] Based on the same application concept, see Figure 4As shown, it is a structural diagram of an electronic device 400 provided in an embodiment of the present application, including: a processor 410, a memory 420 and a bus 430, the memory 420 stores machine-readable instructions executable by the processor 410, when the electronic device 400 is running, the processor 410 and the memory 420 communicate through the bus 430, and the machine-readable instructions are executed by the processor 410 when running to perform the steps of the database query method described in any of the above embodiments.
[0169] Based on the same application concept, an embodiment of the present application further provides a computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor, the steps of the database query method provided in the above embodiment are executed.
[0170] Specifically, the storage medium can be a general storage medium, such as a mobile disk, a hard disk, etc. When the computer program on the storage medium is run, it can execute the query method of the above-mentioned database, generate query conditions by calling the whereComp function and the whereJoin function, and execute the query, thereby further improving the efficiency of SQL development and improving the efficiency and performance of the query.
[0171] In the embodiment of the present application, the computer program can also execute other machine-readable instructions when run by the processor to execute other methods described in the embodiment. For the specific execution method steps and principles, please refer to the description of the embodiment, which will not be repeated here.
[0172] In the embodiments provided in the present application, it should be understood that the disclosed devices and methods can be implemented in other ways. The device embodiments described above are merely schematic. For example, the division of the units is only a logical function division. There may be other division methods in actual implementation. For example, multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be through some communication interfaces, and the indirect coupling or communication connection of the devices or units can be electrical, mechanical or other forms.
[0173] The units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed on multiple network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.
[0174] In addition, each functional unit in the embodiments provided in the present application may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit.
[0175] If the functions are implemented in the form of software functional units and sold or used as independent products, they can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present application, or the part that contributes to the prior art or the part of the technical solution, can be embodied in the form of a software product. The computer software product is stored in a storage medium, including several instructions to enable a computer device (which can be a personal computer, server, or network device, etc.) to execute all or part of the steps of the method described in each embodiment of the present application. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, Random Access Memory), disk or optical disk, and other media that can store program codes.
[0176] It should be noted that similar numbers and letters represent similar items in the following figures. Therefore, once an item is defined in one figure, it does not need to be further defined and explained in subsequent figures. In addition, the terms "first", "second", "third", etc. are only used to distinguish the description and are not to be understood as indicating or implying relative importance.
[0177] Finally, it should be noted that the above-described embodiments are only specific implementation methods of the present application, which are used to illustrate the technical solutions of the present application, rather than to limit them. The protection scope of the present application is not limited thereto. Although the present application is described in detail with reference to the above-mentioned embodiments, ordinary technicians in the field should understand that any technician familiar with the technical field can still modify the technical solutions recorded in the above-mentioned embodiments within the technical scope disclosed in the present application, or can easily think of changes, or make equivalent replacements for some of the technical features therein; 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 application. They should all be included in the protection scope of the present application. Therefore, the protection scope of the present application shall be based on the protection scope of the claims.
Claims
1. A method for querying a database, characterized in that: The query method of the database includes: Define a first configuration array of query conditions and obtain request parameters; the first configuration array includes at least one first configuration rule, each first configuration rule includes a field name, an SQL condition instruction and a parameter name; The first configuration rules in the first configuration array are parsed by the whereComp function, and the corresponding where clause conditions are dynamically generated according to the field name, SQL condition instruction, parameter name and the value of the request parameter parsed from each of the first configuration rules, so as to obtain an SQL query condition array, wherein the SQL query condition array includes at least one where clause condition; the generation process of the SQL query condition array includes the following steps: initializing a first empty array, the first empty array is used to store the generated where clause conditions; traversing each of the first configuration rules in the first configuration array, and checking whether there is a value corresponding to the parameter name in the first configuration rule in the request parameter; if the value corresponding to the parameter name in the first configuration rule exists and is valid, generating a corresponding where clause condition according to the operator; if a valid where clause condition is generated, adding the valid where clause condition to the first empty array to obtain the SQL query condition array; Define a second configuration array for the associated query and obtain a query object of the database; the second configuration array includes at least one second configuration rule, each second configuration rule includes a primary field associated with the primary table, an associated condition instruction and a parameter name; Based on the whereJoin function, the second configuration array, the query object, the request parameters and the SQL query condition array, an associated query condition array is generated; the calling idea of the whereJoin function is to automatically connect the primary table and the foreign table as needed according to the parameter values at actual runtime in a configured manner, and generate corresponding where clause conditions to realize automatic generation of join condition clauses of SQL queries; Execute a query based on the associated query condition array to obtain data query results; The generating of the associated query condition array based on the whereJoin function, the second configuration array, the query object, the request parameter and the SQL query condition array includes: inputting the SQL query condition array, the second configuration array, the query object and the request parameter into the whereJoin function, traversing each second configuration rule in the second configuration array, and dividing each second configuration rule into a primary field associated with the main table, an associated condition instruction and a parameter name according to spaces; performing corresponding associated operations according to the associated condition instructions defined by the business, and encapsulating the associated operations in the associated condition instructions; writing the associated query conditions generated by the associated condition instructions into a second empty array; the second empty array is used to store the generated associated query conditions; returning an updated query object; the updated query object has applied the associated query and where clause conditions; the updated query object includes multiple associated query conditions.
2. The method for querying a database according to claim 1, characterized in that: Before the first configuration array defining the query condition, for any of the first configuration rules, the SQL condition instruction in the first configuration rule is generated according to the following steps: Abstracting and encapsulating the preset query condition type, generating the SQL condition instruction matching the preset query condition type; the SQL condition instructions corresponding to different preset query condition types have corresponding configuration formats and processing logics; The preset query condition type is any one of the following types: an equal value query condition type, a fuzzy matching condition type, and a range query condition type.
3. The method for querying a database according to claim 1, characterized in that: After executing the query based on the associated query condition array and before obtaining the data query result, the database query method further includes: Handle exceptions.
4. A database query device, characterized in that: The database query device includes a first acquisition module, a first generation module, a second acquisition module, a second generation module and a query module; wherein, The first acquisition module is used to define a first configuration array of query conditions and obtain request parameters; the first configuration array includes at least one first configuration rule, each first configuration rule includes a field name, an SQL condition instruction and a parameter name; The first generation module includes a parsing unit; the parsing unit is used to parse each first configuration rule in the first configuration array through a whereComp function, and dynamically generate corresponding where clause conditions according to the field name, SQL condition instruction, parameter name and the value of the request parameter parsed from each first configuration rule to obtain an SQL query condition array, wherein the SQL query condition array includes at least one where clause condition; the parsing unit is specifically used to obtain the SQL query condition array according to the following steps: initialize a first empty array; the first empty array is used to store each generated where clause condition; traverse each first configuration rule in the first configuration array, and check whether there is a value corresponding to the parameter name in the first configuration rule in the request parameter; if the value corresponding to the parameter name in the first configuration rule exists and is valid, generate a corresponding where clause condition according to the operator; if a valid where clause condition is generated, add the valid where clause condition to the first empty array to obtain the SQL query condition array; The second acquisition module is used to define a second configuration array of the associated query and acquire a query object of the database; the second configuration array includes at least one second configuration rule, each second configuration rule includes a primary field associated with the primary table, an associated condition instruction and a parameter name; The second generation module is used to generate an associated query condition array based on the whereJoin function, the second configuration array, the query object, the request parameter and the SQL query condition array; the calling idea of the whereJoin function is to automatically connect the main table and the foreign table as needed according to the parameter value at actual runtime in a configured manner, and generate the corresponding where clause condition, so as to realize the automatic generation of the join condition clause of the SQL query; The query module is used to execute a query based on the associated query condition array to obtain a data query result; Among them, the second generation module is used to input the SQL query condition array, the second configuration array, the query object and the request parameter into the whereJoin function, traverse the second configuration rules in the second configuration array, and split each of the second configuration rules into the main field associated with the main table, the associated condition instruction and the parameter name according to the space; according to the associated condition instruction defined by the business, perform the corresponding associated operation, and encapsulate the associated operation in the associated condition instruction; write the associated query condition generated by the associated condition instruction into the second empty array; the second empty array is used to store the generated associated query conditions; return the updated query object; the updated query object has applied the associated query and where clause conditions; the updated query object includes multiple associated query conditions.
5. An electronic device, characterized in that: include: A processor, a memory and a bus, wherein the memory stores machine-readable instructions executable by the processor, and when the electronic device is running, the processor and the memory communicate through the bus, and the machine-readable instructions are executed by the processor to execute the steps of the database query method as described in any one of claims 1 to 3.
6. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the steps of the database query method according to any one of claims 1 to 3 are executed.
