Hibernate Query Language example

Hibernate Query Language example
Hibernate Query Language example


In this post, we will see Hibernate Query Language(HQL) with example.

Hibernate query language is database independent language where we use entity name instead of table name and field name instead of the column name.

Consider we have an entity called Student.java for this example.

package com.netsurfingzone.entity;


@Entity
public class Student implements Serializable {
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Long id;

	@Column(name = "name")
	private String name;

	@Column(name = "roll_number")
	private String rollNumber;

	@Column(name = "university")
	private String university;

}

And we want to fetch data on basis of name(i.e return all students those study in RGTU university).

We will use this entity is as reference for this tutorial.

And we have configuration file i.e hibernate.cfg.xml

<?xml version='1.0' encoding='UTF-8'?>
<!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="connection.url">jdbc:mysql://localhost:3306/springbootcrudexample</property>
		<property name="connection.username">root</property>
		<property name="connection.password">root</property>

		<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
		<property name="show_sql">true</property>
		<property name="hbm2ddl.auto">update</property>
		<mapping class="com.netsurfingzone.entity.Student" />
	</session-factory>
</hibernate-configuration>

Also we have main class where we will have Session object.

package com.netsurfingzone.main;

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;

import com.netsurfingzone.entity.Student;

public class HibernateMain {
	public static void main(String[] args) {
		SessionFactory factory = null;
		Session session = null;
		Configuration configuration = new Configuration().configure();
		try {
			factory = configuration.buildSessionFactory();
			session = factory.openSession();
			Transaction tx = session.beginTransaction();
			
			//some more code
			tx.commit();
		} catch (Exception e) {
			e.printStackTrace();

		} finally {
			session.close();
			factory.close();
		}
	}

}

Let’s see an example to insert, update and delete a records using HQL.

Insert, Update and Delete Example using Hibernate Query Language(HQL)

Inserting example using Hibernate query language

Hibernate query language does not support insert into some_table value (….). It only supports insert into some_table select(…). observe the below HQL query.


        String insertQuery = "insert into Student(id, name, rollNumber, university)"
                + "select id, name, rollNumber, university from StudentBackup";
        Query query = session.createQuery(insertQuery);

Update example using Hibernate query language

Suppose we want to update student’s rollNumber have id 1.

        String updateQueryAsQuery = "update Student set rollNumber=:rollNumber where id=:id";
        Query updateQuery = session.createQuery(updateQueryAsQuery);
        updateQuery.setParameter("rollNumber", rollNumber);
        updateQuery.setParameter("id", id);

Delete example using Hibernate query language

Delete record on basis of id.

        String deleteQueryAsString = "delete Student where id=:id";
        Query deleteQuery = session.createQuery(deleteQueryAsString);
        deleteQuery.setParameter("id", id);

Let’s see complete example for insert, update and delete using HQL.

package com.netsurfingzone.main;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.query.Query;

public class HibernateMain {
	public static void main(String[] args) {
		SessionFactory factory = null;
		Session session = null;
		Configuration configuration = new Configuration().configure();
		try {

			factory = configuration.buildSessionFactory();
			session = factory.openSession();
			Transaction tx = session.beginTransaction();

			String insertQuery = "insert into Student(id, name, rollNumber, university)"
					+ "select id, name, rollNumber, university from StudentBackup";
			Query query = session.createQuery(insertQuery);
			if (query.executeUpdate() > 0) {
				System.out.println("Record inserted to student table successfully");
			}

			Long id = 2l;
			String rollNumber = "3333";
			String updateQueryAsQuery = "update Student set rollNumber=:rollNumber where id=:id";
			Query updateQuery = session.createQuery(updateQueryAsQuery);
			updateQuery.setParameter("rollNumber", rollNumber);
			updateQuery.setParameter("id", id);

			id = 2l;
			String deleteQueryAsString = "delete Student where id=:id";
			Query deleteQuery = session.createQuery(deleteQueryAsString);
			deleteQuery.setParameter("id", id);
			if (deleteQuery.executeUpdate() > 0) {
				System.out.println("Record deleted successfully");
			}
			tx.commit();
		} catch (Exception e) {
			e.printStackTrace();

		} finally {
			session.close();
			factory.close();
		}
	}

}

