Hibernate / JPA Update Query with @NamedNativeQuery

In Hibernate/JPA, we can perform an update SQL query with a named native query using the @NamedNativeQuery annotation and executing it with EntityManager.executeUpdate() method. The @NamedNativeQuery annotation is used for defining the native SQL queries that can be referred to by name, similar to named queries, and can contain database-specific syntax, similar to native queries.

1. Creating a Named Native Query for SQL Update

The @NamedNativeQuery annotation can 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 native queries for SQL UPDATE statements are defined like this:

@Entity(name = "EmployeeEntity")
@Table(name = "employee")
@SqlResultSetMapping(name = "updateResult", columns = { @ColumnResult(name = "count") })
@NamedNativeQueries({
    @NamedNativeQuery(
        name = "updateEmployeeName",
        query = "UPDATE employee SET firstName = :newFirstName, lastName = :newLastName WHERE id = :employeeId",
        resultSetMapping = "updateResult"
    )
})
public class EmployeeEntity implements Serializable {
    // More code
}

Here we have defined one named native query ‘updateEmployeeName‘. It can be used to update the employee’s first name and last name in the database directly. The result of the update operation is mapped to the “count” column defined in the “updateResult” result set mapping which signifies the number of rows updated as a result of query execution.

This above query is only for reference purposes. We can define more complex queries using the same syntax and technique.

2. Executing the Update Query

To execute the above SQL query, we will need to use ‘EntityManager.executeUpdate()‘ method. The result is the number of rows that were updated by the query.

Make sure to replace the values of newFirstName, newLastName, and employeeId with the appropriate values. The named parameters in the query (:newFirstName, :newLastName, and :employeeId) should match the parameters you set using setParameter.

// Obtain an EntityManager
EntityManager entityManager = entityManagerFactory.createEntityManager();

// Begin a transaction
entityManager.getTransaction().begin();

// Set the named parameters
String newFirstName = "John"; // New first name
String newLastName = "Doe";  // New last name
int employeeId = 123;        // Employee ID to update

// Create and execute the named native query
Query updateQuery = entityManager.createNamedQuery("updateEmployeeName")
    .setParameter("newFirstName", newFirstName)
    .setParameter("newLastName", newLastName)
    .setParameter("employeeId", employeeId);

int updatedCount = updateQuery.executeUpdate();

// Commit the transaction
entityManager.getTransaction().commit();

3. JPA UPDATE Query Example

Let us see how to execute a named SQL query for UPDATE operation with an example.

3.1. Entity Class

The example uses the entity Employee which has simple fields such as id, firstname, lastname end email.

@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
}

3.2. Unit Test

@Test
void testNamedNativeUpdateQuery() {

  EntityManager entityManager = emf.createEntityManager();
  Employee employee = new Employee("Lokesh", "Gupta", "lokesh@email.com", null);

  entityManager.getTransaction().begin();
  entityManager.persist(employee);
  entityManager.getTransaction().commit();

  entityManager.getTransaction().begin();
  // Set the named parameters
  String newFirstName = "John"; // New first name
  String newLastName = "Doe";  // New last name
  int employeeId = employee.getId();        // Employee ID to update

  // Create and execute the named native query
  Query updateQuery = entityManager.createNamedQuery("updateEmployeeName")
    .setParameter("newFirstName", newFirstName)
    .setParameter("newLastName", newLastName)
    .setParameter("employeeId", employeeId);

  int updatedCount = updateQuery.executeUpdate();
  entityManager.getTransaction().commit();

  Assertions.assertEquals(1, updatedCount);
}

Output of the above testcases will be:

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_employee (department_id,email,firstName,lastName,id) values (?,?,?,?,?)
Hibernate: UPDATE tbl_employee SET firstName = ?, lastName = ? WHERE id = ?

Drop me your questions and comments.

Happy Learning !!

Source Code on Github