10.1 Overview
As Hibernate is all about persisting java objects, Hibernate Query Language is a similar to SQL but in terms of Object. We may want queries with where clauses, conditions , aggregate functions etc in our application and HQL does support all these features. One of the important different between SQL and HQL is , class names are used in place of tables and object properties names are used in place of table column names. In this chapter we will discuss HQL in detail. Hibernate also supports native SQL which we will discuss later.
HQL are case insensitive.Using HQL keeps our application code database independent.
10.2 Query Object
There are several methods available in Query API ( refer Chapter 6 [ section 6.4] ) for more details on Query API.
We can create a query object, using session object. Session Object does provide a createQuery() method which takes a query as an argument and returns a Query object.
Refer below code snippet for reference
Query query = session.createQuery(“ from Object ”);
10.3 from Clause
From is the only mandatory part of HQL.. It tells hibernate from where to pull the data. For example if we have a BOOK-INFO table corresponding to Book object and we want to fire a query on Book object .
Query query = session.createQuery(“ from Book ”);
10.4 Aliases
We can assign an aliases to the objects in HQL by just adding an alias next to object OR using “as” keyword.
To assign an alias for Book object we can use any one of below syntax
Query query = session.createQuery(“ from Book as bookAlias ”);
Query query = session.createQuery(“ from Book bookAlias ”);
10.5 select Clause
To get all the columns of table we can simple use “from Object” . There is no need of select clause but if we want to get selected properties only we need to use select clause providing the list of comma separated name of the object properties .
If select statement has multiple properties to fetch then result will be an List of Object array (List<Object[] >. If select has one property then result will be list of property data type
Assuming name and author are the two properties of Book object then we can use below queries.
Query query = session.createQuery(“select name from Book ”);
Query query = session.createQuery(“ select alias.name from Book as alias ”);
To get a list of an object using select statement , we can use constructor . For example
Query query = session.createQuery(“ select new Book(name, price) from Book ”);
List<Book> books = query.list();
10.6 where Clause
We can get the filtered objects using where clause.
Assuming isbn , name and author are the properties of Book object then we can use below queries.
Query query = session.createQuery(“select name from Book where isbn=’123’ ”);
10.7 Supported Operators
a)Logical Operators - AND , OR , NOT
Query query = session.createQuery(“ select name from Book where isbn=’123’ and author=’xyz’ ”);
b) Equality Operators - =, !=
Query query = session.createQuery(“ select name from Book where isbn=’123’ and author !=’xyz’ ”);
c) Comparison Operators - < , > ,<= ,>=, LIKE, NOT LIKE , BETWEEN , NOT BETWEEN , IS NULL , IS NOT NULL , IN
Query query = session.createQuery(“ select name from Book where isbn=’123’ and author like ’%xyz’ ”);
Query query = session.createQuery(“ select name from Book where author in ( ‘xyz’ , ‘abc’ ”);
10.8 Pagination
HQL supports pagination where we can specify the starting record number and number of records to fetch. To do so we can use setFirstResult and setMaxResults methods.
Below query will fetch 15 records starting from 5th record.
Query query = session.createQuery(“select name from Book ”);
query.setFirstResult(5);
query.setMaxResults(15);
10.9 Parameters Binding
Instead of hard coding the variables values , we can bind the values at run time. We can bind the parameters using
- Name – also known as named parameter binding
- Position - also known as positional parameter binding.
10.9.1 Named Parameter Binding
- Using named parameter binding , query becomes more readable and same parameter can be reused multiple times. To use named parameter we need to use “ :name ” syntax
Query query = session.createQuery(“select name from Book where name = : bookName ”);
query.setString(“bookName”, “XYZ”);
10.9.2 Positional Parameter Binding.
– Similar to traditional JDBC Prepared Statement style , we can use ? as a place holder ( 0 based)
Query query = session.createQuery(“select name from Book where name = ? ”);
query.setString(0, “XYZ”);
10.10 Functions
We can use several functions of SQL in HQL as well. Functions like avg() , sum(), count() , max() , min() , distinct are supported.
String hql = “ select sum(book.price) from Book as book”;
Query query = session.createQuery(hql);
10.11 CRUD Operations
HQL supports INSERT, DELETE, UPDATE ,SELECT clauses. For INSERT we cannot provide arbitrary values , instead we can only use select which means select the record from database and insert it. To insert a new record , we can simply use save() method
10.12 Sorting
We can use ORDER BY clause to sort the results
Query query = session.createQuery(“select name from Book order by name desc ”);
10.13 Example
Lets create one example of Product to use HQL .
a. Product.java
package com.tutorial.hibernate;
import java.util.Date;
public class Product {
private int productId;
private String productName;
private double price;
private String productCategory;
private Date expiryDate;
private String batchNumber;
public Product()
{
}
public Product(double price, String productCategory, String batchNumber) {
super();
this.price = price;
this.productCategory = productCategory;
this.batchNumber = batchNumber;
}
public int getProductId() {
return productId;
}
public void setProductId(int productId) {
this.productId = productId;
}
public String getProductName() {
return productName;
}
public void setProductName(String productName) {
this.productName = productName;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public String getProductCategory() {
return productCategory;
}
public void setProductCategory(String productCategory) {
this.productCategory = productCategory;
}
public Date getExpiryDate() {
return expiryDate;
}
public void setExpiryDate(Date expiryDate) {
this.expiryDate = expiryDate;
}
public String getBatchNumber() {
return batchNumber;
}
public void setBatchNumber(String batchNumber) {
this.batchNumber = batchNumber;
}
@Override
public String toString() {
return "Product [productId=" + productId + ", productName="
+ productName + ", price=" + price + ", productCategory="
+ productCategory + ", expiryDate=" + expiryDate
+ ", batchNumber=" + batchNumber + "]";
}
}
b. 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.tutorial.hibernate.Product" table="PRODUCT"> <id name="productId" type="int" column="product_id"> <generator class="native"/> </id> <property name="productName" column="name" type="string"/> <property name="batchNumber" column="batch" type="string" /> <property name="productCategory" column="category" type="string" /> <property name="price" column="price" type="double" /> <property name="expiryDate" column="expiry_date" type="date" /> </class> </hibernate-mapping>
c. hibernate.cfg.xml
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.url">
jdbc:mysql://localhost:3306/tutorial
</property>
<property name="hibernate.connection.username">
root
</property>
<property name="hibernate.connection.password">
password
</property>
<property
name="dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="hibernate.format_sql">true</property>
<property name="show_sql">true</property>
<property name="hibernate.connection.driver_class">
com.mysql.jdbc.Driver
</property>
<mapping resource="product.hbm.xml" />
</session-factory>
</hibernate-configuration>
d. create product table
CREATE TABLE `product` ( `product_id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `batch` varchar(255) DEFAULT NULL, `category` varchar(255) DEFAULT NULL, `price` double DEFAULT NULL, `expiry_date` date DEFAULT NULL, PRIMARY KEY (`product_id`) )
e.HQLExample.java
import java.util.Date;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import com.tutorial.hibernate.Product;
public class HQLExample {
private static SessionFactory sessionFactory;
public static void main(String a[])
{
getSessionFactory();
//save some products in database
Product product1= getProductObject("Product A", "SOAP", "AB-23-F",23.4,new Date() );
Product product2= getProductObject("Product B", "TOOTH PASTE", "XXAB-23-F",13.4,new Date() );
Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
session.save(product1);
session.save(product2);
tx.commit();
session.close();
getAllProducts();
getProductByName("Product B");
getSpecificDetailsOfProduct();
deleteProduct(1);
updateProduct(2,"Product C");
sessionFactory.close();
}
private static void getSessionFactory()
{
Configuration cfg = new Configuration().configure();
sessionFactory = cfg.buildSessionFactory();
}
private static void updateProduct(int productId, String name )
{
Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
String queryString = "update Product set productName = ? where productId =? " ;
Query query = session.createQuery(queryString);
query.setString(0,name);
query.setInteger(1,productId);
query.executeUpdate();
tx.commit();
session.close();
}
private static void deleteProduct(int productId)
{
Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
String queryString = "delete from Product where productId =? " ;
Query query = session.createQuery(queryString);
query.setInteger(0,productId);
query.executeUpdate();
tx.commit();
session.close();
}
private static void getAllProducts()
{
Session session = sessionFactory.openSession();
String queryString = "from Product " ;
Query query = session.createQuery(queryString);
List <Product> products = query.list();
for(int i=0;i<products.size();i++)
{
System.out.println(products.get(i));
System.out.println();
}
session.close();
}
private static void getProductByName(String name )
{
Session session = sessionFactory.openSession();
String queryString = "from Product where productName = :name" ;
Query query = session.createQuery(queryString);
query.setString("name", name);
Product product = (Product)query.uniqueResult();
System.out.println(product);
System.out.println();
session.close();
}
private static void getSpecificDetailsOfProduct()
{
Session session = sessionFactory.openSession();
// return list of objects (2) and each will be an array of 3
String queryString = "select productCategory, price, batchNumber from Product " ;
Query query = session.createQuery(queryString);
List<Object[]> product = query.list();
for(int i=0;i<product.size();i++)
{
Object[] data = product.get(i);
System.out.println(data[0] + "," + data[1] + "," + data[2]);
System.out.println();
}
// Another way
queryString = "select new Product( price, productCategory,batchNumber ) from Product " ;
query = session.createQuery(queryString);
List<Product> products = query.list();
for(int i=0;i<products.size();i++)
{
System.out.println(products.get(i));
}
session.close();
}
private static Product getProductObject(String name , String category, String batch, double price,
Date expiry)
{
Product product = new Product();
product.setBatchNumber(batch);
product.setExpiryDate(expiry);
product.setPrice(price);
product.setProductCategory(category);
product.setProductName(name);
return product;
}
}
