In this example, we shall see how to use native SQL queries in JPA.
The Java Persistence Query Language is a powerful query language that can be used to bridge the Object Relational world. However, there are cases when the JPQL falls short for example, to execute a Stored Procedure.
The JPA optimizes the query written in JPQL for the underlying database. However, there might be some cases where you are satisfied with the JPA optimizations and you want your SQL Query to run. For such cases, the JPA provides us with the option to run native SQL Queries in a seamless manner.
We use the EntityManager.createNativeQuery() method to execute the SQL Query in JPA. This method is used to execute native SQL queries like delete and update which don’t return the resultset.
The EntityManager.createNativeQuery() method is overloaded with EntityManager.createNativeQuery(String sqlQuery, Class clazz). This method is used in other case, i.e. when it is a select query. JPA maps the returned rows to the Class passed as the second argument.
We will look at an example of how we can execute the native SQL query and get the results in the form of objects.
employee.sql:
CREATE TABLE `employee` ( `employee_id` bigint(20) NOT NULL AUTO_INCREMENT , `employee_name` varchar(40) , PRIMARY KEY (`employee_id`) )
Employee.java:
package com.jcg.examples;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name="employee")
public class Employee
{
protected Long employeeId;
protected String name;
@Id
@Column(name="employee_id")
@GeneratedValue(strategy=GenerationType.IDENTITY)
public Long getEmployeeId()
{
return employeeId;
}
public void setEmployeeId(Long employeeId)
{
this.employeeId = employeeId;
}
@Column(name="employee_name")
public String getName()
{
return name;
}
public void setName(String name)
{
this.name = name;
}
@Override
public String toString()
{
return "Employee [employeeId=" + employeeId + ", name=" + name + "]";
}
}
JPADemo.java:
package com.jcg.examples;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
/**
* @author Chandan Singh
*
*/
public class JPADemo
{
public static void main(String[] args)
{
EntityManagerFactory emf = Persistence.createEntityManagerFactory("jcg-JPA");
EntityManager em = emf.createEntityManager();
em.getTransaction().begin();
List<>?> list = em.createNativeQuery("Select employee_id, employee_name from employee", Employee.class).getResultList();
System.out.println(list);
em.getTransaction().commit();
}
}
META-INF/persistence.xml:
<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
version="2.0">
<persistence-unit name="jcg-JPA">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<properties>
<property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect" />
<property name="hibernate.hbm2ddl.auto" value="update" />
<property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver" />
<property name="hibernate.connection.username" value="root" />
<property name="hibernate.connection.password" value="toor" />
<property name="hibernate.connection.url" value="jdbc:mysql://localhost/jcg" />
</properties>
</persistence-unit>
</persistence>
OUTPUT:
[Employee [employeeId=1, name=Chandan], Employee [employeeId=2, name=Nikos]]
Here, we execute the select command in native SQL and store the result in List<Employee>, which stores all the rows returned in the Employee PoJo.
Similarly, for update/delete queries, we can simply execute the queries with the createNativeQuery() method.
Conclusion
In this article, we briefly explained how to execute native SQL Queries when the need arises.
You can download the source code of this example here: JPANativeQueryDemo.zip
