Nhib1_-- criteria condition Query

Conditional Query

The nhib.pdf. icriteria interface indicates a query of a specific persistent class. Isession is the factory of the icriteria instance.

 

Here we use the northwind database as the example Database

Example data table: Employees

Currently, only some fields in the employee table are used.

The persistence class is as follows:

Public class employees

{

Public Virtual int employeeid {Get; set ;}

Public Virtual string lastname {Get; set ;}

Public Virtual string firstname {Get; set ;}

Public Virtual datetime birthdate {Get; set ;}

Public Virtual string address {Get; set ;}

Public Virtual string city {Get; set ;}

Public Virtual string postalcode {Get; set ;}

}

 

The ing file is as follows:

<? XML version = "1.0" encoding = "UTF-8"?>

<Hibernate-mapping xmlns = "urn: nhibernate-mapping-2.2" assembly = "Domain" namespace = "domain. Entities">

<Class name = "employees" table = "employees">

<ID name = "employeeid" column = "employeeid">

<Generator class = "Identity"> </generator>

</ID>

<Property name = "lastname" column = "lastname" type = "string"> </property>

<Property name = "firstname" column = "firstname" type = "string"> </property>

<Property name = "birthdate" column = "birthdate" type = "datetime"> </property>

<Property name = "Address" column = "Address" type = "string"> </property>

<Property name = "city" column = "city" type = "string"> </property>

<Property name = "postalcode" column = "postalcode" type = "string"> </property>

</Class>

</Hibernate-mapping>

 

Start

(1) return all instances (return all employees)

All instances returned here are all attributes (fields)

Icriteria CRT = _ session. createcriteria (typeof (employees ));

Return CRT. List <employees> ();

The isession condition query instance has four constructor methods.

(2) return some instances (return two employees)

Icriteria CRT = _ session. createcriteria (typeof (employees ));

CRT. setmaxresults (2 );

Return CRT. List <employees> ();

(3) constraints for condition Query

(1) Expression

Icriteria CRT = _ session. createcriteria (typeof (employees ));

CRT. Add (expression. eq ("city", "London "));

Return CRT. List <employees> ();

The employee's city is in London. The namespace of expression is nhib.pdf. criterion.

The expression class defines the factory method for obtaining some built-in icriterion types.

(2) Restrictions

Icriteria CRT = _ session. createcriteria (typeof (employees ));

CRT. Add (restrictions. eq ("city", "London "));

Return CRT. List <employees> ();

The employee's city is in London. The restrictions namespace is nhib.pdf. criterion.

(3) query by instance

Employees EE = new employees {city = "London", birthdate = convert. todatetime ("1955-03-04 00:00:00. 000 ")};

Icriteria CRT = _ session. createcriteria (typeof (employees ));

CRT. Add (example. Create (EE ));

Return CRT. List <employees> ();

 

Query London's birthday at that time. (Why should I add a birthday here? Because my persistent class has this attribute, if this value is not given in the instance query, there will be an exception in the date out of bounds. In the following example, we will handle this situation.) This is an equal implementation of the limitation. Below is a similar example:

Employees EE = new employees {firstname = ""};

Example exp = example. Create (EE)

. Enablelike (matchmode. Start)

. Excludeproperty ("birthdate ")

. Ignorecase ();

Icriteria CRT = _ session. createcriteria (typeof (employees ));

CRT. Add (exp );

Return CRT. List <employees> ();

 

In this example, specify a name similar to a to view the instance Exp:

· Enablelike

· Similar comparison matching mode matchmode. Start, which must start with a match. This should be similar to a %

· Excludeproperty, which is used for troubleshooting. In the previous example, the date is not given, so an exception occurs, and this method ruled out this exception (in fact, excluding non-Comparative attributes (fields )).

· Case insensitive

(4) sorting

Icriteria CRT = _ session. createcriteria (typeof (employees ));

CRT. addorder (New nhib.pdf. criterion. Order ("firstname", true ));

Return CRT. List <employees> ();

Sorting field: name, ascending (true)

(5) Aggregation

(1) Number of queried persons

Icriteria CRT = _ session. createcriteria (typeof (employees ));

