In this JPA native query example, we will learn to use JPA native query (SQL SELECT query) using createNativeQuery() method of the EntityManager interface. We will pass in the query string to be executed in underlying database and the entity type that will be returned as result. We will also use named sql native queries in this example.
Native query vs named query
1. Native query refers to actual sql queries (referring to actual database objects). These queries are the sql statements which can be directly executed in database using a database client.
2. Named query is the way you define your query by giving it a name. You could define this in mapping file in hibernate or also using annotations at entity level.
1. Define JPA native queries
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 SQL native queries are defined like this:
@Entity(name="EmployeeEntity")
@Table (name="employee")
@NamedNativeQueries({
@NamedNativeQuery(
name = "getAllEmployees",
query = "SELECT id, firstName, lastName, email, department.id, department.name " +
"FROM employee, department",
resultClass=EmployeeEntity.class
),
@NamedNativeQuery(
name = "getAllEmployeesByDeptId",
query = "SELECT id, firstName, lastName, email, department.id, department.name " +
"FROM employee, department " +
"WHERE department.id = ?",
resultClass=EmployeeEntity.class
)
})
public class EmployeeEntity implements Serializable
{
//more code
}
Here we have defined two named queries getAllEmployees and getAllEmployeesByDeptId. First query returns all employees data, and second one will result all employees for a department id.
2. Execute native query
To execute above SQL queries, you will need to write below code in your DAOImpl class.
@Override
public List<EmployeeEntity> getAllEmployees() {
List<EmployeeEntity> employees = manager.createNamedQuery("getAllEmployees", EmployeeEntity.class)
.getResultList();
return employees;
}
@Override
public List<EmployeeEntity> getAllEmployeesByDeptId(Integer id) {
List<EmployeeEntity> employees = manager.createNamedQuery("getAllEmployeesByDeptId", EmployeeEntity.class)
.setParameter(1, id)
.getResultList();
return employees;
}
One thing to be careful of with SQL queries that return entities is that the resulting entity instances become managed by the persistence context, just like the results of a JPQL query. If you modify one of the returned entities, it will be written to the database when the persistence context becomes associated with a transaction.
So it is important to ensure that all the necessary data required to fully construct the entity is part of the query. If you leave out a field from the query, or default it to some value and then modify the resulting entity, there is a possibility that you will overwrite the correct version already stored in the database.
There are two benefits to getting managed entities back from a SQL query.
- The first is that a SQL query can replace an existing JP QL query and that application code should still work without changes.
- The second benefit is that it allows the developer to use SQL queries as a method of constructing new entity instances from tables that may not have any object-relational mapping.
For example, in many database architectures, there is a staging area to hold data that has not yet been verified or requires some kind of transformation before it can be moved to its final location.
Using JPA, a developer could start a transaction, query the staged data to construct entities, perform any required changes, and then commit. The newly created entities will get written to the tables mapped by the entity, not the staging tables used in the SQL query.
3. JPA Native Query Example
3.1. Entity classes
package com.howtodoinjava.jpa.demo.entity;
import java.io.Serializable;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
import javax.persistence.NamedNativeQueries;
import javax.persistence.NamedNativeQuery;
import javax.persistence.Table;
@Entity(name="EmployeeEntity")
@Table (name="employee")
@NamedNativeQueries({
@NamedNativeQuery(
name = "getAllEmployees",
query = "SELECT id, firstName, lastName, email, department.id, department.name " +
"FROM employee, department",
resultClass=EmployeeEntity.class
),
@NamedNativeQuery(
name = "getAllEmployeesByDeptId",
query = "SELECT id, firstName, lastName, email, department.id, department.name " +
"FROM employee, department " +
"WHERE department.id = ?",
resultClass=EmployeeEntity.class
)
})
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;
@ManyToOne
private DepartmentEntity department;
public EmployeeEntity() {}
public EmployeeEntity(String name, DepartmentEntity department) {
this.firstName = name;
this.department = department;
}
public EmployeeEntity(String name) {
this.firstName = name;
}
//Setters and Getters
@Override
public String toString() {
return "EmployeeVO [id=" + id + ", firstName=" + firstName
+ ", lastName=" + lastName + ", email=" + email
+ ", department=" + department + "]";
}
}
package com.howtodoinjava.jpa.demo.entity;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;
@Entity(name="DepartmentEntity")
@Table (name="department")
public class DepartmentEntity implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue
private Integer id;
private String name;
public DepartmentEntity(){
}
public DepartmentEntity(String name) {
super();
this.name = name;
}
@OneToMany(mappedBy="department",cascade=CascadeType.PERSIST)
private List<EmployeeEntity> employees = new ArrayList<EmployeeEntity>();
//Setters and Getters
@Override
public String toString() {
return "DepartmentVO [id=" + id + ", name=" + name + "]";
}
}
3.2. DAO
public interface DepartmentDAO
{
public boolean addDepartment(DepartmentEntity dept);
}
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.DepartmentEntity;
@Repository
@Transactional
public class DepartmentDAOImpl implements DepartmentDAO {
@PersistenceContext
private EntityManager manager;
@Override
public boolean addDepartment(DepartmentEntity dept) {
try {
manager.persist(dept);
} catch (Exception e) {
e.printStackTrace();
return false;
}
return true;
}
}
public interface EmployeeDAO
{
public List<EmployeeEntity> getAllEmployees();
public List<EmployeeEntity> getAllEmployeesByDeptId(Integer id);
public boolean addEmployee(EmployeeEntity employee);
}
package com.howtodoinjava.jpa.demo.dao;
import java.util.List;
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 List<EmployeeEntity> getAllEmployees() {
List<EmployeeEntity> employees = manager.createNamedQuery("getAllEmployees", EmployeeEntity.class)
.getResultList();
return employees;
}
@Override
public List<EmployeeEntity> getAllEmployeesByDeptId(Integer id) {
List<EmployeeEntity> employees = manager.createNamedQuery("getAllEmployeesByDeptId", EmployeeEntity.class)
.setParameter(1, id)
.getResultList();
return employees;
}
@Override
public boolean addEmployee(EmployeeEntity employee) {
try{
manager.persist(employee);
}catch(Exception e){
e.printStackTrace();
return false;
}
return true;
}
}
3.3. Demo
package com.jpa.demo.test;
import java.util.List;
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.DepartmentDAO;
import com.howtodoinjava.jpa.demo.dao.EmployeeDAO;
import com.howtodoinjava.jpa.demo.entity.DepartmentEntity;
import com.howtodoinjava.jpa.demo.entity.EmployeeEntity;
@ContextConfiguration(locations = "classpath:application-context-test.xml")
@RunWith(SpringJUnit4ClassRunner.class)
public class TestEmployeeDAO
{
@Autowired
private EmployeeDAO employeeDAO;
@Autowired
private DepartmentDAO departmentDAO;
@Test
@Transactional
@Rollback(true)
public void testGetAllEmployees()
{
//Setup some test data in IM (in-memory) database
setupData();
List<EmployeeEntity> employees = employeeDAO.getAllEmployees();
//Validate that data is found
Assert.assertEquals(employees.size(), 1);
EmployeeEntity employeeEntity = employees.get(0);
//Now check if we got correct data
Assert.assertEquals(employeeEntity.getFirstName(),"Lokesh");
Assert.assertEquals(employeeEntity.getLastName(),"Gupta");
Assert.assertEquals(employeeEntity.getDepartment().getName(),"Human Resource");
}
@Test
@Transactional
@Rollback(true)
public void testGetAllEmployeesByDeptId()
{
//Setup some test data in IM (in-memory) database
setupData();
List<EmployeeEntity> employees = employeeDAO.getAllEmployeesByDeptId(1);
//Validate that data is found
Assert.assertEquals(employees.size(), 1);
EmployeeEntity employeeEntity = employees.get(0);
//Now check if we got correct data
Assert.assertEquals(employeeEntity.getFirstName(),"Lokesh");
Assert.assertEquals(employeeEntity.getLastName(),"Gupta");
Assert.assertEquals(employeeEntity.getDepartment().getName(),"Human Resource");
}
public void setupData()
{
DepartmentEntity department = new DepartmentEntity("Human Resource");
departmentDAO.addDepartment(department);
EmployeeEntity employee = new EmployeeEntity();
employee.setFirstName("Lokesh");
employee.setLastName("Gupta");
employee.setEmail("howtodoinjava@gmail.com");
employee.setDepartment(department);
employeeDAO.addEmployee(employee);
}
}
Program output.
Hibernate: drop table department if exists Hibernate: drop table employee if exists Hibernate: create table department (id integer generated by default as identity (start with 1), name varchar(255), primary key (id)) Hibernate: create table employee (id integer generated by default as identity (start with 1), email varchar(255), firstName varchar(255), lastName varchar(255), department_id integer, primary key (id)) Hibernate: alter table employee add constraint FK4722E6AE5591DEEE foreign key (department_id) references department HHH000230: Schema export complete Hibernate: insert into department (id, name) values (default, ?) binding parameter [1] as [VARCHAR] - Human Resource Hibernate: insert into employee (id, department_id, email, firstName, lastName) values (default, ?, ?, ?, ?) binding parameter [1] as [INTEGER] - 1 binding parameter [2] as [VARCHAR] - howtodoinjava@gmail.com binding parameter [3] as [VARCHAR] - Lokesh binding parameter [4] as [VARCHAR] - Gupta Hibernate: SELECT id, firstName, lastName, email, department.id, department.name FROM employee, department WHERE department.id = ? binding parameter [1] as [INTEGER] - 1 Found [1] as column [id] Hibernate: insert into department (id, name) values (default, ?) binding parameter [1] as [VARCHAR] - Human Resource Hibernate: insert into employee (id, department_id, email, firstName, lastName) values (default, ?, ?, ?, ?) binding parameter [1] as [INTEGER] - 2 binding parameter [2] as [VARCHAR] - howtodoinjava@gmail.com binding parameter [3] as [VARCHAR] - Lokesh binding parameter [4] as [VARCHAR] - Gupta Hibernate: SELECT id, firstName, lastName, email, department.id, department.name FROM employee, department Found [2] as column [id]
Drop me your questions and comments.
Happy Learning !!
Reference:
Leave a Reply