Hibernate Named Query Example - @NamedQuery

Welcome to Hibernate Named Query Example Tutorial. We saw how we can use HQL and Native SQL Query in Hibernate. If there are a lot of queries, then they will cause a code mess because all the queries will be scattered throughout the project. That’s why Hibernate provides Named Query that we can define at a central location and use them anywhere in the code. We can created named queries for both HQL and Native SQL.

Hibernate Named Query

Hibernate Named Query can be defined in Hibernate mapping files or through the use of JPA annotations @NamedQuery and @NamedNativeQuery. Today we will look into both of them and how to use hibernate named query in a simple application. We will use the same database tables as in HQL Example, so you can check that post for database setup sql script. For our hibernate named query example project, we will use annotations for hibernate mapping. However we will create some named queries in both mapping files and in entity bean classes. Our final project structure looks like below image, we will focus mainly on the components related to Hibernate Named Query.

Hibernate Configuration XML

hibernate.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>
  <!DOCTYPE hibernate-configuration SYSTEM "https://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
	<session-factory>
		<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
		<property name="hibernate.connection.password">pankaj123</property>
		<property name="hibernate.connection.url">jdbc:mysql://localhost/TestDB</property>
		<property name="hibernate.connection.username">pankaj</property>
		<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>

		<property name="hibernate.current_session_context_class">thread</property>
		<property name="hibernate.show_sql">true</property>

		<mapping class="com.journaldev.hibernate.model.Employee" />
		<mapping class="com.journaldev.hibernate.model.Address" />
		<mapping resource="named-queries.hbm.xml" />
	</session-factory>
</hibernate-configuration>

Hibernate Named Query XML

We have a hibernate mapping file, that contains only HQL named queries and Native SQL named queries. named-queries.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
	"https://hibernate.org/dtd/hibernate-mapping-3.0.dtd">
	
<hibernate-mapping>
	<query name="HQL_GET_ALL_EMPLOYEE">from Employee</query>

	<query name="HQL_GET_EMPLOYEE_BY_ID">
		<![CDATA[from Employee where emp_id = :id]]>
	</query>

	<query name="HQL_GET_EMPLOYEE_BY_SALARY">
		<![CDATA[from Employee where emp_salary > :salary]]>
	</query>
	
	<sql-query name="SQL_GET_ALL_EMPLOYEE">
		<![CDATA[select emp_id, emp_name, emp_salary from Employee]]>
	</sql-query>
	
	<sql-query name="SQL_GET_ALL_EMP_ADDRESS">
		<![CDATA[select {e.*}, {a.*} from Employee e join Address a ON e.emp_id=a.emp_id]]>
		<return alias="e" class="com.journaldev.hibernate.model.Employee" />
		<return-join alias="a" property="e.address"></return-join>
	</sql-query>
</hibernate-mapping>

query element is used for HQL named queries and sql-query element is used for native sql named queries. We can use return element for declaring the entity to which resultset will be mapped. return-join is used when we have join of multiple tables. We should use CDATA to declare our hibernate named query to make sure it’s treated as data, otherwise < and > sings will mess up our mapping XML file.

Hibernate Named Query @NamedQuery Annotation

We have two model classes - Employee and Address. We have defined names queries in Address class as below.

package com.journaldev.hibernate.model;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.NamedNativeQueries;
import javax.persistence.NamedNativeQuery;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.OneToOne;
import javax.persistence.PrimaryKeyJoinColumn;
import javax.persistence.Table;

import org.hibernate.annotations.GenericGenerator;
import org.hibernate.annotations.Parameter;

@Entity
@Table(name = "ADDRESS")
@NamedQueries({ @NamedQuery(name = "@HQL_GET_ALL_ADDRESS", 
			query = "from Address") })
@NamedNativeQueries({ @NamedNativeQuery(name = "@SQL_GET_ALL_ADDRESS", 
			query = "select emp_id, address_line1, city, zipcode from Address") })
public class Address {

	@Id
	@Column(name = "emp_id", unique = true, nullable = false)
	@GeneratedValue(generator = "gen")
	@GenericGenerator(name = "gen", strategy = "foreign", parameters = { @Parameter(name = "property", value = "employee") })
	private long id;

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

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

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

	@OneToOne
	@PrimaryKeyJoinColumn
	private Employee employee;

	public long getId() {
		return id;
	}

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

	public String getAddressLine1() {
		return addressLine1;
	}