CRT. setprojection (projections. rowcount ());

Return CRT. List ();

Nhibrules. expression. Projections is the instance factory of iprojection. Call setprojection () to apply projection to a query.

(2) AVG

From the beginning to the following example, return to the sample data table using products

Icriteria CRT = _ session. createcriteria (typeof (products ));

CRT. setprojection (projections. projectionlist ()

. Add (projections. AVG ("price "))

);

Return CRT. List ();

Add a projection aggregation method by using the projection list.

The average price of the product is obtained here, and there is no restriction here. In the following example, the average price of the product with the product category of 2 is obtained:

Icriteria CRT = _ session. createcriteria (typeof (products ));

CRT. setprojection (projections. projectionlist ()

. Add (projections. AVG ("price ")))

. Add (expression. eq ("categoryid", 2 ));

Return CRT. List ();

(3) max (maximum price)

Icriteria CRT = _ session. createcriteria (typeof (products ));

CRT. setprojection (projections. projectionlist ()

. Add (projections. Max ("price ")));

Return CRT. List ();

(4) min (lowest price)

Icriteria CRT = _ session. createcriteria (typeof (products ));

CRT. setprojection (projections. projectionlist ()

. Add (projections. Min ("price ")));

Return CRT. List ();

(5) sum (and)

Icriteria CRT = _ session. createcriteria (typeof (products ));

CRT. setprojection (projections. projectionlist ()

. Add (projections. sum ("price ")));

Return CRT. List ();

(6) Group

Icriteria CRT = _ session. createcriteria (typeof (products ));

CRT. setprojection (projections. projectionlist ()

. Add (projections. groupproperty ("categoryid ")));

Return CRT. List <int> ();

This group only returns an attribute, so you can use the int generic type. The following example returns the group and the number of groups.

Icriteria CRT = _ session. createcriteria (typeof (products ));

CRT. setprojection (projections. projectionlist ()

. Add (projections. groupproperty ("categoryid "))

. Add (projections. rowcount ()));

Return CRT. List ();

· The list here is system. Collections. ilist and is of the object [] type.

Conditional Query

The nhib.pdf. icriteria interface indicates a query of a specific persistent class. Isession is the factory of the icriteria instance.

 

Here we use the northwind database as the example Database

Example data table: Employees

Currently, only some fields in the employee table are used.

The persistence class is as follows:

Public class employees

{

Public Virtual int employeeid {Get; set ;}

Public Virtual string lastname {Get; set ;}

Public Virtual string firstname {Get; set ;}

Public Virtual datetime birthdate {Get; set ;}

Public Virtual string address {Get; set ;}

Public Virtual string city {Get; set ;}

Public Virtual string postalcode {Get; set ;}

}

 

The ing file is as follows:

<? XML version = "1.0" encoding = "UTF-8"?>

<Hibernate-mapping xmlns = "urn: nhibernate-mapping-2.2" assembly = "Domain" namespace = "domain. Entities">

<Class name = "employees" table = "employees">

<ID name = "employeeid" column = "employeeid">

<Generator class = "Identity"> </generator>

</ID>

<Property name = "lastname" column = "lastname" type = "string"> </property>

<Property name = "firstname" column = "firstname" type = "string"> </property>

<Property name = "birthdate" column = "birthdate" type = "datetime"> </property>

<Property name = "Address" column = "Address" type = "string"> </property>

<Property name = "city" column = "city" type = "string"> </property>

<Property name = "postalcode" column = "postalcode" type = "string"> </property>

</Class>

</Hibernate-mapping>

 

Start

(1) return all instances (return all employees)

All instances returned here are all attributes (fields)

Icriteria CRT = _ session. createcriteria (typeof (employees ));

Return CRT. List <employees> ();

The isession condition query instance has four constructor methods.

(2) return some instances (return two employees)

Icriteria CRT = _ session. createcriteria (typeof (employees ));

CRT. setmaxresults (2 );

Return CRT. List <employees> ();

(3) constraints for condition Query

(1) Expression

Icriteria CRT = _ session. createcriteria (typeof (employees ));

CRT. Add (expression. eq ("city", "London "));