Note – you can also save some students/records using session.save() method.

        Student s1 = new Student();
        s1.setName("Rax");
        s1.setRollNumber("0126CS789");
        s1.setUniversity("RGTU");

        Student s2 = new Student();
        s2.setName("Peter");
        s2.setRollNumber("0126CS790");
        s2.setUniversity("RGTU");

        Student s3 = new Student();
        s3.setName("Jon");
        s3.setRollNumber("0126CS790");
        s3.setUniversity("RGTU");

        session.save(s1);
        session.save(s2);
        session.save(s3);

Get all records from the database using HQL

Hibernate Query language to get all records(students) from database.

        Query query=session.createQuery("from Student");  
        List<Student> students = query.list();
        students.forEach(student -> {
            System.out.println("Name is - "+student.getName());
            System.out.println("RollNumber is - "+student.getRollNumber());
            System.out.println("University is - "+student.getUniversity());
        });

Note – While writing Query query=session.createQuery(“from Student”); make sure Student first character is capital letter(Since we have defined entity as Student not student) else we will get java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException.

We can also define fully quelified class name with package.

        Query query=session.createQuery("from com.netsurfingzone.entity.Student");  
        List<Student> students = query.list();

Below query will also return all records.

        Query query=session.createQuery("from Student student");

We can also get all students records using AS clause in HQL.

        Query query=session.createQuery("from Student AS student");

Retrieve records on basis of fields – HQL Named Parameter

Suppose we want to retrieve all students those belongs to some specific university.


        Query query=session.createQuery("from Student where university= :university"); 
        query.setParameter("university", "RTGU");

Similar way we can get records for multiple fields. For example, return all students whose name is “John” and belongs to “RTGU” university.

        Query query=session.createQuery("from Student where name = :name and university= :university"); 
        query.setParameter("university", "RTGU");
        query.setParameter("name", "John");

Genarted query for above use case.

select student0_.id as id1_0_, student0_.name as name2_0_, student0_.roll_number as roll_num3_0_, student0_.university as universi4_0_ from Student student0_ where student0_.name=? and student0_.university=?

Sorting using Hibernate Query Language – OrderBy Example

Below HQL will sort on basis of name in ascending order.

        Query query=session.createQuery("from Student s ORDER BY s.name asc");
        List<Student> students = query.list();

Sorting using HQL on basis of name field in descending order.

	Query query=session.createQuery("from Student s ORDER BY s.name desc");
	List<Student> students = query.list();

Sorting on basis of multiple fields.

	Query query=session.createQuery("from Student s ORDER BY s.name asc, s.rollNumber desc");
	List<Student> students = query.list();

See a complete tutorial for sorting using Spring Data JPA, HIbernate and Spring boot here.

Other sorting tutorial using Hibernare and Spring Data JPA.

Sorting in Hibernate.Sorting in Spring Data JPA using Spring Boot.

Sorting in Spring Data JPA using Spring Boot.

@OrderBy Annotation in Hibernate for Sorting.

How to sort using Criteria in Hibernate.

Group by example using Hibernate query language

In this example we will see how to use group by clause in Hibernate query language.

Consider we have some student records in database and we want to know how many student are there those have same name.

SQL syntax –

select name, count(*) from student group by name;

This is the very basic group by clause for what we going to write HQL. In below example we will also learn how to get element from object array type list.

        Query query=session.createQuery("select s.name, count(*) from Student s group by s.name");
        List<Object> list = query.list();
        for(int i = 0; i< list.size(); i++){
            Object[] objectArray=(Object[])list.get(i);
            System.out.println("total count of "+objectArray[0]+ " is "+objectArray[1]);
        }

Output is –

Hibernate Query Language example

We can use oder by and group by clause together as below.

Query query=session.createQuery(” select s.name, count(*) from Student s group by s.name order by s.name asc”);

Writing Join query for OneToOne mapping using Hibernate query language.

In this example we will see how to write HQL Join query for One To One bidirectional mapping.

