JPA native query example – @NamedNativeQuery example

In this JPA native query example, we will learn to use JPA native query (SQL SELECT query) using createNativeQuery() method of the EntityManager interface. We will pass in the query string to be executed in underlying database and the entity type that will be returned as result. We will also use named sql native queries in this example.

Native query vs named query

1. Native query refers to actual sql queries (referring to actual database objects). These queries are the sql statements which can be directly executed in database using a database client.

2. Named query is the way you define your query by giving it a name. You could define this in mapping file in hibernate or also using annotations at entity level.

1. Define JPA native queries

Named SQL queries are defined using the @NamedNativeQuery annotation. This annotation may be placed on any entity and defines the name of the query as well as the query text. Like JPQL named queries, the name of the query must be unique within the persistence unit.

Named SQL native queries are defined like this:

@Entity(name="EmployeeEntity")
@Table (name="employee")

@NamedNativeQueries({
	@NamedNativeQuery(
		name = "getAllEmployees",
		query = "SELECT id, firstName, lastName, email, department.id, department.name " +
					"FROM employee, department",
					resultClass=EmployeeEntity.class
	),
	@NamedNativeQuery(
		name = "getAllEmployeesByDeptId",
		query = "SELECT id, firstName, lastName, email, department.id, department.name " +
					"FROM employee, department " + 
					"WHERE department.id = ?",
					resultClass=EmployeeEntity.class
	)
})

public class EmployeeEntity implements Serializable
{
	//more code
}	

Here we have defined two named queries getAllEmployees and getAllEmployeesByDeptId. First query returns all employees data, and second one will result all employees for a department id.

2. Execute native query

To execute above SQL queries, you will need to write below code in your DAOImpl class.

@Override
public List<EmployeeEntity> getAllEmployees() {
	List<EmployeeEntity> employees = manager.createNamedQuery("getAllEmployees", EmployeeEntity.class)
											.getResultList();
	return employees;
}

@Override
public List<EmployeeEntity> getAllEmployeesByDeptId(Integer id) {
	List<EmployeeEntity> employees = manager.createNamedQuery("getAllEmployeesByDeptId", EmployeeEntity.class)
											.setParameter(1, id)
											.getResultList();
	return employees;
}
One thing to be careful of with SQL queries that return entities is that the resulting entity instances become managed by the persistence context, just like the results of a JPQL query. If you modify one of the returned entities, it will be written to the database when the persistence context becomes associated with a transaction.

So it is important to ensure that all the necessary data required to fully construct the entity is part of the query. If you leave out a field from the query, or default it to some value and then modify the resulting entity, there is a possibility that you will overwrite the correct version already stored in the database.

There are two benefits to getting managed entities back from a SQL query.

  1. The first is that a SQL query can replace an existing JP QL query and that application code should still work without changes.
  2. The second benefit is that it allows the developer to use SQL queries as a method of constructing new entity instances from tables that may not have any object-relational mapping.

    For example, in many database architectures, there is a staging area to hold data that has not yet been verified or requires some kind of transformation before it can be moved to its final location.

Using JPA, a developer could start a transaction, query the staged data to construct entities, perform any required changes, and then commit. The newly created entities will get written to the tables mapped by the entity, not the staging tables used in the SQL query.

3. JPA Native Query Example

3.1. Entity classes

package com.howtodoinjava.jpa.demo.entity;

import java.io.Serializable;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
import javax.persistence.NamedNativeQueries;
import javax.persistence.NamedNativeQuery;
import javax.persistence.Table;
 
@Entity(name="EmployeeEntity")
@Table (name="employee")

@NamedNativeQueries({
		@NamedNativeQuery(
				name	=	"getAllEmployees",
				query	=	"SELECT id, firstName, lastName, email, department.id, department.name " +
							"FROM employee, department",
							resultClass=EmployeeEntity.class
		),
		@NamedNativeQuery(
				name	=	"getAllEmployeesByDeptId",
				query	=	"SELECT id, firstName, lastName, email, department.id, department.name " +
							"FROM employee, department " + 
							"WHERE department.id = ?",
							resultClass=EmployeeEntity.class
		)
})

public class EmployeeEntity implements Serializable
{
    private static final long serialVersionUID = 1L;
 
    @Id
    @GeneratedValue
    private Integer id;
     
    private String firstName;
    private String lastName;
    private String email;
     
    @ManyToOne
    private DepartmentEntity department;
     
    public EmployeeEntity() {}
      