Return CRT. List <employees> ();

The employee's city is in London. The namespace of expression is nhib.pdf. criterion.

The expression class defines the factory method for obtaining some built-in icriterion types.

(2) Restrictions

Icriteria CRT = _ session. createcriteria (typeof (employees ));

CRT. Add (restrictions. eq ("city", "London "));

Return CRT. List <employees> ();

The employee's city is in London. The restrictions namespace is nhib.pdf. criterion.

(3) query by instance

Employees EE = new employees {city = "London", birthdate = convert. todatetime ("1955-03-04 00:00:00. 000 ")};

Icriteria CRT = _ session. createcriteria (typeof (employees ));

CRT. Add (example. Create (EE ));

Return CRT. List <employees> ();

 

Query London's birthday at that time. (Why should I add a birthday here? Because my persistent class has this attribute, if this value is not given in the instance query, there will be an exception in the date out of bounds. In the following example, we will handle this situation.) This is an equal implementation of the limitation. Below is a similar example:

Employees EE = new employees {firstname = ""};

Example exp = example. Create (EE)

. Enablelike (matchmode. Start)

. Excludeproperty ("birthdate ")

. Ignorecase ();

Icriteria CRT = _ session. createcriteria (typeof (employees ));

CRT. Add (exp );

Return CRT. List <employees> ();

 

In this example, specify a name similar to a to view the instance Exp:

· Enablelike

· Similar comparison matching mode matchmode. Start, which must start with a match. This should be similar to a %

· Excludeproperty, which is used for troubleshooting. In the previous example, the date is not given, so an exception occurs, and this method ruled out this exception (in fact, excluding non-Comparative attributes (fields )).

· Case insensitive

(4) sorting

Icriteria CRT = _ session. createcriteria (typeof (employees ));

CRT. addorder (New nhib.pdf. criterion. Order ("firstname", true ));

Return CRT. List <employees> ();

Sorting field: name, ascending (true)

(5) Aggregation

(1) Number of queried persons

Icriteria CRT = _ session. createcriteria (typeof (employees ));

CRT. setprojection (projections. rowcount ());

Return CRT. List ();

Nhibrules. expression. Projections is the instance factory of iprojection. Call setprojection () to apply projection to a query.

(2) AVG

From the beginning to the following example, return to the sample data table using products

Icriteria CRT = _ session. createcriteria (typeof (products ));

CRT. setprojection (projections. projectionlist ()

. Add (projections. AVG ("price "))

);

Return CRT. List ();

Add a projection aggregation method by using the projection list.

The average price of the product is obtained here, and there is no restriction here. In the following example, the average price of the product with the product category of 2 is obtained:

Icriteria CRT = _ session. createcriteria (typeof (products ));

CRT. setprojection (projections. projectionlist ()

. Add (projections. AVG ("price ")))

. Add (expression. eq ("categoryid", 2 ));

Return CRT. List ();

(3) max (maximum price)

Icriteria CRT = _ session. createcriteria (typeof (products ));

CRT. setprojection (projections. projectionlist ()

. Add (projections. Max ("price ")));

Return CRT. List ();

(4) min (lowest price)

Icriteria CRT = _ session. createcriteria (typeof (products ));

CRT. setprojection (projections. projectionlist ()

. Add (projections. Min ("price ")));

Return CRT. List ();

(5) sum (and)

Icriteria CRT = _ session. createcriteria (typeof (products ));

CRT. setprojection (projections. projectionlist ()

. Add (projections. sum ("price ")));

Return CRT. List ();

(6) Group

Icriteria CRT = _ session. createcriteria (typeof (products ));

CRT. setprojection (projections. projectionlist ()

. Add (projections. groupproperty ("categoryid ")));

Return CRT. List <int> ();

This group only returns an attribute, so you can use the int generic type. The following example returns the group and the number of groups.

Icriteria CRT = _ session. createcriteria (typeof (products ));

CRT. setprojection (projections. projectionlist ()

. Add (projections. groupproperty ("categoryid "))

. Add (projections. rowcount ()));

Return CRT. List ();

· The list here is system. Collections. ilist and is of the object [] type.