Tutorial - Hibernate HQL example with where clause

Sponsored Links

Ad by Google

In my previous post, I have shared how to use Criteria Query in Hibernate with few examples of Criteria Query here
And, In this post I am going to show you few examples of HQL (Hibernate Query Language).

The HQL is very powerful query language and uses in Hibernate to retrieve the records from the database. The HQL syntax is almost same as SQL, the job of both Sql and Hql is same but HQL is based on object oriented pattern whereas SQL is based on relational approach. In this post, I am going to re-use our one of the previous tutorial on One-To-Many Association Example to store the records into the database and HQL to retrieve the records from the database. Let's see few examples of Hibernate Query Language.

Hibernate Query Language Example With From Clause

Simplest HQL example is using from clause, to fetch all the instances of author.

Query query = session.createQuery("from Author");

Hibernate Query Language Using Select Clause

Using Select clause you can picks the selected properties of your object as a result set of your query. The Select clause will return list of object or list of array object.
Example,

Query query = session.createQuery(" select a.firstName,a.dateOfBirth from Author a");
List<Object[]> objList =  query.list();

Hibernate Query Example using aggregate function

One can use aggregate function in hibernate query to get the sum,count,avg,max etc.
Example,

Query query = session.createQuery("select max(a.dateOfBirth),count(a.authorId) from Author a");
return query.list();

Hibernate Query Example using group by clause

You can use group by clause in hibernate query to fetch the elements based on group by clause.
Example,

Query query = session.createQuery("from Author a group by a.lastName");
return query.list();

Hibernate Query Example using where clause

Of course you can use where clause like in sql.
Example,

Query query = session.createQuery("from Author a where a.firstName = :firstName ");
query.setParameter("firstName", searhName);
return query.list();

Hibernate Query Example using order by clause

Example,

Query query = session.createQuery("from Author a order by a.dateOfBirth desc");
return query.list();

Below is the complete example of HQL in AuthorDAO.java class,To run you need download the previous example of One-To-Many association example and replace the AuthorDAO.java with below source code.
AuthorDAO.java

package com.javamakeuse.poc.dao;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;

import com.javamakeuse.poc.pojo.Author;
import com.javamakeuse.poc.util.HibernateUtility;

public class AuthorDAO {
	private static SessionFactory sessionFactory;
	static {
		sessionFactory = HibernateUtility.getSessionFactory();
	}

	public static List<Author> fromClauseExample() {
		Session session = sessionFactory.openSession();
		Query query = session.createQuery("from Author");
		return query.list();
	}

	public static List<Object[]> selectExample() {
		Session session = sessionFactory.openSession();
		Query query = session
				.createQuery("select max(a.dateOfBirth),count(a.authorId) from Author a");
		return query.list();
	}

	public static List<Object[]> aggregateExample() {
		Session session = sessionFactory.openSession();
		Query query = session
				.createQuery("select a.firstName,a.dateOfBirth from Author a");
		List<Object[]> objList = query.list();
		return objList;
	}

	public static List<Author> whereClauseExample(String searhName) {
		Session session = sessionFactory.openSession();
		Query query = session
				.createQuery("from Author a where a.firstName = :firstName ");
		query.setParameter("firstName", searhName);
		return query.list();
	}

	public static List<Author> fromWithGroupBy() {
		Session session = sessionFactory.openSession();
		Query query = session.createQuery("from Author a group by a.lastName");
		return query.list();
	}

	public static List<Author> fromWithOrderBy() {
		Session session = sessionFactory.openSession();
		Query query = session
				.createQuery("from Author a order by a.dateOfBirth desc");
		return query.list();
	}

}

That's it :)

You can find few popular post at :
How to Configure Second Level Cache in hibernate here
How to call Stored Procedure in hibernate with parameters here
Inheritance strategy in Hibernate here

Sponsored Links