ibatis.net Dynamic SQL statements

Before learning a dynamic SQL statement, you must first understand the conditional query, and first learn how to pass parameters to the ibatis.net mapping file.

First, condition inquiry

　　1. Pass a single parameter

If you query by ID:

    <select id= "Selectpersonbyid" resultmap= "person" parameterclass= "Int32" >      select * from person WHERE id = #Id # --so pass in a parameter    </select>

The method to invoke is:

Personmodel p = Mapper. 　　Queryforobject<personmodel> ("Selectpersonbyid", 3); 3 is the parameter passed to the SQL statement

Therefore, the last SQL statement generated is:

SELECT * from person WHERE Id = 3

In the simplest way, I don't even have a parameterclass.

　　2. Pass multiple parameters

Passing multiple parameters typically uses key-value pairs, such as Hashtable or entity classes.

　　1, the way of key-value pairs

　　A key-value pair can be used Hashtable or DICTIONARY<TKEY,TVALUE>, when the value of the Parameterclass in the mapping file for the latter is: System.Collections.IDictionary.

    parameterclass= "HashTable">      select * from person WHERE Id = #Id # and Name = #Name #    </select>

Call Method:

            Hashtable ht = new Hashtable ();            Ht. ADD ("Id", 1);            Ht. ADD ("Name", "Liu Bei");            

The actual generated SQL statement is as follows:

EXEC sp_executesql n ' SELECT * from person WHERE Id =  @param0  and Name =  @param1 ', N ' @param0 int, @param1 Nvarch AR (2) ', @param0 =1, @param1 =n ' Liu Bei '

Ibatis.net is not bad. Automatically caches the execution plan for parameterized queries.

　　2. Custom parameter Classes

Custom parameter classes typically use this entity class, and you can, of course, customize a parameter class (with the same name as a parameter in an SQL statement).

    <select id= "Selectpersonbyid" resultmap= "person" parameterclass= "Ibatis.Net.Domain.PersonModel" >      Select * From the person WHERE Id = #Id # and Name = #Name #    </select>

Call Method:

  Personmodel p = new Personmodel ();  P.id = 1;  P.name = "Liu Bei";  

Second, dynamic query

Dynamic query means that when a certain condition is met, a piece of SQL code is spliced.

Or just use the example to illustrate the problem:

    <select id= "Selectpersonbyid" resultmap= "person" parameterclass= "Hashtable" >      Select TOP 1 * from person WHERE 1=1      <dynamic prepend= "and" >        <islessequal prepend= "and" property= "Id" comparevalue= "3" >  <!--stitching this clause with          ID = #Id #        </isLessEqual>        <isnotempty prepend= "and" property= when the passed parameter ID is less than 3 o'clock ' Name ' >  <!--stitching the clause--          name = #Name #        </isNotEmpty>      </when the passed parameter name is not "" or null Dynamic>    </select>

Call the method unchanged, interesting place here, if the incoming parameter is: HT. ADD ("Id", 4);

The resulting SQL statement is:

SELECT TOP 1 * from person WHERE 1=1

If the incoming parameter is: HT. ADD ("Id", 2);

The resulting SQL statement is:

SELECT TOP 1 * from person WHERE 1=1 and Id = @param0 ', N ' @param0 int ', @param0 =2

If the incoming parameter is: HT.  ADD ("Id", 2); Ht. ADD ("Name", "Zhuge Liang");

The resulting SQL statement is:

EXEC sp_executesql n ' SELECT TOP 1 * from the person WHERE 1=1 and Id =  @param0 and Name =  @param1 ', N ' @param0 int, @para M1 nvarchar (3) ', @param0 =2, @param1 =n ' Zhuge Liang '

Dynamic query is the case, according to certain conditions, to determine whether a piece of SQL code should be spliced.

More judging conditions are as follows:

Property keyword	Meaning
<isEqual>	The query condition is valid if the parameter equals the value.
<isNotEqual>	The query condition is valid if the parameter is not equal to the value.
<isGreaterThan>	The query condition is valid if the parameter is greater than the value.
<isGreaterEqual>	The query condition is valid if the parameter is greater than or equal to the value.
<isLessEqual>	The query condition is valid if the parameter is less than the value.
<isPropertyAvailable>	If this attribute is in the argument, the query condition is valid.
<isNotPropertyAvailable>	If this attribute is not in the parameter, the query condition is valid.
<isNull>	If the argument is NULL, the query condition is valid.
<isNotNull>	the query condition is valid if the argument is not NULL.
<isEmpty>	If the argument is empty, the query condition is valid.
<isNotEmpty>	The query condition is valid if the argument is not empty.
<isParameterPresent>	The query condition is valid if a parameter object exists.
<isNotParameterPresent>	The query condition is valid if no parameter object exists.

Property Description:

• perpend--the part of an SQL statement that can be overridden, added to the front of the statement, which is optional.
• property--is the property of the comparison, which is required.
• compareproperty--another property to compare to the former (required or select CompareValue property)
• comparevalue--value to compare (required or select Compareproperty property)

There is a more specific condition: iterate. This thing is used to iterate over multiple SQL fragments.

    <select id= "Selectpersonwithcountryname" resultmap= "person" >      select Person.id,person.age,person.name, Country.countryname INNER JOIN country on person.countryid = country.id    </select>    <select Id= "Selectpersonbyid" resultmap= "person" parameterclass= "Hashtable" >      select * from person      <dynamic prepend= "WHERE" >        <isnotnull prepend= "and" property= "NameList" > <!--traverse the NameList parameter for each generation of a name in Namelist[i]-->          Name          in <iterate property= "NameList" open= "(" close= ")" conjunction= "," >            # namelist[]#            </iterate>        </isNotNull>      </dynamic>    </select>

The Calling method is:

  list<string> liststr = new list<string> ();  Liststr.add ("Liu Bei");  Liststr.add ("Jack Huang");  Liststr.add ("Caocao");  Hashtable ht = new Hashtable ();  Ht. ADD ("NameList", liststr);  ilist<personmodel> LISTP = mapper. Queryforlist<personmodel> ("Selectpersonbyid", HT);

The resulting SQL statement is:

EXEC sp_executesql n ' SELECT * from person WHERE Name in (@param0, @param1, @param2) ', N ' @param0 nvarchar (2), @param1 nvarchar (2), @param2 nvarchar (2) ', @param0 =n ' Liu Bei ', @param1 =n ' Jack Huang ', @param2 =n ' Caocao '

Properties of the Iterate:

• prepend--the part of an SQL statement that can be overridden, added to the front of the statement, which is optional.
• The property--type is a list of element properties for traversal, which is required.
• open--the entire string that iterates through the content body, defining parentheses, which is optional.
• A string that close--the end of the entire traversed content body, which defines parentheses, which is optional.
• conjunction--A string between each iteration of the content that is used to define and or or, which is optional.

Finally attached to the hard-to-find 1.62 source code HTTP://MYBATISNET.GOOGLECODE.COM/SVN/TAGS/CS_RELEASE_1.6.2/

Ibatis.net Dynamic SQL statements