Ibatis.net (C #) database query

Quote Please specify http://www.cnblogs.com/13590/archive/2013/03/14/2958735.html

Abstract: query is the core of database SQL language, this paper introduces simple query, conditional query, dynamic query and multi-table query of database by Ibatis.net.

keywords:ibatis.net; dynamic query; multi-table query; Data map

The query is the core of the database SQL language, and the SQL language only provides a single statement for the database query, the SELECT statement. The SELECT statement that is used to express SQL queries is the most powerful and complex SQL statement. In the actual project development process, the query accounted for a large proportion, usually measuring the quality of a framework is also largely dependent on the framework for query flexibility and efficiency. This section describes how database queries are provided in Ibatis.net.

Add the Maps/test3.xml and test3.aspx entries in the project file created in the previous section to record the XML data mapping information and the corresponding program invocation information, respectively.

1. Simple query

Gets the contents of a table, such as obtaining user information for the Dean.sysuser table, and the XML data map configuration information:

<selectid= "Selectsysuser" resultmap= "Sysuserresult" >

SELECT * from DEAN. Sysuser

</select>

The calling code is:

Protectedvoid Button1_Click (object sender, EventArgs e)

{

Simple query

Try

{

Isqlmapper mapper = Mapper.instance (); Get Isqlmapper instances

ilist<ibatistest.domain.sysuser> plist = mapper. Queryforlist<ibatistest.domain.sysuser> ("Test3map.selectsysuser", null);//Call queryForList method

if (plist! = null && plist. Count > 0)

{

Gridview1.datasource = plist;

Gridview1.databind ();

}

Label1.Text = "Simple query Success";

}

catch (Exception ex)

{

Label1.Text = ex. Message;

}

}

The results of this query return all records for the entire table, without passing in the query parameters, and set the parameter to NULL when calling the queryForList method. Considering the efficiency of the system, in actual development, the small table with few records is used.

2. Conditional query

Depending on the criteria, the criteria can be one or more. This approach is widely used in real-world queries and is one of the most applied queries. If the user information is queried based on the login user name, the data map configuration information is:

<selectid= "Selectsysuserbyusername" parameterclass= "string" resultmap= "Sysuserresult" >

SELECT * from DEAN. Sysuser WHERE loginname= #value #

</select>

Ibatis.net a placeholder by using the # or $ symbol, which is the identity parameter. There are 2 ways to introduce parameters into an XML data map file's SQL statement, one for inline parameters, that is, using Parameterclass, as in the configuration above. One is the parameter mapping method, using Parametermap. In the case of inline mode, we are allowed to configure the property name, property type, and vacancy mode in the SQL statement.

When inline parameters are used, there are 3 types of parameters passed in, namely:

(1) Basic parameter types, many SQL statements only accept a parameter when querying, such as int, string, use #value# to refer to, this value is the keyword, immutable, the above example takes this way. You can also use #keyname# to refer to, keyName as the key name, note the case-sensitive.

(2) A dictionary type parameter that uses an object of type IDictionary as a parameter. You can usually use Hashtable. If the above configuration can be modified to

<select id= "Selectsysuserbyusername" parameterclass= "System.Collections.IDictionary" resultmap= "Sysuserresult" >

SELECT * from DEAN. Sysuser WHERE loginname= #LOGINNAME #

</select>

(3) An object type parameter, which can be a class or a hash table hashtable, when using a hash table, a key value can be a list type.

The calling code is as follows:

Protectedvoid button2_click (object sender, EventArgs e)

{

Conditional query

Try

{

String UserName = textbox1.text;//Get query parameter user name

Isqlmapper mapper = Mapper.instance (); Get Isqlmapper instances

ilist<ibatistest.domain.sysuser> plist = mapper. Queryforlist<ibatistest.domain.sysuser> ("Test3map.selectsysuserbyusername", UserName);//Call QueryForList method

if (plist! = null && plist. Count > 0)

{

Gridview1.datasource = plist;

Gridview1.databind ();

}

Label1.Text = "Conditional query succeeded";

}

catch (Exception ex)

{

Label1.Text = ex. Message;

}

}

If you pass in a dictionary type parameter, you only need to modify the parameters in the call to:

Hashtable hash = new Hashtable ();//Declaration Hash Table

Hash. ADD ("LOGINNAME", TextBox1.Text); Get query parameter user name

3. Dynamic Query

Ibatis.net provides the flexibility to provide queries mainly in support of dynamic queries, that is, you can dynamically generate SQL statements. Only grasp the dynamic query, can fully feel the Ibatis framework brought about by the convenience and efficiency. This is also an important reason for many software companies and developers to choose the framework.

This kind of query is often encountered in development, when the user does not enter the query criteria to query all records, if the user entered the query criteria will be queried according to the query criteria. For example, the condition query mentioned above will return all user information if the user name information is not entered. This time you need to use the dynamic query, based on whether the parameter value is empty, generate two different SQL statements. The XML data Map configuration information is:

<selectid= "SelectSysuserDynamic1" parameterclass= "System.Collections.IDictionary" resultmap= "Sysuserresult" >

<! [cdata[SELECT * from DEAN. Sysuser]]>

<dynamicprepend= "WHERE" >

<isnotemptyprepend= "and" property= "LOGINNAME" >

<! [Cdata[LOGINNAME = #LOGINNAME #]]>

</isNotEmpty>

</dynamic>

</select>

The dynamic element is used to differentiate the dynamics of a SQL statement, and dynamic is an option that can contain conditional elements of arbitrary data. The above configuration information generates two SQL statements based on whether the value of the input parameter loginname is empty. If the empty select * from DEAN. Sysuser, if not empty select * from DEAN. Sysuser WHERE LOGINNAME = #LOGINNAME #.

The calling program code is:

Protectedvoid button3_click (object sender, EventArgs e)

{

Dynamic Query 1

Try

{

Hashtable hash = newhashtable ();//Declaration Hash Table

Hash. ADD ("LOGINNAME", TextBox2.Text); Get query parameter user name

Isqlmapper mapper = Mapper.instance (); Get Isqlmapper instances

ilist<ibatistest.domain.sysuser> plist = mapper. Queryforlist<ibatistest.domain.sysuser> ("Test3map.selectsysuserdynamic1", hash);//Call QueryForList method

if (plist! = null && plist. Count > 0)

{

Gridview1.datasource = plist;

Gridview1.databind ();

}

Label1.Text = "Dynamic query 1 Success";

}

catch (Exception ex)

{

Label1.Text = ex. Message;

}

}

}

In Ibatis.net, the conditional elements of a dynamic query include the following: two-element conditional elements, unary conditional elements, and other conditional elements:

3.1 Two-element conditional element

Compare one property value to a static value or another property value, and if the condition is true, the element will be contained in the query SQL statement.

Attributes of a binary condition element:

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)

The binary condition elements are:

<isEqual>	Compares a property value to a static value or another property value for equality, and the query condition is valid if it is equal. Such as: <isequal prepend= "and" property= "status" comparevalue= "Y" > married = ' TRUE ' </isEqual>
<isNotEqual>	Compares a property value to a static value or another property value if it is not equal, and the query condition is valid if it is not equal.
<isGreaterThan>	Compares whether a property value is greater than a static value or another property value, if greater than the query condition is valid. Such as: <isgreaterthan prepend= "and" property= "Age" comparevalue= ">" Adolescent = ' FALSE ' </isGreaterThan>
<isGreaterEqual>	Compares whether the property value is greater than or equal to a static value or another property value, and if equal equals the query condition is valid.
<isLessThan>	Compares whether the property value is less than a static value or another property value, if less than the query condition is valid.
<isLessEqual>	Compares whether a property value is less than or equal to a static value or another property value. Such as: <isLessEqual prepend="AND" property="age" compareValue="18"> ADOLESCENT = ‘TRUE‘ </isLessEqual>

Binary conditional elements are used in the selection of numbers in the range, such as age, price, area, etc., can also be used in the date, string and other types of comparison. If only the user information for the specified ID of id<=10 is displayed, all user information is displayed if the input value is greater than 10. The XML data Map configuration information is:

<selectid= "SelectSysuserDynamic2" parameterclass= "System.Collections.IDictionary" resultmap= "Sysuserresult" >

<! [cdata[SELECT * from DEAN. Sysuser]]>

<dynamicprepend= "WHERE" >

<islessequalprepend= "and" property= "USERID" comparevalue= "ten" >

USERID = #USERID #

</isLessEqual>

</dynamic>

</select>

The calling code is:

Protectedvoid Button4_Click (object sender, EventArgs e)

{

Dynamic Query 2: Two-element conditional elements Query

Try

{

Hashtable hash = newhashtable ();//Declaration Hash Table

int ID = 0;

if (!string. Isnullorwhitespace (TextBox3.Text))

{

ID = Convert.ToInt32 (TextBox3.Text);

}

Hash. ADD ("USERID", ID); Get query parameters

Isqlmapper mapper = Mapper.instance (); Get Isqlmapper instances

ilist<ibatistest.domain.sysuser> plist = mapper. Queryforlist<ibatistest.domain.sysuser> ("Test3map.selectsysuserdynamic2", hash);//Call QueryForList method

if (plist! = null && plist. Count > 0)

{

Gridview1.datasource = plist;

Gridview1.databind ();

}

Else

{

Gridview1.datasource = null;

Gridview1.databind ();

}

Label1.Text = "Dynamic Query 2 Success";

}

catch (Exception ex)

{

Label1.Text = ex. Message;

}

}

3.2 Unary Conditional element

A unary condition element checks whether the state of a property conforms to a specific condition. That is, checks whether the property value satisfies the condition and the query condition is valid if satisfied.

The attribute of a unary conditional element, like a two-element conditional element, has the prepend and property properties, where property is a required attribute.

A unary conditional element is:

<isp Ropertyavailable>	Checks for the existence of this property.
<isnotpropertyavailable>	Check if no The attribute exists.
<isnull>	Check whether the property is null.
<isnotnull>	Check whether the property is not null.
<isempty>	Check whether the property is empty and the data type of the property is Col Lection, String when checking for null or NULL, that is, "" or size () < 1. such as: <isnotempty prepend= "and" property= "firstName"; First_name= #firstName # </ Isnotempty>
<isnotempty>	Check genus Sex is not empty, check the same way.

For example, the following configuration examples:

<selectid= "SELECTSYSUSERDYNAMIC3" resultmap= "Sysuserresult" parameterclass= "System.Collections.IDictionary" >

<! [cdata[SELECT * from DEAN. Sysuser]]>

<dynamicprepend= "WHERE" >

<ispropertyavailableproperty= "SEX" >

<isnotnullproperty= "SEX" prepend= "and" >

sex= #SEX #

</isNotNull>

</isPropertyAvailable>

<ispropertyavailableproperty= "STATUS" >

<isnotnullproperty= "STATUS" prepend= "and" >

status= #STATUS #

</isNotNull>

</isPropertyAvailable>

</dynamic>

</select>

First, determine if there is a SEX parameter in the set of parameters, and if not, do not execute the sex= #SEX # query condition, and then determine whether the parameter is NULL, NOT NULL to execute the query condition. The greatest benefit of the ispropertyavailable element is that if the input parameter set does not include the set parameters, the program does not error, skipping the element settings directly.

3.3 Other element conditions

Other element conditions have two elements, one for parameterpresent, which checks for the existence of a parameter object, and one for iterate, which iterates through the entire collection.

(1) parameterpresent

The Parameterpresent element property is only prepend an attribute that represents the part of an SQL statement that can be overwritten, added to the front of the statement, as an optional property.

<isParameterPresent>	Checks whether a parameter object exists, that is, if the parameter class is not NULL, the query condition is valid. Such as: <isParameterPresent prepend="AND"> EMPLOYEE_TYPE = #empType# </isParameterPresent>
<isNotParameterPresent>	Check if there are no parameter objects, such as: <isNotParameterPresent prepend="AND"> EMPLOYEE_TYPE = ‘DEFAULT‘ </isNotParameterPresent>

(2) Iterate: Iterates through the entire collection element, repeating the contents of the element body for the elements in the list collection.

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.

<iterate>

Iterates over an element of type list. Such as: <iterate prepend= "and" property= "Usernamelist" Open= "(" close= ")" conjunction= "OR" > Username= #UserNameList []# </iterate> Note: When using <iterate>, it is important to include square brackets [] After the list element name, square brackets [] to mark the object as list, in case the parser simply outputs the list to string.

Iterate element when generating SQL statements, the contents of the tag are generated in a loop, as the above example will produce idiom sentences: (username=xxx1 or Username=xxx2 or Username=xxx 3). This element is also often used to dynamically generate in query conditions, such as ID in (xx1,xx2,xx3,.....), which are generated by the element label (including parentheses).

For example, the following configuration examples:

<selectid= "SelectSysuserDynamic4" resultmap= "Sysuserresult" parameterclass= "System.Collections.IDictionary" >

<! [cdata[SELECT * from DEAN. Sysuser]]>

<dynamicprepend= "WHERE" >

<ispropertyavailableproperty= "SEX" >

<isnotnullproperty= "SEX" prepend= "and" >

sex= #SEX #

</isNotNull>

</isPropertyAvailable>

<isnotnullprepend= "and" property= "Useridlist" >

USERID in

<iterateproperty= "Useridlist" open= "(" close= ")" conjunction= "," >

#USERIDLIST []#

</iterate>

</isNotNull>

</dynamic>

</select>

The calling code is:

Protectedvoid Button6_click (object sender, EventArgs e)

{

Dynamic Query 2: Other element conditions, iterate

Try

{

Hashtable hash = newhashtable ();//Declaration Hash Table

string sex = "male";

Hash. ADD ("Sex", sex);

list<int> IDS = newlist<int> ();//Declaring list objects

Ids. ADD (1);

Ids. ADD (2);

Ids. ADD (3);

Hash. ADD ("Useridlist", IDS);

Isqlmapper mapper = Mapper.instance (); Get Isqlmapper instances

ilist<ibatistest.domain.sysuser> plist = mapper. Queryforlist<ibatistest.domain.sysuser> ("Test3map.selectsysuserdynamic4", hash);//Call QueryForList method

if (plist! = null && plist. Count > 0)

{

Gridview1.datasource = plist;

Gridview1.databind ();

}

Else

{

Gridview1.datasource = null;

Gridview1.databind ();

}

Label1.Text = "Dynamic query 4 Success";

}

catch (Exception ex)

{

Label1.Text = ex. Message;

}

}

The system generates in-query conditions dynamically based on the configuration information, and the resulting dynamically generated SQL statement is: SELECT * from DEAN. Sysuser and sex= ' male ' and USERID in (.

4. Multi-Table Query

The previous examples are querying records from one table, and getting the result is a single object. In fact, in program development, it is often necessary to combine multiple tables, and the returned results are complex objects. If you query user rights information, you need to correlate user tables and permission tables.

Add the System Permissions table Sysuserright to the database with the following script:

CREATE TABLE DEAN. Sysuserright

(

ID Number (10,0) is not NULL ENABLE,

USERID Number (10,0) is not NULL ENABLE,

Rightid Number (10,0) is not NULL ENABLE,

Constraint Pk_sysuserright primary KEY (ID)

);

Comment on column DEAN. Sysuserright.id is ' ID ';

Comment on column DEAN. Sysuserright. USERID is ' user ID ';

Comment on column DEAN. Sysuserright. Rightid is ' permission id ';

There are two ways to handle this multi-table query, one is to refer to a single-table query, to customize a new class based on the returned results, or to directly set the return parameter to the Hashtable table. Such as:

<selectid= "MultiTable1" resultclass= "Hashtable" >

SELECT A.*,b.rightid from DEAN. Sysuser A,dean. Sysuserright B WHERE A.userid=b.userid

</select>

Through the user ID (USERID) association user table (Sysuser) and the System permission table (sysuserright) to query the user information and corresponding permissions information, directly returned a Hashtable table, recorded the corresponding information.

The second approach is implemented using the complex properties of Ibatis, adding a property to the Sysuser class:

<summary>

Multi-table Query new permission Properties

</summary>

Privateint _rightid;

Publicint Rightid

{

get {return _rightid;}

set {_rightid = value;}

}

Modify the configuration file information, add a result mapping information in the Resultmaps section, the only number is Userrightresult, it inherits from the Test3map.sysuserresult result mapping, the additional configuration information is as follows:

<resultmapid= "Userrightresult" class= "Sysuser" extends= "Test3map.sysuserresult" >

<resultproperty= "<spansstyle=" color:blue; Rightid "column=" Userid=userid "select=" Test3map.selectsysuserright "/>

</resultMap>

Add the following information to the statements section:

<statementid= "Selectsysuserright" parameterclass= "int" resultclass= "int" >

SELECT Rightid from DEAN. Sysuserright WHERE userid= #USERID #

</statement>

<selectid= "MultiTable2" parameterclass= "int" resultmap= "Userrightresult" >

SELECT * from DEAN. Sysuser ORDER by USERID

</select>

The result of Resultmap in the XML configuration file uses "Select" for an iterative query, that is, to <result property= "Rightid" column= "Userid=userid" select= " Test3map.selectsysuserright "One or more of the entries specified in column/> As a parameter (Userid=userid), passing in and executing the specified SELECT statement selectsysuserright, And the query results are assigned to property= "Rightid", so as to realize the multi-table query.

In this example, a 1:1 relational query is implemented, and if it is a 1:n relational query, only the attributes added to the Sysuser class are modified to the IList type. The return class of the statements node "Selectsysuserright" is modified to resultclass= "Sysuserrightresult".

By Ibatis complex attributes, it is very convenient to implement multi-table query, but this method brings us convenience, but also brings two problems. First, creating a list that contains a large number of objects can consume a lot of memory. Second, this method causes the I/O problem of the database, the reason is the so-called "n+1" query phenomenon. Queries for master-slave tables (also known as parent-child tables) are particularly prone to n+1 query problems, and n+1 query problems are caused by attempts to load child records (right) of multiple parent records, such as user. When querying the parent record, only 1 statements are required, assuming that the N records are returned, then we need to execute the N statement to query the child records, causing the so-called "n+1 query".

Solving n+1 query problems can be implemented by lazy loading (loading), which breaks the loading process down into smaller processes. In a parent-child table query, queries for child tables often do not need to be loaded with the parent table, for example, the user management of the system, the user information list is displayed when a user Information page is opened, and the user's permission information needs to be loaded when a user is clicked. This is especially good for lazy loading, where you can query only one list at a time. When using lazy loading, it is also important to note that all methods and properties of the object used by the dynamic proxy must be of type virtual.

To implement lazy loading, simply add the lazyload= "true" attribute to the configuration file. By delaying loading, it can improve the efficiency of the query, but it does not really solve the database I/O problem, in the worst case, it has the same number of accesses to the database as non-lazy loading. How to really solve n+1 query problem? Ibatis provides a connection statement (join) method to completely avoid the appearance of n+1 queries.

Modify the configuration file information and add the following configuration in the Resultmaps section:

<resultmapid= "Sysuserrightresult" class= "Sysuserright" >

<resultproperty= "id" column= "id"/>

<resultproperty= "userid" column= "userid"/>

<resultproperty= "Rightid" column= "Rightid"/>

</resultMap>

<resultmapid= "Sysuserjoinresult" class= "Sysuserjoin" extends= "Test3map.sysuserresult" groupBy= "Userid" >

<resultproperty= "Rightlist" resultmapping= "Test3map.sysuserrightresult"/>

</resultMap>

This configuration uses the Relultmap resultmapping property, which is used if the property of a data class is not itself a primitive data type, but rather a scene of a complex data type. This time cannot be expressed with a simple result element, and must give him a complete resultmap. The value of resultmapping indicates that the Rightlist property is represented by the complex data type represented by the result mapping set Sysuserrightresult. Because the user and permission information is a one-to-many relationship, the groupby= "Userid" attribute is added to the result map of the primary table.

Note the definition of the Permission property Rightlist of the user class Sysuserjoin, which is a list of permission class Sysuserright. Rightlist is defined as follows:

Multi-table Query new permission list properties

Privateilist<sysuserright> _rightlist;

Publicilist <SysuserRight> Rightlist

{

get {return _rightlist;}

set {_rightlist = value;}

}

Add the following information to the statements section

<selectid= "MultiTable3" resultmap= "Sysuserjoinresult" >

Select a.*,b.* from DEAN. Sysuser A left JOIN DEAN. Sysuserright B on A.userid=b.userid </select>

Then through the program's call, there will be no n+1 query problem.

Ibatis.net (C #) database query