Hibernate query language

The query language

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

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

Some keywords in HQL, such as SELECT, FROM, and WHERE, are case insensi now, but some properties, such as table and column names, are case sensitive.

From statement

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

String hql = "FROM Employee";
Query query = session.createQuery(hql);
List results = query.list();

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

String hql = "FROM com.hibernatebook.criteria.Employee";
Query query = session.createQuery(hql);
List results = query.list();

As statement

AS statements in HQL can be used to assign alias to your class, especially in the case of long queries. For example, our previous example can be shown in the following way:

String hql = "FROM Employee AS E";
Query query = session.createQuery(hql);
List results = query.list();

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

String hql = "FROM Employee E";
Query query = session.createQuery(hql);
List results = query.list();

SELECT statement

Select statements provide more control over the result set than from statements. I f you only want to get a few properties of an object instead of the entire object you need to use select statements. Here is a simple syntax example of a SELECT statement, which is intended to get the first_name employee object:

String hql = "SELECT E.firstName FROM Employee E";
Query query = session.createQuery(hql);
List results = query.list();

It is important to note that Employee.firstName is a property of the Employee object, not a field of an EMPLOYEE table.

Where statement

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

String hql = "FROM Employee E WHERE E.id = 10";
Query query = session.createQuery(hql);
List results = query.list();

ORDER BY statement

In order to sort the HSQ query results, you will need to use the ORDER BY statement. Y ou can use any of these properties to sort your results, including ascending or descending order. Here's a simple example of using an ORDER BY statement:

String hql = "FROM Employee E WHERE E.id > 10 ORDER BY E.salary DESC";
Query query = session.createQuery(hql);
List results = query.list();

If you want to sort multiple properties, you just need to add the properties you want to sort after the ORDER BY statement and split them with a comma:

String hql = "FROM Employee E WHERE E.id > 10 " +
             "ORDER BY E.firstName DESC, E.salary DESC ";
Query query = session.createQuery(hql);
List results = query.list();

GROUP BY statement

This statement allows Hibernate to extract information from the database and group it based on the value of a property, typically using the resulting result to contain an aggregated value. Here's a simple syntax for using group BY statements:

String hql = "SELECT SUM(E.salary), E.firtName FROM Employee E " +
             "GROUP BY E.firstName";
Query query = session.createQuery(hql);
List results = query.list();

Use named parameters

Hibernate's HQL query feature supports naming parameters. T his allows the HQL query feature to accept simple input from the user without the need to defend against SQL injection attacks. Here's a simple syntax for using named parameters:

String hql = "FROM Employee E WHERE E.id = :employee_id";
Query query = session.createQuery(hql);
query.setParameter("employee_id",10);
List results = query.list();

UPDATE statement

HQL Hibernate 3 adds bulk updates and selective removal of work compared to HQL Hibernate 2. The query interface contains an executeUpdate() method that can execute HQL's UPDATE or DELETE statements.

AN UPDATE statement can update one or more properties of one or more objects. Here's a simple syntax for using UPDATE statements:

String hql = "UPDATE Employee set salary = :salary "  + 
             "WHERE id = :employee_id";
Query query = session.createQuery(hql);
query.setParameter("salary", 1000);
query.setParameter("employee_id", 10);
int result = query.executeUpdate();
System.out.println("Rows affected: " + result);

DELETE statement

A DELETE statement can be used to delete one or more objects. Here's a simple syntax for using DELETE statements:

String hql = "DELETE FROM Employee "  + 
             "WHERE id = :employee_id";
Query query = session.createQuery(hql);
query.setParameter("employee_id", 10);
int result = query.executeUpdate();
System.out.println("Rows affected: " + result);

INSERT statement

HQL supports INSERT INTO statements only when records are inserted from one object to another. Here's a simple syntax for using INSERT INTO statements:

String hql = "INSERT INTO Employee(firstName, lastName, salary)"  + 
             "SELECT firstName, lastName, salary FROM old_employee";
Query query = session.createQuery(hql);
int result = query.executeUpdate();
System.out.println("Rows affected: " + result);

The aggregation method

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

S.N.	Method	Describe
1	avg(property name)	The average value of the property
2	count(property name or *)	The number of times the property appeared in the result
3	max(property name)	The maximum value of the property value
4	min(property name)	The minimum value of the property value
5	sum(property name)	The sum of the property values

The distinct keyword means that only the unique value in the row set is calculated. The following query calculates only unique values:

String hql = "SELECT count(distinct E.firstName) FROM Employee E";
Query query = session.createQuery(hql);
List results = query.list();

Use peddle queries

Here are two ways to peddle the interface:

S.N.	Methods and descriptions
1	Query setFirstResult(int startPosition) The method represents the first line in the result in an integer, starting with line 0.
2	Query setMaxResults(int maxResult) This method tells Hibernate to retrieve a fixed number, maxResults objects.

Using both methods, we can construct a platter component in our web or Swing application. Here's an example that you can expand to 10 lines at a time:

String hql = "FROM Employee";
Query query = session.createQuery(hql);
query.setFirstResult(1);
query.setMaxResults(10);
List results = query.list();