Hibernate 5 - Native SQL query example

Posted on June 3, 2018


Hibernate allows us to execute the native SQL queries for all create, update, delete and retrieve operations. This is useful if you want to improve the performance of your application using database specific queries. 

In hibernate, you can execute your native SQL queries using the Session.createNativeQuery() method.

In this post, you will learn how to -

  • Execute simple and complex native SQL query in hibernate application.
  • Map native SQL query result to an @Entity object.
  • Use parameter binding in native SQL queries.

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 and Entity class

Before we start working on native SQL queries, we need to create the database tables and @Entity classes for our examples.

So first 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);

Now, create Department and Employee entities to map with DEPARTMENT and EMPLOYEE tables respectively. 

Department.java

package com.boraji.tutorial.hibernate.entity;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;
@Entity
@Table(name="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
}

Employee.java

package com.boraji.tutorial.hibernate.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

@Entity
@Table(name = "EMPLOYEE")
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
}

 

Hibernate Utility class

Create a helper class HibernateUtil to bootstrap hibernate application.

Map the Department and Employee entities using the #MetadataSources.addAnnotatedClass() method.

HibernateUtil.java

package com.boraji.tutorial.hibernate;

import java.util.HashMap;
import java.util.Map;

import org.hibernate.SessionFactory;
import org.hibernate.boot.Metadata;
import org.hibernate.boot.MetadataSources;
import org.hibernate.boot.registry.StandardServiceRegistry;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Environment;

import com.boraji.tutorial.hibernate.entity.Department;
import com.boraji.tutorial.hibernate.entity.Employee;

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 native SQL query in Hibernate

The following example demonstrates how to use the session.createNativeQuery() method to execute the simple and complex queries in hibernate.

NativeQueryExample.java

package com.boraji.tutorial.hibernate;

import java.util.List;

import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.type.IntegerType;
import org.hibernate.type.StringType;

public class NativeQueryExample {
   @SuppressWarnings("unchecked")
   public static void main(String[] args) {
      Session session = null;
      Transaction transaction = null;
      try {
         session = HibernateUtil.getSessionFactory().openSession();
         transaction = session.beginTransaction();

         // Native query selecting all columns
         List<Object[]> departments = session.createNativeQuery("SELECT * FROM department").list();
         for (Object[] objects : departments) {
            Integer id=(Integer)objects[0];
            String name=(String)objects[1];
            System.out.println("Department["+id+","+name+"]");
         }

         //Native query with custom column selection (scaler query)
         System.out.println("--------------------------------------------------------------");
         List<Object[]> employees = session.createNativeQuery("SELECT * FROM employee")
               .addScalar("emp_id", IntegerType.INSTANCE)
               .addScalar( "name", StringType.INSTANCE )
               .addScalar( "designation", StringType.INSTANCE )
               .list();
         for (Object[] objects : employees) {
            Integer id=(Integer)objects[0];
            String name=(String)objects[1];
            String designation=(String)objects[2];
            System.out.println("Employee["+id+","+name+","+designation+"]");
         }
         
         //Native query with JOIN
         System.out.println("--------------------------------------------------------------");
         List<Object[]> empDepts=session.createNativeQuery(""
               + "select e.name as emp_name, e.designation, d.name as dep_name "
               + "from employee e inner join department d "
               + "on e.dpt_id=d.dpt_id").list();
         for (Object[] objects : empDepts) {
            String employee=(String)objects[0];
            String designation=(String)objects[1];
            String department=(String)objects[2];
            System.out.println("Employee["+employee+","+designation+","+department+"]");
         }
         
         transaction.commit();
      } catch (Exception e) {
         e.printStackTrace();
      } finally {
         if (session != null) {
            session.close();
         }
      }

      HibernateUtil.shutdown();
   }
}

Output

Hibernate: SELECT * FROM department
Department[1,Software Development]
Department[2,Human Resources]
--------------------------------------------------------------
Hibernate: SELECT * FROM employee
Employee[1,Mike,Software Developer]
Employee[2,David,Team Lead]
Employee[3,Peter,Manager]
Employee[4,Andrew,VP]
--------------------------------------------------------------
Hibernate: select e.name as emp_name, e.designation, d.name as dep_name from employee e inner join department d on e.dpt_id=d.dpt_id
Employee[Mike,Software Developer,Software Development]
Employee[David,Team Lead,Software Development]
Employee[Peter,Manager,Human Resources]
Employee[Andrew,VP,Human Resources]

 

Mapping native SQL query result to Entity object

The following example demonstrates how to map the native SQL query result to an entity object by specifying the second parameter in  the session.createNativeQuery() method or using the NativeQuery.addEntity() method.

