hql语句的用法（Usage of the HQL statement）

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); ... } } 小结 上述的代码演示了如何在java中使用hql, 但hql还有两点需要注意一下: 1. hql并不区分字母的大小写, 但在hql中的java类和属性名必须和实际的类和属性名一致.如select和select之间可以互换, 但order和order却代不同的含义. 2. 如果hql中引用的类未被导入, 在hql中必须引用具体的包.如本 例中, 如果com.order未被导入, 在hql中必须将order写成 com.order.