JPA delete entity example – @NamedNativeQuery example

In JPA delete entity example, we will learn to delete JPA entities using @NamedNativeQuery created with createNativeQuery() method of the EntityManager interface. Here we pass in the query string to be executed in underlying database and a dummy result mapping [Read More]. We will use named SQL native queries for defining the delete queries in one place in this example.

1. Create named native DELETE query

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 native queries for SQL DELETE statements are 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 = ?"
				,resultSetMapping = "deleteResult"
		)
})

public class EmployeeEntity implements Serializable
{
	//more code
}	

Here we have defined one named native query deleteEmployeeById. It can be used to delete an employee record from database using it’s employee id. It’s an example of jpa delete entity by id.

2. How to execute named delete queries

To execute above SQL delete queries, we will need use EntityManager.executeUpdate() method.

@PersistenceContext
private EntityManager manager;

@Override
public boolean deleteEmployeeById( Integer id ) 
{
	try
	{
		manager.createNamedQuery("deleteEmployeeById", EmployeeEntity.class)
		.setParameter(1, id)
		.executeUpdate();
		
		//Execute the delete query
		manager.flush();
		
		//update entity manager with changes
		manager.refresh(getEmployeeById(id));
		
		return true;
	}
	catch (Exception e)
	{
		return false;
	}
}

3. JPA delete entity example – complete example

3.1. EmployeeEntity.java

package com.howtodoinjava.jpa.demo.entity;

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

@SqlResultSetMapping(name="deleteResult", columns = { @ColumnResult(name = "count")})

@NamedNativeQueries({
		@NamedNativeQuery(
				name	=	"deleteEmployeeById",
				query	=	"DELETE FROM employee WHERE id = ?"
				,resultSetMapping = "deleteResult"
		)
})

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;
     
    public EmployeeEntity() {}

    //Setters and Getters
    
    @Override
    public String toString() {
        return "EmployeeVO [id=" + id + ", firstName=" + firstName
                + ", lastName=" + lastName + ", email=" + email + "]";
    }
}

3.2. EmployeeDAO.java

public interface EmployeeDAO 
{
	public boolean deleteEmployeeById(Integer id);
	
	public EmployeeEntity getEmployeeById(Integer id);

	public boolean addEmployee(EmployeeEntity employee);
}

3.3. EmployeeDAOImpl.java

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.EmployeeEntity;

@Repository
@Transactional
public class EmployeeDAOImpl implements EmployeeDAO 
{

	@PersistenceContext
	private EntityManager manager;
	
	@Override
	public boolean deleteEmployeeById( Integer id ) 
	{
		try
		{
			manager.createNamedQuery("deleteEmployeeById", EmployeeEntity.class)
			.setParameter(1, id)
			.executeUpdate();
			
			//Execute the delete query
			manager.flush();
			
			//update entity manager with changes
			manager.refresh(getEmployeeById(id));
			
			return true;
		}
		catch (Exception e)
		{
			return false;
		}
	}

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

	@Override
	public EmployeeEntity getEmployeeById(Integer id) {
		EmployeeEntity employee = null;
		try 
		{
			employee = manager.find(EmployeeEntity.class, id);
		} 
		catch (Exception e) {
			e.printStackTrace();
		}
		return employee;
	}
}

3.4. TestEmployeeDAO.java

package com.jpa.demo.test;

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

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.EmployeeDAO;
import com.howtodoinjava.jpa.demo.entity.EmployeeEntity;

@ContextConfiguration(locations = "classpath:application-context-test.xml")
@RunWith(SpringJUnit4ClassRunner.class)
public class TestEmployeeDAO 
{
	
	@Autowired
	private EmployeeDAO employeeDAO;
	
	@PersistenceContext
	private EntityManager manager;
	
	/*@Autowired
	private DepartmentDAO departmentDAO;*/
	
	@Test
	@Transactional
	@Rollback(true)
	public void testUpdateEmployee()
	{
		//Setup some test data in IM (in-memory) database
		setupData();
		
		EmployeeEntity employee = employeeDAO.getEmployeeById(1);
				
		//Now check if we got correct data
		Assert.assertEquals(employee.getFirstName(),"Lokesh");
		Assert.assertEquals(employee.getLastName(),"Gupta");
		
		/***********Now delete this employee using native SQL DELETE query***********/
		
		employeeDAO.deleteEmployeeById(1);
		
		//Now verify if employee is deleted or not
		
		EmployeeEntity deletedEmployee = employeeDAO.getEmployeeById(1);
		Assert.assertNull(deletedEmployee);
	}
	
	public void setupData()
	{
		EmployeeEntity employee = new EmployeeEntity();
		employee.setFirstName("Lokesh");
		employee.setLastName("Gupta");
		employee.setEmail("howtodoinjava@gmail.com");
		
		employeeDAO.addEmployee(employee);
	}
}

Output of above testcases will be:

Hibernate: insert into employee (id, email, firstName, lastName) values (default, ?, ?, ?)
binding parameter [1] as [VARCHAR] - howtodoinjava@gmail.com
binding parameter [2] as [VARCHAR] - Lokesh
binding parameter [3] as [VARCHAR] - Gupta
Hibernate: DELETE FROM employee WHERE id = ?
binding parameter [1] as [INTEGER] - 1
Hibernate: select employeeen0_.id as id0_0_, employeeen0_.email as email0_0_, employeeen0_.firstName as firstName0_0_, employeeen0_.lastName as lastName0_0_ from employee employeeen0_ where employeeen0_.id=?
binding parameter [1] as [INTEGER] - 1
Hibernate: select employeeen0_.id as id0_0_, employeeen0_.email as email0_0_, employeeen0_.firstName as firstName0_0_, employeeen0_.lastName as lastName0_0_ from employee employeeen0_ where employeeen0_.id=?
binding parameter [1] as [INTEGER] - 1

Use above method to create query for jpa delete all

Drop me your questions and comments.

Happy Learning !!

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.

Leave a Comment

HowToDoInJava

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