Hibernate 5 - Named query example

Posted on June 9, 2018


In Hibernate, a named query is a JPQL or SQL expression with predefined unchangeable query string. You can define a named query either in hibernate mapping file or in an entity class.

Annotations @NamedQuery and @NamedQueries are used for HQL or JPQL expression whereas @NamedNativeQuery and @NamedNativeQueries are used for native SQL expression.

This post shows you how to use the named queries annotations in hibernation application.

Tools and technologies used for this application are - 

  • Hibernate ORM 5.2.17.Final
  • JAXB API 2.3.0
  • JavaSE 9
  • MySQL Server 5.7.12
  • Eclipse Oxygen.1
  • Maven 3.3.9

Jar dependencies

Add the following jar dependencies in pom.xml file of your maven project.

<dependencies>
   <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate-core</artifactId>
      <version>5.2.17.Final</version>
   </dependency>
   <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>6.0.6</version>
   </dependency>
   <dependency>
      <groupId>javax.xml.bind</groupId>
      <artifactId>jaxb-api</artifactId>
      <version>2.3.0</version>
   </dependency>
</dependencies>

Database table creation

Before we start working on named queries, we need to create the database tables for our examples. So, execute the following DDL and DML statements in your MySQL server.

create table DEPARTMENT(
	DPT_ID int not null primary key, 
    NAME varchar(100) not null
);

create table EMPLOYEE(
	EMP_ID int not null primary key,
    NAME varchar(100) not null,
    DESIGNATION varchar(100),
    DPT_ID int,
    foreign key (DPT_ID) references DEPARTMENT(DPT_ID)
);

insert into DEPARTMENT(DPT_ID,NAME) values(1,'Software Development');
insert into DEPARTMENT(DPT_ID,NAME) values(2,'Human Resources');
insert into EMPLOYEE(EMP_ID,NAME,DESIGNATION,DPT_ID) values(1,'Mike','Software Developer',1);
insert into EMPLOYEE(EMP_ID,NAME,DESIGNATION,DPT_ID) values(2,'David','Team Lead',1);
insert into EMPLOYEE(EMP_ID,NAME,DESIGNATION,DPT_ID) values(3,'Peter','Manager',2);
insert into EMPLOYEE(EMP_ID,NAME,DESIGNATION,DPT_ID) values(4,'Andrew','VP',2);

Hibernate Utility class

Create a helper class HibernateUtil to bootstrap hibernate application.

HibernateUtil.java

package com.boraji.tutorial.hibernate;

import java.util.*;
import org.hibernate.*;
import org.hibernate.boot.*;
import org.hibernate.boot.registry.*;
import org.hibernate.cfg.*;
import com.boraji.tutorial.hibernate.entity.*;

public class HibernateUtil {
   private static StandardServiceRegistry registry;
   private static SessionFactory sessionFactory;

   public static SessionFactory getSessionFactory() {
      if (sessionFactory == null) {
         try {
            StandardServiceRegistryBuilder registryBuilder = new StandardServiceRegistryBuilder();

            //Configuration properties
            Map<String, Object> settings = new HashMap<>();
            settings.put(Environment.DRIVER, "com.mysql.cj.jdbc.Driver");
            settings.put(Environment.URL, "jdbc:mysql://localhost:3306/BORAJI?useSSL=false");
            settings.put(Environment.USER, "root");
            settings.put(Environment.PASS, "admin");
            settings.put(Environment.HBM2DDL_AUTO, "validate");
            settings.put(Environment.SHOW_SQL, true);
            
            registryBuilder.applySettings(settings);
            registry = registryBuilder.build();
            
            MetadataSources sources = new MetadataSources(registry);
            sources.addAnnotatedClass(Department.class);
            sources.addAnnotatedClass(Employee.class);
            Metadata metadata = sources.getMetadataBuilder().build();
            
            sessionFactory = metadata.getSessionFactoryBuilder().build();
         } catch (Exception e) {
            if (registry != null) {
               StandardServiceRegistryBuilder.destroy(registry);
            }
            e.printStackTrace();
         }
      }
      return sessionFactory;
   }

   public static void shutdown() {
      if (registry != null) {
         StandardServiceRegistryBuilder.destroy(registry);
      }
   }
}

 

Using @NamedQuery and @NamedQueries annotations example

Create an @Entity class and annotate it with the @NamedQuery and @NamedQueries annotation for using single or multiple named HQL/JPQL query expression as follows.

Department.java

package com.boraji.tutorial.hibernate.entity;

import java.util.*;
import javax.persistence.*;

@Entity
@Table(name="DEPARTMENT")

//Using @NamedQuery for single JPQL or HQL
@NamedQuery(name="get_total_dept", query="select count(1) from Department")

//Using @NamedQueries for multiple JPQL or HQL
@NamedQueries({
   @NamedQuery(name="get_dept_name_by_id", query="select name from Department where id=:id"),
   @NamedQuery(name="get_all_dept", query="from Department")
})

public class Department {
   @Id
   @Column(name = "DPT_ID")
   private int id;

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

   @OneToMany(cascade = CascadeType.ALL, mappedBy = "department")
   private List<Employee> employees = new ArrayList<>();

   // Getter and Setter methods
}

Now, create a main class to execute the named query using the Session.createNamedQuery() method as follows.

NamedQueryExample.java

