Hibernate Query Language (HQL)

Hibernate Query Language (HQL) is an object-oriented query language, like SQL which works with persistent objects and their properties.HQL queries are translated by Hibernate into conventional SQL queries which in turns perform action on database.Query interface is used to perform HQL queries.

There are following methods of Query interface.

  • public int executeUpdate() : It is used to execute the update or delete query.
  • public List list() :It returns the result of the SELECT query as a list.
  • public Query setFirstResult(int offset) : It specifies the offset from where record will be retrieved.
  • public Query setMaxResults(int limit) : It specifies the no. of records to be retrieved from the table.
  • public Query setParameter(int position, Object value): It sets the value to the JDBC PreparedStatement style query parameter.
  • public Query setParameter(String name, Object value): it sets the value to a named query parameter.
  • public Query setParameterList(String name, Collection collectionValue): it sets the Collection to a named query parameter.It is mainly used with in clause where we provide collection values.

We can obtain Query object using createQuery() method of Session interface.

SELECT operation Example

To get all records from a table as below.

 
//Here persistent class is Product
Query query=session.createQuery("from Product");
List list=query.list();

Pagination Example

 
Query query=session.createQuery("from Product");  
query.setFirstResult(0);  
query.setMaxResults(10);  
List list=query.list();//It will return the records from 0 to 10th number  

UPDATE operation Example

 
Transaction tx=session.beginTransaction();  
Query q=session.createQuery("update Product set price=:p where id=:i");  
q.setParameter("p",100);  
q.setParameter("i",10);  
int status=q.executeUpdate();  
System.out.println(status);  
tx.commit(); 

DELETE operation Example

 
Query query=session.createQuery("delete from Product where id=10");  
//here Product is persistent class
query.executeUpdate();  

ORDER BY Clause

 
String hql = "FROM Product ORDER BY qty DESC";
Query query = session.createQuery(hql);
List results = query.list();

GROUP BY Clause

 
String hql = "SELECT COUNT(p.id), p.name FROM Product p " +
             "GROUP BY p.categoryId";
Query query = session.createQuery(hql);
List results = query.list();

IN Clause Example

 
 Query query = session.createQuery("from Product where price in(:priceList)");
 ArrayList<Double> list2 = new ArrayList<Double>();
 list2.add(750.0);
 list2.add(100.0);
 list2.add(70.0);
 query.setParameterList("priceList", list2);
 List list  = query.list();

Now I am taking example to execute HQL here.

Required jars

  • antlr-2.7.7.jar
  • dom4j-1.6.1.jar
  • hibernate-commons-annotations-4.0.5.Final.jar
  • hibernate-core-4.3.10.Final.jar
  • hibernate-jpa-2.1-api-1.0.0.Final.jar
  • jandex-1.1.0.Final.jar
  • javassist-3.18.1-GA.jar
  • jboss-logging-3.1.3.GA.jar
  • jboss-logging-annotations-1.2.0.Beta1.jar
  • jboss-transaction-api_1.2_spec-1.0.0.Final.jar
  • mysql-connector-java-5.0.3-bin
Download Jars

Required files

  • Product.java
  • hibernate.cfg.xml
  • HibernateHQLTest.java

users.sql


CREATE TABLE IF NOT EXISTS `product` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`description` varchar(255) DEFAULT NULL,
`price` float DEFAULT NULL, 
PRIMARY KEY (`id`),
)

Product.java


package com.startwithjava.beans;

import java.util.Set;
public class Product {
 private int id;
 private String name;
 private String description;
 private double price;
 public int getId() {
 return id;
}
public void setId(int id) {
 this.id = id;
}
public String getName() {
 return name;
}
public void setName(String name) {
 this.name = name;
}
public String getDescription() {
 return description;
}
public void setDescription(String description) {
 this.description = description;
}
public double getPrice() {
 return price;
}
public void setPrice(double price) {
 this.price = price;
}
}

product.hbm.xml


<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="com.startwithjava.beans.Product">
<id name="id">
<generator class="increment"></generator>
</id>
<property name="name"></property>
<property name="description"></property>
<property name="price" type="double"></property>
</class>
</hibernate-mapping>

hibernate.cfg.xml


<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration SYSTEM "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="hbm2ddl.auto">update</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="connection.url">jdbc:mysql://localhost:3306/hibernatetest</property>
<property name="connection.username">root</property>
<property name="connection.password"></property>
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<mapping resource="product.hbm.xml"/>
</session-factory>
</hibernate-configuration>

HQLTest.java

It fetches first 2 products and order by price descending order.


package com.startwithjava.test;
import java.util.Iterator;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import com.startwithjava.beans.Product;
public class HQLTest {
public static void main(String args[]){
Configuration cfg=new Configuration();
cfg.configure("hibernate.cfg.xml");
SessionFactory factory=cfg.buildSessionFactory();
Session session = factory.openSession();
//Fetch first 2 products and order by price desc
Query query = session.createQuery("from Product ORDER BY price DESC");
query.setFirstResult(0);
query.setMaxResults(2);
List list  = query.list();
Iterator itr= list.iterator();
System.out.println("Name\t  Price");
while(itr.hasNext()){
Product product = (Product)itr.next();
System.out.println(product.getName()+"\t"+product.getPrice());
}
session.close();
}
}

 

Leave a Reply