See Hibernate OneToOne bidirectional mapping complete tutorial using Spring Data JPA and Spring Boot from scratch.

        Long id = 1l;
        String hql = "from Student student left join fetch student.address where  student.id=:id";
        Query<Student> query = session.createQuery(hql, Student.class);
        query.setParameter("id", id);
        Student student = query.uniqueResult();

Let’s see complete example.

Student.java

package com.netsurfingzone.entity;

import java.io.Serializable;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToOne;

@Entity
public class Student implements Serializable {
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Long id;

	@Column(name = "name")
	private String name;

	@Column(name = "roll_number")
	private String rollNumber;

	@Column(name = "university")
	private String university;

	@OneToOne(cascade = CascadeType.ALL)
	@JoinColumn(name = "address_id", referencedColumnName = "id")
	private Address address;
	
	//Getter-Setter
}

Address.java

package com.netsurfingzone.entity;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToOne;

@Entity
public class Address {
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Long id;

	@Column(name = "city")
	private String city;

	@Column(name = "pin_code")
	private String pinCode;

	@OneToOne(cascade = CascadeType.ALL, mappedBy = "address")
	private Student student;

	// Getter-Setter

}

HibenrateMain.java

package com.netsurfingzone.main;

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;

import com.netsurfingzone.entity.Address;
import com.netsurfingzone.entity.Student;

public class HibernateMain {
	public static void main(String[] args) {
		SessionFactory factory = null;
		Session session = null;
		Configuration configuration = new Configuration().configure();
		try {

			factory = configuration.buildSessionFactory();
			session = factory.openSession();
			Transaction tx = session.beginTransaction();
			
			//Save some records using session.save() method
			Student student1 = new Student();
			student1.setName("Rax");
			student1.setRollNumber("0126CS789");
			student1.setUniversity("RGTU");
			
			Student student2 = new Student();
			student2.setName("Peter");
			student2.setRollNumber("0126CS790");
			student2.setUniversity("RGTU");
			
			Student student3 = new Student();
			student3.setName("Jon");
			student3.setRollNumber("0126CS790");
			student3.setUniversity("RGTU");
			
			Address address1 = new Address();
			address1.setCity("Delhi");
			address1.setPinCode("803114");
			Address address2 = new Address();
			address2.setCity("Mumbai");
			address2.setPinCode("803115");
			Address address3 = new Address();
			address3.setCity("Bangalore");
			address3.setPinCode("803116");
			
			
			student1.setAddress(address1);
			student2.setAddress(address2);
			student3.setAddress(address3);
			session.save(student1);
			session.save(student2);
			session.save(student3);
			
			tx.commit();
		} catch (Exception e) {
			e.printStackTrace();

		} finally {
			session.close();
			factory.close();
		}
	}

}

Database details.

Hibernate Query Language example

HQL to get child entity from parent.


package com.netsurfingzone.main;

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;

import com.netsurfingzone.entity.Address;
import com.netsurfingzone.entity.Student;

public class HibernateMain {
	public static void main(String[] args) {
		SessionFactory factory = null;
		Session session = null;
		Configuration configuration = new Configuration().configure();
		try {

			factory = configuration.buildSessionFactory();
			session = factory.openSession();
			Transaction tx = session.beginTransaction();

			Long id = 1l;
			String hql = "from Student student left join fetch student.address where  student.id=:id";
			Query<Student> query = session.createQuery(hql, Student.class);
			query.setParameter("id", id);
			Student student = query.uniqueResult();

			System.out.println("Student name is" + student.getName());
			System.out.println("Student name is" + student.getRollNumber());
			System.out.println("Student name is" + student.getUniversity());

			Address address = student.getAddress();
			if (address != null) {
				System.out.println(address.getCity());
				System.out.println(address.getPinCode());
			}

			tx.commit();
		} catch (Exception e) {
			e.printStackTrace();

		} finally {
			session.close();
			factory.close();
		}
	}

}

QUery generated for above HQL.

