Definition –
Hibernate Query Language (HQL) is used to perform database operations on objects. HQL allows you to perform SQL-like queries on your objects, and provides a way to navigate through object graphs using object-oriented syntax.
A- Performing database operations using HQL: – To perform database operations using HQL, you must first create a Hibernate Session object, which is used to manage database operations. Once you have a Session object, you can use HQL to query your objects and perform operations on them.
B- Understanding HQL syntax: – HQL is similar to SQL in syntax, but instead of working with tables and columns, it works with objects and their properties. HQL queries are written in a high-level, object-oriented syntax that is easy to read and understand.
Here’s an example of an HQL query that retrieves all Person objects from the database:
String hql = "FROM Person";
Query query = session.createQuery(hql);
List<Person> persons = query.list();
C- Using HQL to query objects: HQL can be used to query objects based on their properties, relationships, and other criteria. Here are a few examples:
- Querying objects based on a property:
String hql = "FROM Person p WHERE p.name = 'Kuldeep kaushik'";
Query query = session.createQuery(hql);
List<Person> persons = query.list();
In this example, the HQL query retrieves all Person objects whose name is “Kuldeep kaushik”.
- Querying objects based on a relationship:
String hql = "FROM Person p WHERE p.address.city = 'noida'";
Query query = session.createQuery(hql);
List<Person> persons = query.list();
In this example, the HQL query retrieves all Person objects whose address is in noida.
- Querying objects based on a date range:
String hql = "FROM Person p WHERE p.dob BETWEEN :startDate AND :endDate";
Query query = session.createQuery(hql);
query.setParameter("startDate", new Date());
query.setParameter("endDate", new Date());
List<Person> persons = query.list();
In this example, the HQL query retrieves all Person objects whose date of birth is between the current date and the current date.
10 Features of Query Object in Hibernate-
This example demonstrates the following features of the Query object:
- Retrieving all objects of a certain type from the database (
FROMclause). - Retrieving a single object based on its ID (
WHEREclause andsetParameter()method). - Updating an object in the database (
update()method). - Deleting an object from the database (
delete()method). - Filtering objects based on a certain property (
WHEREclause andLIKEoperator). - Joining tables and filtering based on a related object’s property (
p.address.city). - Filtering objects based on a range of values (
BETWEENoperator). - Retrieving a count of objects (
SELECT COUNT(*)). - Retrieving an aggregate value (
MAX(),AVG()). - Retrieving specific properties of objects as a DTO (
SELECT newsyntax).
Here’s an example that demonstrates various features of the Query object in Hibernate:
Suppose we have a Person entity class as defined earlier. We also have an Address entity class with the following properties:
public class Address {
private long id;
private String street;
private String city;
private String state;
private String zip;
private Person person;
// getters and setters
}
and It’s mapping xml file is below. –
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="com.example.Address" table="addresses">
<id name="id" type="long">
<column name="address_id" />
<generator class="native" />
</id>
<property name="street" column="street" />
<property name="city" column="city" />
<property name="state" column="state" />
<property name="zipCode" column="zip_code" />
</class>
</hibernate-mapping>
Note – Don’t forget to add this hbm file in hbm configuration file.
Complete Code –
import java.util.Date;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.query.Query;
public class QueryDemo {
public static void main(String[] args) {
// create a SessionFactory object
Configuration configuration = new Configuration().configure();
SessionFactory sessionFactory = configuration.buildSessionFactory();
// create a new Person object and save it to the database
Session session = sessionFactory.openSession();
Transaction transaction = session.beginTransaction();
Person person = new Person("Kuldeep kaushik", new Date());
session.save(person);
transaction.commit();
session.close();
// retrieve all Person objects from the database
session = sessionFactory.openSession();
transaction = session.beginTransaction();
Query<Person> query = session.createQuery("FROM Person", Person.class);
List<Person> persons = query.list();
for (Person p : persons) {
System.out.println(p.getName() + " was born on " + p.getDob());
}
transaction.commit();
session.close();
// retrieve a single Person object from the database based on its ID
session = sessionFactory.openSession();
transaction = session.beginTransaction();
query = session.createQuery("FROM Person p WHERE p.id = :id", Person.class);
query.setParameter("id", person.getId());
person = query.getSingleResult();
System.out.println("Retrieved " + person.getName() + " from the database");
transaction.commit();
session.close();
// update a Person object in the database
session = sessionFactory.openSession();
transaction = session.beginTransaction();
person.setName("Deep sharma");
session.update(person);
transaction.commit();
session.close();
// delete a Person object from the database
session = sessionFactory.openSession();
transaction = session.beginTransaction();
session.delete(person);
transaction.commit();
session.close();
// retrieve all Person objects whose name contains a certain string
session = sessionFactory.openSession();
transaction = session.beginTransaction();
query = session.createQuery("FROM Person p WHERE p.name LIKE :name", Person.class);
query.setParameter("name", "%Doe%");
persons = query.list();
for (Person p : persons) {
System.out.println(p.getName() + " was born on " + p.getDob());
}
transaction.commit();
session.close();
// retrieve all Person objects whose address is in a certain city
session = sessionFactory.openSession();
transaction = session.beginTransaction();
query = session.createQuery("FROM Person p WHERE p.address.city = :city", Person.class);
query.setParameter("city", "New York");
persons = query.list();
for (Person p : persons) {
System.out.println(p.getName() + " lives in " + p.getAddress().getCity());
}
transaction.commit();
session.close();
// retrieve all Person objects whose date of birth is within a certain range
session = sessionFactory.openSession();
transaction = session.beginTransaction();
query = session.createQuery("FROM Person p WHERE p.dob BETWEEN :start AND :end", Person.class);
query.setParameter("start", new Date(0)); // January 1, 1970
query.setParameter("end", new Date()); // current date and time
persons = query.list();
for (Person p : persons) {
System.out.println(p.getName() + " was born on " + p.getDob());
}
transaction.commit();
session.close();
// retrieve the count of all Person objects in the database
session = sessionFactory.openSession();
transaction = session.beginTransaction();
query = session.createQuery("SELECT COUNT(*) FROM Person", Person.class);
long count = query.getSingleResult();
System.out.println("There are " + count + " Person objects in the database");
transaction.commit();
session.close();
// retrieve the maximum date of birth among all Person objects in the database
session = sessionFactory.openSession();
transaction = session.beginTransaction();
query = session.createQuery("SELECT MAX(p.dob) FROM Person p", Date.class);
Date maxDob = query.getSingleResult();
System.out.println("The maximum date of birth is " + maxDob);
transaction.commit();
session.close();
// retrieve the average date of birth among all Person objects in the database
session = sessionFactory.openSession();
transaction = session.beginTransaction();
query = session.createQuery("SELECT AVG(p.dob) FROM Person p", Double.class);
double avgDob = query.getSingleResult();
System.out.println("The average date of birth is " + new Date((long) avgDob));
transaction.commit();
session.close();
// retrieve the name and date of birth of all Person objects in the database as a DTO
session = sessionFactory.openSession();
transaction = session.beginTransaction();
query = session.createQuery("SELECT new PersonDTO(p.name, p.dob) FROM Person p", PersonDTO.class);
List<PersonDTO> dtos = query.list();
for (PersonDTO dto : dtos) {
System.out.println(dto.getName() + " was born on " + dto.getDob());
}
transaction.commit();
session.close();
// close the SessionFactory object
sessionFactory.close();
}
Core Java in 25 hours 