) Ibatis dynamic query

In the complex Query Process, we often need to determine the query conditions based on the user's selection.
It is not just a parameter in SQL, but can be changed if it includes the fields and conditions included in the SELECT statement.
. In typical cases, for example, on a complex composite query page, we must query the results based on user selection and input.
Query condition combination.
A typical page is as follows:
For this Combined Query page, we should generate different query languages for the selected content
Sentence.
If you submit a query request without entering any information, we should return all records:
Select * From t_user;
If the user only fills in the name "Erica" on the page, we should generate something similar:
Select * From t_user where name like '% Erica % ';
SQL query statement.
If you only fill in the address "Beijing" on the page, we should generate something similar:
Select * From t_user where address like '% Beijing % ";
.
If the user has both the name and address ("Erica" & "Beijing"), we should generate something similar:
Select * From t_user where name like '% Erica %' and address like '% Beijing %"
SQL query statement.
For the orm implementation that requires pre-specifying SQL statements like ibatis, the traditional approach is nothing more
The IF-else statement determines the input parameters and then calls different statement definitions for the user. For
In the preceding simple case (the arrangement and combination of the two query conditions, a total of four cases), the weight of statement
The re-definition work has been annoying, but it often has seven or eight query conditions, or even dozens of query conditions.
In terms of arrangement and combination, the trivial and repeated statement Definition is really unbearable.
Considering this problem, ibatis introduces a dynamic ing mechanism, that is, in the statement Definition, according to different
Query parameters and set the corresponding SQL statement.
Take the preceding example as an example:
<Select id = "getusers"
Parameterclass = "user"
Resultmap = "get-user-result">
Select
ID,
Name,
Sex
From t_user
<Dynamic prepend = "where">
<Isnotempty prepend = "and" property = "name">
(Name like # name #)
</Isnotempty>
<Isnotempty prepend = "and" property = "Address">
(Address like # address #)
</Isnotempty>
</Dynamic>
</SELECT>
A dynamic where clause is defined through a dynamic node. In this where clause
It may contain two judgment conditions for the name and address fields. Whether or not these two fields are added to the search depends on
The query conditions provided by the user (whether the field is blank [isnotempty]).
For a typical web Program The field name in the form is obtained through httpservletrequest.
And set it to query parameters, such:
User. setname (request. getparameter ("name "));
User. setaddress (request. getparameter ("Address "));
Sqlmap. queryforlist ("user. getusers", user );
When queryforlist ("user. getusers", user) is executed, ibatis is
And create corresponding SQL statements.
In the preceding example, by determining the node isnotempty
Dynamic Rules:
<Isnotempty prepend = "and" property = "name">
(Name like # name #)
</Isnotempty>
The semantics of this node is that if the "name" attribute of the parameter class is not empty (isnotempty, that is, not empty
The SQL where statement that is generated contains the condition (name like # name #),
# Name # is filled with the name attribute value of the parameter class.
The determination of the address attribute is identical to that of the name attribute.
In this way, by introducing dynamic nodes in the statement Definition, we can easily implement SQL judgment.
The dynamic generation of sub-sentences brings great convenience for complex composite queries.
The definition of decision nodes can be very flexible. We can even use nested decision nodes to implement complex motion
State ing, such:
<Isnotempty prepend = "and" property = "name">
(Name = # name #
<Isnotempty prepend = "and" property = "Address">
Address = # address #
</Isnotempty>
)
</Isnotempty>
This definition specifies that you can query the address data only when you provide the name information.
If the address data is provided and the name information is ignored, it will still be considered as a full search ).
The prepend attribute of the dynamic node and the judgment node specifies that the SQL clause defined in this node is
The prefix when the subject SQL appears.
For example:
<Dynamic prepend = "where">
<Isnotempty prepend = "and" property = "name">
(Name like # name #)
</Isnotempty>
<Isnotempty prepend = "and" property = "Address">
(Address like # address #)
</Isnotempty>
</Dynamic>
If the value of the "name" attribute is "Erica" and the value of the "Address" attribute is "Beijing ",
Generate a SQL clause similar to the following (preparedstatement with placeholders will be generated in actual runtime, where
And then fill in the data ):
Where (name like 'beijing') and (address like 'beijing ')
The where statement is defined in the dynamic node.
Prepend ("where") is used as the prefix, where "and" is actually
Prepend setting of isnotempty node, which leads to the SQL clause defined in the corresponding node. As
The isnotempty node corresponding to the name attribute. Because ibatis automatically determines whether prepend needs to be appended.
Prefix. Here (name like # name #) is the first condition clause in the WHERE clause.
, So it is automatically omitted.
The determination node is not limited to isnotempty. ibatis provides a wide range of judgment and definition functions.
There are two types of nodes:
Ø one dollar judgment
One-dimensional determination is a determination of the attribute value itself, such as whether the attribute is null or null.
In the above example, isnotempty is a typical one-dimensional decision.
One-dimensional judgment nodes include:
Node Name Description
<Ispropertyavailable> whether this attribute is provided in the parameter class
<Isnotpropertyavailable> opposite to <ispropertyavailable>
<Isnull> whether the attribute value is null
<Isnotnull> opposite to <isnull>
<Isempty> If the attribute is collection or string, whether its size is <1,
If not
String. valueof (attribute value)
After obtaining the value of the string type, determine whether its size is <1
<Isnotempty> is opposite to <isempty>.
Ø binary Determination
Binary determination has two judgment parameters. One is the attribute name, but the judgment value, as shown in figure
<Isgreaterthan prepend = "and" property = "Age"
Comparevalue = "18">
(Age = # age #)
</Isgreaterthan>
Property = "Age" specifies the property name "Age", and comparevalue = "18" indicates
The value is 18 ".
The semantics of isgreaterthan is as follows: if the age attribute is greater
18 (comparevalue), add the (age = # age #) condition to the SQL statement.
Binary judgment nodes include:
Relationship between node name attribute values and comparevalues
<Isequal> equal.
<Isnotequal>.
<Isgreaterthan> greater
<Isgreaterequal> greater than or equal
<Islessthan> less
<Islessequal> less than or equal