Ibatis dynamic mapped statement configuration details

Dynamic mapped statement
A common issue of using JDBC directly is dynamic SQL. Use the parameter values, parameters themselves, and data Columns
It is a dynamic SQL statement, which is usually very difficult. A typical solution is to use a series of IF-else condition statements and a series
Annoying string connection. For this problem, the SQL map API uses a very similar structure with mapped statement,
Provides a more elegant method. Here is a simple example:
<Select id = "dynamicgetaccountlist"
Cachemodel = "account-Cache"
Resultmap = "account-result">
Select * from account
<Isgreaterthan prepend = "and" property = "ID" comparevalue = "0">
Where acc_id = # ID #
</Isgreaterthan>
Order by acc_last_name
</SELECT>
 
In the preceding example, two possible statements can be created based on the bean "ID" attribute. If
If the number of "IDS" is greater than 0, the following statement is created:
Select * from account where acc_id =?
Alternatively, if the "ID" parameter is smaller than or equal to 0, the following statement is created:
Select * from account
In more complex cases, dynamic mapped statement is more useful. The following is a complex example:
<Statement id = "dynamicgetaccountlist" resultmap = "account-result">

Select * from account
<Dynamic prepend = "where">
<Isnotnull prepend = "and" property = "firstname">
(Acc_first_name = # firstname #
<Isnotnull prepend = "or" property = "lastname">
Acc_last_name = # lastname #
</Isnotnull>
)
</Isnotnull>
<Isnotnull prepend = "and" property = "emailaddress">
Acc_email like # emailaddress #
</Isnotnull>
<Isgreaterthan prepend = "and" property = "ID" comparevalue = "0">
Acc_id = # ID #
</Isgreaterthan>
</Dynamic>
Order by acc_last_name

</Statement>
Based on different conditions, the above dynamic statement can generate 16 different query statements. Use the IF-else structure and
String that generates hundreds of lines of messy code.
The use of dynamic statement is as easy as inserting condition labels around the dynamic part of SQL. For example:
<Statement id = "somename" resultmap = "account-result">
Select * from account
<Dynamic prepend = "where">
<Isgreaterthan prepend = "and" property = "ID" comparevalue = "0">
Acc_id = # ID #
</Isgreaterthan>
<Isnotnull prepend = "and" property = "lastname">
Acc_last_name = # lastname #
</Isnotnull>
</Dynamic>
Order by acc_last_name
</Statement>
 
In the preceding example, the <dynamic> element divides the dynamic part of the SQL statement. The dynamic part can contain any number
The condition tag determines whether the SQL code is included in the statement. All condition tag Elements
It will work based on the parameter objects passed to the dynamic query statement. <Dynamic> both element and condition element have
"Prepend" attribute, which is part of dynamic SQL code and can be "prepend" of the parent element if necessary"
Overwrite attributes. In the preceding example, the prepend attribute "where" overwrites the first true condition element. This pair

To ensure that the correct SQL statement is generated. For example, in the first condition element that is "true", "and"
It is not required. In fact, adding it will definitely make an error. The following sections discuss different condition elements, including binary condition elements.
Element, mona1 condition element, and other dynamic elements.
Binary condition Element
The binary condition element compares an attribute value with a static value or another attribute value. If the condition is true
The contents of explain will be included in the query SQL statement. Attributes of binary condition elements:
Prepend-an SQL statement that can be overwritten. (optional)
Property-the property to be compared (required)
Compareproperty-another property used for comparison with the former (required or selected comparevalue)
Comparevalue-value used for comparison (required or compareproperty)
Compare whether the property value is equal to the static value or another property value.
<Isequal>
Compare whether the property value is not equal to the static value or another property value.
<Isnotequal>

Compares whether a property value is greater than a static value or another property value.
<Isgreaterthan>
Compare whether the property value is greater than or equal to the static value or another property value.
<Isgreaterequal>
Compare whether the property value is smaller than the static value or another property value.
<Islessthan>
Compare whether the property value is less than or equal to the static value or another property value.
<Islessequal>
Example:
<Islessequal prepend = "and" property = "Age" comparevalue = "18">
Adolescent = 'true'
</Islessequal>
 
Mona1 condition Element
The mona1 condition element checks whether the attribute status meets specific conditions. Attributes of a one-dimensional condition element:
Prepend-an SQL statement that can be overwritten. (optional)
Property-the property to be compared (required)

Check whether this property exists (the property of parameter bean exists ).
<Ispropertyavailable>
Check whether this attribute does not exist (the parameter bean attribute does not exist ).
<Isnotpropertyavailable>
Check whether the property is null.
<Isnull>
Check whether the property is not null.
<Isnotnull>
Check the value of collection. Size (), string or string. valueof () of the property,
<Isempty>
Whether it is null or null ("" or size () <1 ).
Check the value of collection. Size (), string or string. valueof () of the property,
<Isnotempty>
Whether it is not null or not empty ("" or size ()> 0 ).
Example:
<Isnotempty prepend = "and" property = "firstname">
First_name = # firstname #
</Isnotempty>
 
 
Other elements parameter present: these elements check whether the parameter object exists.
Parameter present attributes:
Prepend-an SQL statement that can be overwritten. (optional)
Check whether a parameter object exists (not null ).
<Isparameterpresent>
Check whether the parameter object does not exist (the parameter object is null ).
<Isnotparameterpresent>
Example:
<Isnotparameterpresent prepend = "and">
Employee_type = 'default'
</Isnotparameterpresent>
Iterate: This property traverses the entire set and repeats the content of the element body in the list set.
Iterate attributes:
Prepend-an SQL statement that can be overwritten. (optional)
Property-elements used for traversal whose type is Java. util. List (required)
Open-string starting from the whole traversal content body, used to define parentheses (optional)
Close-the end string of the entire traversal body, used to define parentheses (optional)
Conjunction-a string between each traversal content, used to define and or (optional)
The element whose traversal type is Java. util. List.
<Iterate>
Example:
<Iterate prepend = "and" property = "usernamelist"
Open = "(" close = ")" conjunction = "or">
Username = # usernamelist [] #
</Iterate>
 
 
Note: When Using <iterate>, it is very important to include square brackets [] after the name of the list element. square brackets []
Mark the object as list to prevent the parser from simply outputting the list to a string.