Go NHibernate Tour (3): Explore Query NHibernate Query Language (HQL)

The content of this section

• Query methods in the NHibernate
• NHibernate Query Language (HQL)
  • 1.from clause
  • 2.select clause
  • 3.where clause
  • 4.order BY clause
  • 5.group BY clause
• Example analysis
• Conclusion

In the previous section, we initially set up a nhibernate program, completed mapping the Customer table and read the data function, this section and the next section we discuss the query method in the NHibernate. I still remember the previous section of the finished things, one of the pictures many people replied that very classic, simple and clear! Or look at the picture. Summarize the last three important things: Create a database table-----write a persisted class-----write the mapping file, and then configure the use.

Query methods in the NHibernate

There are a lot of queries available in NHibernate, here are just 3 ways to go: NHibernate Query Language (hql,nhibernate queries Language), conditional query (criteria api,query by Example (QBE) is a special case for the criteria API), 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 the NHibernate query language (hql,nhibernate queries Language).

NHibernate Query Language (HQL)

NHibernate Querying language (hql,nhibernate query Language) is a unique object-oriented SQL query language based on NHibernate, which has inheritance, polymorphism, and correlation characteristics. The tables and columns in the database are actually mapped with objects and properties in OOP.

For example, this sentence: select C.firstname from Customer C

The customer is a database table, FirstName is a column, and for Hql:customer is an object, FirstName is the property of the Customer object. In contrast, SQL statements are flexible, but there is no compile-time syntax validation support.

This section describes the underlying syntax: A FROM clause, a SELECT clause, a WHERE clause, an ORDER BY clause, a GROUP BY clause, and a separate instance that can be run. As for correlation and connectivity, polymorphic (polymorphism) queries, subqueries are learned in a specific instance later. Note: The HQL keyword is not case sensitive.

Note: As space is limited, I just post the code for the data access layer, which is the method that can be called directly in the business logic layer. 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. This section, based on the previous section of the source code, creates a new QueryHql.cs class in the data access layer to write the HQL query method, creating a new QueryHqlFixture.cs class for testing at the test layer of the data access.

1.from clause

As the name implies, similar to SQL statements:

1. Simple usage: Returns all data in the table.

