JPA 命名查询更新示例 - @NamedNativeQuery 示例

在此JPA executeUpdate 示例中，我们将学习使用jpa 存储库中的更新查询，该存储库是使用$$ 的createNativeQuery() 方法创建的$ 接口。在这里，我们传入要在基础数据库中执行的更新查询 字符串以及将作为结果返回的实体类型。在此示例中，我们将使用 @NamedNativeQuery 注释在一个地方定义 SQL 查询。

1. 创建命名本机 UPDATE 查询

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

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

@Entity(name="EmployeeEntity")
@Table (name="employee")

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

@NamedNativeQueries({
		@NamedNativeQuery(
				name	=	"updateEmployeeName",
				query	=	"UPDATE employee SET firstName = ?, lastName = ? WHERE id = ?"
				,resultSetMapping = "updateResult"
		)
})

public class EmployeeEntity implements Serializable
{
	//more code
}

这里我们定义了一个命名的本地查询 updateEmployeeName。它可用于直接更新数据库中员工的名字和姓氏。

2.如何执行命名更新查询

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

@PersistenceContext
private EntityManager manager;

@Override
public boolean updateEmployeeName( Integer id, String firstName, String lastName ) 
{
	try
	{
		manager.createNamedQuery("updateEmployeeName", EmployeeEntity.class)
		.setParameter(1, firstName)
		.setParameter(2, lastName)
		.setParameter(3, id)
		.executeUpdate();
		
		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="updateResult", columns = { @ColumnResult(name = "count")})
@NamedNativeQueries({
		@NamedNativeQuery(
				name	=	"updateEmployeeName",
				query	=	"UPDATE employee SET firstName = ?, lastName = ? WHERE id = ?"
				,resultSetMapping = "updateResult"
		)
})

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 updateEmployeeName(Integer id, String firstName, String lastName);
	
	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 updateEmployeeName( Integer id, String firstName, String lastName ) 
	{
		try
		{
			manager.createNamedQuery("updateEmployeeName", EmployeeEntity.class)
			.setParameter(1, firstName)
			.setParameter(2, lastName)
			.setParameter(3, id)
			.executeUpdate();
			
			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 update the first name and last name***********/
		
		employeeDAO.updateEmployeeName(1, "NewFirstName", "NewLastName");
		
		//Update the employee entity instance in current persistence session
		manager.refresh(employee);
		
		//Now check if we got correct data
		Assert.assertEquals(employee.getFirstName(),"NewFirstName");
		Assert.assertEquals(employee.getLastName(),"NewLastName");
	}
	
	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: UPDATE employee SET firstName = ?, lastName = ? WHERE id = ?
binding parameter [1] as [VARCHAR] - NewFirstName
binding parameter [2] as [VARCHAR] - NewLastName
binding parameter [3] 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
Found [cundage@gmail.com] as column [email0_0_]
Found [NewFirstName] as column [firstName0_0_]
Found [NewLastName] as column [lastName0_0_]

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

快乐学习！！

更多参考 – JPA 教程

标签2： JPA

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