NHibernate application five: HQL Daquan

First, the basic content

1. FROM clause

A, simple usage:

 Public Ilist<customerentity> Getcustomerfrom () {        　// Returns an instance of all the customer classes      return _session. CreateQuery ("fromcustomerentity").　　List<customerentity>(); }　　

B, aliases:

 Public Ilist<customerentity> Fromalias ()   {        // Returns all instances of the Customer class, customer Assigned the alias customer        return _session. CreateQuery ("fromcustomerentity as Customer"). List<customerentity>();   }

Note that as is optional, that is to say, the above code can be written as

 Public Ilist<customerentity> Fromalias ()   {        // Returns all instances of the Customer class, customer Assigned the alias customer        return _session. CreateQuery ("fromcustomerentity customer"). List<customerentity>();   }

C, Cartesian product

 Public Ilist<object[]> Getfromalias ()   {        return _session. CreateQuery ("fromorderproduct,orderform"). List<object[]>();   }

Note: This is actually a connection query using cross join (Cartesian product), which is poor performance and is not recommended for use.

2. SELECT clause

A, simple usage: Returns the specified object and property in the result set

Public ilist<string> Getcustomerall () {     //returns all customer's CustomerID     return _session. CreateQuery ("Select C.customerid from customerentity C").　　List<string> (); }

B, array: Returns multiple objects or attributes using an array of object[]

Public ilist<object[]> Getcustomerobject () {     return _session. CreateQuery ("Select C.customerid C. CustomerName from customerentity C").　　List<object[]> (); }

CStatistical functions: WithObject[]count (Elements (C.customerid))

     Public ilist<Object[]> aggregatefunction ()    {        REturn _ Session. CreateQuery ("Select AVG (c.customerid), sum (C.customerid), Count (c) from customerentity C" ). list<Object[]>();    }

DDistinctUsage:DistinctAndAllUsage and semantics of keywords andsql same.

Public ilist<string> Distinct ()
{
Return _session. CreateQuery ("SELECT DISTINCT C. CustomerName from customerentity C"). List<string> ();
}

3. WHERE clause

A, mathematical operator +,-, *,/;　　　　　　　　

 Public Ilist<customerentity> Getcustomerbyid () {        return _session. CreateQuery ("fromcustomerentity where customerid= ' 48cbcdfc-1aaa-4a08-9c9b-4578c6f59e8c '" ).　　List<customerentity>(); }

B, binary comparison operator =, >=, <=,<>,! =, like;

 Public Ilist<customerentity> Getcustomerbyname () {        return _session. CreateQuery ("fromcustomerentity where CustomerName like ' h% '").　　List<customerentity>(); }

C, logical operators and , or,not;

 Public Ilist<customerentity> getcustomerbywhere ()    {        return _session. CreateQuery ("fromcustomerentity where customerid= ' 48cbcdfc-1aaa-4a08-9c9b-4578c6f59e8c ' and CustomerName like ' h% '). List<customerentity>();    }

D, judge the comparatorin, not in, between, was null, is isn't null, is empty , is not empty;

 Public Ilist<customerentity> getcustomerbystring ()    {        return _session. CreateQuery ("fromcustomerentity where CustomerName in (' Huhai ')"). List<customerentity>();    }

E, SQL scalar function: Upper (), lower ();

 Public Ilist<customerentity> getcustomerbylower ()    {        return _session. CreateQuery (""). List<customerentity>();    }

F, position parameters:?

 Public Ilist<customerentity> Getcustomerbyid ()    {        = _session. CreateQuery ("fromcustomerentity where customerid=? ") " );        Query. SetString (0"48cbcdfc-1aaa-4a08-9c9b-4578c6f59e8c");         return query. List<customerentity>();    }

G, named parameter:: name,:x1;

 Public Ilist<customerentity> Getcustomerbyid ()    {        = _session. CreateQuery ("fromcustomerentity where Customerid=:fn");        Query. SetString ("fn""48cbcdfc-1aaa-4a08-9c9b-4578c6f59e8c " );         return query. List<customerentity>();    }

4. ORDER BY clause

A, desc Descending

     public ilist<customerentity> Getcustomerbyorder ()    {        return _session . CreateQuery ("fromcustomerentity C ORDER BY c.customerid desc"). List<customerentity>();    }

B, ASC Ascending

 Public Ilist<customerentity> Getcustomerbyorder ()    {        return _session. CreateQuery ("fromcustomerentity C ORDER by c.customername ASC"). List<customerentity>();    }

5. GROUP BY clause

     Public ilist<Object[]> getcustomerbygroup ()    {        return _ Session. CreateQuery ("Select C.customername, Count (C.customername) from the Customer C Group by C.customername "). list<Object[]>();    }

Second, in-depth expansion

1. Connection

A,inner join inside joins

 Public ilist<Object[]> getcustomerinterjoin ()    {        return _session. CreateQuery ("select C from customerentity as C inner join C.orderform"). list<Object[]>();    }

B, leftouter join outer connection

 Public ilist<Object[]> getcustomerleftjoin ()    {        return _session. CreateQuery ("select C from customerentity C left outer joins C.orderform by  C.orderform.customerid "). list<Object[]>();    }

C, rightouter joins

 Public ilist<Object[]> getcustomerrightjoin ()    {        return _session. CreateQuery ("select C from customerentity C right outer join C.orderform by  C.orderform.customerid "). list<Object[]>();    }

D, fulljoin fully connected, not commonly used

2. Aggregation function

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

NHibernate application five: HQL Daquan