JPA named query update example – @NamedNativeQuery example

In this JPA executeUpdate example, we will learn to use update query in jpa repository created using createNativeQuery() method of the EntityManager interface. Here we pass in the update query string to be executed in underlying database and the entity type that will be returned as result. We will use @NamedNativeQuery annotation for defining the SQL queries in one place in this example.

1. Create named native UPDATE 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 UPDATE statements are defined like this:

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

Here we have defined one named native query updateEmployeeName. It can be used to update the employee’s first name and last name in database directly.

2. How to execute named update queries

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

@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 named query update – 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="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.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 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.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 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("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: 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 [howtodoinjava@gmail.com] as column [email0_0_]
Found [NewFirstName] as column [firstName0_0_]
Found [NewLastName] as column [lastName0_0_]

Drop me your questions and comments.

Happy Learning !!

Read More – JPA Tutorials

3 Comments
Newest
Oldest Most Voted
Inline Feedbacks
View all comments

Comments are closed for this article!

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.