JPA - Java Persistence Query Language (JPQL)

Java Persistence Query Language (JPQL) is a string-based query language used to define queries over entities and their persistent states. It enables the application developer to specify the semantics of queries in a portable way and independent of the particular database schema that in use.

Java Persistence Query Language (JPQL) is not only portable but also it does optimize the query methods by allowing the developers to compile their query language direct into target language, in that the execution of queries will be shifted to the native language facilities provided by the database (or any other persistent store) instead of requiring queries to be executed on the Runtime Representation of the entity state. This tutorial provides an explanation of how Java Persistence Query Language used according to Java Persistence API 2.1.

EclipseLink does support a Java Persistence Query Language (JPQL) as a one of the Java Persistence API (JPA) vendor, it provides many extensions to the standard JPA Persistence Query Language (JPQL), these extensions referred to as the Eclipselink Query Language.

At this tutorial, you should find a full coverage for what Java Persistence API has provided in the Java Persistence Query Language field as well the main extensions that been added by the Eclipselink under what called after that EclipseLink Query Langauge.

Required Terminologies

This tutorial and next upcoming ones could use one or more of the complex terms that should be understood for making the JPQL concept more easier.

Abstract Persistence Schema: This term refers to the persistent schema abstraction ( persistent entities, their states and their relationships) over which Java Persistence Queries operate. Queries over this persistent schema abstraction are translated into queries that are executed over the database schema to which entities are mapped.
A Persistence Unit: This terms defines the set of all classes that are related or grouped by the application and which must be calculated in their mapping to a single database.

Statement Types

A Java Persistence query language statement may be either a select statement, an update statement or a delete statement. Any Java Persistence query language statement may be constructed dynamically or may be statically defined in a metadata annotation or XML descriptor element.

Anatomy of Select Statement

A select statement is a string which consists of the following clauses:

a SELECT clause, which determines the type of the objects or values to be selected.
a FROM clause, which provides declarations that designate the domain to which the expressions specified in the other clauses of the query apply.
an optional WHERE clause, which may be used to restrict the results that are returned by the query.
an optional GROUP BY clause, which allows query results to be aggregated in terms of groups.
an optional HAVING clause, which allows filtering over aggregated groups.
an optional ORDER BY clause, which may be used to order the results that are returned by the query.
a select statement must always have SELECT and a FROM clause.

Classes Domain Anatomy

The classes anatomy section should provide a full picture for about those classes that forming the persistent domain over which the select statement operate. This tutorial will mostly depend on a previous persistent domain that already done in the EclipseLink – JPA examples. See Figure 1.0 shows you the classes domain.

Figure 1.0

We’ve been clarifying the following points using the previous figure.

The Employee entity is in the middle of the entities.
The Employee has OneToOne (Bidirectional) association with an Address entity.
The Employee has OneToMany (Bidirectional) association with a Phone entity.
The Employee has ManyToMany (Bidirectional) association with a Project entity.
The License has ManyToOne (Unidirectional) association with an Employee entity (The Employee has no reference for License entity).

Querying using Select

The example that you would see is constantly use a static query instead of using dynamic one (See Dynamic vs Static) .

JPAImpl.java

package net.javabeat.eclipselink;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;

import net.javabeat.eclipselink.data.Address;
import net.javabeat.eclipselink.data.AddressPK;
import net.javabeat.eclipselink.data.ContractDeveloper;
import net.javabeat.eclipselink.data.Developer;
import net.javabeat.eclipselink.data.DriverLicense;
import net.javabeat.eclipselink.data.Employee;
import net.javabeat.eclipselink.data.EmployeePeriod;
import net.javabeat.eclipselink.data.FreelanceDeveloper;
import net.javabeat.eclipselink.data.GlobalProject;
import net.javabeat.eclipselink.data.ICDLComputerLicense;
import net.javabeat.eclipselink.data.License;
import net.javabeat.eclipselink.data.LocalProject;
import net.javabeat.eclipselink.data.Phone;
import net.javabeat.eclipselink.data.PhonePK;
import net.javabeat.eclipselink.data.Project;

public class JPAImpl {
 static EntityManagerFactory factory = null;
 static EntityManager em = null;
 static {
 factory = Persistence.createEntityManagerFactory("EclipseLink-JPA-Installation");
 em = factory.createEntityManager();
 }