	public void setAddressLine1(String addressLine1) {
		this.addressLine1 = addressLine1;
	}

	public String getZipcode() {
		return zipcode;
	}

	public void setZipcode(String zipcode) {
		this.zipcode = zipcode;
	}

	public String getCity() {
		return city;
	}

	public void setCity(String city) {
		this.city = city;
	}

	public Employee getEmployee() {
		return employee;
	}

	public void setEmployee(Employee employee) {
		this.employee = employee;
	}

	@Override
	public String toString() {
		return "AddressLine1= " + addressLine1 + ", City=" + city
				+ ", Zipcode=" + zipcode;
	}
}

Hibernate Named Query Test Program

Let’s write a test program to use all the hibernate named queries defined above.

package com.journaldev.hibernate.main;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;

import com.journaldev.hibernate.model.Address;
import com.journaldev.hibernate.model.Employee;
import com.journaldev.hibernate.util.HibernateUtil;

public class HibernateNamedQueryExample {

	@SuppressWarnings("unchecked")
	public static void main(String[] args) {

		// Prep work
		SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
		Session session = sessionFactory.getCurrentSession();
		Transaction tx = session.beginTransaction();

		//HQL Named Query Example
		Query query = session.getNamedQuery("HQL_GET_ALL_EMPLOYEE");
		List<Employee> empList = query.list();
		for (Employee emp : empList) {
			System.out.println("List of Employees::" + emp.getId() + ","
					+ emp.getAddress().getCity());
		}

		query = session.getNamedQuery("HQL_GET_EMPLOYEE_BY_ID");
		query.setInteger("id", 2);
		Employee emp = (Employee) query.uniqueResult();
		System.out.println("Employee Name=" + emp.getName() + ", City="
				+ emp.getAddress().getCity());

		query = session.getNamedQuery("HQL_GET_EMPLOYEE_BY_SALARY");
		query.setInteger("salary", 200);
		empList = query.list();
		for (Employee emp1 : empList) {
			System.out.println("List of Employees::" + emp1.getId() + ","
					+ emp1.getSalary());
		}

		query = session.getNamedQuery("@HQL_GET_ALL_ADDRESS");
		List<Address> addressList = query.list();
		for (Address addr : addressList) {
			System.out.println("List of Address::" + addr.getId() + "::"
					+ addr.getZipcode() + "::" + addr.getEmployee().getName());
		}
		
		//Native SQL Named Query Example
		query = session.getNamedQuery("@SQL_GET_ALL_ADDRESS");
		List<Object[]> addressObjArray = query.list();
		for(Object[] row : addressObjArray){
			for(Object obj : row){
				System.out.print(obj + "::");
			}
			System.out.println("\n");
		}
		
		query = session.getNamedQuery("SQL_GET_ALL_EMP_ADDRESS");
		addressObjArray = query.list();
		for(Object[] row : addressObjArray){
			Employee e = (Employee) row[0];
			System.out.println("Employee Info::"+e);
			Address a = (Address) row[1];
			System.out.println("Address Info::"+a);
		}
		// rolling back to save the test data
		tx.commit();

		// closing hibernate resources
		sessionFactory.close();
	}

}

When we execute above program with test data we have, it produces following output.

