hql语句的用法(Usage of the HQL statement)
hql语句的用法(Usage of the HQL statement)
HQL statement usage 2007-10-30 15:43
A ORM framework is built on an object oriented basis. The best example is how Hibernate provides class SQL queries. Although the syntax of HQL is similar to SQL, the object of its query is actually the object. HQL has all the features of object oriented languages, including polymorphism, inheritance, and composition. This is equivalent to an object-oriented SQL, and in order to provide more powerful functions, HQL also provides a lot of query functions. These functions can be divided into four categories:
1. projection function
2. constraint function
3. aggregation function
4. grouping function
Using HQL, you can create simple queries, or you can create more complex queries. In this article, very complex queries, such as queries containing subquery and many connections, are not discussed. This article discusses only queries that connect two tables. Now let's get closer to HQL!
Projection
Projection, such as projection, is an attribute of an object or object that can be accessed. In HQL, you can use the from
and select clauses to do this work.
The from clause returns all instances of the specified class. For example, from Order returns all instances of the Order class. In other words, the query above corresponds to the following SQL statement:
Select * from order
From is the simplest query clause. From can follow one or more class names (the class name can also be aliased). To get all instances of Order and Product, you can use the following query:
From, Order, Product
Like the class name, aliases can also be used after from, as shown in the following code:
From, Order, as, O, Product, P
When the query is complex, adding aliases can reduce the length of the statement. We can look at the following SQL statement:
Select, o.*, p.*, from, order, O, product, P, where, o.order_id = p.order_id
We can easily see that the query above is a one to many relationship. In HQL, it is equivalent to an instance of several other classes in a class. Therefore, the above SQL is written as HQL:
From, Order, as, O, inner, join, o.products, as, product
Now let's consider another one that gets the specified property from the table. This is the most commonly used select clause. This works the same way in HQL as in SQL. In HQL, if you want only the attributes of the class, the select statement is the final choice. The SQL above can be converted to the following HQL statement using the select clause:
Select, product, from, Order, as, O, inner, join,, o.products, as, product
The above HQL statement returns all the Products instances in Order. If you want to get an attribute of an object, you can write the HQL statement in the following form:
Select, product.name, from, Order, as, O, inner, join,, o.products, as, product
If you want properties for multiple objects, you can write the HQL statement in the following form:
Select, o.id, product.name, from, Order, as, O, inner,, join, o.products, as, product
Next, we will move on to the next topic. Suppose we need to get data based on certain conditions. Then the above HQL statement will not meet the requirements. To achieve this, we will use the constraint clause to be discussed.
constraint
From what you see above, the projection returns all the data. But most of the time, we don't need so much data. This requires filtering the data. In HQL, the clause that filters the data is the same as SQL, and also where. Its syntax is similar to that of SQL, which can be filtered through the where clause. We can see the following SQL statement:
Select * from, orders, where, id = ` 1234 '
This query returns all fields equal to ID 1234. The equivalent of this SQL is the following HQL statement:
Select, O, from, Order, O, where, o.id=, 1234
As you can see from the above two statements, their where clauses are very similar. The only difference is that SQL operates on records, while HQL operates on objects. In HQL, the having clause can do this except that the where clause can filter data. (for details about the having clause, I'll discuss it in the packet section). Projection and constraint are two basic operations, and these two operations, together with aggregate functions, will make HQL more powerful. Now let's talk about aggregation.
polymerization
Each of these queries takes each record as a unit, and if you use aggregation, you can take a class of records (objects) as a unit. Then a series of operations are performed on each type of record, such as averaging, summing, counting rows, etc. for
a column. HQL supports the following aggregation functions:
1. avg... ), sum... )
2. min... ), max... )
3. count (*), count... ) count (distinct)... ) count (all)... )
The aggregate function above returns the numeric type. These operations can be used in the select clause, as follows:
Select, max (o.priceTotal) + max (p.price), from, Order, O, join, o.products, P,, group, by, o.id
The above HQL statement returns the sum of the two values: the maximum of the priceTotal in the orders table and the sum of the maximum of the price in the products table. We can also filter the packets using the having clause. If we want to count HQL by ID, the number of priceTotal less than 1000 can be implemented as follows:
Select, count (o), from, Order, O, having, o.priceTotal, < 1000, group, by, o.id
We can also use the aggregate function and the having clause together. If we want to count the number of price products under the ID of the products table, the HQL statement is as follows: amount:
Select, count (P), from, Product, P, having, p.price, AVG (amount), group, by, p.id
As can be seen from a series of HQL statements above, all that are implemented through SQL can be implemented through HQL.
Grouping
In the last section, the concept of grouping has been covered. Grouping operations is a collection of rows. It groups a recordset according to a column (attribute). This is achieved by the group clause. The following example describes the general use of the group clause.
Select count (o) from Order o having o.priceTotal and o.priceTotal > = 1200 < = 3200 group by o.id
The grouping in HQL is similar to the grouping in SQL. In short, all other SQL functions can be described using HQL, in addition to some special extensions to SQL. In the next section, let's illustrate how to use HQL in java.
Using HQL in Java
So far, we've learned the basic uses of HQL. Next, let's give an example of how to use HQL in Java. The following example is given in the main part, because this is to discuss the usage of HQL, therefore, Hibernate on the set and in the main () calls the Hibernate function in part is not given, the reader can refer to the relevant article when. Now let's take a look at the following example.
Here is a package that you must refer to
Import java.util.List;
Import org.hibernate.*;
Import org.hibernate.cfg.*
Import com.Order;
The following is a class declaration
Public class MyOrder
{
......
}
Now let's implement the constructor of the MyOrder class
Public class MyOrder
{
SessionFactory sf;
Public, MyOrder ()
{
Configuration, CFG = new, Configuration (),.AddClass (Order.class);
SF = cfg.buildSessionFactory ();
}
......
}
The following getOrder function returns the Order object based
on the interval value of the priceTotal.
Public class MyOrder
{
... ... .
Public, Order, getOrder (String, lower, String, upper)
{
/ / open a session
Session sess = sf.openSession ();
/ / HQL statement
String query = "select, O, from, O""
+ "Order, as, O, join, o.products, as, P.""
+ "where o.priceTotal >: priceTotalLower"
+ "and o.priceTotal<: priceTotalUpper"";
Query q = sess.createQuery (query);
The two parameter / / incoming HQL
Q.setDouble ("priceTotalLower", "Double.parseDouble" (lower));
Q.setDouble ("priceTotalUpper", "Double.parseDouble" (upper));
List list = q.list ();
Order o= (Order) list.iterator.next ();
Return o;
}
......
}
The following main function tests the MyOrder class
Public class MyOrder
{
......
Public, static, void, main (String, args[])
{
Order o=MyOrder ().GetOrder ("100", "300");
System.out.println ("id=" + o.id);
......
}
}
Summary
The code above demonstrates how to use HQL in Java, but HQL has two more points to note:
The 1. HQL does not distinguish the case of letters, but the Java class and attribute names in HQL must correspond to the actual class and property names. For example, SELECT and select can be interchanged, but Order and order represent different meanings.
2., if the class referenced in HQL is not imported, specific packages must be referenced in HQL. As in this example, if com.Order is not imported, the Order must be written as com.Order in HQL.