 public static void main(String [] args){
 // Define a string based query
 String selectQueryStr = "SELECT emp FROM Employee emp";

 // SELECT STATEMENT

 // Create a dynamic query
 Query query = em.createQuery(selectQueryStr);
 // Uses a query object retrieved from the entity manager for acquiring the results
 List<Object> objects = query.getResultList();
 // Looping Over fetched objects
 for(Object obj : objects){
 Employee emp = null;
 if(obj instanceof Employee)
 emp = (Employee) obj;
 else if(obj instanceof Object []){
 Object [] os = (Object [])obj;
 for(Object o : os){
 System.out.println(o);
 }
 }

 if(emp != null){
 System.out.println("Employee Id : "+ emp.getEmployeeId());
 System.out.println("Employee Name : "+ emp.getEmployeeName());
 System.out.println("Employee Period # : Start Date :: "+
 emp.getEmployeePeriod().getStartDate() + " :: End Date " +
 emp.getEmployeePeriod().getEndDate());
 System.out.println("Employee's Address : "+ emp.getAddress().getAddressId());
 System.out.println("Employee's Phones # : "+ emp.getPhones().size());
 System.out.println("Employee's Projects # : "+ emp.getProjects().size());
 }
 }
}
}

Type of retrieved objects could be determined by using of SELECT statement.
By referencing an Employee entity, the application is capable of accessing all those entities that Employee refers to.
Although an Employee entity has a reference for DriverLicense, but it couldn’t be reached from an Employee.

The Result of JPAImpl.java

Employee Id : 1
Employee Name : John Smith
Employee Period # : Start Date :: Wed Mar 12 00:00:00 GMT+02:00 2014 :: End Date Wed Mar 12 00:00:00 GMT+02:00 2014
Employee's Address : net.javabeat.eclipselink.data.AddressPK@109b2a51
Employee's Phones # : 1
Employee's Projects # : 2

But what’s could happen if we’ve changed the select statement by making it doing a query for attributes (persistent fields) instead of types (Entities).

JPAImpl.java

package net.javabeat.eclipselink;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;

import net.javabeat.eclipselink.data.Address;
import net.javabeat.eclipselink.data.AddressPK;
import net.javabeat.eclipselink.data.ContractDeveloper;
import net.javabeat.eclipselink.data.Developer;
import net.javabeat.eclipselink.data.DriverLicense;
import net.javabeat.eclipselink.data.Employee;
import net.javabeat.eclipselink.data.EmployeePeriod;
import net.javabeat.eclipselink.data.FreelanceDeveloper;
import net.javabeat.eclipselink.data.GlobalProject;
import net.javabeat.eclipselink.data.ICDLComputerLicense;
import net.javabeat.eclipselink.data.License;
import net.javabeat.eclipselink.data.LocalProject;
import net.javabeat.eclipselink.data.Phone;
import net.javabeat.eclipselink.data.PhonePK;
import net.javabeat.eclipselink.data.Project;

public class JPAImpl {
 static EntityManagerFactory factory = null;
 static EntityManager em = null;
 static {
 factory = Persistence.createEntityManagerFactory("EclipseLink-JPA-Installation");
 em = factory.createEntityManager();
 }

 public static void main(String [] args){
 // Define a string based query
 String selectQueryStr = "SELECT emp.employeeId,emp.employeeName FROM Employee emp";

 // SELECT STATEMENT

 // Create a dynamic query
 Query query = em.createQuery(selectQueryStr);
 // Uses a query object retrieved from the entity manager for acquiring the results
 List<Object> objects = query.getResultList();
 // Looping Over fetched objects
 for(Object obj : objects){
 Employee emp = null;
 if(obj instanceof Employee)
 emp = (Employee) obj;
 else if(obj instanceof Object []){
 Object [] os = (Object [])obj;
 for(Object o : os){
 System.out.println(o);
 }
 }

 if(emp != null){
 System.out.println("Employee Id : "+ emp.getEmployeeId());
 System.out.println("Employee Name : "+ emp.getEmployeeName());
 System.out.println("Employee Period # : Start Date :: "+
 emp.getEmployeePeriod().getStartDate() + " :: End Date " +
 emp.getEmployeePeriod().getEndDate());
 System.out.println("Employee's Address : "+ emp.getAddress().getAddressId());
 System.out.println("Employee's Phones # : "+ emp.getPhones().size());
 System.out.println("Employee's Projects # : "+ emp.getProjects().size());
 }
 }

}

}

A SELECT can be used for defining retrieving attributes/types or both.

The Result of JPAImp.java

1
John Smith

Updating and Deleting

The use of queries do not consider the SELECT statement only, but they also could be used for handling a wide range of modification and deletion statements.

Anatomy of Update Statement

The anatomy of update statement is identical to that defined in the SQL-database.
An UPDATE clause, which defines the entities over which the update statement affect.
A SET clause, which provides comma separated pairs (property, value) for those properties being changed.
A WHERE clause, which provides the conditions that based on the entities have updated.

