Home » Enterprise Java » jpa » JPA Native SQL Queries Example

About Chandan Singh

Chandan Singh
Chandan holds a degree in Computer Engineering and is a passionate software programmer. He has good experience in Java/J2EE Web-Application development for Banking and E-Commerce Domains.

JPA Native SQL Queries Example

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.

Download
You can download the source code of this example here: JPANativeQueryDemo.zip

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

 

Leave a Reply

avatar

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  Subscribe  
Notify of