JPA – Java Persistence Query Language (JPQL) – PART II

This tutorial is a complement for Java Persistence Query Language (JPQL) Part I, in which a new techniques of querying should be discovered and learned. Instead of depending on the entities that being retreived upon query for accessing their inner state/association fields, we’ve also would be querying those fields through an advanced techniques of query. This tutorial is intended to cover such these ways of inquiring. Whenever you’ve felt that you have to see the full examples that made before for covering the different associations of JPA you could refer to EclipseLink Tutorial.

Executing the Queries

To avoid code redundancy, we’ve listed a complete execution example that could be used for executing the provided queries that are listed. So, make sure that you are copying the provided query per each subsequent section and paste it in place of queryStr and execute the application to see the result of the suggested query.

package net.javabeat.eclipselink;

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.DriverLicense;
import net.javabeat.eclipselink.data.Employee;
import net.javabeat.eclipselink.data.Phone;

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 queryStr = "Put Examined Query Here";
		// Create a Query Using of Entity Manager
		Query query = em.createQuery(queryStr);
		// Try to fetch single result
		try {
			Object object = query.getSingleResult();
			if(object instanceof Employee){
				System.out.println("EmployeeID :: "+((Employee)object).getEmployeeId());
			}
			else if(object instanceof Phone){
				System.out.println("PhoneID :: "+((Phone)object).getPhoneId() + " Owned By the Employee :: " + ((Phone)object).getEmployee().getEmployeeId());
			}
			else if(object instanceof DriverLicense){
				System.out.println("DriverLicenseID :: "+((DriverLicense)object).getLicenseId() + " Owned By the Employee " + ((DriverLicense)object).getEmployee().getEmployeeId());
			}
			else {
				System.out.println("Value Queried Is :: "+object + " :: ");
			}
		}
		catch(Exception e){
			// Try to fetch the result list
			List<Object> objects = query.getResultList();
			// Loop over results
			 for(Object  object : objects){
					if(object instanceof Employee){
						System.out.println("EmployeeID :: "+((Employee)object).getEmployeeId());
					}
					else if(object instanceof Phone){
						System.out.println("PhoneID :: "+((Phone)object).getPhoneId() + " Owned By the Employee :: " + ((Phone)object).getEmployee().getEmployeeId());
					}
					else if(object instanceof DriverLicense){
						System.out.println("DriverLicenseID :: "+((DriverLicense)object).getLicenseId() + " Owned By the Employee " + ((DriverLicense)object).getEmployee().getEmployeeId());
					}
					else if(object instanceof Object []){
						// loop over the array of objects
						Object [] arrayObjects = (Object [])object;

						for(Object arrayObject : arrayObjects){
							if(arrayObject instanceof Employee){
								System.out.print("EmployeeID :: "+((Employee)arrayObject).getEmployeeId());
							}
							else if(arrayObject instanceof Phone){
								System.out.print("PhoneID :: "+((Phone)arrayObject).getPhoneId() + " Owned By the Employee :: " + ((Phone)arrayObject).getEmployee().getEmployeeId() + " :: ");
							}
							else if(arrayObject instanceof DriverLicense){
								System.out.print("DriverLicenseID :: "+((DriverLicense)arrayObject).getLicenseId() + " Owned By the Employee " + ((DriverLicense)arrayObject).getEmployee().getEmployeeId() + " ::");
							}
							else {
								System.out.print("Value Queried Is :: "+arrayObject + " :: ");
							}
						}
						System.out.println("");
					}
					else {
						System.out.println("Value Queried Is :: "+object + " :: ");
					}
			 }

		}
	}

}

• Some minor changes are happened per each executed query for making the messages more desirable in explanation the concept the query is about to clarify.

Using of Identification Variables

The identification variable is a key stone that the next coming topics need it for being clarified, look at the following fragment of code that shows the application of (identification variable) inside the query of JPQL:

SELECT employee FROM Employee employee

• The employee is an identification variable.
• The identification variable is a valid identifier declared in the FROM clause.
• An identification variable must not be reserved identifier or have the same name as any entity in the same persistent unit.
• Identification variables are case sensitive, so the use of employee is differ from using Employee in case the Employee entity doesn’t defined in the same persistent unit.
• An identification variable evaluates to a value of the type of the expression use in declaring the variable.
• The employee evaluates into Employee instance.
• The identification variable does support the composing of Path Expression.

Accessing State/Association Fields By Path Expression

An identification variable followed by the navigation operator (.) and a state field or association field compose a Path Expression.

SELECT employee.employeeId, phones.phoneNumber FROM Employee employee, employee.phones phones

• The employee and phones are two different identification variables.
• The employee.employeeId, phones.phoneNumber compose a Path Expression.
• The type of the path expression is the type computed as a result of navigation, so the type of the state field or association field to which the expression navigates.
• The employee.employeeId computed to an integer value, cause the employeeId is of type int.
• The phones.phoneNumber computed to a String value, cause the phoneNumber is of type String.
• Depending on the navigability, a path expression that leads to an association field or field whose type is an embeddable class may be further composed.
• Path Expression can be composed from other path expressions if the original path expression evaluates to a single-valued type (not a collection).
• An implicit inner joins has been specified at the FROM clause (See Joins Section).

Let’s try navigate the employee identification variable for accessing the phones by using the statement employee.phones.