    public EmployeeEntity(String name, DepartmentEntity department) {
        this.firstName = name;
        this.department = department;
    }
      
    public EmployeeEntity(String name) {
        this.firstName = name;
    }
 
    //Setters and Getters

    @Override
    public String toString() {
        return "EmployeeVO [id=" + id + ", firstName=" + firstName
                + ", lastName=" + lastName + ", email=" + email
                + ", department=" + department + "]";
    }
}
package com.howtodoinjava.jpa.demo.entity;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

@Entity(name="DepartmentEntity")
@Table (name="department")
public class DepartmentEntity implements Serializable {
 
    private static final long serialVersionUID = 1L;
     
    @Id
    @GeneratedValue
    private Integer id;
    private String name;
     
    public DepartmentEntity(){
    }
 
    public DepartmentEntity(String name) {
        super();
        this.name = name;
    }
     
    @OneToMany(mappedBy="department",cascade=CascadeType.PERSIST)
    private List<EmployeeEntity> employees = new ArrayList<EmployeeEntity>();
     
    //Setters and Getters
	
	 @Override
	public String toString() {
		return "DepartmentVO [id=" + id + ", name=" + name + "]";
	}
}

3.2. DAO

public interface DepartmentDAO 
{
	public boolean addDepartment(DepartmentEntity dept);
}
package com.howtodoinjava.jpa.demo.dao;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import com.howtodoinjava.jpa.demo.entity.DepartmentEntity;

@Repository
@Transactional
public class DepartmentDAOImpl implements DepartmentDAO {

	@PersistenceContext
	private EntityManager manager;

	@Override
	public boolean addDepartment(DepartmentEntity dept) {
		try {
			manager.persist(dept);
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}
}
public interface EmployeeDAO 
{
	public List<EmployeeEntity> getAllEmployees();
	
	public List<EmployeeEntity> getAllEmployeesByDeptId(Integer id);

	public boolean addEmployee(EmployeeEntity employee);
}
package com.howtodoinjava.jpa.demo.dao;

import java.util.List;

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

import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.howtodoinjava.jpa.demo.entity.EmployeeEntity;

@Repository
@Transactional
public class EmployeeDAOImpl implements EmployeeDAO {
	
	@PersistenceContext
    private EntityManager manager;

	@Override
	public List<EmployeeEntity> getAllEmployees() {
		List<EmployeeEntity> employees = manager.createNamedQuery("getAllEmployees", EmployeeEntity.class)
												.getResultList();
        return employees;
	}

	@Override
	public List<EmployeeEntity> getAllEmployeesByDeptId(Integer id) {
		List<EmployeeEntity> employees = manager.createNamedQuery("getAllEmployeesByDeptId", EmployeeEntity.class)
												.setParameter(1, id)
												.getResultList();
        return employees;
	}

	@Override
	public boolean addEmployee(EmployeeEntity employee) {
		try{
			manager.persist(employee);
		}catch(Exception e){
			e.printStackTrace();
			return false;
		}
		return true;
	}
}

3.3. Demo

package com.jpa.demo.test;

import java.util.List;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.annotation.Rollback;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.transaction.annotation.Transactional;
import com.howtodoinjava.jpa.demo.dao.DepartmentDAO;
import com.howtodoinjava.jpa.demo.dao.EmployeeDAO;
import com.howtodoinjava.jpa.demo.entity.DepartmentEntity;
import com.howtodoinjava.jpa.demo.entity.EmployeeEntity;

@ContextConfiguration(locations = "classpath:application-context-test.xml")
@RunWith(SpringJUnit4ClassRunner.class)
public class TestEmployeeDAO 
{
	
	@Autowired
	private EmployeeDAO employeeDAO;
	
	@Autowired
	private DepartmentDAO departmentDAO;
	
	@Test
	@Transactional
	@Rollback(true)
	public void testGetAllEmployees()
	{
		//Setup some test data in IM (in-memory) database
		setupData();
		
		List<EmployeeEntity> employees = employeeDAO.getAllEmployees();
		
		//Validate that data is found
		Assert.assertEquals(employees.size(), 1);
		EmployeeEntity employeeEntity = employees.get(0);
		
		//Now check if we got correct data
		Assert.assertEquals(employeeEntity.getFirstName(),"Lokesh");
		Assert.assertEquals(employeeEntity.getLastName(),"Gupta");
		Assert.assertEquals(employeeEntity.getDepartment().getName(),"Human Resource");
	}
	
