JPA 删除实体示例——@NamedNativeQuery 示例

在JPA delete entity example中，我们将学习delete JPA entities using @NamedNativeQuery created with createNativeQuery() EntityManager 接口的方法。这里我们传入要在底层数据库中执行的查询字符串和一个虚拟结果映射 [更多参考]。在此示例中，我们将使用命名 SQL 本机查询在一个地方定义删除查询。

1.创建命名本机DELETE查询

命名 SQL 查询是使用 @NamedNativeQuery 注释定义的。此注释可以放置在任何实体上并定义查询的名称以及查询文本。与 JPQL 命名查询一样，查询的名称在持久性单元中必须是唯一的。

SQL DELETE 语句的命名本机查询定义如下：

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

这里我们定义了一个命名的本地查询 deleteEmployeeById。它可用于使用员工 ID 从数据库中删除员工记录。这是一个jpa delete entity by id的例子。

2.如何执行命名删除查询

要执行上述 SQL 删除查询，我们需要使用 EntityManager.executeUpdate() 方法。

@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删除实体示例-完整示例

3.1.员工实体.java

package com.cundage.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.cundage.jpa.demo.dao;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import com.cundage.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.cundage.jpa.demo.dao.EmployeeDAO;
import com.cundage.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("cundage@gmail.com");
		
		employeeDAO.addEmployee(employee);
	}
}

上述测试用例的输出将是：

Hibernate: insert into employee (id, email, firstName, lastName) values (default, ?, ?, ?)
binding parameter [1] as [VARCHAR] - cundage@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

使用上述方法为 jpa delete all 创建查询

把你的问题和意见告诉我。

快乐学习！！

标签2： JPA

地址：https://www.cundage.com/article/jpa-native-delete-sql-query-example.html