NativeQueryEntityExample.java

package com.boraji.tutorial.hibernate;

import java.util.List;

import org.hibernate.Session;
import org.hibernate.Transaction;

import com.boraji.tutorial.hibernate.entity.Department;
import com.boraji.tutorial.hibernate.entity.Employee;

public class NativeQueryEntityExample {
   @SuppressWarnings("unchecked")
   public static void main(String[] args) {
      Session session = null;
      Transaction transaction = null;
      try {
         session = HibernateUtil.getSessionFactory().openSession();
         transaction = session.beginTransaction();

         //Mapping Native query to Entity
         List<Employee> employees = session.createNativeQuery("SELECT * FROM employee",Employee.class)
               .list();
         for (Employee employee : employees) {
            Integer id=employee.getId();
            String name=employee.getName();
            String designation=employee.getDesignation();
            System.out.println("Employee["+id+","+name+","+designation+"]");
         }
         
         //Mapping Native JOIN query to Entities
         System.out.println("--------------------------------------------------------------");
         List<Object[]> departments=session.createNativeQuery(""
               + "select e.*, d.* "
               + "from department d inner join  employee e "
               + "on d.dpt_id=e.dpt_id")
               .addEntity("d", Department.class)
               .addJoin("e", "d.employees")
               .list();
         
         for (Object[] object : departments) {
            Department department=(Department)object[0];
            System.out.println("Department - "+department.getName());
            for (Employee employee : department.getEmployees()) {
               System.out.println("\t Employee - "+employee.getName()+" \t Designation - "+employee.getDesignation());
            }
         }
         
         transaction.commit();
      } catch (Exception e) {
         e.printStackTrace();
      } finally {
         if (session != null) {
            session.close();
         }
      }

      HibernateUtil.shutdown();
   }
}

Output

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=?
Employee[1,Mike,Software Developer]
Employee[2,David,Team Lead]
Employee[3,Peter,Manager]
Employee[4,Andrew,VP]
--------------------------------------------------------------
Hibernate: select e.*, d.* from department d inner join  employee e on d.dpt_id=e.dpt_id
Department - Software Development
	 Employee - Mike 	 Designation - Software Developer
	 Employee - David 	 Designation - Team Lead
Department - Software Development
	 Employee - Mike 	 Designation - Software Developer
	 Employee - David 	 Designation - Team Lead
Department - Human Resources
	 Employee - Peter 	 Designation - Manager
	 Employee - Andrew 	 Designation - VP
Department - Human Resources
	 Employee - Peter 	 Designation - Manager
	 Employee - Andrew 	 Designation - VP

Parameter binding with native SQL query

This example demonstrates how to use the positional and named parameter binding with native SQL queries.

NativeQueryParamExample.java

package com.boraji.tutorial.hibernate;

import java.util.List;

import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.type.IntegerType;
import org.hibernate.type.StringType;

import com.boraji.tutorial.hibernate.entity.Department;

public class NativeQueryParamExample {
   @SuppressWarnings("unchecked")
   public static void main(String[] args) {
      Session session = null;
      Transaction transaction = null;
      try {
         session = HibernateUtil.getSessionFactory().openSession();
         transaction = session.beginTransaction();

         //Binding parameter for native query
         List<Object[]> employees = session.createNativeQuery("SELECT * FROM employee where dpt_id=?")
               .addScalar("emp_id", IntegerType.INSTANCE)
               .addScalar( "name", StringType.INSTANCE )
               .addScalar( "designation", StringType.INSTANCE )
               .setParameter(1, 2) //positional parameter binding 
               .list();
         for (Object[] objects : employees) {
            Integer id=(Integer)objects[0];
            String name=(String)objects[1];
            String designation=(String)objects[2];
            System.out.println("Employee["+id+","+name+","+designation+"]");
         }
         
         //Mapping Native query with Entity
         System.out.println("--------------------------------------------------------------");
         List<Department> departments = session.createNativeQuery("SELECT * FROM department where name like :deptName")
               .addEntity(Department.class)
               .setParameter("deptName", "H%") //named parameter binding 
               .list();
         for (Department department : departments) {
            System.out.println("Department["+department.getId()+","+department.getName()+"]");
         }
         
         transaction.commit();
      } catch (Exception e) {
         e.printStackTrace();
      } finally {
         if (session != null) {
            session.close();
         }
      }

      HibernateUtil.shutdown();
   }
}

Output

Hibernate: SELECT * FROM employee where dpt_id=?
Employee[3,Peter,Manager]
Employee[4,Andrew,VP]
--------------------------------------------------------------
Hibernate: SELECT * FROM department where name like ?
Department[2,Human Resources]