SELECT employee.employeeId,employee.phones, phones.phoneNumber FROM Employee employee, employee.phones phones

If you’ve executed the previous snippet of code you are surely getting an exception.

Navigating Into Embeddable Class

Let’s try the following the that composed a navigation into Embeddable address field.

SELECT employee.employeeId,address.addressId.addressId,address.addressId.addressCityId FROM Employee employee, employee.address address

• The Path Expression that navigates the address field of Employee does again composed to access those fields inside the Embeddable AddressPK. (See EclipseLink Examples).

Inner Joins

An inner join may be implicitly specified by the use of a cartesian product on the FROM (As you’ve seen before) clause and a join condition in the where clause. In the absence of a join condition, this reduces to the cartesian product. Let’s see the difference between an implicit of inner join and explicit join. But before seeing the result of the samples, we’ve assumed that we have two employees and 3 phones. Employee with an id (1) references two phones and Employee with an id (2) references 1 phone.

Using Implicit JOIN

SELECT employee.employeeId,phone.phoneId FROM Employee employee, employee.phones phone

Using Explicit JOIN

SELECT employee.employeeId,phone.phoneId FROM Employee employee JOIN employee.phones phone

The result of two samples of query should be identical:

EmployeeID :: 1 :: PhoneID :: 1 ::
EmployeeID :: 1 :: PhoneID :: 2 ::
EmployeeID :: 2 :: PhoneID :: 3 ::

• The Java Persistence Query Language (JPQL) does provide an inner join in case the absence of JOIN keyword and if the JOINED entities does provide a foreign key that is mapped to an entity relationship.
• The Implicit Inner Join called (Theta-Join).
• If you’ve removed the JOIN keyword, you will get the same result, cause there is a foreign key between Employee and Phone entities, so an inner JOIN should be specified by the FROM clause and without need for WHERE clause.

Let’s look at the following example that does make a query between Employee and DriverLicense, in that the Employee entity doesn’t provide a direct association of DriverLicense.

SELECT employee,license FROM Employee employee, DriverLicense license

The Result of the previous code is:

Employee ID :: 1 :: DriverLicense ID :: 26 :: License Owned By Employee :: 1
Employee ID :: 2 :: DriverLicense ID :: 26 :: License Owned By Employee :: 1

• Even the DriverLicense of id 26 doesn’t relate anymore to the Employee of ID 2, however the result shows a Cross Join that already made by the JPQL FROM clause.
• The solution for such kind problem is by using the WHERE condition.
• The using of Join condition that specified at the WHERE clause, when a join doesn’t involve a foreign key relationship that is mapped to an entity relationship.

Let’s look at the next coming fragment of code that does provide a WHERE condition.

SELECT employee,license FROM Employee employee, DriverLicense license
WHERE employee.employeeId = license.employee.employeeId

The Result of the previoud code is:

Employee ID :: 1 :: DriverLicense ID :: 26 :: License Owned By Employee :: 1

Left Outer Joins

The LEFT JOIN & LEFT OUTER JOIN are synonymous. They enable the retrieval of a set of entities where matching values in the join condition may be absent.

At the next following example, let we have a two different employees, one of them is associated with two phone objects. let’s querying the database using two different queries.

SELECT DISTINCT employee.employeeId FROM Employee employee, employee.phones phone

The result of the query is

EmployeeID :: 1 ::

• By using the inner join as we have done in the previous query will not give us that employee that’s not associated with any instance of Phone.
• The solution of such that problem is by querying the database by using the LEFT JOIN.

SELECT DISTINCT employee.employeeId FROM Employee employee LEFT JOIN employee.phones phone

The result of the query is

EmployeeID :: 1 ::
EmployeeID :: 2 ::

• The result of the LEFT JOIN is the employees that have a phones and those employees that haven’t a phones.

Queries Using Input Parameters

The following query finds the employee whose ID is designated by an input parameters.

// Create a parameterized query string
String queryStr = "SELECT employee.employeeId FROM Employee employee WHERE employee.employeeId = ?1";
// Create a Query Using of Entity Manager
Query query = em.createQuery(queryStr);
// Set the parameter by specify the index of the parameter and the value
query.setParameter(1, 1);

• To specify a parameter in the query string you have to type a question suffixed by the index of the parameter.
• The query above specify one parameter at the index 1.
• When using a parametrized query, you have to specify the parameter index and parameter value by invoking the setParameter upon Query object.

ORDER BY Clause

We’ve the opportunity to order the result of the query by using ORDER BY reserved keyword. Order By reserved keyword is working in conjunction with ASC and DESC.

SELECT employee.employeeId,employee.employeeName FROM Employee employee ORDER BY employee.employeeName DESC

The result of the query above

Employee ID Is :: 2 :: Employee Name :: Suza Smith ::
Employee ID Is :: 1 :: Employee Name :: Gilbert Samouel ::

• The ORDER BY clause allows objects or values that are returned by the query to be ordered.
• The keyword ASC specifies that ascending ordering, while the DESC specifies that descending ordering.
• Ascending order is the default ordering.

Summary

Java Persistence Query Language (JPQL), provides a lot of techniques for doing a query. Identification Variables, Path Expressions, Inner Joins, Left Joins and other concept are introduced at this tutorial.