CN110858202A - Method and device for generating where clause in database query statement - Google Patents
Method and device for generating where clause in database query statement Download PDFInfo
- Publication number
- CN110858202A CN110858202A CN201810953871.3A CN201810953871A CN110858202A CN 110858202 A CN110858202 A CN 110858202A CN 201810953871 A CN201810953871 A CN 201810953871A CN 110858202 A CN110858202 A CN 110858202A
- Authority
- CN
- China
- Prior art keywords
- parameter
- operator
- name
- clause
- caller
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Abstract
The invention discloses a method and a device for generating a where clause in a database query sentence, and relates to the technical field of computers. One embodiment of the method comprises: converting parameters transmitted by a caller into a where clause object, wherein the where clause object comprises a parameter name, an operator and a parameter value of the parameters; processing the operator according to a predefined operator processing rule to obtain an operator which can be identified by the database query statement; and generating a where clause according to the operator, the parameter name and the parameter value which can be identified by the database query statement. According to the implementation method, unified processing of all single field query conditions can be realized by using only a small amount of codes, so that a large amount of redundant invalid codes are avoided, the code development amount is greatly reduced, the code utilization rate is increased, and the development efficiency and the code execution efficiency are improved.
Description
Technical Field
The invention relates to the technical field of computers, in particular to a method and a device for generating a where clause in a database query sentence.
Background
Currently, in the Mybatis framework, a common method for analyzing and processing a mapper. xml mapping file to obtain a where conditional clause of a database Query statement SQL (Structured Query Language) statement is as follows: enumerating all query conditions provided by the query statement of the database, checking parameters transmitted through the mapper. xml mapping file one by one, and dynamically adding fields corresponding to the parameters to a where clause (referred to as a clause) in the SQL statement as a part of a final query statement when the transmitted parameters are not empty.
For example: in a certain functional module of a certain system, a data query function of a certain table is provided. Three query conditions of name (name), state (state _ id), and creation time (created _ time) are provided, and the query conditions can be provided separately or in combination. Then the existing implementation scheme is: all the three query condition fields are listed in the where clause, and whether the three query condition fields are added to the where clause of the final SQL statement is determined by judging whether the input parameter values are null or not. One possible implementation code in the mapper file is as follows:
when the query conditions change, for example: if the query mode of the name field needs to be added to support fuzzy query, a query statement needs to be added again, and the following codes need to be added:
similarly, when a specific variety of state supports need to be added to a state, or a support range query support needs to be added to a creation time, etc., a corresponding query statement needs to be added.
In the process of implementing the invention, the inventor finds that at least the following problems exist in the prior art:
1. the code function is single and can not be used universally. For example: judging the name field, only applying to the name field in the SQL, and when the name field needs to be processed in other SQL sentences, repeatedly writing the code;
2. when the query condition is changed, a large number of codes are required to be modified to add a new query statement, otherwise, if a new query condition is added to the original statement, the query condition is wrong. Each additional query condition requires a new additional query statement. The code amount is multiplied, but the useful conditions of one query only account for a few, most of the codes cannot be executed, so that the code utilization rate is low, and the execution efficiency is not high.
Disclosure of Invention
In view of this, embodiments of the present invention provide a method and a device for generating a where clause in a database query statement, which can implement unified processing of all single-field query conditions by using only a small number of codes, thereby avoiding occurrence of a large number of redundant invalid codes, greatly reducing code development amount, increasing code utilization rate, and improving development efficiency and code execution efficiency.
To achieve the above object, according to an aspect of the embodiments of the present invention, a method for generating a where clause in a database query statement is provided.
A method for generating a where clause in a database query statement comprises the following steps: converting parameters transmitted by a caller into a where clause object, wherein the where clause object comprises parameter names, operational characters and parameter values of the parameters; processing the operator according to a predefined operator processing rule to obtain an operator which can be identified by a database query statement; and generating a where clause according to the operator which can be identified by the database query statement, the parameter name and the parameter value. Optionally, converting the parameters introduced by the caller into a where clause object comprises: acquiring a parameter name, an operator and a parameter value of a parameter transmitted by a caller; and if the preset parameter mapping table contains the parameter name of the parameter and the parameter value of the parameter is not null, packaging the parameter name, the operator and the parameter value of the parameter as a where clause object, wherein the parameter mapping table stores the mapping relation between the parameter name of the parameter transmitted by a caller and the parameter name field name of the where clause.
Optionally, the parameter imported by the caller is a parameter of a key-value pair type, wherein the key name is a parameter name, the key value is a parameter value, the parameter name comprises the parameter name and an operator, and the operator is connected with the parameter name through a delimiter in a postfix manner.
Optionally, obtaining the parameter name, the operator and the parameter value of the parameter transmitted by the caller comprises: judging whether the parameter name of the parameter transmitted by the caller contains a separator between the parameter name and the operator; if yes, dividing the parameter name according to the separator to obtain the parameter name and the operator.
Optionally, the operation corresponding to the operator includes: equal to, not equal to, greater than, not greater than, less than, not less than, left-side fuzzy query, right-side fuzzy query, two-side fuzzy query, enumerated value range query, query not in enumerated value range.
Optionally, if the operator in the parameter name is null, the default is "equal to" operation.
According to another aspect of the embodiments of the present invention, an apparatus for generating a where clause in a database query statement is provided.
A device for generating a where clause in a database query statement, comprising: the parameter conversion module is used for converting parameters transmitted by a caller into a where clause object, and the where clause object comprises parameter names, operational characters and parameter values of the parameters; the operator processing module is used for processing the operator according to a predefined operator processing rule to obtain an operator which can be identified by the database query statement; and the where clause generating module is used for generating the where clause according to the operator which can be identified by the database query sentence, the parameter name and the parameter value. Optionally, the parameter conversion module is further configured to: acquiring a parameter name, an operator and a parameter value of a parameter transmitted by a caller; and if the preset parameter mapping table contains the parameter name of the parameter and the parameter value of the parameter is not null, packaging the parameter name, the operator and the parameter value of the parameter as a where clause object, wherein the parameter mapping table stores the mapping relation between the parameter name of the parameter transmitted by a caller and the parameter name field name of the where clause.
Optionally, the parameter imported by the caller is a parameter of a key-value pair type, wherein the key name is a parameter name, the key value is a parameter value, the parameter name comprises the parameter name and an operator, and the operator is connected with the parameter name through a delimiter in a postfix manner.
Optionally, obtaining the parameter name, the operator and the parameter value of the parameter transmitted by the caller comprises: judging whether the parameter name of the parameter transmitted by the caller contains a separator between the parameter name and the operator; if yes, dividing the parameter name according to the separator to obtain the parameter name and the operator.
Optionally, the operation corresponding to the operator includes: equal to, not equal to, greater than, not greater than, less than, not less than, left-side fuzzy query, right-side fuzzy query, two-side fuzzy query, enumerated value range query, query not in enumerated value range.
Optionally, if the operator in the parameter name is null, the default is "equal to" operation.
According to another aspect of the embodiment of the invention, an electronic device for generating a where clause in a database query statement is provided.
An electronic device for generation of a where clause in a database query statement, comprising: one or more processors; the storage device is used for storing one or more programs, and when the one or more programs are executed by the one or more processors, the one or more processors implement the method for generating the where clause in the database query statement provided by the embodiment of the invention.
According to yet another aspect of embodiments of the present invention, a computer-readable medium is provided.
A computer-readable medium, on which a computer program is stored, where the computer program, when executed by a processor, implements a method for generating a where clause in a database query statement according to an embodiment of the present invention.
One embodiment of the above invention has the following advantages or benefits: parameters transmitted by a caller are converted into a where clause object, the operator included in the where clause object is processed according to a predefined operator processing rule to obtain an operator which can be identified by a database query statement, and then the where clause is generated by combining a parameter name and a parameter value, so that the way that a general template is extracted by abstracting a where condition in an SQL statement and is specifically realized as the operator processing rule in a mapper file to develop a general code is realized, and the unified processing of all single field query conditions can be realized by using only a small amount of codes, thereby avoiding a large amount of redundant invalid codes, greatly reducing the code development amount, increasing the code utilization rate, and improving the development efficiency and the code execution efficiency. Meanwhile, the invention associates the where statement in the mapper file with the entity class WhereClauseItem, so that the caller only needs to transmit the WhereClauseItem linked list to the mapper interface without concerning the transmission quantity and sequence of specific parameters, thereby realizing the decoupling of the caller. In addition, the invention also provides common SQL query condition support, the determination of the operator type in the SQL query where statement is preposed to the caller, and the caller can finally determine the operator in the where statement only by modifying the parameter name suffix, thereby increasing the flexibility of the code and being suitable for more query scenes. Finally, the invention also provides parameter mapping operation, prevents the caller from transmitting harmful parameters maliciously and improves the code security.
Further effects of the above-mentioned non-conventional alternatives will be described below in connection with the embodiments.
Drawings
The drawings are included to provide a better understanding of the invention and are not to be construed as unduly limiting the invention. Wherein:
FIG. 1 is a schematic diagram of main steps of a method for generating a where clause in a database query statement according to an embodiment of the present invention;
FIG. 2 is a flow chart illustrating an implementation of an embodiment of the present invention;
FIG. 3 is a schematic diagram of main modules of an apparatus for generating a where clause in a database query statement according to an embodiment of the present invention;
FIG. 4 is an exemplary system architecture diagram in which embodiments of the present invention may be employed;
fig. 5 is a schematic block diagram of a computer system suitable for use in implementing a terminal device or server of an embodiment of the invention.
Detailed Description
Exemplary embodiments of the present invention are described below with reference to the accompanying drawings, in which various details of embodiments of the invention are included to assist understanding, and which are to be considered as merely exemplary. Accordingly, those of ordinary skill in the art will recognize that various changes and modifications of the embodiments described herein can be made without departing from the scope and spirit of the invention. Also, descriptions of well-known functions and constructions are omitted in the following description for clarity and conciseness.
By analyzing the SQL syntax definition and a large amount of code, it can be summarized that: the where clauses in all database query statements conform to (or may be converted to) the following structure: parameter name operator parameter values. For example: name >18, hobbies in ('movie', 'basketball'), and the like.
According to this structure, a generic template is extracted:
columnName operator value。
in the above example, columnName (parameter name) is: name, age, hobbies; operators (operators or operators) are: (ii) h, >, in; values are Zhang III, 18, movie and basketball respectively.
Thus, a wide variety of where clauses can be described using the three fields included in the above template. The above structure is then abstracted into the WhereClauseItem class (see below for class definitions). By changing the value of the attribute of the class object, different where clauses can be represented.
For example: when the value of the columnName attribute of an object of the WhereClauseltem class is "name", the value of the operator attribute is "leave three", and the value of the value attribute is "leave three", the expression is the where conditional query clause of "leave three".
Further, since operators such as ═ >, < and the like belong to special characters and are not convenient to use directly in codes, various operators can be represented by character strings, for example, "eq" represents "═," gt "represents" >, "lt" represents "<", and the like. Characters such as ═, >, < etc. are used when finally converted into an SQL query statement.
Therefore, in order to express the where conditional query clause of "name" · zhang ", it is necessary to set the value of the operator attribute of the object of the wheterlauseitemtem class to" eq ".
And because one where clause can contain a plurality of query conditions, the condition that a plurality of WhereClauseltem objects can be introduced is represented by defining the introduced parameters as a linked list of WhereClauseltem.
The following describes the implementation process of the technical solution of the present invention in detail.
Fig. 1 is a schematic diagram of main steps of a method for generating a where clause in a database query statement according to an embodiment of the present invention. As shown in fig. 1, the method for generating a where clause in a database query statement according to the embodiment of the present invention mainly includes the following steps S101 to S103.
Step S101: converting parameters transmitted by a caller into a where clause object, wherein the where clause object comprises a parameter name, an operator and a parameter value of the parameters;
step S102: processing the operator according to a predefined operator processing rule to obtain an operator which can be identified by the database query statement;
step S103: and generating a where clause according to the operator, the parameter name and the parameter value which can be identified by the database query statement.
As described above, when receiving the parameters that the caller has introduced, the parameters need to be packaged as the where clause object corresponding to the extracted generic template.
In one embodiment of the present invention, a where clause object class WhereClauseItem class is predefined and defined as follows:
the WhereLauseItem object (namely, the where clause object) can be generated by assigning the parameters of 'columnName', 'operator', 'value' in the WhereLauseItem class. According to the parameters transmitted by the caller, the parameters in the WhereClauseItem class can be assigned, so that the parameter names, the operational characters and the parameter values of the parameters are encapsulated into corresponding where clause objects. When the number of parameters transmitted by the caller is multiple, the generated where clause objects are also multiple correspondingly and exist in the form of a wheereClauseltem linked list, wherein the wheereClauseltem linked list stores the wheereClauseltem objects (i.e. where clause objects) one by one.
Specifically, when step S101 is executed to convert parameters that are introduced by a caller into a where clause object, the following steps may be included:
step S1011: acquiring a parameter name, an operator and a parameter value of a parameter transmitted by a caller;
step S1012: and if the preset parameter mapping table contains the parameter name of the parameter and the parameter value of the parameter is not null, packaging the parameter name, the operator and the parameter value of the parameter as a where clause object, wherein the parameter mapping table stores the mapping relation between the parameter name of the parameter transmitted by a caller and the parameter name field name of the where clause.
According to one embodiment of the invention, the parameter transmitted by the caller is a parameter of a key-value pair type, wherein the key name is a parameter name, the key value is a parameter value, the parameter name comprises the parameter name and an operator, and the operator is connected with the parameter name through a delimiter in a postfix mode. Specifically, the parameter that the caller has introduced is, for example, a Map < String, Object > type parameter, the key name key of the Map parameter stores the parameter name, and the key value stores the parameter value (i.e., the field value of the where condition). When a caller passes in parameters, the parameter name needs to include the parameter name and an operator, and the operator is connected with the parameter name in a suffix form through a SEPARATOR (for example, WhereClauseItem. operator _ SEPARATOR), and only one SEPARATOR is allowed to appear in the parameter name. Since exact matches (i.e.: equal operations) are most widely used in database queries, an "equal (eq)" operation is defaulted if the parameter name in the parameter name has no operator suffix.
After the caller enters the parameters, the 3 fields of the parameter name, the operator and the parameter value of the parameters are obtained. Since the parameter name and the operator in the parameters introduced by the caller are both contained in the parameter name and connected by the separator, when the parameter name and the operator are obtained, the following steps can be performed:
judging whether the parameter name of the parameter transmitted by the caller contains a separator between the parameter name and the operator;
if yes, dividing the parameter name according to the separator to obtain the parameter name and the operator.
If the parameter name does not contain the parameter name and the separator of the operator, the operator is default or null, and the operation is "eq (eq)" by default.
In addition, in order to ensure the correctness and the safety of the parameters transmitted by the caller, the invention adds a parameter mapping table paramsMap, and maps the parameter names of the parameters transmitted by the caller and the parameter name field names of the where clause in SQL. And finally transmitting the parameters into the mapper file for processing only when the parameter names of the parameters transmitted by the caller exist in the parameter mapping table and the parameter values are not empty. In one embodiment of the invention, the mapping table is defined as follows:
Map<String,String>paramsMap＝new HashMap<>()；
paramsMap.put("name","name")；
paramsMap.put("stateId","state_id")；
paramsMap.put("createdTime","created_time")。
the functions of the above codes are: and establishing a mapping relation between the parameter names in the java code and the parameter names in the database table. Before SQL is executed, the parameter names introduced by java code are converted into the parameter names in SQL. For example, as for the stateId parameter, in the java code, its parameter name is "stateId", and in the database, the corresponding parameter name is "state _ id". When the SQL query of the database is performed, if the conversion of the parameter name is not performed, the parameter name stateId in the code is directly imported, which inevitably causes the SQL query error because the database does not have the field of the stateId.
According to an embodiment of the present invention, a specific implementation of step S101 is, for example, by defining a method: public int count (Map < String, Object > params), by executing the method, the WhereClauseItem Object linked list can be generated. One specific implementation of the method is as follows:
by executing the codes, Map parameters transmitted by a caller can be converted into a where clause object WhereClauseltem object, and the where clause object WhereClauseltem object exists in a form of a WhereClauseltem linked list.
After the whitecellameltem linked list is obtained, step S102 may be executed: and processing the operator according to the predefined operator processing rule to obtain the operator which can be identified by the database query statement.
Wherein the predefined operator processing rule is realized by abstract extraction of conditional query logic in the mapper file where statement. In the embodiment of the invention, the conditional query logics represented by the 'if' conditional statement in the wrapper file where statement are analyzed, summarized and the like, different operators are defined for different operations, and the implementation of the operator corresponding to each operation is represented by the 'while' conditional statement, so that the abstract extraction of the operations corresponding to various conditional query logics into a common template is realized. One specific implementation of the embodiment of the present invention is, for example:
through the general template realized by the codes, the following various types of operations can be realized for each query operation: equal to (eq), not equal to (neq), greater than (gt), not greater than (neq), less than (lt), not less than (nlt), left-side fuzzy query (llk), right-side fuzzy query (rlk), two-side fuzzy query (flk), enumerated value range query (in), and so forth. The types of operations are not limited to the types listed above, and include, for example: queries that are NULL, queries that are not within the enumerated value range, and the like. In the specific implementation process, the method can be flexibly set according to the application requirements.
According to the operator processing rule implemented by the common template, operators (e.g., eq, lt, gt, etc.) included in the where clause object WhereClauseItem object can be processed to obtain operators (e.g., eq, lt, gt, etc.) recognizable by the database query statement.
Finally, step S103 is executed: and generating a where clause according to the operator, the parameter name and the parameter value which can be identified by the database query statement. Specifically, when the where clause is generated, the name of the parameter, an operator recognizable by the database query sentence, and the order of the parameter values are combined.
According to an embodiment of the present invention, a where clause is generated for each WhereClauseItem object. For each WhereClauseltem object, taking the parameter name columnName corresponding to the WhereClauseltem object as the parameter name of the where clause, and then adding an operator after the parameter name according to the operator corresponding to the WhereClauseltem object, which may specifically include the following cases: if operator is "eq or null", it means "equal to" operation, and an equal sign "equal to" is added after columnName;
if operator is "neq", this means "not equal to" operation, and an unequal number "! ═ i ";
if operator is "gt", it means "greater than" operation, then add greater than number ">" after columnName;
if operator is "ngt", it means "not greater than" operation, and a number of ≤ is added after columnName "< ═ or";
if operator is "lt", it means "less than" operation, then add a less than sign "<" after columnName;
if operator is "nlt", it means "not less than" operation, and a number "equal to or greater than" > ", is added after columnName;
if operator is "llk", indicating "left side fuzzy query" operation, adding left side fuzzy query condition "LIKE concate concatat ('%', value)", after columnName;
if operator is "rlk", indicating "right fuzzy query" operation, right fuzzy query condition "LIKE concke CONCAT (value, '%')" is added after columnName;
if the operator is "flk", it indicates the operation of "two-side fuzzy query", then the two-side fuzzy query condition "LIKE CONCAT concate ('%', value, '%')" is added after the columnName;
if operator is "in", it indicates "enumeration value range query" operation, and then an enumeration value range query condition "in (value)" is added after columnName.
After the operator is added, adding the parameter value corresponding to the WhereClauseItem object as the parameter value of the where clause is added to the operator or filling the position of the value in the operator. Thus, a where clause can be generated. Through the same process, all the parameters transmitted by the caller can be generated into corresponding where clauses.
In the above-described steps, the execution of step S101 is generally performed at the Service layer. After obtaining the WhereClauseItem linked list, the Service layer calls the data access layer Dao layer to realize data query. And the data access layer performs data interaction with the database through a mapper interface (an interface for realizing association between the Java interface and the SQL statement).
In an embodiment of the present invention, if the SQL of a certain statistical data number is defined as follows:
wherein, a where clause corresponding to the predefined operator processing rule is referred to by "< include confirmed ═ where"/> ". When executing the database query statement containing the where clause, the where clause needs to be transmitted into the WhereClauseltem object linked list.
The Service layer generates a WhereClauseItem object linked list according to the parameters transmitted by the caller, for example, a method is defined: public int count (Map < String, Object > params), by executing the method, the WhereClauseItem Object linked list can be generated. Then, the Service layer transfers the WhereClauseItem object linked list to the Dao layer by calling the mapper method of the Dao layer, and the specific implementation of the calling process is as follows: count (whereClauseltem (params)). Then, query is performed through mapper interface of Dao layer, and a query method is defined as follows: int count (@ Param ("whereClauseList") List < wheereClauseList > whereClauseList).
The mapper interface judges the type of an operator of each WhereClauseltem object by traversing the parameters of the transmitted WhereClauseltem object linked list, generates the operator which can be identified by a database query statement, and generates a final where condition query clause aiming at the WhereClauseltem object by combining the parameter name columnName and the parameter value attribute of the operator.
The following describes an implementation flow of the embodiment of the present invention with reference to fig. 2. Fig. 2 is a schematic diagram of an implementation flow of the embodiment of the present invention, and as shown in fig. 2, after receiving a call request sent by a caller and acquiring a passed parameter, the parameter is traversed, and whether a parameter name of the parameter is in a parameter mapping table is determined. If not, acquiring the next parameter and judging again; if yes, judging whether the parameter value is empty, if yes, acquiring the next parameter and judging whether the parameter name of the parameter is in the parameter mapping table; otherwise, the parameter name and operator are separated. And then generating a WhereClauseItem object according to the parameter name, the operator and the parameter value, and adding the WhereClauseItem object into a WhereClauseItem object linked list. And then, transferring the WhereClauseItem object linked list to the processing logic of the where clause of the mapper file by calling a Dao layer method. And finally, traversing a WhereLauseItem object linked list by the processing logic of the where clause, judging the type of the operator of each WhereLauseItem object, generating the operator which can be identified by the query statement of the database, and generating the where clause by combining the parameter name and the parameter value of the operator.
Fig. 3 is a schematic block diagram of a main module of an apparatus for generating a where clause in a database query statement according to an embodiment of the present invention. As shown in fig. 3, the apparatus 300 for generating a where clause in a database query statement according to an embodiment of the present invention mainly includes a parameter transformation module 301, an operator processing module 302, and a where clause generating module 303.
The parameter conversion module 301 is configured to convert a parameter input by a caller into a where clause object, where the where clause object includes a parameter name, an operator, and a parameter value of the parameter;
the operator processing module 302 is configured to process an operator according to a predefined operator processing rule to obtain an operator that can be identified by a database query statement;
the where clause generating module 303 is configured to generate a where clause according to the operator, the parameter name, and the parameter value that can be recognized by the database query statement.
According to an embodiment of the present invention, the parameter conversion module 301 may be further configured to:
acquiring a parameter name, an operator and a parameter value of a parameter transmitted by a caller;
if the preset parameter mapping table contains the parameter name of the parameter and the parameter value of the parameter is not null, the parameter name, the operator and the parameter value of the parameter are packaged into a where clause object, wherein the parameter mapping table stores the mapping relation between the parameter name of the parameter transmitted by a caller and the parameter name field name of the where clause.
According to another embodiment of the invention, the parameter introduced by the caller is a parameter of a key-value pair type, wherein the key name is a parameter name, the key value is a parameter value, the parameter name comprises the parameter name and an operator, and the operator is connected with the parameter name in a suffix form through a separator.
According to another embodiment of the present invention, the obtaining of the parameter name, the operator, and the parameter value of the parameter transmitted by the caller may specifically include:
judging whether the parameter name of the parameter transmitted by the caller contains a separator between the parameter name and the operator;
if yes, dividing the parameter name according to the separator to obtain the parameter name and the operator.
According to an embodiment of the present invention, the operation corresponding to the operator includes, but is not limited to: equal to, not equal to, greater than, not greater than, less than, not less than, left-side fuzzy query, right-side fuzzy query, two-side fuzzy query, enumerated value range query, query not in enumerated value range.
According to an embodiment of the present invention, if the operator in the parameter name is null, the default is an "equal" operation.
According to the technical scheme of the embodiment of the invention, parameters transmitted by a caller are converted into the where clause object, the operator included in the where clause object is processed according to the predefined operator processing rule to obtain the operator capable of being identified by the database query sentence, and then the where clause is generated by combining the parameter name and the parameter value, so that the where condition in the SQL sentence is abstracted, the general template is extracted, and the general code is developed by specifically realizing the operator processing rule in the mapper file, and only a small amount of codes are used, so that the unified processing of all single field query conditions can be realized, thereby avoiding a large amount of redundant codes, greatly reducing the code development amount, increasing the code utilization rate, and improving the development efficiency and the code execution efficiency. Meanwhile, the invention associates the where statement in the mapper file with the entity class WhereClauseItem, so that the caller only needs to transmit the WhereClauseItem linked list to the mapper interface without concerning the transmission quantity and sequence of specific parameters, thereby realizing the decoupling of the caller. In addition, the invention also provides common SQL query condition support, the determination of the operator type in the SQL query where statement is preposed to the caller, and the caller can finally determine the operator in the where statement only by modifying the parameter name suffix, thereby increasing the flexibility of the code and being suitable for more query scenes. Finally, the invention also provides parameter mapping operation, prevents the caller from transmitting harmful parameters maliciously and improves the code security.
Fig. 4 shows an exemplary system architecture 400 of a method for generating a where clause in a database query statement or a device for generating a where clause in a database query statement, to which an embodiment of the present invention may be applied.
As shown in fig. 4, the system architecture 400 may include terminal devices 401, 402, 403, a network 404, and a server 405. The network 404 serves as a medium for providing communication links between the terminal devices 401, 402, 403 and the server 405. Network 404 may include various types of connections, such as wire, wireless communication links, or fiber optic cables, to name a few.
A user may use terminal devices 401, 402, 403 to interact with a server 405 over a network 404 to receive or send messages or the like. The terminal devices 401, 402, 403 may have installed thereon various communication client applications, such as shopping-like applications, web browser applications, search-like applications, instant messaging tools, mailbox clients, social platform software, etc. (by way of example only).
The terminal devices 401, 402, 403 may be various electronic devices having a display screen and supporting web browsing, including but not limited to smart phones, tablet computers, laptop portable computers, desktop computers, and the like.
The server 405 may be a server providing various services, such as a background management server (for example only) providing support for shopping websites browsed by users using the terminal devices 401, 402, 403. The backend management server may analyze and perform other processing on the received data such as the product information query request, and feed back a processing result (for example, target push information, product information — just an example) to the terminal device.
It should be noted that the method for generating a where clause in a database query statement provided in the embodiment of the present invention is generally executed by the server 405, and accordingly, the generating device for a where clause in a database query statement is generally disposed in the server 405.
It should be understood that the number of terminal devices, networks, and servers in fig. 4 is merely illustrative. There may be any number of terminal devices, networks, and servers, as desired for implementation.
Referring now to FIG. 5, a block diagram of a computer system 500 suitable for use with a terminal device or server implementing an embodiment of the invention is shown. The terminal device or the server shown in fig. 5 is only an example, and should not bring any limitation to the functions and the scope of use of the embodiments of the present invention.
As shown in fig. 5, the computer system 500 includes a Central Processing Unit (CPU)501 that can perform various appropriate actions and processes according to a program stored in a Read Only Memory (ROM)502 or a program loaded from a storage section 508 into a Random Access Memory (RAM) 503. In the RAM 503, various programs and data necessary for the operation of the system 500 are also stored. The CPU 501, ROM 502, and RAM 503 are connected to each other via a bus 504. An input/output (I/O) interface 505 is also connected to bus 504.
The following components are connected to the I/O interface 505: an input portion 506 including a keyboard, a mouse, and the like; an output portion 507 including a display such as a Cathode Ray Tube (CRT), a Liquid Crystal Display (LCD), and the like, and a speaker; a storage portion 508 including a hard disk and the like; and a communication section 509 including a network interface card such as a LAN card, a modem, or the like. The communication section 509 performs communication processing via a network such as the internet. The driver 510 is also connected to the I/O interface 505 as necessary. A removable medium 511 such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, or the like is mounted on the drive 510 as necessary, so that a computer program read out therefrom is mounted into the storage section 508 as necessary.
In particular, according to the embodiments of the present disclosure, the processes described above with reference to the flowcharts may be implemented as computer software programs. For example, embodiments of the present disclosure include a computer program product comprising a computer program embodied on a computer readable medium, the computer program comprising program code for performing the method illustrated in the flow chart. In such an embodiment, the computer program may be downloaded and installed from a network through the communication section 509, and/or installed from the removable medium 511. The computer program performs the above-described functions defined in the system of the present invention when executed by the Central Processing Unit (CPU) 501.
It should be noted that the computer readable medium shown in the present invention can be a computer readable signal medium or a computer readable storage medium or any combination of the two. A computer readable storage medium may be, for example, but not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination of the foregoing. More specific examples of the computer readable storage medium may include, but are not limited to: an electrical connection having one or more wires, a portable computer diskette, a hard disk, a Random Access Memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing. In the present invention, a computer readable storage medium may be any tangible medium that can contain, or store a program for use by or in connection with an instruction execution system, apparatus, or device. In the present invention, however, a computer readable signal medium may include a propagated data signal with computer readable program code embodied therein, for example, in baseband or as part of a carrier wave. Such a propagated data signal may take many forms, including, but not limited to, electro-magnetic, optical, or any suitable combination thereof. A computer readable signal medium may also be any computer readable medium that is not a computer readable storage medium and that can communicate, propagate, or transport a program for use by or in connection with an instruction execution system, apparatus, or device. Program code embodied on a computer readable medium may be transmitted using any appropriate medium, including but not limited to: wireless, wire, fiber optic cable, RF, etc., or any suitable combination of the foregoing.
The flowchart and block diagrams in the figures illustrate the architecture, functionality, and operation of possible implementations of systems, methods and computer program products according to various embodiments of the present invention. In this regard, each block in the flowchart or block diagrams may represent a module, segment, or portion of code, which comprises one or more executable instructions for implementing the specified logical function(s). It should also be noted that, in some alternative implementations, the functions noted in the block may occur out of the order noted in the figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently, or the blocks may sometimes be executed in the reverse order, depending upon the functionality involved. It will also be noted that each block of the block diagrams or flowchart illustration, and combinations of blocks in the block diagrams or flowchart illustration, can be implemented by special purpose hardware-based systems which perform the specified functions or acts, or combinations of special purpose hardware and computer instructions.
The units or modules described in the embodiments of the present invention may be implemented by software, or may be implemented by hardware. The described units or modules may also be provided in a processor, and may be described as: a processor includes a parameter conversion module, an operator processing module, and a where clause generation module. The names of these units or modules do not form a limitation to the units or modules themselves in some cases, for example, the parameter conversion module may also be described as a "module for converting parameters introduced by a caller into a where clause object".
As another aspect, the present invention also provides a computer-readable medium that may be contained in the apparatus described in the above embodiments; or may be separate and not incorporated into the device. The computer readable medium carries one or more programs which, when executed by a device, cause the device to comprise: converting parameters transmitted by a caller into a where clause object, wherein the where clause object comprises parameter names, operational characters and parameter values of the parameters; processing the operator according to a predefined operator processing rule to obtain an operator which can be identified by a database query statement; and generating a where clause according to the operator which can be identified by the database query statement, the parameter name and the parameter value.
According to the technical scheme of the embodiment of the invention, parameters transmitted by a caller are converted into the where clause object, the operator included in the where clause object is processed according to the predefined operator processing rule to obtain the operator capable of being identified by the database query sentence, and then the where clause is generated by combining the parameter name and the parameter value, so that the where condition in the SQL sentence is abstracted, the general template is extracted, and the general code is developed by specifically realizing the operator processing rule in the mapper file, and only a small amount of codes are used, so that the unified processing of all single field query conditions can be realized, thereby avoiding a large amount of redundant codes, greatly reducing the code development amount, increasing the code utilization rate, and improving the development efficiency and the code execution efficiency. Meanwhile, the invention associates the where statement in the mapper file with the entity class WhereClauseItem, so that the caller only needs to transmit the WhereClauseItem linked list to the mapper interface without concerning the transmission quantity and sequence of specific parameters, thereby realizing the decoupling of the caller. In addition, the invention also provides common SQL query condition support, the determination of the operator type in the SQL query where statement is preposed to the caller, and the caller can finally determine the operator in the where statement only by modifying the parameter name suffix, thereby increasing the flexibility of the code and being suitable for more query scenes. Finally, the invention also provides parameter mapping operation, prevents the caller from transmitting harmful parameters maliciously and improves the code security.
The above-described embodiments should not be construed as limiting the scope of the invention. Those skilled in the art will appreciate that various modifications, combinations, sub-combinations, and substitutions can occur, depending on design requirements and other factors. Any modification, equivalent replacement, and improvement made within the spirit and principle of the present invention should be included in the protection scope of the present invention.
Claims (14)
1. A method for generating a where clause in a database query statement is characterized by comprising the following steps:
converting parameters transmitted by a caller into a where clause object, wherein the where clause object comprises parameter names, operational characters and parameter values of the parameters;
processing the operator according to a predefined operator processing rule to obtain an operator which can be identified by a database query statement;
and generating a where clause according to the operator which can be identified by the database query statement, the parameter name and the parameter value.
2. The method of claim 1, wherein converting caller-incoming parameters into a where clause object comprises:
acquiring a parameter name, an operator and a parameter value of a parameter transmitted by a caller;
and if the preset parameter mapping table contains the parameter name of the parameter and the parameter value of the parameter is not null, packaging the parameter name, the operator and the parameter value of the parameter as a where clause object, wherein the parameter mapping table stores the mapping relation between the parameter name of the parameter transmitted by a caller and the parameter name field name of the where clause.
3. The method of claim 2, wherein the parameter introduced by the caller is a parameter of a key-value pair type, wherein the key name is a parameter name, the key value is a parameter value, the parameter name comprises a parameter name and an operator, and the operator is connected with the parameter name by a delimiter in a postfix manner.
4. The method of claim 3, wherein obtaining parameter names, operators, and parameter values for parameters incoming by a caller comprises:
judging whether the parameter name of the parameter transmitted by the caller contains a separator between the parameter name and the operator;
if yes, dividing the parameter name according to the separator to obtain the parameter name and the operator.
5. The method of claim 1, wherein the operation corresponding to the operator comprises: equal to, not equal to, greater than, not greater than, less than, not less than, left-side fuzzy query, right-side fuzzy query, two-side fuzzy query, enumerated value range query, query not in enumerated value range.
6. The method of claim 5, wherein if the operator in the parameter name is null, then the default is an "equal" operation.
7. A device for generating a where clause in a database query statement, comprising:
the parameter conversion module is used for converting parameters transmitted by a caller into a where clause object, and the where clause object comprises parameter names, operational characters and parameter values of the parameters;
the operator processing module is used for processing the operator according to a predefined operator processing rule to obtain an operator which can be identified by the database query statement;
and the where clause generating module is used for generating the where clause according to the operator which can be identified by the database query sentence, the parameter name and the parameter value.
8. The apparatus of claim 7, wherein the parameter conversion module is further configured to:
acquiring a parameter name, an operator and a parameter value of a parameter transmitted by a caller;
and if the preset parameter mapping table contains the parameter name of the parameter and the parameter value of the parameter is not null, packaging the parameter name, the operator and the parameter value of the parameter as a where clause object, wherein the parameter mapping table stores the mapping relation between the parameter name of the parameter transmitted by a caller and the parameter name field name of the where clause.
9. The apparatus of claim 8, wherein the parameter imported by the caller is a parameter of a key-value pair type, wherein the key name is a parameter name, the key value is a parameter value, the parameter name comprises a parameter name and an operator, and the operator is connected with the parameter name by a delimiter in a suffix form.
10. The apparatus of claim 9, wherein obtaining parameter names, operators, and parameter values for parameters incoming by a caller comprises:
judging whether the parameter name of the parameter transmitted by the caller contains a separator between the parameter name and the operator;
if yes, dividing the parameter name according to the separator to obtain the parameter name and the operator.
11. The apparatus of claim 7, wherein the operation corresponding to the operator comprises: equal to, not equal to, greater than, not greater than, less than, not less than, left-side fuzzy query, right-side fuzzy query, two-side fuzzy query, enumerated value range query, query not in enumerated value range.
12. The apparatus of claim 11, wherein if the operator in the parameter name is null, then the default is an "equal" operation.
13. An electronic device for generating a where clause in a database query statement, comprising:
one or more processors;
a storage device for storing one or more programs,
when executed by the one or more processors, cause the one or more processors to implement the method of any one of claims 1-6.
14. A computer-readable medium, on which a computer program is stored, which, when being executed by a processor, carries out the method according to any one of claims 1-6.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201810953871.3A CN110858202A (en) | 2018-08-21 | 2018-08-21 | Method and device for generating where clause in database query statement |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201810953871.3A CN110858202A (en) | 2018-08-21 | 2018-08-21 | Method and device for generating where clause in database query statement |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN110858202A true CN110858202A (en) | 2020-03-03 |
Family
ID=69634985
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201810953871.3A Pending CN110858202A (en) | 2018-08-21 | 2018-08-21 | Method and device for generating where clause in database query statement |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN110858202A (en) |
Cited By (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111831659A (en) * | 2020-07-09 | 2020-10-27 | 天津车之家数据信息技术有限公司 | Index checking method and device and computing equipment |
| CN112199387A (en) * | 2020-11-16 | 2021-01-08 | 广州速威智能系统科技有限公司 | Method and system for processing database query statement |
| CN112286958A (en) * | 2020-11-16 | 2021-01-29 | 广州速威智能系统科技有限公司 | Processing method and system for database query expression |
| CN113111079A (en) * | 2021-04-13 | 2021-07-13 | 北京沃东天骏信息技术有限公司 | Database execution statement generation method and device |
| CN113111239A (en) * | 2021-04-08 | 2021-07-13 | 北京联创新天科技有限公司 | Universal database operation method, device and storage medium thereof |
| CN113641701A (en) * | 2021-10-13 | 2021-11-12 | 苏州浪潮智能科技有限公司 | Data query method, system, heterogeneous acceleration platform and storage medium |
-
2018
- 2018-08-21 CN CN201810953871.3A patent/CN110858202A/en active Pending
Cited By (10)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111831659A (en) * | 2020-07-09 | 2020-10-27 | 天津车之家数据信息技术有限公司 | Index checking method and device and computing equipment |
| CN111831659B (en) * | 2020-07-09 | 2022-09-06 | 天津车之家数据信息技术有限公司 | Index checking method and device and computing equipment |
| CN112199387A (en) * | 2020-11-16 | 2021-01-08 | 广州速威智能系统科技有限公司 | Method and system for processing database query statement |
| CN112286958A (en) * | 2020-11-16 | 2021-01-29 | 广州速威智能系统科技有限公司 | Processing method and system for database query expression |
| CN113111239A (en) * | 2021-04-08 | 2021-07-13 | 北京联创新天科技有限公司 | Universal database operation method, device and storage medium thereof |
| CN113111239B (en) * | 2021-04-08 | 2024-03-29 | 北京联创新天科技有限公司 | General database operation method, device and storage medium thereof |
| CN113111079A (en) * | 2021-04-13 | 2021-07-13 | 北京沃东天骏信息技术有限公司 | Database execution statement generation method and device |
| CN113641701A (en) * | 2021-10-13 | 2021-11-12 | 苏州浪潮智能科技有限公司 | Data query method, system, heterogeneous acceleration platform and storage medium |
| CN113641701B (en) * | 2021-10-13 | 2022-02-18 | 苏州浪潮智能科技有限公司 | Data query method, system, heterogeneous acceleration platform and storage medium |
| US11893011B1 (en) | 2021-10-13 | 2024-02-06 | Inspur Suzhou Intelligent Technology Co., Ltd. | Data query method and system, heterogeneous acceleration platform, and storage medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN110858202A (en) | Method and device for generating where clause in database query statement | |
| CN111447257B (en) | Message conversion method and device | |
| CN107491382B (en) | Log output method and device | |
| CN110471848B (en) | Method and device for dynamically returning message | |
| CN111427701A (en) | Workflow engine system and business processing method | |
| CN110555030A (en) | SQL statement processing method and device | |
| CN110795315A (en) | Method and device for monitoring service | |
| CN109062560B (en) | Method and apparatus for generating information | |
| CN113076153A (en) | Interface calling method and device | |
| CN113626223A (en) | Interface calling method and device | |
| CN112653614A (en) | Request processing method and device based on message middleware | |
| CN110377273B (en) | Data processing method, device, medium and electronic equipment | |
| CN110704200A (en) | Method and device for converting call interface | |
| CN111427899A (en) | Method, device, equipment and computer readable medium for storing file | |
| CN110109983B (en) | Method and device for operating Redis database | |
| CN111241189A (en) | Method and device for synchronizing data | |
| CN108959294B (en) | Method and device for accessing search engine | |
| CN110795135A (en) | Method and device for realizing injection-resolution configuration | |
| CN112612467A (en) | Method and device for processing micro front-end architecture based on qiankun | |
| CN112181542A (en) | Function calling method and device, electronic equipment and storage medium | |
| CN110764769A (en) | Method and device for processing user request | |
| CN113779018A (en) | Data processing method and device | |
| CN113709171A (en) | Method and device for cross-component state communication | |
| CN113760969A (en) | Data query method and device based on elastic search | |
| CN112905273A (en) | Service calling method and device |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |