Hibernate / JPA @NamedNativeQuery Example

In this hibernate / JPA tutorial, we will learn to define and execute a native SQL query (SQL SELECT query) using @NamedNativeQuery annotation and EntityManager.createNativeQuery() method. We will pass in the query string to be executed in the underlying database and the entity type that will be returned as a result.

1. Native Query, Named Query vs. Named Native Query

Native queries and named queries are two different ways to execute SQL or JPQL queries in JPA or Hibernate.

Native Query refers to actual SQL queries (referring to actual database objects). These queries are the SQL statements that can be directly executed in the database using a database client. These have the standard SQL syntax that is specific to the database system.

String sqlQuery = "SELECT * FROM Product WHERE price >= :minPrice AND price <= :maxPrice";

List<Product> productList = entityManager.createNativeQuery(sqlQuery, Product.class)
            .setParameter("minPrice", minPrice)
            .setParameter("maxPrice", maxPrice)
            .getResultList();

Named Query uses JPQL syntax, which is database-agnostic. JPQL is similar to SQL but is designed to work with Java objects. It is the way you define the SQL query to be executed by giving it a name. We could define a named query in the mapping file in hibernate or on @Entity annotation.

@Entity
@NamedQuery( name = "Product.findProductsByPriceRange", 
	query = "SELECT p FROM Product p WHERE p.price >= :minPrice AND p.price <= :maxPrice")
public class Product {
	//...
}

// executing a named query

List<Product> productList = entityManager.createNamedQuery("Product.findProductsByPriceRange", Product.class)
            .setParameter("minPrice", minPrice)
            .setParameter("maxPrice", maxPrice)
            .getResultList();

Named Native Query combines both and allows to define and execute native SQL queries using a name or identifier, similar to how named queries are executed.

@Entity
@NamedNativeQuery(
    name = "Product.findProductById",
    query = "SELECT * FROM product WHERE id = :productId",
    resultClass = Product.class
)
public class Product {
    // Entity fields and methods
}

// executing a named query

List<Product> productList = entityManager.createNamedQuery("Product.findProductsByPriceRange", Product.class)
            .setParameter("minPrice", minPrice)
            .setParameter("maxPrice", maxPrice)
            .getResultList();

2. Defining Named 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 e.id, e.firstName, e.lastName, e.email, d.id as department_id, d.name as department_name " +
            "FROM employee e " +
            "INNER JOIN department d ON e.department_id = d.id",
    resultClass = EmployeeEntity.class
  ),
  @NamedNativeQuery(
    name = "getAllEmployeesByDeptId",
    query = "SELECT e.id, e.firstName, e.lastName, e.email, d.id as department_id, d.name as department_name " +
            "FROM employee e " +
            "INNER JOIN department d ON e.department_id = d.id " +
            "WHERE d.id = ?",
    resultClass = EmployeeEntity.class
  )
})
public class EmployeeEntity implements Serializable {
  //fields
}

Here we have defined two named queries ‘getAllEmployees‘ and ‘getAllEmployeesByDeptId‘. The first query returns all employees’ data, and the second one will result in all employees for a department ID.

3. Executing a Named Native Query

To execute the above-named native SQL queries, you will need to write the code below in your DAO class.

public List<EmployeeEntity> getAllEmployees() {

  List<EmployeeEntity> employees = entityManager
    .createNamedQuery("getAllEmployees", EmployeeEntity.class)
    .getResultList();

  return employees;
}
 
public List<EmployeeEntity> getAllEmployeesByDeptId(Integer id) {

  List<EmployeeEntity> employees = entityManager
  	.createNamedQuery("getAllEmployeesByDeptId", EmployeeEntity.class)
    .setParameter(1, id)
    .getResultList();

  return employees;
}

One thing to be careful that the returned entity instances are 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 is 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 value already stored in the database.

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

• The first is that a SQL query can replace an existing JPQL query and that application code would still work without changes.
• 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 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.

4. JPA Named Native Query Example

Let us see how to execute a named native query with an example.

4.1. Entity Classes

The example uses two entities Employee and Department. An employee belongs to one department, where one department can have many employees.

@Entity
@Table(name = "tbl_employee")
@NamedNativeQueries({
  @NamedNativeQuery(
    name = "getEmployeesByDeptId",
    query = "SELECT e.id, e.firstName, e.lastName, e.email, d.id as department_id, d.name " +
      "FROM tbl_employee e " +
      "INNER JOIN tbl_department d ON d.id = e.department_id " +
      "WHERE d.id = :deptId",
    resultClass = Employee.class
  )
})
public class Employee implements Serializable {

  @Id
  @GeneratedValue
  private Integer id;
  private String firstName;
  private String lastName;
  private String email;

  @ManyToOne
  private Department department;

  // setters and getters
}

@Entity
@Table(name = "tbl_department")
public class Department implements Serializable {
  private static final long serialVersionUID = 1L;

  @Id
  @GeneratedValue
  private Integer id;
  private String name;

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

  // setters and getters
}

3.2. Unit Test

In the following test, we first stored a few records such that two employees are assigned to the department “HR”. Later, we executed the named native SQL query ‘getEmployeesByDeptId‘ which fetches all the employees by Id.

When we fetch all employees for ‘HR‘ department, we get the two employees.

@Test
void testNamedNativeSelectQuery() {

  EntityManager entityManager = emf.createEntityManager();

  entityManager.getTransaction().begin();

  Department dept1 = new Department("HR");
  Department dept2 = new Department("Finance");
  entityManager.persist(dept1);
  entityManager.persist(dept2);

  entityManager.persist(new Employee("Lokesh", "Gupta", "lokesh@email.com", dept1));
  entityManager.persist(new Employee("Amit", "Gupta", "amit@email.com", dept1));
  entityManager.persist(new Employee("Govind", "Gupta", "govind@email.com", dept2));

  entityManager.getTransaction().commit();

  List<Employee> employeeList = entityManager.createNamedQuery("getEmployeesByDeptId", Employee.class)
    .setParameter("deptId", dept1.getId())
    .getResultList();

  Assertions.assertEquals(2, employeeList.size());
}

Program output.

Hibernate: select next_val as id_val from tbl_department_SEQ for update
Hibernate: update tbl_department_SEQ set next_val= ? where next_val=?
Hibernate: select next_val as id_val from tbl_department_SEQ for update
Hibernate: update tbl_department_SEQ set next_val= ? where next_val=?
Hibernate: select next_val as id_val from tbl_employee_SEQ for update
Hibernate: update tbl_employee_SEQ set next_val= ? where next_val=?
Hibernate: select next_val as id_val from tbl_employee_SEQ for update
Hibernate: update tbl_employee_SEQ set next_val= ? where next_val=?

Hibernate: insert into tbl_department (name,id) values (?,?)
Hibernate: insert into tbl_department (name,id) values (?,?)
Hibernate: insert into tbl_employee (department_id,email,firstName,lastName,id) values (?,?,?,?,?)
Hibernate: insert into tbl_employee (department_id,email,firstName,lastName,id) values (?,?,?,?,?)
Hibernate: insert into tbl_employee (department_id,email,firstName,lastName,id) values (?,?,?,?,?)

Hibernate: SELECT e.id, e.firstName, e.lastName, e.email, d.id as department_id, d.name FROM tbl_employee e INNER JOIN tbl_department d ON d.id = e.department_id WHERE d.id = ?

Drop me your questions and comments.

Happy Learning !!

Source Code on Github