Hibernate: select employee0_.emp_id as emp_id1_1_, employee0_.emp_name as emp_name2_1_, employee0_.emp_salary as emp_sala3_1_ from EMPLOYEE employee0_
Hibernate: select address0_.emp_id as emp_id1_0_0_, address0_.address_line1 as address_2_0_0_, address0_.city as city3_0_0_, address0_.zipcode as zipcode4_0_0_, employee1_.emp_id as emp_id1_1_1_, employee1_.emp_name as emp_name2_1_1_, employee1_.emp_salary as emp_sala3_1_1_ from ADDRESS address0_ left outer join EMPLOYEE employee1_ on address0_.emp_id=employee1_.emp_id where address0_.emp_id=?
Hibernate: select address0_.emp_id as emp_id1_0_0_, address0_.address_line1 as address_2_0_0_, address0_.city as city3_0_0_, address0_.zipcode as zipcode4_0_0_, employee1_.emp_id as emp_id1_1_1_, employee1_.emp_name as emp_name2_1_1_, employee1_.emp_salary as emp_sala3_1_1_ from ADDRESS address0_ left outer join EMPLOYEE employee1_ on address0_.emp_id=employee1_.emp_id where address0_.emp_id=?
Hibernate: select address0_.emp_id as emp_id1_0_0_, address0_.address_line1 as address_2_0_0_, address0_.city as city3_0_0_, address0_.zipcode as zipcode4_0_0_, employee1_.emp_id as emp_id1_1_1_, employee1_.emp_name as emp_name2_1_1_, employee1_.emp_salary as emp_sala3_1_1_ from ADDRESS address0_ left outer join EMPLOYEE employee1_ on address0_.emp_id=employee1_.emp_id where address0_.emp_id=?
Hibernate: select address0_.emp_id as emp_id1_0_0_, address0_.address_line1 as address_2_0_0_, address0_.city as city3_0_0_, address0_.zipcode as zipcode4_0_0_, employee1_.emp_id as emp_id1_1_1_, employee1_.emp_name as emp_name2_1_1_, employee1_.emp_salary as emp_sala3_1_1_ from ADDRESS address0_ left outer join EMPLOYEE employee1_ on address0_.emp_id=employee1_.emp_id where address0_.emp_id=?
List of Employees::1,San Jose
List of Employees::2,Santa Clara
List of Employees::3,Bangalore
List of Employees::4,New Delhi
Hibernate: select employee0_.emp_id as emp_id1_1_, employee0_.emp_name as emp_name2_1_, employee0_.emp_salary as emp_sala3_1_ from EMPLOYEE employee0_ where emp_id=?
Employee Name=David, City=Santa Clara
Hibernate: select employee0_.emp_id as emp_id1_1_, employee0_.emp_name as emp_name2_1_, employee0_.emp_salary as emp_sala3_1_ from EMPLOYEE employee0_ where emp_salary>?
List of Employees::3,300.0
List of Employees::4,400.0
Hibernate: select address0_.emp_id as emp_id1_0_, address0_.address_line1 as address_2_0_, address0_.city as city3_0_, address0_.zipcode as zipcode4_0_ from ADDRESS address0_
List of Address::1::95129::Pankaj
List of Address::2::95051::David
List of Address::3::560100::Lisa
List of Address::4::100100::Jack
Hibernate: select emp_id, address_line1, city, zipcode from Address
1::Albany Dr::San Jose::95129::

2::Arques Ave::Santa Clara::95051::

3::BTM 1st Stage::Bangalore::560100::

4::City Centre::New Delhi::100100::

Hibernate: select e.emp_id as emp_id1_1_0_, e.emp_name as emp_name2_1_0_, e.emp_salary as emp_sala3_1_0_, a.emp_id as emp_id1_0_1_, a.address_line1 as address_2_0_1_, a.city as city3_0_1_, a.zipcode as zipcode4_0_1_ from Employee e join Address a ON e.emp_id=a.emp_id
Employee Info::Id= 1, Name= Pankaj, Salary= 100.0, {Address= AddressLine1= Albany Dr, City=San Jose, Zipcode=95129}
Address Info::AddressLine1= Albany Dr, City=San Jose, Zipcode=95129
Employee Info::Id= 2, Name= David, Salary= 200.0, {Address= AddressLine1= Arques Ave, City=Santa Clara, Zipcode=95051}
Address Info::AddressLine1= Arques Ave, City=Santa Clara, Zipcode=95051
Employee Info::Id= 3, Name= Lisa, Salary= 300.0, {Address= AddressLine1= BTM 1st Stage, City=Bangalore, Zipcode=560100}
Address Info::AddressLine1= BTM 1st Stage, City=Bangalore, Zipcode=560100
Employee Info::Id= 4, Name= Jack, Salary= 400.0, {Address= AddressLine1= City Centre, City=New Delhi, Zipcode=100100}
Address Info::AddressLine1= City Centre, City=New Delhi, Zipcode=100100

Hibernate Named Query Important Points

Few important points about Hibernate Named Query are;

1. Hibernate Named Query helps us in grouping queries at a central location rather than letting them scattered all over the code.
2. Hibernate Named Query syntax is checked when the hibernate session factory is created, thus making the application fail fast in case of any error in the named queries.
3. Hibernate Named Query is global, means once defined it can be used throughout the application.
4. One of the major disadvantage of Named query is that it’s hard to debug, because we need to find out the location where it’s defined.

That’s all for the Hibernate Named Query Example, you can download the sample project from below link.

Download Hibernate Named Query Project