JPAImpl.java

package net.javabeat.eclipselink;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;

import net.javabeat.eclipselink.data.Address;
import net.javabeat.eclipselink.data.AddressPK;
import net.javabeat.eclipselink.data.ContractDeveloper;
import net.javabeat.eclipselink.data.Developer;
import net.javabeat.eclipselink.data.DriverLicense;
import net.javabeat.eclipselink.data.Employee;
import net.javabeat.eclipselink.data.EmployeePeriod;
import net.javabeat.eclipselink.data.FreelanceDeveloper;
import net.javabeat.eclipselink.data.GlobalProject;
import net.javabeat.eclipselink.data.ICDLComputerLicense;
import net.javabeat.eclipselink.data.License;
import net.javabeat.eclipselink.data.LocalProject;
import net.javabeat.eclipselink.data.Phone;
import net.javabeat.eclipselink.data.PhonePK;
import net.javabeat.eclipselink.data.Project;

public class JPAImpl {
 static EntityManagerFactory factory = null;
 static EntityManager em = null;
 static {
 factory = Persistence.createEntityManagerFactory("EclipseLink-JPA-Installation");
 em = factory.createEntityManager();
 }

public static void main(String [] args){

String updateQueryStr = "UPDATE Employee emp SET emp.employeeName = 'Gilbert Samouel' WHERE emp.employeeId = '1'";

// UPDATE STATEMENT
 em.getTransaction().begin();
 Query query = em.createQuery(updateQueryStr);
 int rowsAffected = query.executeUpdate();
 System.out.print("Number of rows affected Are : "+rowsAffected);
 em.getTransaction().commit();

}

}

The Result of Execution

1
John Smith
Number of rows affected Are : 1

The Modification Inside the Database

The update statement means a modification and that should be applied within a Transaction.

Anatomy of Delete Statement

The anatomy of delete statement is identical to that defined in the SQL-database.
A DELETE clause.
A FROM clause, which used for defining the entities that being removed.
A WHERE clause, which provides the conditions that based on the entities have removed.

JPAImpl.java

package net.javabeat.eclipselink;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;

import net.javabeat.eclipselink.data.Address;
import net.javabeat.eclipselink.data.AddressPK;
import net.javabeat.eclipselink.data.ContractDeveloper;
import net.javabeat.eclipselink.data.Developer;
import net.javabeat.eclipselink.data.DriverLicense;
import net.javabeat.eclipselink.data.Employee;
import net.javabeat.eclipselink.data.EmployeePeriod;
import net.javabeat.eclipselink.data.FreelanceDeveloper;
import net.javabeat.eclipselink.data.GlobalProject;
import net.javabeat.eclipselink.data.ICDLComputerLicense;
import net.javabeat.eclipselink.data.License;
import net.javabeat.eclipselink.data.LocalProject;
import net.javabeat.eclipselink.data.Phone;
import net.javabeat.eclipselink.data.PhonePK;
import net.javabeat.eclipselink.data.Project;

public class JPAImpl {
 static EntityManagerFactory factory = null;
 static EntityManager em = null;
 static {
 factory = Persistence.createEntityManagerFactory("EclipseLink-JPA-Installation");
 em = factory.createEntityManager();
 }

public static void main(String [] args){
 String deleteQueryStr1 = "DELETE FROM DriverLicense lic WHERE lic.employee.employeeId = '1'";

// DELETE STATEMENT
 em.getTransaction().begin();
 // Remove the DriverLicense
 Query query = em.createQuery(deleteQueryStr1);
 int rowsAffected = query.executeUpdate();
 System.out.print("Number of rows affected Are : "+rowsAffected);
 em.getTransaction().commit();

}

}

The Result of Execution

Number of rows affected Are : 1

Don’t try removing the DriverLicense entity by mentioning the License entity, that’s because of License is mapped superclass and it is used for sharing mapping information and persistent states (See Mapped superclass).
Removing an entity does consider a Transaction.

Summary

One of the exciting parts inside the Java Persistence API is a Java Persistence Query Language that provides a (String Based Query) for querying the target persistent domain such as database. This tutorial provides you a basic elementary of how could be using of Java Persistence Query Language for the most common of SELECT, UPDATE & DELETE.

The next coming tutorials should cover more attractive parts that related to the JPQL and hence we’re talking about Eclipselink, it also, should provide an excellent tutorials and practices of what Eclipselink has added.

Required Terminologies

Statement Types

Anatomy of Select Statement

Classes Domain Anatomy

Querying using Select

Updating and Deleting

Anatomy of Update Statement

Anatomy of Delete Statement

Summary

About Amr Mohammed