Public ilist<customer> from () {    //Returns all instances of the Customer class    return _session. CreateQuery ("from Customer")        . List<customer> ();}

2. Use aliases: Use as to give the table an alias, as can be omitted.

Public ilist<customer> Fromalias () {    //Returns all instances of the customer class, and customer assigns the alias customer    return _session. CreateQuery ("From customer as Customer")        . List<customer> ();}

3. Cartesian product: Multiple classes appear, or aliases are used to return a Cartesian product or a "cross" connection.

2.select clause

1. Simple usage: Returns the specified object and property in the result set.

Public ilist<int> Select () {    //returns all customer CustomerID return    _session. CreateQuery ("Select C.customerid from Customer C")        . List<int> ();}

2. Array: Returns multiple objects and/or multiple properties using an array of object[], or uses special elements functions, which are generally used in conjunction with GROUP by. Note that this is an array of object[], and we can define a collection of DTO objects to return, that is, to use type-safe. NET object.

Public ilist<object[]> SelectObject () {   return _session. CreateQuery ("Select C.firstname, Count (C.firstname) from the Customer C Group by C.firstname")        . List<object[]> ();}

3. Statistic function: Returns the result of the statistic function of the attribute with an array of object[], note that the variable of the statistic function can also be the collection count (elements (C.customerid)). Note that this is an array of object[], and we can define a collection of DTO objects to return.

Public ilist<object[]> aggregatefunction () {    return _session. CreateQuery ("Select AVG (C.customerid), sum (C.customerid), Count (c) from Customer C")        . List<object[]> ();}

4.DISTINCT usage: The distinct and all keywords have the same usage and semantics as SQL. Example: Get FirstName for different customer.

Public ilist<string> Distinct () {     return _session. CreateQuery ("SELECT distinct c.firstname from Customer C")        . List<string> ();}

3.where clause

The WHERE clause lets you narrow the list of instances that you want to return.

Public ilist<customer> Where () {    return _session. CreateQuery ("from Customer C where c.firstname= ' yjing '")        . List<customer> ();}

The WHERE clause allows the expression that appears to include most of the cases in SQL:

• Math operators: +,-, *,/
• True and false comparison operators: =, >=, <=, <>,! =, like
• Logical operators: And, OR, not
• String Join operator: | |
• SQL scalar functions: Upper (), Lower ()
• No prefix (): Indicates grouping
• In, between, is null
• Position parameter:?
• Named parameter:: Name,: start_date,: x1
• SQL text: ' foo ', 69, ' 1970-01-01 10:00:01.0 '
• enumeration value or constant: color.tabby

4.order BY clause

Sort by the properties of any returned class or component: ASC ascending, desc descending.

Public ilist<customer> () {    return _session. CreateQuery ("from Customer C order by C.firstname asc,c.lastname desc")        . List<customer> ();}

5.group BY clause

Grouped by the properties of any returned class or component.

Public ilist<object[]> Groupby () {    return _session. CreateQuery ("Select C.firstname, Count (C.firstname) from the Customer C Group by C.firstname")        . List<object[]> ();}

Example analysis

OK, the above basic query is really very simple, we still refer to examples, analysis of how we write HQL query it!

Example 1: Follow FirstName to inquire customers:

Public ilist<customer> Getcustomersbyfirstname (string firstname) {    //notation 1    //return _session. CreateQuery ("from Customer C where c.firstname= '" + Firstname + "'")    //    . List<customer> ();    Notation 2: Position-type parameter    //return _session. CreateQuery ("from Customer C where c.firstname=?")    //    . SetString (0, FirstName)    //    . List<customer> ();    Notation 3: Named parameter (recommended)    return _session. CreateQuery ("from Customer C where C.firstname=:fn")        . SetString ("FN", FirstName)        . List<customer> ();}

There are four ways to write HQL parameters:

• Syntax 1: May cause SQL injection, do not use.
• 2:ado.net style? parameters, NHibernate parameters are counted starting from 0.
• Notation 3: Named parameters are represented in the query string as name, at which point the IQuery interface binds the actual parameters to the named parameters.
• 4: Named parameter list, add some parameters to a collection list, such as can query whether the data in this collection list.

There are some advantages to using named parameters: named parameters do not depend on the order in which they appear in the query string; they can be used more than once in the same query; they are good readability. Therefore, it is recommended to use the named Parameter form when writing HQL parameters.

Test this method: Look at the database FirstName to "Yjinglee" the number of records is 1, and can judge the query out of the data FirstName property is not "Yjinglee".

[test]public void Getcustomerbyfirstnametest () {    Ilist<customer> customers = _ Queryhql.getcustomersbyfirstname ("Yjinglee");    Assert.AreEqual (1, customers. Count);    foreach (Var c in customers)    {        assert.areequal ("Yjinglee", C.firstname);}    }

Example 2: Get customers with a customer ID greater than CustomerID:

Public ilist<customer> Getcustomerswithcustomeridgreaterthan (int customerId) {    return _session. CreateQuery ("from Customer C where C.customerid >: CID")        . SetInt32 ("CID", CustomerId)        . List<customer> ();}

Conclusion

In this article, we understand the nhibernate one of the query language hql, these instances I strive to write out to run, we download the source to see the effect, some data need to be modified according to personal circumstances. For example, query criteria results. The next section continues to introduce another query language! Note that this article has some return ilist<object[]> type, it is not possible to use this type in the actual project, we need to use an object, that is, a DTO transformation returns the ilist<classdto> type.

Go NHibernate Tour (3): Explore Query NHibernate Query Language (HQL)