The Select query is a very useful query as it returns a subset of one or more tables of a database. The query can be used to select which rows of a table should be returned and which fields of each row. When the query is executed a list of results is returned and can be processed and/or displayed by the application.
The steps used to create and use a JPQL Select query include:
- Obtaining an instance of an
EntityManager - Using the
createQuerymethod to create an instance of aQuerybased on a Select JPQL string argument - Using a method such as the
getResultListto execute and return the results of the queryThe easiest place to find an instance of the
EntityManageris in a facade class. This is where we will place our JPQL-based methods.
The findAll method is provided as part of the AbstractFacade base class. However, it uses the Criteria API to return a list of every entity in the underlying database. In this recipe, we will focus on how to use JPQL to accomplish the same task.
Add a findAll method to the PatientFacade. Use the @Override annotation to explicitly override the base class method.
@Stateless
public class PatientFacade extends AbstractFacade<Patient> {
@PersistenceContext(unitName = "PatientApplication-ejbPU")
private EntityManager entityManager;
...
@Override
public List<Patient> findAll() {
Query query = entityManager.createQuery("select p FROM Patient p");
List<Patient> list = query.getResultList();
return list;
}
...
}
Add a call to the method in the PatientServlet after the body tag is displayed.
...
out.println("<body>");
List<Patient> firstNameList = patientFacade.findAll();
for (Patient patient : firstNameList) {
out.println("<h5>" + patient.getLastName() + ", " + patient.getFirstName() + "</h5>");
}
Execute the servlet. The result is shown in the following screenshot:
The first step created the Query instance using the createQuery method. This required the use of the EntityManager class and a JPQL string. Since this method is a member of the PatientFacade, we used the entityManager variable and the createQuery method to create a Query object.
A JPQL query statement string was used as the argument to the createQuery method. What we would like the query to do is to make a request such as: "Return a list of all patients from the PATIENT table". The Select JPQL statement is designed to return this type of result. The statement consists of two parts: a Select clause and a From clause. The Select clause indicates what we want to retrieve and the From clause determines the origin of this information.
SELECT and FROM are keywords and are not case-sensitive. In these examples, they will be written in upper case to clarify the queries. A JPQL statement meeting our needs looks like this: SELECT p FROM Patient p. We can add this string to complete the process of creating the query.
Notice the use of the entity name, Patient, as opposed to the name of the table, PATIENT. The variable, p, is called an identification variable. All identification variables are declared in a From clause. They can be referenced from a Select clause, or as we will see later, from a Where clause. Optionally, we can use the AS keyword to be more explicit.
SELECT p FROM Patient AS p
For those familiar with SQL, we would use an * to identify all of the fields of the table row. In JPQL, we simply use the identification variable.
Since we are dealing at the entity level, we need an identifier to specify what we want to select. For example, if we had been interested in only the first name of the entity we would use a dot and the field name. The dot is called a navigation operator.
SELECT p.firstName FROM Patient p
To complete the process of creating and executing a query we needed to actually execute the query. The createQuery method only creates the query, it does not execute it. There are several methods we can use to execute the query. Since we are using a Select query and we need multiple entities returned, we used the getResultList method which returns a java.util.List object.
A more condensed way of writing the method is to use Java's invocation chaining. This process simply eliminates the middleman and avoids intermediate variables.
public List<Patient> findAll() {
return em.createQuery("SELECT e FROM Patient e").getResultList();
}
There is more to the Select query than we can present here. However, there are several issues we should address including:
- Eliminating duplicate entities
- Using the
Order Byclause
For some tables, certain Select queries may return multiple entities which are identical. For example, add the following method to the Medication class that will return all medications of type ACE (Angiotensin Converting Enzyme Inhibitors):
public List<String> findByType() {
Query query = entityManager.createQuery("SELECT m.name FROM Medication m WHERE m.type = 'ACE'");
List<String> list = query.getResultList();
return list;
}
Add the following code sequence to the PatientServlet after the body tag is displayed.
...
out.println("<body>");
List<String> medications = medicationFacade.findByType();
out.println("<h3>Medications of type ACE</h3>");
for (String m : medications) {
out.println("<h3>Medication: " + m + "</h3>");
}
Execute the PatientServlet. Altace is returned twice as illustrated in the following screenshot:
To avoid this duplication of medication names we can use the DISTINCT keyword. Only those entities with a distinct set of values will be returned. Replace the query in the findByType method with this query:
SELECT DISTINCT m.name FROM Medication m WHERE m.type = 'ACE'
Re-execute the PatientServlet. Duplicate names are now removed as shown here:
The Order By clause is useful in controlling the order of entities returned by a Select statement. The clause follows the Where clause and can consist of a comma delimited list of entity fields. Modify the findAll method to use the query:
SELECT DISTINCT m.name FROM Medication m ORDER BY m.name
Re-execute the PatientServlet. The list returned is sorted in ascending order by the medication's name as shown here:
If we need to sort the elements in descending order the DESC keyword is used.
SELECT DISTINCT m.name FROM Medication m ORDER BY m.name DESC
While the keyword ASC can be used explicitly to indicate an ascending sort, the Order By clause defaults to ascending. If multiple elements are included in the clause, the left-most elements have higher precedence.