HQL Query Statements

Query Language

Hibernate Query Language (HQL) is an object-oriented query language, similar to SQL, but not to operations on tables and columns, but object-oriented and their properties. HQL queries are translated into traditional SQL queries by Hibernate to manipulate the database.

Although you can use local SQL statements directly, I recommend that you use the HQL statement as much as possible to avoid database portability and to reflect Hibernate's SQL generation and caching policies.

Some keywords such as SELECT, from, and where are not case-sensitive in HQL, but some attributes such as table and column names are case-sensitive.

From statement

If you want to load a complete and persistent object in the store, you will use the FROM statement. Here are some simple syntax for the FROM statement:

String hql = "from Employee"== Query.list ();

If you need to fully qualify the class name in HQL, just specify the package and class name, as follows:

1 String hql = "from Com.hibernatebook.criteria.Employee"; 2 Query query = session.createquery (HQL); 3 List results = query.list ();

As statement

The AS statement in HQL can be used to assign aliases to your class, especially in the case of long queries. For example, our previous examples can be shown in the following ways:

1 String hql = "from Employee as E"; 2 Query query = session.createquery (HQL); 3 List results = query.list ();

The keyword as is selectable and you can also specify an alias directly after the class name, as follows:

1 String hql = "from Employee E"; 2 Query query = session.createquery (HQL); 3 List results = query.list ();

SELECT statement

The SELECT statement provides more control over the result set than the FROM statement. If you want to get only a few properties of the object instead of the whole object you need to use the SELECT statement. The following is an example of a simple syntax for a SELECT statement, which is to get the first_name field for the Employee object:

1 String hql = "Select E.firstname from Employee E"; 2 Query query = session.createquery (HQL); 3 List results = query.list ();

It is important to note that Employee.firstname is the property of the Employee object, not the field of an employee table.

WHERE statement

If you want to accurately return specific objects from the database store, you need to use the WHERE statement. Here is an example of a simple syntax for the WHERE statement:

1 String hql = "from Employee E WHERE e.id = ten"; 2 Query query = session.createquery (HQL); 3 List results = query.list ();

ORDER by statement

In order to sort the HSQ query results, you will need to use the order by statement. You can use any attribute to sort your results, including ascending or descending sort. The following is a simple example of using an ORDER by statement:

1 String hql = "from Employee E WHERE e.id > The ORDER by e.salary DESC"; 2 Query query = session.createquery (HQL); 3 List results = query.list ();

If you want to sort multiple attributes, you just need to add the attributes you want to sort after the order by statement, and split them with commas:

1 String hql = "from Employee E WHERE e.id >" +2              "ORDER by e.firstname Desc, e.salary desc" c3>; 3 Query query = session.createquery (HQL); 4 List results = query.list ();

GROUP by statement

This statement allows Hibernate to extract information from the database and group the information based on the value of a property, which, in general, uses the resulting result to contain an aggregated value. The following is a simple syntax for using the GROUP by statement:

1 String hql = "Select SUM (e.salary), e.firtname from Employee E" +2              "GROUP by E.firstname"; 
    3 query query = session.createquery (HQL); 4 List results = query.list ();

Using named parameters

Hibernate's HQL query feature supports named parameters. This allows the HQL query to accept simple input from the user without the need to defend against SQL injection attacks. The following is a simple syntax for using named arguments:

1 String hql = "from Employee E WHERE e.id =: employee_id"; 2 Query query = session.createquery (HQL); 3 query.setparameter ("employee_id", ten); 4 List results = query.list ();

UPDATE statement

HQL Hibernate 3 is more than HQL Hibernate 2, adding the ability to batch update and selective deletion. The query interface contains a executeupdate () method that can execute HQL UPDATE or DELETE statements.

The UPDATE statement can update one or more properties of one or more objects. The following is a simple syntax for using the UPDATE statement:

1 String hql = "UPDATE Employee Set salary =: Salary"  2              "WHERE id =: employee_id"; 3 Query query = session.createquery (HQL); 4 query.setparameter ("salary", +); 5 query.setparameter ("employee_id", ten); 6 int result = query.executeupdate (); 7 System.out.println ("Rows Affected:" + result);

DELETE statement

Delete statements can be used to delete one or more objects. The following is a simple syntax for using the DELETE statement:

1 String hql = "DELETE from Employee"  2              "WHERE id =: employee_id"; 3 Query query = session.createquery (HQL); 4 query.setparameter ("employee_id", ten); 5 int result = query.executeupdate (); 6 System.out.println ("Rows Affected:" + result);

INSERT statement

HQL the INSERT INTO statement is supported only when the record is inserted from one object to another. The following is a simple syntax for using the INSERT into statement:

1 String hql = "INSERT into Employee (FirstName, LastName, salary)"  2              "Select FirstName, LastName, salary from Old_employee "; 3 Query query = session.createquery (HQL); 4 int result = query.executeupdate (); 5 System.out.println ("Rows Affected:" + result);

Aggregation methods

HQL is similar to SQL and supports a series of aggregation methods that work in HQL and SQL in the same way, and the following are several available methods:

S.N.	Method	Description
1	Avg (property name)	Average of attributes
2	Count (property name or *)	Number of occurrences of the property in the result
3	Max (property name)	Maximum value of the property value
4	Min (property name)	Minimum value of the property value
5	Sum (property name)	Sum of property values

The DISTINCT keyword indicates that only unique values in the rowset are computed. The following query only calculates a unique value:

1 String hql = "SELECT count (Distinct e.firstname) from Employee E"; 2 Query query = session.createquery (HQL); 3 List results = query.list ();

Using paged queries

Here are two ways to page through the query interface:

S.N.	Method & Description
1	Query setfirstresult (int startposition) The method represents the first row in the result as an integer, starting with 0 rows.
2	Query setmaxresults (int maxresult) This method tells Hibernate to retrieve a fixed number, that is, the MaxResults object.

Using both of these methods, we can construct a paging component in our web or Swing application. Here is an example that you can extend to fetch 10 rows at a time:

1 String hql = "from Employee"; 2 Query query = session.createquery (HQL); 3 query.setfirstresult (1); 4 query.setmaxresults (ten); 5 List results = query.list ();

http://wiki.jikexueyuan.com/project/hibernate/query-language.html

HQL Query Statements