Hibernate Query Language

Hibernate query language (HQL) is an object oriented query language. HQL is similar to SQL and it is case-insensitive except from the java classes and properties. Keywords like SELECT, FROM and WHERE etc. are not case sensitive but properties like table and column names are case sensitive in HQL. For understanding the HQL syntax, The HQL supports the following elements. They are

  1. Clauses
  2. Aggregate functions
  3. Subqueries
  4. Association and joins
  5. Expressions
  6. Pagination using query

 

Clauses

HQL support nine different clauses. They are, a)      from clause From clause is the most simplest hibernate query. This clause is used to load a complete persistent object into memory. Syntax:-

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

b)      As clause As clause allows assign aliases to the classes. Synatax: –

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

The keyword ‘As’ is optional and multiple classes can appear resulting in a Cartesian product or “cross” join. Syntax1: –

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

Syntax2:-

String hql = "FROM Person P, FROM Works W ";
Query query = session.createQuery(hql);
List results = query.list();

c)      select clause The select clause is used to obtain few properties of objects instead of the complete object. Syntax: –

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

d)     where clause The where clause is helps to pick up the specific objects that are returned from storage. Syntax: –

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

e)      Order by clause This clause is helps to sort our HQL query’s results. We can arrange the result by any property on the objects in the result set either ascending (ASC) or descending (DESC) order. Syntax: –

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

f)       Group by clause Group by clause is used to group the information based on a value of an attribute and also use the result to include an aggregate value. Syntax: –

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

g)      Update clause The UPDATE clause can be used to update one or more properties of one or more objects. Syntax: –

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

h)      Delete clause The DELETE clause can be used to delete one or more objects. Syntax: –

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

i)        Insert clause Insert into clause of hibernate query language support where records can be inserted from one object to another object. Syntax: –

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

Aggregate functions

HQL support the following five aggregate methods.

  1. avg(property name):- This function helps to take the average of a property’s value.
  2. count(property name or *):- count function helps to evaluate the number times a property occurs in the results.
  3. max(property name):- It takes the maximum value of the property values.
  4. min(property name):- It takes the minimum value of the property values.
  5. sum(property name):- It takes the sum total of the property values.

The distinct keyword helps to count the unique values in the row set. Syntax: –

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

Subqueries

Subqueries are query within another query. A subquery must be surrounded by parentheses (often by an SQL aggregate function call). Even correlated subqueries (subqueries that refer to an alias in the outer query) are allowed. Eg: –

 from Cat as fatcat
where fatcat.weight > (
select avg(cat.weight) from DomesticCat cat
)

Association and joins

We may also assign aliases to associated entities, or even to elements of a collection of values, using a join. The supported join types are borrowed from ANSI SQL

  • inner join
  • left outer join
  • right outer join
  • full join (not usually useful)

The inner join, left outer join and right outer join constructs may be abbreviated. Eg: –

from Cat as cat
join cat.mate as mate
left join cat.kittens as kitten

Expressions

Expressions are permits in the where clause. They are,

  • mathematical operators +, -, *, /
  • binary comparison operators =, >=, <=, <>, !=, like
  • logical operations and, or, not
  • string concatenation ||
  • SQL scalar functions like upper() and lower()
  • Parentheses ( ) indicate grouping  in, between, is null, not in, is not null, is empty, is not empty, member of and not member of.
  • “Simple” case, case ... when ... then ... else ... end, and “searched”  case, case when ... then ... else ... end
  • string concatenation ...||... or concat(...,...),current_date(), current_time(), and current_timestamp(),second(...), minute(...), hour(...), day(...), month(...), and year(...)
  • Any function or operator defined by EJB-QL 3.0: substring(), trim(),  lower(),upper(), length(), locate(), abs(), sqrt(), bit_length(), mod() ,coalesce() and nullif()
  • str() for converting numeric or temporal values to a readable string
  • cast(... as ...), where the second argument is the name of a Hibernate type, and extract(... from ...) if ANSI cast() and extract() is supported by the underlying database
  • The HQL index() function, that applies to aliases of a joined indexed collection
  • HQL functions that take collection-valued path expressions: size(), minelement(), maxelement(), minindex(), maxindex(), along with the special elements() and indices functions that can be quantified using some, all, exists, any, in.
  • Any database-supported SQL scalar function like sign(), trunc(), rtrim(), and sin()
  • JDBC IN parameters
  • Named parameters :name, :start_date, : x1
  • SQL literals ‘foo’, 69, ‘1970-01-01 10:00:01.0’
  • Java public static final constants eg.Color.TABBY

Pagination using query

There are two methods of the Query interface for pagination.

  1. Query setFirstResult(int startPosition):- This method takes an integer that represents the first row in your result set, starting with row 0.
  2. Query setMaxResults(int maxResult):- This method tells Hibernate to retrieve a fixed number maxResults of objects.

Syntax: –

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

Features of Hibernate Query Language

  1. Full support for relational operations
  2. Return result as object
  3. Polymorphic queries
  4. Easy to learn
  5. Support for advance features like pagination, fetch join with dynamic profiling, Inner/outer/full joins, Cartesian products, Projection, Aggregation (max, avg) and grouping, Ordering, Sub queries and SQL function calls.
  6. Database independent