Hibernate HQL Order By and Group By Example

By Arvind Rai, December 04, 2015

On this page we will provide Hibernate HQL Order By and Group By example. Order by orders the data on the basis of given property in ascending or descending order. Group by groups the data on the basis of given property. In HQL we perform order by and group by for the given property of entity or associated entities. Find the complete example.

Data Used for Demo

Hibernate HQL Order By Example

The properties of Entity and its associated Entities can be used to order the data. The keyword is order by.

String hql = "from Book as bk order by bk.bookName desc";

We can also use more than one property to order the data.

String hql = "from Book as bk order by bk.bookName, bk.writer desc";

Find the example.
OrderByDemo.java

package com.concretepage;
import java.util.List;
import org.hibernate.Session;
public class OrderByDemo {
	public static void main(String[] args) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		String hql = "from Book as bk order by bk.bookName, bk.writer desc";
		List<?> list = session.createQuery(hql).list();
		for(int i=0; i<list.size(); i++) {
			Book book = (Book) list.get(i);
			System.out.println(book.getBookId()+", "+ book.getBookName()+
					","+book.getWriter()+", "+book.getPrice());
		}		
		session.close();
	}
}

Find the output.

Hibernate: select book0_.book_id as book_id1_0_, book0_.book_name as book_nam2_0_, book0_.price as price3_0_, book0_.writer as writer4_0_ from book book0_ order by book0_.book_name, book0_.writer desc
0, FGH,Mahesh, 100
3, PFG,Mahesh, 200
1, PQR,Mahesh, 200
2, XYZ,Sohan, 100

Hibernate HQL Group By Example

Like order by, the properties can also be used to group data. The keyword is group by.

String hql = "select bk.writer, max(bk.price) from Book as bk group by bk.writer";

We can use more than one property to group data. To filter the group we can also use having keyword as follows.

String hql = "select bk.writer, max(bk.price) from Book as bk group by bk.writer having avg(bk.price) > 100";

Find the example.
GroupByDemo.java

package com.concretepage;
import java.util.List;
import org.hibernate.Session;
public class GroupByDemo {
	public static void main(String[] args) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		String hql = "select bk.writer, max(bk.price) from Book as bk group by bk.writer"
				+ " having avg(bk.price) > 100";
		List<?> list = session.createQuery(hql).list();
		for(int i=0; i<list.size(); i++) {
			Object[] row = (Object[]) list.get(i);
			System.out.println(row[0]+", "+ row[1]);
		}
		session.close();
	}
}

Find the output.

Hibernate: select book0_.writer as col_0_0_, max(book0_.price) as col_1_0_ from book book0_ group by book0_.writer having avg(book0_.price)>100
Mahesh, 200

Entity

Book.java

package com.concretepage;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name="book")
public class Book {
	@Id
	@Column(name="book_id")
	private int bookId;
	@Column(name="book_name")
	private String bookName;
	@Column(name="writer")
	private String writer;
	@Column(name="price")
	private int price;	
	public int getBookId() {
		return bookId;
	}
	public void setBookId(int bookId) {
		this.bookId = bookId;
	}
	public String getBookName() {
		return bookName;
	}
	public void setBookName(String bookName) {
		this.bookName = bookName;
	}
	public String getWriter() {
		return writer;
	}
	public void setWriter(String writer) {
		this.writer = writer;
	}
	public int getPrice() {
		return price;
	}
	public void setPrice(int price) {
		this.price = price;
	}	
}

hibernate.cfg.xml

<!DOCTYPE hibernate-configuration PUBLIC
    "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
  <session-factory>
    <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="hibernate.connection.url">
    jdbc:mysql://localhost:3306/concretepage</property>
    <property name="hibernate.connection.username">root</property>
    <property name="hibernate.connection.password"></property>
    <property name="hibernate.connection.pool_size">10</property>
    <property name="show_sql">true</property>
    <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
    <property name="hibernate.hbm2ddl.auto">update</property>
    <mapping class="com.concretepage.Book"/>
   </session-factory>
</hibernate-configuration>

Hibernate Utility Class

HibernateUtil.java

package com.concretepage;
import org.hibernate.SessionFactory;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;
public class HibernateUtil {
    private static SessionFactory sessionFactory ;
    static {
       Configuration configuration = new Configuration().configure();
       StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder()
                      .applySettings(configuration.getProperties());
       sessionFactory = configuration.buildSessionFactory(builder.build());
    }
    public static SessionFactory getSessionFactory() {
        return sessionFactory;
    }
}