package com.boraji.tutorial.hibernate;
import java.util.*;
import org.hibernate.*;
import com.boraji.tutorial.hibernate.entity.*;

public class NamedQueryExample {
   public static void main(String[] args) {
      Session session = null;
      Transaction transaction = null;
      try {
         session = HibernateUtil.getSessionFactory().openSession();
         transaction = session.beginTransaction();
         
         // Executing named queries
         
         List<Long> totalDept=session.createNamedQuery("get_total_dept",Long.class).getResultList();
         System.out.println("Total Department: "+totalDept.get(0));
         
         List<String> deptName=session.createNamedQuery("get_dept_name_by_id",String.class)
               .setParameter("id", 2)
               .getResultList();
         for (Object object : deptName) {
            System.out.println(object);
         }
         
         List<Department> departments=session.createNamedQuery("get_all_dept",Department.class)
               .getResultList();
         for (Department department : departments) {
            System.out.println("ID : "+department.getId()+" \tNAME : "+department.getName());
         }
         transaction.commit();
      } catch (Exception e) {
         e.printStackTrace();
      } finally {
         if (session != null) {
            session.close();
         }
      }
      HibernateUtil.shutdown();
   }
}

Output

Hibernate: select count(1) as col_0_0_ from DEPARTMENT department0_
Total Department: 2
Hibernate: select department0_.NAME as col_0_0_ from DEPARTMENT department0_ where department0_.DPT_ID=?
Human Resources
Hibernate: select department0_.DPT_ID as DPT_ID1_0_, department0_.NAME as NAME2_0_ from DEPARTMENT department0_
ID : 1 	NAME : Software Development
ID : 2 	NAME : Human Resources

 

Using @NamedNativeQuery and @NamedNativeQueries annotations example

Create an @Entity class and annotate it with the @NamedNativeQuery and @NamedNativeQueries annotation for using single or multiple named native SQL query expression as follows.

Employee.java

package com.boraji.tutorial.hibernate.entity;

import javax.persistence.*;

@Entity
@Table(name = "EMPLOYEE")

// Using @NamedNativeQuery for single native sql query
@NamedNativeQuery(name = "get_total_emp", query = "select count(1) from EMPLOYEE")

// Using @NamedNativeQuery for multiple native sql queries
@NamedNativeQueries({
      // Using @NamedNativeQuery with parameter binding
      @NamedNativeQuery(name = "get_total_emp_by_dept", query = "select count(1) from EMPLOYEE where dpt_id=:did"),

      @NamedNativeQuery(name = "get_all_emp", query = "select * from EMPLOYEE",resultClass=Employee.class) 
  })
public class Employee {

   @Id
   @Column(name = "EMP_ID")
   private int id;

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

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

   @ManyToOne
   @JoinColumn(name = "DPT_ID")
   private Department department;

   // Getter and Setter methods

}

The following example shows how to execute the named native queries, defined in the Employee entity class, in hibernate.

NamedNativeQueryExample.java

package com.boraji.tutorial.hibernate;
import java.util.*;
import org.hibernate.*;
import com.boraji.tutorial.hibernate.entity.*;

public class NamedNativeQueryExample {
   @SuppressWarnings("unchecked")
   public static void main(String[] args) {
      Session session = null;
      Transaction transaction = null;
      try {
         session = HibernateUtil.getSessionFactory().openSession();
         transaction = session.beginTransaction();
         
         // Executing named native queries 
         
         List<Object> totalEmp=session.createNamedQuery("get_total_emp").getResultList();
         System.out.println("Total Employees : "+totalEmp.get(0));
         
         List<Object> totalEmpByDept=session.createNamedQuery("get_total_emp_by_dept")
               .setParameter("did", 2)
               .getResultList();
         System.out.println("Total Employees : "+totalEmpByDept.get(0));
         
         List<Employee> employees=session.createNamedQuery("get_all_emp",Employee.class)
               .getResultList();
         for (Employee employee : employees) {
            System.out.println("EMP ID : "+employee.getId()+"\t NAME : "+employee.getName()+"\tDESIGNATION : "+employee.getDesignation());
            Department department=employee.getDepartment();
            System.out.println("\t Department : "+department.getName());
         }
         transaction.commit();
      } catch (Exception e) {
         e.printStackTrace();
      } finally {
         if (session != null) {
            session.close();
         }
      }
      HibernateUtil.shutdown();
   }
}

Output

Hibernate: select count(1) from EMPLOYEE
Total Employees : 4
Hibernate: select count(1) from EMPLOYEE where dpt_id=?
Total Employees : 2
Hibernate: select * from EMPLOYEE
Hibernate: select department0_.DPT_ID as DPT_ID1_0_0_, department0_.NAME as NAME2_0_0_ from DEPARTMENT department0_ where department0_.DPT_ID=?
Hibernate: select department0_.DPT_ID as DPT_ID1_0_0_, department0_.NAME as NAME2_0_0_ from DEPARTMENT department0_ where department0_.DPT_ID=?
EMP ID : 1	 NAME : Mike	DESIGNATION : Software Developer
	 Department : Software Development
EMP ID : 2	 NAME : David	DESIGNATION : Team Lead
	 Department : Software Development
EMP ID : 3	 NAME : Peter	DESIGNATION : Manager
	 Department : Human Resources
EMP ID : 4	 NAME : Andrew	DESIGNATION : VP
	 Department : Human Resources
Download Sources