Go NHibernate Tour (4): Search query for conditional queries (criteria query)

The content of this section

• Query methods in the NHibernate
• Conditional queries (criteria query)
  • 1. Create a Icriteria instance
  • 2. Result set Limitations
  • 3. Sorting result Sets
  • 4. Some notes
• Query by Example
• Example analysis
• Conclusion

In the previous section, we introduced one of the NHibernate query languages: The NHibernate Query Language (hql,nhibernate queries Language), which describes the conditional query (criteria API).

Query methods in the NHibernate

There are three types of queries available in NHibernate: NHibernate Query Language (hql,nhibernate queries Language), conditional query (criteria Api,criteria query), (based on sample query (Qbe,query by Example) is a special case of conditional queries), native SQL (Literal sql,t-sql, PL/sql). Each person has different preferences and strengths, can choose to use one or several of them according to their own circumstances. In this section we introduce conditional queries.

Conditional queries (criteria query)

HQL is extremely powerful, but some people want to be able to dynamically create queries using an object-oriented API instead of. NET code embedded in a string. In NHibernate, an intuitive, extensible criteria API is provided. When we type in the query, we provide a compile-time syntax check, VS provides powerful smart hints. If you don't feel comfortable with HQL's grammar, it might be easier to use this method. This API is also more extensible than HQL.

Typical usage: Create an Icriteria instance object from the ISession interface, set one or more expressions on this Icriteria instance object, require the Icriteria interface to return the desired list, or return an object from the database based on an expression.

Note: As space is limited, I just post the code for the data access layer here. The code to test these methods is not posted, so you can download the source code for this series and take a closer look at the codes that test these methods. These examples I strive to write to be able to run, we download the source code to see the effect, some data need to be in the personal database data changes. For example, query criteria and results. This section, based on the previous section of the source code, creates a new QueryCriteriaAPI.cs class in the data access layer for writing conditional query methods, and creates a new QueryCriteriaAPIFixture.cs class for testing at the test layer of data access.

1. Create a Icriteria instance

The Createcriteria method using the ISession interface creates a query instance of the Nhibernate.icriteria interface for a particular persisted class, or ISession is the factory that is used to create the criteria instance.

Public ilist<customer> Createcriteria () {    Icriteria crit = _session. Createcriteria (typeof (Customer));    Crit. Setmaxresults (+);    Ilist<customer> customers = Crit. List<customer> ();    return customers;}

For example, the above example returns a collection of customer objects and sets the maximum number of collections to 50.

2. Result set Limitations

Using the Add method provided by the Icriteria interface adds a constraint expression in the restrictions class to limit the effect of some result sets.

Public ilist<customer> Narrowing () {    ilist<customer> customers = _session. Createcriteria (typeof (Customer))        . ADD (Restrictions.like ("Firstname", "yjing%"))        . ADD (Restrictions.between ("Lastname", "A%", "Y%"))        . List<customer> ();    return customers;}

3. Sorting result Sets

Using Icriteria.order to sort the result set, the second parameter, True, represents asc,false for Desc. For example, the following example queries the customer object by FirstName Descending, LastName Ascending.

Public ilist<customer> Order () {    return _session. Createcriteria (typeof (Customer))        . ADD (Restrictions.like ("Firstname", "Y%"))        . AddOrder (New NHibernate.Criterion.Order ("Firstname", false))        . AddOrder (New NHibernate.Criterion.Order ("Lastname", True))        . List<customer> ();}

4. Some notes

Conditional queries also support associative queries, dynamic associative crawls (described in the introduction of one-to-many, many-to-many relationships), projection, aggregation, and grouping, offline (detached) queries and subqueries are new additions to version 2.0, and are introduced later in the relevant knowledge. You can also refer to NHibernate Reference document 13 for your own reference.

Query by Example

According to the sample query (Qbe,query by Example) is a special case of a conditional query, the NHibernate.Criterion.Example class creates query conditions based on the instance you specify. Typical usage: Create a example instance, set a value on the example instance, and return its collection of objects based on example and Settings nhibernate.

For example, in the following example, the records in the database are queried by the specified customer:

Public ilist<customer> Query () {    customer customersample = new Customer () {Firstname = "yjing", Lastname = "Lee" };    Return _session. Createcriteria (typeof (Customer))        . ADD (Example.create (customersample))        . List<customer> ();}

You can adjust the example yourself to make it more practical:

Public ilist<customer> Usequerybyexample_getcustomer (Customer customersample) {    Example Example = Example.create (customersample)        . IgnoreCase ()        . Enablelike ()        . Setescapecharacter (' & ');    Return _session. Createcriteria (typeof (Customer))       . ADD (example)       . List<customer> ();}

Example analysis

Example 1: Use Criteriaapi to query customers by FirstName and LastName.

Public ilist<customer> Getcustomersbyfirstnameandlastname (string firstname, String lastname) {    return _ Session. Createcriteria (typeof (Customer))        . ADD (Restrictions.eq ("Firstname", Firstname))        . ADD (Restrictions.eq ("Lastname", Lastname))        . List<customer> ();}

Test: Call the Getcustomersbyfirstnameandlastname method, query FirstName as "Yjing", LastName for "Lee" customers, determine whether the query result number is 1. (Note: This record is met in the database)

[test]public void Getcustomerbyfirstnameandlastnametest () {    Ilist<customer> customers =         _ Querycriteriaapi.getcustomersbyfirstnameandlastname ("yjing", "Lee");    Assert.AreEqual (1, customers. Count);}

Example 2: Use Criteriaapi to get customers with a customer ID greater than CustomerID.

Public ilist<customer> Getcutomerswithidgreaterthan (int customerId) {    return _session. Createcriteria (typeof (Customer))        . ADD (restrictions.gt ("CustomerId", CustomerId))        . List<customer> ();}

Conclusion

Well, through the introduction of 2 articles, nhibernate in the query syntax has a general understanding of the NHibernate in the two most important query methods, there is a native SQL query, the content is not much, please refer to the NHibernate official documents it. Practice a lot! The following section describes the operation of the object.

Go NHibernate Tour (4): Search query for conditional queries (criteria query)