[NHibernate] HQL Query

Directory

Write in front

Documentation and series articles

Several ways to query

HQL Query

An example

Summarize

Write in front

The previous article introduced the basic configuration of NHibernate in the project, including the setting of the database connection string, the configuration of the mapping file, and the place to be aware, this article describes the NHibernate query method.

Documentation and series articles

[Nhibernate] Architecture

[NHibernate] Isessionfactory Configuration

[NHibernate] Persistence class (persistent Classes)

[NHibernate] O/R Mapping Basics

[NHibernate] Collection Class (collections) mappings

[NHibernate] Association mappings

[NHibernate] Parent/child

[NHibernate] Cache (nhibernate.caches)

[NHibernate] NHibernate.Tool.hbm2net

[NHibernate] Nullables

[NHibernate] NHibernate How to map image fields in SQL Server

[NHibernate] Basic configuration and testing

Several ways to query

The following three types of queries are common in NHibernate:

1. NHibernate the Query Language (hql,nhibernate query Language).
2. Conditional queries (criteria Api,query by Example (QBE) are a special case of the criteria API).
3. Native SQL (Literal, PL, sql,t-sql).

HQL Query

This article focuses on HQL query methods.

HQL (Hibernate query Language) query provides richer and more flexible query features, so Hibernate will HQL query to the official recommended standard Query method, HQL query in the context of all the functions of the query, Provides query methods similar to standard SQL statements, and also provides more object-oriented encapsulation.

The complete HQL statement form is as follows:

Select/update/delete ... from ... to ... ... where ... ..... .... ... ...... ... asc/desc.-------the.

Where Update/delete is the newly added feature in Hibernate3, the HQL query is very similar to a standard SQL query. (from Baidu Encyclopedia)

Note: HQL is an object-oriented query language.

How do you understand this? Unlike SQL, a query in HQL is a property of an object. For example

1         /// <summary>2         ///Fuzzy query based on the customer's name3         /// </summary>4         /// <param name= "StrName" >Query Criteria</param>5         /// <returns>customer information that meets the criteria</returns>6          PublicIlist<customer> Searchbyname (stringstrName)7         {8Nhibernatehelper Nhibernatehelper =NewNhibernatehelper ();9ISession session =nhibernatehelper.getsession ();Ten             //the From is followed by the persisted class Customer instead of the data table name Tb_customer One             returnSession. CreateQuery ("From customer as customer where customer. CustomerName like '%"+ StrName +"% '"). List<customer>(); A}

followed by the name of the persisted class, not the table name. Otherwise, the following exception will appear:

also supports SELECT, which returns a generic collection of ilist<object[]> Object arrays when querying multiple properties.

1         /// <summary>2         ///Fuzzy query based on the customer's name3         /// </summary>4         /// <param name= "StrName" >Query Criteria</param>5         /// <returns>customer information that meets the criteria</returns>6          Publicilist<Object[]> Searchbyname (stringstrName)7         {8Nhibernatehelper Nhibernatehelper =NewNhibernatehelper ();9ISession session =nhibernatehelper.getsession ();Ten             //the From is followed by the persisted class Customer instead of the data table name Tb_customer One             returnSession. CreateQuery ("Select customer. Customerid,customer. Customername,customer. CustomerAddress from customer as customer where customer. CustomerName like '%"+ StrName +"% '"). list<Object[]>(); A}

When querying a single property, such as querying the IDs of all customers, returns ilist<guid>

1         /// <summary>2         ///get the ID of all customers3         /// </summary>4         /// <returns></returns>5          PublicIlist<guid>Getallcustomerid ()6         {7Nhibernatehelper Nhibernatehelper =NewNhibernatehelper ();8ISession session =nhibernatehelper.getsession ();9             returnSession. CreateQuery ("Select customer. CustomerID from customer as customer"). List<guid>();Ten}

Another order by, the use of group BY,DISTINCT is similar to SQL, no longer give an example.
Note: In HQL, it is also case insensitive, which is case insensitive.

An example

The WHERE conditional clause also supports named and positional parameters in most cases, except for SQL.
Query customer information by user name

1         /// <summary>2         ///query Customer information by name3         /// </summary>4         /// <param name= "StrName" ></param>5         /// <returns></returns>6          PublicIlist<customer> Getcustomerbyname (stringstrName)7         {8Nhibernatehelper Nhibernatehelper =NewNhibernatehelper ();9ISession session =nhibernatehelper.getsession ();Ten             //Way One One             //return session. CreateQuery ("from Customer C where c.customername= '" + strName + "'"). List<customer> (); A             //mode two: position type parameter -             //return session. CreateQuery ("from Customer C where c.customername=?") -             //    . SetString (0, StrName) the             //     . List<customer> (); -             //notation 3: Named parameters (recommended) -             returnSession. CreateQuery ("From Customer C where C.CUSTOMERNAME=:CN") -. SetString ("cn", StrName) +. List<customer>(); -}

The above code lists the common three styles of HQL statement notation.
Method One: Similar to the General query in ADO, this kind of writing may cause SQL injection, not recommended.

Way two: ADO-style? parameters, NHibernate parameters are counted starting from 0.

Method Three: Named parameters are represented in the query string as name, at which point the IQuery interface binds the actual parameters to the named parameters. (similar to the parameterized query in ADO, recommend this query method).

We'll monitor the way. Three generated SQL statements:

We found that in the generated SQL, the query condition was:

customer0_. CustomerName=@p0', N'@p0nvarchar(4000) ' , @p0 =n 'wolfy'

@p0 parameter, and then executes the stored procedure, assigning the argument Wolfy string to the parameter @p0.

Summarize

HQL queries are object-oriented and should be noted when using queries that are persisted class objects, rather than data table names, from behind. Other places are similar to SQL queries, such as order by, group by, usage of statistical functions, and so on.

When using HQL queries, it is recommended to use named parameters for querying.

Reference articles

Http://www.cnblogs.com/lyj/archive/2008/10/15/1312089.html

[NHibernate] HQL Query