Dynamic sql--Official documentation

Https://ibatis.apache.org/docs/dotnet/datamapper/ch03s09.html

3.9. Dynamic SQL

A very common problem with working directly with ADO is dynamic SQL. It is normally very difficult to work with SQL statements, and the values of parameters, but which Paramet ERs and columns is included at all. The typical solution is usually a mess of conditional if-else statements and horrid string concatenations. The desired result is often a query by example, where a query can be built to find objects that was similar to the example Object. The IBATIS datamapper API provides a relatively elegant solution that can is applied to any mapped statement element. Here are a simple example:

Example 3.56. A simple Dynamic Select sttatement with possible outcomes

<select id= "dynamicgetaccountlist" cachemodel= "Account-cache" parameterclass= "account" 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 above example, there is the possible statements that could is created depending on the state of the the Id property of The Parameter object. If the Id parameter is greater than 0 and then the statement would be created as follows:

SELECT * FROM account where acc_id =?

Or if the Id parameter is 0 or less, the statement would look as follows.

SELECT * FROM Account

The immediate usefulness of this might isn't become apparent until a more complex situation is encountered. For example, the following are a somewhat more complex example.

Example 3.57. A complex Dynamic SELECT statement with possible outcomes

<select id= "dynamicgetaccountlist" parameterclass= "account" 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</ select>     

Depending on the situation, there could be as many as + different SQL queries generated from the above dynamic statement. To code the IF-ELSE structures and string concatenations could get quite messy and require hundreds of lines of code.

Using dynamic statements is as simple as inserting some conditional tags around the dynamic parts of your SQL. For example:

Example 3.58. Creating a dynamic statement with conditional tags

<statement id= "somename" parameterclass= "account" resultmap= "Account-result" >   <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 above statement, the <dynamic> element demarcates a sections of the SQL that are dynamic. The dynamic element is optional and provides a-a-manage a prepend in cases where the prepend ("where") should isn't be Included unless the contained conditions append to the statement. The statement section can contain any number of conditional elements (see below) that would determine whether the contained SQL code'll is included in the statement. All of the conditional elements work based in the state of the parameter object passed into the query. Both the dynamic element and the conditional elements have a "prepend" attribute. The prepend attribute is a part of the code, the is free to be overridden by the A parent element ' s prepend if necessary. In the above example the "where" prepend would override the first true conditional prepend. This was necessary to ensure the SQL statement is built properly. For example, in the case of the first true condition, there are no need for tHe and, and in fact it would the statement. The following sections describe the various kinds of elements, including Binary conditionals, unary conditionals, and Iter Ate.

3.9.1. Binary Conditional Elements

Binary conditional elements Compare a property value to a static value or another property value. If The result is true, the body content was included in the SQL query.

3.9.1.1. Binary Conditional Attributes:

Prepend–the Overridable SQL part that'll be prepended to the statement (optional)

Property–the property to be compared (required)

Compareproperty–the compared (required or comparevalue)

Comparevalue–the value to be compared (required or compareproperty)

Table 3.7. Binary Conditional attributes

Element	Description
<isEqual>	Checks the equality of a property and a value, or another property. Example Usage: <isequal prepend= "and" property= "status" comparevalue= "Y" >married = ' TRUE ' </isEqual>
<isNotEqual>	Checks the inequality of a property and a value, or another property. Example Usage: <isnotequal prepend= "and" property= "status" comparevalue= "N" >married = ' FALSE ' </isNotEqual>
<isGreaterThan>	Checks if a property was greater than a value or another property. Example Usage: <isgreaterthan prepend= "and" property= "age" comparevalue= "" >adolescent = ' FALSE ' </ Isgreaterthan>
<isGreaterEqual>	Checks if a property was greater than or equal to a value or another property. Example Usage: <isgreaterequal prepend= "and" property= "shoesize" comparevalue= "a" >bigfoot = ' TRUE ' </ Isgreaterequal>
<isLessEqual>	Checks If a property was less than or equal to a value or another property. Example Usage: <islessequal prepend= "and" property= "age" comparevalue= "all" >adolescent = ' TRUE ' </isLessEqual>

3.9.2. Unary Conditional Elements

Unary conditional elements Check the state of a property for a specific condition.

3.9.2.1. Unary Conditional Attributes:

Prepend–the Overridable SQL part that'll be prepended to the statement (optional)

Property–the property to be checked (required)

Table 3.8. Unary conditional attributes

