Wind shadow summary nhibernate5 query-hql)

ArticleDirectory

• 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.

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 nhib.pdfProgramIn this section and the next section, we will initially discuss the query method in nhib.pdf. Before that, I would like to recall what was completed in the previous section. Summarize the three important things in the previous section: creating a database table ----- writing persistence classes ----- writing a ing file, and then configuring and using it.

One thing I want to add,

That is, the configuration file of NH can no longer be in the app. configration or web configuration file, which greatly simplifies the content of the configuration file and adopts a strong type.

 
1:Public StaticConfiguration configurenhibernate (StringAssembly)
 
 
2:{
 
 
3:Configuration =NewConfiguration ();
 
 
4:Configuration. databaseintegration (DBI =>
 
 
5:{
 
 
6:DBI. dialect <mssql2008dialect> ();
 
 
7:DBI. Driver <sqlclientdriver> ();
 
 
8:DBI. connectionprovider <driverconnectionprovider> ();
 
 
9:DBI. isolationlevel = isolationlevel. readcommitted;
 
10:DBI. Timeout = 15;
 
 
11:DBI. connectionstringname ="Guitarstore";
 
 
12:});
 
 
13:Configuration. addassembly (assembly );
 
 
14: ReturnConfiguration;
 
 
15:}
 
 
 
 
Modify the enhibernate in the previous nhibernatebase.
 
 
Then the app configuration file can be simplified
 
 
1:<? XML version ="1.0"Encoding ="UTF-8"?>
 
2:<Configuration>
 
 
3:<Configsections>
 
 
4:<Section name ="Hibernate-configuration"
 
 
5:Type ="Nhibler. cfg. configurationsectionhandler, Nhibernate"/>
 
 
6:</Configsections>
 
 
7:<Connectionstrings>
 
 
8:<Add name ="Guitarstore"
 
 
9:Connectionstring ="Data Source =.; initial catalog = demo; Integrated Security = true"/>
 
 
10:</Connectionstrings>
 
11:<Startup>
 
 
12:<Supportedruntime version ="V4.0"SKU =". Netframework, version = v4.5"/>
 
 
13:</Startup>
 
 
14:</Configuration>
 
 
Is it good. As a class library, you can directly separate it.
 
 
 
 
 
 
 
 
Let's take a look at hql today.
 
The query method in nhib.pdf
We have selected many query methods in nhib.pdf. Here we only list three methods: hql and nhib1_query language, and criteria API, query by example (QBE) is a special case of 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. This section describes the hql and nhib1_query language ). In fact, there are also queries in the LINQ mode and subsequent supplements in the lambda mode.
 
 
 
 
 
 
 
Hql)
 
Hql (nhib1_query language) is an object-oriented SQL query language unique to nhib1_. it has the characteristics of inheritance, polymorphism, and association. The object and attribute in OOP are used to map tables and columns in the database.
 
 
For example, select G. goodstypename from goodstype g
 
 
Goodstype is a database table, goodstypename is a column, and hql: goodstype is an object, goodstypename is the attribute of goodstype object. In contrast, SQL statements are very flexible, but they do not support syntax verification during compilation.
 
 
This section describes the basic syntax: From clause, select clause, where clause, order by clause, and group by clause, and lists 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 keywords are case insensitive.
 
Note: Due to the limited space, I only posted the data access layerCodeIs a method that can be called directly at the business logic layer. The code for testing these methods has not been posted. You can downloadSource codeTake a closer look at the code to test these methods. This section is based on the source code of the previous section.
 
 
 
 
1. From clause
 
Similar to SQL statements, as the name implies:
 
 
1. Simple usage: return all data in the table.
 
 
 
 
The from keyword directly matches the model name to obtain all data in the table corresponding to the model.
 
 
Nhibernatebase. session. createquery is used to process hql statements. Return an IQUERY object and call the generic list method to obtain the result set. It is strongly typed.
 
 
2. Use an alias: use as to assign an alias to a table. As can be omitted.
 
 
Public ilist <customer> fromalias () {// returns all instances of the customer class, and the customer assigns the alias customer return _ Session. createquery ("from customer as customer "). list <customer> ();}
 
 
 
 
2. Select clause
 
1. Simple usage: return the specified object and attribute in the result set.
 
 
 
 
 
In this way, you can obtain the data of a specific attribute of an object. However, such data is no longer related to the model.
 
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. Note: Here is the array of object []. We can define the DTO object set to return, that is, the type-safe. Net object is used.
 
 
Public ilist <object []> SelectObject () {return _ Session. createquery ("select C. firstname, count (C. firstname) from customer C group by C. firstname "). list <object []> ();}
 
 
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 count (elements, C. customerid ). Note: Here is the long set. We can define the DTO object set to return.
 
 
 
 
 
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.
 
 
 
3. Where clause
 
The where clause allows you to narrow down the list of instances to be returned.
 
 
 
 
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.
 
 
 
5. Group by clause
 
Group by the attributes of any returned class or component.
 
 
 
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:
 
 
PublicIlist <customer> getcustomersbyfirstname (StringFirstname ){
 
 
// Write 1 
 
// Return _ session. createquery ("from customer C where c. firstname = '" + firstname + "'")
 
 
//. List <customer> (); 
 
 
// Statement 2: Location Parameter 
 
 
// Return _ session. createquery ("from customer C where c. firstname =? ") //. Setstring (0, firstname)
 
 
//. List <customer> (); 
 
 
// Statement 3: naming parameters (recommended) 
 
 
Return_ Session. createquery ("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.
 
 
Obtain a customer whose customer ID is 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 learned about hql, a query language in nhib.pdf. The next section introduces another query language! Note: Some of this article returns the ilist <object []> type, which cannot be used in actual projects. We need to use an object, which is a DTO conversion and returns the ilist <classdto> type.