(turn) Ibatis dynamic Query

In the complex query process, we often need to determine the query criteria according to the user's choice, where the change
Not just parameters in SQL, including the fields and qualifications included in the SELECT statement, can change
Of. Typical cases, such as in a complex query page, we have to choose according to the user's choice and input decision to check
A combination of criteria for the inquiry.
A typical page is as follows:
For this combination query page, we should generate a different query language according to the content that the user chooses to fill in.
Sentence
If the user does not fill out any information to submit the query request, we should return all records:
Select * from T_user;
If the user only fills in the name "Erica" on the page, we should generate a similar:
Select * from T_user where name like '%erica% ';
SQL query statement.
If the user only fills out the address "Beijing" on the page, we should generate a similar:
Select * from T_user where the address is like '%beijing% ';
of SQL.
And if the user fills in both the name and address ("Erica" & ' Beijing '), we should generate a similar:
Select * from T_user where name like '%erica% ' and '%beijing% '
SQL query statement.
For Ibatis, which requires an ORM implementation that specifies the SQL statement in advance, the traditional approach is to
The If-else statement determines the input parameters and then invokes a different statement definition for the user selection. For
The above simple situation (two kinds of permutation combinations of query conditions, a total of 4 kinds of cases), statement's heavy
The complex definition of work has been a bother, and for every so frequently have seven or eight query conditions, or even more than 10 query conditions
, the trivial and repetitive statement definitions are unbearable.
In view of this problem, Ibatis introduces the dynamic mapping mechanism, that is, in the statement definition, according to the different
Query parameters, set the corresponding SQL statement.
Or take the example above:
<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>
Through the dynamic node, we define a dynamically where clause. In this WHERE clause, the
There may be two criteria for determining the name and address fields. And whether these two fields join the search depends on
The query criteria provided by the user (whether the field is empty [Isnotempty]).
For a typical Web program, we get the field names in the form by HttpServletRequest
and set it into query parameters, such as:
User.setname (Request.getparameter ("name"));
User.setaddress (Request.getparameter ("Address"));
Sqlmap.queryforlist ("User.getusers", User);
When executing queryforlist ("User.getusers", User), Ibatis is based on the configuration text
The SQL dynamic generation rules set up in the, create the corresponding SQL statements.
In the example above, we specified the name and address genus by the decision node Isnotempty
Dynamic rules for sex:
<isnotempty prepend= "and" property= "name" >
(Name like #name #)
</isNotEmpty>
The semantics of this node is that if the "name" property of the parameter class is Non-null (Isnotempty, or Non-empty
String ""), the resulting SQL WHERE clause includes a decision condition (name like #name #) whose
#name# will be populated with the Name property value of the parameter class.
The decision generation for the Address property is exactly the same as the Name property, and this is no longer a repeat.
Thus, by introducing the dynamic node in the statement definition, we implemented the SQL sentence very simply.
The dynamic generation of the stator sentence will bring great convenience to the complicated combinatorial query.
The definition of node can be very flexible, we can even use nested decision nodes to implement complex dynamic
State mappings, such as:
<isnotempty prepend= "and" property= "name" >
(Name= #name #
<isnotempty prepend= "and" property= "Address" >
address= #address #
</isNotEmpty>
)
</isNotEmpty>
This definition stipulates that only when the user provides the name information can the query be combined with the address data (if only
Providing address data and ignoring name information will still be considered full search.
The prepend attribute in the dynamic node and the decision node indicates that the SQL clause defined in this node is
The prefix that occurs in the principal SQL.
Such as:
<dynamic prepend= "WHERE" >
<isnotempty prepend= "and" property= "name" >
(Name like #name #)
</isNotEmpty>
<isnotempty prepend= "and" property= "Address" >
(Address like #address #)
</isNotEmpty>
</dynamic>
Assuming the value of the "name" property is "Erica" and the value of the "Address" property is "Beijing", it
Generates a SQL clause similar to the following (the actual runtime will generate a PreparedStatement with placeholders,
And then populate it with data):
WHERE (name like ' Beijing ') and (address like ' Beijing ')
The statement after where is defined in the dynamic node, so the dynamic node
The prepend setting ("WHERE") is prefixed with the "and" which is actually the Address property
The prepend setting of the Isnotempty node, which leads to the SQL clause defined in the corresponding node. As for
The Isnotempty node for the name attribute, because Ibatis automatically determines whether an append prepend is required
Prefix, where (name like #name #) is the first conditional clause in the WHERE clause, without and before
suffix, so automatically omitted.
The decision node is not limited to Isnotempty,ibatis to provide a rich decision definition function.
The decision nodes are divided into two categories:
Ø One-yuan determination
A unary decision is a determination of the property value itself, such as whether the property is null, whether it is null, or not.
Isnotempty in the example above is a typical unary decision.
A unary decision node has:
Section name Description
<isPropertyAvailable> whether this property is provided in the parameter class
<isNotPropertyAvailable> Contrary to <isPropertyAvailable>
<isNull> whether the property value is null
<isNotNull> Contrary to <isNull>
<isEmpty> if the property is collection or string, its size is <1,
If not both of the above types, the
String.valueof (property value)
To determine whether its size is <1 after obtaining its string type value
<isNotEmpty> is the opposite of <isEmpty>.
Ø two Yuan judgment
The binary decision has two decision parameters, one is the attribute name, but the judgment value, such as
<isgreaterthan prepend= "and" property= "age"
Comparevalue= ">"
(age= #age #)
</isGreaterThan>
where property= "age" specifies the attribute name "age", comparevalue= "18" indicates
The judgment value is "18".
The corresponding semantics for the above decision node Isgreaterthan is: If the age attribute is greater than
(CompareValue), add the (age= #age #) condition in SQL.
The binary decision nodes are:
Relationship between node-name attribute value and Comparevalues
<isEqual> equal.
<isNotEqual> range.
<isGreaterThan> Greater than
<isGreaterEqual> greater than or equal
<isLessThan> less than
<isLessEqual> Less than equal