Element	Description
<isPropertyAvailable>	Checks If a property is available (i.e are a property of the Parameter object). Example Usage: <ispropertyavailable property= "id" > account_id= #id # </isPropertyAvailable>
<isNotPropertyAvailable>	Checks If a property was unavailable (i.e not a property of the Parameter object). Example Usage: <isnotpropertyavailable property= "Age" > status= ' New ' </isNotEmpty>
<isNull>	Checks If a property is null. Example Usage: <isnull prepend= "and" property= "Order.id" > Account . account_id = ORDER. ACCOUNT_ID (+) </isNotEmpty>
<isNotNull>	Checks If a property was not null. Example Usage: <isnotnull prepend= "and" property= "order.id" > order. order_id = #order .id#</isnotempty>
<isEmpty>	Checks to see if the value of a Collection, String property is null or empty ("" or size () < 1). Example Usage: <isempty property= "FirstName" > LIMIT 0, 20</isnotempty>
<isNotEmpty>	Checks to see if the value of a Collection, String property is not null and not empty ("" or size () < 1). Example Usage: <isnotempty prepend= "and" property= "FirstName" > first_name like '% $FirstName $% ' </isNotEmpty>

3.9.3. Parameter Present Elements

These elements check for parameter object existence.

3.9.3.1. Parameter Present Attributes:

Prepend–the Overridable SQL part that'll be prepended to the statement (optional)

Table 3.9. Testing to see if a parameter is present

Element	Description
<isParameterPresent>	Checks to see if the Parameter object was present (not NULL). <isparameterpresent prepend= "and" > employee_type = #empType #</isparameterpresent>
<isNotParameterPresent>	Checks to see if the parameter object was not present (null). Example Usage: <isnotparameterpresent prepend= "and" > employee_type = ' DEFAULT ' </isNotParameterPresent>

3.9.4. Iterate Element

This tag would iterate over a collection and repeat the body content for each item in a List

3.9.4.1. Iterate Attributes:

Prepend–the Overridable SQL part that'll be prepended to the statement (optional)

Property–a property of type IList-is-iterated over (required)

Open–the string with which to open the entire block of iterations, useful for brackets (optional)

Close–the string with which to close the entire block of iterations, useful for brackets (optional)

Conjunction–the string to is applied in between each iteration, useful for and and OR (optional)

Table 3.10. Creating a list of conditional clauses

Element

Description

<iterate>

Iterates over a property the is of type IList Example Usage: <iterate prepend= "and" property= "Usernamelist" open= "(" close= ")" conjunction= "OR" > username=# Usernamelist[]#</iterate> Note:it is very important to include the square brackets[] at the end of the List property name when using the iterate El Ement. These brackets distinguish this object as a list to keep the parser from simply outputting the list as a string.

3.9.5. Simple Dynamic SQL Elements

Despite the power of the full Dynamic Mapped Statement API discussed above, sometimes-just need a simple, small piece of your SQL to BES dynamic. For the, SQL statements and statements can contain simple dynamic SQL elements to help implement dynamic ORDER BY clauses , dynamic Select columns or pretty much any part of the SQL statement. The concept works much like inline parameter maps, but uses a slightly different syntax. Consider the following example:

Example 3.59. A dynamic element that changes the collating order

<statement id= "getproduct" resultmap= "Get-product-result" >  $preferredOrder $</statement>

In the above example the Preferredorder dynamic element would be a replaced by the value of the Preferredorder property of th E Parameter object (just like a parameter map). The difference is, and that's a fundamental change to the SQL statement itself, which are much more serious than simply SE Tting a parameter value. A mistake made in a Dynamic SQL Element can introduce security, performance and stability risks. Redundant checks to ensure, the simple dynamic SQL elements is being used appropriately. Also, be mindful of your design, as there are potential for database specifics to encroach on your business object model. For example, the want a column name intended for the ORDER BY clause to end up as a property in your business objec T, or as a field value on your Server page.

Simple dynamic elements can being included within <statements> come in handy when there are a need to modify the SQL Statement itself. For example:

Example 3.60. A dynamic element that changes the comparison operator

<statement id= "getproduct" resultmap= "Get-product-result" >  select * from product  <dynamic prepend= " WHERE ">    <isnotempty property=" Description ">       $operator $ #Description #    </isnotempty >  </dynamic></statement>

The above example the operator property of the Parameter object would be used to replace the $operator $ token. So if the operator is equal to like and the description is equal to%dog%, then the SQL statement gene Rated would be:

  Like '%dog% '

Dynamic sql--Official documentation