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 !!
Comments