select student0_.id as id1_1_0_, address1_.id as id1_0_1_, student0_.address_id as address_5_1_0_, student0_.name as name2_1_0_, student0_.roll_number as roll_num3_1_0_, student0_.university as universi4_1_0_, address1_.city as city2_0_1_, address1_.pin_code as pin_code3_0_1_ from Student student0_ left outer join Address address1_ on student0_.address_id=address1_.id where student0_.id=?

Note – We have seen how to get the child entity (Address.java) from the parent Employee. We can also get parent entity i.e student details from child entity i.e address.

        Long id = 2l;
        String hql = "from Address address left outer join fetch address.student where address.id=:id";
        Query<Address> query = session.createQuery(hql, Address.class);
        query.setParameter("id", id);
        Address address = query.uniqueResult();
        Student student = address.getStudent();

Let’s see complete example.

package com.netsurfingzone.main;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.query.Query;

import com.netsurfingzone.entity.Address;
import com.netsurfingzone.entity.Student;

public class HibernateMain {
	public static void main(String[] args) {
		SessionFactory factory = null;
		Session session = null;
		Configuration configuration = new Configuration().configure();
		try {

			factory = configuration.buildSessionFactory();
			session = factory.openSession();
			Transaction tx = session.beginTransaction();

			Long id = 2l;
			String hql = "from Address address left outer join fetch address.student where address.id=:id";
			Query<Address> query = session.createQuery(hql, Address.class);
			query.setParameter("id", id);
			Address address = query.uniqueResult();
			Student student = address.getStudent();
			if (address != null && student != null) {
				System.out.println("City is " + address.getCity());
				System.out.println("Pincode is " + address.getPinCode());
				System.out.println("Student name is " + student.getName());
				System.out.println("Student name is " + student.getRollNumber());
				System.out.println("Student name is " + student.getUniversity());
			}

			tx.commit();
		} catch (Exception e) {
			e.printStackTrace();

		} finally {
			session.close();
			factory.close();
		}
	}

}

Pagination using Hibernate Query Language

org.hibernate.query.Query interface provides different methods for pagination support. Suppose we want to fetch records between 1 to 5 .

        Query query=session.createQuery(" from Student");
        query.setFirstResult(1);
        query.setMaxResults(5);
        List<Student> list = query.list();

Query generated for above HQL.

select student0_.id as id1_0_, student0_.name as name2_0_, student0_.roll_number as roll_num3_0_, student0_.university as universi4_0_ from Student student0_ limit ?, ?
Hibernate: select student0_.id as id1_0_, student0_.name as name2_0_, student0_.roll_number as roll_num3_0_, student0_.university as universi4_0_ from Student student0_ limit ?, ?

Aggregation example using Hibernate query language

Consider we have field age in Student entity.


@Entity
public class Student implements Serializable {
	@Id
	@GeneratedValue(strategy = GenerationType.SEQUENCE)
	private Long id;

	@Column(name = "name")
	private String name;

	@Column(name = "roll_number")
	private String rollNumber;

	@Column(name = "university")
	private String university;
	
	@Column(name = "age")
	private int age;		
}

HQL to get max age record from student table.

    	Query query =session.createQuery("select max(age) from Student");  
	List<Student> list = query.list();

HQL to get min age record from student table.

	Query query =session.createQuery("select min(age) from Student");  
	List<Student> list = query.list();

HQL to get average of age.

        Query query =session.createQuery("select avg(age) from Student");  
        List<Student> list = query.list();








Spring Boot HQL example using Spring Data JPA.

Open eclipse and create maven project, Don’t forget to check ‘Create a simple project (skip)’ and click on next. Fill all details(GroupId – springboothqlexample, ArtifactId – springboothqlexample and name – springboothqlexample) and click on finish. Keep packaging as the jar.

HQL

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>springbootmysqlcrudexample</groupId>
	<artifactId>springbootmysqlcrudexample</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>springbootmysqlcrudexample</name>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.0.2.RELEASE</version>
	</parent>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-thymeleaf</artifactId>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>
	</dependencies>
</project>

Define Entity and other classes/interfaces.

Student.java

