Nhib.pdf journey (3): Exploring and querying the nhib.pdf Query Language (hql)

Content of this section

• The query method in nhib.pdf
• Hql)
  • 1. From clause
  • 2. Select clause
  • 3. Where clause
  • 4. Order by clause
  • 5. Group by clause
• Instance analysis
• Conclusion

In the previous section, we initially set up a Nhibernate program to map the customer table and read data. This section and the next section will be discussed in
The query method in nhib.pdf. Before that, I would like to recall what was completed in the previous section. one of the pictures is a classic and simple and clear one! Still look at the graph. Summary of section 3 above
Important: Create a database table ----- write persistence class ----- write a ing file, and then configure and use it.

The query method in nhib.pdf

Three query methods are available in Nhibernate: hql, nhib1_query
Language), condition query (criteria API, query by example (QBE) is criteria
API), native SQL (literal SQL, T-SQL, PL/SQL ). Each person has different preferences and specialties. You can choose one or more of them based on your own situation. In this section, we will introduce the nhibbench Query Language (hql, nhibbench
Query Language ).

Hql)

Hql, nhib1_query
Language) is an object-oriented SQL query language unique to nhib.pdf. It has inheritance, polymorphism, association, and other features. The object and attribute in OOP are used to map tables and columns in the database.

For example, select C. firstname from customer C

The customer is a database table, and the firstname is a column. For hql: customer is an object, and the firstname is the attribute of the customer object. In contrast, SQL statements are very flexible, but they do not support syntax verification during compilation.

This section introduces the basic syntax: From clause, select clause, where clause, order by clause, Group
By clause and list the instances that can be run separately. For Association and connection and polymorphism queries, subqueries will be learned in specific instances in the future. Note: hql is case insensitive.

Note: Due to the limited space, I only paste the data access layer code here, which is a method that can be directly called at the business logic layer. The code for testing these methods has not been posted. You can download
The source code takes a closer look at the code to test these methods. In this section, we create a new queryhql. CS class in the data access layer based on the source code of the previous section to compile the hql query method.
In the test layer, create a new queryhqlfixture. CS class for testing.

1. From clause

Similar to SQL statements, as the name implies:

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

Public ilist <customer> from ()
{
// Return all instances of the customer class
Return _ session. createquery ("from customer ")
. List <customer> ();
}

2. Use an alias: use as to assign an alias to a table. As can be omitted.

Public ilist <customer> fromalias ()
{
// Return all instances of the customer class. The customer gives the alias "customer ".
Return _ session. createquery ("from customer as customer ")
. List <customer> ();
}

3. Cartesian Product: Multiple classes or aliases are used to return cartesian products or cross join.

2. Select clause

1. Simple usage: return the specified object and attribute in the result set.

Public ilist <int> select ()
{
// Return the customerid of all customers
Return _ session. createquery ("select C. customerid from customer C ")
. List <int> ();
}

2. array: Use the array of object [] to return multiple objects and/or attributes, or use the special elements function. Note that it should be used in combination with group.

public IList<object[]> SelectObject()
{
return _session.CreateQuery("select c.Firstname, count(c.Firstname) from Customer c group by c.Firstname")
        .List<object[]>();
}

Or use a type-safe. Net object, which will be described later in the instance.

3. statistical function: return the result of the statistical function of the attribute using the array of object []. Note that the variable of the statistical function can also be the count (elements (C. customerid) set)
)

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 usage and semantics of distinct and all keywords are the same as those of SQL. Instance: Get the firstname of different customers.

public IList<string> Distinct()
{
return _session.CreateQuery("select distinct c.Firstname from Customer c")
        .List<string>();
}

3. Where clause

The where clause allows you to narrow down the list of instances to be returned.

public IList<Customer> Where()
{
return _session.CreateQuery("select from Customer c where c.Firstname='YJing'")
        .List<Customer>();
}

The expressions allowed by the WHERE clause include the following in most cases in SQL:

• Mathematical operators: + ,-,*,/
• True and false comparison operators: =, >=, <=, <> ,! =, Like
• Logical operators: And, or, not
• String connection OPERATOR: |
• SQL scalar functions: Upper (), lower ()
• () Without a prefix: indicates a group.
• In, between, is null
• Location Parameter :?
• Name parameter: name,: start_date,: x1
• SQL text: 'foo', 69, '2017-01-01 10:00:01. 0'
• Enumeration value or constant: color. Tabby

4. Order by clause

Sort by the attributes of any returned class or component: ASC in ascending order and desc in descending order.

public IList<Customer> Orderby()
{
return _session.CreateQuery("select from Customer c order by c.Firstname asc,c.Lastname desc")
        .List<Customer>();
}

5. Group by clause

Group by the attributes of any returned class or component.

public IList<object[]> Groupby()
{
return _session.CreateQuery("select c.Firstname, count(c.Firstname) from Customer c group by c.Firstname")
        .List<object[]>();
}

Instance analysis

Okay. The above basic query is indeed very simple. Let's take a look at the example and analyze how we can write hql queries!

Instance 1: Query customers by firstname:

Public ilist <customer> getcustomersbyfirstname (string firstname)
{
// Write 1
// Return _ session. createquery ("select from customer C where c. firstname = '" + firstname + "'")
//. List <customer> ();

// Statement 2: Location Parameter
// Return _ session. createquery ("select from customer C where c. firstname =? ")
//. Setstring (0, firstname)
//. List <customer> ();

// Statement 3: naming parameters (recommended)
Return _ session. createquery ("select from customer C where c. firstname =: FN ")
. Setstring ("FN", firstname)
. List <customer> ();
}

There are four methods to write hql parameters:

• Statement 1: it may cause SQL injection. Do not use it.
• Statement 2: Ado. Net style? Parameter. The Nhibernate parameter starts from 0.
• Statement 3: The name parameter is represented in the query string in the form of name. In this case, the IQUERY interface binds the actual parameter to the name parameter.
• Syntax 4: Name the parameter list. Add some parameters to a set list. For example, you can query whether data is in this set list.

Naming parameters have some advantages: naming parameters do not depend on the order they appear in the query string; they can be used multiple times in the same query; they are readable. Therefore, we recommend naming parameters when writing hql parameters.

Test this method: Check whether the number of records whose firstname is "yjinglee" in the database is one, and check whether the queried Data's firstname attribute is "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);
    }
}

Instance 2: obtain a customer whose customer ID is greater than customerid:

public IList<Customer> GetCustomersWithCustomerIdGreaterThan(int customerId)
{
return _session.CreateQuery("select from Customer c where c.CustomerId > :cid")
        .SetInt32("cid", customerId)
        .List<Customer>();
}

Conclusion

In this article, we learned about hql, a query language in nhib.pdf. I tried to write these instances and run them. You can download the source code to see the results, some data needs to be modified according to personal circumstances. For example, query condition results. The next section introduces another query language!

This series of links: nhibana travel series Article navigation

Nhib1_q &

• Welcome to the Chinese community of nhib.pdf to discuss nhib.pdf knowledge!
• Please download the source code of this series from the Chinese community of nhib.pdf.

Continue to share nhib.pdf next time!

Author: yjinglee's blog)
Source: http://lyj.cnblogs.com
The copyright of this article is shared by the author and the blog Park. You are welcome to repost this article. However, you must retain this statement without the author's consent and provide a clear link to the original article on the article page. Otherwise, you will be held legally liable.

Tag: nhib.pdf