	@Test
	@Transactional
	@Rollback(true)
	public void testGetAllEmployeesByDeptId()
	{
		//Setup some test data in IM (in-memory) database
		setupData();
		
		List<EmployeeEntity> employees = employeeDAO.getAllEmployeesByDeptId(1);
		
		//Validate that data is found
		Assert.assertEquals(employees.size(), 1);
		EmployeeEntity employeeEntity = employees.get(0);
		
		//Now check if we got correct data
		Assert.assertEquals(employeeEntity.getFirstName(),"Lokesh");
		Assert.assertEquals(employeeEntity.getLastName(),"Gupta");
		Assert.assertEquals(employeeEntity.getDepartment().getName(),"Human Resource");
	}
	
	public void setupData()
	{
		DepartmentEntity department = new DepartmentEntity("Human Resource");
		departmentDAO.addDepartment(department);
		
		EmployeeEntity employee = new EmployeeEntity();
		employee.setFirstName("Lokesh");
		employee.setLastName("Gupta");
		employee.setEmail("howtodoinjava@gmail.com");
		employee.setDepartment(department);
		
		employeeDAO.addEmployee(employee);
	}
}

Program output.

Hibernate: drop table department if exists
Hibernate: drop table employee if exists
Hibernate: create table department (id integer generated by default as identity (start with 1), name varchar(255), primary key (id))
Hibernate: create table employee (id integer generated by default as identity (start with 1), email varchar(255), firstName varchar(255), lastName varchar(255), department_id integer, primary key (id))
Hibernate: alter table employee add constraint FK4722E6AE5591DEEE foreign key (department_id) references department
HHH000230: Schema export complete
Hibernate: insert into department (id, name) values (default, ?)
binding parameter [1] as [VARCHAR] - Human Resource
Hibernate: insert into employee (id, department_id, email, firstName, lastName) values (default, ?, ?, ?, ?)
binding parameter [1] as [INTEGER] - 1
binding parameter [2] as [VARCHAR] - howtodoinjava@gmail.com
binding parameter [3] as [VARCHAR] - Lokesh
binding parameter [4] as [VARCHAR] - Gupta
Hibernate: SELECT id, firstName, lastName, email, department.id, department.name FROM employee, department WHERE department.id = ?
binding parameter [1] as [INTEGER] - 1
Found [1] as column [id]
Hibernate: insert into department (id, name) values (default, ?)
binding parameter [1] as [VARCHAR] - Human Resource
Hibernate: insert into employee (id, department_id, email, firstName, lastName) values (default, ?, ?, ?, ?)
binding parameter [1] as [INTEGER] - 2
binding parameter [2] as [VARCHAR] - howtodoinjava@gmail.com
binding parameter [3] as [VARCHAR] - Lokesh
binding parameter [4] as [VARCHAR] - Gupta
Hibernate: SELECT id, firstName, lastName, email, department.id, department.name FROM employee, department
Found [2] as column [id]

Drop me your questions and comments.

Happy Learning !!

Reference:

JPA Docs
Hibernate JPA Docs

Was this post helpful?

Join 7000+ Fellow Programmers

Subscribe to get new post notifications, industry updates, best practices, and much more. Directly into your inbox, for free.

6 thoughts on “JPA native query example – @NamedNativeQuery example”

  1. This example is very simple. I cannot see a difference between a NamedQuery and a NamedNativeQuery here. I’d like to use a complex native query, with multiple tables and multiple selects, joining them together with “union”, like so: “select blah blah from table1, table2, table3 where complex-expression-1 UNION select blah blah from table1, table2, table3, table4 from complex-expression-2.” That’s what I need a native query for. Your example could also be written with a not-native query.

    Reply
  2. can we create a query to get the data without creating Entity class. as i want to export that data to excel directly if i am getting the List of what columns i will select from the table then my problem may got solved.

    I want to fetch the data from database tables and export that raw data to excel which is in my application only.
    though the table in database may increase so i have to keep the dynamic query to change the table name per selection.

    can you suggest the solution please.

    Reply
  3. How to execute dynamic sql query?

    Is that? HaHa!
    [java]

    @NamedNativeQuery(
    name = “getAllEmployees”,
    query = “?”,
    resultClass=EmployeeEntity.class
    )
    [java]

    Reply

Leave a Comment

HowToDoInJava

A blog about Java and its related technologies, the best practices, algorithms, interview questions, scripting languages, and Python.