package com.springboothql.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class Student {
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Long id;

	@Column(name = "name")
	private String name;

	@Column(name = "roll_number")
	private String rollNumber;

	@Column(name = "university")
	private String university;

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public String getUniversity() {
		return university;
	}

	public void setUniversity(String university) {
		this.university = university;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getRollNumber() {
		return rollNumber;
	}

	public void setRollNumber(String rollNumber) {
		this.rollNumber = rollNumber;
	}

}

StudentService.java

package com.springboothql.service;

import org.springframework.stereotype.Component;

import com.springboothql.entity.Student;

@Component
public interface StudentService {

	public Student save(Student student);
	public Student get(Long id);
}

StudentRepository.java

package com.springboothql.repository;

import java.io.Serializable;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import com.springboothql.entity.Student;

@Repository
public interface StudentRepository extends JpaRepository<Student, Serializable> {

}

See more details about Spring Data JPA here.

StudentServiceImpl.java

package com.springboothql.serviceimpl;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.hibernate.Session;
import org.hibernate.query.Query;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.springboothql.entity.Student;
import com.springboothql.repository.StudentRepository;
import com.springboothql.service.StudentService;

@Service
public class StudentServiceImpl implements StudentService {

	@Autowired
	private StudentRepository studentRepository;

	@PersistenceContext
	private EntityManager entityManager;

	@Transactional
	public Student save(Student student) {
		Student createResponse = studentRepository.save(student);
		return createResponse;
	}

	@Transactional
	public Student get(Long id) {
		// Get session from entityManager
		Session session = entityManager.unwrap(Session.class);
		Query<Student> query = session.createQuery("from Student where id = :id");
		query.setParameter("id", id);
		Student student = query.uniqueResult();
		return student;
	}

}

See more details about how to get session from entityManager in Spring Boot application. @Transaction – Check a separate tutorial here.

StudentController.java

package com.springboothql.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.springboothql.entity.Student;
import com.springboothql.repository.StudentRepository;
import com.springboothql.service.StudentService;

@RestController
@RequestMapping("/student")
public class StudentController {

	@Autowired
	private StudentService studentService;

	@Autowired
	private StudentRepository studentRepository;

	@PostMapping("/create")
	public Student createStudent1(@RequestBody Student student) {
		Student createResponse = studentService.save(student);
		return createResponse;
	}

	@GetMapping("/{id}")
	public Student getStudent(@PathVariable Long id) {
		Student getReponse = studentService.get(id);
		return getReponse;
	}

}

@RestController – This annotation is a combined form of @Controller and @ResponseBody. See more detail here.

Note – See here more about @Component, @Controller, @Service and @Repository annotations here.

JpaConfig.java

package com.springboothql.config;

import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

@Configuration
@EnableJpaRepositories(basePackages = "com.springboothql.repository")
public class JpaConfig {

}

@Configuration – This annotation is used for configuration purpose. See more details about @Configuration annotation here.

SpringMain.java

package com.springboothql.main;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.domain.EntityScan;
import org.springframework.context.annotation.ComponentScan;

@SpringBootApplication
@ComponentScan(basePackages = "com.springboothql.*")
@EntityScan("com.springboothql.*")
public class SpringMain {
	public static void main(String[] args) {
		SpringApplication.run(SpringMain.class, args);
	}
}

application.yml

spring:
  jpa:
    show-sql: true
    hibernate:
      ddl-auto: create
  datasource:
    url: jdbc:mysql://localhost:3306/springbootcrudexample
    username: root
    password: root
server:
   port: 9091       

Let’s test both API using psotman.

First perform save operation.

http://localhost:9091/student/create

Request data.

{

    "name": "kim",
    "rollNumber": "0126CS01",
    "university": "rgtu"
}
Hibernate Query Language example

Retrieve this record using HQL.

http://localhost:9091/student/1

HQL

That’s all about Hibernate Query Language.

You may like.

Hibernate/JPA association and inheritance mapping.

Hibenrate Query language Docs.

Download source code from github.

Summary – We learned how to write HQL queries for different scenarios. We also covered the Spring boot HQL example with Spring Data JPA and MySql.