Hibernate / JPA Native Delete Query Example

In this JPA delete query example, we will learn to delete database rows using the native SQL statements that are declared using @NamedNativeQuery and executed with EntityManager.createNativeQuery(). Here we pass the SQL query string to be executed in the underlying database and it returns a dummy result mapping. We will use @NamedNativeQueries annotation for defining the delete queries in one place in this example.

1. Creating a Native DELETE Query in JPA

Named SQL queries are defined using the @NamedNativeQuery annotation. This annotation can be placed on an @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 DELETE statements can be defined like this:

@Entity(name="EmployeeEntity")
@Table (name="employee")
@SqlResultSetMapping(name="deleteResult", columns = { @ColumnResult(name = "count")})
@NamedNativeQueries({
  @NamedNativeQuery(
    name  = "deleteEmployeeById",
    query = "DELETE FROM employee WHERE id = :employeeId"
    ,resultSetMapping = "deleteResult"
  )
})
public class EmployeeEntity implements Serializable {

  // fields, getters, setters
} 

Here we have defined one named native query ‘deleteEmployeeById‘. It can be used to delete an employee record from the database using its employee id. The result of the delete operation is mapped to the “count” column defined in the “deleteResult” result set mapping which signifies the number of rows affected 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 Delete Query

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

Make sure to replace the value of employeeId with the appropriate value. The named parameter in the query (:employeeId) should match the parameter set using setParameter.

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

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

// Set the named parameter
int employeeIdToDelete = 123; // Employee ID to delete

// Create and execute the named native query
Query deleteQuery = entityManager.createNamedQuery("deleteEmployeeById")
    .setParameter("employeeId", employeeIdToDelete);

int deletedCount = deleteQuery.executeUpdate();

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

3. JPA DELETE Query Example

Let us see how to execute a named SQL query for DELETE 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")
@SqlResultSetMapping(name = "deleteResult", columns = {@ColumnResult(name = "count")})
@NamedNativeQueries({
  //...
  @NamedNativeQuery(
    name = "deleteEmployeeById",
    query = "DELETE FROM tbl_employee WHERE id = :employeeId"
    , resultSetMapping = "deleteResult"
  )
})
public class Employee implements Serializable {

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

  // setters and getters
}

3.2. Unit Test

@Test
void testNamedNativeDeleteQuery() {

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

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

  int employeeId = employee.getId();        // Employee ID to delete

  entityManager.getTransaction().begin();

  // Create and execute the named native query
  Query deleteQuery = entityManager.createNamedQuery("deleteEmployeeById")
    .setParameter("employeeId", employeeId);

  int deletedCount = deleteQuery.executeUpdate();
  entityManager.getTransaction().commit();

  Assertions.assertEquals(1, deletedCount);
}

Output of the above testcase 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: DELETE FROM tbl_employee WHERE id = ?

Drop me your questions and comments.

Happy Learning !!

Source Code on Github

Comments

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments

About Us

HowToDoInJava provides tutorials and how-to guides on Java and related technologies.

It also shares the best practices, algorithms & solutions and frequently asked interview questions.

Our Blogs

REST API Tutorial

Dark Mode

Dark Mode