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